2024年xlsx、xlsx-style导出excel文件列宽、行高、样式、合并表格一步到位！

先上效果图

安装库

npm install --save file-saver //导出文件的库，也可以不安装这个，不安装也能下载excel，但是我用了这个
npm install --save xlsx-style //导出excel后表格的样式需要用到这个库
npm install --save xlsx //声明工作簿，创建文件，塞入数据要用到这个库

安装xlsx-style会报错的几个问题及解决办法

1.This relative module was not found:./cptable in ./node_modules/xlsx-style@0.8.13@xlsx-style/dist/cpexcel.js

解决：

找到在\node_modules\xlsx-style\dist\cpexcel.js 807行的var cpt = require('./cpt' + 'able');更换成var cpt = cptable;保存

2.Error: Can't resolve 'fs' in 'E:\xlsx-style'

解决： 在vue.config.js中的configureWebpack加入 resolve: { fallback: { fs: false } },代码如下

module.exports = defineConfig({
  transpileDependencies: true,
  productionSourceMap: false,
  publicPath: "./",
  outputDir: "dist",
  assetsDir: "assets",
  configureWebpack: {
    resolve: { fallback: { fs: false } },
  }
})

3.jszip not a constructor

解决：

node_modules\xlsx-style\xlsx.js (1339行左右)
将
if(typeof jszip === 'undefined') jszip = require('./js'+'zip').JSZip;
替换成
if(typeof jszip === 'undefined') jszip = require('./jszip.js');

以上是本人遇到的错误

代码步骤

页面代码

 <el-button type="primary" @click="downLoadExcel">导出12月报表</el-button>

函数代码

 //导出ecxel
    downLoadKPIExcel() {
//第一步，引入库，使用import引入可能会导致引入的不全
      const XLSX = require("xlsx");
      const XLSXStyle = require("xlsx-style");
      const FileSaver = require("file-saver");
 
//这是后端返回的数据进行处理，只挑出要导出的数据
      const newArr = [];
      this.tableData.map((item, index) => {
        var obj = {};
        obj.index = index + 1;
        obj.name = item.name;
        obj.deptName = item.deptName;
        obj.valueScore = item.valueScore;
        obj.valueRealScore = item.valueRealScore;
        obj.targetScore = item.targetScore;
        obj.KPIScore = (Number(item.targetScore) + Number(item.valueRealScore)).toFixed(2);
        obj.bonusCoefficient = item.bonusCoefficient + "%";
        newArr.push(obj);
      });
//处理结束，如果是直接用table的数据请看后面的代码
 
      // 创建工作簿
      const workbook = XLSX.utils.book_new();
 
      // 创建工作表并定义列标题
      const worksheet = XLSX.utils.aoa_to_sheet([
        [`${this.year}年${this.month}月奖金系数汇总`],  //这里是要合并单元格的，所以写一个数据就行
        ["序号", "名字", "部门", "价值观分数", "价值观得分", "****实际得分", "****得分", "系数"], //第二列表头，共8列，这个数字在下面的代码会用到
      ]);
 
      //合并单元格 这里指定列合并单元格的范围,与列宽度设置类似也是一个数组
      let excelMerges = [];
      excelMerges.push({
        s: { r: 0, c: 0 },
        e: { r: 0, c: 7 }, //合并范围是第1行第一列到第1行第8列
      });
      worksheet["!merges"] = excelMerges; //可以打印worksheet数据来看，以！开头的就是设置列宽行高合并的，其他就是单元格
 
      // 遍历后端数据并写入工作表
      newArr.forEach((row, rowIndex) => {
 
        const sheetRow = [];
  //循环列
        for (let i = 0; i < 8; i++) {
//下面是因为中间的数据格式必须要是数字，所以push进去要转一下，默认都是字符串类型的，不需要的可以删除
          if (i === 3 || i === 4 || i === 5 || i === 6) {
            sheetRow.push(Number(Object.values(row)[i]));
          } else {
            sheetRow.push(Object.values(row)[i]); // 替换为实际属性名或处理空值
          }
        }
     //将数据添加到工作表中，从第三行开始add，因为一二行是表头
        XLSX.utils.sheet_add_aoa(worksheet, [sheetRow], { origin: rowIndex + 2 });
      });
 
//开始添加样式
      const styles = {
//第一行表头的样式
        firstHeader: {
          font: { bold: true, name: "微软雅黑", sz: 16 },
          alignment: {
            //文字居中
            horizontal: "center",
            vertical: "center",
            wrap_text: true,
          },
          border: {
            top: { style: "thin" },
            left: { style: "thin" },
            right: { style: "thin" },
            bottom: { style: "thin" },
          },
        },
//第二行表头的样式
        headerStyle: {
          font: { bold: true, name: "微软雅黑", sz: 11 },
          fill: { fgColor: { rgb: "C0C0C0" } }, // 背景色
          alignment: {
            //文字居中
            horizontal: "center",
            vertical: "center",
            wrap_text: true,
          },
          border: {
            top: { style: "thin" },
            left: { style: "thin" },
            right: { style: "thin" },
            bottom: { style: "thin" },
          },
          // height: 40,
        },
//其他单元格的样式
        cellStyle: {
          font: { name: "微软雅黑", sz: 11 },
          alignment: {
            //文字居中
            horizontal: "center",
            vertical: "center",
            wrap_text: true,
          },
          border: {
            top: { style: "thin" },
            left: { style: "thin" },
            right: { style: "thin" },
            bottom: { style: "thin" },
          },
          // height: 40,
        },
      };
 
      // 设置列宽行高
  
//先声明worksheet["!rows"]、worksheet["!cols"]
      if (!worksheet["!rows"] || !worksheet["!cols"]) {
        worksheet["!rows"] = [];
        worksheet["!cols"] = [];
      }
//worksheet["!ref"]是工作表的范围
      const range = XLSX.utils.decode_range(worksheet["!ref"]);
     
//循环列，设置列宽为20字符，也可以设置像素wpx：200
      for (var i = 0; i < 8; i++) {
        worksheet["!cols"][i] = { wch: 20 };
      }
//循环行，设置第一行像素为40，其余行为30
      for (let i = range.s.r + 1; i < range.e.r + 1; i++) {
        worksheet["!rows"][0] = { hpx: 40 };
        worksheet["!rows"][i] = { hpx: 30 };
      }
      console.log(worksheet);
 
      // 应用样式到列头
      for (let col of ["A", "B", "C", "D", "E", "F", "G", "H"]) {
        let cellRef1 = `${col}1`;
        if (worksheet[cellRef1]) {
          worksheet[cellRef1].s = styles.firstHeader;
        }
        let cellRef2 = `${col}2`;
        if (worksheet[cellRef2]) {
          worksheet[cellRef2].s = styles.headerStyle;
        }
      }
 
     //应用样式到单元格
      for (let row = 3; row < range.e.r + 1; row++) {
 
        for (let col of ["A", "B", "C", "D", "E", "F", "G", "H"]) {
          let cellRef = `${col}${row}`;
          if (worksheet[cellRef]) {
            worksheet[cellRef].s = styles.cellStyle;
 
          }
        }
      }
 
 
 
      // 添加工作表到工作簿
      XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1");
      // 确保导出时包含样式信息，一定要使用xlsxstyle来写入，不然就没有样式了
      const wbOut = XLSXStyle.write(workbook, { bookType: "xlsx", type: "binary" });
 
      FileSaver.saveAs(
        // Blob: 对象表示一个不可变 原始数据的类文件对象,不一定是JS原生格式的数据。
        // File: 基于Blob，继承了blob的功能并将其扩展使其支持用户系统上的文件。
        new Blob([this.s2ab(wbOut)], { type: "appliction/octet-stream" }),
        // 设置导出的文件名称可随意
        `2023年12月份*****.xlsx`,
      );
 
    },
    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;
    },

塞入数据方法一：使用后端返回的数据塞入到excel每一个单元格内（适合有些数据很复杂页面上要看到但是不需要导出来）

//这里就是简单的处理一下  
const newArr = [];
      this.tableData.map((item, index) => {
        var obj = {};
        obj.index = index + 1;
        obj.name = item.name;
        obj.deptName = item.deptName;
        obj.valueScore = item.valueScore;
        obj.valueRealScore = item.valueRealScore;
        obj.targetScore = item.targetScore;
        obj.KPIScore = (Number(item.targetScore) + Number(item.valueRealScore)).toFixed(2);
        obj.bonusCoefficient = item.bonusCoefficient + "%";
        newArr.push(obj);
    
      });
      

塞入数据方法二：前端表格table绑定一个id，利用id来获取表格内的数据（适合下载后的数据和页面上的table数据一致）

//页面上的table绑定的id叫dwl-table，wb就是上面代码的workbook
 var wb = XLSX.utils.table_to_book(document.querySelector("#dwl-table"));
//ws是worksheet，可直接从样式添加开始
 var ws = wb.Sheets[wb.SheetNames[0]];

行高设置需要修改源码

打开xlsx-style文件中的xlsx.js，找到write_ws_xml_data这个函数，替换成下面的代码

var DEF_PPI = 96, PPI = DEF_PPI;
function px2pt(px) { return px * 96 / PPI; }
function pt2px(pt) { return pt * PPI / 96; }
function write_ws_xml_data(ws, opts, idx, wb) {
	var o = [], r = [], range = safe_decode_range(ws['!ref']), cell, ref, rr = "", cols = [], R, C,rows = ws['!rows'];
	for(C = range.s.c; C <= range.e.c; ++C) cols[C] = encode_col(C);
	for(R = range.s.r; R <= range.e.r; ++R) {
		r = [];
		rr = encode_row(R);
		for(C = range.s.c; C <= range.e.c; ++C) {
			ref = cols[C] + rr;
			if(ws[ref] === undefined) continue;
			if((cell = write_ws_xml_cell(ws[ref], ref, ws, opts, idx, wb)) != null) r.push(cell);
		}
		if(r.length > 0){
      params = ({r:rr});
      if(rows && rows[R]) {
        row = rows[R];
        if(row.hidden) params.hidden = 1;
        height = -1;
        if (row.hpx) height = px2pt(row.hpx);
        else if (row.hpt) height = row.hpt;
        if (height > -1) { params.ht = height; params.customHeight = 1; }
        if (row.level) { params.outlineLevel = row.level; }
      }
      o[o.length] = (writextag('row', r.join(""), params));
    }
	}
  if(rows) for(; R < rows.length; ++R) {
    if(rows && rows[R]) {
      params = ({r:R+1});
      row = rows[R];
      if(row.hidden) params.hidden = 1;
      height = -1;
      if (row.hpx) height = px2pt(row.hpx);
      else if (row.hpt) height = row.hpt;
      if (height > -1) { params.ht = height; params.customHeight = 1; }
      if (row.level) { params.outlineLevel = row.level; }
      o[o.length] = (writextag('row', "", params));
    }
  }
	return o.join("");
}
//前面新增了三句代码也要复制过来，保存后要重启vue项目

以上 就酱