ConstructionOperation/web/maintain/billsGuidance_lib/js/exportExcel.js

class ExportExcel {
  workBook = null;

  sheet = null;
  
  sheetIndex = 0;

  border = new GC.Spread.Sheets.LineBorder('#000', GC.Spread.Sheets.LineStyle.thin);

  excelIo = new GC.Spread.Excel.IO();

  $info = $('#excel-info');

  // 表格当前画到的行
  curRow = 0;

  curMaxCol = 0;

  billTree = null;

  libID = '';

  // 分部章节单元格数据
  sections = [];


  // 清单ID - 清单精灵映射
  billIDElfMap = {};

  // 叶子清单ID
  leafIDs = [];

  // 叶子清单总数量
  total = 0;

  // 清单ID - 最大列映射
  maxColMap = {};

  // 叶子节点的父项ID
  leafParentIDs = [];

  // 字体
  font = 'normal normal 9pt 宋体';
  blockTitleFont = 'bold normal 9pt 宋体';
  bigSectionFont = 'bold normal 12pt 黑体';
  leafSectionFont = 'bold normal 11pt 黑体';

  constructor(workBook, billTree, libID) {
    this.workBook = workBook;
    this.sheet = this.workBook.getSheet(0);
    this.billTree = billTree;
    this.libID = libID;
    this.leafIDs = billTree.items.filter(node => !node.children.length).map(node => node.data.ID);
    this.total = this.leafIDs.length;
  }

  // 导出
  async export() {
    if (!this.workBook) {
      return;
    }
    const json = this.workBook.toJSON();
    this.excelIo.save(json, function (blob) {
      saveAs(blob, '清单精灵排版.xlsx');
    }, function (e) {
        // process error
        alert(e);
        console.log(e);
    });
    }

    // 将数据画在工作簿上
    async paintOnWorkBook() {
      this.updateProcessInfo();
      // 根据专业区分不同sheet
      for (const root of this.billTree.roots) {
        this.initSheet(this.sheetIndex++, root.data.name);
        const nodes = [root, ...root.getPosterity()];
        await this.paintOnSheet(nodes);
      }
      this.afterPaint();
    }

  /* ========================================================以下为私有方法============================================== */

  // 当前到多少条叶子清单
  get curProcessCount () {
    return this.total - this.leafIDs.length;
  }

  // 将数据画在表格上
  async paintOnSheet(nodes) {
    this.updateProcessInfo();
    let i = 0;
    this.sheet.suspendPaint();
    this.sheet.suspendEvent();
    for (const billNode of nodes) {
      if (this.curRow >= this.sheet.getRowCount()) {
        this.sheet.addRows(this.curRow, 2);
      }
      this.curMaxCol = 0;
      if (billNode.children.length) {
        // 清单分部章节，合并的列数依赖主体表格，所以先存数据画空行，后续再画具体内容
        this.sections.push({ ID: billNode.data.ID, isNotLeaf: true, row: this.curRow, col: 0, text: `${billNode.data.code} ${billNode.data.name}`, rowCount: 1, colCount: 1, font: this.bigSectionFont });
        this.curRow++;
      } else {
        if (!this.leafParentIDs.includes(billNode.data.ParentID)) {
          this.leafParentIDs.push(billNode.data.ParentID);
        }
        if (this.maxColMap[billNode.data.ParentID] === undefined) {
          this.maxColMap[billNode.data.ParentID] = 0;
        }
        // 叶子清单，合并的列数依赖主体表格，所以先存数据画空行，后续再画具体内容
        const billText = `编码：${billNode.data.code}    名称：${billNode.data.name}    单位：${billNode.data.unit}`;
        this.sections.push({ ID: billNode.data.ID, row: this.curRow, col: 0, text: billText, rowCount: 1, colCount: 1, font: this.leafSectionFont });
        this.curRow++;
        // 画清单精灵表格
        const elfTree = await this.getElfTree(billNode.data.ID);
        if (!elfTree) {
          continue;
        }
        this.drawBlock(elfTree);
        if (this.maxColMap[billNode.data.ParentID] < this.curMaxCol) {
          this.maxColMap[billNode.data.ParentID] = this.curMaxCol;
        }
        i++;
        
      }
      /* if (i === 10) {
        break;
      } */
    }
    // 画章节
    this.drawSection(this.sections);

    const range = this.sheet.getRange(0, 0, this.sheet.getRowCount(), this.sheet.getColumnCount());
    range.vAlign(GC.Spread.Sheets.VerticalAlign.center);
    range.wordWrap(true);
    this.sheet.resumeEvent();
    this.sheet.resumePaint();
  }

  // 画完表格后的处理
  afterPaint() {
      $('#excel-dialog').width('800px');
      $('#excel-spread').show();
      this.workBook.refresh();
      $('#export-excel-confirm').show();
  }

  // 超出范围追加行列
  checkRange(blockRange) {
    // 不够行数，追加行数
    const needRows = this.curRow + blockRange.rowCount;
    const curRowCount = this.sheet.getRowCount();
    if (curRowCount < needRows) {
      this.sheet.addRows(this.curRow, needRows - curRowCount + 5);
    }
    // 不够列数，追加列数
    const curColCount = this.sheet.getColumnCount();
    if (curColCount < blockRange.colCount) {
      this.sheet.addColumns(curColCount - 1, blockRange.colCount - curColCount);
    }
  }

  // 画单元格、合并单元格
  drawCell(block, addCurRow) {
    block.forEach(item => {
      const row =  addCurRow ? item.row + this.curRow : item.row;
      this.sheet.addSpan(row, item.col, item.rowCount, item.colCount);
      this.sheet.setFormatter(row, item.col, '@');
      const font = item.font || this.font;
      this.sheet.getCell(row, item.col).font(font);
      if (item.isTitle) {
        // 标题水平居中，字体加粗
        const range = this.sheet.getRange(row, item.col, 1, 1);
        range.hAlign(GC.Spread.Sheets.VerticalAlign.center);
      } else if (item.isNotLeaf) {
        const range = this.sheet.getRange(row, item.col, 1, 1);
        range.hAlign(GC.Spread.Sheets.VerticalAlign.center);
      }
      this.sheet.setText(row, item.col, item.text);
    });
  }

  // 画边框
  drawBorder(range) {
    range.setBorder(this.border, { all: true })
  }

  // 更新进度信息
  updateProcessInfo() {
    this.$info.text(`导出排版中: ${this.curProcessCount} / ${this.total}`)
  }

  // 完善章节清单ID - 最大列映射
  setSectionMaxCol() {
    this.leafParentIDs.forEach(ID => {
      const node = this.billTree.findNode(ID);
      if (!node) {
        return;
      }
      node.children.forEach(child => {
        this.maxColMap[child.data.ID] = this.maxColMap[ID];
      });
      let parent = node.parent;
      while (parent && this.maxColMap[parent.data.ID] === undefined) {
        this.maxColMap[parent.data.ID] = this.maxColMap[ID];
        parent = parent.parent;
      }
    });
  }


  // 画章节
  drawSection(sections) {
    this.setSectionMaxCol();
    sections.forEach(section => {
      const maxCol = this.maxColMap[section.ID] || 0;
      section.colCount = maxCol + 1;
    });
    this.drawCell(sections, false);
  }

  // 画主体表格
  async drawBlock(elfTree) {
    const block = this.getBlock(elfTree);
    if (!block.length) {
      return;
    }
    const blockRange = this.getBlockRange(elfTree);
    this.checkRange(blockRange);
    const range = this.sheet.getRange(block[0].row + this.curRow, block[0].col, blockRange.rowCount, blockRange.colCount)
    // 画单元格
    this.drawCell(block, true);
    // 画边框
    this.drawBorder(range);
    this.curRow += blockRange.rowCount;
  }

  // 精灵树数据转换为表格块单元格数据
  getBlock(elfTree) {
    const block = [];
    // 表格正文
    const blockContent = this.getBlockContent(elfTree);
    // 表格标题：获取完正文，才知道标题的合并列数，因此先获取表格正文，再获取表格标题
    const blockTitle = this.getBlockTitle();
    // 表格正文的行号，整体下移
    blockContent.forEach(cellInfo => {
      cellInfo.row += 1;
    });
    block.push(...blockTitle);
    block.push(...blockContent);
    return block;
  }

  // 获取表格块正文单元格数据
  getBlockContent(elfTree) {
    const block = elfTree.items.map(node => {
      // rowCount、colCount标记合并单元格范围
      const rowCount = node.posterityLeafCount() || 1;
      const parentRow = node.parent && node.parent.cellInfo ? node.parent.cellInfo.row : 0;
      // let prevRowCount = node.preSibling && node.preSibling.cellInfo ? node.preSibling.cellInfo.row + node.preSibling.cellInfo.rowCount - parentRow  : 0;
      const prev = node.prevNode();
      let prevRowCount = prev && prev.cellInfo ? prev.cellInfo.row + prev.cellInfo.rowCount - parentRow  : 0;
      const row = parentRow + prevRowCount;
      const col = node.depth();
      const name = node.data.type === itemType.ration ? node.data.name.split(' ')[0] : node.data.name;
      const text = `${isProcessNode(node) && node.data.required ? '* ' : ''}${name}`
      node.cellInfo = {
        row,
        col,
        rowCount,
      };
      if (this.curMaxCol < col) {
        this.curMaxCol = col;
      }
      return {
        row,
        col,
        rowCount,
        text,
        isRation: node.data.type === itemType.ration,
        colCount: 1,
        font: this.font,
      }
    });
    this.moveRationTextToLastCol(block);
    return block;
  }

  // 获取表格标题单元格数据，计算标题合并列依赖正文表格
  getBlockTitle() {
    return [
      { row: 0, col: 0, rowCount: 1, colCount: 1, text: '工序', isTitle: true, font: this.blockTitleFont },
      { row: 0, col: 1, rowCount: 1, colCount: this.curMaxCol === 0 ? 1 : this.curMaxCol - 1, text: '选项', isTitle: true, font: this.blockTitleFont },
      { row: 0, col: this.curMaxCol === 0 ? 2 : this.curMaxCol, rowCount: 1, colCount: 1, text: '定额', isTitle: true, font: this.blockTitleFont },
    ];
  }
  
  // 将定额文本挪到表格最大列处显示
  moveRationTextToLastCol(block) {
    block.forEach(cellInfo => {
      if (cellInfo.isRation) {
        cellInfo.col = this.curMaxCol;
      }
    });
  }

  // 从表格中获取最大列号
  getMaxCol(block) {
    return Math.max(...block.map(cellInfo => cellInfo.col));
  }

  // 根据精灵树数据，获取表格块range
  getBlockRange(elfTree) {
    const rowCount = elfTree.roots.reduce((prev, node) => prev + (node.posterityLeafCount() || 1), 0) + 1; // +1是因为有一行标题
    const a = Date.now();
    const colCount = Math.max(...elfTree.items.map(node => node.depth())) + 1;
    console.log(Date.now() - a);
    return {
      rowCount,
      colCount: colCount === 1 ? 3 : colCount, // 有些表格没有选项和定额
    }
  }

  // 获取清单的精灵树
  async getElfTree(billID) {
    const items = await this.getElfItems(billID);
    if (!items || !items.length) {
      return null;
    }
    const tree = idTree.createNew({ id: 'ID', pid: 'ParentID', nid: 'NextSiblingID', rootId: -1, autoUpdate: true });
    tree.loadDatas(items);
    return tree;
  }

  // 获取清单的精灵数据
  async getElfItems(billID) {
    if (this.billIDElfMap[billID]) {
      return this.billIDElfMap[billID];
    }
    if (!this.leafIDs.length) {
      return null;
    }
    const count = 20; // 每次拉取数据条数
    const billIDs = this.leafIDs.splice(0, count)
    await setTimeoutSync(null, 100);
    const items = await ajaxPost('/billsGuidance/api/getItemsByBillIDs', { guidanceLibID: this.libID, billIDs });
    this.updateProcessInfo();
    items.forEach(item => {
      (this.billIDElfMap[item.billsID] || (this.billIDElfMap[item.billsID] = [])).push(item)
    });
    return this.billIDElfMap[billID];
  }

  initSheet(index, name) {
    this.curRow = 0;
    this.sections = [];
    this.leafParentIDs = [];
    if (index !== 0) {
      // 工作簿自身会有一张表
      this.workBook.addSheet(index);
    }
    this.sheet = this.workBook.getSheet(index);
    this.sheet.name(name);
    for (let col = 0; col < this.sheet.getColumnCount(); col++) {
      this.sheet.setColumnWidth(col, 100);
    }
  }
}