.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());

    }