excel前端下载工具函数封装总结

主要使用：xlsx库进行下载，因为需要改变样式，比如有的单元格需要换行和分裂和处理颜色和宽度高度，需要引入xlsxStyle。

需要手动设置单元格的计算范围 类似["A1",..., "H2"] A1代表第一行第一列

根据业务场景，childrenList作为多sheet的子表，lineBreak控制单元格是否换行。

Promise.all进行并发处理，对文件内容进行分片。

主要操作workbook 对象

核心代码：

function exportExcel(headers, data, fileName = '测试.xlsx', lineBreak, childrenList) {
  let fenpianshu = 10
  const _headers = headers

    .map((item, i) => Object.assign({}, { key: item.dataIndex, title: item.title, position: tans(i + 1) + 1 }))
    .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});

  let _data = {}
  let times = Math.ceil((data.length) / fenpianshu);
  let tempArr = [];
  for (let i = 0; i < times; i++) {

    tempArr.push(
      findString(data.slice(i * fenpianshu, (i + 1) * fenpianshu), headers, lineBreak, i, fenpianshu)
    );
  }
  Promise.all(tempArr).then((res) => {
    let arr1 = res.flat()
    arr1.forEach((item) => {
      _data = Object.assign(_data, item);
    })


    const output = Object.assign({}, _headers, _data);
    // 获取所有单元格的位置
    const outputPos = Object.keys(output);
    // console.log(outputPos);
    // 计算出范围 ,["A1",..., "H2"]



    const ref = `${outputPos[0]}:${outputPos[outputPos.length - 1]}`;
    const colsArr = []
    const rowsArr = []
    data.forEach(() => {
      rowsArr.push({ hpx: 130 })
    })
    headers.forEach(() => {
      colsArr.push({ wpx: 100 })
    })
    // 构建 workbook 对象
    const wb = {
      SheetNames: [fileName,],
      Sheets: {
      },
    };
    wb.Sheets[fileName] =
      Object.assign(
        {},
        output,
        {
          '!ref': ref,
          '!cols': colsArr,
          "!rows": rowsArr
        },
      )
    // ----------------------处理多sheet------------------------
    if (childrenList.length > 0) {
      childrenList.forEach(childitem => {

        let _childheaders = childitem.column
          .map((item, i) => Object.assign({}, { key: item.dataIndex, title: item.title, position: tans(i + 1) + 1 }))
          .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { key: next.key, v: next.title } }), {});

        let _childdata = childitem.data
          .map((item, i) => childitem.column.map((key, j) => Object.assign({}, { content: typeof (item[key.dataIndex]) !== "object" || item[key.dataIndex] === null ? `${item[key.dataIndex]}` : lineBreak ? `${item[key.dataIndex].join("\r\n")}` : `${item[key.dataIndex].filter(i => i || i === 0).join("            ")}`, position: tans(j + 1) + (i + 2) })))

          .reduce((prev, next) => prev.concat(next))

          .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { v: next.content } }), {});


        let childoutput = Object.assign({}, _childheaders, _childdata);

        const childoutputPos = Object.keys(childoutput);

        const childref = `${childoutputPos[0]}:${childoutputPos[childoutputPos.length - 1]}`;
        wb.SheetNames.push(childitem.sheetName)
        wb.Sheets[childitem.sheetName] = Object.assign(
          {},
          childoutput,
          {
            '!ref': childref,
            // '!cols': [{ wpx: 45 }, { wpx: 100 }, { wpx: 200 }, { wpx: 80 }, { wpx: 150 }, { wpx: 100 }, { wpx: 300 }, { wpx: 300 }],
            '!cols': colsArr,

          },
        )

      })
    }
    // ------------------------------------------------

    wb.SheetNames.forEach((k, index) => {


      let dataInfo = wb.Sheets[wb.SheetNames[index]];
      for (var i in dataInfo) {
        // console.log(dataInfo[i].v);
        if (i.indexOf('!') < 0) {
          if (!isNaN(Number(dataInfo[i].v.replace("%", "")))) {
            if (dataInfo[i].v && dataInfo[i].v.length > 10) {
              dataInfo[i].v = '\t' + dataInfo[i].v
            } else {
              dataInfo[i + ''].t = "n"
            }
          }
          dataInfo[i + ''].s = {
            //居中属性
            alignment: {
              horizontal: "center",
              vertical: "center",
              wrapText: 1,
              indent: 0
            },
          }
        }

      }
    })


    // 导出 Excel
    let wbout = XLSXStyle.write(wb, {
      bookType: "xlsx",
      bookSST: true,
      type: "buffer",
    });
    // var xlsxParam = { raw: false }; //导出的内容只做解析，不进行格式转换(添加此行代码表格中的百分比就不会再导出的时候被转换成小数点)
    try {
      FileSaver.saveAs(
        new Blob([wbout], { type: "application/octet-stream" }),
        fileName
      );
    } catch (e) {
      if (typeof console !== "undefined") console.log(e, wbout);
    }
    return wbout;
    // XLSX.writeFile(wb, fileName,);
  });
}

function tans(columnNumber) {
  var dividend = columnNumber;
  var columnName = "";
  var modulo;

  while (dividend > 0) {
    modulo = (dividend - 1) % 26;
    columnName = String.fromCharCode(65 + modulo) + columnName;
    dividend = parseInt((dividend - modulo) / 26);
  }

  return columnName;
}//该方法定位位置

const findString = (arr1, headers, lineBreak, num1, fenpianshu) => {
  return arr1
    .map((item, i) => headers.map((key, j) => Object.assign({}, { content: typeof (item[key.dataIndex]) !== "object" || item[key.dataIndex] === null ? `${item[key.dataIndex]}` : lineBreak ? `${item[key.dataIndex].join("\r\n")}` : `${item[key.dataIndex].filter(i => i || i === 0).join("            ")}`, position: tans(j + 1) + (i + 2 + num1 * fenpianshu) })))
    // 对刚才的结果进行降维处理（二维数组变成一维数组）
    .reduce((prev, next) => prev.concat(next))
    // 转换成 worksheet 需要的结构
    .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { v: next.content } }), {});
};

还有很多好用的属性可以参考：

https://www.npmjs.com/package/js-xlsxstyle