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

NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目, 使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。本文主要介绍.NET Core(C#)中，使用NPOI读写Excel(.xls,.xlsx)的方法，以及相关的示例代码。

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 rowList = new List();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()) && !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 stuList = new List()             {                 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 columns = new List();    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);  }}