利用 Nuget 安装 NPOI 组件。
所需引用的 dll：ICSharpCode.SharpZipLib.dll、NPOI.dll、NPOI.OOXML.dll、NPOI.OpenXml4Net.dll、NPOI.OpenXmlFormats.dll
程序代码：
using System; using System.Collections.Generic; using System.Data; using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; namespace MyBaseFramework { /// <summary> /// Excel 数据导入导出 /// </summary> public class ExcelHelper { /// <summary> /// 从 Excel 导入数据 /// </summary> /// <param name="fileFullPath">Excel文件路径及名称</param> /// <param name="sheetName">excel中的sheet名称</param> /// <param name="firstRowIsTitle">第一行是否是列标题</param> /// <returns>DataTable格式的数据</returns> public static DataTable ImportDataFromExcel(string fileFullPath, string sheetName = "", bool firstRowIsTitle = true) { IWorkbook workbook = null; ISheet sheet = null; DataTable dataTable = new DataTable(); //检查文件是否存在 if (!File.Exists(fileFullPath)) return null; using (FileStream fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read)) { try { if (fileFullPath.IndexOf(".xlsx") > 0) // Excle 2007 及以后的版本 workbook = new XSSFWorkbook(fs); else if (fileFullPath.IndexOf(".xls") > 0) // Excle 2003 及以前的版本 workbook = new HSSFWorkbook(fs); //如果指定了sheet名称则打开指定的sheet if (!string.IsNullOrEmpty(sheetName)) { sheet = workbook.GetSheet(sheetName); if (sheet == null) return null; } else sheet = workbook.GetSheetAt(0); //Excel中至少会存在一个Sheet //处理sheet中的数据 IRow firstRow = sheet.GetRow(0); int columnCount = firstRow.LastCellNum; //取第一行中最后一个cell的编号，即总列数 int rowCount = sheet.LastRowNum; //取最后一行的行号，即总行数 int dataRowStart = 0; //sheet中数据起始行的序号 //给datatable依次添加列标题 if (firstRowIsTitle) { for (int i = firstRow.FirstCellNum; i < columnCount; ++i) { ICell cell = firstRow.GetCell(i); if (cell != null) { string cellValue = cell.StringCellValue; //判断列标题是否为空 if (!string.IsNullOrEmpty(cellValue)) { DataColumn column = new DataColumn(cellValue); dataTable.Columns.Add(column); } } } dataRowStart = sheet.FirstRowNum + 1; } else dataRowStart = sheet.FirstRowNum; //给datatable依次添加各行数据 for (int i = dataRowStart; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) //没有数据的行默认是null continue; DataRow dataRow = dataTable.NewRow(); for (int j = row.FirstCellNum; j < columnCount; ++j) { ICell cell = row.GetCell(j); if (cell != null) { //根据格式读取数据类型 switch (cell.CellType) { case CellType.Blank: dataRow[j] = ""; break; case CellType.String: dataRow[j] = cell.StringCellValue; break; case CellType.Numeric: //数值 if (DateUtil.IsCellDateFormatted(cell)) //检查单元格格式是否为日期格式 dataRow[j] = cell.DateCellValue; else dataRow[j] = cell.NumericCellValue; break; case CellType.Formula: //公式 HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook); dataRow[j] = e.Evaluate(cell).StringValue; break; default: dataRow[j] = ""; break; } } } dataTable.Rows.Add(dataRow); } return dataTable; } catch (Exception ex) { fs.Close(); throw ex; } } } /// <summary> /// 导出数据到 Excel /// </summary> /// <param name="dt">源数据</param> /// <param name="fileSaveFullPath">Excel 文件保存位置</param> public static void ExportDataToExcel(DataTable dt, string fileSaveFullPath) { try { IWorkbook workbook = null; ISheet sheet = null; if (fileSaveFullPath.IndexOf(".xlsx") > 0) // Excle 2007 及以后的版本 workbook = new XSSFWorkbook(); else if (fileSaveFullPath.IndexOf(".xls") > 0) // Excle 2003 及以前的版本 workbook = new HSSFWorkbook(); sheet = workbook.CreateSheet(); // 表头的样式 ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.IsLocked = true; headStyle.SetFont(font); // 数据内容的样式 IFont fontText = workbook.CreateFont(); fontText.FontHeightInPoints = 10; ICellStyle styleText = workbook.CreateCellStyle(); styleText.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleText.SetFont(fontText); // 数据行索引 int rowIndex = 0; for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0 || i % 65535 == 0) { // Excle 2003 及以前的版本单个 sheet 最多只支持 65535 行，超出后重新创建 sheet if (i > 0) sheet = workbook.CreateSheet(); // 设置表头 IRow headerRow = sheet.CreateRow(0); foreach (DataColumn column in dt.Columns) { int columnIndex = column.Ordinal; headerRow.CreateCell(columnIndex).SetCellValue(column.Caption); headerRow.GetCell(columnIndex).CellStyle = headStyle; sheet.SetColumnWidth(columnIndex, 50 * 128); } // 固定首行 sheet.CreateFreezePane(0, 1, 0, dt.Columns.Count - 1); rowIndex = 1; } // 填充数据 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dt.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(Convert.ToString(dt.Rows[i][column])); dataRow.GetCell(column.Ordinal).CellStyle = styleText; } rowIndex++; } // 保存文件 using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); using (FileStream fs = new FileStream(fileSaveFullPath, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); ms.Flush(); } } } catch (Exception ex) { throw ex; } } /// <summary> /// 从特定格式的数据字典Excel文件中提取的数据 /// 说明：该方法用于根据数据字典文档来生成数据库表 /// </summary> /// <param name="fileFullPath">Excel文件路径及名称</param> /// <returns></returns> public static List<DataTable> ImportDataFromExcelForGenDbScript(string fileFullPath) { IWorkbook workbook = null; ISheet sheet = null; try { using (FileStream fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read)) { if (fileFullPath.IndexOf(".xlsx") > 0) // Excle 2007 及以后的版本 workbook = new XSSFWorkbook(fs); else if (fileFullPath.IndexOf(".xls") > 0) // Excle 2003 及以前的版本 workbook = new HSSFWorkbook(fs); fs.Flush(); fs.Close(); } List<DataTable> _dataDictionaryList = new List<DataTable>(); for (int i = 0; i < workbook.NumberOfSheets; i++) { sheet = workbook.GetSheetAt(i); if (sheet != null) { DataTable data = new DataTable(); IRow firstRow = sheet.GetRow(1); //从第二行开始才是数据行 int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数（舍去最后一列，因为最后一列有时变成了两列） for (int j = 0; j < cellCount; j++) { ICell cell = firstRow.GetCell(j); if (cell != null) { string cellValue = cell.StringCellValue; if (cellValue != null) { DataColumn column = new DataColumn(cellValue); data.Columns.Add(column); } } } int rowCount = sheet.LastRowNum; for (int k = 0; k <= rowCount; ++k) { IRow row = sheet.GetRow(k); if (row == null) continue; //没有数据的行默认是null if (row.GetCell(row.FirstCellNum) == null) continue; //如果第一行为空则忽略此行 DataRow dataRow = data.NewRow(); for (int m = row.FirstCellNum; m < cellCount; m++) if (row.GetCell(m) != null) //同理，没有数据的单元格都默认是null dataRow[m] = row.GetCell(m).ToString(); data.Rows.Add(dataRow); } if (data != null) _dataDictionaryList.Add(data); } } return _dataDictionaryList; } catch (Exception ex) { throw ex; } } } }