Reply to comment

Export GridView or Other Data as Microsoft Excel (CSV) File with C#

Submitted by Ben Hodson on Sat, 11/15/2008 - 18:41

The following is a quick and easy way to export a list of data to a comma separated file (CSV). The nice thing about CSV is that Microsoft Excel can open it directly and it reads just like a simple Excel document. The rules of CSV are pretty straight forward:

  • Values in the CSV file must be separated by a comma.
  • The values must match the exact count of each row. If a specific column value is empty, make sure to place a "," as a placeholder for that value so it retains the same order.
  • Each line in the file must end with a line break (CR or CRLF).
  • If the value you are adding for a field has a comma(s) already in it, you must escape that field by wrapping it in quotes (") at the beginning and end.
  • If the field has comma(s) and already has quotes in it, you must "escape the escape characters" by putting double quotes (2 sets of quotes in a row) for each quote in the field value.

First, we need a simple utility method to assist us in escaping our CSV field values:



   1:  // Escapes a CSV field value.  sFieldValueToEscape is the field value we want 
   2:  // to parse and escape correctly for the CSV format.
   3:  private string _EscapeCsvField(string sFieldValueToEscape)
   4:  {
   5:      // since we delimit values with a comma, we need to escape commas that are
   6:      // actually in the value to escape.  We do this by putting a (") quote at
   7:      // the front and end of the string.
   8:      if (sFieldValueToEscape.Contains(","))
   9:      {
  10:          // if the string we are escaping already has a (") quote in it, we have to 
  11:          // 'escape the escape character' by putting double quotes in it's place.
  12:          if (sFieldValueToEscape.Contains("\""))
  13:          {
  14:              return "\"" + sFieldValueToEscape.Replace("\"", "\"\"") + "\"";
  15:          }
  16:          else
  17:          {
  18:              // there are no quotes in this string so just escape it by wrapping it in
  19:              // quotes.
  20:              return "\"" + sFieldValueToEscape + "\"";
  21:          }
  22:      }
  23:      else
  24:      {
  25:          // There are no commas in this string so just return it as is
  26:          return sFieldValueToEscape;
  27:      }
  28:  }

Now we add the code to a button click event or whatever method is easiest to actually export our data as a CSV format.

   1:  // The name of the file as you want to to appear to the client browser when it downloads.
   2:  string sFileName = "my_file_name1.csv";
   3:  // clear the response to there is nothing coming back.  NOTE: if you don't do this, then you will get other 
   4:  // HTTP and HTML junk in your CSV file content.
   5:  HttpContext.Current.Response.Clear();
   6:  // Add an HTTP header for the content type to tell the browser that this is a file that is being passed back.
   7:  HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", sFileName));
   8:  // I like to read CSV with excel so I tell the browser to associate this file with Microsoft Excel.  That way
   9:  // when the user clicks "open", the file opens in that program if they have it.
  10:  HttpContext.Current.Response.ContentType = "application/ms-excel";
  11:   
  12:  // Here is a sample CSV format of data you might pass back to the client.
  13:  System.Text.StringBuilder sbCsvValues = new System.Text.StringBuilder();
  14:  sbCsvValues.AppendLine("First Name,Last Name,Street Adress,Phone Number");
  15:   
  16:  // Query your database or the data source property of your GridView here...
  17:  // Add values for the sections.  NOTE: Make sure each line of the CSV is 
  18:  // on a separate line in the file by using AppendLine().
  19:  string sFirstName1 = "Bob";
  20:  string sLastName1 = "Sanders";
  21:  string sStreetAddress = "123 \"Oregon\" Street";
  22:  string sPhoneNumber = "(888) 867-5309, ext. 12";
  23:  sbCsvValues.Append(_EscapeCsvField(sFirstName1));
  24:  sbCsvValues.Append(",");
  25:  sbCsvValues.Append(_EscapeCsvField(sLastName1));
  26:  sbCsvValues.Append(",");
  27:  sbCsvValues.Append(_EscapeCsvField(sStreetAddress));
  28:  sbCsvValues.Append(",");
  29:  sbCsvValues.Append(_EscapeCsvField(sPhoneNumber));
  30:  sbCsvValues.AppendLine();
  31:   
  32:  // write the response to the HTTP stream back to the client browser.
  33:  HttpContext.Current.Response.Write(sbCsvValues.ToString());
  34:  // End the stream write and commit it.
  35:  HttpContext.Current.Response.End();

Now when the user downloads your file, they will see something like this in excel:

export to excel csv format

This is really useful to download data on pages that might contain statistics or long data lists. It makes it easy for non-technical people to get at that data without having to work with a programmer or database administrator to query for the data.

Reply