angularexceltypescriptsheetjs

Change column order in SheetsJS


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


Solution

  • 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 });