excel.js实践（2021-6-25）

import * as ExcelJs from 'exceljs/dist/exceljs';

export function jsonToExcel(data) {
  const tableName = "表名称";
  const borderStyle = {
    top: { style: 'thin' },
    left: { style: 'thin' },
    bottom: { style: 'thin' },
    right: { style: 'thin' }
  };
  let newData = undefined;
  let colInfo = undefined;
  if (data.tBody && data.tHeader) {
    newData = resetData(data.tBody, data.tHeader);
    const colData = rowToCol(newData); // 行列转换
    colInfo = getMaxValueLength(colData, 1); // 获取整列数据表格宽度信息
  }
  let secHeader = undefined;
  if (data.tHeader) {
    secHeader = resetHeader(data.tHeader);
  }
  const workbook = new ExcelJs.Workbook();
  workbook.created = new Date();
  workbook.modified = new Date();
  workbook.lastPrinted = new Date();
  workbook.properties.date1904 = true; // 设置日期系统
  const worksheet = workbook.addWorksheet('sheet', { properties: { tabColor: { argb: 'FF00FF00' } } });
  for (const key in colInfo) { // 设置列宽
    const dobCol = worksheet.getColumn(colInfo[key].colLocation);
    if (colInfo[key] && dobCol) {
      if (colInfo[key].colWidth && colInfo[key].colWidth > 15) {
        dobCol.width = colInfo[key].colWidth;
      } else {
        dobCol.width = 15;
      }
    } else {
      dobCol.width = 15;
    }
  }
  const firstRow = worksheet.getRow(1);
  firstRow.getCell(1).border = borderStyle;
  firstRow.getCell(1).value = {
    'richText': [{
      'font': {
        'bold': true,
        'size': 16,
        'color': { 'theme': 1 },
        'name': '黑体',
        'family': 2,
        'scheme': 'minor'
      },
      'text': tableName
    }]
  };
  firstRow.alignment = { vertical: 'middle', horizontal: 'center' };
  worksheet.mergeCells(1, 1, 1, secHeader.length + 1);
  worksheet.mergeCells('A2', 'A3');
  if (data.tHeader && data.tBody && data.tBody.length > 0) { // 获取表头
    const header = data.tHeader;
    const headerRow = worksheet.getRow(2); // 标题行
    headerRow.alignment = { vertical: 'middle', horizontal: 'center' };
    let startRow = 2; // 从第二列开始
    for (const index in header) {
      headerRow.getCell(1).value = header[0].name;
      headerRow.getCell(1).border = borderStyle;
      if (index > 0) {
        let step = header[index].children.length;
        let endRow = startRow + step - 1;
        headerRow.getCell(startRow).value = header[index].name;
        headerRow.getCell(startRow).border = borderStyle;
        worksheet.mergeCells(2, startRow, 2, endRow);
        startRow = endRow + 1;
      }
    }
    const secHeaderRow = worksheet.getRow(3);  // 遍历二级标题行
    for (const key in secHeader) {
      const index = parseInt(key);
      secHeaderRow.getCell(index + 2).value = secHeader[index];
      secHeaderRow.getCell(index + 2).alignment = { vertical: 'middle', horizontal: 'center' }; // 内容居中
      secHeaderRow.getCell(index + 2).border = borderStyle;
    }
    for (const key in newData) { // 遍历数据体;
      const lineData = newData[key];
      if (lineData.length > 0) {
        let row = worksheet.getRow(parseInt(key) + 4);
        for (const index in lineData) {
          const num = parseInt(index);
          if (num > 0) {
            row.getCell(num + 1).value = changeType(lineData[num]);
          } else {
            row.getCell(num + 1).value = lineData[num];
          }
          row.getCell(num + 1).alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }; // 内容居中
          row.getCell(num + 1).border = borderStyle;
        }
      }
    }
    workbook.xlsx.writeBuffer().then(buffer => { // 保存设置;
      saveAs(new Blob([buffer], {
        type: 'application/octet-stream'
      }), tableName + ".xlsx");
    });
  }

  function getMaxValueLength(data, startColNum) {
    let colInfo = [];
    let location = startColNum;
    if (data.length > 0) {
      for (const index in data) {
        if (index > 0) {
          let maxNum = 0;
          for (const key in data[index]) {
            const str = String(changeType(data[index][key]));
            let strLength = 0;
            if (!str) {
              strLength = 15;
            } else {
              strLength = getStringLength(str);
            }
            if (maxNum < strLength) {
              maxNum = strLength;
            }
          }
          let col = {
            colLocation: getExcelColName(location - 1),
            colWidth: maxNum
          };
          colInfo.push(col);
        } else {
          let col = {
            colLocation: getExcelColName(location - 1),
            colWidth: 20
          };
          colInfo.push(col);
        }
        location += 1;
      }
    }
    return colInfo;
  }

  function getStringLength(str) {
    let length = 0;
    for (let index = 0; index < str.length; index++) {
      if (isChinese(str.charAt(index))) {
        length += 3;
      } else {
        length += 1;
      }
    }
    return length + 4;
  }

  function isChinese(temp) {
    var re = /[^\u4E00-\u9FA5]/;
    if (re.test(temp)) return false;
    return true;
  }

  function getExcelColName(n) {
    var ordA = 'A'.charCodeAt(0);
    var ordZ = 'Z'.charCodeAt(0);
    var len = ordZ - ordA + 1;
    var s = "";
    while (n >= 0) {
      s = String.fromCharCode(n % len + ordA) + s;
      n = Math.floor(n / len) - 1;
    }
    return s;
  }

  function rowToCol(data) {
    let colData = [];
    if (data && data.length > 0) {
      for (const key in data[0]) {
        colData.push([]);
      }
      for (const key in data) {
        for (const index in data[key]) {
          colData[index][key] = data[key][index];
        }
      }
    }
    return colData;
  }

  function resetHeader(data) {
    const resArr = [];
    for (const key in data) {
      if (key > 0 && data[key].children.length > 0) {
        for (const index in data[key].children) {
          resArr.push(data[key].children[index]);
        }
      }
    }
    return resArr;
  }

  function changeType(data) {
    let newData = data;
    if (newData === "") {
      return "";
    }
    if (newData.type === "text" || newData.type === "text" || newData.type === "text") {
      return newData.value;
    }
    if (newData.type === "checkboxgroup") {
      const arr = newData.value || [];
      let res = "";
      for (const key in arr) {
        if (key > 0) {
          res += ", " + arr[key];
        } else {
          res += arr[key];
        }
      }
      return res;
    }
    if (newData.type === "image") {
      const arr = newData.value || [];
      let res = "";
      for (const key in arr) {
        if (key > 0) {
          res += ", " + arr[key].url;
        } else {
          res += arr[key].url;
        }
      }
      return res;
    }
    return newData.value;
  }

  function resetData(data, headerData) {
    const returnData = [];
    const arr = data || [];
    for (const key in arr) {
      const line = [];
      line.push(arr[key].time);
      for (const index in arr[key].oneLine) {
        if (arr[key].oneLine[index].items && arr[key].oneLine[index].items.length > 0) {
          for (const k in arr[key].oneLine[index].items) {
            line.push(arr[key].oneLine[index].items[k]);
          }
        } else {
          // 空数据长度获取
          let spaceLength = getLong(index, headerData);
          for (let index = 0; index < spaceLength; index++) {
            line.push("");
          }
        }
      }
      returnData.push(line);
    }
    return returnData;
  }

  function getLong(index, data) {
    let length = 0;
    for (const key in data) {
      if ((parseInt(key) - 1) === parseInt(index)) {
        length = data[key].children.length;
      }
    }
    return length;
  }

  function saveAs(obj, fileName) {
    let excelTable = document.createElement('a');
    excelTable.download = fileName || '下载';
    excelTable.href = URL.createObjectURL(obj);
    excelTable.click();
    setTimeout(function () {
      URL.revokeObjectURL(obj);
    }, 100);
  }
}