StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Almost a week and a half since I began this problem, I've finally managed to get it all working to some extent. I'll wait temporarily from marking an answer to see if anybody else has a more efficient, better 'best practices' method.</p> <p>By generating a JSON string, I've divorced the JavaScript from the GridView. The JSON is generated in code behind when the data is populated:</p> <pre><code> 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()); } </code></pre> <p>Returns a string of data such as</p> <p>[ {"Caller":"John Doe", "Office":"5555","Type":"Incoming", etc},</p> <p>{"Caller":"Jane Doe", "Office":"7777", "Type":"Outgoing", etc}, {etc} ]</p> <p>I've hidden this string by assigning the text to a Literal in the UpdatePanel using:</p> <pre><code> <div id="div_JSON" style="display: none;"> <asp:Literal id="lit_JSON" runat="server" /> </div> </code></pre> <p>And the JavaScript parses that output by reading the contents of the div:</p> <pre><code>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 </code></pre> <p>The string output and the JavaScript 'eval' parsing both work surprisingly fast, but looping through the JSON object is a little slower than I'd like.</p> <p>I believe that this method would be limited to around 1 billion characters of data -- maybe less depending how memory testing works out. (I've calculated that I'll probably be looking at a maximum of 1 million characters per day, so that should be fine, within one year of reporting.)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload