.NET Core(C#) 使用NPOI读写Excel(.xls,.xlsx)示例代码(不用安装Office)

1､安装引用DotNetCore.NPOI

1）使用Nuget界面管理器

搜索"DotNetCore.NPOI",在列表中找到它，点击"安装"

相关文档：VS(Visual Studio)中Nuget的使用

2）使用Package Manager命令安装

PM> Install-Package DotNetCore.NPOI

3）使用.NET CLI命令安装

> dotnet add TodoApi.csproj package DotNetCore.NPOI

2、使用NPOI读取Excel文件示例代码

DataTable dtTable = new DataTable();
List<string> rowList = new List<string>();
ISheet sheet;
using (var stream = new FileStream("TestData.xlsx", FileMode.Open))
{
    stream.Position = 0;
    XSSFWorkbook xssWorkbook = new XSSFWorkbook(stream);
    sheet = xssWorkbook.GetSheetAt(0);
    IRow headerRow = sheet.GetRow(0);
    int cellCount = headerRow.LastCellNum;
    for (int j = 0; j < cellCount; j++)
    {
        ICell cell = headerRow.GetCell(j);
        if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
        {
            dtTable.Columns.Add(cell.ToString());
        } 
    }
    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
    {
        IRow row = sheet.GetRow(i);
        if (row == null) continue;
        if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
        for (int j = row.FirstCellNum; j < cellCount; j++)
        {
            if (row.GetCell(j) != null)
            {
                if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) &amp;&amp; !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
                {
                    rowList.Add(row.GetCell(j).ToString());
                }
            }
        }
        if(rowList.Count>0)
        dtTable.Rows.Add(rowList.ToArray());
        rowList.Clear(); 
    }
}

3、通过NPOI写入Excel文件导出数据

public class STU
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string City { get; set; }
    }
  List<STU> stuList = new List<STU>()
             {
                 new STU{ID=1,Name="Lily",Age=18,City="NewYork"},
                 new STU{ID=2,Name="Lucy",Age=20,City="NewYork"},
                 new STU{ID=1,Name="LiLei",Age=18,City="BeiJIng"}
             };
//象数据转换为Datatable，以简化逻辑。
//Datatable是处理复杂数据类型的最简单方法，便于读取和格式化。
DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(stuList), (typeof(DataTable)));
var memoryStream = new MemoryStream();
using (var fs = new FileStream("Result.xlsx", FileMode.Create, FileAccess.Write))
{
    IWorkbook workbook = new XSSFWorkbook();
    ISheet excelSheet = workbook.CreateSheet("Sheet1");
    List<String> columns = new List<string>();
    IRow row = excelSheet.CreateRow(0);
    int columnIndex = 0;
    foreach (System.Data.DataColumn column in table.Columns)
    {
        columns.Add(column.ColumnName);
        row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
        columnIndex++;
    }
    int rowIndex = 1;
    foreach (DataRow dsrow in table.Rows)
    {
        row = excelSheet.CreateRow(rowIndex);
        int cellIndex = 0;
        foreach (String col in columns)
        {
            row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
            cellIndex++;
        }
        rowIndex++;
    }
    workbook.Write(fs);
  }
}