Export DataTable to CSV File Download in C#

In 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!

Share and Enjoy:
  • Digg
  • DotNetKicks
  • del.icio.us
  • Facebook
  • Google Bookmarks
  • Live
  • MySpace
  • Netvibes
  • Reddit
  • StumbleUpon
You can leave a response, or trackback from your own site.

15 Responses to “Export DataTable to CSV File Download in C#”

  1. rushme says:

    code gives error message “File failed to load completely

  2. Victor Chen says:

    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.

  3. jdt says:

    Do you have a version that will work within and AJAX update panel?

  4. Victor Chen says:

    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.

  5. Bipin R. Singh says:

    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

  6. gopi says:

    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

  7. gopi says:

    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

  8. Marco says:

    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?

  9. Mick says:

    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.

  10. web2vil says:

    Great piece of code dude…
    Worked well for Mi..
    Thks :P

  11. Terry says:

    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 !

  12. Wes says:

    Thanks very much, very helpful example of how to avoid the Ajax exception.

  13. Avinash says:

    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.

  14. Fei says:

    Brilliant solution!!

Leave a Reply