Export to Excel in C#. CSV format.

Very often, I receive requests to export some data into Excel.
Most of time its just plain table of values that have text or numeric columns.
At the beginning I tried different libraries that can create Excel documents with the data.
After that I found the CSV format.
Its really easy. All you need is to write values row by row into the text file and separate each column by coma.
Then Excel or Open Office will easily parses your text file and opens it without any problem.
You should no more worry neither about a library or version of the Excel - CSV is very easy to build and use:

		public byte[] BuildSpreadsheetVersionOfReport()
		{
			int totalRecords;

			List reports = BrowseReports();

			StringBuilder bld = new StringBuilder();

			bld.AppendLine("Event Id, Submitted Date/Time, Contracted Manufacturer, Status, Status Date/Time, Partner");

			foreach (ManufacturerReport report in reports)
			{
				bld.AppendFormat("{0},{1},{2},{3},{4},{5}\r\n", 
					report.EventId, 
					report.SubmittedDate, 
					report.ManufacturerName.GetCSVEncodedValue(), 
					report.Status.ToString().GetCSVEncodedValue(), 
					report.ReportAudit.EventDate, 
					report.Partner.Code.GetCSVEncodedValue());
			}

			return Encoding.ASCII.GetBytes(bld.ToString());
		}

As you may see, everything is simple. I'm just using a StringBuilder to build a list of rows with values.

However, there are two important things!

1. According to a CSV format description, there are few characters like comma, newline or double quote that's reserved for the format. So, if your report has that values, you should enclose them in double quote.
That's why I'm using GetCSVEncodedValue() extension method for some of my text values where reserved characters may appear :

		public static string GetCSVEncodedValue(this string val)
		{
			if (string.IsNullOrEmpty(val))
				return "\"\"";

			val = val.Replace("\"", "\"\"");

			if (val.Any(f => new[] { ',', '\"' }.Contains(f))
				|| val.IndexOf(Environment.NewLine) != -1
				|| val.StartsWith(" ")
				|| val.EndsWith(" "))
				return String.Format("\"{0}\"", val);

			return val;
		}

2. The second important thing is also relates to enclosing special characters with adouble quotes.
Compare two strings that's using as a templates in a String.Format for building each row of data:

bld.AppendFormat("{0},{1},{2},{3},{4},{5}\r\n",.....

and

bld.AppendFormat("{0}, {1}, {2}, {3}, {4}, {5}\r\n",.....

As you may see, in the second example there is a space between a comma and a value. Since we enclosing the string with a double quote, the results will be as following

"Value1","Value2, and a special character","Value3 \r\n"....

and

"Value1", "Value2, and a special character", "Value3 \r\n"....

The important thing is that second example will not work. Excel, as well as Open Office treats enclosing with a double quote only if the double guote starts right after the comma that delimits one value from another. As you may see, there is a space between a comma and a first double qoute.
This will cause that the end the file will be broken and can't be recognized correctly.

At the end its very easy to use generated content and return it to the customer as a CSV file (ASP.NET MVC code):

		public ActionResult CsvExport(OrderByInfo orderByInfo, ReportFilter filter)
		{
			return File(
				distributionReportManager.BuildSpreadsheetVersionOfReport(),
								"text/csv", 
								"ExportFile.csv");
		}