javascriptexceljs

How do I make an array of objects from an excel sheet in js with empty cells?


I am trying to make an array of objects from an excel sheet. The property names would be the first row and then each object would be made from each row beneath as long as there is a value in the first column. I am using exceljs to do this currently and almost everything is working. If there is an empty cell inside one of the rows I want to save the property still in the object just with and empty string. Currently it is not adding a property at all for the empty cell. I do have some logic where I am trying to check if the cell is empty and then add the property with an empty string as the value but it is not working.

When I console.log the row inside the loop I see that there are cells there, but the cells that have no values are not showing up. So I am guessing that when exceljs reads this it is not adding the cells to the row, this is why my logic is not working.

function handleSaveCSV() {
        if (files.accepted.length === 0) {
            console.log('No file selected.');
            return;
        }
       //this is the file I am using
        const file = files.accepted[0]; 
        const reader = new FileReader();

        reader.onload = async (event) => {
            const data = new Uint8Array(event.target.result);
            const workbook = new ExcelJS.Workbook();
            await workbook.xlsx.load(data);

            const worksheet = workbook.getWorksheet(1);
            const headers = worksheet.getRow(1).values;

            const dataArray = [];

            for (let i = 2; i <= worksheet.rowCount; i++) {
                const row = worksheet.getRow(i);
                const rowData = {};

                // Iterate over each cell in the row
                row.eachCell((cell, colNumber) => {
                    if (headers[colNumber]) {
                        rowData[headers[colNumber]] = cell.value !== undefined && cell.value !== null ? cell.value : ''; // this is where I am trying to save an empty string as the property value but it is not working
                    }
                });

                // Check if any value exists in column A
                if (rowData[headers[1]]) {
                    dataArray.push(rowData);
                }
            }

            console.log(dataArray);
        };

        reader.readAsArrayBuffer(file);
    }

Solution

  • Have you tried using { includeEmpty: true }?

    Like:

    row.eachCell({ includeEmpty: true }, function(cell, colNumber){....});
    

    Check the documentation. https://github.com/exceljs/exceljs?tab=readme-ov-file#columns