Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Mar 2005
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Timeout when Exporting to Excel

    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?

  • #2
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #3
    New Coder
    Join Date
    Mar 2005
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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:

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

  • #4
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #5
    New Coder
    Join Date
    Mar 2005
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by SpirtOfGrandeur
    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

  • #6
    New Coder
    Join Date
    Mar 2005
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #7
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    So it's taking longer than 600seconds to complete? How big is your datatable?

  • #8
    New Coder
    Join Date
    Mar 2005
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Brandoe85
    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?

  • #9
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    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?

    Edit:
    Just did 32,000 rows and 5 columns in almost the exact same amount of time
    Last edited by Brandoe85; 03-02-2006 at 01:57 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •