Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
888 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

877 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