- 相信大家在项目中难免会遇到一些关于数据导出的问题,今天就给大家带来一个相对简单的一种导出数据格式为excel格式的方法
- 首先,需要下载一个叫better-xlsx,的插件,以yarn 为例 ' yarn add better-xlsx --save '下载相关依赖包( npm 方式 ' npm i better-xlsx --save ')
- 新建一个html文件,引入如下文件
- 在页面定义一个按钮,用于点击导出数据,书写导出代码
- 首先需要创建一个excel文件
let file = new xlsx.File()
//创建一个sheet,如果有子表,还可以同样的方式创建子表
let sheet = file.addSheet('sheet1');
-
创建行,创建单元格 ,给单元格赋值 let row = sheet.addRow() let cell = row.addCell()
-
以下是完整代码,每一步的注释我都写了,就不在这里用文字在叙述一遍了,有不明白的欢迎大家留言.
$(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'; } }) - 最后附上该插件文档地址 https://d-band.github.io/better-xlsx/class/src/cell.js~Cell.html
效果图
10.附上一个带多个sheet页的案例
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
<script type="text/javascript" src="./node_modules/jszip/dist/jszip.min.js"></script>
<script type="text/javascript" src="./node_modules/better-xlsx/dist/xlsx.js"></script>
<script type="text/javascript" src="./node_modules/jszip/vendor/FileSaver.js"></script>
<script src="./node_modules/jquery/dist/jquery.min.js"></script>
</head>
<body>
<div style="margin:40 auto;position: relative">
<span style="display:block;width:80px;height: 40px;line-height: 40px;background-color: #0094ff;position: absolutez;color: #fff;text-align:center;cursor: pointer"
id="btn">导出报表</span>
</div>
</body>
<script>
$(function () {
let data = [{
'keyColumn': '姓名',
'mainID': 'UUID001',
'姓名': '张三',
'性别': '男',
'班级': '高三（ 1） 班',
'children': [{
'tname': '课程',
'dataList': [{
'reMainID': 'UUID001',
'课程': '语文',
'老师': '张老师',
'分数': 89
}, {
'reMainID': 'UUID001',
'课程': '数学',
'老师': '王老师',
'分数': 92
}, {
'reMainID': 'UUID001',
'课程': '英语',
'老师': '李老师',
'分数': 93
}]
},
{
'tname': '爱好',
'dataList': [{
'reMainID': 'UUID001',
'爱好': '足球',
'活动': '比赛',
'时间': '7 月1日'
}, {
'reMainID': 'UUID001',
'爱好': '跑步',
'活动': '马拉松',
'时间': '2 月4日'
}]
}
]
}, {
'keyColumn': '姓名',
'mainID': 'UUID002',
'姓名': '李四',
'性别': '女',
'班级': '高三（15） 班',
'children': [{
'tname': '课程',
'dataList': [{
'reMainID': 'UUID002',
'课程': '语文',
'老师': '网老师',
'分数': 89
}, {
'reMainID': 'UUID002',
'课程': '数学',
'老师': '找老师',
'分数': 92
}, {
'reMainID': 'UUID002',
'课程': '英语',
'老师': '李老师',
'分数': 93
}]
},
{
'tname': '爱好',
'dataList': [{
'reMainID': 'UUID002',
'爱好': '篮球',
'活动': '比赛',
'时间': '7 月8日'
}, {
'reMainID': 'UUID002',
'爱好': '美术',
'活动': '马拉松',
'时间': '2 月8日'
}]
}
]
}, ]
$('#btn').click(function () {
//首先创建一个xlsx文件
let file = new xlsx.File()
//创建一个sheet
let sheet = file.addSheet('sheet1');
//创建sheet的头部行
let header = sheet.addRow();
//存储头部单元格
let headArr = []
//设置行高
header.setHeightCM(0.8);
for (subitem in data[0]) {
if (subitem != 'children' && subitem != 'keyColumn') {
headArr.push(subitem)
}
}
console.log(headArr)
//主表顶部一栏
headArr.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)
})
//主表body部分
let bodyArr = []
for (let i = 0; i < data.length; i++) {
let bodyObj = {}
for (let subitem in data[i]) {
// console.log(data[i])
if (subitem != 'children' && subitem != 'keyColumn') {
// console.log(data[i][subitem])
bodyObj[subitem] = data[i][subitem]
}
}
bodyArr.push(bodyObj)
}
bodyArr.forEach(item => {
//根据数据,创建对应个数的行
let row = sheet.addRow();
row.setHeightCM(0.8);
//创建对应的列
headArr.forEach((it, index) => {
let cell = 'cell' + index
cell = row.addCell()
cell.value = item[it]
cell.style.align.v = 'center';
cell.style.align.h = 'center';
if (index === 0) {
border(cell, 1, 1, 0, 0)
} else {
border(cell, 0, 0, 0, 1)
}
fillColor(cell, 2)
})
})
for (let i = 0; i < headArr.length; i++) {
sheet.col(i).width = 20;
}
//导出子表
//创建子表名
let subArr = []
if (data[0].children) {
data[0].children.forEach(it => {
subArr.push(it.tname)
})
}
let subId = []
data.forEach(item => {
let subNa = {}
subNa.id = item.mainID
subNa.name = item[item.keyColumn]
subId.push(subNa)
})
console.log(subId)
subArr.forEach((item, index) => {
let sheet = file.addSheet(item);
//每创建一个sheet,就要知道这个sheet中有几个人填报
//有几个人填报就有几张表,有几个大对象就有几个人填报
//每张表有一个连头,还有一个真正的头部
for (let i = 0; i < data.length; i++) {
let NameAndId = ['姓名', subId[i].name, '关联主数据id', subId[i].id];
let headerTop = sheet.addRow();
headerTop.setHeightCM(0.8);
for (let i = 0; i < 4; i++) {
let cellTop = headerTop.addCell();
//设置文本在单元格内水平垂直居中
cellTop.style.align.h = 'center';
cellTop.style.align.v = 'center';
cellTop.value = NameAndId[i];
border(cellTop, 0, 0, 1, 0)
fillColor(cellTop, 1)
}
for (let i = 0; i < 4; i++) {
sheet.col(i).width = 20;
}
//次级顶部栏
let header = sheet.addRow();
header.setHeightCM(0.8);
let subHeadName = []
for (j in data[0].children[index].dataList[0]) {
if (j != 'reMainID') {
subHeadName.push(j)
}
}
console.log(subHeadName)
subHeadName.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, 2)
})
//表身体
data[i].children.forEach((ev, ins) => {
//循环subArr中的index 表示数据在哪个sheet中显示
//subArr中的第一个for循环中的i 表示数据在哪个子表中显示
if (ev.tname === item && index == ins) {
ev.dataList.forEach(es => {
let subBody = sheet.addRow();
subBody.setHeightCM(0.8);
let subBodyArr = []
for (let value in es) {
if (value != 'reMainID') {
subBodyArr.push(es[value])
}
}
console.log(subBodyArr)
subBodyArr.forEach(item => {
//创建头部行中的单元格
let hc = subBody.addCell();
//给每个单元格设置值
hc.value = item;
//设置文本在单元格内水平垂直居中
hc.style.align.h = 'center';
hc.style.align.v = 'center';
//设置字体颜色
hc.style.font.color = '00000000';
//给每个单元格设置边框和填充颜色
border(hc, 0, 0, 1, 0)
fillColor(hc, 3)
})
})
}
})
}
})
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;
}
//给单元格填充背景,前景色
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';
}
})
</script>
</html>以供大家参考,如有更好的方法,恳请各位大佬赐教
4230
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
