前端导出Excel

1.首先,需要下载一个叫better-xlsx,的插件,以yarn 为例 ' yarn add better-xlsx --save '下载相关依赖包( npm 方式 ' npm i better-xlsx --save ')
新建一个html文件,引入如下文件

 2.在页面定义一个按钮,用于点击导出数据,书写导出代码

3.首先需要创建一个excel文件
let file = new xlsx.File()

  //创建一个sheet,如果有子表,还可以同样的方式创建子表

  let sheet = file.addSheet('sheet1');

  创建行,创建单元格 ,给单元格赋值 let row = sheet.addRow()  let cell = row.addCell()

 

前端导出Excel完整代码：

$(function () {
$('#btn').click(function () {
//首先创建一个xlsx文件
let file = new xlsx.File()
//创建一个sheet
let sheet = file.addSheet('sheet1');
let data = [
['Auto', 200, 90, 'B2-C2'],
['Entertainment', 200, 32, 'B3-C3'],
['Food', 350, 205.75, 'B4-C4'],
['Home', 300, 250, 'B5-C5'],
['Medical', 100, 35, 'B6-C6'],
['Personal Items', 300, 80, 'B7-C7'],
['Travel', 500, 350, 'B8-C8'],
['Utilities', 200, 100, 'B9-C9'],
['Other', 50, 60, 'B10-C10']
];
//创建sheet的头部行
let header = sheet.addRow();
//设置行高
header.setHeightCM(0.8);
let headers = ['张三', '李四', '王五', '张柳']
headers.forEach(item => {
//创建头部行中的单元格
let hc = header.addCell();
//给每个单元格设置值
hc.value = item;
//设置文本在单元格内水平垂直居中
hc.style.align.h = 'center';
hc.style.align.v = 'center';
//设置字体颜色
hc.style.font.color = 'ffffffff';
//给每个单元格设置边框和填充颜色
border(hc, 0, 0, 1, 0)
fillColor(hc, 1)
})



data.forEach((item, index) => {
//根据数据,创建对应个数的行
let row = sheet.addRow();
row.setHeightCM(0.8);
//创建对应个数的单元格.并填充值
//col1
let cell1 = row.addCell();
cell1.value = item[0]
cell1.style.align.v = 'center';
cell1.style.align.h = 'center';
if (index === 0) {
border(cell1, 1, 1, 0, 0)
} else {
border(cell1, 0, 0, 0, 1)
}
fillColor(cell1, 2)
//col2
let cell2 = row.addCell();
cell2.value = item[1]
cell2.numFmt = '$#,##0.00';
cell2.cellType = 'TypeNumeric';
cell2.style.align.v = 'center';
cell2.style.align.h = 'center';
if (index === 0) {
border(cell2, 1, 1, 0, 0)
} else {
border(cell2, 0, 0, 0, 1)
}
fillColor(cell2, 3)
//col3
let cell3 = row.addCell();
cell3.value = item[2]
cell3.numFmt = '$#,##0.00';
cell3.cellType = 'TypeNumeric';
cell3.style.align.v = 'center';
cell3.style.align.h = 'center';
if (index === 0) {
border(cell3, 1, 1, 0, 0)
} else {
border(cell3, 0, 0, 0, 1)
}
fillColor(cell3, 4)
//col4
let cell4 = row.addCell();
cell4.formula = item[3]
cell4.numFmt = '$#,##0.00';
cell4.cellType = 'TypeFormula';
cell4.style.align.v = 'center';
cell4.style.align.h = 'center';
if (index === 0) {
border(cell4, 1, 1, 0, 0)
} else {
border(cell4, 0, 0, 0, 1)
}
fillColor(cell4, 4)
})
for (var i = 0; i < 4; i++) {
//设置每列的宽度
sheet.col(i).width = 20;
}
file.saveAs('blob').then(function (content) {
saveAs(content, "example.xlsx");
});
})

//给单元格填充边框
function border(cell, top, right, bottom, left) {
const light = 'ffded9d4';
const dark = 'ff7e6a54';
cell.style.border.top = 'thin';
cell.style.border.topColor = top ? dark : light;
cell.style.border.left = 'thin';
cell.style.border.leftColor = left ? dark : light;
cell.style.border.bottom = 'thin';
cell.style.border.bottomColor = bottom ? dark : light;
cell.style.border.right = 'thin';
cell.style.border.rightColor = right ? dark : light;
// console.log(hc.style.border.topColor=0?2:3) 3
// console.log(hc.style.border.leftColor=0?2:3) 3
// console.log(hc.style.border.bottomColor=0?2:3)3
// console.log(hc.style.border.rightColor=1?2:3) 2
//也就是说给单元格设置边框颜色时,如果给边框赋值为0,则表示不给该边框赋值,转换为布尔值为false,反之则相反
}
//给单元格填充背景,前景色
function fillColor(cell, type) {
type = type || 0;
let colors = ['ffffffff', 'ffa2917d', 'ffe4e2de', 'fffff8df', 'fff1eeec']
// 0: white ,1: header, 2: first col, 3: second col, 4: gray,
cell.style.fill.patternType = 'solid';
cell.style.fill.fgColor = colors[type];
cell.style.fill.bgColor = 'ffffffff';
}

})