Angular中如何在SheetJS表头行前添加居中报表标题

问题描述 #

在Angular项目里用SheetJS将JSON数据导出为Excel文件时，无法在表头行上方添加居中的报表标题，希望实现表头前一行显示合并列且居中的标题。

原导出服务代码 #

import { Injectable } from '@angular/core';
import * as FileSaver from 'file-saver';
import * as XLSX from 'xlsx';

@Injectable({
  providedIn: 'root'
})
export class ExportService {
  mimeTypes: any = {
    xlsx: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8',
    xls: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8',
    txt: 'text/plain;charset=utf-16',
    csv: 'text/csv;charset=utf-16',
    xml: 'application/xml',
    zip: 'application/zip',
    jpg: 'image/jpeg',
    jpeg: 'image/jpeg',
    png: 'image/png',
    mp4: 'video/mp4',
    avi: 'video/avi',
    bmp: 'image/bmp',
    doc: 'application/msword',
    docx: 'application/vnd.openxmlformats-officedocument.wordprocessingml.document',
    ppt: 'application/vnd.ms-powerpoint',
    pps: 'application/vnd.ms-powerpoint',
    pptx: 'application/vnd.openxmlformats-officedocument.presentationml.presentation',
    pdf: 'application/pdf',
    rar: 'application/vnd.rar',
    webp: 'image/webp',
    '7z': 'application/x-7z-compressed',
    gif: 'image/gif',
    gz: 'application/gzip',
    json: 'application/json'
  };

  public exportExcel(jsonData: any[], fileName: string): void {
    if (jsonData.length > 0) {
      const header = Object.keys(jsonData[0]); // 获取列名

      const wscols = [];
      for (const head of header) {
        wscols.push({ wch: head.length + 5 });
      }

      const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(jsonData);
      ws['!cols'] = wscols;

      const wb: XLSX.WorkBook = { Sheets: { data: ws }, SheetNames: ['data'] };
      const excelBuffer: any = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
      this.saveFile(excelBuffer, fileName + '.xlsx', 'xlsx');
    }
  }

  private saveFile(buffer: any, fileName: string, type: string) {
    const data: Blob = new Blob([buffer], { type: this.mimeTypes[type] });
    FileSaver.saveAs(data, fileName);
  }
}

尝试过但无效的代码 #

public exportExcel(jsonData: any[], fileName: string,reportName: any, fromDate: any,toDate:any): void {
    if (jsonData.length > 0) {
      const header = Object.keys(jsonData[0]); // columns name               
      const arr = Object.values(jsonData);   
      const wscols = [];
      for (const head of header) {
        wscols.push({ wch: head.length + 5 });
      }

      const ws: XLSX.WorkSheet = XLSX.utils.json_to_sheet(jsonData);
      ws['!cols'] = wscols;

      const merge = [
        { s: { r: 0, c: 0 }, e: { r: 0, c: 4 } },
        { s: { r: 1, c: 0 }, e: { r: 1, c: 4 } },
      ];
      ws["!merges"] = merge;
      // XLSX.utils.sheet_add_aoa(ws, header , {origin: -1});
      const wb: XLSX.WorkBook = { Sheets: { data: ws }, SheetNames: ['data'] };
      XLSX.utils.sheet_add_json(ws, arr, { origin: 'A5', skipHeader: true });
      const excelBuffer: any = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
      this.saveFile(excelBuffer, fileName + '.xlsx', 'xlsx');
    }
  }

有效解决方案 #

问题核心是直接用json_to_sheet生成工作表后再插入标题，会导致原有表头和数据位置冲突。正确做法是先创建空工作表，依次添加标题、可选信息，最后插入JSON数据：

public exportExcel(jsonData: any[], fileName: string, reportName: string, fromDate?: string, toDate?: string): void {
  if (jsonData.length === 0) return;

  const header = Object.keys(jsonData[0]);
  const columnCount = header.length;

  // 1. 创建空工作表
  const ws: XLSX.WorkSheet = XLSX.utils.book_new();

  // 2. 添加报表标题行（合并所有列）
  const titleRow = [[reportName]];
  XLSX.utils.sheet_add_aoa(ws, titleRow, { origin: 'A1' });

  // 3. 可选：添加日期范围行（按需使用）
  let dataStartRow = 2;
  if (fromDate && toDate) {
    const dateRow = [[`日期范围：${fromDate} 至 ${toDate}`]];
    XLSX.utils.sheet_add_aoa(ws, dateRow, { origin: 'A2' });
    dataStartRow = 3;
  }

  // 4. 插入JSON数据及表头
  XLSX.utils.sheet_add_json(ws, jsonData, { 
    origin: `A${dataStartRow}`,
    header: header,
    skipHeader: false
  });

  // 5. 设置合并单元格规则
  const merges = [
    { 
      s: { r: 0, c: 0 }, // 起始位置：第1行第1列（索引从0开始）
      e: { r: 0, c: columnCount - 1 } // 结束位置：第1行最后一列
    }
  ];
  // 若有日期行，同步合并日期行
  if (fromDate && toDate) {
    merges.push({
      s: { r: 1, c: 0 },
      e: { r: 1, c: columnCount - 1 }
    });
  }
  ws['!merges'] = merges;

  // 6. 设置列宽
  const wscols = header.map(head => ({ wch: head.length + 5 }));
  ws['!cols'] = wscols;

  // 7. 生成工作簿并导出
  const wb: XLSX.WorkBook = { Sheets: { data: ws }, SheetNames: ['data'] };
  const excelBuffer: any = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
  this.saveFile(excelBuffer, `${fileName}.xlsx`, 'xlsx');
}

关键说明 #

• 先创建空工作表，避免直接用json_to_sheet导致的位置覆盖问题
• 用sheet_add_aoa插入标题和日期行，通过origin精准控制插入位置
• 合并单元格的列索引从0开始，需计算最后一列的索引为columnCount - 1
• 插入JSON数据时指定header参数，确保表头与数据字段完全对应

内容的提问来源于stack exchange，提问作者Raveen