title: Export a large data query (60k+ rows) to Excel
body: <p>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.   </p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>&lt;asp:UpdatePanel ID="update_ResultSet" runat="server"&gt;
  &lt;Triggers&gt;
      &lt;asp:AsyncPostBackTrigger ControlID="btn_Submit" /&gt;
  &lt;/Triggers&gt;
  &lt;ContentTemplate&gt;
  &lt;asp:Panel ID="pnl_ResultSet" runat="server" Visible="False"&gt;
      &lt;div runat="server" id="div_ResultSummary"&gt;
          &lt;p&gt;This Summary Section is Automatically Completed from Code-Behind&lt;/p&gt;
      &lt;/div&gt;
          &lt;asp:GridView ID="gv_Results" runat="server" 
              HeaderStyle-BackColor="LightSkyBlue" 
              AlternatingRowStyle-BackColor="LightCyan"  
              Width="100%"&gt;
          &lt;/asp:GridView&gt;
      &lt;/div&gt;
  &lt;/asp:Panel&gt;
  &lt;/ContentTemplate&gt;
  &lt;/asp:UpdatePanel&gt;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>    List&lt;USP_Report_AreaResult&gt; areaResults = new List&lt;USP_Report_AreaResult&gt;();
      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 &gt; 0)
      {
          PopulateSummary(ref dtResults);
          gv_Results.DataSource = dtResults;
          gv_Results.DataBind();
  </code></pre>
  
  <p>(I know what you're thinking!  But yes, I have learned much more about parameterization since then.)</p>
  
  <p>The LINQToDataTable function isn't anything special, just converts a list to a datatable.</p>
  
  <p>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! </p>
  
  <pre><code>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 &lt; 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 &lt; oMyGridView.rows(y).cells.length; x++) {
                  var bHid = false;
                  for (iHidCol = 0; iHidCol &lt; oHid.length; iHidCol++) {
                      if (oHid[iHidCol].length !=0 &amp;&amp; oHid[iHidCol] == x) {
                          bHid = true;
                          break; 
                      } 
                  }
                  if (!bHid) {
                      oSheet.Cells(iRow + 1, iCol + 1) = oMyGridView.rows(y).cells(x).innerText;
                      iCol++;
                  }
              }
              iRow++;
          }
      }
  </code></pre>
  
  <p><strong>What I'm trying to do:</strong> Create a solution (probably client-side) that can handle this data and process it into Excel.  Someone might suggest using the <a href="https://stackoverflow.com/questions/5565382/how-to-export-gridview-to-excel">HtmlTextWriter</a>, but afaik that doesn't allow for automatically generating a PivotTable and creates an obnoxious pop-up warning....</p>
  
  <p><strong>What I've tried:</strong></p>
  
  <ul>
  <li>Populating a JSON object -- I still think this has potential but I haven't found a way of making it work.</li>
  <li>Using a SQLDataSource -- I can't seem to use it to get any data back out. </li>
  <li>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.</li>
  </ul>
  
  <p><strong>Update:</strong>
  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....</p>
  
  <pre><code>    protected static string ConstructReportJSON(ref DataTable dtResults)
      {
          StringBuilder sb = new StringBuilder();
          sb.Append("var sJSON = [");
          for (int r = 0; r &lt; dtResults.Rows.Count; r++)
          {
              sb.Append("{");
              for (int c = 0; c &lt; 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();
      }
  </code></pre>
  
  <p>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.</p>