/// <summary>
/// 将DataTable导出为Excel(csv)
/// </summary>
/// <param name="table">DataTable数据源</param>
/// <param name="name">文件名</param>
public static void ExportToSpreadsheet(DataTable table, string name)
{
Random r = new Random();
string rf = "";
for (int j = 0; j < 10; j++)
{
rf = r.Next(int.MaxValue).ToString();
}
HttpContext context = HttpContext.Current;
context.Response.Clear();
context.Response.ContentType = "text/csv";
context.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + rf + ".xls");
context.Response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());
foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + ",");
//context.Response.Write(column.ColumnName + "(" + column.DataType + "),");
}
context.Response.Write(Environment.NewLine);
double test;
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
switch (table.Columns[i].DataType.ToString())
{
case "System.String":
if (double.TryParse(row[i].ToString(), out test)) context.Response.Write("=");
context.Response.Write("\"" + row[i].ToString().Replace("\"", "\"\"") + "\",");
break;
case "System.DateTime":
if (row[i].ToString() != "")
context.Response.Write("\"" + ((DateTime)row[i]).ToString("yyyy-MM-dd hh:mm:ss") + "\",");
else
context.Response.Write("\"" + row[i].ToString().Replace("\"", "\"\"") + "\",");
break;
default:
context.Response.Write("\"" + row[i].ToString().Replace("\"", "\"\"") + "\",");
break;
}
}
context.Response.Write(Environment.NewLine);
}
context.Response.End();
}
public static void ExporttoExcel(HttpContext context, DataTable table)
{
context.Response.Clear();
context.Response.Buffer = true;
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + DateTime.Now.Ticks + ".xlsx");
context.Response.Write("<table border=1><tr bgcolor=#AAAAAA >");
foreach (DataColumn item in table.Columns)
{
context.Response.Write("<th>" + item.ColumnName + "</th>");
}
context.Response.Write("</tr>");
foreach (DataRow row in table.Rows)
{
string htmltable = "<tr>";
for (int i = 0; i < table.Columns.Count; i++)
{
htmltable += "<td style='vnd.ms-excel.numberformat:@'>" + row[i].ToString() + "</td>";
}
htmltable += "</tr>";
context.Response.Write(htmltable);
}
context.Response.Write("</table>");
context.Response.End();
}
/// 将DataTable导出为Excel(csv)
/// </summary>
/// <param name="table">DataTable数据源</param>
/// <param name="name">文件名</param>
public static void ExportToSpreadsheet(DataTable table, string name)
{
Random r = new Random();
string rf = "";
for (int j = 0; j < 10; j++)
{
rf = r.Next(int.MaxValue).ToString();
}
HttpContext context = HttpContext.Current;
context.Response.Clear();
context.Response.ContentType = "text/csv";
context.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + rf + ".xls");
context.Response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());
foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + ",");
//context.Response.Write(column.ColumnName + "(" + column.DataType + "),");
}
context.Response.Write(Environment.NewLine);
double test;
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
switch (table.Columns[i].DataType.ToString())
{
case "System.String":
if (double.TryParse(row[i].ToString(), out test)) context.Response.Write("=");
context.Response.Write("\"" + row[i].ToString().Replace("\"", "\"\"") + "\",");
break;
case "System.DateTime":
if (row[i].ToString() != "")
context.Response.Write("\"" + ((DateTime)row[i]).ToString("yyyy-MM-dd hh:mm:ss") + "\",");
else
context.Response.Write("\"" + row[i].ToString().Replace("\"", "\"\"") + "\",");
break;
default:
context.Response.Write("\"" + row[i].ToString().Replace("\"", "\"\"") + "\",");
break;
}
}
context.Response.Write(Environment.NewLine);
}
context.Response.End();
}
public static void ExporttoExcel(HttpContext context, DataTable table)
{
context.Response.Clear();
context.Response.Buffer = true;
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + DateTime.Now.Ticks + ".xlsx");
context.Response.Write("<table border=1><tr bgcolor=#AAAAAA >");
foreach (DataColumn item in table.Columns)
{
context.Response.Write("<th>" + item.ColumnName + "</th>");
}
context.Response.Write("</tr>");
foreach (DataRow row in table.Rows)
{
string htmltable = "<tr>";
for (int i = 0; i < table.Columns.Count; i++)
{
htmltable += "<td style='vnd.ms-excel.numberformat:@'>" + row[i].ToString() + "</td>";
}
htmltable += "</tr>";
context.Response.Write(htmltable);
}
context.Response.Write("</table>");
context.Response.End();
}