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
859 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now