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

Convert enum to a custom string

Enums are really usefull.
I'm using them in each application.
The primary benefit of using enum is that it makes your code more readable.
If you have variables that should has only strict number of values, then enums is really what you should use.
For example, if you have class that describing a person, its always better to have a property containing Gender.Male or Gender.Female then using just a string  "Male" or number "1" meaning that this is male and "0" for female.
Its not a problem even if you have dozen values, like age kinds:

public enum Age
{
  Unknown,
  Infant,
  Child,
  Adolescent,
  Adult,
  MatureAdult,
  OlderAdult,
  AgedAdult,
  Neonate
 }

If you are using enum you will be always sure that the code will not compile if you make a typo and type "Chaild" instead of "Child".
Its also much easy to add new states to the list when you need it.
In other words you are keep control all possible values that your variable may has. At the same time, the value  of the variable is looking friendly and well readable.

One of the problem with this approach is to show custom text that should be associated with the Enum value.

For instance, you want to build optional list that contains all possible values of the Age enum:

As you may see, the list looks ugly because the text is not readable at all. Also, you may want to describe each state and specify which age range is for each option so the user will be easy to select proper value :

To build such select list you should create a relation between the enum value and custom text value:

ASP.NET MVC Controller code:

public ActionResult Index()
        {
         Array values = Enum.GetValues(typeof(Age));
   List<KeyValuePair<string,string>> enumValues = new List<KeyValuePair<string, string>>();
         
   foreach (int value in values)
   {
    string text = string.Empty;
          switch ((Age)value)
          {
           case Age.Unknown:
            text = "Unknown";
            break;
           case Age.Infant:
            text = "Infant (>28 days<1 year)";
            break;
           case Age.Child:
      text = "Child (1-12 years)";
            break;
           case Age.Adolescent:
      text = "Adolescent (13-17 years)";
            break;
           case Age.Adult:
      text = "Adult (18-64 years)";
            break;
           case Age.MatureAdult:
      text = "Mature Adult (65-74 years)";
            break;
           case Age.OlderAdult:
      text = "Older Adult (75-84 years)";
            break;
           case Age.AgedAdult:
      text = "Aged Adult (>85 years)";
            break;
           case Age.Neonate:
      text = "Neonate (0-28 days)";
            break;
           default:
            throw new ArgumentOutOfRangeException();
          }
          
    enumValues.Add(new KeyValuePair<string, string>(text,value.ToString()));
         }
         
   ViewData["Ages"] = enumValues;
            
   return View();
        }

ASP.NET MVC View code:

<body>
    <% foreach (KeyValuePair<string,string> enumValue in (IEnumerable<KeyValuePair<string, string>>) ViewData["Ages"])
    { %>
      <%= Html.RadioButton(enumValue.Key, enumValue.Value)%>  
      <%= Html.Label(enumValue.Key, enumValue.Key)%> <br />
    <%
    } 
    %>
</body>

As you may see to build such relation, you should reserve some place in your code to have all this friendly strings and remember where this place is locating.
If you going to use it again, you should either copy/paste the same text or move it to the some class.
Both ways are not good because you and other developers of your project should always remember where is the place where the text can be changed.
Its great that there is a solution that allows to have custom text in the same place where you have Enum definition. You should use an attribute System.ComponentModel.DescriptionAttribute 
The attribute allows to specify any text as description for each enum element:

public enum Age
 {
  Unknown,
[Description("Infant (>28 days<1 year)")]
  Infant,
[Description("Child (1-12 years)")]
  Child,

  [Description("Adolescent (13-17 years)")]
  Adolescent,

  [Description("Adult (18-64 years)")]
  Adult,

  [Description("Mature Adult (65-74 years)")]
  MatureAdult,

  [Description("Older Adult (75-84 years)")]
  OlderAdult,

  [Description("Aged Adult (>85 years)")]
  AgedAdult,

  [Description("Neonate (0-28 days)")]
  Neonate
 }

When Enum is defined like above, its easy to get those custom text values with simple extender that's using reflection:

public static class EnumExtender
 {
  public static string GetDescription(this Enum en)
  {
   return GetDescription(en.GetType(), en.ToString());
  }

  public static string GetDescription(Type enumType, Enum en)
  {
   return GetDescription(enumType, en.ToString());
  }

  public static string GetDescription(Type enumType, string name)
  {
   MemberInfo[] members = enumType.GetMember(name);
   if (members.Length > 0)
   {
    MemberInfo mi = members[0];
    object[] attrs = mi.GetCustomAttributes(typeof(DescriptionAttribute), false);

    if (attrs.Length == 1 && attrs[0] is DescriptionAttribute)
    {
     DescriptionAttribute attr = (DescriptionAttribute)attrs[0];
     return attr.Description;
    }
   }
   return name;
  }

 }

In this way the code that builds custom text looks much better :

public ActionResult Index()
        {
         Array values = Enum.GetValues(typeof(Age));
   List<KeyValuePair<string,string>> enumValues = new List<KeyValuePair<string, string>>();
         
   foreach (int value in values)
   {
    string customText = ((Age)value).GetDescription();
    string enumValue = value.ToString();
    KeyValuePair<string, string> keyValuePair = new KeyValuePair<string, string>(customText, enumValue);

    enumValues.Add(keyValuePair);
   }

         ViewData["Ages"] = enumValues;
            
   return View();
        }