如何使用XLSX npm包添加带颜色的自定义分组表头

前置说明 #

xlsx核心库不支持单元格样式和合并操作，需要替换为支持样式的分支版本sheetjs-style，先安装依赖：

npm install sheetjs-style

完整改造代码 #

import XLSX from 'sheetjs-style';
import moment from 'moment';

const rawToHeaders = ({
  id,
  externalIds,
  dateOfBirth = {},
  postalCode,
  locale,
  siteId,
  status = {},
  prescreenerMetrics,
}) => {
  const { day, month, year } = dateOfBirth;
  const dob = [day, month, year].filter(Boolean).join('-');
  const { type, label, comment, timestamp } = status;
  const timeInStatus = moment(timestamp).toNow(true);

  const N_A = 'not available';

  return {
    'Candidate ID': id,
    'External IDs': externalIds
      ?.map(({ source, value }) => `${source}: ${value}`)
      .join('; '),
    'Date of birth': dob,
    'Postal code': postalCode,
    Locale: locale,
    'Site ID': siteId,
    'Current status': type,
    'Current sub-status': label,
    'Current status comment': comment,
    'Time in current status': timeInStatus,
    'Source/recruiter': prescreenerMetrics?.source,
    Referrer: prescreenerMetrics?.referrer,
  };
};

// 定义表头分组及样式配置
const headerGroups = [
  { 
    name: 'HEADER 1', 
    columns: ['Candidate ID', 'External IDs', 'Date of birth'], 
    style: {
      fill: { fgColor: { rgb: 'FF0000' } }, // 红色背景
      font: { name: 'Arial', sz: 12, bold: true, color: { rgb: 'FFFFFF' } }, // 白色粗体字
      alignment: { horizontal: 'center', vertical: 'center' },
      border: { top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }
    }
  },
  { 
    name: 'HEADER 2', 
    columns: ['Postal code', 'Locale', 'Site ID'], 
    style: {
      fill: { fgColor: { rgb: '0000FF' } }, // 蓝色背景
      font: { name: 'Arial', sz: 12, bold: true, color: { rgb: 'FFFFFF' } },
      alignment: { horizontal: 'center', vertical: 'center' },
      border: { top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }
    }
  },
  { 
    name: 'HEADER 3', 
    columns: ['Current status', 'Current sub-status', 'Current status comment'], 
    style: {
      fill: { fgColor: { rgb: '008000' } }, // 绿色背景
      font: { name: 'Arial', sz: 12, bold: true, color: { rgb: 'FFFFFF' } },
      alignment: { horizontal: 'center', vertical: 'center' },
      border: { top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }
    }
  },
  { 
    name: 'HEADER 4', 
    columns: ['Time in current status', 'Source/recruiter', 'Referrer'], 
    style: {
      fill: { fgColor: { rgb: 'FFFFFF' } }, // 白色背景
      font: { name: 'Arial', sz: 12, bold: true, color: { rgb: '000000' } }, // 黑色字保证可读性
      alignment: { horizontal: 'center', vertical: 'center' },
      border: { top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }
    }
  },
];

const generateMasterReport = (data) => {
  const wb = XLSX.utils.book_new();
  const formattedData = data.map(rawToHeaders);
  
  // 按分组配置确定列顺序，避免键顺序混乱
  const allColumns = headerGroups.flatMap(group => group.columns);
  
  // 数据从第3行开始（索引2），跳过默认表头
  const ws = XLSX.utils.json_to_sheet(formattedData, { header: allColumns, skipHeader: true, origin: 2 });
  
  // 添加原始列头行（第2行，索引1）
  allColumns.forEach((colName, colIndex) => {
    const cellAddress = XLSX.utils.encode_cell({ r: 1, c: colIndex });
    ws[cellAddress] = { 
      v: colName, 
      t: 's', 
      s: {
        alignment: { horizontal: 'center' },
        border: { top: { style: 'thin' }, bottom: { style: 'thin' }, left: { style: 'thin' }, right: { style: 'thin' } }
      }
    };
  });
  
  // 添加分组表头行（第1行，索引0）并处理合并单元格
  let currentCol = 0;
  const merges = [];
  headerGroups.forEach(group => {
    const colCount = group.columns.length;
    const startCell = XLSX.utils.encode_cell({ r: 0, c: currentCol });
    const endCell = XLSX.utils.encode_cell({ r: 0, c: currentCol + colCount - 1 });
    
    // 设置分组表头的内容和样式
    ws[startCell] = { v: group.name, t: 's', s: group.style };
    // 记录合并规则
    merges.push({ s: { r: 0, c: currentCol }, e: { r: 0, c: currentCol + colCount - 1 } });
    
    currentCol += colCount;
  });
  
  // 应用合并单元格配置
  ws['!merges'] = merges;
  
  // 调整列宽（可选，优化显示效果）
  ws['!cols'] = allColumns.map(() => ({ wch: 20 }));
  
  XLSX.utils.book_append_sheet(wb, ws, 'Master Report');
  
  return XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });
};

export default generateMasterReport;

关键说明 #

1. 依赖替换：用sheetjs-style替代xlsx，获取单元格样式和合并单元格的支持
2. 分组配置：集中管理分组名称、对应列、背景色及字体样式，后续调整只需修改此对象
3. 行偏移处理：新增分组表头行后，数据行从索引2开始，原始列头行放在索引1位置
4. 合并单元格：通过计算每个分组的列范围，生成!merges配置实现表头合并
5. 样式优化：为分组表头添加居中对齐、边框，白色背景分组改用黑色字体，避免文字不可见

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