How to optimize cell protection settings for performance when exporting Excel using excelJs?

Export Excel using exceljs library: Performance optimization strategy

When using the exceljs library to export Excel files containing cell protection settings, if you use the method of setting protection properties one by one, especially when processing large amounts of data, the efficiency will be very low, resulting in slow export speed or even stuttering. This article discusses two optimization strategies to significantly improve the export performance.

Problem: The developer hopes to export Excel tables, some columns can be edited, and the rest cannot be edited. The initial code unlocks cells of the specified column one by one, which is inefficient.

Solution: Avoid cell-by-cell operation and adopt batch processing.

Strategy One: Batch Operation Column

The initial method traverses each column and then traverses each row, setting cell protection properties one by one. The optimization strategy is to directly operate the column, use sheet.columns.forEach method to traverse the column, determine whether it needs to be unlocked, and then batch operate all cells of the column. This method greatly reduces the number of cycles and improves efficiency.

Improved code example:

 const ExcelJS = require('exceljs');

async function exportExcel(data) {
  const workbook = new ExcelJS.Workbook();
  const sheet = workbook.addWorksheet('Sheet1');

  data.forEach(row => sheet.addRow(row));

  sheet.protect('yourpassword', { selectLockedCells: true, selectUnlockedCells: true });

  const unlockColumns = [6, 7, 8, 9, 12];
  sheet.columns.forEach((col, colNumber) => {
    if (unlockColumns.includes(colNumber 1)) {
      col.eachCell((cell, rowNumber) => {
        if (rowNumber !== 1) { // Skip the title line cell.protection = { locked: false };
        }
      });
    }
  });

  await workbook.xlsx.writeFile('output.xlsx');
}

// Sample data (omitted...)

Strategy 2: Use Excel templates

Pre-create Excel template files containing the required protection settings, and only write data when exporting. This method avoids setting cell protection at runtime, and is suitable for cases where data structures are fixed and Excel is frequently exported in the same format.

Summary: The core of both strategies is to reduce the number of cycles and improve efficiency. Batch operation columns directly process column data, while using templates completely avoids runtime protection settings. Which strategy to choose depends on the specific application scenario. By comparing the code before and after the improvement, you can clearly see the performance improvement.

The above is the detailed content of How to optimize cell protection settings for performance when exporting Excel using excelJs?. For more information, please follow other related articles on the PHP Chinese website!