Angular前端xlsx-style导出Excel
2020-10-26 本文已影响0人
我爱福尔摩斯呀
项目需要在前端导出Excel，数据导出很容易，但是导出表格的样式设置折腾了好久终于搞定。记录如下：
1、在Angular中安装xlsx和xlsx-style
"file-saver": "^1.3.3",
"xlsx": "^0.10.5",
"xlsx-style": "^0.8.13",
2、实现代码
//表格样式
defaultCellStyle = {
font: {
name: "宋体", sz: 9, color: { auto: 1 } , bold: true
},
border: {
color: { auto: 1 },
top: { style: 'thin' },
bottom: { style: 'thin'},
left: { style: 'thin' },
right: { style: 'thin' }
},
alignment: {
/// 自动换行
wrapText: 1,
// 居中
horizontal: "center",
vertical: "center",
indent: 0
}
};
titleStyle = {
font:{name:"宋体",sz:22,bold:true},
alignment: {
wrapText: 1,
horizontal: "center",
vertical: "center",
indent: 0
}};
subTitleStyle = {
font:{name:"宋体",sz:9},
alignment: {
wrapText: 1,
horizontal: "right",
vertical: "center",
indent: 0
}
};
commonStyle = {
font:{name:"宋体",sz:9},
border: {
color: { auto: 1 },
top: { style: 'thin' },
bottom: { style: 'thin'},
left: { style: 'thin' },
right: { style: 'thin' }
},
alignment: {
wrapText: 1,
horizontal: "center",
vertical: "center",
indent: 0
}};
//导出文件
exportFilterExcel() {
// 表头信息 要合并的字段用null代替
let aoa = [
['内江市2020年1-4月重点项目完成情况表（续建）'],
['单位：个、亿元、亩'],
['序号', '项目名称', '建设地址', '牵头领导','责任单位','业主单位','开工年月','竣工年月','总投资','建设规模及内容','预计2020年底累计完成投资','2021年',null,'2020年1-4月',null,null,'已落实土地','已落实资金','存在的问题','备注'],
[null, null, null, null, null, null, null, null, null, null, null, '预计投资', '计划达到的形象进度', '累计完成投资', '占年计划%', '截至本月达到的形象进度',null,null,null,null],
];
let sheet = this.sheet_from_array_of_arrays(aoa);
// 表头合并： r: row 行；c:column 列
const mergeTitle = [
{ s: { c: 0, r: 0 }, e: { c: 19, r: 0 } },
{ s: { c: 0, r: 1 }, e: { c: 19, r: 1 } },
{ s: { c: 0, r: 2 }, e: { c: 0, r: 3 } },
{ s: { c: 1, r: 2 }, e: { c: 1, r: 3 } },
{ s: { c: 2, r: 2 }, e: { c: 2, r: 3 } },
{ s: { c: 3, r: 2 }, e: { c: 3, r: 3 } },
{ s: { c: 4, r: 2 }, e: { c: 4, r: 3 } },
{ s: { c: 5, r: 2 }, e: { c: 5, r: 3 } },
{ s: { c: 6, r: 2 }, e: { c: 6, r: 3 } },
{ s: { c: 7, r: 2 }, e: { c: 7, r: 3 } },
{ s: { c: 8, r: 2 }, e: { c: 8, r: 3 } },
{ s: { c: 9, r: 2 }, e: { c: 9, r: 3 } },
{ s: { c: 10, r: 2 }, e: { c: 10, r: 3 } },
{ s: { c: 11, r: 2 }, e: { c: 12, r: 2 } },
{ s: { c: 13, r: 2 }, e: { c: 15, r: 2 } },
{ s: { c: 16, r: 2 }, e: { c: 16, r: 3 } },
{ s: { c: 17, r: 2 }, e: { c: 17, r: 3 } },
{ s: { c: 18, r: 2 }, e: { c: 18, r: 3 } },
{ s: { c: 19, r: 2 }, e: { c: 19, r: 3 } }
]
sheet['!merges'] = mergeTitle;
// 冻结前6行和第一列，右下可以滑动
sheet["!freeze"] = {
xSplit: "1",
ySplit: "6",
topLeftCell: "B7",
activePane: "bottomRight",
state: "frozen",
}
sheet["!margins"] = { left: 1.0, right: 1.0, top: 1.0, bottom: 1.0, header: 0.5, footer: 0.5 }
// 列宽 使用的不是像素值
const sheetCols = [
{ wch: 8} ,
{ wch: 24 },
{ wch: 20 },
{ wch: 9 },
{ wch: 8 },
{ wch: 18 },
{ wch: 15 },
{ wch: 9 },
{ wch: 9 },
{ wch: 12 },
{ wch: 9 },
{ wch: 9 },
{ wch: 10 },
{ wch: 10 },
{ wch: 10 },
{ wch: 27 },
];
sheet['!cols'] = sheetCols;
this.addRangeBorder(mergeTitle, sheet);
const wbBlob = this.sheet2blob(sheet, '续建')
// 保存下载
FileSaver.saveAs(wbBlob, 'd.xlsx')
}
//生成表头
sheet_from_array_of_arrays(data) {
const ws = {};
const range = {s: {c:10000000, r:10000000}, e: {c:0, r:0 }};
for(let R = 0; R < data.length; R++) {
for(let C = 0; C < data[R].length; C++) {
if(range.s.r > R) range.s.r = R;
if(range.s.c > C) range.s.c = C;
if(range.e.r < R) range.e.r = R;
if(range.e.c < C) range.e.c = C;
/// 这里生成cell的时候，使用上面定义的默认样式
let cell;
switch (R) {
case 0:
cell = {v: data[R][C], s: this.titleStyle};
break;
case 1:
cell = {v: data[R][C], s: this.subTitleStyle};
break;
case 2: case 3:
cell = {v: data[R][C], s: this.defaultCellStyle};
break;
default:
cell = {v: data[R][C], s: this.commonStyle};
break;
}
if(cell['v'] == null) continue;
const cell_ref = XLSX.utils.encode_cell({c:C,r:R});
/* TEST: proper cell types and value handling */
if(typeof cell['v'] === 'number') {
cell['t'] = 'n';
}
else if(typeof cell['v'] === 'boolean') cell['t']= 'b';
else if(cell['v'] instanceof Date) {
cell['t'] = 'n';
cell['z'] = XLSX.SSF._table[19];
cell['v'] = cell.v;
}
else cell['t'] = 's';
ws[cell_ref] = cell;
}
}
/* TEST: proper range */
if(range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
const workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = sheet
window.console.log(workbook)
// 生成excel的配置项
const wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
const wbout = XLSX.write(workbook, wopts, { defaultCellStyle: this.commonStyle });
const blob = new Blob([s2ab(wbout)], {type: "application/octet-stream"});
// 字符串转ArrayBuffer
function s2ab(s) {
const buf = new ArrayBuffer(s.length);
const view = new Uint8Array(buf);
for (let i=0; i!==s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
}
return blob;
}
//合并的单元格边框无效，所以需要重新进行设置
addRangeBorder(range,ws){
let arr = ["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"];
range.forEach(item=>{
let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);
let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);
const test = ws[arr[startRowNumber] + (startColNumber + 1)];
for(let col = startColNumber ; col <= endColNumber ; col++)
{
for(let row = startRowNumber; row <= endRowNumber ; row++)
{
ws[arr[row] + (col + 1)] = test;
}
}
})
return ws;
}
3、运行后报错：
Can't resolve 'fs' in '/node_modules/xlsx-style/ods.js'
解决方法：在package.json中加入
"browser": {
"fs": false
}
4、运行结果
image.png
