受欢迎的博客标签

Read/Write Excel file in .NET using NPOI

Published

 

NPOI 是 Apache POI 项目的 .NET 版本。POI 是一个开源项目,能帮助你读/写 xls、doc、ppt 等文件。它具有广泛用途,如:

在服务器未安装 Microsoft Office 套件的情况下生成 Excel 报告,并且比在后台调用 Microsoft Excel ActiveX 效率更高;
从 Office 文档中提取文本,以帮助你实现全文索引功能(大多数情况下,此功能用于创建搜索引擎);
从 Office 文档中提取图像;
生成包含公式的 Excel 工作表。

1.https://github.com/svn2github/npoi  (updated stop)

2.https://github.com/dotnetcore/NPOI (This repository has been archived by the owner. It is now read-only.)

Stop using Dotnetcore/NPOI nuget package. It’s obsolete!

3.https://github.com/nissl-lab/npoi  (updated latest)

NPOI 由瞿总和他的团队由 Apache POI 移植到 .NET 的,以下是NPOI Github 地址:

在 Linux 上,你需要安装 libgdiplus。从 1.2.0 开始,还需要 libdl

安装过程见:https://github.com/dotnetcore/NPOI/blob/master/README.zh-CN.md#%E9%80%9A%E5%91%8A

How to use NPOI on Linux

https://github.com/nissl-lab/npoi/wiki/How-to-use-NPOI-on-Linux

使用NPOI——C#和WEB API导出到Excel

https://blog.csdn.net/mzl87/article/details/100712603

webapi下使用npoi导出数据到excel,包含自动列宽和自动合并某一列单元格
https://blog.csdn.net/u010162297/article/details/76572269

asp.net core webapi 生成导出excel
https://www.cnblogs.com/jianjialin/p/9549924.html

前后端分离,导出数据为文件或下载文件,前端如何处理后端返回的数据
https://www.cnblogs.com/sunshine6/p/8296945.html

 

Step 1:

PM> Install-Package NPOI -Version 2.5.1


 Step 2:Read the content of excel file using NPOI

static string ReadExcel()
        {
            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()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
                            {
                                rowList.Add(row.GetCell(j).ToString());
                            }
                        }
                    }
                    if(rowList.Count>0)
                    dtTable.Rows.Add(rowList.ToArray());
                    rowList.Clear(); 
                }
            }
            return JsonConvert.SerializeObject(dtTable);
        }