使用NPOI 操作Excel
个人使用的电脑基本默认安装Excel 操作起来
调用Excel的组件便可.如果是一台服务器.没有安装Excel,也就无法调用Excel组件.
在此推荐第三方插件.NPOI 支持XLS(2007)和XLSX(2012)读写.
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.IO; namespace WebApplication1.Helper { public class ExcelHelper : IDisposable { private string fileName = null; //文件名 private IWorkbook workbook = null; private FileStream fs = null; private bool disposed; public ExcelHelper(string fileName) { this.fileName =http://www.likecs.com/ fileName; disposed = false; fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(fs); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(fs); } public List<string> SheetName { get { List<string> data = null; if (workbook != null) { data = new List<string>(); for (int i = 0; i < workbook.NumberOfSheets; i++) { data.Add(workbook.GetSheetAt(i).SheetName.ToString()); } } return data; } } public int SheetCount { get { return workbook == null ? 0 : workbook.NumberOfSheets; } } /// <summary> /// 将excel中的数据导入到DataTable中 /// </summary> /// <param>excel工作薄sheet的名称</param> /// <param>第一行是否是DataTable的列名</param> /// <returns>返回的DataTable</returns> public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn) { ISheet sheet = null; DataTable data = new DataTable(); int startRow = 0; try { //fs = new FileStream(fileName, FileMode.Open, FileAccess.Read); //if (fileName.IndexOf(".xlsx") > 0) // 2007版本 // workbook = new XSSFWorkbook(fs); //else if (fileName.IndexOf(".xls") > 0) // 2003版本 // workbook = new HSSFWorkbook(fs); if (sheetName != null) { sheet =http://www.likecs.com/ workbook.GetSheet(sheetName); if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet { sheet = workbook.GetSheetAt(0); } } else { sheet = workbook.GetSheetAt(0); } if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数 if (isFirstRowColumn) { for (int i = firstRow.FirstCellNum; i < cellCount; ++i) { ICell cell =http://www.likecs.com/ firstRow.GetCell(i); if (cell != null) { string cellValue =http://www.likecs.com/ cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } startRow = sheet.FirstRowNum + 1; } else { startRow =http://www.likecs.com/ sheet.FirstRowNum; } //最后一列的标号 int rowCount =http://www.likecs.com/ sheet.LastRowNum; for (int i = startRow; i <= rowCount; ++i) { IRow row =http://www.likecs.com/ sheet.GetRow(i); if (row == null) continue; //没有数据的行默认是null DataRow dataRow =http://www.likecs.com/ data.NewRow(); for (int j = row.FirstCellNum; j < cellCount; ++j) { if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null dataRow[j] =http://www.likecs.com/ row.GetCell(j).ToString(); } data.Rows.Add(dataRow); } } return data; } catch (Exception ex) { Console.WriteLine("Exception: " + ex.Message); return null; } } public static void CreateExcel(string projectName) { try { string fileName = $"{projectName}.xlsx"; // 文件名称 string filePath = $"{ConfigurationManager.AppSettings["file"].ToString()}" + "\\" + fileName; // 2.解析单元格头部，设置单元头的中文名称 XSSFWorkbook workbook = new XSSFWorkbook(); // 工作簿 ISheet sheet = workbook.CreateSheet("sheet"); //#region 设置Excel表格第一行的样式 //IRow titleInfo = sheet.CreateRow(0); //ICell cellTitle = titleInfo.CreateCell(0); //cellTitle.SetCellValue("会员信息批量录入模板"); //ICellStyle titleStyle = workbook.CreateCellStyle(); //IFont titleFont = workbook.CreateFont(); //titleFont.FontHeightInPoints = 25; //titleFont.Boldweight = short.MaxValue;//字体加粗 //titleStyle.SetFont(titleFont); //cellTitle.CellStyle = titleStyle; //#endregion //IRow dataFields = sheet.CreateRow(2); //ICellStyle style = workbook.CreateCellStyle();//创建样式对象 //style.Alignment = HorizontalAlignment.CENTER;//水平对齐 //style.VerticalAlignment = VerticalAlignment.CENTER;//垂直对齐 //IFont font = workbook.CreateFont(); //创建一个字体样式对象 //font.FontName = "宋体"; //和excel里面的字体对应 //font.Color = new HSSFColor.RED().GetIndex();//颜色参考NPOI的颜色对照表(替换掉PINK()) //font.FontHeightInPoints = 10;//字体大小 //font.Boldweight = short.MaxValue;//字体加粗 //style.SetFont(font); //将字体样式赋给样式对象 //sheet.SetColumnWidth(0, 20 * 256);//设置列宽 //string[] colums = { "*会员卡号", "*会员手机", "*会员姓名", "*会员等级", "会员性别", "电子邮箱", "会员状态", "固定电话", "永久有效", "身份证号", "开卡费用", "会员地址", "备注信息" }; //ICell Cell = null; //for (int i = 0; i < colums.Count(); i++) //{ // Cell = dataFields.CreateCell(i); // Cell.CellStyle = style; // Cell.SetCellValue(colums[i]); // sheet.SetColumnWidth(i, 17 * 256); //} //sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colums.Count()));//合并单元格 // 4.生成文件 FileStream file = new FileStream(filePath, FileMode.Create); workbook.Write(file); file.Close(); } catch (Exception ex) { throw ex; } } public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (!this.disposed) { if (disposing) { if (fs != null) fs.Close(); } fs = null; disposed = true; } } } }