c# - 将大型数据查询(60k+ 行)导出到 Excel

Question

我创建了一个报告工具作为内部网络应用程序的一部分。报告将所有结果显示在一个 GridView 中，我使用 JavaScript 将 GridView 的内容逐行读取到 Excel 对象中。 JavaScript 继续在不同的工作表上创建数据透视表。

遗憾的是没想到GridView的大小如果返回多了几天就会导致浏览器过载的问题。该应用程序每天有几千条记录，比如说每月 60k，理想情况下我希望能够返回长达一年的所有结果。行数导致浏览器挂起或崩溃。

我们在带有 SQL Server 的 Visual Studio 2010 上使用 ASP.NET 3.5，预期的浏览器是 IE8。该报告包含一个 GridView ，该 View 根据用户选择的总体从少数存储过程中的一个获取数据。 gridview 在 UpdatePanel 中:

<asp:UpdatePanel ID="update_ResultSet" runat="server">
<Triggers>
    <asp:AsyncPostBackTrigger ControlID="btn_Submit" />
</Triggers>
<ContentTemplate>
<asp:Panel ID="pnl_ResultSet" runat="server" Visible="False">
    <div runat="server" id="div_ResultSummary">
        <p>This Summary Section is Automatically Completed from Code-Behind</p>
    </div>
        <asp:GridView ID="gv_Results" runat="server" 
            HeaderStyle-BackColor="LightSkyBlue" 
            AlternatingRowStyle-BackColor="LightCyan"  
            Width="100%">
        </asp:GridView>
    </div>
</asp:Panel>
</ContentTemplate>
</asp:UpdatePanel>

我对我的团队还比较陌生，所以我遵循了他们将存储过程返回到 DataTable 并将其用作后面代码中的 DataSource 的典型做法:

    List<USP_Report_AreaResult> areaResults = new List<USP_Report_AreaResult>();
    areaResults = db.USP_Report_Area(ddl_Line.Text, ddl_Unit.Text, ddl_Status.Text, ddl_Type.Text, ddl_Subject.Text, minDate, maxDate).ToList();
    dtResults = Common.LINQToDataTable(areaResults);

    if (dtResults.Rows.Count > 0)
    {
        PopulateSummary(ref dtResults);
        gv_Results.DataSource = dtResults;
        gv_Results.DataBind();

(我知道你在想什么!但是，是的，从那以后我学到了更多关于参数化的知识。)

LINQToDataTable 函数没有什么特别之处，只是将列表转换为数据表。

对于几千条记录(最多几天)，这可以正常工作。 GridView 显示结果，并且有一个按钮供用户单击以启动 JScript 导出器。外部 JavaScript 函数将每一行读入 Excel 工作表，然后使用它来创建数据透视表。数据透视表很重要!

function exportToExcel(sMyGridViewName, sTitleOfReport, sHiddenCols) {
//sMyGridViewName = the name of the grid view, supplied as a text
//sTitleOfReport = Will be used as the page header if the spreadsheet is printed
//sHiddenCols = The columns you want hidden when sent to Excel, separated by semicolon (i.e. 1;3;5).
//              Supply an empty string if all columns are visible.

var oMyGridView = document.getElementById(sMyGridViewName);

//If no data is on the GridView, display alert.
if (oMyGridView == null)
    alert('No data for report');
else {
    var oHid = sHiddenCols.split(";");  //Contains an array of columns to hide, based on the sHiddenCols function parameter
    var oExcel = new ActiveXObject("Excel.Application");
    var oBook = oExcel.Workbooks.Add;
    var oSheet = oBook.Worksheets(1);
    var iRow = 0;
    for (var y = 0; y < oMyGridView.rows.length; y++)
    //Export all non-hidden rows of the HTML table to excel.
    {
        if (oMyGridView.rows[y].style.display == '') {
            var iCol = 0;
            for (var x = 0; x < oMyGridView.rows(y).cells.length; x++) {
                var bHid = false;
                for (iHidCol = 0; iHidCol < oHid.length; iHidCol++) {
                    if (oHid[iHidCol].length !=0 && oHid[iHidCol] == x) {
                        bHid = true;
                        break; 
                    } 
                }
                if (!bHid) {
                    oSheet.Cells(iRow + 1, iCol + 1) = oMyGridView.rows(y).cells(x).innerText;
                    iCol++;
                }
            }
            iRow++;
        }
    }

我正在尝试做的事情:创建一个可以处理此数据并将其处理到 Excel 中的解决方案(可能是客户端)。有人可能会建议使用 HtmlTextWriter ，但是 afaik 不允许自动生成数据透视表并创建令人讨厌的弹出警告....

我尝试过的:

• 填充一个 JSON 对象——我仍然认为它有潜力，但我还没有找到让它发挥作用的方法。
• 使用 SQLDataSource——我似乎无法使用它来取回任何数据。
• 对页面进行分页和循环——混合进度。虽然通常很难看，但我仍然有一个问题，即为显示的每个页面查询并返回整个数据集。

更新:我仍然对替代解决方案持开放态度，但我一直在追求 JSON 理论。我有一个有效的服务器端方法，可以从 DataTable 生成 JSON 对象。我不知道如何将该 JSON 传递到(外部)exportToExcel JavaScript 函数....

    protected static string ConstructReportJSON(ref DataTable dtResults)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("var sJSON = [");
        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);
        sb.Append("];");
        return sb.ToString();
    }

谁能举例说明如何将这个 JSON 对象携带到外部 JS 函数中？或用于导出到 Excel 的任何其他解决方案。

Answer 1

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

1. 如果您还没有安装 Microsoft Open SDK(谷歌“下载 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 并在for each 中使用它。您真的不想依赖同时将所有数据存储在内存中，否则您会遇到内存不足和/或内存利用率高的问题。