...

View Full Version : Timeout when Exporting to Excel



JayM
02-28-2006, 03:03 PM
Hello all...

I have a problem. In my application, we're exporting the contents of a DataGrid (dynamically populated through a Type Data Set) into an Excel worksheet at the click of a button.

All is working A OK, until a large amount of data is exported... the operation seems to time out. We tried changing over to a CSV, Word, PDF, but all seem to time out. Is there some good way I can handle such large amounts of data?

SpirtOfGrandeur
02-28-2006, 03:31 PM
Server.ScriptTimeout (http://www.microsoft.com/windows2000/en/server/IIs/default.asp?url=/windows2000/en/server/IIs/htm/asp/vbob246s.htm)

JayM
02-28-2006, 03:50 PM
OK, but this script is running in excess of 5 minutes... LOTS of data :)

is there some good way to page through the data and only write a certain amount at a time?

here's my code so far:


private void ExportRptBtn_ServerClick(object sender, System.EventArgs e)
{
if((RptResDataGrid == null) || (RptResDataGrid.Items.Count <= 0))
{
log.Debug("RptResDataGrid Count: " + RptResDataGrid.Items.Count);
RptStatusLbl.Text = _ReportName + " : No Results Displayed to Export";
}
else
{
Server.ScriptTimeout = 600;
Response.Clear();
Response.Buffer= true;

// Export to Excel Format
Response.ContentType = "application/vnd.ms-excel";

// If the file name contains / or \ characters the
// file name may not show up properly replace them with space
string filename = _ReportName.Replace('/', ' ');
filename = filename.Replace('\\', ' ');

//Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");

Response.ContentEncoding = System.Text.Encoding.Default;
Response.Charset = "";
EnableViewState = false;

System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

// Do not allow paging
RptResDataGrid.AllowPaging = false;

// Bind the results again to the grid
DataTable reportDataTable = null;

if (!_IsChild)
{
reportDataTable = (DataTable) Session["SelectedReportDataTable"];
}
else
{
reportDataTable = (DataTable) Session["ChildReportDataTable"];
}

RptResDataGrid.DataSource = reportDataTable;
RptResDataGrid.DataBind();
this.ClearControls(RptResDataGrid);
RptResDataGrid.RenderControl(oHtmlTextWriter);
Response.AddHeader("Content-Disposition", "attachment; filename=" + filename + ".xls");
Response.Buffer = false;
Response.Write(oStringWriter.ToString());
Response.End();

SpirtOfGrandeur
02-28-2006, 11:21 PM
Not really because it all needs to be sent to the client. You could make like 6 excel sheets and have them DL them all. Or create the file on the server and have them DL it that way... any thing you want to do.

JayM
03-01-2006, 02:39 PM
Server.ScriptTimeout (http://www.microsoft.com/windows2000/en/server/IIs/default.asp?url=/windows2000/en/server/IIs/htm/asp/vbob246s.htm)
i set this w/in the on click function of that button and set it to 600 (secs) but i'm still not seeing results back

JayM
03-01-2006, 11:43 PM
OK, i'm really stuck now...

i've tried exporting to Excel but i need to change the grid items to a text type, and that takes too long

i tried exporting to CSV, but i have to first (again) loop through my DataTable and paint all of the items as text... again, times out

i tried exporting to Word, which works, but there's so much data that Word crashes (8.0+ mb's)


is there anything anyone can suggest?

Brandoe85
03-01-2006, 11:58 PM
So it's taking longer than 600seconds to complete? How big is your datatable?

JayM
03-02-2006, 12:38 AM
So it's taking longer than 600seconds to complete? How big is your datatable?
approx 30,000 rows of data... 8 columns worth

that's a mid-size table, some are larger

suggestions?

Brandoe85
03-02-2006, 12:43 AM
I just created a CSV file from a datatable that had 2,100 rows and 5 columns within seconds. Can you post your code you're using to create the CSV?


Just did 32,000 rows and 5 columns in almost the exact same amount of time



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum