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