小Q的博客

  • 首页
  • net编程
    • 产品和框架
    • 开发实例
    • 经验技巧
    • 开源组件
  • wp独立站
  • 自媒体
  • 日记本
  • 工具箱
每个程序员,都应该有一个自己的博客站
  1. 首页
  2. net编程
  3. 经验技巧
  4. 正文

c#MVC项目中基于Aspose.Cells组件,实现Excel导出

2023年11月16日 1600点热度 0人点赞 0条评论

最近做项目,用到了一个功能,就是要把Web报表导出至Excel文件,网上找了些资料,东拼西凑的把需求搞定,这篇算是一个总结和整理。网上有的贴子只有后端代码、有的只有前端代码,这篇前端和后端都有,不用再去别的地方找了。我用的组件是Aspose.Cells,推荐使用

前端代码
比较简单同,一句话就能搞定,以前还试过Ajax访问等,其实都不用。只要用window.open方法即可,参考代码,直接跳转至后端的Controller方法即可

var href = '/Module/ExportTrustGoodsStockAnalyse?selectedSource=' + selectedSource + "&stockQtyPeriod=" + stockQtyPeriod + "&selectedStockSku=&orderBy=" + orderBy;
window.open(href);

后端代码
其实和Winform代码一样,都是先创建一个WorkBook对象,再创建WorkSheet,然后填充Cell单元格数据。最后要把这个WorkBook对象转成二进制流就行了

public ActionResult ExportTrustGoodsStockAnalyse(string selectedSource, string stockQtyPeriod, string selectedStockSku, string orderBy)
{
    //先调用其他方法,获取业务数据
    ContentResult cr = this.CalcTrustGoodsStockAnalyse(selectedSource, stockQtyPeriod, selectedStockSku, orderBy) as ContentResult;
    FormReturnLayUIInfo info = JsonHelper.GetObjectByJson<FormReturnLayUIInfo>(cr.Content);
    List<TrustGoodsStockAnalyseModel> rows = JsonHelper.GetObjectByJson<List<TrustGoodsStockAnalyseModel>>(info.data.ToString());

    //创建Excel工作簿
    Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();

    //添加工作表
    Aspose.Cells.Worksheet ws = wb.Worksheets["Sheet1"];
    if (ws == null)
        ws = wb.Worksheets.Add("Sheet1");            

    //先插入表头
    ws.Cells[0, 0].Value = "序号";
    ws.Cells[0, 1].Value = "图片";
    ws.Cells[0, 2].Value = "平台";
    ws.Cells[0, 3].Value = "单品编码";
    ws.Cells[0, 4].Value = "单品名称";
    ws.Cells[0, 5].Value = "今日销售数";
    ws.Cells[0, 6].Value = "7日销售数";
    ws.Cells[0, 7].Value = "当月销售数";
    ws.Cells[0, 8].Value = "平台库存数";
    ws.Cells[0, 9].Value = "在途库存数";
    ws.Cells[0, 10].Value = "本地库存数";
    ws.Cells[0, 11].Value = "建议预配数";
    ws.Cells[0, 12].Value = "导入日期";
    ws.Cells.SetColumnWidth(1, 14);
    ws.Cells.SetColumnWidth(12, 18.56);

    //继续插入数据
    int rowIndex = 1;
    for(int i = 0; i < rows.Count; i++)
    {
        TrustGoodsStockAnalyseModel row = rows[i];

        //先插入主表
        ws.Cells[rowIndex, 0].Value = i + 1;
        //ws.Cells[rowIndex, 1].Value = "";
        ws.Cells[rowIndex, 2].Value = "所有平台";
        ws.Cells[rowIndex, 3].Value = row.GoodsCode;
        ws.Cells[rowIndex, 4].Value = row.GoodsName;
        ws.Cells[rowIndex, 5].Value = row.SaleQtyToToday;
        ws.Cells[rowIndex, 6].Value = row.SaleQtyTo7Day;
        ws.Cells[rowIndex, 7].Value = row.SaleQtyTo30Day;
        ws.Cells[rowIndex, 8].Value = row.PlatStockQty;
        ws.Cells[rowIndex, 9].Value = row.OnPassageQty;
        ws.Cells[rowIndex, 10].Value = row.GoodsStockQty;
        ws.Cells[rowIndex, 11].Value = row.SuggestQty;
        ws.Cells[rowIndex, 12].Value = row.ImportDateText;
        ws.Cells.SetRowHeight(rowIndex, 104);

        //插入图片
        try
        {
            string imageUrl = row.GoodsImageUrl;
            var byteImage = NS.Core.UrlHelper.DownloadData(imageUrl);
            System.IO.MemoryStream ms = new System.IO.MemoryStream(byteImage);

            int iIndex = ws.Pictures.Add(rowIndex, 1, ms);
            Aspose.Cells.Drawing.Picture pic = ws.Pictures[iIndex];
            pic.Placement = Aspose.Cells.Drawing.PlacementType.FreeFloating;
            pic.Height = 130;
            pic.Width = 100;
        }
        catch(Exception ex)
        {

        }

        rowIndex++;
    }

    ////设置响应头信息
    //Response.Clear();
    //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    //Response.AddHeader("content-disposition", "attachment; filename=persons.xlsx");

    //将Excel文件写入响应流
    byte[] fileStream = wb.SaveToStream().ToArray();
    string fileName = "全托管商品备货分析" + DateTime.Now.ToString("yyMMdd-HHmm") + ".xls";
    return File(fileStream, "application/octet-stream", fileName);
}

 

标签: Aspose.Cells Excel导出 MVC 前端代码 后端代码
最后更新:2023年11月16日

小Q

80后中年不油腻大叔,喜欢编写代码、打羽毛球、做木制玩具。目前定居浙江杭州

打赏 点赞
< 上一篇
下一篇 >

文章评论

razz evil exclaim smile redface biggrin eek confused idea lol mad twisted rolleyes wink cool arrow neutral cry mrgreen drooling persevering
取消回复

COPYRIGHT © 2022 小Q的博客. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

浙ICP备2022019157号-2