将大数据查询(60k+ 行)导出到 Excel [英] Export a large data query (60k+ rows) to Excel

问题描述

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

不幸的是，我没想到如果返回超过几天，GridView 的大小会导致浏览器过载问题.该应用程序每天有几千条记录，假设每月有 6 万条记录，理想情况下我希望能够返回长达一年的所有结果.行数导致浏览器挂起或崩溃.

我们在带有 SQL Server 的 Visual Studio 2010 上使用 ASP.NET 3.5，预期浏览器是 IE8.该报告由一个 gridview 组成，它根据用户选择的人群从少数存储过程中的一个获取数据.网格视图位于 UpdatePanel 中:

<触发器><asp:AsyncPostBackTrigger ControlID="btn_Submit"/></触发器><内容模板><asp:Panel ID="pnl_ResultSet" runat="server" Visible="False"><div runat="server" id="div_ResultSummary"><p>此摘要部分是从代码隐藏自动完成的</p>

<asp:GridView ID="gv_Results" runat="server"HeaderStyle-BackColor="LightSkyBlue"AlternatingRowStyle-BackColor="LightCyan"宽度="100%"></asp:GridView>

</asp:面板></内容模板></asp:UpdatePanel>

我对我的团队来说相对较新，所以我遵循了他们的典型做法，将 sproc 返回到 DataTable 并将其用作后面代码中的 DataSource:

 ListareaResults = new List();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);如果(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 = 网格视图的名称，以文本形式提供//sTitleOfReport = 如果打印电子表格，将用作页眉//sHiddenCols = 发送到 Excel 时要隐藏的列，以分号分隔(即 1;3;5).//如果所有列都可见，则提供一个空字符串.var oMyGridView = document.getElementById(sMyGridViewName);//如果GridView上没有数据，则显示警报.如果(oMyGridView == null)alert('没有报告数据');别的 {var oHid = sHiddenCols.split(";");//包含要隐藏的列数组，基于sHiddenCols函数参数var oExcel = new ActiveXObject("Excel.Application");var oBook = oExcel.Workbooks.Add;var oSheet = oBook.Worksheets(1);变量 iRow = 0;for (var y = 0; y < oMyGridView.rows.length; y++)//将HTML表格的所有非隐藏行导出到excel.{if (oMyGridView.rows[y].style.display == '') {变量 iCol = 0;for (var x = 0; x 

我想要做的: 创建一个解决方案(可能是客户端)，可以处理这些数据并将其处理为 Excel.有人可能会建议使用 HtmlTextWriter，但不允许自动生成数据透视表并创建令人讨厌的弹出警告....

我尝试过的:

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

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

 受保护的静态字符串 ConstructReportJSON(ref DataTable dtResults){StringBuilder sb = new StringBuilder();sb.Append("var sJSON = [");for (int r = 0; r 

任何人都可以展示如何将此 JSON 对象携带到外部 JS 函数中的示例吗?或任何其他导出到 Excel 的解决方案.

解决方案

编写 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/(下面，我清理了他的例子对新用户略有帮助.)

在我自己的使用中，我发现这对于常规数据非常简单，但我确实必须从我的真实数据中去除"字符.

使用 DocumentFormat.OpenXml;使用 DocumentFormat.OpenXml.Packaging;使用 DocumentFormat.OpenXml.Spreadsheet；

...

 using (var workbook = SpreadsheetDocument.Create("SomeLargeFile.xlsx", SpreadsheetDocumentType.Workbook)){列表属性列表；OpenXmlWriter 编写器；workbook.AddWorkbookPart();WorksheetPart workSheetPart = workbook.WorkbookPart.AddNewPart();writer = OpenXmlWriter.Create(workSheetPart);writer.WriteStartElement(new Worksheet());writer.WriteStartElement(new SheetData());for (int i = 1; i <= 50000; ++i){attributeList = new List();//这是行索引attributeList.Add(new OpenXmlAttribute("r", null, i.ToString()));writer.WriteStartElement(new Row(), attributeList);for (int j = 1; j <= 100; ++j){attributeList = new List();//这是数据类型 ("t")，带有 CellValues.String ("str")attributeList.Add(new OpenXmlAttribute("t", null, "str"));//建议你也有单元格引用，但是//您必须自己计算正确的单元格引用.//这是一个例子://attributeList.Add(new OpenXmlAttribute("r", null, "A1"));writer.WriteStartElement(new Cell(), attributeList);writer.WriteElement(new CellValue(string.Format("R{0}C{1}", i, j)));//这是针对单元格的writer.WriteEndElement();}//这是用于行writer.WriteEndElement();}//这是用于 SheetDatawriter.WriteEndElement();//这是工作表writer.WriteEndElement();writer.Close();writer = OpenXmlWriter.Create(workbook.WorkbookPart);writer.WriteStartElement(new Workbook());writer.WriteStartElement(new Sheets());//你可以像这样使用对象初始值设定项//是实际属性.SDK 类有时具有类似属性的属性//但实际上是类.例如，Cell 类具有 CellValue//属性"，但实际上是内部的子类.//如果属性对应于实际的 XML 属性，那么您就可以了.writer.WriteElement(new Sheet(){Name = "Sheet1",SheetId = 1,Id = workbook.WorkbookPart.GetIdOfPart(workSheetPart)});writer.WriteEndElement();//为 WorkSheet 元素写入结束writer.WriteEndElement();//为 WorkBook 元素写入结束writer.Close();工作簿.关闭()；}

如果您查看该代码，您会注意到两个主要写入内容，首先是工作表，然后是包含工作表的工作簿.工作簿部分是最后的无聊部分，前面的工作表部分包含所有的行和列.

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

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

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

最后，如果您正在处理非常大的数据集，永远不要调用 ToList().使用数据阅读器风格的数据流方法.例如，您可以有一个 IQueryable 并在 for each 中使用它.您永远不想依赖同时将所有数据保存在内存中，否则您会遇到内存不足限制和/或内存利用率高的问题.

I created a reporting tool as part of an internal web application. The report displays all results in a GridView, and I used JavaScript to read the contents of the GridView row-by-row into an Excel object. The JavaScript goes on to create a PivotTable on a different worksheet.

Unfortunately I didn't expect that the size of the GridView would cause overloading problems with the browser if more than a few days are returned. The application has a few thousand records per day, let's say 60k per month, and ideally I'd like to be able to return all results for up to a year. The number of rows is causing the browser to hang or crash.

We're using ASP.NET 3.5 on Visual Studio 2010 with SQL Server and the expected browser is IE8. The report consists of a gridview that gets data from one out of a handful of stored procedures depending on which population the user chooses. The gridview is in an 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>

I was relatively new to my team, so I followed their typical practice of returning the sproc to a DataTable and using that as the DataSource in the code behind:

    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();

(I know what you're thinking! But yes, I have learned much more about parameterization since then.)

The LINQToDataTable function isn't anything special, just converts a list to a datatable.

With a few thousand records (up to a few days), this works fine. The GridView displays the results, and there's a button for the user to click which launches the JScript exporter. The external JavaScript function reads each row into an Excel sheet, and then uses that to create a PivotTable. The PivotTable is important!

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++;
        }
    }

What I'm trying to do: Create a solution (probably client-side) that can handle this data and process it into Excel. Someone might suggest using the HtmlTextWriter, but afaik that doesn't allow for automatically generating a PivotTable and creates an obnoxious pop-up warning....

What I've tried:

• Populating a JSON object -- I still think this has potential but I haven't found a way of making it work.
• Using a SQLDataSource -- I can't seem to use it to get any data back out.
• Paginating and looping through the pages -- Mixed progress. Generally ugly though, and I still have the problem that the entire dataset is queried and returned for each page displayed.

Update: I'm still very open to alternate solutions, but I've been pursuing the JSON theory. I have a working server-side method that generates the JSON object from a DataTable. I can't figure out how to pass that JSON into the (external) exportToExcel JavaScript function....

    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();
    }

Can anybody show an example of how to carry this JSON object into an external JS function? Or any other solution for the export to Excel.

解决方案

It's easy and efficient to write CSV files. However, if you need Excel, it can also be done in a reasonably efficient way, that can handle 60,000+ rows by using the Microsoft Open XML SDK's open XML Writer.

1. Install Microsoft Open SDK if you don't have it already (google "download microsoft open xml sdk")
2. Create a Console App
3. Add Reference to DocumentFormat.OpenXml
4. Add Reference to WindowsBase
5. Try running some test code like below (will need a few using's)

Just Check out Vincent Tan's solution at http://polymathprogrammer.com/2012/08/06/how-to-properly-use-openxmlwriter-to-write-large-excel-files/ ( Below, I cleaned up his example slightly to help new users. )

In my own use I found this pretty straight forward with regular data, but I did have to strip out "" characters from my real data.

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();
        }

If you review that code you'll notice two major writes, first the Sheet, and then later the workbook that contains the sheet. The workbook part is the boring part at the end, the earlier sheet part contains all the rows and columns.

In your own adaptation, you could write real string values into the cells from your own data. Instead, above, we're just using the row and column numbering.

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

Worth noting, the row numbering in Excel starts at 1 and not 0. Starting rows numbered from an index of zero will lead to "Corrupt file" error messages.

Lastly, if you're working with very large sets of data, never call ToList(). Use a data reader style methodology of streaming the data. For example, you could have an IQueryable and utilize it in a for each. You never really want to have to rely on having all the data in memory at the same time, or you'll hit an out of memory limitation and/or high memory utilization.

这篇关于将大数据查询(60k+ 行)导出到 Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！