.net导出excel 向客户端推送文件 下载文件 ClosedXML导出Excel NPOI导出Excel

导出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());    }