当前位置：首页 > article >正文

利用ExcelJS封装一个excel表格的导出

article 2024/11/5 13:31:16

ExcelJS 操作和写入Excel 文件。
直接上代码，js部分：
exportFn.js

import ExcelJS from 'exceljs';
import { saveAs } from 'file-saver';

export function exportExcleUtils(tHeader, filterVal, listData, fileName) {
  //设置工作簿属性
  const workbook = new ExcelJS.Workbook();
  workbook.creator = 'Me';
  workbook.lastModifiedBy = 'Her';
  workbook.created = new Date();
  workbook.modified = new Date();
  workbook.lastPrinted = new Date();
  //添加工作薄
  const worksheet = workbook.addWorksheet('sheet1');
  //计算标题宽
  let colWidth = calculateColumnWidth(tHeader);
  console.log(colWidth);
  let style = { alignment: { vertical: 'middle', horizontal: 'center' } };
  let columns = [];
  for (let i = 0; i < tHeader.length; i++) {
    let header = {
      header: tHeader[i],
      key: filterVal[i] ? filterVal[i] : i + '',
      width: colWidth[i] ? colWidth[i] + 20 : 10,
      style: JSON.parse(JSON.stringify(style)),
    };
    columns.push(header);
  }
  worksheet.columns = columns;

  //添加数据
  listData.forEach((item) => {
    worksheet.addRow(item);
  });

  worksheet.getRow(1).font = { name: '宋体', family: 4, size: 16, bold: true };

  fileName = fileName + '.xlsx';
  workbook.xlsx.writeBuffer().then((data) => {
    let blob = new Blob([data], {
      type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    });
    saveAs(blob, fileName);
  });
}

export function calculateColumnWidth(tHeader) {
  let colWidth = [];
  //计算列宽
  for (let i = 0; i < tHeader.length; i++) {
    if (colWidth[i] && colWidth[i] < tHeader[i].length) {
      colWidth[i] = tHeader[i].length * 2;
    } else if (colWidth[i] === undefined) {
      colWidth[i] = tHeader[i].length * 2;
    }
  }
  return colWidth;
}

举个栗子：

//从公共函数中拿出
import { exportExcleUtils } from '@/libs/exportFn';
...
...
...
//再写个按钮
    <el-button type="primary" @click="exportFn">导出按钮</el-button>
...
...
...

    //导出
    exportFn() {
      let loadingInstance = Loading.service({
        text: '正在导出，请稍等...',
      });

      let table = this.selectedRowArr;
		//表头
      let tHeader = [
		     'a',
		     'b',
		     'c',
		     'd',
      ];
		//数据的里字段
      let filterVal = [
	        'person',
	    	'dog',
	    	'cat',
	    	'pig',
      ];
      let fileName = '导出的表文件名';
      loadingInstance.close();
      exportExcleUtils(tHeader, filterVal, table, fileName);
    },