I passed an array to 'xlsx' to get an Excel file but I got the elements of the array parsed across each row

Viewed 805

I have an array of type T and I wish to convert it to an Excel document sheet in a way that each element of the array is listed in an individual row on the sheet.

let arr = ['one', 'two', 'three']

When I pass this array to a function I grabbed from the internet:

static exportArrayToExcel(arr: any[], name?: string) {
        let { sheetName, fileName } = getFileName(name);

        var wb = XLSX.utils.book_new();
        var ws = XLSX.utils.json_to_sheet(arr);
        XLSX.utils.book_append_sheet(wb, ws, sheetName);
        XLSX.writeFile(wb, `${fileName}.xlsx`);
    }

It does not behave as I expected it. Instead of return a sheet in which each row contains the element, it scatters every element across the row's cell in excel For example: Instead of

row1: |'one'|
row2: |'two'|
row3: |'three'|

It returns:

row1: o | n |  e 
row2: t | w | o

etc...

I do not know why the function is parsing every element of the array and scattering it.

The function exists within a class called TableUtil:

import * as XLSX from "xlsx";

const getFileName = (name: string) => {
    let timeSpan = new Date().toISOString();
    let sheetName = name || "ExportResult";
    let fileName = `${sheetName}-${timeSpan}`;
    return {
        sheetName,
        fileName,
    };
};
export class TableUtil {
    static exportTableToExcel(tableId: string, name?: string) {
        let { sheetName, fileName } = getFileName(name);
        let targetTableElm = document.getElementById(tableId);
        let wb = XLSX.utils.table_to_book(targetTableElm, <
            XLSX.Table2SheetOpts
        >{
            sheet: sheetName,
        });
        XLSX.writeFile(wb, `${fileName}.xlsx`);
    }

    static exportArrayToExcel(arr: any[], name?: string) {
        let { sheetName, fileName } = getFileName(name);

        var wb = XLSX.utils.book_new();
        var ws = XLSX.utils.json_to_sheet(arr);
        XLSX.utils.book_append_sheet(wb, ws, sheetName);
        XLSX.writeFile(wb, `${fileName}.xlsx`);
    }
}
1 Answers

Could it be because your array is not written in json? see this SO question.

What if you would try

var ws = XLSX.utils.json_to_sheet([
  { A: "one", B: "two", C: "three" }
], {header: ["A", "B", "C"], skipHeader: true});;

Please let me know if it works or fails (and eventual error messages).

Take care and good luck.

Related