C# 使用NPOI导入Excel,不用安装Office及OLEDB,支持xls/xlsx, x86/x64
NPOI优点:部署机器不用安装Excel或OLEDB,支持32及64位的操作系统,支持xls/xlsx
使用NuGet搜索安装NPOI最新版,添加以下命名空间
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel;
添加类 ExcelImporter
public static class ExcelImporter { /// <summary> /// 根据sheet序号获取数据 /// </summary> /// <param name="fileName"></param> /// <param name="sheetIndex"></param> /// <returns></returns> public static DataTable ImportByIndex(string fileName, int sheetIndex = 0) { using (FileStream fs = File.OpenRead(fileName)) { IWorkbook book = null; if (fileName.ToLower().EndsWith("xls")) book = new HSSFWorkbook(fs); if (fileName.ToLower().EndsWith("xlsx")) book = new XSSFWorkbook(fs); var sheet = book.GetSheetAt(sheetIndex); if (sheet == null) throw new Exception($"Can't find sheet at index of {sheetIndex}"); return sheet.ToTable(); } } /// <summary> /// 根据sheet名称获取数据 /// </summary> /// <param name="fileName"></param> /// <param name="sheetName"></param> /// <returns></returns> public static DataTable ImportBySheetName(string fileName, string sheetName) { using (FileStream fs = File.OpenRead(fileName)) { IWorkbook book = null; if (fileName.ToLower().EndsWith("xls")) book = new HSSFWorkbook(fs); if (fileName.ToLower().EndsWith("xlsx")) book = new XSSFWorkbook(fs); var sheet = book.GetSheet(sheetName); if (sheet == null) throw new Exception($"Can't find sheet name of {sheetName}"); return sheet.ToTable(); } } /// <summary> /// 将sheet转化为DataTable /// </summary> /// <param name="sheet"></param> /// <returns></returns> private static DataTable ToTable(this ISheet sheet) { DataTable dt = new DataTable(); var firstRow = sheet.GetRow(0); if (firstRow != null) { for (int columnIndex = 0; columnIndex < firstRow.LastCellNum; columnIndex++) { var cell = firstRow.GetCell(columnIndex); dt.Columns.Add(cell.StringCellValue.Trim(), typeof(string)); } for (int rowIndex = 1; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++) { var row = sheet.GetRow(rowIndex); if (row != null) { DataRow drNew = dt.NewRow(); for (int columnIndex = 0; columnIndex < firstRow.LastCellNum; columnIndex++) { drNew[columnIndex] = Convert.ToString(row.GetCell(columnIndex)); } dt.Rows.Add(drNew); } } } return dt; } }
使用时传入Excel文件路径及表格名称/序号即可,返回DataTable