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
861 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

14 Experts available now in Live!

Get 1:1 Help Now