Monday 18 August 2008

Exporting data as a CSV and allow download from browser C#

I recently did some work to export a data table as a Comma Separated Variable file or a MDB file.

The following code allows you to generate a csv from a data table.

   1: public static void CreateCSVHelper(DataTable sourceTable)
   2: {
   3:     //locals
   4:     StringBuilder outputBuilder = new StringBuilder();
   5:     
   6:     //write column names        
   7:     for (int i = 0; i < sourceTable.Columns.Count; i++)
   8:     {
   9:         if (i > 0)
  10:             outputBuilder.Append(",");
  11:         outputBuilder.Append(sourceTable.Columns[i].ColumnName);        
  12:     }
  13:     outputBuilder.Append(Environment.NewLine);
  14:     
  15:     //Put in data
  16:     foreach (DataRow row in sourceTable.Rows)
  17:     {
  18:         for (int i = 0; i < sourceTable.Columns.Count; i++)
  19:         {       
  20:             if (i > 0)
  21:                 outputBuilder.Append(",");                     
  22:             outputBuilder.Append(string.Format("\"{0}\"",row[i].ToString()));           
  23:         }
  24:         
  25:         outputBuilder.Append(Environment.NewLine);
  26:     }
  27:  
  28:     try
  29:     {
  30:         //Attempt to write file
  31:         StreamWriter sw = new StreamWriter(filename);
  32:         sw.Write(outputBuilder.ToString());
  33:         sw.Close();
  34:     }
  35:     catch (Exception ex)
  36:     {
  37:         System.Diagnostics.Debug.Write(ex);
  38:     }
  39: }

To display a link through to a browser, i.e. provide a link on a page where the user can click, get prompted do you wish to open or save this file – put the following in the code behind of the relevant page.

   1: protected void btnExportCSV_onClick(object sender, EventArgs e)
   2: {
   3:     //Clear buffer
   4:     Response.Clear();
   5:  
   6:     //Tell the browser it expects to get text output in the form of a csv
   7:     Response.ContentType = "text/csv";
   8:  
   9:     //Append header to tell the browser to expect a file
  10:     Response.AppendHeader("Content-Disposition", string.Format("attachment; filename={0}", filename));
  11:     
  12:     //Send file to browser response stream
  13:     Response.TransmitFile(filepath);
  14:  
  15:     //End the response 
  16:     Response.End();
  17: }

Finally if you are trying to use the code inside an update panel you will need to set the export button as a postback trigger. This can either be done using the following asp.net markup:

   1: <asp:UpdatePanel ID="upExport" runat="server">  
   2:     <ContentTemplate>  
   3:         <asp:LinkButton ID="btnExportCSV" runat="server">Export To Excel</asp:LinkButton>  
   4:     </ContentTemplate>  
   5:     <Triggers>  
   6:         <asp:PostBackTrigger ControlID="btnExportCSV">  
   7:         </asp:PostBackTrigger>  
   8:     </Triggers>  
   9: </asp:UpdatePanel>  

or you can set it dynamically in the code behind with the following lines put into the page_load event.


   1: //Deal with postback 
   2: smExportCSV.RegisterPostBackControl(btnExportCSV);


Where smExportCSV is your script manager control.