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

前端导出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',
  }); // 字符串转ArrayBuffer

  return 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页面：

  <table border="1" cellspacing="0" width="100%" class="table" rules="all" style="margin-top: 10px;display: none"
             id="exportTable">
        <tr class="contents" align="center">
          <th colspan="17" v-text="detailTitle"></th>
        </tr>
        <tr align='center' style="height: 2.5rem">
          <th>序号</th>
          <th colspan="2">班级</th>
          <th colspan="2">学号</th>
          <th colspan="2">姓名</th>
          <th colspan="2">日期</th>
          <th colspan="2">宿舍床位</th>
          <th colspan="2">归寝时间</th>
          <th>正常归寝</th>
          <th>未出门</th>
          <th>晚归</th>
          <th>未归</th>
        </tr>
        <tr align='center' style="height: 2.5rem" v-if="returnList.length === 0">
          <td colspan="17" v-if="">暂无数据</td>
        </tr>
        <tr align='center' style="height: 2.5rem" v-else v-for="(item,index) in returnList">
          <td v-text="index + 1"></td>
          <td colspan="2" v-text="item.className"></td>
          <td colspan="2" v-text="item.stuNo"></td>
          <td colspan="2" v-text="item.stuName"></td>
          <td colspan="2" v-text="item.dailyTime"></td>
          <td colspan="2" v-text="item.dorm"></td>
          <td colspan="2" v-text="item.returnTime"></td>
          <td>
            <span v-if="item.dailyStatus === 1" class="big-size">✔</span>
          </td>
          <td>
            <span v-if="item.dailyStatus === 2" class="big-size">✔</span>
          </td>
          <td>
            <span v-if="item.dailyStatus === 3" class="big-size">✔</span>
          </td>
          <td>
            <span v-if="item.dailyStatus === 4" class="big-size">✔</span>
          </td>
        </tr>
      </table>

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;
      })
    },

效果如下