I generate an Excel file using xlsx
(based on SheetJS
). In my excel file, I need an specific order in the generated columns, as well as custom headers. I generate my excel with custom headers like this:
const finalHeaders = [
[
this.tr.instant('shared.targetLanguage') as string,
'Nº Items',
this.tr.instant('reports.summarized.withoutimages') as string,
this.tr.instant('reports.summarized.creativewrd') as string,
this.tr.instant('reports.summarized.noncreativewrd') as string,
this.tr.instant('shared.client') as string,
this.tr.instant('shared.project') as string,
this.tr.instant('shared.sourceLanguage') as string,
],
];
/** Generate the excel file */
const workBook = XLSX.utils.book_new();
XLSX.utils.sheet_add_aoa(workBook, finalHeaders);
const workSheet = XLSX.utils.sheet_add_json(workBook, data, { origin: 'A2', skipHeader: true });
XLSX.utils.book_append_sheet(workBook, workSheet, 'Sheet 1'); // add the worksheet to the book
XLSX.writeFile(workBook, `Quantitative_Summarized_Report_${new Date().toLocaleString()}.xlsx`);
But after this, I need to sort the columns in an specific order. I can do it, but I loose the custom headers:
const sortedHeader = ['clientName', 'projectName', 'sourceLanguageName', 'targetLanguageName', 'numItems', 'numItemsWithoutImages', 'numWordsCreative', 'numWordsNonCreative'];
const workSheet = XLSX.utils.json_to_sheet(data, { header: finalHeaders });
Is there a way to combine these two solutions? Thanks in advance
Finally, I get it done. With the combination of the two ways, I can do it:
const sortedHeader = ['clientName', 'projectName', 'sourceLanguageName', 'targetLanguageName', 'numItemsTarget', 'numItemsWithoutImages', 'numWordsCreative', 'numWordsNonCreative'];
const finalHeaders = [
[
this.tr.instant('shared.client') as string,
this.tr.instant('shared.project') as string,
this.tr.instant('shared.sourceLanguage') as string,
this.tr.instant('shared.targetLanguage') as string,
'Nº Items',
this.tr.instant('reports.summarized.withoutimages') as string,
this.tr.instant('reports.summarized.creativewrd') as string,
this.tr.instant('reports.summarized.noncreativewrd') as string,
],
];
const workBook = XLSX.utils.book_new();
XLSX.utils.sheet_add_aoa(workBook, finalHeaders);
const workSheet = XLSX.utils.sheet_add_json(workBook, data, { origin: 'A2', header: sortedHeader, skipHeader: true });