先看下导出表格的格式
目前能做到只能把从第二行到第五行都排在前面,却不能分开部分两行排在后面比如总数和合计应该排在最后一行的右边。
原因可能在要循环渲染的产品数据。
后端 先安装npm插件
npm install exceljs
在app/service/下新建common.js
'use strict';
const Service = require('egg').Service;
const Excel = require('exceljs');
class CommonService extends Service {
/**
* 数据并生成excel
* @param {Array} headers excel标题栏
* @param {Array} param 数据参数
* @param {string} name 文件名称
*/
async excelCommon(headers, data, name) {
let columns = []; //exceljs要求的columns
let hjRow = {}; //合计行
let titleRows = headers.length;// 标题栏行数
//处理表头
for (let i = 0; i < titleRows; i++) {
let row = headers[i];
//console.log(row)
for (let j = 0, rlen = row.length; j < rlen; j++) {
let col = row[j];
let { f, t, w = 12 } = col;
if (!f) continue;// 不存在f则跳过
if (col.totalRow) hjRow[f] = true;
if (col.totalRowText) hjRow[f] = col.totalRowText;
col.style = { alignment: { vertical: 'middle', horizontal: 'center' } };
col.header = t;
col.key = f;
col.width = w;
columns.push(col);
}
}
let workbook = new Excel.Workbook();
let sheet = workbook.addWorksheet('My Sheet', { views: [{ xSplit: 1, ySplit: 1 }] });
sheet.columns = columns;
sheet.addRows(data);
// 处理复杂表头
if (titleRows > 1) {
for (let i = 1; i < titleRows; i++) sheet.spliceRows(1, 0, []);// 头部插入空行
for (let i = 0; i < titleRows; i++) {
let row = headers[i];
for (let j = 0, rlen = row.length; j < rlen; j++) {
let col = row[j];
if (!col.m1) continue;
sheet.getCell(col.m1).value = col.t;
sheet.mergeCells(col.m1 + ":" + col.m2);
}
}
}
// 处理样式、日期、字典项
sheet.eachRow(function (row, rowNumber) {
// 设置行高
row.height = 20;
row.eachCell({ includeEmpty: true }, (cell)=> {
// 设置边框 黑色 细实线
const top = { style: 'thin', color: { argb: '000000' } };
const left = { style: 'thin', color: { argb: '000000' } };
const bottom = { style: 'thin', color: { argb: '000000' } };
const right = { style: 'thin', color: { argb: '000000' } };
cell.border = { top, left, bottom, right };
// 设置标题部分为粗体
if (rowNumber <= titleRows) { cell.font = { bold: true }; return; }
});
});
this.ctx.set('Content-Type', 'application/vnd.openxmlformats');
// 这个地方的空格不要更改
this.ctx.set('Content-Disposition', "attachment;filename*=UTF-8' '" + encodeURIComponent(name) + '.xlsx');
this.ctx.body = await workbook.xlsx.writeBuffer();
}
}
module.exports = CommonService;
自己代码控制器下
async orderExcel() {
const { ctx, app, service } = this;
let sorder_id = ctx.query.id;
//格式化从sequelize获取到数据
let data = goods.map( item => {
return {
detail_id: item.detail_id,
gname: item.write_offer.gname,
gcode: item.write_offer.gcode,
cars: item.write_offer.cars,
nums: item.nums,
gunit: item.write_offer.gunit,
out_price: item.out_price,
tal_price: item.tal_price,
library_no: item.write_offer.library_no,
brands: item.write_offer.brands
}
})
let headers = [
[{ t: '销售出库单', m1:'A1',m2:'J1'}],
[{ t: info, m1:'A2',m2:'J2'}],
[{ t: detail, m1:'A3',m2:'J3'}],
[{ t: shop, m1:'A4',m2:'J4'}],
[{ t: sales, m1:'A5',m2:'J5'}],
[
{ t: '序号', f: 'detail_id', totalRow: true },
{ t: '配件名称', f: 'gname', totalRow: true },
{ t: '配件编码', f: 'gcode', totalRow: true },
{ t: '车型', f: 'cars', totalRow: true },
{ t: '数量', f: 'nums', totalRow: true },
{ t: '单位', f: 'gunit', totalRow: true },
{ t: '单价', f: 'out_price', totalRow: true },
{ t: '金额', f: 'tal_price', totalRow: true },
{ t: '货架', f: 'library_no', totalRow: true },
{ t: '品牌', f: 'brands', totalRow: true },
],
];
// let data = [{ trade_time: '2020-12-10', order_type:'线上' },] // 需要在这边自己适配数据,这边为空
await service.common.excelCommon(headers, data, '销售出库单'+res.order_sn);
}
前端部分 截图
难在前端部分该怎么从后端获取,目前只能做个a 标签
<a :href="alink+row.sorder_id">导出</a>
感谢的分享