The simplest way is to simply write either csv, or html (in particular, a <table><tr><td>...</td></tr>...</table>
) to the output, and simply pretend that it is in excel format via the content-type header. Excel will happily load either; csv is simpler...
Here's a similar example (it actually takes an IEnumerable, but it would be similar from any source (such as a DataTable
, looping over the rows).
public static void WriteCsv(string[] headers, IEnumerable<string[]> data, string filename)
{
if (data == null) throw new ArgumentNullException("data");
if (string.IsNullOrEmpty(filename)) filename = "export.csv";
HttpResponse resp = System.Web.HttpContext.Current.Response;
resp.Clear();
// remove this line if you don't want to prompt the user to save the file
resp.AddHeader("Content-Disposition", "attachment;filename=" + filename);
// if not saving, try: "application/ms-excel"
resp.ContentType = "text/csv";
string csv = GetCsv(headers, data);
byte[] buffer = resp.ContentEncoding.GetBytes(csv);
resp.AddHeader("Content-Length", buffer.Length.ToString());
resp.BinaryWrite(buffer);
resp.End();
}
static void WriteRow(string[] row, StringBuilder destination)
{
if (row == null) return;
int fields = row.Length;
for (int i = 0; i < fields; i++)
{
string field = row[i];
if (i > 0)
{
destination.Append(',');
}
if (string.IsNullOrEmpty(field)) continue; // empty field
bool quote = false;
if (field.Contains("""))
{
// if contains quotes, then needs quoting and escaping
quote = true;
field = field.Replace(""", """");
}
else
{
// commas, line-breaks, and leading-trailing space also require quoting
if (field.Contains(",") || field.Contains("
") || field.Contains("
")
|| field.StartsWith(" ") || field.EndsWith(" "))
{
quote = true;
}
}
if (quote)
{
destination.Append('"');
destination.Append(field);
destination.Append('"');
}
else
{
destination.Append(field);
}
}
destination.AppendLine();
}
static string GetCsv(string[] headers, IEnumerable<string[]> data)
{
StringBuilder sb = new StringBuilder();
if (data == null) throw new ArgumentNullException("data");
WriteRow(headers, sb);
foreach (string[] row in data)
{
WriteRow(row, sb);
}
return sb.ToString();
}
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…