前后端分离，前端Vuejs，后端.Net6 WebApi
后端代码
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 }
vue端代码
api.js页面
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();
});
});
}
}
标签：WebApi,header,Vuejs,worksheet,Cells,Excel,Value,item,rownum From： https://www.cnblogs.com/wangyulong/p/17603327.html