首页 新闻 赞助 找找看

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

 

跪求高手帮忙!

CaptainTina的主页 CaptainTina | 初学一级 | 园豆:3
提问于:2014-07-09 17:52
< >
分享
最佳答案
0

28,29行注销一下试试

 

收获园豆:30
Yu | 专家六级 |园豆:12980 | 2014-07-09 20:38

试过的,没有用

CaptainTina | 园豆:3 (初学一级) | 2014-07-10 19:16

@green66:  ms 已在Write内部被关闭了, 除非你修改NPIO原码 , 可以试试用 EPPlus

Yu | 园豆:12980 (专家六级) | 2014-07-11 08:37

@green66: 

只能这样

FileStream fs = new FileStream(@"d:\测试文件.xlsx", FileMode.Create);
workbook.Write(fs);
return File(@"d:\测试文件.xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "测试文件.xlsx");

Yu | 园豆:12980 (专家六级) | 2014-07-11 09:17

@Yu: 谢谢!这样可以实现!就是先将文件写到服务器,再将文件导出哈

CaptainTina | 园豆:3 (初学一级) | 2014-07-11 14:51
其他回答(2)
0

是不是内存流不让ms.Flush()

没用过这种高科技

吴瑞祥 | 园豆:29449 (高人七级) | 2014-07-09 19:44

不是滴

支持(0) 反对(0) CaptainTina | 园豆:3 (初学一级) | 2014-07-15 15:36
0

 

直接用ExcelResult这种方式实现导出吧。

Alex_QY1987 | 园豆:1888 (小虾三级) | 2014-07-10 15:54

这个ExcelResult是自己写的吧?

支持(0) 反对(0) CaptainTina | 园豆:3 (初学一级) | 2014-07-11 14:51

@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

    }
}
支持(0) 反对(0) Alex_QY1987 | 园豆:1888 (小虾三级) | 2014-07-11 15:08

@Alex_QY1987: WOW,非常感谢!

支持(0) 反对(0) CaptainTina | 园豆:3 (初学一级) | 2014-07-15 15:37
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册