自己在网上找了一段导出excel代码,本地测试和网络测试都没有问题,但是老板下载后就是乱码,后来发现我下载后把它保存文xlsx格式就行了
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 + ".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();
}
后来找了一下问题,有人说:context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + rf + ".xls");该段代码申明数据格式是xls的格式,但是实际上,你是导出的csv文件格式;
也有人说:
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + rf + ".xls");
context.Response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());
这又问题。
另外csv格式的数据是以“,”分割的,你把数据装好好,一次Respose.Write()就可以了。
但都是没找到最终的解决办法,就决定调用NPOI
public void NPOIExpot(DataTable table, string name)
{
string[] excelHead = { "单号", "姓名", "电话", "具体位置", "省", "市", "留言信息", "日期", "书名", "数量", "价格" };
var workhood = new HSSFWorkbook();
var sheet = workhood.CreateSheet("基本信息");
var col = sheet.CreateRow(0);
for (int i = 0; i < excelHead.Length; i++)
{
col.CreateCell(i).SetCellValue(excelHead[i]);
}
int a = 1;
foreach (DataRow item in table.Rows)
{
var row = sheet.CreateRow(a);
for (int i = 0; i < table.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(item[i].ToString());
}
a++;
}
System.IO.MemoryStream ms = new System.IO.MemoryStream();
workhood.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", name));
Response.BinaryWrite(ms.ToArray());
workhood = null;
ms.Close();
ms.Dispose();
}
果然轻松好用