vue2前端导出带背景色表格 xlsx xlsx-style
vue2 +elmentui+xlsx10.0.0+xlsx-style
坑有点多。
xlsx10.0.0以后的版本 用require导入或者使用什么导入什么，不要import * xlsx全部导入 ,不然jszip组件报错找不到。
配置文件：
const { defineConfig } = require('@vue/cli-service')
module.exports = defineConfig({
transpileDependencies: true,
configureWebpack: {resolve:{
fallback: {
fs: false,
},
},
externals:{
'./cptable': 'var cptable'
},
}
})
改xlsx-style源码：
参考
js代码：
<script>
// @ is an alias to /src
import { utils } from 'xlsx';
import XLSXS from "xlsx-style";
import FileSaver from "file-saver";
export default
{
name: 'HomeView',
data()
{
return {
tableList: [
{
date: '2016-05-02',
name: '王小虎',
address: '上海市普陀区金沙江路 1518 弄',
id: 0,
},
{
date: '2016-05-04',
name: '王小虎',
address: '上海市普陀区金沙江路 1517 弄',
id: 1,
},
{
date: '2017-05-01',
name: '王小虎',
id: 2,
address: '上海市普陀区金沙江路 1511111 弄'
},
{
date: '2016-05-03',
name: '王小虎',
address: '上海市普陀区金沙江路 1516 弄',
id: 3
}, ]
}
},
methods:
{
s2ab(s)
{
var buf = new ArrayBuffer(s.length);
var view = new Uint8Array(buf);
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
return buf;
},
print()
{
let wb = utils.table_to_book(document.querySelector("#table1"), { sheet: "分组表" });
this.setExlStyle(wb["Sheets"]["分组表"]);
this.setbgColor(wb["Sheets"]["分组表"]);
console.log(wb);
var ws = XLSXS.write(wb,
{
type: "binary",//node.js6以上，写buffer会报nodeBuffer不支持
});
try
{
FileSaver.saveAs(
new Blob([this.s2ab(ws)], { type: "application/octet-stream" }),
'5.xlsx'
);
}
catch (e)
{
if (typeof console !== "undefined")
console.log(e, ws);
}
return ws;
},
setExlStyle(data)
{
let borderAll = {
//单元格外侧框线
top:
{
style: "thin",
},
bottom:
{
style: "thin",
},
left:
{
style: "thin",
},
right:
{
style: "thin",
},
};
data["!cols"] = [];
for (let key in data)
{
if (data[key] instanceof Object)
{
data[key].s = {
border: borderAll,
alignment:
{
horizontal: "center", //水平居中对齐
vertical: "center",
},
font:
{
sz: 11,
},
bold: true,
numFmt: 0,
};
data["!cols"].push({ wpx: 115 });
}
}
return data;
},
//根据条件改变背景
setbgColor(ws)
{
let borderAll = {
//单元格外侧框线
top:
{
style: "thin",
},
bottom:
{
style: "thin",
},
left:
{
style: "thin",
},
right:
{
style: "thin",
},
};
let arr = ["A", "B", "C", "D"];
for (let i = 1; i <= this.tableList.length; i++)
{
for (let key in arr)
{
var key1 = 'A' + i;
var key2 = arr[key] + i;
var val = ws[key1];
if (val.v % 2 == 0)
{
ws[key2].s = {
border: borderAll,
alignment:
{
horizontal: "center", //水平居中对齐
vertical: "center",
},
bold: true,
numFmt: 0,
font:
{
name: 'Arial',
sz: 10,
bold: true,
color: { rgb: "FFFFFFFF" }
},
fill:
{
//背景色
bgColor: { rgb: "FF59AB44" },
fgColor: { rgb: "FF59AB44" },
},
}
}
}
}
return ws;
}
}
