ExcelJS freeze rows

Question

I'm trying to freeze rows in ExcelJS but cannot get it to work.

I'm trying both

worksheet.views = [
{state: 'frozen', ySplit: 1}
];

and

workbook.addWorksheet(sheetName, {
views: [
{state: 'frozen', ySplit: 1}
]
});

but it doesn't work.

I also get the "We found a problem with...Do you want to recover as much.." warning when opening up the spreadsheet.

Anyone getting this to work? All I want to do is freeze the 7th row. I can do this in Excel itself

There's an Issue reported here that seems to say it's just not a feature in Excel itself. But I'm wondering why it corrupts the file too.

Answer 1

Try this:

workbook.addWorksheet(sheetName, {
    views: [{ state: "frozen", ySplit: 1 }],
});

It's working for me.

More details:

const Excel = require("exceljs");

const writeFile = async (sheetName, columns, list, file) => {
  let workbook = new Excel.Workbook();
  let sheet = workbook.addWorksheet(sheetName, {
    views: [{ state: "frozen", ySplit: 1 }],
  });
  sheet.columns = columns;

  for (const record of list) {
    sheet.addRow(record);
  }
  workbook.xlsx.writeFile(file);
};


const test = async () => {
  const columns = [
    { header: "Column1", key: "column1" },
    { header: "Column2", key: "column2" },
    { header: "Column3", key: "column3" },
  ];
  let list = [];
  for (let i = 1; i < 100; i++) {
    let record = {};
    record.column1 = `line${i}_column1`;
    record.column2 = `line${i}_column2`;
    record.column3 = `line${i}_column3`;
    list.push(record);
  }
  const file = "c:/temp/test.xlsx";
  await writeFile("sheet1", columns, list, file);
};
test();