Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
885 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

722 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