.net使用NPOI的XSSFWorkbook进行web开发中导出Excel

Wesley13
• 阅读 910

之前也使用过NPOI导出excel,这次是因为在导出的excel里新增了几个列,正好超出了255的限制,所以又要改了。

今天主要出了4个问题:

1. Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV') 

2.c# npoi  XSSFWorkbook无法访问已关闭的流。

3.由于代码已经过优化或者本机框架位于调用堆栈之上,无法计算表达式的值。

4.错误提示: Excel在“春天Excel2007.xlsx”中发现不可读取内容。是否恢复工作簿的内容?如果信任此工作簿的来源,请单击“是”。 单击“是”后:Excel 已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃。

经历了一上午,不断百度,进行了很多次尝试,也经历了几次项目的平衡,所以做一个总结。

之前的导出是这样的,使用hssf方法,代码如下:

1 public static MemoryStream RenderToExcel(DataTable table)
 2 {
 3     MemoryStream ms = new MemoryStream();
 4     using (table)
 5  { 6 IWorkbook workbook = new HSSFWorkbook(); 7 ISheet sheet = workbook.CreateSheet(); 8 IRow headerRow = sheet.CreateRow(0); 9 foreach (DataColumn column in table.Columns) 10  { 11 var headCell = headerRow.CreateCell(column.Ordinal); 12  headCell.SetCellValue(column.Caption); 13  } 14 int rowIndex = 1; 15 foreach (DataRow row in table.Rows) 16  { 17 IRow dataRow = sheet.CreateRow(rowIndex); 18 foreach (DataColumn column in table.Columns) 19  { 20 var cellell = dataRow.CreateCell(column.Ordinal); 21  cellell.SetCellValue(row[column].ToString()); 22  } 23 rowIndex++; 24  } 25  workbook.Write(ms); 26  ms.Flush(); 27 ms.Position = 0; 28  } 29 return ms; 30 } 

这样导出的是“.XLS”格式的excel表格,所以在列数超过256之后就会提示第一个提示错误。上网百度,找了很多,参考https://www.cnblogs.com/jRoger/p/4602239.html一篇文章,说是office2007之后,可以创建的列数大多了,才才用了XSSF的方法。

刚开始直接将hssf换成xssf,结果提示第二个错误,无法访问已关闭的流。再百度,参考https://www.cnblogs.com/caoyc/p/6215210.html文章,说NPOI生产.xlsx文件件时,在使用book.Write(ms);后,会关闭流,这样导致再次使用Respons输出流的时候就出错了。使用自定义流代替内存流,这样就可以保证流不会被自定关闭了。改写代码如下:

1 public class NPOIMemoryStream : MemoryStream
 2 {
 3     /// <summary>
 4     /// 获取流是否关闭
 5     /// </summary>
 6     public bool IsColse
 7     {
 8         get; 9 private set; 10  } 11 public NPOIMemoryStream(bool colse = false) 12  { 13 IsColse = colse; 14  } 15 public override void Close() 16  { 17 if (IsColse) 18  { 19 base.Close(); 20  } 21  } 22 } 23 public static MemoryStream RenderToExcelNew(DataTable table) 24 { 25 NPOIMemoryStream ms = new NPOIMemoryStream(); 26 using (table) 27  { 28 IWorkbook workbook = new XSSFWorkbook(); 29 ISheet sheet = null; 30 sheet =workbook.CreateSheet(); 31 IRow headerRow = sheet.CreateRow(0); 32 foreach (DataColumn column in table.Columns) 33  { 34 var headCell = headerRow.CreateCell(column.Ordinal); 35  headCell.SetCellValue(column.Caption); 36  } 37 int rowIndex = 1; 38 foreach (DataRow row in table.Rows) 39  { 40 IRow dataRow = sheet.CreateRow(rowIndex); 41 foreach (DataColumn column in table.Columns) 42  { 43 var cellell = dataRow.CreateCell(column.Ordinal); 44  cellell.SetCellValue(row[column].ToString()); 45  } 46 rowIndex++; 47  } 48  workbook.Write(ms); 49  ms.Flush(); 50 ms.Position = 0; 51  } 52 return ms; 53 } 

注意上面是新建了一个NPOIMemoryStream类,它继承于MemoryStream。下面的导出方法还是和之前的类似。有兴趣的读者,可以将它们合并成

1 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
2  workbook = new XSSFWorkbook(fs);
3 else if (fileName.IndexOf(".xls") > 0) // 2003版本
4  workbook = new HSSFWorkbook(fs);

这样的形式。
另外在这个过程中,因为项目中引用NPOI的位置有两个,导致两个加载之后的版本不一致,报这样的错误“未能加载文件或程序集“ICSharpCode.SharpZipLib, Version=0.86.0.518, Culture=neutral, PublicKeyToken=1b03e6acf1164f73”或它的某一个依赖项”。于是一不做二不休,将文件全部启用NuGet程序包,来获取NPOI。虽然它的速度特别慢。但是由于之前在项目中新建了文件夹存放4个NPOI的dll,导致有一个项目的引用始终不是NuGet的程序包。当时还一度想放弃了,我把列数降下来不就行啦,费那老鼻子劲干嘛。中午午休之后,想了想,自己还是把这个问题解决吧。于是仔细看了dll引用路径之后,将项目的npoi文件夹删除了,果然引用就正确了。

我的导出是流的形式,生成之后导出的代码是:

1 using (MemoryStream ms = RenderToExcelNew(table))
 2 {
 3     RenderToBrowser(ms, context, fileName);
 4 }
 5 public static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName) 6 { 7  context.Response.Clear(); 8 context.Response.ContentEncoding = Encoding.GetEncoding("GB2312"); 9 context.Response.ContentType = "application/octet-stream"; 10 context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName); 11  context.Response.BinaryWrite(ms.ToArray()); 12  context.Response.Flush(); 13  context.Response.End(); 14 }

这样就出现了第3个问题,“由于代码已经过优化或者本机框架位于调用堆栈之上,无法计算表达式的值。”,异常报错在最后一行,Response.End() 由于代码已经过优化或者本机框架位于调用堆栈之上,无法计算表达式的值。再百度吧,看到https://www.cnblogs.com/Alben-wang/p/6096568.html,于是将Response.End,改成调用HttpContext.Current.ApplicationInstance.CompleteRequest方法。于是最后一行为context.ApplicationInstance.CompleteRequest();。

但还是有问题,运行了一遍程序之后,就爆出了第4个错误。文件导出之后,提示:Excel在“xxxxxx.xlsx”中发现不可读取内容。是否恢复工作簿的内容?如果信任此工作簿的来源,请单击“是”。 单击“是”后:Excel 已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃。再百度吧,看到了https://www.cnblogs.com/qk2014/p/7729215.html,这篇文章,介绍的很好。加上设置大小下载下来的.xlsx文件打开时才不会报“Excel 已完成文件级验证和修复。此工作簿的某些部分可能已被修复或丢弃”

long fileSize = ms.Length;
context.Response.AddHeader("Content-Length", fileSize.ToString());
即可。

于是最后的代码就变成了:

1 public static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
 2 {
 3     context.Response.Clear();
 4     context.Response.ContentEncoding = Encoding.GetEncoding("GB2312");
 5     context.Response.ContentType = "application/octet-stream"; 6 context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName); 7 long fileSize = ms.Length; 8 context.Response.AddHeader("Content-Length", fileSize.ToString()); 9  context.Response.BinaryWrite(ms.ToArray()); 10  context.Response.Flush(); 11  context.ApplicationInstance.CompleteRequest(); 12 }

注意两段代码的第6行,略微有点不同。

至此忙活了一上午和中午,就得到了这样的成就,还是有很多不懂的地方啊。

看到网上有很多是导出成datatable形式的,也留几个链接参考:https://www.cnblogs.com/lazyneal/p/6148912.html,

https://www.cnblogs.com/luxiaoxun/p/3374992.html,https://www.cnblogs.com/a14907/p/6423733.html

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
PHP导入导出EXCELl,CSV
PHP导入导出Excel,CSVHTML<formaction"{:U('Admin/Unit/importcsv')}"method"post"name"myform"id"myform"enctype"multipart/formdata"<input
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这