Front-end merged cell complex header export based on xlsx-style

Project function

• Combining cells at any level and exporting complex headers

• Table headers are directly mapped with data items, without maintaining the matching relationship of Excel index items

• Automatically calculate and generate table headers and merge cell configuration information

Online example

• Step 0: If you need a quick test, you can click the example button at the top to quickly fill in the merged cells at each level Excel test data

• Step 1: Enter the test data source, that is, the data array obtained from the backend.
  

• Step 2: Enter the mapping relationship array between "Excel header structure string" and "target data structure key"

  

  • "key" is the Excel header, the hierarchical relationship of each column. For example, "Basic Information. Age" corresponds to the relationship in the second column of the Excel table. The first level is "Basic Information" and the second level is "Age".

  

  • "value" is the hierarchical relationship of the data source data structure. "baseInfo.age" means to set the value of the baseInfo.age property in the data source to the second column of the Excel table.

  

  • [Special Note 1] The order of the array is the order of the Excel table header. The configuration of the N-1 index item, which is the menu configuration of the Nth column in Excel

  

  • [Special attention 2] There are no mandatory constraints and requirements between the hierarchical mapping relationship of the Excel table and the hierarchical mapping relationship of the target object. For example, "mobile phone number" in Excel has only one layer of structure, but it can be completely transformed into the second-level structure of "contact.phone" in the target object. vice versa.

  

• Step 3: Click the "Export Excel" button to complete the data export to Excel file.

Introduction to the parsed page

• The left area is the data of each cell after the data source is converted into Excel data. Including the header data at the top, and specific data rows.
• The area on the right is the merged cell configuration item of the Excel header data. Using this configuration data, you can complete the cell merging of the header through the "xlsx-style" library.

How to use the specific function

• The example follows the principle of least knowledge. The core.js file in the project is the file where the conversion function is located. There are less than 200 lines of code in it. You can directly paste and copy it into the desired project.

• The example directly uses the way the script file is imported as a whole. Therefore, when migrating to an npm-based project, you need to change the usage of the 'Window.XLS.write' global variable in core.js to the usage of import { write } from 'xlsx' .
• If this project really helps small partners, if they have needs, they can raise issues in github. If necessary, they will supplement the version based on npm and the version with ts type constraints.

All core source code

 /**
 * 将数据源，转化为 Excel 单元格数据，并生成 Excel 表头
 * @param dataList 数据源
 * @param textKeyMaps // Excel 中文表头层级与数据源英文层级间的映射表
 * @param headerFirstRow // 表头首行所在行，为了兼容表格顶部还插入有其他 Excel 行的情况，即表格不在首行
 * @returns {
    headerMerges, // 表头合并单元格配置项
    cells, // 表头及数据项的 Excel 单元格数组
  }
 */
function transformDataToSheetCells(dataList, textKeyMaps, headerFirstRow = 0) {

  // 获取从 textKeyMaps 解析，拆分后的，中英文 keys 数组
  function getKeysList(textKeyMaps) {
    const chineseKeysList = []
    const englishKeysList = []
    textKeyMaps.forEach(textKeyMap => {
      const keyStr = Object.values(textKeyMap)[0]
      const textStr = Object.keys(textKeyMap)[0]
      englishKeysList.push(keyStr.split('.'))
      chineseKeysList.push(textStr.split('.'))
    })
    return {
      englishKeysList,
      chineseKeysList
    }
  }

  // 获取表头行数
  function getHeaderRowNum(chineseKeysList) {
    let maxLevel = 1
    chineseKeysList.forEach(chineseKeys => {
      maxLevel = Math.max(chineseKeys.length, maxLevel)
    })
    return maxLevel
  }

  // 获取表头行 cell 数据
  function getHeaderRows(headerRowNum, chineseKeysList) {
    const headerRows = []
    // 初始化，全部设置为 ''
    for (let rowIndex = 0; rowIndex < headerRowNum; rowIndex++) {
      const row = new Array(chineseKeysList.length).fill('')
      headerRows.push(row)
    }
    // 将表头 cell 设置为对应的中文
    chineseKeysList.forEach((chineseKeys, colIndex) => {
      for (let rowIndex = 0; rowIndex < chineseKeys.length; rowIndex++) {
        headerRows[rowIndex][colIndex] = chineseKeys[rowIndex]
      }
    })

    // 去除需要合并单元格的每一列中。重复的 cell 数据，重复的，则设置为 ''
    headerRows.forEach(headerRow => {
      let lastColValue = ''
      headerRow.forEach((cell, colIndex) => {
        if (lastColValue !== cell) {
          lastColValue = cell
        } else {
          headerRow[colIndex] = ''
        }
      })
    })

    return headerRows
  }

  // 获取合并单元格配置
  function getMerges(headerRowNum, chineseKeysList) {
    const merges = []
    // 竖向合并
    chineseKeysList.forEach((chineseKeys, colIndex) => {
      // 当前列，每一行都有数据，这无需要竖向合并
      if (chineseKeys.length === headerRowNum) {
        return
      }
      // 否则。存在数据需要竖向合并，竖向合并的行数，即为比最高行数少的行数
      merges.push({
        s: {
          r: chineseKeys.length - 1 + headerFirstRow,
          c: colIndex,
        },
        e: {
          r: headerRowNum - 1 + headerFirstRow,
          c: colIndex,
        }
      })
    })
    // 横向合并
    for (let rowIndex = 0; rowIndex < headerRowNum; rowIndex++) {
      const rowCells = chineseKeysList.map(chineseKeys => chineseKeys[rowIndex])
      let preCell = '' // 前一个单元格
      let merge = null // 当前合并配置项
      rowCells.forEach((cell, colIndex) => {
        if (preCell === cell) { // 如果二者相同，则证明需要横向合并单元格
          if (!merge) { // merge 不存在，则创建，
            merge = {
              s: {
                r: rowIndex + headerFirstRow,
                c: colIndex - 1
              },
              e: {
                r: rowIndex + headerFirstRow,
                c: colIndex
              }
            }
            merges.push(merge) // 添加一个合并对象
          } else {
            merge.e.c = colIndex // 修改其合并结束列
          }
        } else {
          preCell = cell
          merge = null
        }
      })
    }
    return merges
  }

  // 获取转化数据结构为 Excel 数据行
  function getDataRows(dataList) {
    const dataRows = []
    dataList.forEach(dataItem => {
      const cells = []
      englishKeysList.forEach(keyLevel => {
        const value = keyLevel.reduce((dataItem, key) => dataItem[key] || '', dataItem).toString()
        cells.push(value)
      })
      dataRows.push(cells)
    })
    return dataRows
  }

  const { englishKeysList, chineseKeysList } = getKeysList(textKeyMaps)
  const headerRowNum = getHeaderRowNum(chineseKeysList)
  const headerMerges = getMerges(headerRowNum, chineseKeysList)
  const headerRows = getHeaderRows(headerRowNum, chineseKeysList)
  const dataRows = getDataRows(dataList)

  return {
    headerMerges,
    cells: [...headerRows, ...dataRows],
  }
}

/**
 * 导出为携带样式的 xlsx 文件
 * @param {*} param
 * @param  param.filename 导出的文件名
 * @param  param.worksheet 导出的 sheet 数据
 */
function toStyleXlsx({ filename, worksheet }) {
  const workbook = {
    SheetNames: [filename],
    Sheets: {
      [filename]: worksheet,
    },
  }

  // writeFile(workbook, filename, { bookType: 'xlsx' })
  let wopts = {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  }
  let wbout = window.XLS.write(workbook, wopts) // 使用xlsx-style 写入
  function s2ab(s) {
    let buf = new ArrayBuffer(s.length)
    let view = new Uint8Array(buf)
    for (let i = 0; i !== s.length; ++i) {
      // eslint-disable-next-line no-bitwise
      view[i] = s.charCodeAt(i) & 0xFF
    }
    return buf
  }
  saveAs(new Blob([s2ab(wbout)], { type: '' }), filename)
}

There is also an example of Excel merged cell complex header import analysis