.Net ramblings
# Wednesday, 23 March 2005
Howto: Export a dataset to Excel (c# / asp.net)

In my web applications, i occassionaly need to allow the user to export a dataset as an excel file.  I was using a control written by Prashant Nayak posted on Code Project but he released a new version which was problematic for me, so i looked at other solutions.

obinna igbokwe  from  www.dedicatedsolutions.co.uk posted a good approach which creates a DataGrid object and binds it to the dataset, and then Renders the output of the control to the HttpResponse stream.  This works very well.  I have adapted his code to C# and added an option to specify a filename for the excel file.

Here is the code:

using System;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Whatever
{
 /// 
 /// This class provides a method to write a dataset to the HttpResponse as
 /// an excel file. 
 /// 
 public class ExcelExport
 {
  public static void ExportDataSetToExcel(DataSet ds, string filename)
  {
   HttpResponse response = HttpContext.Current.Response;
   
   // first let's clean up the response.object
   response.Clear();
   response.Charset = "";
   
   // set the response mime type for excel
   response.ContentType = "application/vnd.ms-excel";
   response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");
   
   // create a string writer
   using (StringWriter sw = new StringWriter())
   {
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
     // instantiate a datagrid
     DataGrid dg = new DataGrid();
     dg.DataSource = ds.Tables[0];
     dg.DataBind();
     dg.RenderControl(htw);
     response.Write(sw.ToString());
     response.End(); 
    }
   }
  }
 }
}

Wednesday, 23 March 2005 16:47:02 (GMT Standard Time, UTC+00:00)  #    Comments [72]  Asp.Net | Database

Wednesday, 07 June 2006 15:51:06 (GMT Daylight Time, UTC+01:00)
This works! Simple and to the point. Thanks, Tim.
Jeff
Wednesday, 04 October 2006 09:48:04 (GMT Daylight Time, UTC+01:00)
Do you know a method where it is possible to give the formules in the excel sheet?
Instead of just plain html text
freggel
Tuesday, 24 April 2007 10:50:04 (GMT Daylight Time, UTC+01:00)
This is really greate as per my requirement
Thanks Tim !!!
Ambesh
Thursday, 17 May 2007 20:05:50 (GMT Daylight Time, UTC+01:00)
Awesome, thanks.
Ian
Monday, 14 January 2008 09:59:51 (GMT Standard Time, UTC+00:00)
Thanks!!!!!!
Redzonic
Thursday, 31 July 2008 12:34:28 (GMT Daylight Time, UTC+01:00)
absolutly perfect code
thnx Tim
Mohd Sabir Ali
Thursday, 29 January 2009 13:31:34 (GMT Standard Time, UTC+00:00)
Thank you very much, clean and precise.
Ari
Sunday, 24 May 2009 13:12:11 (GMT Daylight Time, UTC+01:00)
I have exported grids to excel before, but I never thought about creating a method to instaniate grid just for this purpose. GREAT CONCEPT!!!!
Wednesday, 10 June 2009 22:31:52 (GMT Daylight Time, UTC+01:00)
How i Call this method from my UI
Please help me
Apurba Mukherjee
Wednesday, 10 June 2009 22:54:47 (GMT Daylight Time, UTC+01:00)
Hi
finally i solv this.Thanks for Your help.This is a great Code.
Apurba Mukherjee
Thursday, 11 June 2009 18:41:47 (GMT Daylight Time, UTC+01:00)
Nice post, however this is not a true Excel export, just a text stream.

Thanks
Joe Posmith
Thursday, 11 June 2009 18:43:26 (GMT Daylight Time, UTC+01:00)
True, but it reads perfectly in excel. In my view it's not worth trying to interop with the excel object model.
Cheers
Tim
tim
Monday, 15 June 2009 05:15:45 (GMT Daylight Time, UTC+01:00)
thanks for the codes. it helped me alot!! cheers!!
Alvin
Tuesday, 23 June 2009 21:04:19 (GMT Daylight Time, UTC+01:00)
Where can I see the exported file after I execute this code ?
musab
Tuesday, 23 June 2009 21:18:34 (GMT Daylight Time, UTC+01:00)
hi musab, have a read of the comment in the code " /// This class provides a method to write a dataset to the HttpResponse as
an excel file. "
once you execute the code the client can choose to open or save it, their browser may open it automatically depending on the browser settings.
if you want to write it to a file on the server, use File.WriteAllText(sw.ToString()) instead of the response.Write() parts.
good luck
tim
tim
Wednesday, 24 June 2009 17:45:23 (GMT Daylight Time, UTC+01:00)
I would add this to your code to help alleviate some of the strange character formatting that can result from encoding differences.

Response.Charset = "";
Response.ContentEncoding = System.Text.Encoding.Default;
Matt
Wednesday, 24 June 2009 17:48:44 (GMT Daylight Time, UTC+01:00)
hi Matt. thanks for the suggestion. surely your code just uses the encoding specified for the web site in web.config, or the default if not specified?
tim
Wednesday, 24 June 2009 18:24:30 (GMT Daylight Time, UTC+01:00)
Actually, I didn't realize there was a default setting available in the web.config. That would be a better way to deal with it, unless you needed to specify a particular encoding for your export.

Thanks for the tip!
Matt
Thursday, 25 June 2009 05:48:51 (GMT Daylight Time, UTC+01:00)
I see, I didn't get the response.write because I had my button inside an update panel so I thought the code doesn't work :)
But now it works great thanks alot !
Musab
Thursday, 02 July 2009 12:58:43 (GMT Daylight Time, UTC+01:00)
Man... Your code is so GREAT

SIMPLE UNBELIEVABLE!!!!
Well Done
Greek Nikos
Monday, 03 August 2009 08:10:06 (GMT Daylight Time, UTC+01:00)
Hi,

Please suggest code for windows service.
Monday, 03 August 2009 13:44:28 (GMT Daylight Time, UTC+01:00)
Hi,
One doubt for Excel Download. My Datatable contains value 002 whenever i download in Datatable to Excel. It shows only 2 but i need the following format 002 in Excel download file(Using ASP.Net-05, C#).Is it possible.
Help me.

Thank u.
Ganesh
Monday, 03 August 2009 16:18:14 (GMT Daylight Time, UTC+01:00)
Hi ganesh.
It is excel that is removing the leading zeroes from the file. If you open the file in notepad you will see they are there. For more information, google: excel leading zeroes
Tim
tim
Tuesday, 04 August 2009 10:24:14 (GMT Daylight Time, UTC+01:00)
Hi Tim,
Thank u for u r reply,
I want only Excel file.

Tanxs.
Ganesh
Friday, 04 September 2009 09:59:38 (GMT Daylight Time, UTC+01:00)
Hi Tim,

How can I generate the excel files in the client side. this code generates excel files in the server. could you help please
umit
Tuesday, 29 September 2009 10:44:05 (GMT Daylight Time, UTC+01:00)
hi tim,
i want to convert dataset to xls file & save file on the server.
is it possible to use your code for saving the dataset to excel file?

Thanks
Pragnesh
Pragnesh
Tuesday, 29 September 2009 10:46:13 (GMT Daylight Time, UTC+01:00)
Hi Pragnesh,
Instead of using Response.Write() to the web page, output the stringwriter to a file, for example:
File.WriteAllText(sw.ToString(), yourFilePath);
Good luck
Tim
tim
Thursday, 01 October 2009 09:52:30 (GMT Daylight Time, UTC+01:00)
hi tim,
thanks for your help.
it works great.
but i want proper excel file.
As opening of the exported file it promts it is not in proper format do you still want to open it?


Thanks
Pragnesh

Pragnesh
Wednesday, 07 October 2009 22:31:28 (GMT Daylight Time, UTC+01:00)
While this code works, binding a grid in code-behind to a dataset or table with 100K records is slow. This will work poorly on large sets.
Zir
Friday, 13 November 2009 15:16:16 (GMT Standard Time, UTC+00:00)
You saved the day! Many thanks!
Remington
Tuesday, 15 December 2009 16:06:06 (GMT Standard Time, UTC+00:00)
Excellent work, all working.
Thanks
Monday, 21 December 2009 15:09:44 (GMT Standard Time, UTC+00:00)
Hi,

I want to encode the xls file into iso-8859-1 because "é" "è" and all the other french accent doesn't show properly. I suppose I have to change the Html Header but I don't know how to do it.

Thanks
Fortin
Monday, 21 December 2009 16:43:25 (GMT Standard Time, UTC+00:00)
Sorry I found it:

_response.ContentEncoding = System.Text.Encoding.GetEncoding("iso-8859-1");
Fortin
Tuesday, 05 January 2010 08:53:35 (GMT Standard Time, UTC+00:00)
how to store large data with good performance in Excel.
Avinash
Wednesday, 20 January 2010 22:19:44 (GMT Standard Time, UTC+00:00)
Awesome, thanks.
Alex
Wednesday, 27 January 2010 12:49:23 (GMT Standard Time, UTC+00:00)
I have exported 4000 data in excel sheet but after exporting the excel sheet take very much time to open can u suggest me any solution to improve the opening time

Thanks,
Arkroop
Arkroop
Wednesday, 27 January 2010 12:52:17 (GMT Standard Time, UTC+00:00)
Hi arkroop,
If it takes a long time for Excel to open the file on your computer, I can't help you!
Good luck
Tim
tim
Wednesday, 27 January 2010 12:59:44 (GMT Standard Time, UTC+00:00)
Hi Tim,

Thanks for your responce but I have opening this excel file in different-2 pc its also taking long time to open.

Thanks
Arkroop
Wednesday, 27 January 2010 14:24:55 (GMT Standard Time, UTC+00:00)
hi Arkroop,
it doesn't happen for me. it may be because the file contains HTML which Excel must convert when it opens. But I often open very large files this way and I see no delays.
Tim
tim
Monday, 08 February 2010 08:25:43 (GMT Standard Time, UTC+00:00)
Is it possible to use this in a form instead of a website?
Monday, 08 February 2010 09:46:25 (GMT Standard Time, UTC+00:00)
hi mike. the output of the stringbuilder is your export text (sw.ToString()), if you are not in a web forms environment, just take the text and use it whatever way you need instead of using Response.Write etc.
good luck. tim
tim
Friday, 26 February 2010 15:21:43 (GMT Standard Time, UTC+00:00)
Thanks it was very usefull
Fernando
Tuesday, 09 March 2010 11:49:27 (GMT Standard Time, UTC+00:00)
e.Message Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack. string


Give me this error
tote
Tuesday, 13 July 2010 06:59:06 (GMT Daylight Time, UTC+01:00)
It is very useful and working code

Piyush
Piyush Mahajan
Wednesday, 04 August 2010 09:02:18 (GMT Daylight Time, UTC+01:00)
Very Good Useful Code.Helped me to export Dataset to grid. Even i did not know u can instantiate a Grid like this.
vineeta
Thursday, 12 August 2010 04:07:37 (GMT Daylight Time, UTC+01:00)
Hi,
Great code..i have one problem with this..whenever i run this on my local machine it will generate .xls file...but on web i.e. for my website it is not generating .xls file..not even download popup...
can u please help in this?
Thanks in advance..
Thursday, 12 August 2010 09:44:33 (GMT Daylight Time, UTC+01:00)
Hi,
Great code..i have one problem with this..whenever i run this on my local machine it will generate .xls file...but on web i.e. for my website it is not generating .xls file..not even download popup...
can u please help in this?
Thanks in advance..
Atul Nemade
Thursday, 19 August 2010 11:52:46 (GMT Daylight Time, UTC+01:00)
How can i export the tables as worksheets. One worksheet for each table in dataset is what i need.
Yuvraj
Friday, 27 August 2010 06:23:22 (GMT Daylight Time, UTC+01:00)
Same pbm as Yuvraj faced...Want to write multiple sheets from dataset with out using interop...
pradeep
Friday, 03 September 2010 21:57:51 (GMT Daylight Time, UTC+01:00)
this is really gr8
but it won't work for large amount of data where the number of rows may be more than 200K.....
biwa
Tuesday, 26 October 2010 11:19:37 (GMT Daylight Time, UTC+01:00)
hi,
inorder to write the sw data into an existing file i found your suggestion to use this code.
File.WriteAllText(sw.ToString(), yourFilePath);

but when i am trying to give the file path as c:\report.xsl it is saying illegal character in path .
can you suggest an option.
Thanks,
Pramuk Navap
Pramuk Navap
Thursday, 04 November 2010 21:10:54 (GMT Standard Time, UTC+00:00)
Thanks for the post!

To get unicode characters to display correctly in Excel, I made the following changes (vb.net):

Response.Clear()
Response.Charset = "utf-8" 'we want utf-8 for correct handling of Unicode characters in names

'set response mime type for excel
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("Content-Disposition", "attachment;filename=""" + Filename + """")

'utf8 byte order mark. From: http://blog.nicdex.com/?p=24
Dim BOM As Byte() = {&HEF, &HBB, &HBF}
Response.BinaryWrite(BOM)

'create a string writer...

davidt
Thursday, 20 January 2011 10:56:21 (GMT Standard Time, UTC+00:00)
This works alot but this is a class file we need to use this as a assembly.
we can refer this as a namespace,class and methods.
sai krishna
Thursday, 20 January 2011 10:58:00 (GMT Standard Time, UTC+00:00)
whatever.ExcelExport.ExportDataSetToExcel(ds, "c:/abc.xls");

we can call like this in your main program.. hope will work for everyone
sai krishna
Tuesday, 15 February 2011 15:49:28 (GMT Standard Time, UTC+00:00)
This code, unfortunately, does not work with Google Chrome nor with Firefox. When opening the XLS file the result shows the computer's background wallpaper, not the data from the dataset. How can we get around that? Thanks
maybush1
Tuesday, 15 February 2011 15:53:10 (GMT Standard Time, UTC+00:00)
hi maybush1, it works fine in firefox, i use it all the time. haven't tested in chrome, but there is no browser-specific content rendered, it is all generated server-side. your browser settings may be trying to embed the file in a browser window instead of prompting to download. i would suggest doing a google search on the various HTTP header options available to you when sending a file to the client. best of luck.
tim
Tuesday, 12 April 2011 16:07:08 (GMT Daylight Time, UTC+01:00)
Thanks Tim :) this post was very helpful and saved me a world of trouble.
Rafs
Tuesday, 19 April 2011 08:06:03 (GMT Daylight Time, UTC+01:00)

Thank u its really gr8
Sree
Thursday, 28 April 2011 09:51:03 (GMT Daylight Time, UTC+01:00)
Hi,
The code works great and it saves the data in excel but when i mail the excel file , the file that i recieve on the oter end is null without any values.

-Creator
creator
Wednesday, 01 June 2011 12:54:54 (GMT Daylight Time, UTC+01:00)
God bless you , it works perfectly , thank you sooooo much
AmAA
Thursday, 30 June 2011 11:06:17 (GMT Daylight Time, UTC+01:00)
how to export dataset tables into multiple worksheet of one workbook
pranali
Friday, 08 July 2011 07:53:17 (GMT Daylight Time, UTC+01:00)
Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled.
Details: Error parsing near '<table cellspacing="'.
Tuesday, 19 July 2011 21:43:01 (GMT Daylight Time, UTC+01:00)
hi,
code is simply great.but can i get the code in c#(c sharp)??
thanks,
himanshu
himanshu
Tuesday, 19 July 2011 21:56:43 (GMT Daylight Time, UTC+01:00)
sorry guys..
himanshu
Thursday, 08 September 2011 12:37:53 (GMT Daylight Time, UTC+01:00)
it is very much not good
Tuesday, 01 November 2011 06:10:44 (GMT Standard Time, UTC+00:00)
Superb!! It is working fine!!
Dhananjay
Friday, 16 December 2011 13:37:09 (GMT Standard Time, UTC+00:00)
dis code is after execution goes to catch if i use it in try{} catch{
}...
rajiv
Saturday, 11 February 2012 08:37:09 (GMT Standard Time, UTC+00:00)
Awesome Thanks!
Jaswinder
Tuesday, 14 February 2012 12:38:01 (GMT Standard Time, UTC+00:00)
Great Job !!!
Saturday, 25 February 2012 18:29:05 (GMT Standard Time, UTC+00:00)
What if I want to export multiple tables from a dataset into different worksheets...How to refer different sheets of a workbook in this case
Mohan
Sunday, 04 March 2012 06:29:54 (GMT Standard Time, UTC+00:00)
Thank it really help me out.Thanks Great job
Noor
Thursday, 30 August 2012 16:27:13 (GMT Daylight Time, UTC+01:00)
Fantastic. This is exactly what I was looking for. Many thanks.
Venkata Raghavan
OpenID
Please login with either your OpenID above, or your details below.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, strike) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview