Create csv with asp.net and open in excel

Question

I'm using Asp.net to create a csv file that the user can open directly in excel. I want to make it possible to show the download popup and for the user to choose "Open with Excel" and that will open the file in excel.

The code to create the csv:

Response.Clear();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.csv", "test"));
Response.ContentType = "text/csv";
Response.Write("Year, Make, Model, Length\n1997, Ford, E350, 2.34\n2000, Mercury, Cougar, 2.38");
Response.End();

Excel needs to understand that "Year", "Make", etc should all be different columns. That doesn't seem to work with the csv I'm creating, everything is just in the same column. It shows up like this:

http://oi54.tinypic.com/2evyb0k.jpg

The only way to get it working with excel is to use the "Text Import Wizard". Then everything shows up in different columns like it should.

As I said what I need is to create a spreadsheet (it doesn't need to be csv), it should just be easy for the user to open in excel or whatever they are using to start working with it. How would you solve this? Thanks!

Answer 1

Not sure why it doesn't work like that - it does for me, but at a minimum try quoting the data:

Response.Write("\"Year\", \"Make\", \"Model\", \"Length\"\n\"1997\", \"Ford\", \"E350\", \"2.34\"\n\"2000\", \"Mercury\", \"Cougar\", \"2.38\"");

Excel can also import XML, google "<?mso-application progid="Excel.Sheet"?>" for the format. Or just save an excel spreadsheet as XML to see an example. Excel XML files can be named ".xls" and it will be able to open them directly.

Finally, you can use NPOI in .NET to manipulate excel spreadsheets in the native format: http://npoi.codeplex.com/

For XML you can also use a wrapper class I wrote that encapsulates this in a simple C# object model: http://snipt.org/lLok/

Here's some pseudocode for using the class:

XlsWorkbook book = new XlsWorkbook();
XlsWorksheet ws = new XlsWorksheet();
ws.Name = "Sheet Name";

XlsRow row;
XlsCell cell;

foreach (datarow in datasource) {
  row = new XlsRow();   
  foreach (datavalue in datarow) {
    cell = new XlsCell(datavalue.ToString());
    cell.DataType = datavalue is numeric ? DataTypes.Numeric | DataTypes.String;
    row.Cells.Add(cell);
   }
   ws.Rows.Add(row);
}
wkb.Worksheets.Add(ws);
Response.Write(wkb.XmlOutput());

Or just create your own method, here are the basics.

Create the header this way:

    System.Text.StringBuilder sb = new System.Text.StringBuilder();
    sb.Append("<?xml version=\"1.0\"?>\n");
    sb.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
    sb.Append(
      "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
    sb.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
    sb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
    sb.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
    sb.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\"&gt;\n");
    sb.Append(
      "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
    sb.Append("</DocumentProperties>");
    sb.Append(
      "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");
    sb.Append("<ProtectStructure>False</ProtectStructure>\n");
    sb.Append("<ProtectWindows>False</ProtectWindows>\n");
    sb.Append("</ExcelWorkbook>\n");

From there, add to your output string by creating nodes using this basic structure:

<Worksheet ss:Name="SheetName">
  <Table>
    <Row>
      <Cell>
        <Data ss:Type="DataType">Cell A1 Contents Go Here</Data>
      </Cell>
    </Row>
  </Table>
</Worksheet>

Within a <Row>, each <Cell> element creates a new column. Add a new <Row> for each spreadsheet row. The DataType for <Data> can be "String" or "Number".