导出excel大致分两种:
第一种是浏览器页面改名法:也就是说将数据
拼接成固定的格式,然后通过HttpResponse推送到浏览器
并且把后缀改成xslx
代码格式:
public void dtExcel(DataTable dt, string filename)
{
HttpResponse resp = System.Web.HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel";
resp.AppendHeader("Content-Disposition", "attachment;filename=" + filename);
string colHeaders = "", ls_item = "";
int i = 0;
//定义表对象与对象,同时用DataSet对其值进行初始化
DataRow[] myRow = dt.Select("");
//typeid=="1"时导出为excel格式檔;;typeid=="2"时导出为xml格式檔
//取得数据表名列标题,各标题之间以\t分割,最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count - 1; i++)
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
colHeaders += dt.Columns[i].Caption.ToString();
//向http输出流中写入取重的数据信息
resp.Write(colHeaders);
//逐行自理数据
bool firstline = true;
foreach (DataRow row in myRow)
{
//在当前行中,逐列取得数据,数据之间以\t分割,结束时加回车符\n
if (firstline == true && row[2].ToString() == "Top") { firstline = false; } else { ls_item += "\n"; }
for (i = 0; i < dt.Columns.Count - 1; i++)
ls_item += row[i].ToString() + "\t";
ls_item += row[i].ToString();
//当前行数据写入http输出流,并且置空ls_item以下数据行
resp.Write(ls_item);
ls_item = "";
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
其中DataTable是数据,filename文件名字
我不推荐这种写法,太low了,通常这种方法通过
office打开会报 “”的文件格式和扩展名不匹配。文件可能已经损坏或不安全。除非您信任其来源,否则请勿打开。是否仍要打开?
如果要解决这样的问题,就需要通过代码创建一个临时excel表来,将数据写入到表中,然后推送给浏览器,
下面就是第二种方法:
第一种:ClosedXML导出Excel
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
worksheet.Cell("A1").Value = "Hello World!";
workbook.SaveAs("HelloWorld.xlsx");
上面是作者的写法
步骤如下:
第一步nuget安装
第二步
这是DataTable 转excel
DataTable dt = ds.Tables[0];
var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Sample Sheet");
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
worksheet.Cell(i + 1, j + 1).Value = dt.Rows[i][j].ToString();
}
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Flush();
ms.Position = 0;
//推送到浏览器
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.sheet.binary.macroEnabled.12";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName + ".xlsx");
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
//清除缓存
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
//关闭缓冲区
ms.Close();
第二种
使用NPOI导出Excel
第一步
第二步
public void ExportDataTableToExcel(DataTable sourceTable, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//推送到浏览器
HttpResponse resp = System.Web.HttpContext.Current.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel";
resp.AppendHeader("Content-Disposition", "attachment;filename=" + sheetName);
resp.BinaryWrite(ms.ToArray());
}