Solved

Using ASP.NET with C# and VS2010, how would you write code on a button click to export over 5,000 records to an Excel 2003 file using Web forms?

Posted on 2013-12-08
14
867 Views
Last Modified: 2014-04-22
I am writing my first ASP.NET application using C# with VS2010 and Web forms.

What code would you write for a button click event to transfer the records from either a Stored Procdure or a GridView to export over 5,000 rows to an Excel 2003 file.

For a field record layout example:

SELECT transferdate,  processdate,  Bank, Type, Office, CheckNo, RefNo, Description

export to file Clients.xls
0
Comment
Question by:zimmer9
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39705462
You can render your gridview on an HTMLWriter and then force a download to the client side.

A very detailed example is listed here:
http://www.c-sharpcorner.com/UploadFile/DipalChoksi/exportxl_asp2_dc11032006003657AM/exportxl_asp2_dc.aspx

and if you are looking for a downloadable sample:
http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39705556
Chinmay's first link looks very good but the mime types are wrong. You should update this line:
Response.ContentType = "application/ms-excel";

Open in new window

...to the correct mime type:
Response.ContentType = "application/vnd.ms-excel" // classic Excel .xls
-- OR -- 
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" // modern Excel .xlsx

Open in new window

0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39705586
@Angel:
application/ms-excel should work just fine give it a try.
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39705608
Yes it may well work but it's wrong. If you use the correct mime type you can have more confidence it will continue to work in the future.

http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/05/08/office-2007-open-xml-mime-types.aspx
http://en.wikipedia.org/wiki/Microsoft_Excel#File_formats
0
 
LVL 16

Accepted Solution

by:
Kamal Khaleefa earned 500 total points
ID: 39705842
hi
see this code

  Dim dt As New DataTable

dt=GetMyRowsFromDB()

  HttpContext.Current.Response.ContentType = "Application/x-msexcel"
            HttpContext.Current.Response.Clear() '
            'add the response headers
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=CPS.xls")
            Response.Write("<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />")

            'create our datagrid object
            Dim csvGrid As DataGrid = New DataGrid()
            'set the datasource for datatable
            csvGrid.DataSource = dt 'DsClon.Tables(0)
            'bind the values in our datatable to our grid
            csvGrid.DataBind()
            'create our stringwriter object
            Dim StringWriter As System.IO.StringWriter = New System.IO.StringWriter()


            'create our htmltextwriter
            Dim htmlWriter As HtmlTextWriter = New HtmlTextWriter(StringWriter)
            'render the contents of our datagrid to the htmlwriter
            csvGrid.RenderControl(htmlWriter)
            'since the convert to string the value of our stringwriter object.
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8
            HttpContext.Current.Response.Write(StringWriter.ToString())
            '                       //close the connection
            '                       command.Connection.Close();
            'end the http response
            HttpContext.Current.Response.End()

Open in new window

0
 

Author Comment

by:zimmer9
ID: 39705952
I had used the following code in the past as you suggested.

The problem is that when I export 5,000 rows of data, I get the error message:

Problems duing load
Problems came up in the following areas during load:
cell data too large
---------------------------------------------------------------------

 protected void Button1_Click1(object sender, EventArgs e)
        {
            string attachment = "attachment; filename=Contacts.xls";

            Response.ClearContent();
            Response.AddHeader("contenet-disposition", attachment);
            Response.ContentType = "application/vnd.ms-excel";

            StringWriter stringWriter = new StringWriter();
            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
            GridView1.RenderControl(htmlTextWriter);
            Response.Write(stringWriter.ToString());
            Response.End();
        }

        public override void VerifyRenderingInServerForm(Control control)
        {

        }
0
 

Author Comment

by:zimmer9
ID: 39705961
I'm only exporting 5 fields of data per row as follow:
-------------------------------------------------------
TransDate    file type  datetime

ProcessDate  file type  datetime

Bank             nvarchar(255)

Type             nvarchar(255)

Office          nvarchar(255)
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39706409
Try this

Response.ContentType = "application/vnd.xls";

Open in new window

0
 

Author Comment

by:zimmer9
ID: 39706503
Response.ContentType = "application/vnd.xls";
----------------------------------------------------------------

This revised code results in the following error:

The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


--------------------------------------------------------------------------------

The operation completed successfully. Error processing resource 'http://localhost:4380/default.aspx'. Line 8, Position 92

   <td>9/26/2013 12:00:00 AM</td><td>9/26/2013 12:00:00 AM</td><td>0184</td><td>A&...
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39706839
Alright try this:

Response.ContentType = "application/vnd.xls"; 
Response.ContentEncoding = System.Text.Encoding.UTF8; 

Open in new window


Source:
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_24049444.html
0
 

Author Comment

by:zimmer9
ID: 39706925
string attachment = "attachment; filename=Contacts.xls";
                Response.ClearContent();
                Response.AddHeader("contenet-disposition", attachment);
                Response.ContentType = "application/vnd.xls";
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                StringWriter stringWriter = new StringWriter();
                HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
                GridView1.RenderControl(htmlTextWriter);
                Response.Write(stringWriter.ToString());
                Response.End();
--------------------------------------------
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.


--------------------------------------------------------------------------------

The operation completed successfully. Error processing resource 'http://localhost:4380/default.aspx'. Line 8, Position 92

   <td>9/26/2013 12:00:00 AM</td><td>9/26/2013 12:00:00 AM</td><td>0184</td><td>A&...
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39707526
Can you send a screenshot of the error please. Thanks.
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 39707886
Hi zimmer9,

Was it a correct answer? If yes then it is ok I got bit confused as you are using C# code and that sample is in VB.

Regards,
Chinmay.
0
 

Author Comment

by:zimmer9
ID: 39711126
Thanks for asking. Yes the answer I chose works for my application.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question