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");
		}

Maximum length of Excel worksheet name can't be more then 31 characters

Today I was faced with very weird issue.
In our programm we are generating Excel reports.

There are many page in it. 

Each of the page is describing its own location.

Location names are storing in the database and putting to the Excel document as the name of the sheet.

Today, people started  complaint that they can't open the file.

During opening, Excel show very strange exception about incorrect format of the file:

The content of the log file told me, that the content of the file is wrong, but did not tell me where exactly is the issue. So, i had to start searching  it by myself.

I found that nothing has changed in the database except of  few new locations were added.

After comparing that locations with other that was added earlier, we found, that few of them are very long.

I had to cut the name of the location to 31 characters to solve the issue.

If you editing Excel file manually, Excel does not let you to type more then 31 characters in the name of the sheet. But, if you creating document dynamically, as we do with some .NET component, its easy to avoid this limitation and enter any length you want.

So, even in Microsoft Office 10, the name of the Excel sheet has so huge limitation.