将DataTable中的数据保存到Excel （二） 使用NPOI

背景

前面写过一篇DataTable导出到Excel的文章，使用的是Office COM组件进行导入导出，用户体验感有点差，对环境依赖性太强（比如“检索COM类工厂…”错误），且需要通过Marshal.ReleaseComObject方法释放资源，但依然避免不了性能差。

之后一直寻找新的方法，发现了NPOI，一个干净又强大且免费使用的开源控件。

1 NPOI 简介

NPOI是一个开源的.NET组件，可以读写Excel文件，支持Office97-2007格式，功能强大。且不需要安装Microsoft Ofifce.

主要优势：
1 免费使用，不涉及任何版权问题
2 支持对标准Excel的读写（单元格格式、数据格式、公式等等）
3 基于.net 2.0 也支持xlsx 和docx格式（当然更高框架的4.0也支持）
4 比起使用Office 的API更加方便，更人性化，易于上手
5 性能优异（相对于前面的方法）

GitHub开源地址: https://github.com/nissl-lab/npoi
使用案例(Demo)地址： https://github.com/nissl-lab/npoi-examples

2 使用NPOI

可以通过Nuget安装包管理下载安装。

文章目前用的是NPOI的最新版本2.0.6.0 ，使用到的程序集有 NPOI.dll、 NPOI.OOXML.dll、NPOI.OpenXmlFormats.dll直接添加到项目引用即可。

最常用的对象主要位于NPOI.HSSF.UserMode空间下，主要有以下对象，对应的接口位于NPOI.SS.UserMode空间下

HSSF对象	SS接口	描述
HSSFWorkbook	IWorkbook	Excel文件
HSSFSheet	ISheet	工作表
HSSFRow	IRow	行
HSSFCell	ICell	列

2.1 创建一个简单的工作簿

创建一个Workbook对象，添加一个工作表，在工作表中添加行和列，并设置单元格的内容。

using System.IO;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

 void CreateSheet()
        {
            //创建workbook对象
            IWorkbook workbook = new HSSFWorkbook();
            //创建工作表
            ISheet sheet = workbook.CreateSheet("testSheet1");
            workbook.CreateSheet("testSheet2");
            workbook.CreateSheet("testSheet3");
            //在工作表1中添加行
            IRow row = sheet.CreateRow(0);
            sheet.CreateRow(1);
            //在行中添加一列
            ICell cell = row.CreateCell(0);
            //设置单元格的内容
            cell.SetCellValue("Hello Test");
            //继续添加列
            cell = row.CreateCell(1);
            //设置单元格的内容
            cell.SetCellValue("HHH");
            //写入文件中
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            workbook.Write(file);
            file.Close();
        }

效果图如下：

2.2 简单的读取内容

使用FileStream读取上一小节生成的Excel的内容

  void ReadExcel()
        {
            //文件路径
            string path = @"test.xls";

            //创建workbook对象
            IWorkbook workBook = null;

            using(FileStream fs = File.OpenRead(path))
            {//文件流读取
               
                workBook = new HSSFWorkbook(fs);

                //读取工作簿
                ISheet sheet = workBook.GetSheetAt(0);
                if(sheet == null)
                {
                    return;
                }

                //读取行
                IRow row = sheet.GetRow(0);
                if(row == null)
                {
                    return;
                }

                //获取单元格
                ICell cell = row.GetCell(0);
                if(cell == null)
                {
                    return;
                }

                //获取单元格内容
                string value = cell.ToString();
                //打印测试一下读取到的值
                Console.WriteLine(value);

                cell = row.GetCell(1);
                if (cell == null)
                {
                    return;
                }

                //获取单元格内容
                 value = cell.ToString();
                //打印测试一下读取到的值
                Console.WriteLine(value);
            }
        }

效果图

2.3 将DataTable数据导出到Excel(NPOI)

将DataTable中的数据导出到本地，使用Excel保存。

     private DataTable dataTable;

        /// <summary>
        /// 初始化DataTable数据
        /// </summary>
        private void InitTable()
        {
            dataTable = new DataTable();
            dataTable.Columns.Add("姓名", typeof(string));
            dataTable.Columns.Add("年龄", typeof(string));
            dataTable.Columns.Add("城市", typeof(string));

            List<string> citys = new List<string>
           {
              "广州","深圳","佛山","东莞","中山", 
               "珠海","江门","肇庆","惠州"
           };

            Random random = new Random();

            for(int i=0; i<500; i++)
            {
                DataRow row = dataTable.NewRow();
                //姓名
                row[0] = "小" + i.ToString("d3");
                //年龄
                row[1] = random.Next(18, 36).ToString();
                //城市
                row[2] = citys[i % 9];

                dataTable.Rows.Add(row);
            }
        }


        /// <summary>
        /// DataTable导出到Excel
        /// </summary>
        void DataTableToExcel()
        {
            //初始化DataTable的数据
            InitTable();

            //创建工作簿对象
            IWorkbook workBook = new HSSFWorkbook();

            //创建一个sheet
            ISheet sheet = workBook.CreateSheet("DataTable");

            //创建标题行
            IRow headerRow = sheet.CreateRow(0);
            ICell cell;
            for(int i=0; i<dataTable.Columns.Count; i++)
            {
                cell = headerRow.CreateCell(i);
                cell.SetCellValue(dataTable.Columns[i].Caption);
            }
           
            //将内容写入
            for(int i=0; i<dataTable.Rows.Count; i++)
            {
                //创建新行
                headerRow = sheet.CreateRow(i + 1);

                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    //创建单元格并设置内容
                    cell = headerRow.CreateCell(j);
                    cell.SetCellValue(dataTable.Rows[i][j].ToString());
                }
            }
            

            //写入文件
            using(FileStream fs = File.OpenWrite(@"DataTable.xls"))
            {
                workBook.Write(fs);
                fs.Close();
            }

        }

效果图：导出速度还是很快的

2.4 Excel(NPOI)导入到DataTable

读取Excel数据，并存到DataTable中

        /// <summary>
        /// 读取Excel数据，存储到DataTable
        /// </summary>
        DataTable ExcelToDataTable()
        {
           // 初始化DataTable
            DataTable table = new DataTable();
           
            string path = @"DataTable.xls";
            using(FileStream fs = File.OpenRead(path))
            {
                //获取文件
                IWorkbook workBook = new HSSFWorkbook(fs);
                if(workBook == null)
                {
                    return table;
                }

                //获取Sheet
                ISheet sheet = workBook.GetSheetAt(0);
                if (sheet == null)
                {
                    return table;
                }

                //获取标题行
                IRow headerRow = sheet.GetRow(0);
                if(headerRow == null)
                {
                    return table;
                }
                //获取列和行的数量
                int cellCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;

                for(int i=headerRow.FirstCellNum; i < cellCount; i++)
                {//设置列标题
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                for(int i= (sheet.FirstRowNum + 1); i<=rowCount; i++)
                {//行
                    DataRow dataRow = table.NewRow();
                    IRow row = sheet.GetRow(i);
                    
                    if(row == null)
                    {
                        continue;
                    }

                    for(int j= row.FirstCellNum; j < cellCount; j++)
                    {//列
                        if(row.GetCell(j) != null)
                        {

                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                    //将行添加到DataTable
                    table.Rows.Add(dataRow);
                }
                
            }

            //Console.WriteLine(table.Rows[0][0].ToString());
            //Console.WriteLine(table.Rows[0][1].ToString());
            //Console.WriteLine(table.Rows[0][2].ToString());
            return table;

        }

效果图：验证一下，存入Table中的数据是对的

3 NPOI 总结

1 开发中主要用到的对象是在 NPOI.HSSF.UserMode命名空间下

2 对应的接口位于 NPOI.SS.UserMode命名空间下

3 主要对象 HSSFWorkbook（IWorkbook）、 HSSFSheet（ISheet） 、 HSSFRow (IRow)、HSSFCell (ICell)