Question 309383 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Separate columns in a header row using excel.js module + node.js

I’m trying to add a header row to a CSV file using excel.js, but all the columns in the row are ending up in a single cell.

How can I separate the columns?

enter image description here

Any help will be appreciated

The code I tried is:

static async tratarPlanilha(planilhaPath) {
        const workbook = new excelJs.Workbook();
        await workbook.csv.readFile(planilhaPath);

        const header = ['disciplina  teste', 'RESP_1', 'RESP_2', 'RESP_3', 'RESP_4', 'RESP_5', 'MEDIA', 'COD QUESTAO', 'TITULO', 'MEDIA GERAL', 'NUMERO PREENCHIMENTO']
        const planilhas = [];
        workbook.eachSheet((sheet, sheetId) => {
            sheet.eachRow((row, rowNumber) => {
                const disciplina = row.getCell(1).value;
                const valores = disciplina.split(';');
                console.log(valores[0]);
                if (!planilhas[valores[0]]) {
                    planilhas[valores[0]] = new excelJs.Workbook();
                    planilhas[valores[0]].addWorksheet(sheet.name);
                    planilhas[valores[0]].getWorksheet(sheet.name).addRow(header);
                }
                planilhas[valores[0]].getWorksheet(sheet.name).addRow(row.values);
            })
        });
        for (const disciplina in planilhas) {
            const outputPath = `planilha_${disciplina[0]}.csv`;
            await planilhas[disciplina].csv.writeFile(outputPath);
        }
    }
Tags:

4

Answers


  1. 0

    Looks like you are placing the header as a row. By looking at the docs, it says that to add a header you can include an extra parameter initializing the csv.

    planilhas[valores[0]].addWorksheet(sheet.name, {
  headerFooter:{firstHeader: "Headers content", firstFooter: "Footer content"}
});
    Login or Signup to reply.
  2. 0

    They appear to be in a single cell, because that’s the way Excel on your computer displays CSV on opening it, but if you import it instead, and use , as a delimiter, it should display separated.

    It seems that it separates the data by ; delimiter, so if it’s not important, you could adapt the CSV delimiter to the way Excel initially displays the data, but that’s not recommended (on some other computer, it might show in a single cell, and it would be harder to process it, because it wouldn’t have comma as a usual separator…).

    To do that, use ; as delimiter in options, which you pass to writeFile

    Note: this will make ; as CSV delimiter, not comma (,)

    const options = {
    formatterOptions: {
        delimiter: ';',
        quote: true,
    }
};

//..
await planilhas[disciplina].csv.writeFile(outputPath, options);
//..
    Login or Signup to reply.
  3. 0

    the problem may be in the program to see .csv, I run your code and the output is very good. I am using a vs code extension.

    output using vs code excel viewer

    Login or Signup to reply.
  4. 0

    Demo code, it will support multiple sheet too.

    Save as ‘save-csv.js’

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

class SpreadsheetProcessor {
    static async processSpreadsheet(spreadsheetPath, csvPath) {
        const workbook = new ExcelJS.Workbook();
        await workbook.xlsx.readFile(spreadsheetPath);

        let csvContent = [];

        workbook.eachSheet((sheet, sheetId) => {
            sheet.eachRow((row, rowNumber) => {
                let rowData = [];
                row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
                    rowData.push(cell.text);
                });
                csvContent.push(rowData.join(','));
            });
        });

        fs.writeFileSync(csvPath, csvContent.join('n'));
        console.log(`Data saved to ${csvPath}`);
    }
}

async function main() {
    const fileName = 'data.xlsx';
    const csvFileName = 'data.csv';
    await SpreadsheetProcessor.processSpreadsheet(fileName, csvFileName);
}

main().catch(error => console.error(error));

    Input file
    enter image description here

    Result

    disciplina teste,RESP_1,RESP_2,RESP_3,RESP_4,RESP_5,MEDIA,COD QUESTAO,TITULO,MEDIA GERAL,NUMERO PREENCHIMENTO
x,0,0,5,1,3,64,2,Q2,4,01,8
x,0,0,5,2,3,64,3,Q3,4,01,8
x,0,0,3,1,12,1,Q1,4,01,8
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search