asp.net将百万级数据导出到xlsx文件&大文件输出解决方案

因Aspose.Cells Workbook  Response.BinaryWrite(workbook.SaveToStream().ToArray()); 输出流输出sheet页最大行65536行；可改为先存储文件，再以url连接下载或文件流的方式输出:

参考代码:其中dt拥有100万数据量

  DataTable dt = cms_bl.EOL_Regist.ExportALLEOL_list("");
            if (dt != null)
            {
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
                Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];
                Aspose.Cells.Cells cells = sheet.Cells;

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    cells[0, i].PutValue(dt.Columns[i].ColumnName);
                }

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cells[i + 1, j].PutValue(dt.Rows[i][j].ToString());
                    }
                }
                string path = Server.MapPath("");
                string targetPath = path + "\\tempfile";
                if (!Directory.Exists(targetPath))
                {
                    Directory.CreateDirectory(targetPath);
                }
                string fileName = DateTime.Now.ToString("yyyyMMddHHmmssffff")+".xlsx";
                string targetFilePath = targetPath + "\\" + fileName;
                workbook.Save(targetFilePath, SaveFormat.Xlsx);
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "utf-8";
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";    
                FileStream fs = new FileStream(targetFilePath, FileMode.Open);
                byte[] buffer = new byte[fs.Length];
                fs.Position = 0;
                fs.Read(buffer, 0, buffer.Length);
                Response.BinaryWrite(buffer);
                fs.Close();
                File.Delete(targetFilePath);
            }

另外是针对FileStream 和MemoryStream的认识:

1.两者都是字节序列的抽象,一个为内存流,一个为文件流,通过两者均可以在服务器向web客户端输出文件;

2.Response.BinaryWrite()用于输出二进制字节,因此其具有一般通用性。

3.FileStream 和MemoryStream通过Response.BinaryWrite()输出字节流时,MemorySteam可以直接通过MemorySteam.ToArray()得到字节数组 byte[] ,但是FileStream不行,需要经过FileStream.Read()获得byte[]；

4.通过FileStream输出时如果需要及时删除文件需要将文件流关闭才能成功删除文件.

*****************************************************************

2021-07-30更新

以上虽然数据百万级别,但输出excel并不大,我们发散一下,如果要输出100G级别大小的文件要怎么实现呢?

很显然,采用memorystream是不可行的,将文件流一次性读到一个byte[]类型的变量也是不现实的,因为主机内存无法支撑,那么此时我们只能利用文件源流分批读取byte[],Response.OutputStream输出流分批传输byte[]

        //targetFilePath路径文件100G级别      
        FileStream ms = new FileStream(targetFilePath, FileMode.Open);        
        ms.Position = 0;
        byte[] buffer = new byte[4096];
        int ii = 0;
        long length = 0;
        while (length < ms.Length)
        {
            ii = ms.Read(buffer, 0, 4096);
            Response.OutputStream.Write(buffer, 0, ii);
            length += ii;
        };
        ms.Dispose();
        Response.Flush();
        Response.End();