Excel js: Get header key from the template excel file

Question

I created an Excel file template with just a row with columns on it.

worksheet.columns = [
  { header: 'Id', key: 'id', width: 10 },
  { header: 'Name', key: 'name', width: 32 },
  { header: 'D.O.B.', key: 'dob', width: 10}
];

and later I have to load the same Excel file in different transactions and when I try to retrieve the column key I get unidentified. I want column keys because I want to add rows with key-value

const rows = [
  {id:1, name: 'Barbara', dob: new Date()},
  {id:2, name: 'Barbara 2', dob: new Date()}
];
const newRows = worksheet.addRows(rows);

isn't this possible? I can use another approach row by array but for this, I need to map the data and find the proper column. I guess it's not possible to get column keys while I load the Excel template file.

// Add column headers and define column keys and widths
// Note: these column structures are a workbook-building convenience only,
// apart from the column width, they will not be fully persisted.

Found this in the documentation.

Answer 1

Excel Style and data copy now working well. So this workaround to read header data only from the template and style set programmatically.

This code will work

Saved template.xlsx

demo code.

Save as demo.js

const fs = require('fs');
const ExcelJS = require('exceljs');

async function processTemplateFile(providedRows, file_name, result_file_name) {
    try {
        const fileData = fs.readFileSync(file_name);

        const data = {
            file: {
                data: fileData
            }
        };

        const params = {};

        // get template headers
        const rows = await create(data, params);

        // Add rows data
        providedRows.forEach(rowData => {
            rows.push(rowData);
        });

        // Save the processed data to result.xlsx
        await saveDataToExcel(rows, result_file_name);

        console.log('Data saved to result.xlsx successfully');
    } catch (error) {
        console.error('Error processing template file:', error);
    }
}

async function create(data, params) {
    const { file } = data;
    const workbook = new ExcelJS.Workbook();

    await workbook.xlsx.load(file.data);
    const worksheet = workbook.worksheets[0];

    const rows = [];
    worksheet.eachRow((row, rowNumber) => {
        const rowData = {};
        row.eachCell((cell, colNumber) => {
            rowData[`col${colNumber}`] = cell.value;
        });
        rows.push(rowData);
    });

    return rows;
}

async function saveDataToExcel(data, file_name) {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Sheet1');

    // Add data to worksheet
    data.forEach((row, rowIndex) => {
        Object.keys(row).forEach((key, colIndex) => {
            worksheet.getCell(rowIndex + 1, colIndex + 1).value = row[key];
        });
    });
    
    // Header style settings
    const headerRow = worksheet.getRow(1);
    headerRow.font = { bold: true };
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFC6EFCE' }
    };

    // Save workbook to file
    await workbook.xlsx.writeFile(file_name);
}

const providedRows = [
    { id: 1, name: 'Barbara', dob: new Date() },
    { id: 2, name: 'Barbara 2', dob: new Date() }
];

processTemplateFile(providedRows, 'template.xlsx', 'result.xlsx')
    .then(() => {
        console.log('Processing completed');
    })
    .catch(error => {
        console.error('Error:', error);
    });

Install Dependency

npm install exceljs

Run it

node demo.js

Result

Open `result.xlsx' file.