Vuejs+WebApi导出Excel

  1 public class SalesReportController : BaseController
  2 {
  3     private Serilog.ILogger _log = GetLogger<SalesReportController>();
  4     private readonly ISqlSugarClient _db;
  5     private IHostEnvironment _hostEnvironment;
  6 
  7     public SalesReportController(ISqlSugarClient db, IHostEnvironment hostEnvironment)
  8     {
  9         _db = db;
 10         _hostEnvironment = hostEnvironment;
 11     }
 12     /// <summary>
 13     /// 导出批次库存
 14     /// </summary>
 15     /// <param name="request"></param>
 16     /// <returns></returns>
 17     [HttpPost]
 18     public async Task<IActionResult> ExportStockBatchReportAsync(ListStockBatchReportAsyncRequest request)
 19     {
 20         if (string.IsNullOrWhiteSpace(request.CustNo))
 21         {
 22             return Fail(ApiResultMessage.ARGUMENTNULL);
 23         }
 24         try
 25         {
 26             #region 获取数据
 27             //1、获取数据
 28             List<StockBatchReportView> result = new List<StockBatchReportView>();
 29             string sql = @"select flowno, cust_no,    cust_name, id, product,    sheetno, [no], pricetype, CONVERT(VARCHAR(100),oper_date, 120) AS oper_date, sub_qty, sub_amt, avg_price, qty, amount, chg_qty, chg_amt, 
 30                 out_qty, ret_qty, close_qty, org_price
 31                 from v_stock_batch 
 32                 where cust_no = @cust_no and id = @productid";
 33             if (!string.IsNullOrWhiteSpace(request.No))
 34             {
 35                 sql += " and [no] like @no";
 36             }
 37             result = await _db.Ado.SqlQueryAsync<StockBatchReportView>(sql, new { cust_no = request.CustNo, productid = request.ProductId, no = "%" + request.No + "%" });
 38 
 39             #endregion
 40             #region 构建Excel
 41 
 42             //2、
 43             string rootPath = _hostEnvironment.ContentRootPath;
 44             if(!Directory.Exists("ExportFiles"))
 45             {
 46                 Directory.CreateDirectory("ExportFiles");
 47             }
 48             string fileName = $"代理商存货批次-{Guid.NewGuid()}.xlsx";
 49             string filePath = Path.Combine(rootPath, "ExportFiles", fileName);
 50             FileInfo file = new FileInfo(filePath);  //Path.Combine把多个字符串组成一个路径
 51             byte[] byteArray;
 52             ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
 53             using (ExcelPackage package = new ExcelPackage(file))   //ExcelPackage 操作excel的主要对象
 54             { 
 55 　　　　　　　　　 // 添加worksheet
 56                 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
 57                 //添加头
 58                 StockBatchReportExportView header = new StockBatchReportExportView()
 59                 {
 60                     FlowNo = "流水号",
 61                     Cust_No = "代理商号",
 62                     Cust_Name = "代理商名",
 63                     Product = "产品名称",
 64                     SheetNo = "批次号",
 65                     No = "锁(项目)号",
 66                     PriceType = "价格类型",
 67                     Oper_Date = "批次时间",
 68                     Sub_Qty = "批次数量",
 69                     Sub_Amt = "批次金额",
 70                     Avg_Price = "批次均价",
 71                     Qty = "销售数量",
 72                     Amount = "销售金额",
 73                     Chg_Qty = "变更数量",
 74                     Chg_Amt = "变更金额",
 75                     Out_Qty = "使用数量",
 76                     Ret_Qty = "返库数量",
 77                     Close_Qty = "结存数量",
 78                     Org_Price = "赠送原价"
 79                 };
 80 　　　　　　　　　 worksheet.Cells[1, 1].Value = header.FlowNo;
 81                 worksheet.Cells[1, 2].Value = header.Cust_No;
 82                 worksheet.Cells[1, 3].Value = header.Cust_Name;
 83                 worksheet.Cells[1, 4].Value = header.Product;
 84                 worksheet.Cells[1, 5].Value = header.SheetNo;
 85                 worksheet.Cells[1, 6].Value = header.No;
 86                 worksheet.Cells[1, 7].Value = header.PriceType;
 87                 worksheet.Cells[1, 8].Value = header.Oper_Date;
 88                 worksheet.Cells[1, 9].Value = header.Sub_Qty;
 89                 worksheet.Cells[1, 10].Value = header.Sub_Amt;
 90                 worksheet.Cells[1, 11].Value = header.Avg_Price;
 91                 worksheet.Cells[1, 12].Value = header.Qty;
 92                 worksheet.Cells[1, 13].Value = header.Amount;
 93                 worksheet.Cells[1, 14].Value = header.Chg_Qty;
 94                 worksheet.Cells[1, 15].Value = header.Chg_Amt;
 95                 worksheet.Cells[1, 16].Value = header.Out_Qty;
 96                 worksheet.Cells[1, 17].Value = header.Ret_Qty;
 97                 worksheet.Cells[1, 18].Value = header.Close_Qty;
 98                 worksheet.Cells[1, 19].Value = header.Org_Price;
 99                 //添加值
100                 int rownum = 2;
101                 foreach (var item in result)
102                 {
103                     worksheet.Cells["A" + rownum].Value = item.FlowNo;
104                     worksheet.Cells["B" + rownum].Value = item.Cust_No;
105                     worksheet.Cells["C" + rownum].Value = item.Cust_Name;
106                     worksheet.Cells["D" + rownum].Value = item.Product;
107                     worksheet.Cells["E" + rownum].Value = item.SheetNo;
108                     worksheet.Cells["F" + rownum].Value = item.No;
109                     worksheet.Cells["G" + rownum].Value = item.PriceType;
110                     worksheet.Cells["H" + rownum].Value = item.Oper_Date;
111                     worksheet.Cells["I" + rownum].Value = item.Sub_Qty;
112                     worksheet.Cells["J" + rownum].Value = item.Sub_Amt;
113                     worksheet.Cells["K" + rownum].Value = item.Avg_Price;
114                     worksheet.Cells["L" + rownum].Value = item.Qty;
115                     worksheet.Cells["M" + rownum].Value = item.Amount;
116                     worksheet.Cells["N" + rownum].Value = item.Chg_Qty;
117                     worksheet.Cells["O" + rownum].Value = item.Chg_Amt;
118                     worksheet.Cells["P" + rownum].Value = item.Out_Qty;
119                     worksheet.Cells["Q" + rownum].Value = item.Ret_Qty;
120                     worksheet.Cells["R" + rownum].Value = item.Close_Qty;
121                     worksheet.Cells["S" + rownum].Value = item.Org_Price;
122 
123                     rownum++;
124                 }
125                 //存储在本地
126                 package.Save();
127                 //转化为字节流下载
128                 //byteArray = package.GetAsByteArray();
129
130             }
131 
132             #endregion
133 
134             var memory = new MemoryStream();
135             using (var stream = new FileStream(filePath, FileMode.Open))
136             {
137                 stream.CopyTo(memory);
138             }
139             memory.Position = 0;
140             //string mimeType = "application/octet-stream";
141             string mimeType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
142             return File(memory, mimeType, Path.GetFileName(filePath));
143         }
144         catch (Exception ex)
145         {
146             _log.Error(ex.ToString());
147             return Fail(ex.ToString());
148         }
149     }
150 }

 1 import axios from 'axios'
 2 import { getToken, removeToken, removeName, removeId, removeGrant } from './auth'
 3 import {
 4     MessageBox,
 5     Toast,
 6     Indicator
 7 } from 'mint-ui'
 8 import router from '@/router/index'
 9 axios.interceptors.request.use((config) => {
10     if(['/api/api/Auth/Login'].indexOf(config.url) === -1) {
11         const token = getToken()
12         if(token) {
13             config.headers.Authorization = "Bearer " + token
14         } else {
15             removeToken()
16             removeId()
17             removeName()
18             removeGrant()
19             router.push({path: '/login'});//返回登录页
20         }
21     }
22     return config
23 }, (error) => {
24     return Promise.reject(error)
25 })
26 axios.interceptors.response.use((response) => {
27     return response
28 }, (error) => {
29     console.dir(error)
30     if(error.response.status === 401) {
31         removeToken()
32         removeId()
33         removeName()
34         removeGrant()
35         router.push({path: '/login'});//返回登录页
36     }
37     return Promise.reject(error)
38 })
39 //库存报表导出
40 export function ExportStockBatchReport(params) {
41     return axios.post(`/api/api/SalesReport/ExportStockBatchReport`, JSON.stringify(params), { responseType: 'blob' }).then((res) => {
42         return Promise.resolve(res.data)
43     }, (res) => {
44         judgeByMsg(res)
45     })
46 }

import { ExportStockBatchReport } from '@/common/script/api'
methods: {
    _exportStockBatchReport() {
            this.$indicator.open("数据处理中")
            return ExportStockBatchReport({
                CustNo: this.cust_No,
                ProductId: this.product_Id,
                No: this.no
            }).then((res) => {
                //返回的res是个file，需要实现下载的文件的动作
                let blob = new Blob([res], { type: res.type })
                if('download' in document.createElement('a')) {
                    let a = document.createElement('a')
                    a.href = URL.createObjectURL(blob)
                    a.download = '代理商存货批次.xlsx'
                    a.style.display = 'none'
                    document.body.appendChild(a)
                    a.click()
                    URL.revokeObjectURL(a.href)
                    document.body.removeChild(a)
                } else {
                    navigator.msSaveBlob(blob, '代理商存货批次.xlsx')
                }
             }).then(() => {
                setTimeout(() => {
                    this.$indicator.close();
                });
            });
        }
}