NPOI导出Excel2007-xlsx

2023-01-25 21:23:06

今天在用npoi导出xls时会报错，经过在网上查找资料，找到一篇博客文章介绍的，原文地址
https://www.cnblogs.com/spring_wang/p/3160020.html

1.今天再处理Excel2007、2010文件，格式.xlsx文件存在一个问题,在调用 Write方法之后关闭了传入的文件流。

2.今天针对此问题好一顿的测试：

2.1 在有文件构建时不是.xlsx文件格式会报错，构建不成。.xls文件是不行的。

2.2 XSSFWorkbook对象调用 write方法传去MemoryStream对象后，会自动关闭传入的参数。导致往Response.OutputStream会有问题？

HSSFWorkbook对象则不会,针对这个问题还专门查了HSSFWorkbook源代码下面有，本来想查XSSFWorkbook的源代码，现在还没公开呢。

3.再有导出.xlsx文件时，在打开时总报：

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

那么这些问题如何处理呢？直接上代码如下：

HSSFWorkbook类对象Write方法：

/// <summary>

        /// Write out this workbook to an Outputstream.  Constructs

        /// a new POI POIFSFileSystem, passes in the workbook binary representation  and

        /// Writes it out.

        /// </summary>

        /// <param name="stream">the java OutputStream you wish to Write the XLS to</param>

        public override void Write(Stream stream)

        {

            byte[] bytes = GetBytes();

            POIFSFileSystem fs = new POIFSFileSystem();

            // For tracking what we've written out, used if we're

            //  going to be preserving nodes

            List<string> excepts = new List<string>();

            MemoryStream newMemoryStream = new MemoryStream(bytes);

            // Write out the Workbook stream

            fs.CreateDocument(newMemoryStream, "Workbook");

            // Write out our HPFS properties, if we have them

            WriteProperties(fs, excepts);

            if (preserveNodes)

            {

                // Don't Write out the old Workbook, we'll be doing our new one

                excepts.Add("Workbook");

                // If the file had WORKBOOK instead of Workbook, we'll Write it

                //  out correctly shortly, so don't include the old one

                excepts.Add("WORKBOOK");

                // Copy over all the other nodes to our new poifs

                CopyNodes(this.filesystem, fs, excepts);

            }

            fs.WriteFileSystem(stream);

            fs.Dispose();

            newMemoryStream.Dispose();

            bytes = null;

        }

问题2对应代码：

FileStream fileStream = new FileStream(HttpContext.Current.Server.MapPath("~/Resources/Template/" + strHeaderText  + ".xlsx"), FileMode.Create, FileAccess.Write);

                workbook.Write(fileStream);//调用这个后会关于文件流，在HSSFWorkbook不会关闭所以在处理时应注意

                FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/Resources/Template/" + strHeaderText + ".xlsx"), FileMode.Open, FileAccess.Read);

                long fileSize = fs.Length;

byte[] fileBuffer = new byte[fileSize];

                fs.Read(fileBuffer, , (int)fileSize);

                HttpContext.Current.Response.BinaryWrite(fileBuffer);

                fs.Close();

问题3对应代码：

FileStream fileStream = new FileStream(HttpContext.Current.Server.MapPath("~/Resources/Template/" + strHeaderText  + ".xlsx"), FileMode.Create, FileAccess.Write);

                workbook.Write(fileStream);//调用这个后会关于文件流，在HSSFWorkbook不会关闭所以在处理时应注意

                FileStream fs = new FileStream(HttpContext.Current.Server.MapPath("~/Resources/Template/" + strHeaderText + ".xlsx"), FileMode.Open, FileAccess.Read);

                long fileSize = fs.Length;

　　　　　　　　   //加上设置大小下载下来的.xlsx文件打开时才没有错误
HttpContext.Current.Response.AddHeader("Content-Length", fileSize.ToString());

            byte[] fileBuffer = new byte[fileSize];

                fs.Read(fileBuffer, , (int)fileSize);

                HttpContext.Current.Response.BinaryWrite(fileBuffer);

                fs.Close();

结语：最后在本地正常，放服务器每次下载文件比实际要大，打开提示删除不可见内容在最后加上一句正常

 context.Response.Flush();

码农公寓

相关文章