Create a .csv file in C#

Question

Alright I want to create a .csv file in C#. I have been looking around and noticed a lot of people are using the system.IO.memorystream and system.io.streamwriter.

The problem is this: I have a web application. I want to give the user the ability to export to excel. Problem is, Excel cannot be installed on the server (don't ask). I want to be able to write an .csv sheet export for a report. Now, the reports headers and data will be different for all of the reports (looping through will solve this). Anybody have an example or better resources for me to go through?

Answer 1

This the approach i normally take. Probably not the most efficient though.

        /// <summary>
    /// Generates the contents of the log file.
    /// </summary>
    /// <returns>The contents of the log file.</returns>
    internal string GenerateLogFile()
    {
        StringBuilder csvExport = new StringBuilder();
        csvExport.AppendLine(Resources.CSVHeader);

        foreach (DataRow row in this.logEntries.Rows)
        {
            csvExport.AppendLine(
                string.Format(
                "\"{0}\",\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",\"{7}\",\"{8}\", \"{9}\"",
                row[ColumnNames.LogTime], row[ColumnNames.Field1], row[ColumnNames.Field2], row[ColumnNames.Field3], row[ColumnNames.Field4], row[ColumnNames.Field5], row[ColumnNames.Field6], row[ColumnNames.Field7], row[ColumnNames.Field8], row[ColumnNames.Field9]));
        }

        return csvExport.ToString();
    }

    /// <summary>
    /// Adds the CSV file to the response.
    /// </summary>
    /// <param name="csvExportContents">The contents of the CSV file.</param>
    internal void DisplayLogFile(string csvExportContents)
    {
        byte[] data = new ASCIIEncoding().GetBytes(csvExportContents);

        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.ContentType = "APPLICATION/OCTET-STREAM";
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment; filename=Export.csv");
        HttpContext.Current.Response.OutputStream.Write(data, 0, data.Length);
        HttpContext.Current.Response.End();
    }

Answer 2

You should be able to use the examples using System.IO.MemoryStream and System.IO.StreamWriter just fine.

Instead of writing the MemoryStream out to a file, you would return it as the ResponseStream in ASP.NET (making sure that you set the appropriate header values so the browser knows that it's downloading a file).

Answer 3

There is really no magic in creating a CSV file. The only problem with CSV files is that there is not really a standard, and most importers will just implement one particular behavior for the import process. If you are lucky, you will get something that can guess relatively well.

Generating the CSV file is just a matter of printing the lines. As for the actual details, familiarize yourself with:

http://en.wikipedia.org/wiki/Comma-separated_values

That being said, if what you want is to export to Excel, you could use Microsoft's OOXML SDK:

http://msdn.microsoft.com/en-us/library/bb448854.aspx

The OOXML SDK is a C# library that you can use to generate Excel files on the server. The Brian Jones blog is a great resource on how to use this library:

http://blogs.msdn.com/b/brian_jones/

Answer 4

Excel is only required if you are using Office Interop. Using StringWriter, you are simply going to create a file and add some response information. Excel is only required when opening the file.

Answer 5

If you don't mind using a 3rd party library and the LGPL license is okay in your project, then FileHelpers is a great tool for this.

Answer 6

private void WriteItem<T>(StreamWriter sr, T item)
{
    string itemString = item.ToString();
    if(itemString.IndexOfAny('"', ',', '\n', '\r') != -1)//skip test and always escape for different speed/filesize optimisation
    {
        sr.Write('"');
        sr.Write(itemString.Replace("\"", "\"\""));
        sr.Write('"');
    }
    else
        sr.Write(itemString);
}
private void WriteLine<T>(StreamWriter sr, IEnumerable<T> line)
{
    bool first = true;
    foreach(T item in line)
    {
        if(!first)
            sr.Write(',');
        first = false;
        WriteItem(sr, item);
    }
}
private void WriteCSV<T>(StreamWriter sr, IEnumerable<IEnumerable<T>> allLines)
{
    bool first = true;
    foreach(IEnumerable<T> line in allLines)
    {
        if(!first)
            sr.Write('\n');
        first = false;
        WriteLine(sr, line);
    }
}
private void WriteCSV<T>(HttpResponse response, IEnumerable<IEnumerable<T>> allLines)
{
    response.ContentType = "text/csv";
    WriteCSV(response.Output, allLines);
}

It can be worth also sending a content-disposition header with a recommended filename.