Winform中通过NPOI导出Excel的三种方式(HSSFWorkbook，XSSFWorkbook，SXSSFWorkbook)附代码下载

￡神魔★判官ぃ 2023-10-06 09:59 46阅读 0赞

## 场景 ##

### HSSFworkbook,XSSFworkbook,SXSSFworkbook区别 ###

HSSFWorkbook:

是操作Excel2003以前（包括2003）的版本，扩展名是.xls；导出excel最常用的方式；但是此种方式的局限就是导出的行数至多为65535行，超出65536条后系统就会报错。

XSSFWorkbook:

是操作Excel2007后的版本，扩展名是.xlsx；为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行，不过这样就伴随着一个问题---OOM内存溢出，原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化。

SXSSFWorkbook:

也是操作Excel2007后的版本，扩展名是.xlsx；SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

所以会在默认的C盘目录下生成一些临时文件，默认路径是：

C:\\Users\\HAOHAO\\AppData\\Local\\Temp\\poifiles

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

如果忘记了位置要清理的话，可以借助垃圾清理软件

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1][]

注：

博客主页：  
[https://blog.csdn.net/badao\_liumang\_qizhi ][https_blog.csdn.net_badao_liumang_qizhi]  
关注公众号  
霸道的程序猿  
获取编程相关电子书、教程推送与免费下载。

## 实现 ##

通过以上三种方式在导出Eecel时新建sheet和Row和Cell时是一样的，只不过在一开始新建wookbook对象时以及文件扩展名不同。

在实现导出Excel之前首先要进行npoi的dll的引用，NPOI需要引用的dll如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2][]

可以看到除了NPOI开头的引用，还有另外的两个引用，为什么要有这两个引用，我们可以通过其github下载源码

[https://github.com/svn2github/npoi][https_github.com_svn2github_npoi]

然后可以看到其源码中引用了这两个dll，所以这里也需要引用这两个dll

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 3][]

dll下载链接：

[https://download.csdn.net/download/BADAO\_LIUMANG\_QIZHI/12473431][https_download.csdn.net_download_BADAO_LIUMANG_QIZHI_12473431]

### HSSFWorkbook导出Excel ###

新建一个窗体页面然后进入其代码，为例构建导出的数据，首先新建一个对象类DataItem

public class DataItem
        {
    
            public int Age { get; set; }
    
    
            public string Name { get; set; }
    
    
            public string Address { get; set; }
    
            public int Sex { get; set; }
    
        }

然后进入此窗体的代码中，首先构建导出的数据

//数据
            List<DataItem> ItemList = new List<DataItem>()
            {
                new DataItem() {Name = "霸道",Age = 24,Address = "中国",Sex = 1},
                new DataItem() {Name = "流氓",Age = 25,Address = "北京",Sex = 0},
                new DataItem() {Name = "气质",Age = 26,Address = "上海",Sex = 0},
                new DataItem() {Name = "程序猿",Age = 27,Address = "青岛",Sex = 1},
            };

拖拽一个按钮，然后在其点击事件中

private void button2_Click(object sender, EventArgs e)
            {
                //创建HSSFWorkbook对象
                HSSFWorkbook wb = new HSSFWorkbook();
                //创建sheet　并指定sheet的名字
                ISheet sheet1 = wb.CreateSheet("详细数据");
                //新建style对象并设置样式属性
                ICellStyle style1 = wb.CreateCellStyle();//样式
                IFont font1 = wb.CreateFont();//字体
                font1.FontName = "宋体";
                font1.FontHeightInPoints = 11;
                font1.Boldweight = (short)FontBoldWeight.Bold;
                style1.SetFont(font1);//样式里的字体设置具体的字体样式
    
                //创建第一行
                IRow row0 = sheet1.CreateRow(0);
                //创建第一行第一列并设置值
                row0.CreateCell(0).SetCellValue("姓名");
                //获取第一行第一列并设置样式
                row0.GetCell(0).CellStyle = style1;
                row0.CreateCell(1).SetCellValue("年龄");
                row0.GetCell(1).CellStyle = style1;
                row0.CreateCell(2).SetCellValue("地址");
                row0.GetCell(2).CellStyle = style1;
                row0.CreateCell(3).SetCellValue("性别");
                row0.GetCell(3).CellStyle = style1;
    
                //循环添加数据
                foreach (DataItem item in ItemList)
                {
                    int item_index = ItemList.IndexOf(item);
                    //从第二行开始
                    IRow rowi = sheet1.CreateRow(item_index+1);
                    rowi.CreateCell(0).SetCellValue(item.Name);
                    rowi.CreateCell(1).SetCellValue(item.Age);
                    rowi.CreateCell(2).SetCellValue(item.Address);
                    rowi.CreateCell(3).SetCellValue(item.Sex);
                   
                    //设置列宽度，256*字符数，因为单位是1/256个字符
                    sheet1.SetColumnWidth(item_index, 256 * item.Address.Length *4);
                }
                try
                {
                    //将内存中的数据写入磁盘
                    using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "badao.xls"), FileMode.Create))
                    {
                        wb.Write(filestream);
                        filestream.Close();
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex);
                }
                MessageBox.Show("导出完成");
               
            }

运行项目点击按钮，然后会在D盘下生成xls的excel

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 4][]

### XSSFWorkbook导出Excel ###

然后再拖拽一个按钮并设置按钮的点击事件

private void button3_Click(object sender, EventArgs e)
            {
              
                //创建XSSFWorkbook对象
                XSSFWorkbook wb = new XSSFWorkbook();
                //创建sheet　并指定sheet的名字g
                ISheet sheet1 = wb.CreateSheet("详细数据");
                //新建style对象并设置样式属性
                ICellStyle style1 = wb.CreateCellStyle();//样式
                IFont font1 = wb.CreateFont();//字体
                font1.FontName = "宋体";
                font1.FontHeightInPoints = 11;
                font1.Boldweight = (short)FontBoldWeight.Bold;
                style1.SetFont(font1);//样式里的字体设置具体的字体样式
    
                //创建第一行
                IRow row0 = sheet1.CreateRow(0);
                //创建第一行第一列并设置值
                row0.CreateCell(0).SetCellValue("姓名");
                //获取第一行第一列并设置样式
                row0.GetCell(0).CellStyle = style1;
                row0.CreateCell(1).SetCellValue("年龄");
                row0.GetCell(1).CellStyle = style1;
                row0.CreateCell(2).SetCellValue("地址");
                row0.GetCell(2).CellStyle = style1;
                row0.CreateCell(3).SetCellValue("性别");
                row0.GetCell(3).CellStyle = style1;
    
                //循环添加数据
                foreach (DataItem item in ItemList)
                {
                    int item_index = ItemList.IndexOf(item);
                    //从第二行开始
                    IRow rowi = sheet1.CreateRow(item_index + 1);
                    rowi.CreateCell(0).SetCellValue(item.Name);
                    rowi.CreateCell(1).SetCellValue(item.Age);
                    rowi.CreateCell(2).SetCellValue(item.Address);
                    rowi.CreateCell(3).SetCellValue(item.Sex);
    
                    //设置列宽度，256*字符数，因为单位是1/256个字符
                    sheet1.SetColumnWidth(item_index, 256 * item.Address.Length * 4);
                }
                try
                {
                    //将内存中的数据写入磁盘
                    using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "liumang.xlsx"), FileMode.Create))
                    {
                        wb.Write(filestream);
                        filestream.Close();
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex);
                }
                MessageBox.Show("导出完成");
            }

然后运行项目，点击按钮

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 5][]

### SXSSFWorkbook导出Excel ###

同理再拖拽一个按钮，在按钮的点击事件中，不同的是需要提前构建一个大数据量的数据，然后导出时等待一段时间

private void button4_Click(object sender, EventArgs e)
            {
               
                //创建SXSSFWorkbook对象
                SXSSFWorkbook wb = new SXSSFWorkbook();
                //创建sheet　并指定sheet的名字
                ISheet sheet1 = wb.CreateSheet("详细数据");
                //新建style对象并设置样式属性
                ICellStyle style1 = wb.CreateCellStyle();//样式
                IFont font1 = wb.CreateFont();//字体
                font1.FontName = "宋体";
                font1.FontHeightInPoints = 11;
                font1.Boldweight = (short)FontBoldWeight.Bold;
                style1.SetFont(font1);//样式里的字体设置具体的字体样式
    
                //创建第一行
                IRow row0 = sheet1.CreateRow(0);
                //创建第一行第一列并设置值
                row0.CreateCell(0).SetCellValue("姓名");
                //获取第一行第一列并设置样式
                row0.GetCell(0).CellStyle = style1;
                row0.CreateCell(1).SetCellValue("年龄");
                row0.GetCell(1).CellStyle = style1;
                row0.CreateCell(2).SetCellValue("地址");
                row0.GetCell(2).CellStyle = style1;
                row0.CreateCell(3).SetCellValue("性别");
                row0.GetCell(3).CellStyle = style1;
    
                //构建大数据量
                List<DataItem> bigData = new List<DataItem>();
                for (int i = 0; i < 50000;i++ )
                {
                    DataItem data = new DataItem();
                    data.Name = "霸道" + i;
                    data.Age = i;
                    data.Address = "青岛" + i;
                    data.Sex = i;
                    bigData.Add(data);
    
                }
                //循环添加数据
                foreach (DataItem item in bigData)
                {
                    int item_index = bigData.IndexOf(item);
                    //从第二行开始
                    IRow rowi = sheet1.CreateRow(item_index + 1);
                    rowi.CreateCell(0).SetCellValue(item.Name);
                    rowi.CreateCell(1).SetCellValue(item.Age);
                    rowi.CreateCell(2).SetCellValue(item.Address);
                    rowi.CreateCell(3).SetCellValue(item.Sex);
    
                    //设置列宽度，256*字符数，因为单位是1/256个字符
                    sheet1.SetColumnWidth(item_index, 256 * item.Address.Length * 4);
                }
                try
                {
                    //将内存中的数据写入磁盘
                    using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "qizhi.xlsx"), FileMode.Create))
                    {
                        wb.Write(filestream);
                        filestream.Close();
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex);
                }
                MessageBox.Show("导出完成");
            }

### ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 6][] ###

### 代码下载 ###

见下面文章末

[WinformNPOI导出Excel三种(HSSFWorkbook，XSSFWorkbook，SXSSFWorkbook)附代码][WinformNPOI_Excel_HSSFWorkbook_XSSFWorkbook_SXSSFWorkbook]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200529130656715.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200529130701828.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_badao_liumang_qizhi]: https://blog.csdn.net/badao_liumang_qizhi
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200529130709557.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[https_github.com_svn2github_npoi]: https://github.com/svn2github/npoi
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200529130715493.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[https_download.csdn.net_download_BADAO_LIUMANG_QIZHI_12473431]: https://download.csdn.net/download/BADAO_LIUMANG_QIZHI/12473431
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200529130756818.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200529130813453.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200529130840457.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[WinformNPOI_Excel_HSSFWorkbook_XSSFWorkbook_SXSSFWorkbook]: https://mp.weixin.qq.com/s/GX-y9xxcufcMeFzegxySeg