Create and open Excel spreadsheet from ASP.NET

[John Straumann]

Hi all:

I found an earlier thread that showed creating an Excel file from ASP.NET,
pasted below and works great! However I set the code to create 3 columns and
25 data rows, and it creates a CSV file but when I open the file in Excel
the data shows up in one column thus:

A
Col 1;Col 2; Col3;
0;2;3
1;3;4;

etc.

So all the data is showing up in the first column, separated by ;.

Does anyone know how I could modify the code so I can create separate
columns in Excel?

Thanks for any and all input.

John.

public static void ExportToSpreadsheet(DataTable table, string name)
{
HttpContext context = HttpContext.Current;
context.Response.Clear();
foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + ";");
}
context.Response.Write(Environment.NewLine);
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row.ToString().Replace(";",
string.Empty) + ";");
}
context.Response.Write(Environment.NewLine);
}
context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment;
filename=" + name + ".csv");
context.Response.End();
}

 

[John Straumann]

Thanks, Mark.

JS.

context.Response.Write(column.ColumnName + ";");
context.Response.Write(row.ToString().Replace(";", string.Empty) +
";");

So all the data is showing up in the first column, separated by ;.

Does anyone know how I could modify the code so I can create separate
columns in Excel?

"CSV" = "comma-separated values", so...

context.Response.Write(column.ColumnName + ",");

context.Response.Write(row.ToString().Replace(",", string.Empty) +
",");

 

[Mark Stevens]

There is a rather good library on Codeplex which can write to Excel
2007 spreadsheets. Check out http://simpleooxml.codeplex.com/.

I've used it and the only problems I've found are

1. Formatting can max out the CPU.
2. It fails to paste bit field correctly (this has been reported as an
issue).

Hope this helps,
Mark
--
|\ _,,,---,,_ A picture used to be worth a
ZZZzzz /,`.-'`' -. ;-;;, thousand words - then along
|,4- ) )-,_. ,\ ( `'-' came television!
'---''(_/--' `-'\_)

Mark Stevens (mark at thepcsite fullstop co fullstop uk)

This message is provided "as is".