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

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.

nice one...

Submitted by Anonymous on Mon, 11/14/2011 - 15:49.

worked like a charm...!

It worked like a charm

Submitted by Anonymous on Tue, 03/01/2011 - 16:38.

Thank you just what I need it. It worked like a charm

J

Thanks... :-)

Submitted by Anonymous on Wed, 07/28/2010 - 01:05.

Thanks dude ...

Problem with ";"

Submitted by Anonymous on Fri, 05/14/2010 - 07:17.

Hi,

thanks for that helpful function.
But i've got still one prolbem. If the string contains ";" and i seperate the csv by "," first all looks nice. After that i want to put the values in the columns, looking after "," to split correctly.
But if i do that, the string will be cut where the ";" was and the rest of the string will go to the next column. So the following values are not in the correct column now.

Any Idea? Sorry, for my english ;-)

Thanks - it worked.

Submitted by Anonymous on Sat, 11/21/2009 - 17:28.

And this csv was starting to look like a mission.

Good job

Submitted by Anonymous on Fri, 05/29/2009 - 16:31.

Thanks just what I needed.

Don't work

Submitted by Anonymous on Thu, 05/14/2009 - 03:57.

After exprt, data are set in one column A, no like in your example. Any ideas?

Need use ';' not ','

Submitted by Anonymous on Thu, 05/14/2009 - 04:00.

I set ';' instead ',' and while exproting data are set in other columns.

Great Help...

Submitted by Anonymous on Fri, 02/20/2009 - 04:39.

I have implemented your given code without any difficulty.
You have saved much of my time.

May You Sail Smooooooooooooth !

All the datas are merged into one cell !!

Submitted by Anonymous on Wed, 07/21/2010 - 02:44.

Hi i've opened a CSv file (which has four columns) in window application using

System.Diagnostics.Process.Start(this.FilePath);

It opened but it has only one column all the values in other column merged into first column. when i closed the window application (or stop the EXCEL.EXE process manualy) and opened the csv file manually now it's working fine !

Reading that ????

Submitted by Anonymous on Sun, 03/08/2009 - 23:59.

it works fine ... the comma (,) char is present in the column of the excel file where we wanted it to be.
But the problem comes when i read the fields...

i read the fields by using streamreader line by line and splitting it with the delimiter as comma(,) so its not working ...is there any other way to read csv file

This code only refers to creating a CSV file

Submitted by Ben Hodson on Mon, 03/09/2009 - 08:00.

There is nothing here about reading a CSV.  But I would think that an easy way to do this would be to read in each line from your file.  Per line, parse each column by comma.  Make sure to check for the comma to be escaped as well as other CSV escape characters.  

On its most simplest level, you could just use the Split() string function to split on a comma and then parse the characters or you could loop the lines characters and peak at each upcoming character to determine the matching yourself.

PASS WORD PROTECT

Submitted by Anonymous on Fri, 08/19/2011 - 07:35.

IS IT POSSIBLE TO PASSWORD PROTECT THE EXCEL FILE USING THIS METHOD