asp.net mvc 利用NPOI导出Excel2007，报错：System.ObjectDisposedException: 无法访问已关闭的流
0
悬赏园豆：30
[已解决问题]
解决于 2014-09-12 11:04
在Asp.net mvc4平台，将数据导出到Excel2007,代码如下：
1 public ActionResult ExportData() 2 { 3 //新建一个Excel文件 4 IWorkbook workbook = new XSSFWorkbook(); 5 //创建一个Excel的Sheet 6 ISheet sheet = workbook.CreateSheet(); 7 8 MemoryStream ms = new MemoryStream(); 9 10 //创建一列，并赋值 11 // headerRow.CreateCell(0).SetCellValue("测试列"); 12 13 14 for (int i = 0; i <= 15; i++) 15 { 16 IRow row = sheet.CreateRow(i); 17 for (int j = 0; j < 15; j++) 18 { 19 row.CreateCell(j).SetCellValue(i + "行" + j + "列"); 20 } 21 } 22 23 workbook.Write(ms); 24 25 ms.Flush(); 26 ms.Position = 0; 27 28 sheet = null; 29 workbook = null; 30 31 return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "测试文件.xlsx"); 32 }
错误信息：
“/”应用程序中的服务器错误。
无法访问已关闭的流。
说明: 执行当前 Web 请求期间，出现未经处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。
异常详细信息: System.ObjectDisposedException: 无法访问已关闭的流。
源错误:
行 61:
行 62: ms.Flush();
行 63: ms.Position = 0;
行 64:
行 65: sheet = null;
|
源文件: f:\Desktop\ExportExcelTest\ExportExcelTest\Controllers\HomeController.cs 行: 63
堆栈跟踪:
[ObjectDisposedException: 无法访问已关闭的流。] System.IO.__Error.StreamIsClosed() +57 System.IO.MemoryStream.set_Position(Int64 value) +10908800 ExportExcelTest.Controllers.HomeController.ExportData() in f:\Desktop\ExportExcelTest\ExportExcelTest\Controllers\HomeController.cs:63 lambda_method(Closure , ControllerBase , Object[] ) +62 System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters) +211 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters) +27 System.Web.Mvc.Async.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() +28 System.Web.Mvc.Async.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _) +10 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +48 System.Web.Mvc.Async.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33() +57 System.Web.Mvc.Async.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49() +223 System.Web.Mvc.Async.<>c__DisplayClass37.<BeginInvokeActionMethodWithFilters>b__36(IAsyncResult asyncResult) +10 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +48 System.Web.Mvc.Async.<>c__DisplayClass2a.<BeginInvokeAction>b__20() +24 System.Web.Mvc.Async.<>c__DisplayClass25.<BeginInvokeAction>b__22(IAsyncResult asyncResult) +102 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +57 System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +43 System.Web.Mvc.<>c__DisplayClass1d.<BeginExecuteCore>b__18(IAsyncResult asyncResult) +14 System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62 System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +57 System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62 System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +47 System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10 System.Web.Mvc.<>c__DisplayClass8.<BeginProcessRequest>b__3(IAsyncResult asyncResult) +25 System.Web.Mvc.Async.<>c__DisplayClass4.<MakeVoidDelegate>b__3(IAsyncResult ar) +23 System.Web.Mvc.Async.WrappedAsyncResult`1.End() +62 System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +47 System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9514928 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +155
|
跪求高手帮忙！
最佳答案
0
28,29行注销一下试试
收获园豆：30
试过的，没有用
@green66: ms 已在Write内部被关闭了, 除非你修改NPIO原码 , 可以试试用 EPPlus
@green66:
只能这样
FileStream fs = new FileStream(@"d:\测试文件.xlsx", FileMode.Create);
workbook.Write(fs);
return File(@"d:\测试文件.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "测试文件.xlsx");
@Yu: 谢谢！这样可以实现！就是先将文件写到服务器，再将文件导出哈
其他回答(2)
0
是不是内存流不让ms.Flush()
没用过这种高科技
不是滴
0
直接用ExcelResult这种方式实现导出吧。
这个ExcelResult是自己写的吧？
@green66: 我的错，没给你代码。
using System; using System.Collections.Generic; using System.Reflection; using System.Web; using System.Web.Mvc; using org.in2bits.MyXls; namespace TXManager.Controllers { public class ExcelResult<T> : ActionResult where T : OutputExcel { public ExcelResult(IList<T> entity, string fileName, string wSheet) { this.Entity = entity; DateTime time = DateTime.Now; this.FileName = string.Format("{0}_{1}_{2}_{3}_{4}", fileName, time.Month, time.Day, time.Hour, time.Minute); this.WSheet = wSheet; } public ExcelResult(IList<T> entity, string fileName, string[] wSheetLs) { this.Entity = entity; DateTime time = DateTime.Now; this.FileName = string.Format("{0}_{1}_{2}_{3}_{4}", fileName, time.Month, time.Day, time.Hour, time.Minute); this.WSheetLs = wSheetLs; } public ExcelResult(IList<T> entity) { this.Entity = entity; DateTime time = DateTime.Now; this.FileName = string.Format("{0}_{1}_{2}_{3}_{4}", this.FileName, time.Month, time.Day, time.Hour, time.Minute); } public IList<T> Entity { get; set; } public string FileName { get; set; } /// <summary> /// 需要生成EXCEL模板 WorkSheet1:约看中 WorkSheet2： /// </summary> public string WSheet { get; set; } public string[] WSheetLs { get; set; } public override void ExecuteResult(ControllerContext context) { if (Entity == null) { new EmptyResult().ExecuteResult(context); return; } SetResponse(context); } /// <summary> /// 设置并向客户端发送请求响应。 /// </summary> /// <param name="context"></param> private void SetResponse(ControllerContext context) { XlsDocument xls = new XlsDocument(); //创建空xls文档 //xls.FileName =FileName; xls.FileName =HttpUtility.UrlEncode(FileName); Worksheet sheet = xls.Workbook.Worksheets.AddNamed(this.FileName); SetCellWidth(xls, sheet); //自适应宽度 //设置文档列属性 //ColumnInfo cinfo = new ColumnInfo(xls, sheet);//设置xls文档的指定工作页的列属性 //cinfo.Collapsed = true; //设置列的范围 如 0列-10列 //cinfo.ColumnIndexStart = 0;//列开始 //cinfo.ColumnIndexEnd = 16;//列结束 //cinfo.Collapsed = true; //cinfo.Width = 85 * 65; //cinfo.Width = 58 * 100;//列宽度 //sheet.AddColumnInfo(cinfo); //设置文档列属性结束 ConvertEntity(sheet); xls.Send(); } /// <summary> /// 把泛型集合转换成组合Excel表格的字符串。 /// </summary> /// <returns></returns> private void ConvertEntity(Worksheet sheet) { Cells cells = sheet.Cells; //获得指定工作页列集合 if (!string.IsNullOrEmpty(this.WSheet)) { AddCells(cells); } else if (this.WSheetLs != null) { AddCells_ByLs(cells); } AddTableBody(cells); } public void AddCells_ByLs(Cells cell) { int temp = 0; foreach (string s in this.WSheetLs) { temp++; cell.Add(1, temp, s); } } public void AddCells(Cells cell) { switch (this.WSheet) { case "WorkSheet1": cell.Add(1, 1, "房源编号"); cell.Add(1, 2, "小区名"); cell.Add(1, 3, "所在城市"); cell.Add(1, 4, "详细地址"); cell.Add(1, 5, "支付方式"); cell.Add(1, 6, "房东报价"); cell.Add(1, 7, "出价时间"); cell.Add(1, 8, "真实姓名"); cell.Add(1, 9, "手机号码"); cell.Add(1, 10, "出价次数"); cell.Add(1, 11, "出价金额"); cell.Add(1, 12, ""); cell.Add(1, 13, ""); cell.Add(1, 14, ""); break; case "WorkSheet2": cell.Add(1, 1, ""); cell.Add(1, 2, ""); cell.Add(1, 3, ""); cell.Add(1, 4, ""); cell.Add(1, 5, ""); cell.Add(1, 6, ""); cell.Add(1, 7, ""); cell.Add(1, 8, ""); cell.Add(1, 9, ""); cell.Add(1, 10, ""); cell.Add(1, 11, ""); cell.Add(1, 12, ""); cell.Add(1, 13, ""); cell.Add(1, 14, ""); break; default: break; } } /// <summary> /// 根据IList泛型集合中的每项的属性值来组合Excel表格。 /// </summary> /// <param name="sb"></param> //private void AddTableBody(Cells cells) //{ // if (Entity == null || Entity.Count <= 0) // { // return; // } // int tmepCell = 1; // foreach (var item in Entity) // { // tmepCell++; // cells.Add(tmepCell, 1, item.CellText1); // cells.Add(tmepCell, 2, item.CellText2); // cells.Add(tmepCell, 3, item.CellText3); // cells.Add(tmepCell, 4, item.CellText4); // cells.Add(tmepCell, 5, item.CellText5); // cells.Add(tmepCell, 6, item.CellText6); // cells.Add(tmepCell, 7, item.CellText7); // cells.Add(tmepCell, 8, item.CellText8); // cells.Add(tmepCell, 9, item.CellText9); // cells.Add(tmepCell, 10, item.CellText10); // cells.Add(tmepCell, 11, item.CellText11); // cells.Add(tmepCell, 12, item.CellText12); // cells.Add(tmepCell, 13, item.CellText13); // cells.Add(tmepCell, 14, item.CellText14); // } //} #region 遍历数据 /// <summary> /// 根据IList泛型集合中的每项的属性值来组合Excel表格。 /// author:huhangfei /// </summary> /// <param name="cells"></param> private void AddTableBody(Cells cells) { if (Entity == null || Entity.Count <= 0) { return; } int tmepCell = 1; foreach (T s in Entity) { tmepCell++; int tempColu = 0; foreach (PropertyInfo pi in typeof (T).GetProperties()) { object value = null; if (s != null) { tempColu++; value = pi.GetValue(s, null); cells.Add(tmepCell, tempColu, value); } } } } #endregion #region 设置列宽自适应 /// <summary> /// 设置列宽自适应 /// author:huhangfei /// </summary> /// <param name="xls"></param> /// <param name="sheet"></param> public void SetCellWidth(XlsDocument xls, Worksheet sheet) { if (Entity == null || Entity.Count <= 0) { return; } int tmepCell = 0; //列 foreach (PropertyInfo pi in typeof (T).GetProperties()) { if (tmepCell + 1 > WSheetLs.Length) { break; } ushort InitLenth = (ushort) System.Text.Encoding.Default.GetByteCount(WSheetLs[tmepCell]); foreach (T s in Entity) { if (pi.GetValue(s, null) != null) { ushort l = (ushort) System.Text.Encoding.Default.GetByteCount(pi.GetValue(s, null).ToString()); InitLenth = InitLenth > l ? InitLenth : l; } } //设置文档列属性 ColumnInfo cinfo = new ColumnInfo(xls, sheet); //设置xls文档的指定工作页的列属性 cinfo.Collapsed = true; //设置列的范围 如 0列-10列 cinfo.ColumnIndexStart = (ushort) (tmepCell); //列开始 cinfo.ColumnIndexEnd = (ushort) (tmepCell); //列结束 cinfo.Collapsed = true; //cinfo.Width = 85 * 65; cinfo.Width = (ushort) ((InitLenth*1.2)*256); //列的宽度计量单位为 1/256 字符宽 sheet.AddColumnInfo(cinfo); tmepCell++; } return; } #endregion } }
@Alex_QY1987: WOW,非常感谢！
