将大型数据查询(60k+ 行）导出到 Excel

编写 CSV 文件既简单又高效。 但是，如果您需要Excel，也可以以相当有效的方式完成，可以使用Microsoft Open XML SDK的Open XML Writer处理60,000+行。

安装Microsoft Open SDK，
1. 如果你还没有它（谷歌"下载Microsoft Open XML SDK"）
2. 创建控制台应用
3. 添加对 DocumentFormat.OpenXml 的引用
4. 添加对 WindowsBase 的引用
5. 尝试运行一些测试代码，如下所示（需要一些使用代码）

只需在 http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/查看Vincent Tan的解决方案（下面，我稍微清理了他的示例以帮助新用户。

在我自己的使用中，我发现常规数据非常简单，但我确实必须从我的真实数据中删除"''0"字符。

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

。

        using (var workbook = SpreadsheetDocument.Create("SomeLargeFile.xlsx", SpreadsheetDocumentType.Workbook))
        {
            List<OpenXmlAttribute> attributeList;
            OpenXmlWriter writer;
            workbook.AddWorkbookPart();
            WorksheetPart workSheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
            writer = OpenXmlWriter.Create(workSheetPart);
            writer.WriteStartElement(new Worksheet());
            writer.WriteStartElement(new SheetData());
            for (int i = 1; i <= 50000; ++i)
            {
                attributeList = new List<OpenXmlAttribute>();
                // this is the row index
                attributeList.Add(new OpenXmlAttribute("r", null, i.ToString()));
                writer.WriteStartElement(new Row(), attributeList);
                for (int j = 1; j <= 100; ++j)
                {
                    attributeList = new List<OpenXmlAttribute>();
                    // this is the data type ("t"), with CellValues.String ("str")
                    attributeList.Add(new OpenXmlAttribute("t", null, "str"));
                    // it's suggested you also have the cell reference, but
                    // you'll have to calculate the correct cell reference yourself.
                    // Here's an example:
                    //attributeList.Add(new OpenXmlAttribute("r", null, "A1"));
                    writer.WriteStartElement(new Cell(), attributeList);
                    writer.WriteElement(new CellValue(string.Format("R{0}C{1}", i, j)));
                    // this is for Cell
                    writer.WriteEndElement();
                }
                // this is for Row
                writer.WriteEndElement();
            }
            // this is for SheetData
            writer.WriteEndElement();
            // this is for Worksheet
            writer.WriteEndElement();
            writer.Close();
            writer = OpenXmlWriter.Create(workbook.WorkbookPart);
            writer.WriteStartElement(new Workbook());
            writer.WriteStartElement(new Sheets());
            // you can use object initialisers like this only when the properties
            // are actual properties. SDK classes sometimes have property-like properties
            // but are actually classes. For example, the Cell class has the CellValue
            // "property" but is actually a child class internally.
            // If the properties correspond to actual XML attributes, then you're fine.
            writer.WriteElement(new Sheet()
            {
                Name = "Sheet1",
                SheetId = 1,
                Id = workbook.WorkbookPart.GetIdOfPart(workSheetPart)
            });
            writer.WriteEndElement(); // Write end for WorkSheet Element
            writer.WriteEndElement(); // Write end for WorkBook Element
            writer.Close();
            workbook.Close();
        }

如果查看该代码，您会注意到两个主要的写入，首先是工作表，然后是包含工作表的工作簿。 工作簿部分是末尾的无聊部分，较早的工作表部分包含所有行和列。

在您自己的改编中，您可以从自己的数据将实际字符串值写入单元格。 相反，在上面，我们只是使用行和列编号。

writer.WriteElement(new CellValue("SomeValue"));

值得注意的是，Excel中的行号从1而不是0开始。 从索引零开始编号的行将导致"文件损坏"错误消息。

最后，如果您正在处理非常大的数据集，切勿调用 ToList（）。 使用数据读取器样式的数据流方法。 例如，您可以拥有一个 IQueryable 并在每个人中使用它。 您永远不想真正依赖同时将所有数据放在内存中，否则您将遇到内存不足限制和/或高内存利用率。

对于要流式传输的大文件，您可能希望尝试提供输出流而不是文件名;但是，根据此答案 OpenXml 和 HttpResponse.OutputStream OpenXML 库需要能够追溯到开头，因此在托管文件之前保存到文件似乎是大量数据的唯一选择。

我会尝试使用displaytag来显示结果。您可以设置它每页显示一定的数字，这应该可以解决您的过载问题。然后，您可以设置显示标记以允许 Excel 导出。

我们通常使用"导出"命令按钮来处理这个问题，该按钮连接到服务器端方法以获取数据集并将其转换为CSV。然后我们调整响应标头，浏览器会将其视为下载。我知道这是一个服务器端解决方案，但您可能需要考虑它，因为在实现服务器端记录分页之前，您将继续遇到超时和浏览器问题。

自从我开始这个问题以来将近一个半星期，我终于设法在某种程度上让它正常工作。 我将暂时等待标记答案，看看是否有其他人有更有效，更好的"最佳实践"方法。

通过生成一个JSON字符串，我已经将JavaScript与GridView分开了。JSON 是在填充数据时在代码隐藏中生成的：

    protected static string ConstructReportJSON(ref DataTable dtResults)
    {
        StringBuilder sb = new StringBuilder();
        for (int r = 0; r < dtResults.Rows.Count; r++)
        {
            sb.Append("{");
            for (int c = 0; c < dtResults.Columns.Count; c++)
            {
                sb.AppendFormat("'"{0}'":'"{1}'",", dtResults.Columns[c].ColumnName, dtResults.Rows[r][c].ToString());
            }
            sb.Remove(sb.Length - 1, 1); //Truncate the trailing comma
            sb.Append("},");
        }
        sb.Remove(sb.Length - 1, 1);
        return String.Format("[{0}]", sb.ToString());
    }

返回一串数据，例如

[ {"呼叫者"："John Doe"， "Office"："5555"，"Type"："Incoming"等}，

{"呼叫者"："Jane Doe"， "办公室"："7777"， "类型"："Outgoing"等}， {etc} ]

我通过使用以下命令将文本分配给 UpdatePanel 中的文字来隐藏此字符串：

    <div id="div_JSON" style="display: none;">
            <asp:Literal id="lit_JSON" runat="server" /> 
    </div>

JavaScript 通过读取div 的内容来解析该输出：

function exportToExcel_Pivot(sMyJSON, sTitleOfReport, sReportPop) {
     //sMyJSON = the name, supplied as a text, of the hidden element that houses the JSON array.
     //sTitleOfReport = Will be used as the page header if the spreadsheet is printed.
     //sReportPop = Determines which business logic to create a pivot table for.
var sJSON = document.getElementById(sMyJSON).innerHTML;
var oJSON = eval("(" + sJSON + ")");
 //    DEBUG Example Test Code
 //    for (x = 0; x < oJSON.length; x++) {
 //        for (y in oJSON[x])
 //            alert(oJSON[x][y]); //DEBUG, returns field value
 //            alert(y); //DEBUG, returns column name
 //    }

//If no data is in the JSON object array, display alert.
if (oJSON == null)
    alert('No data for report');
else {
    var oExcel = new ActiveXObject("Excel.Application");
    var oBook = oExcel.Workbooks.Add;
    var oSheet = oBook.Worksheets(1);
    var oSheet2 = oBook.Worksheets(2);
    var iRow = 0;
    var iCol = 0;
        //Take the column names of the JSON object and prepare them in Excel
        for (header in oJSON[0])
        {
            oSheet.Cells(iRow + 1, iCol + 1) = header;
            iCol++;
        }
        iRow++;
        //Export all rows of the JSON object to excel
        for (var r = 0; r < oJSON.length; r++)
        {
            iCol = 0;
            for (c in oJSON[r]) 
                    {
                        oSheet.Cells(iRow + 1, iCol + 1) = oJSON[r][c];
                        iCol++;
                    } //End column loop
            iRow++;
        } //End row

字符串输出和JavaScript 'eval'解析都非常快，但遍历JSON对象比我想要的要慢一些。

我相信这种方法将限制在大约 10 亿个字符的数据 - 也许更少，具体取决于内存测试的工作方式。（我计算过，我每天最多可以查看 100 万个字符，所以在报告后的一年内应该没问题。