前端vue导出excel(标题加粗+表头自定义样式+表格边框+单元格自定义样式)

接近过年，被一大堆excel报表烦***的我，遇到要求前端导出excel的后端，差点猝***的我拼命学习中，整理出这篇文章,希望看到这篇文章的你有所收获，也希望能收到大佬们的指点

之前用c#，.net弄过导出word，excel，可以点击查看.NET使用Aspose控件生成Word（可构建自定义表格）、Excel这篇文章

话不多说，进入正题，前端导出excel,使用插件 file-saver、xlsx、xlsx-style，xlsx-style 主要用来优化excel的样式

准备

安装插件 npm install file-saver xlsx xlsx-style --save

引入xlsx-style，发现导出样式依然没反应。需要在\node_modules\xlsx-style\dist\cpexcel.js 807行 的 var cpt = require(’./cpt’ + ‘able’); 改成var cpt = cptable;

但是如果每次打包之前去修改包里的代码很麻烦,如果是多人合作，别人打包的适合也可能不知道直接打包了。所以我去node_modules包里直接修改了xlsx.full.min.js，然后吧文件复制到静态文件夹中，在index.html页面引入即可

这一步很重要，笔者就是在这个问题上耗费了整整一下午，非常惨痛的代价

创建导出execl工具文件exportExecl.js

1.table数据导出execl文件

/** * table数据导出execl文件 * @param {*} id:table元素id * @param {*} fileName :导出文件名称 */function exportExecl(id, fileName, HEADERRANGE, TABLERANGE, REDRANGE = false) {  let excelName = fileName||'导出表格.xlsx'  var xlsxParam = { raw: false } // 导出数据是否未加工  let tables=document.getElementById(id)  if(!tables) return  tables = document.getElementById(id).cloneNode(true)  // 移除固定列，防止重复生成表格  if (tables.querySelector('.el-table__fixed') !== null) {    tables.removeChild(tables.querySelector('.el-table__fixed'))  }  let table_book = utils.table_to_book(tables, xlsxParam)  table_book.Sheets.Sheet1 = setTitleClass(table_book.Sheets.Sheet1)  table_book.Sheets.Sheet1 = setCellStyle(table_book.Sheets.Sheet1, HEADERRANGE, headerStyle)  table_book.Sheets.Sheet1 = setCellStyle(table_book.Sheets.Sheet1, TABLERANGE, contentStyle)  if (REDRANGE) {    table_book.Sheets.Sheet1 = setCellStyle(table_book.Sheets.Sheet1, REDRANGE, redFont)  }  table_book.Sheets.Sheet1 = addRangeBorder(table_book.Sheets.Sheet1['!merges'], table_book.Sheets.Sheet1) // 合并项添加边框  var table_write = XLSX.write(table_book, {    bookSST: false,    type: 'binary',    bookType: 'xlsx'  })  try {      FileSaver.saveAs(        new Blob([s2ab(table_write)], { type: 'application/octet-stream' }),        excelName      )  } catch (e) {    console.log(e, table_write)  }  return table_write}function s2ab(s) {  var buf = new ArrayBuffer(s.length)  var view = new Uint8Array(buf)  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;  return buf}

2.为合并项添加边框

// 为合并项添加边框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'  ]  if (range) {    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         for (let row = startRowNumber; row           ws[arr[row] + (col + 1)] = test;        }      }    });  }  return ws;}  font: { name: '宋体', sz: 11, bold: false,    color: {      rgb: '000000'    }  },  alignment: { horizontal: 'center', vertical: 'center', wrapText: false }}const titleStyle = {  font: { name: '黑体', sz: 18, bold: false,    color: {      rgb: '000000'    }  },  alignment: { horizontal: 'center', vertical: 'center', wrapText: false }}const headerStyle = {  font: { name: '黑体', sz: 11, bold: false,    color: {      rgb: '000000'    }  },  alignment: { horizontal: 'center', vertical: 'center', wrapText: false },  border: {    top: { style: 'thin' },    bottom: { style: 'thin' },    left: { style: 'thin' },    right: { style: 'thin' }  }  // fill: {  //   fgColor: { rgb: 'ebebeb' }// 设置标题单元格的背景颜色  // }}const redFont = {  font: { name: '宋体', sz: 11, bold: false,    color: {      rgb: 'FF0000' // 十六进制，不带#    }  },  alignment: { horizontal: 'left', vertical: 'center', wrapText: false }}const contentStyle = {  font: { name: '宋体', sz: 11, bold: false },  alignment: { horizontal: 'center', vertical: 'center', wrapText: false },  border: {    top: { style: 'thin' },    bottom: { style: 'thin' },    left: { style: 'thin' },    right: { style: 'thin' }  }}// 设置 标题及公共样式function setTitleClass(worksheet) {  // title  for (const key in worksheet) {   if (key.indexOf('!') === -1 && worksheet[key].v) {      if(key == 'A1'){ // title        worksheet[key].s = titleStyle      } else {        worksheet[key].s = commonStyle      }   }  }  return worksheet}// 根据范围和style设置单元格样式function setCellStyle(worksheet, range, RANGESTYLE) {  const headerRange = utils.decode_range(range)  for (let col = headerRange.s.c; col     for ( let row = headerRange.s.r; row       const headerCell = utils.encode_cell({ r: row, c: col })      if( worksheet[headerCell] != undefined) {        worksheet[headerCell].s = RANGESTYLE      }    }  }  return worksheet} utils } from 'xlsx'const commonStyle = {  font: { name: '宋体', sz: 11, bold: false,    color: {      rgb: '000000'    }  },  alignment: { horizontal: 'center', vertical: 'center', wrapText: false }}const titleStyle = {  font: { name: '黑体', sz: 18, bold: false,    color: {      rgb: '000000'    }  },  alignment: { horizontal: 'center', vertical: 'center', wrapText: false }}const headerStyle = {  font: { name: '黑体', sz: 11, bold: false,    color: {      rgb: '000000'    }  },  alignment: { horizontal: 'center', vertical: 'center', wrapText: false },  border: {    top: { style: 'thin' },    bottom: { style: 'thin' },    left: { style: 'thin' },    right: { style: 'thin' }  }  // fill: {  //   fgColor: { rgb: 'ebebeb' }// 设置标题单元格的背景颜色  // }}const contentStyle = {  font: { name: '宋体', sz: 11, bold: false },  alignment: { horizontal: 'center', vertical: 'center', wrapText: false },  border: {    top: { style: 'thin' },    bottom: { style: 'thin' },    left: { style: 'thin' },    right: { style: 'thin' }  }}const redFont = {  font: { name: '宋体', sz: 11, bold: false,    color: {      rgb: 'FF0000' // 十六进制，不带#    }  },  alignment: { horizontal: 'left', vertical: 'center', wrapText: false }}/** * table数据导出execl文件 * @param {*} id:table元素id * @param {*} fileName :导出文件名称 */function exportExecl(id, fileName, HEADERRANGE, TABLERANGE, REDRANGE = false) {  let excelName = fileName||'导出表格.xlsx'  var xlsxParam = { raw: false } // 导出数据是否未加工  let tables=document.getElementById(id)  if(!tables) return  tables = document.getElementById(id).cloneNode(true)  // 移除固定列，防止重复生成表格  if (tables.querySelector('.el-table__fixed') !== null) {    tables.removeChild(tables.querySelector('.el-table__fixed'))  }  let table_book = utils.table_to_book(tables, xlsxParam)  table_book.Sheets.Sheet1 = setTitleClass(table_book.Sheets.Sheet1)  table_book.Sheets.Sheet1 = setCellStyle(table_book.Sheets.Sheet1, HEADERRANGE, headerStyle)  table_book.Sheets.Sheet1 = setCellStyle(table_book.Sheets.Sheet1, TABLERANGE, contentStyle)  if (REDRANGE) {    table_book.Sheets.Sheet1 = setCellStyle(table_book.Sheets.Sheet1, REDRANGE, redFont)  }  table_book.Sheets.Sheet1 = addRangeBorder(table_book.Sheets.Sheet1['!merges'], table_book.Sheets.Sheet1) // 合并项添加边框  var table_write = XLSX.write(table_book, {    bookSST: false,    type: 'binary',    bookType: 'xlsx'  })  try {      FileSaver.saveAs(        new Blob([s2ab(table_write)], { type: 'application/octet-stream' }),        excelName      )  } catch (e) {    console.log(e, table_write)  }  return table_write}function setCellStyle(worksheet, range, RANGESTYLE) {  const headerRange = utils.decode_range(range)  for (let col = headerRange.s.c; col     for ( let row = headerRange.s.r; row       const headerCell = utils.encode_cell({ r: row, c: col })      if( worksheet[headerCell] != undefined) {        worksheet[headerCell].s = RANGESTYLE      }    }  }  return worksheet}function setTitleClass(worksheet) {  // title  for (const key in worksheet) {   if (key.indexOf('!') === -1 && worksheet[key].v) {      if(key == 'A1'){ // title        worksheet[key].s = titleStyle      } else {        worksheet[key].s = commonStyle      }   }  }  return worksheet}// 为合并项添加边框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'  ]  if (range) {    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         for (let row = startRowNumber; row           ws[arr[row] + (col + 1)] = test;        }      }    });  }  return ws;}function s2ab(s) {  var buf = new ArrayBuffer(s.length)  var view = new Uint8Array(buf)  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;  return buf}export default exportExecl