RSS FeedIn C#, a common function in web pages containing data in a table is allowing the web user to export to excel. Through my experiences, I have uncovered that to export from a GridView (or DataGrid) to a Microsoft Excel (xls) file is messy and not easily customizable. Instead, I found that taking the underlying DataTable and instead exporting to a Microsoft Excel Comma Separated Value (CSV) file as a much better method. Below is the snippet of code for the function.
private void exportDataTableToCsv(DataTable formattedDataTable, string filename)
{
DataTable toExcel = formattedDataTable.Copy();
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn column in toExcel.Columns)
{
context.Response.Write(column.ColumnName + ",");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in toExcel.Rows)
{
for (int i = 0; i < toExcel.Columns.Count; i++)
{
context.Response.Write(row[i].ToString().Replace(",", string.Empty) + ",");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
context.Response.End();
}
To call this function, simple create a C# LinkButton or Button Web Control. In your aspx page, your control should look similar to:
<asp:LinkButton ID="lbToExcel" runat="server">Export To Excel</asp:LinkButton>
From here, the lbToExcel_Click function will call the Export to DataTable function exportDataTableToCsv defined earlier in this post. I would also advise making edits at this point to your DataTable by removing columns that were hidden in the GridView (or DataGrid). We want to pass a formattedDataTable.
If your page uses Ajax’s Update Panel controls, it is required to set the lbToExcel web control as a PostBackTrigger as opposed to an AsyncPostBackTrigger. To add a PostBackTrigger in Visual Studio 2008’s Ajax Update Panel, find the properties of the Update Panel. Under Behavior, find Triggers and choose to edit the Collection. Next, Click Add and from the Dropdown select PostBackTrigger. Next, you need to manually (without the aid of intellisense) type in the control name. In our case, the control name was lbToExcel. The result should be the code snippet example below:
<asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <asp:LinkButton ID="lbToExcel" runat="server">Export To Excel</asp:LinkButton> </ContentTemplate> <Triggers> <asp:PostBackTrigger ControlID="lbToExcel"> </asp:PostBackTrigger> </Triggers> </asp:UpdatePanel>
Overall, this has worked flawlessly for me. If you have any problems, feel free to drop a line describing your challenges and I can attempt to give you a hand!
Posted in 
Tags: 
code gives error message “File failed to load completely
Hey rushme – It should work in all cases, but do you have any non alphanumeric symbols in the datatable? I’d interested in updating the above code to be more robust.
Do you have a version that will work within and AJAX update panel?
jdt,
This version above happens to work in an AJAX Update Panel. The important thing to remember is to include the control as a PostBackTrigger (as opposed to an AysncPostBackTrigger under the properties of a Update Panel (Trigger). You can add this by code by seeing the last paragraph of the code in the tutorial.
Hi,
First of all thanks a lot for providing this wonderfull code on net. i used this code & really it works great even its’ very light weighted.
thaks a lot once again & keep it up.
Thanks & Regards,
Bipin R. Singh
1.
2.
3. Export To Excel
4.
5.
6.
7.
8.
9.
If i gave above code its not downloading throwing webform.pageManager Exception.Can you tell the alternative how to download the CSV or excel using the AsyncPostBackTrigger
Export To Excel
6.
If i gave above code its not downloading throwing webform.pageManager Exception.Can you tell the alternative how to download the CSV or excel using the AsyncPostBackTrigger
Leave a Reply
I’ve been working on exporting to excel myself and I’m wondering;
If you use the postbacktrigger instead of the async trigger, you cannot use ajax update progress (which would be the whole point of using ajax as I see it…)
So is there a way to export and open the file?
perhaps without using the context.respond or opening the file another way?
Am I missing something here or are you bodging the data by deleting commas?
A more reasonable techique would be to enclose each field in double quotes. You would also of course also need to double up any existing quotes.
Great piece of code dude…
Worked well for Mi..
Thks :P
Fantastic, i always got a Sys.WebForms.PageRequestManagerParserErrorException:
when i tried to do anything like this but his solved it thanks a bunch, yousaved me hours of programming to save the csv file to the server then redirect to the csv, brilliant !
Thanks very much, very helpful example of how to avoid the Ajax exception.
[...] http://www.victorchen.info/export-datatable-to-csv-file-download-in-c/ Posted: Sep 28 2009, 03:28 PM by sukumarraju | with no comments [...]
I tried this code bUT after saving or cancelling or opening the file, my other code does not work. OR after saving the file, i again click on button to export to csv, it does not work. If I do a page refresh, then only it works.
Can you please provide a solution or workaround for this.
Brilliant solution!!