官网：
1、xlsx-js-style xlsx-js-style | xlsx-js-style homepage
2、xlsx SheetJS 中文网
一、使用第三方插件
1、安装
npm install xlsx-js-style
2、引入
import xlsx from 'xlsx-js-style'
xlsx插件是基础的导出，不可以修改样式，直接xlsx-style插件式修改样式的，所以这里直接用二者合体插件即可
二、页面使用
1、数据源
[{"rectifyPresideName": "朱佳佳","accepUser": "张红艳","rectifyCompleteTime": "2024-09-27 10:17:43","checkTableType": "重点时段及节假日前排查","rectifyTerm": "2024-09-27 23:59:59","rectifyDeptName": "炭黑厂炭黑车间","rectifyPlan": "建立使用记录","faultName": "岗位急救箱无使用记录","createDate": "2024年09月27日"},{"rectifyPresideName": "崔国梁","accepUser": "崔国梁","rectifyCompleteTime": "2024-09-26 17:13:05","checkTableType": "重点时段及节假日前排查","rectifyTerm": "2024-09-26 23:59:59","rectifyDeptName": "焦化厂第三干熄焦车间","rectifyPlan": "更换或检查修复","faultName": "疏散指示灯不亮","createDate": "2024年09月26日"},{"rectifyPresideName": "贾成成","accepUser": "贾成成","rectifyCompleteTime": "2024-09-26 17:23:12","checkTableType": "重点时段及节假日前排查","rectifyTerm": "2024-09-26 23:59:59","rectifyDeptName": "甲醇厂公辅车间","rectifyPlan": "安排岗位人员更换消防抢","faultName": "消防水枪损坏","createDate": "2024年09月26日"},{"rectifyPresideName": "王小娟","accepUser": "李学红","rectifyCompleteTime": "2024-09-25 16:50:45","checkTableType": "重点时段及节假日前排查","rectifyTerm": "2024-09-25 23:59:59","rectifyDeptName": "苯精制厂","rectifyPlan": "安装阀门","faultName": "萃取塔回流罐液位计排污阀为单阀","createDate": "2024年09月24日"}
]2、将数据源转成需要的二维数组
const data = res.data.data.map((ele, i) => （{xuhao: i + 1,faultName: ele.faultName,createDate: ele.createDate,checkTableType: ele.checkTableType,rectifyPlan: ele.rectifyPlan,rectifyDeptName: ele.rectifyDeptName,rectifyPresideName: ele.rectifyPresideName,rectifyTerm: ele.rectifyTerm,rectifyCompleteTime: ele.rectifyCompleteTime,accepUser: ele.accepUser,
}))
const body = data.map((x) => [x.xuhao,x.faultName,x.createDate,x.checkTableType,x.rectifyPlan,x.rectifyDeptName,x.rectifyPresideName,x.rectifyTerm,x.rectifyCompleteTime,x.accepUser,])// 转换后的二维数据
[[1,"岗位急救箱无使用记录","2024年09月27日","重点时段及节假日前排查","建立使用记录","炭黑厂炭黑车间","朱佳佳","2024-09-27 23:59:59","2024-09-27 10:17:43","张红艳"],[2,"疏散指示灯不亮","2024年09月26日","重点时段及节假日前排查","更换或检查修复","焦化厂第三干熄焦车间","崔国梁","2024-09-26 23:59:59","2024-09-26 17:13:05","崔国梁"],[3,"消防水枪损坏","2024年09月26日","重点时段及节假日前排查","安排岗位人员更换消防抢","甲醇厂公辅车间","贾成成","2024-09-26 23:59:59","2024-09-26 17:23:12","贾成成"],[4,"萃取塔回流罐液位计排污阀为单阀","2024年09月24日","重点时段及节假日前排查","安装阀门","苯精制厂","王小娟","2024-09-25 23:59:59","2024-09-25 16:50:45","李学红"]
]3、定义表头
// 定义Excel表头const header = [['隐患排查治理台账'],['序号','问题点','检查时间','检查类别','整改措施','责任单位','责任人','整改时间','','验收人',],['', '', '', '', '', '', '', '计划完成时间', '实际完成时间', ''],]4、将定义好的表头添加到 body 中
body.unshift(...header)
5、创建虚拟的 workbook
const workbook = xlsx.utils.book_new()6、aoa_to_sheet 将二维数组转成 sheet
先写到这儿，明天再写，下班，886~
继续-----------
const sheet = xlsx.utils.aoa_to_sheet(body)
// aoa_to_sheet 是将【一个二维数组】转化成 sheet
// json_to_sheet 是将【由对象组成的数组】转化成sheet
// table_to_sheet 是将【table的dom】直接转成sheet7、!merges 单元格合并
①【根据实际情况合并，我这只是处理了表头】
const merges = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 9 } },// 第0行第0列开始 —— 第0列到第9列结束（按数组的index方式计算，excel表第一列就是0开始计算）{ s: { r: 1, c: 0 }, e: { r: 2, c: 0 } },{ s: { r: 1, c: 1 }, e: { r: 2, c: 1 } },{ s: { r: 1, c: 2 }, e: { r: 2, c: 2 } },{ s: { r: 1, c: 3 }, e: { r: 2, c: 3 } },{ s: { r: 1, c: 4 }, e: { r: 2, c: 4 } },{ s: { r: 1, c: 5 }, e: { r: 2, c: 5 } },{ s: { r: 1, c: 6 }, e: { r: 2, c: 6 } },{ s: { r: 1, c: 7 }, e: { r: 1, c: 8 } },{ s: { r: 1, c: 9 }, e: { r: 2, c: 9 } },]s:是开始的意思 r代表第几行 c代表第几列
e:是结束的意思 r代表第几行 c代表第几列sheet['!merges'] = merges; // 添加到sheet中②【这里处理单元格内容，后端对数据进行了排序，本项目需求就是合并三个，那我就要动态将数据一样的进行合并单元格】
// 合并单元格的函数function addMerges(startRow, endRow, col) {if (startRow < endRow) {merges.push({s: { r: startRow, c: col },e: { r: endRow, c: col },})}}
// 动态合并相同内容的单元格的函数function mergeDynamicCells(colIndex) {let startRow = 3 // 这是单元格内容起始行for (let i = 4; i < body.length; i++) {if (body[i][colIndex] !== body[startRow][colIndex]) {addMerges(startRow, i - 1, colIndex) // 合并相同内容的单元格startRow = i // 更新起始行}}addMerges(startRow, body.length - 1, colIndex) // 合并最后一段相同内容}
// 只对第2列和第5列进行动态合并mergeDynamicCells(2) // 合并 2 列mergeDynamicCells(3) // 合并 3 列mergeDynamicCells(5) // 合并 5 列8、!cols 设置列宽
const cols = [{ wch: 10 },{ wch: 30 },{ wch: 25 },{ wch: 30 },{ wch: 30 },{ wch: 25 },{ wch: 15 },{ wch: 25 },{ wch: 25 },{ wch: 15 },]
sheet['!cols'] = cols; // 添加到sheet中9、!rows 设置行高
const rows = [{ hpx: 30 }, { hpx: 30 }, { hpx: 30 }]sheet['!rows'] = rows // 行高添加到sheet中10、样式添加-内容垂直居中
let range = xlsx.utils.decode_range(sheet['!ref']); let headerStyle = { font: { sz: 14, name: '宋体', bold: true }, alignment: { vertical: 'center', horizontal: 'center' } }; let defaultStyle = { font: { sz: 11, name: '宋体' }, alignment: { wrapText: 1, vertical: 'center', horizontal: 'center' } // wrapText: 1-------自动换行}; for (var row = range.s.r; row <= range.e.r; ++row) { for (let col = range.s.c; col <= range.e.c; ++col) { let cellAddress = xlsx.utils.encode_cell({ r: row, c: col }); let cell = sheet[cellAddress]; if (cell) { if (!cell.s) cell.s = {}; // 处理表头 if (cell.v === '隐患排查治理台账') { cell.s = headerStyle; } else { cell.s = defaultStyle; } } } } 11、向workbook中添加sheet
xlsx.utils.book_append_sheet(workbook, sheet, this.compname || 'Sheet')12、导出
xlsx.writeFile(workbook, (this.compname || 'Data') + '.xlsx', {type: 'binary',}) 三、完整代码
expeortexcel(ids) {exportCheckTableFault(ids).then((res) => {const data = res.data.data.map((ele, i) => ({xuhao: i + 1,faultName: ele.faultName,createDate: ele.createDate,checkTableType: ele.checkTableType,rectifyPlan: ele.rectifyPlan,rectifyDeptName: ele.rectifyDeptName,rectifyPresideName: ele.rectifyPresideName,rectifyTerm: ele.rectifyTerm,rectifyCompleteTime: ele.rectifyCompleteTime,accepUser: ele.accepUser,}))const body = data.map((x) => [x.xuhao,x.faultName,x.createDate,x.checkTableType,x.rectifyPlan,x.rectifyDeptName,x.rectifyPresideName,x.rectifyTerm,x.rectifyCompleteTime,x.accepUser,])// 定义Excel表头const header = [['隐患排查治理台账'],['序号','问题点','检查时间','检查类别','整改措施','责任单位','责任人','整改时间','','验收人',],['', '', '', '', '', '', '', '计划完成时间', '实际完成时间', ''],]body.unshift(...header) // 将定义好的表头添加到 body 中const sheet = xlsx.utils.aoa_to_sheet(body) // aoa_to_sheet 将二维数组转成 sheet// 合并单元格的函数const merges = [{ s: { r: 0, c: 0 }, e: { r: 0, c: 9 } },{ s: { r: 1, c: 0 }, e: { r: 2, c: 0 } },{ s: { r: 1, c: 1 }, e: { r: 2, c: 1 } },{ s: { r: 1, c: 2 }, e: { r: 2, c: 2 } },{ s: { r: 1, c: 3 }, e: { r: 2, c: 3 } },{ s: { r: 1, c: 4 }, e: { r: 2, c: 4 } },{ s: { r: 1, c: 5 }, e: { r: 2, c: 5 } },{ s: { r: 1, c: 6 }, e: { r: 2, c: 6 } },{ s: { r: 1, c: 7 }, e: { r: 1, c: 8 } },{ s: { r: 1, c: 9 }, e: { r: 2, c: 9 } },]function addMerges(startRow, endRow, col) {if (startRow < endRow) {merges.push({s: { r: startRow, c: col },e: { r: endRow, c: col },})}}// 动态合并相同内容的单元格的函数function mergeDynamicCells(colIndex) {let startRow = 3 // 数据部分从第4行开始for (let i = 4; i < body.length; i++) {if (body[i][colIndex] !== body[startRow][colIndex]) {addMerges(startRow, i - 1, colIndex) // 合并相同内容的单元格startRow = i // 更新起始行}}addMerges(startRow, body.length - 1, colIndex) // 合并最后一段相同内容}// 只对第2列和第5列进行动态合并mergeDynamicCells(2) // 合并 2 列mergeDynamicCells(3) // 合并 3 列mergeDynamicCells(5) // 合并 5 列sheet['!merges'] = merges // 将合并信息添加到 sheet 中const cols = [{ wch: 10 },{ wch: 30 },{ wch: 25 },{ wch: 30 },{ wch: 30 },{ wch: 25 },{ wch: 15 },{ wch: 25 },{ wch: 25 },{ wch: 15 },]sheet['!cols'] = cols // 设置列宽const rows = [{ hpx: 30 }, { hpx: 30 }, { hpx: 30 }]sheet['!rows'] = rows // 设置行高// 合并居中表格内容var range = xlsx.utils.decode_range(sheet['!ref'])for (var R = range.s.r; R <= range.e.r; ++R) {for (var C = range.s.c; C <= range.e.c; ++C) {var cell_ref = xlsx.utils.encode_cell({ r: R, c: C })var cell = sheet[cell_ref]if (cell) {if (!cell.s) cell.s = {}if (cell.v == '隐患排查治理台账') {cell.s = {font: {sz: 14,name: '宋体',bold: true,},alignment: {vertical: 'center', // 垂直居中horizontal: 'center', // 水平居中},}} else {cell.s = {font: {sz: 11,name: '宋体',},alignment: {wrapText: 1, //自动换行vertical: 'center', // 垂直居中horizontal: 'center', // 水平居中},}}}}}const workbook = xlsx.utils.book_new()
xlsx.utils.book_append_sheet(workbook, sheet, this.compname || 'Sheet') // 向workbook中添加sheet
xlsx.writeFile(workbook, (this.compname || 'Data') + '.xlsx', {type: 'binary',}) // 导出 workbook})},借鉴：前端使用xlsx-js-style导出Excel文件并修饰单元格样式-CSDN博客
