如何在ExcelJS中将第3或第4行设为表头？代码问题排查

问题描述 #

我希望在ExcelJS中将第3或第4行设置为表头，此前参考过类似问题的解决方案但尝试后并未生效。请帮忙排查以下代码中的问题：

var filename = this.state.id + "no_machinereport.xlsx";
var workbook = new Excel.Workbook();
workbook.creator = "Sourav Singha";
workbook.lastModifiedBy = "Sourav Singha";
workbook.modified = new Date();
workbook.lastPrinted = new Date();
var worksheet = workbook.addWorksheet("Publications", { properties: { tabColor: { argb: '6B5B95' }, defaultRowHeight: 30, defaultColWidth: 25 } });
worksheet.getRow(1).height = 48;
worksheet.getRow(2).height = 21;

// Print Title
worksheet.mergeCells(1, 1, 1, 8);
worksheet.getCell(1, 1).value = "Machine " + this.state.id + " Report";
worksheet.getCell(1, 1).style = { font: { size: 16, underline: true, bold: true }, alignment: { vertical: "middle", horizontal: "center" }, width: 25 };

var header_row = worksheet.getRow(3)
const Header = [
    { header: "Date", key: "date", width: 30, style: { font: { size: 12, underline: false, bold: true }, alignment: { vertical: "middle", horizontal: "center" } } },
    { header: "Hour", key: "hour", width: 30, style: { font: { size: 12, underline: false, bold: true }, alignment: { vertical: "middle", horizontal: "center" } } },
    { header: "Runtime(runtime)", key: "runtime", width: 30, style: { font: { size: 12, underline: false, bold: true }, alignment: { vertical: "middle", horizontal: "center" } } },
    { header: "Stoptime(stoptime)", key: "stoptime", width: 30, style: { font: { size: 12, underline: false, bold: true }, alignment: { vertical: "middle", horizontal: "center" } } },
    { header: "Offline", key: "offline", width: 30, style: { font: { size: 12, underline: false, bold: true }, alignment: { vertical: "middle", horizontal: "center" } } },
    { header: "Stoppaage", key: "stoppage", width: 30, style: { font: { size: 12, underline: false, bold: true }, alignment: { vertical: "middle", horizontal: "center" } } },
];
header_row.header = Header

问题排查与修正 #

核心问题 #

1. 错误的表头赋值方式：ExcelJS的Row对象不存在header属性，直接给header_row.header = Header无法渲染表头内容。
2. 样式与列宽未正确绑定：Header数组中的style和width不会自动应用到单元格和列，需要单独处理。

修正代码（设置第3行为表头） #

var filename = this.state.id + "no_machinereport.xlsx";
var workbook = new Excel.Workbook();
workbook.creator = "Sourav Singha";
workbook.lastModifiedBy = "Sourav Singha";
workbook.modified = new Date();
workbook.lastPrinted = new Date();
var worksheet = workbook.addWorksheet("Publications", { 
  properties: { 
    tabColor: { argb: '6B5B95' }, 
    defaultRowHeight: 30, 
    defaultColWidth: 25 
  } 
});
worksheet.getRow(1).height = 48;
worksheet.getRow(2).height = 21;

// 打印标题
worksheet.mergeCells(1, 1, 1, 8);
worksheet.getCell(1, 1).value = "Machine " + this.state.id + " Report";
worksheet.getCell(1, 1).style = { 
  font: { size: 16, underline: true, bold: true }, 
  alignment: { vertical: "middle", horizontal: "center" } 
};

// 定义表头配置
const headerConfig = [
  { header: "Date", key: "date", width: 30 },
  { header: "Hour", key: "hour", width: 30 },
  { header: "Runtime(runtime)", key: "runtime", width: 30 },
  { header: "Stoptime(stoptime)", key: "stoptime", width: 30 },
  { header: "Offline", key: "offline", width: 30 },
  { header: "Stoppaage", key: "stoppage", width: 30 },
];

// 设置列的宽度与数据关联key
worksheet.columns = headerConfig.map(col => ({
  key: col.key,
  width: col.width
}));

// 获取第3行作为表头行
const headerRow = worksheet.getRow(3);
headerRow.height = 30;

// 逐个设置表头单元格的内容与样式
headerConfig.forEach((col, index) => {
  const cell = headerRow.getCell(index + 1);
  cell.value = col.header;
  cell.style = {
    font: { size: 12, bold: true },
    alignment: { vertical: "middle", horizontal: "center" }
  };
});

// 示例：添加数据（数据会从第4行开始插入）
// const reportData = [
//   { date: "2024-05-20", hour: "09:00", runtime: 180, stoptime: 20, offline: 0, stoppage: 10 },
//   // 更多数据条目...
// ];
// worksheet.addRows(reportData);

设置第4行为表头的调整 #

如果需要将第4行设为表头，只需将代码中的const headerRow = worksheet.getRow(3);改为const headerRow = worksheet.getRow(4);，后续调用addRows插入的数据会自动从第5行开始排列。

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