c#MVC汇出Excel的三种方法

Love The Way You Lie 2022-05-17 05:15 85阅读 0赞

初学C\#，入职不久，公司考核项目，其中就有将同比环比数据导出Excel,网上找了很多的例子，非常头痛，所以发誓自己写完了就吧所有的集中在一起，当然参照了很多博客，其中一篇有三种方法，想引入进来，但是忘记地址了，万分抱歉，闲话少说

**1.第一种 IO流**

两个参数 TranDate, BranchCode 一个为时间，一个为分店号，我是用这两个参数获取数据的，主要获取DataTable,按你自己的方式，从数据库获取DataTable数据，再操作。

将数据表的内容导出到excel中
    public void OutDataExcel(DateTime TranDate, int BranchCode)
    {
    //获取数据
    BranchSaleBLL branchSaleBLL = new BranchSaleBLL();
    DataTable dt = branchSaleBLL.GetSaleInfo(TranDate, BranchCode);
    string shtnl = "";
    shtnl = "<table border='1' cellspacing='1' cellpadding='1'>";
    shtnl = shtnl + "<tr>";
    shtnl += "<th>分店号</th>";
    shtnl += "<th>分店名</th>";
    shtnl += "<th>产品类型编号</th>";
    shtnl += "<th>产品类型</th>";
    shtnl += "<th>当月销售总额</th>";
    shtnl += "<th>同比</th>";
    shtnl += "<th>环比</th>";
    shtnl += "</tr>";
    //以上代码为表头，这个循环从数据库表头还没去尝试，以上写死
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        shtnl = shtnl + "<tr>";
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            shtnl = shtnl + "<td>" + dt.Rows[i][j] + "</td>";
        }
        shtnl = shtnl + "</tr>";
    }
    shtnl = shtnl + "</table>";
    ExportToExcel("application/x-excel", TranDate.Year + "年" + TranDate.Month + "月销售情况.xls", shtnl);
    }
    
    public void ExportToExcel(string FieldType, string FileName, string dt)
    {
    System.Web.HttpContext.Current.Response.Charset = "utf-8";
    System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());
    System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
    System.Web.HttpContext.Current.Response.ContentType = FieldType;
    StringWriter tw = new StringWriter();
    System.Web.HttpContext.Current.Response.Output.Write(dt);
    System.Web.HttpContext.Current.Response.Flush();
    System.Web.HttpContext.Current.Response.End();
    }

ResponseObject是一个响应类 我定义了一个全局的ResponseObject实例

/// <summary>
     /// 响应对象
     /// </summary>
     public class ResponseObject
     {
         /// <summary>
         /// 响应状态码
         /// </summary>
         public int StatusCode { get; set; }
    
         /// <summary>
         /// 响应消息
         /// </summary>
         public string Message { get; set; }
    
         /// <summary>
         /// 响应数据
         /// </summary>
         public object Data { get; set; }
     }

**前段调取接口要用window.open();如下所示，用ajax好像不可以。**

window.open("/BranchSale/DownloadFile?TranDate=" + ChooseTime + "&BranchCode=" + branchCode, "exportxls", null);

如上第一中方法，这样就可以导出了。

2.利用微软软件汇出Excel

/ <summary>
    / 第二种方法，利用微软自带插件
    / </summary>
    / <returns></returns>
    public ActionResult DownloadFile(DateTime TranDate, int BranchCode)
    {
    try
    {
        BranchSaleBLL branchSaleBLL = new BranchSaleBLL();
        System.Data.DataTable dt = branchSaleBLL.GetSaleInfo(TranDate, BranchCode);
        if (dt.Rows.Count > 0)
        {
            //Excel文件名
            var excelName = dt.Rows[0][1].ToString() + TranDate.Year + '年' + TranDate.Month;
            string str = Server.HtmlEncode(Request.PhysicalApplicationPath).ToString() + "Excel\\" + excelName + "月销售情况.xls";
            if (System.IO.File.Exists(str))
            {
                //如果存在则删除
                System.IO.File.Delete(str);
            }
    
            DataToExcel.DataTableToExcel(dt, str);
            return File(str, "application/vnd.ms-excel", excelName + "月销售情况.xls");
        }
        else
        {
            responseObject.StatusCode = (int)StatusCode.Fail;
            responseObject.Message = "无记录";
            responseObject.Data = null;
        }
        return Json(responseObject, JsonRequestBehavior.AllowGet);
    }
    catch
    {
        throw;
    }
    }

public static void DataTableToExcel(System.Data.DataTable datas, string path)
    {
    
    Application application = new Application();
    application.SheetsInNewWorkbook = 1;
    application.Workbooks.Add();
    Worksheet sheet = (Worksheet)application.ActiveWorkbook.Worksheets[1];
    
    for (int i = 0; i < datas.Columns.Count; i++)
    {
    sheet.Cells[1, i + 1] = datas.Columns[i].ColumnName;
    }
    
    for (int i = 0; i < datas.Rows.Count; i++)
    {
    for (int j = 0; j < datas.Columns.Count; j++)
    {
      sheet.Cells[2 + i, j + 1] = datas.Rows[i][j].ToString();
    }
    }
    application.Visible = false;
    int FormatNum;//保存excel文件的格式
    string Version;//excel版本号
    Version = application.Version;//获取你使用的excel 的版本号
    if (Convert.ToDouble(Version) < 12)//You use Excel 97-2003
    {
    FormatNum = -4143;
    }
    else//you use excel 2007 or later
    {
    FormatNum = 56;
    }
    try
    {
    application.ActiveWorkbook.SaveAs(path, FormatNum);
    }
    catch
    {
    }
    application.Quit();
    }

上述获取版本信息，主要是2003扩展名为xls，2007为xlsx，所以有区别。

**3.利用NPOI插件导出Excel，强烈建议使用此方法**  
为什么强烈推荐使用此方法，因为我们要吧项目放到服务器上，而服务器上是没有微软插件的，所以第二种行不通，并且使用此插件的话，速度更快，所以推荐使用此方法  
1）添加引用  
右键点击项目，找到“管理NuGet程序包”，弹出框，点浏览，再在输入框输入NPOI,点击面板上的安装，1.5M，下完就引入了 就可以使用了。  
引入命名空间

using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using NPOI.XSSF.UserModel;
    using System.IO;

public ActionResult DownloadFile(DateTime TranDate, int BranchCode)
    {
    try
    {
    BranchSaleBLL branchSaleBLL = new BranchSaleBLL();
    System.Data.DataTable dt = branchSaleBLL.GetSaleInfo(TranDate, BranchCode);
    if (dt.Rows.Count > 0)
    {
    //Excel文件名
    var excelName = dt.Rows[0][1].ToString() + TranDate.Year + '年' + TranDate.Month;
    string str = Server.HtmlEncode(Request.PhysicalApplicationPath).ToString() + "Excel\\" + excelName + "月销售情况.xls";
    if (System.IO.File.Exists(str))
    {
    //如果存在则删除
    System.IO.File.Delete(str);
    }
    
    DataToExcel.TableToExcel(dt, str);
    // System.Threading.Thread.Sleep(3000);
    return File(str, "application/vnd.ms-excel", excelName + "月销售情况.xls");
    }
    else
    {
    responseObject.StatusCode = (int)StatusCode.Fail;
    responseObject.Message = "无记录";
    responseObject.Data = null;
    }
    return Json(responseObject, JsonRequestBehavior.AllowGet);
    }
    catch
    {
    throw;
    }
    }

public static void TableToExcel(System.Data.DataTable dt, string file)
    {
     IWorkbook workbook;
     string fileExt = Path.GetExtension(file).ToLower();
     if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
     if (workbook == null) { return; }
     ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
    
     //表头 
     IRow row = sheet.CreateRow(0);
     for (int i = 0; i < dt.Columns.Count; i++)
     {
         ICell cell = row.CreateCell(i);
         cell.SetCellValue(dt.Columns[i].ColumnName);
     }
    
     //数据 
     for (int i = 0; i < dt.Rows.Count; i++)
     {
         IRow row1 = sheet.CreateRow(i + 1);
         for (int j = 0; j < dt.Columns.Count; j++)
         {
             ICell cell = row1.CreateCell(j);
             cell.SetCellValue(dt.Rows[i][j].ToString());
         }
     }
    
     //转为字节数组 
     MemoryStream stream = new MemoryStream();
     workbook.Write(stream);
     var buf = stream.ToArray();
    
     //保存为Excel文件 
     using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
     {
         fs.Write(buf, 0, buf.Length);
         fs.Flush();
     }
    }

**这三种方法都可以导出Excel，推荐第三种**