纯前段实现报表完美导出为excel格式纯前段实现报表完美导出为e

相信大家在项目中难免会遇到一些关于数据导出的问题,今天就给大家带来一个相对简单的一种导出数据格式为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 = https://www.it610.com/article/[ ['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 = https://www.it610.com/article/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 = https://www.it610.com/article/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 = https://www.it610.com/article/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 = https://www.it610.com/article/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