导出一个大数据查询（60k +行）到Excel [英] Export a large data query (60k+ rows) to Excel
问题描述
我创建了一个报告工具，作为内部Web应用程序的一部分。该报告显示了GridView中的所有结果，并且我使用JavaScript将GridView的内容逐行读取到Excel对象中。 JavaScript继续在不同的工作表上创建数据透视表。
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.
不幸的是，如果返回了几天，我没有想到GridView的大小会导致浏览器出现重载问题。该应用程序每天有几千条记录，我们说每个月有60k个记录，理想情况下，我希望能够将所有结果返还一年。行数导致浏览器挂起或崩溃。
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.
我们在Visual Studio 2010上使用ASP.NET 3.5与SQL Server，预期的浏览器是IE8。该报告包括一个网格视图，根据用户选择的人口，从一小部分存储过程中获取数据。 gridview在UpdatePanel中：
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>
我对我的团队来说比较新，所以我按照他们将sproc返回到DataTable的典型做法并将其用作代码中的DataSource：
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.)
LINQToDataTable函数不是什么特别的，只需将列表转换为数据。
The LINQToDataTable function isn't anything special, just converts a list to a datatable.
几千条记录（最多几天），这工作正常。 GridView显示结果，并且有一个按钮用于单击哪个启动JScript导出器。外部JavaScript函数将每行读入Excel表单，然后使用它创建数据透视表。数据透视表很重要！
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++;
}
}
我正在努力做什么： 创建一个可以处理该数据并将其处理为Excel的解决方案（可能是客户端）。有人可能会建议您使用 HtmlTextWriter ，但不允许自动使用生成数据透视表，并创建一个令人讨厌的弹出式警告....
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....
我试过的：
- 填充一个JSON对象 - 我仍然认为这有潜力，但我还没有找到一种方法。
- 使用SQLDataSource - 我似乎无法使用它来获取任何数据。
- 分页并循环遍历页面 - 混合进度。通常丑陋，而且我仍然有问题，整个数据集被查询并显示每页显示。
更新：
我仍然很乐于替代解决方案，但我一直在追求JSON理论。我有一个工作的服务器端方法从DataTable生成JSON对象。我不知道如何将该JSON传递到（外部）exportToExcel JavaScript函数....
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();
}
任何人都可以显示如何将此JSON对象载入外部JS的示例功能？或导出到Excel的任何其他解决方案。
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.
推荐答案
我们通常用一个导出命令按钮来处理，以服务器端方式抓取数据集并将其转换为CSV。然后我们调整响应标题，浏览器将其视为下载。我知道这是一个服务器端解决方案，但是您可能需要考虑这一点，因为您将继续执行超时和浏览器问题，直到您实现服务器端记录分页。
We typically handle this with an "Export" command button which is wired up to a server side method to grab the dataset and convert it to CSV. Then we adjust the response headers and the browser will treat it as a download. I know this is a server side solution, but you may want to consider it since you'll continue having timeout and browser issues until you implement server side record paging.
这篇关于导出一个大数据查询（60k +行）到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
