1-安装依赖
npm i node-xlsx
2-引入与使用
const xlsx = require("node-xlsx");
//导出填报统计列表
function exportTotalExcel(req, res, next) {
const err = validationResult(req);
if (!err.isEmpty()) {
const [{ msg }] = err.errors;
next(boom.badRequest(msg));
} else {
let { department, year, month } = req.query;
//设置没有作为搜索时，将值设置为空字符串
department = department ? department : "";
year = year ? year : "";
month = month ? month : '';
let queryApply = ` select e.userChinese, e.upDepartment, e.departChinese, a.percentage, m.operation, a.project, m.projectCode from apply a inner join filltotal e on a.year=e.year and a.month=e.month and a.username=e.username inner join manage m on a.project=m.project where (e.department like '%${department}%') and (e.year like '%${year}%') and (e.month like '%${month}%') and (e.status ='1')`
// const query = `select d.userChinese d.departChinese from filltotal d where (department like '%${department}%') and (year like '%${year}%') and (month like '%${month}%') and (status ='1') `;
querySql(queryApply).then((data) => {
if (!data || data.length === 0) {
res.json({
code: CODE_ERROR,
msg: "导出项目列表为空",
data: [],
});
} else {
let transData = [];
// transData.push(Object.keys(data[0])); // excel表格表头
transData.push(
[`${year ? year : '本'}年${month ? month : '本'}月项目工时与人工成本分摊表`, '', '', '', '', '', '', ''],
['员工与成本信息(HR填写)', '', '', '', '', '项目投入(业务方填写)', '', ''],
['姓名', '一级部门', '二级部门', '应分配总人天', '人工成本', '业务标签', '分摊项目', '项目编码']
)
data.forEach((item) => {
transData.push(Object.values(item));
});
transData.forEach((item,index)=>{
if(index > 2){
item.splice(3,0,'')
}
})
let creatDate = (year && month) ? ` ${year}年${month}月填报统计`: (year && !month) ? ` ${year}年填报统计`: (!year && month) ? ` 每年${month}月填报统计`: '填报统计';
let excelData = [ //数据源 { name: creatDate, data: transData, }, ];
// console.log(transData, "transData");
let sheetOptions = {
//表格大小
"!cols": [
{ wch: 10 },
{ wch: 20 },
{ wch: 10 },
{ wch: 15 },
{ wch: 10 },
{ wch: 10 },
{ wch: 30 },
{ wch: 15 },
],
// "!rows": [{ hpx: 40 }, { hpx: 20 }, { hpx: 50 }, { hpx: 30 }],
};
let range = { s: { c: 0, r: 0 }, e: { c: 7, r: 0 } }; // A1:H1
let range2 = { s: { c: 0, r: 1 }, e: { c: 4, r: 1 } }; // A1:D1
let range3 = { s: { c: 5, r: 1 }, e: { c: 7, r: 1 } }; // E1:H1
sheetOptions["!merges"] = [range, range2, range3];
res.setHeader(
"Content-Disposition",
"attachment; filename=" + encodeURIComponent(creatDate) + ".xlsx"
); //设置文件名称
res.send(xlsx.build(excelData, { sheetOptions }));
}
});
}
}
3-分析代码
3-1根据关联多表实现多表匹配，模糊查询---sql语句
let queryApply = ` select e.userChinese, e.upDepartment, e.departChinese, a.percentage, m.operation, a.project, m.projectCode from apply a inner join filltotal e on a.year=e.year and a.month=e.month and a.username=e.username inner join manage m on a.project=m.project where (e.department like '%${department}%') and (e.year like '%${year}%') and (e.month like '%${month}%') and (e.status ='1')`;
querySql(queryApply).then((data) => {})
首先是虚拟表，比如字母 e、a、m分别代表着表filltotal、apply、manage。select后面是在各表中需要的字段，这里也有顺序，哪个在前导出的表就在前面。主要是使用 inner join实现表关联，on后面是两表关联的要求，也就是在这种关系下筛选出来的filltotal表。同时apply表再次关联manage表，同样筛选出manage表。最后载通过where 实现filltotal表的模糊查询，也就是在filltotal再加一层筛选。
3-2处理sql执行完的数据
transData为空数组，之后push固定的元素，也就是说这些固定元素自己操作当作表头。对筛选出的data进行遍历，将Object.values(item)添加到transData中。利用splice遍历可以选择性的插入空值。
3-3封装excelData和sheetOptions
excelData里就是表名和数据源。sheetOptions里!cols代表某列宽度，!rows代表某行高度，!merges代表合并单元格，而range代表着坐标，想象s是一个点，e是一个点。
设置响应头，设置下载xlsx表名
res.setHeader()中设置，createData是变量
3-4返回给前端二进制流
res.send(xlsx.build(excelData, { sheetOptions }));需要注意使用send方法
4-修改样式问题
node-xlsx只支持合并单元格，而不能修改样式。但在xlsx-style中支持修改样式但不支持合并单元格。在网上找的方法是修改源码。
4-1下载相应的依赖包
"node-xlsx": "^0.14.1","xlsx-style": "^0.8.13"。这里我尝试过用最新版的，源码有所变化，所以下载的是跟作者相同的版本。
4-修改nodemodules下的node-xlsx和xlsx-style文件，www.jianshu.com/p/877631e7e… 方法链接，我按照作者只跟换部分js，不好使。就把整个文件夹替换了。可以实现样式改变
//导出填报统计列表
function exportTotalExcel(req, res, next) {
const err = validationResult(req);
if (!err.isEmpty()) {
const [{ msg }] = err.errors;
next(boom.badRequest(msg));
} else {
const query = `select * from filltotal`;
querySql(query).then((data) => {
if (!data || data.length === 0) {
res.json({
code: CODE_ERROR,
msg: "导出项目列表失败",
data: null,
});
} else {
let transData = [];
let mockData = [];
transData.push(Object.keys(data[0])); // excel表格表头
data.forEach((item) => {
transData.push(Object.values(item));
});
transData.map((v, i) => {
if (i === 0) {
const firstLine = [];
v.map((firstItem, i) => {
firstLine.push({
v: firstItem,
s: {
alignment: {
vertical: "center",
horizontal: "center",
},
font: {
size: 19,
bold: true,
color: { rgb: "ffffff" },
},
fill: {
fgColor: {
rgb: "a4a3a5",
},
},
},
});
});
mockData.push(firstLine);
} else {
const line = [];
v.map((item, i) => {
line.push({
v: item,
s: {
alignment: {
vertical: "center",
horizontal: "center",
},
font: {
size: 19,
color: { rgb: "ff280c" },
},
},
});
});
mockData.push(line);
}
});
let excelData = [ //数据源 { name: "填报统计", data: mockData, }, ];
console.log(mockData,'mockData')
let sheetOptions = { //表格大小
'!cols': [{ wch: 6 }, { wch: 2 }, { wch: 10 }, { wch: 15 },{ wch: 6 }, { wch: 2 }, { wch: 10 }, { wch: 15 }],
'!rows': [{ hpx: 40},{ hpx: 20},{ hpx: 50},{ hpx: 30}]
};
const range = {s: {c: 0, r: 0}, e: {c: 3, r: 0}}; // A1:D1
const range2 = {s: {c: 4, r: 0}, e: {c: 7, r: 0}}; // E1:H1
sheetOptions['!merges'] = [range,range2]
res.setHeader("Content-Disposition", "attachment; filename=" + encodeURIComponent("填报统计") + ".xlsx"); //设置文件名称
res.send(xlsx.build(excelData, sheetOptions));
}
});
}
}
对比上方两段代码。看出对数据的处理方式不同。
