excel.js 实现前端html 表格导出

文档

• 中文文档
  https://github.com/exceljs/exceljs/blob/master/README_zh.md

• git 地址
  https://github.com/exceljs/exceljs/tree/master

表格属性

批量设置表格列宽度

worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'D.O.B.', key: 'DOB', width: 10, outlineLevel: 1 }
];

• 设置高度

// 获取一个行对象。如果尚不存在，则将返回一个新的空对象
const row = worksheet.getRow(5);

// Get multiple row objects. If it doesn't already exist, new empty ones will be returned
const rows = worksheet.getRows(5, 2); // start, length (>0, else undefined is returned)

// 获取工作表中的最后一个可编辑行（如果没有，则为 `undefined`）
const row = worksheet.lastRow;

// 设置特定的行高
row.height = 42.5;

• 可以通过eachRow批量设置高度

// 遍历工作表中具有值的所有行
worksheet.eachRow(function(row, rowNumber) {
  console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});

// 遍历工作表中的所有行（包括空行）
worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) {
  console.log('Row ' + rowNumber + ' = ' + JSON.stringify(row.values));
});

图片插入表格实现

// 通过文件名将图像添加到工作簿
const imageId1 = workbook.addImage({
  filename: 'path/to/image.jpg',
  extension: 'jpeg',
});

// 通过 buffer 将图像添加到工作簿
const imageId2 = workbook.addImage({
  buffer: fs.readFileSync('path/to.image.png'),
  extension: 'png',
});

// 通过 base64  将图像添加到工作簿
const myBase64Image = "data:image/png;base64,iVBORw0KG...";
const imageId2 = workbook.addImage({
  base64: myBase64Image,
  extension: 'png',
});

导出excel文件

// 写入文件
const workbook = createAndFillWorkbook();
await workbook.xlsx.writeFile(filename);

// 写入流
await workbook.xlsx.write(stream);

// 写入 buffer
const buffer = await workbook.xlsx.writeBuffer();

合并单元格

// 合并一系列单元格
worksheet.mergeCells('A4:B5');

// ...合并的单元格被链接起来了
worksheet.getCell('B5').value = 'Hello, World!';
expect(worksheet.getCell('B5').value).toBe(worksheet.getCell('A4').value);
expect(worksheet.getCell('B5').master).toBe(worksheet.getCell('A4'));

// ...合并的单元格共享相同的样式对象
expect(worksheet.getCell('B5').style).toBe(worksheet.getCell('A4').style);
worksheet.getCell('B5').style.font = myFonts.arial;
expect(worksheet.getCell('A4').style.font).toBe(myFonts.arial);

// 取消单元格合并将打破链接的样式
worksheet.unMergeCells('A4');
expect(worksheet.getCell('B5').style).not.toBe(worksheet.getCell('A4').style);
expect(worksheet.getCell('B5').style.font).not.toBe(myFonts.arial);

// 按左上，右下合并
worksheet.mergeCells('K10', 'M12');

// 按开始行，开始列，结束行，结束列合并（相当于 K10:M12）
worksheet.mergeCells(10,11,12,13);

添加行数据

const table = ws.getTable('MyTable');

// 删除前两行
table.removeRows(0, 2);

// 在索引 5 处插入新行
table.addRow([new Date('2019-08-05'), 5, 'Mid'], 5);

// 在表格底部追加新行
table.addRow([new Date('2019-08-10'), 10, 'End']);

// 将表更改提交到工作表中
table.commit();

样式设置

// 在A1周围设置单个细边框
ws.getCell('A1').border = {
  top: {style:'thin'},
  left: {style:'thin'},
  bottom: {style:'thin'},
  right: {style:'thin'}
};

// 在A3周围设置双细绿色边框
ws.getCell('A3').border = {
  top: {style:'double', color: {argb:'FF00FF00'}},
  left: {style:'double', color: {argb:'FF00FF00'}},
  bottom: {style:'double', color: {argb:'FF00FF00'}},
  right: {style:'double', color: {argb:'FF00FF00'}}
};

// 在A5中设置厚红十字边框
ws.getCell('A5').border = {
  diagonal: {up: true, down: true, style:'thick', color: {argb:'FFFF0000'}}
};

rowspan colspan 实现参考

<template>
  <div>
    <table id="export-table" ref="table">
      <tr>
        <th>Header 1</th>
        <th>Header 2</th>
        <th>Header 3</th>
      </tr>
      <tr>
        <td rowspan="2">Rowspan 2</td>
        <td colspan="2">Colspan 2</td>
      </tr>
      <tr>
        <td>Data 2-1</td>
        <td>Data 2-2</td>
      </tr>
      <tr>
        <td>Data 3-1</td>
        <td>Data 3-2</td>
        <td>Data 3-3</td>
      </tr>
    </table>
    <button @click="exportToExcel">Export to Excel</button>
  </div>
</template>

<script>
import ExcelJS from 'exceljs';

export default {
  methods: {
    async exportToExcel() {
      const table = this.$refs.table;
      const workbook = new ExcelJS.Workbook();
      const worksheet = workbook.addWorksheet('Sheet1');

      let currentRow = 1;

      for (const row of table.rows) {
        let currentColumn = 1;

        for (const cell of row.cells) {
          const cellValue = cell.innerText;
          const rowspan = cell.rowSpan || 1;
          const colspan = cell.colSpan || 1;

          // 找到第一个空闲的单元格
          while (worksheet.getCell(currentRow, currentColumn).value) {
            currentColumn++;
          }

          const excelCell = worksheet.getCell(currentRow, currentColumn);
          excelCell.value = cellValue;

          // 处理 colspan: 在横向单元格中先填充 null
          for (let i = 1; i < colspan; i++) {
            worksheet.getCell(currentRow, currentColumn + i).value = null;
          }

          // 合并单元格处理
          if (rowspan > 1 || colspan > 1) {
            worksheet.mergeCells(currentRow, currentColumn, currentRow + rowspan - 1, currentColumn + colspan - 1);
          }

          // 移动到下一个列位置
          currentColumn += colspan;
        }

        // 移动到下一行
        currentRow++;
      }

      // 导出 Excel 文件
      const buffer = await workbook.xlsx.writeBuffer();
      const blob = new Blob([buffer], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
      const url = window.URL.createObjectURL(blob);
      const a = document.createElement('a');
      a.href = url;
      a.download = 'table_with_span.xlsx';
      a.click();
      window.URL.revokeObjectURL(url);
    }
  }
}
</script>