300字范文 > 前端导出excel自定义样式(行高除外)

前端导出excel自定义样式(行高除外)

时间：2023-01-18 12:04:04

前端导出excel需要用到的依赖有xlsx、xlsx-style

import XLSX from 'xlsx';import XLSXStyle from 'xlsx-style';

npm install后xlsx-style会报错,只需在vue.config.js中加上

externals: {'./cptable': 'var cptable'}

封装好的的js工具类,两种方法，一种传dom，一种传数据、表头

在这里插入代码片// 源码什么的都不需要改动import XLSX from 'xlsx';import XLSXStyle from 'xlsx-style';//---------------------------------------------------------------------1.传数据表头开始/*const titles = {landNumber: '宗地号',name: '姓名',xiangName: '乡名称',cunName: '村名称',createTime: '档案上传日期',qlrSignTime: '权利人签署日期',typeList: '驳回原因'}const fields = ['landNumber', 'name', 'xiangName', 'cunName', 'createTime', 'qlrSignTime', 'typeList']const ws = createWs(this.recordList, // 后台返回的json数据fields,titles)openDownloadDialog(sheet2blob(ws), '权利人分析.xlsx');*/ // vue传值示例export function createWs(data, fields, titles) {const ws = XLSX.utils.json_to_sheet(data, {header: fields,});/*const = merges: [// 合并所需的单元格我这个导出中没有用到就没用视业务而定{ s: { r: 0, c: 2 }, e: { r: 0, c: 5 } },{ s: { r: 0, c: 0 }, e: { r: 1, c: 0 } },{ s: { r: 0, c: 1 }, e: { r: 1, c: 1 } },{ s: { r: 0, c: 6 }, e: { r: 1, c: 6 } },{ s: { r: 0, c: 7 }, e: { r: 1, c: 7 } },{ s: { r: 0, c: 8 }, e: { r: 1, c: 8 } },{ s: { r: 0, c: 9 }, e: { r: 1, c: 9 } },{ s: { r: 0, c: 10 }, e: { r: 1, c: 10 } },{ s: { r: 0, c: 11 }, e: { r: 1, c: 11 } },{ s: { r: 0, c: 12 }, e: { r: 1, c: 12 } },{ s: { r: 0, c: 13 }, e: { r: 1, c: 13 } },{ s: { r: 0, c: 14 }, e: { r: 1, c: 14 } },{ s: { r: 0, c: 15 }, e: { r: 1, c: 15 } },{ s: { r: 0, c: 16 }, e: { r: 1, c: 16 } },{ s: { r: 0, c: 17 }, e: { r: 1, c: 17 } },],ws['!merges'] = merges;*/ws['!cols'] = [ //设置表格的宽度{wpx: 150,},{wpx: 100,},{wpx: 100,},{wpx: 150,},{wpx: 150,},{wpx: 150,},{wpx: 200,},];const range = XLSX.utils.decode_range(ws['!ref']);// 设置表格样式for (let i = range.s.c; i < range.e.c + 1; i++) {for (let j = range.s.r; j < range.e.r + 1; j++) {let cell_address = {c: i,r: j,};let column = XLSX.utils.encode_cell(cell_address);ws[column].s = {font: {name: '宋体',sz: 11,color: {auto: 1,},},border: {color: {auto: 1,},top: {style: 'thin',},bottom: {style: 'thin',},left: {style: 'thin',},right: {style: 'thin',},},alignment: {/// 自动换行wrapText: 1,// 居中horizontal: 'center',vertical: 'center',indent: 0,},};}}//设置中文标题因为后台返回的数据中属性只能以英文返回所以需要转化为中文for (let c = range.s.c; c <= range.e.c; c++) {const header = XLSX.utils.encode_col(c) + '1';ws[header].v = titles[ws[header].v];}return ws;}//---------------------------------------------------------------------1.传数据表头结束//---------------------------------------------------------------------2.传表格dom开始export function setExport2Excel(dom,saveName) {let xlsxParam = {raw: true } //这个保证表格只进行解析不做运算var wb = XLSX.utils.table_to_sheet(dom,xlsxParam)for (let i = 0; i < 16; i++) {wb["!cols"][i] = {wpx: 80}}for (const key in wb) {if (key === 'A1'){wb[key].s = {font: {//字体设置sz: 18,bold: true,color: {rgb: '000000'//十六进制，不带#},},alignment: {//文字居中horizontal: 'center',vertical: 'center',wrap_text: true},border: {// 设置边框top: {style: 'none'},bottom: {style: 'none'},left: {style: 'none'},right: {style: 'none'}},}}else if(key === 'A2' || key === 'B2'|| key === 'D2'|| key === 'F2' || key === 'H2' || key === 'J2' || key === 'L2'|| key === 'N2'|| key === 'O2'|| key === 'P2'|| key === 'Q2') {wb[key].s = {font: {//字体设置sz: 14,bold: true,color: {rgb: '000000'//十六进制，不带#},},alignment: {//文字居中horizontal: 'center',vertical: 'center',wrap_text: true},border: {// 设置边框top: {style: 'thin'},bottom: {style: 'thin'},left: {style: 'thin'},right: {style: 'thin'}},}} else if(key.indexOf('!') === -1 && wb[key].v) {wb[key].s = {font: {//字体设置sz: 12,bold: false,color: {rgb: '000000'//十六进制，不带#},},alignment: {//文字居中horizontal: 'center',vertical: 'center',wrap_text: true},border: {// 设置边框top: {style: 'none'},bottom: {style: 'none'},left: {style: 'none'},right: {style: 'none'}},}}}let data = addRangeBorder(wb['!merges'], wb) //合并项添加边框let fileData = sheet2blob(data)openDownloadDialog(fileData, saveName)}//为合并项添加边框export function addRangeBorder(range, ws) {let arr = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"];range.forEach(item => {let startColNumber = Number(item.s.r), endColNumber = Number(item.e.r);let startRowNumber = Number(item.s.c), endRowNumber = Number(item.e.c);const test = ws[arr[startRowNumber] + (startColNumber + 1)];for (let col = startColNumber; col <= endColNumber; col++) {for (let row = startRowNumber; row <= endRowNumber; row++) {ws[arr[row] + (col + 1)] = test;}}})return ws;}//---------------------------------------------------------------------2.传表格dom结束// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载export function sheet2blob(sheet, sheetName) {sheetName = sheetName || 'sheet1';var workbook = {SheetNames: [sheetName],Sheets: {},};workbook.Sheets[sheetName] = sheet; // 生成excel的配置项// 下载这里一定要用 xlsx-style 的write() 方法才可以使导出excel表格带样式var wbout = XLSXStyle.write(workbook, {type: 'buffer'});var blob = new Blob([wbout], {type: 'application/octet-stream',}); // 字符串转ArrayBufferreturn blob;}export function openDownloadDialog(url, saveName) {if (typeof url == 'object' && url instanceof Blob) {url = URL.createObjectURL(url); // 创建blob地址}var aLink = document.createElement('a');aLink.href = url;aLink.download = saveName || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效var event;if (window.MouseEvent) event = new MouseEvent('click');else {event = document.createEvent('MouseEvents');event.initMouseEvent('click',true,false,window,0,0,0,0,0,false,false,false,false,0,null);}aLink.dispatchEvent(event);}

第一种方法传dom的vue页面：

js部分

// 导出操作exportClick() {this.exportLoading = true;this.queryParams.beginTime = this.queryParams.dateTime[0];this.queryParams.endTime = this.queryParams.dateTime[1];exportDaily(this.queryParams).then(response => {this.returnList = response.rows}).then(() => {let text = '归寝日报汇总表.xlsx'setExport2Excel(document.querySelector('#exportTable'),text)this.exportLoading = false;})},

效果如下

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。