web前端读取和导出Excel

前端读取、导出 excel 的需求越来越多，也难免需要支持修改样式、合并行复杂表格，以及添加数据校验等功能。

本文主要分享和记录以下功能的实现

• 表格读取
• 简单表格导出
• 为表格添加样式（背景色、字体、字号、字体颜色等）
• 设置行高、列宽
• 添加数据校验（必填、类型校验、序列选择等）
• 行合并、列合并

1.技术选型

1.1.xlsx

https://www.npmjs.com/package/xlsx

xlsx下载量最高和 star最多的库，但是免费版不支持修改样式以及添加数据校验

1.2.exceljs

https://www.npmjs.com/package/exceljs

exceljs拥有中文文档，对国内开发者很友好，支持修改样式和数据校验

workbook：工作簿，可以理解为整个 excel 表格

worksheet：工作表，即 Excel 表格中的 sheet 页

columns：列，通过 worksheet.columns可设置表头

// 设置表头
worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'Age', key: 'age', width: 10, outlineLevel: 1 }
];
// 通过键，字母和基于1的列号访问单个列
const idCol = worksheet.getColumn('id');
const nameCol = worksheet.getColumn('B');
const ageCol = worksheet.getColumn(3);

row：行，可以添加一行或者同时添加多行数据

// 通过 json 添加一行数据，需要先设置 columns
worksheet.addRow({id: 1, name: 'John Doe', age: 18});
// 循环每一行
worksheet.eachRow(function (row, rowNumber) {

})

cell：单元格，设置样式、添加数据校验、合并等

// 循环行内每个单元格
ageCol.eachCell(function (col, colNumber) {

})
// 设置样式
ageCol.width = 15
// 添加数据校验
nameCol.dataValidation = {
  type: 'textLength',
  operator: 'greaterThan',
  showErrorMessage: true,
  formulae: [0],
  error: '必填项不能为空'
}
// 序列选择
nameCol.dataValidation = {
  type: 'list',
  allowBlank: !item.required,
  showErrorMessage: true,
  formulae: ['张三', '李四', '赵五']
}

合并单元格

// 合并一系列单元格
worksheet.mergeCells('A4:B5');
// 按左上，右下合并
worksheet.mergeCells('K10', 'M12');
// 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
worksheet.mergeCells(10,11,12,13);
// 取消单元格合并将打破链接的样式
worksheet.unMergeCells('A4');

2.Excel读取

这里仅读取第一个工作表，如果要访问所有工作表可以使用 workbook.eachSheet

import ExcelJS from 'exceljs'

/**
 * 读取excel文件
 * @param {ArrayBuffer} buffer
 */
export async function readExcelToJson(buffer) {
  let columns = []
  const data = []

  const workbook = new ExcelJS.Workbook()
  await workbook.xlsx.load(buffer)

  const worksheet = workbook.getWorksheet(1) // 获取第一个worksheet
  worksheet.eachRow(function (row, rowNumber) {
    const rowValues = row.values
    rowValues.shift()
    if (rowNumber === 1) {
      columns = rowValues
    } else {
      const sheetToJson = {}
      rowValues.forEach((item, index) => {
        sheetToJson[columns[index]] = item
      })
      data.push(sheetToJson)
    }
  })

  return { columns, data }
}

3.数据导出Excel

3.1.设置样式

worksheet.eachRow(function (row, rowNumber) {
  // 对齐：垂直、水平居中
  row.alignment = { vertical: 'middle', horizontal: 'center' }

  if (rowNumber === 1) {
    // 填充首行
    row.eachCell(function (cell) {
      cell.font = {
        color: { argb: 'FFFFFFFF' }
      }
      cell.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FF808080' }
      }
    })
  }
})

3.2.自动列宽

worksheet.eachRow(function (row, rowNumber) {
  if (rowNumber === 1) {
    // 设置列宽
    row.eachCell(function (cell, colNumber) {
      const column = worksheet.getColumn(colNumber)
      column.width = calcWidth(column.values)
    })
  }
})

/**
 * 计算列单元格最大宽度
 * @param {Array} data 列的所有数据
 * @returns {Number}
 */
function calcWidth(data) {
  let maxWidth = 0
  data.forEach(val => {
    const temp = countWord(val.toString())

    if (maxWidth < temp) {
      maxWidth = temp
    }
  })
  return maxWidth
}

/**
 * 计算字符数
 * @param {String} str
 * @returns {Number}
 */
function countWord(str) {
  let intLength = 0
  for (let i = 0; i < str.length; i++) {
    if (str.charCodeAt(i) < 0 || str.charCodeAt(i) > 255) {
      intLength += 2.2
    } else {
      intLength += 1.4
    }
  }
  return intLength
}

3.3.添加数据校验

// 设置单元格属性，如注释、数据校验
const header = worksheet.getRow(1)
columns.forEach(item => {
  const headerCell = header.getCell(item.key)
  // 单元格注释
  if (item.$note) {
    headerCell.note = item.$note
  }

  const column = worksheet.getColumn(item.key)
  // 数据校验
  if (item.$dataValidation) {
    column.eachCell(function (cell, rowNumber) {
      if (rowNumber !== 1) {
        cell.dataValidation = item.$dataValidation
      }
    })
  }
})

3.4.合并行

数组下标从0开始，但是单元格从1开始，所以行列都需要加1

/**
 * 合并行
 * merges [{ start: { r: 开始行, c: 开始列 }, end: { r: 结束行, c: 结束列 }}]
 */
merges.forEach(({ start, end }) => {
  // 按开始行，开始列，结束行，结束列合并
  worksheet.mergeCells(start.r + 1, start.c + 1, end.r + 1, end.c + 1)
})

3.5.保存文件

这里需要用到file-saver

import { saveAs } from 'file-saver'

/**
 * 保存文件
 * @param {Object} workbook ExcelJS 工作表实例
 * @param {String} filename 文件名
 */
async function writeFile(workbook, filename = 'excel-js') {
  const buffer = await workbook.xlsx.writeBuffer()
  saveAs(
    new Blob([buffer], {
      type: 'application/octet-stream'
    }),
    `${filename}.xlsx`
  )
}

4.Table元素导出Excel

4.1.读取Table

/**
 * 读取table元素数据
 * @param {Element} table
 * @returns {Array} [数据，合并单元格]
 */
function generateArray(table) {
  let out = []
  let rows = table.querySelectorAll('tr')
  let ranges = []
  for (let R = 0; R < rows.length; ++R) {
    const outRow = []
    let row = rows[R]
    let columns = row.querySelectorAll('th, td')
    for (let C = 0; C < columns.length; ++C) {
      let cell = columns[C]
      let colspan = cell.getAttribute('colspan')
      let rowspan = cell.getAttribute('rowspan')
      let cellValue = cell.innerText
      if (cellValue !== '' && cellValue == +cellValue) cellValue = +cellValue

      // Skip ranges
      ranges.forEach(function ({ start, end }) {
        if (R >= start.r && R <= end.r && outRow.length >= start.c && outRow.length <= end.c) {
          for (let i = 0; i <= end.c - start.c; ++i) outRow.push(null)
        }
      })

      // Handle Row Span
      if (rowspan || colspan) {
        rowspan = +rowspan || 1
        colspan = +colspan || 1
        ranges.push({
          start: {
            r: R,
            c: outRow.length
          },
          end: {
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1
          }
        })
      }

      // Handle Value
      outRow.push(cellValue !== '' ? cellValue : null)

      // Handle Colspan
      if (colspan) {
        outRow.push(...Array(colspan - 1).fill(null))
      }
    }
    out.push(outRow)
  }
  return [out, ranges]
}

4.2.Table数据导出

这里需要用到上面 数据导出Excel 的方法

/**
 * 导出table到excel
 * @param {Element} table
 * @param {String} filename
 */
export function exportTableToExcel(table, filename) {
  const [out, ranges] = generateArray(table)

  return exportJsonToExcel({
    columns: [], // 表头
    data: out,
    merges: ranges,
    filename
  })
}

5.完整代码

// @link https://github.com/exceljs/exceljs
import ExcelJS from 'exceljs'
import { saveAs } from 'file-saver'

/**
 * 导出JSON到excel文件
 * @param {Object} param
 */
export function exportJsonToExcel({
  autoWidth = true, // 自动列宽
  columns, // 表头
  data, // 数据
  merges = [], // 合并行，行列下标从0开始 [{ start: { r: 开始行, c: 开始列 }, end: { r: 结束行, c: 结束列 }}]
  filename // 导出excel文件名称
} = {}) {
  const workbook = new ExcelJS.Workbook()
  const worksheet = workbook.addWorksheet('My Sheet')

  // 设置工作簿属性，默认列宽
  if (!autoWidth) {
    worksheet.properties.defaultColWidth = 15
  }

  if (typeof columns[0] !== 'object') {
    columns = columns.map(item => {
      return {
        header: item,
        key: item
      }
    })
  }

  worksheet.columns = columns
  worksheet.addRows(data)

  // 合并行
  merges.forEach(({ start, end }) => {
    // 按开始行，开始列，结束行，结束列合并
    worksheet.mergeCells(start.r + 1, start.c + 1, end.r + 1, end.c + 1)
  })

  // 设置单元格属性，如注释、数据校验
  const header = worksheet.getRow(1)
  columns.forEach(item => {
    const headerCell = header.getCell(item.key)
    // 单元格注释
    if (item.$note) {
      headerCell.note = item.$note
    }

    const column = worksheet.getColumn(item.key)
    // 数据校验
    if (item.$dataValidation) {
      column.eachCell(function (cell, rowNumber) {
        if (rowNumber !== 1) {
          cell.dataValidation = item.$dataValidation
        }
      })
    }
  })

  // 设置样式
  setStyle(worksheet, { isFillHeader: !!columns.length, autoWidth })

  // 添加回调处理
  return writeFile(workbook, filename)
}

/**
 * 设置样式
 * @param {Object} worksheet 工作簿
 * @param {Boolean} isFillHeader 是否填充表头
 */
function setStyle(worksheet, { isFillHeader, autoWidth }) {
  worksheet.eachRow(function (row, rowNumber) {
    // 对齐：垂直、水平居中
    row.alignment = { vertical: 'middle', horizontal: 'center' }

    if (rowNumber === 1) {
      if (isFillHeader) {
        // 填充首行
        row.eachCell(function (cell) {
          cell.font = {
            color: { argb: 'FFFFFFFF' }
          }
          cell.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FF808080' }
          }
        })
      }

      // 设置列宽
      if (autoWidth) {
        row.eachCell(function (cell, colNumber) {
          const column = worksheet.getColumn(colNumber)
          column.width = calcWidth(column.values)
        })
      }
    }
  })
}

/**
 * 计算列单元格最大宽度
 * @param {Array} data 列的所有数据
 * @returns {Number}
 */
function calcWidth(data) {
  let maxWidth = 0
  data.forEach(val => {
    const temp = countWord(val.toString())

    if (maxWidth < temp) {
      maxWidth = temp
    }
  })
  return maxWidth
}

/**
 * 计算字符数
 * @param {String} str
 * @returns {Number}
 */
function countWord(str) {
  let intLength = 0
  for (let i = 0; i < str.length; i++) {
    if (str.charCodeAt(i) < 0 || str.charCodeAt(i) > 255) {
      intLength += 2.2
    } else {
      intLength += 1.4
    }
  }
  return intLength
}

/**
 * 导出table到excel
 * @param {Element} table
 * @param {String} filename
 */
export function exportTableToExcel(table, filename) {
  const [out, ranges] = generateArray(table)

  return exportJsonToExcel({
    columns: [], // 表头
    data: out,
    merges: ranges,
    filename
  })
}

/**
 * 读取table元素数据
 * @param {Element} table
 * @returns {Array} [数据，合并单元格]
 */
function generateArray(table) {
  let out = []
  let rows = table.querySelectorAll('tr')
  let ranges = []
  for (let R = 0; R < rows.length; ++R) {
    const outRow = []
    let row = rows[R]
    let columns = row.querySelectorAll('th, td')
    for (let C = 0; C < columns.length; ++C) {
      let cell = columns[C]
      let colspan = cell.getAttribute('colspan')
      let rowspan = cell.getAttribute('rowspan')
      let cellValue = cell.innerText
      if (cellValue !== '' && cellValue == +cellValue) cellValue = +cellValue

      // Skip ranges
      ranges.forEach(function ({ start, end }) {
        if (R >= start.r && R <= end.r && outRow.length >= start.c && outRow.length <= end.c) {
          for (let i = 0; i <= end.c - start.c; ++i) outRow.push(null)
        }
      })

      // Handle Row Span
      if (rowspan || colspan) {
        rowspan = +rowspan || 1
        colspan = +colspan || 1
        ranges.push({
          start: {
            r: R,
            c: outRow.length
          },
          end: {
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1
          }
        })
      }

      // Handle Value
      outRow.push(cellValue !== '' ? cellValue : null)

      // Handle Colspan
      if (colspan) {
        outRow.push(...Array(colspan - 1).fill(null))
      }
    }
    out.push(outRow)
  }
  return [out, ranges]
}

/**
 * 保存文件
 * @param {Object} workbook ExcelJS 工作表实例
 * @param {String} filename 文件名
 */
async function writeFile(workbook, filename = 'excel-js') {
  const buffer = await workbook.xlsx.writeBuffer()
  saveAs(
    new Blob([buffer], {
      type: 'application/octet-stream'
    }),
    `${filename}.xlsx`
  )
}

/**
 * 读取excel文件
 * @param {ArrayBuffer} buffer
 */
export async function readExcelToJson(buffer) {
  let columns = []
  const data = []

  const workbook = new ExcelJS.Workbook()
  await workbook.xlsx.load(buffer)

  const worksheet = workbook.getWorksheet(1) // 获取第一个worksheet
  worksheet.eachRow(function (row, rowNumber) {
    const rowValues = row.values
    rowValues.shift()
    if (rowNumber === 1) {
      columns = rowValues
    } else {
      const sheetToJson = {}
      rowValues.forEach((item, index) => {
        sheetToJson[columns[index]] = item
      })
      data.push(sheetToJson)
    }
  })

  return { columns, data }
}

参考文档：

• 前端复杂表格导出excel，一键导出 Antd Table 看这篇就够了（附源码） - 掘金
• https://github.com/PanJiaChen/vue-element-admin/blob/33a93a12b4dc6429dbe91f43ef17fb76236f2d6c/src/vendor/Export2Excel.js