Sheetjs: Export JSON to CSV

[ankita199]

Hi,

I am trying to export JSON to CSV file.
const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
const csvOutput: string = XLSX.utils.sheet_to_csv(worksheet);
csvOutput successfully generate comma seperated values but XLSX.write function needs only workbook as a object. Is any other way to generate it?

[reviewher]

As the names imply, json_to_sheet generates a worksheet object and sheet_to_csv reads a sheet object. read and write deal with workbook objects. You can generate a workbook manually:

/* Raw object */
const wb1: XLSX.WorkBook = {
    Sheets: { Sheet1: worksheet },
    SheetNames: ["Sheet1"] }
};

/* New Workbook using the functions */
const wb2: XLSX.WorkBook = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb2, worksheet, "Sheet1");

https://jsfiddle.net/uxnf00z8/ is a complete jsfiddle to generate an XLSX workbook from JS object array

[ankita199]

@reviewher but i want to export to csv format from json

[reviewher]

The example code you gave in your first comment generates CSV:

const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(json);
const csvOutput: string = XLSX.utils.sheet_to_csv(worksheet);

If you want to force a download, there are multiple techniques. Here's an example using the FileSaver.js library: https://jsfiddle.net/7t0n3vqk/

/* original data */
var data = [
    {"name":"John", "city": "Seattle"},
    {"name":"Mike", "city": "Los Angeles"},
    {"name":"Zach", "city": "New York"}
];

/* make the worksheet */
var ws = XLSX.utils.json_to_sheet(data);

/* write workbook (use type 'binary') */
var csv = XLSX.utils.sheet_to_csv(ws);

/* generate a download */
function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i=0; i!=s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
    return buf;
}

saveAs(new Blob([s2ab(csv)],{type:"application/octet-stream"}), "sheetjs.csv");

[subrat7]

I have a array of data in json format like

var data = [
{status: "Maintenance", currentOwner: "Carl Brown", gps: {…}, fleetId: "b618e80322ab4417b679a2f593661c6d", acquiredDate: "02/24/2017", …}

{status: "Maintenance", currentOwner: "Carl Brown", gps: {…}, fleetId: "b618e80322ab4417b679a2f593661c6d", acquiredDate: "02/24/2017", …}

{status: "Maintenance", currentOwner: "Carl Brown", gps: {…}, fleetId: "b618e80322ab4417b679a2f593661c6d", acquiredDate: "02/24/2017", …}
];

I want to change the width of the header width when it will download in csv file.

Please reply

[ggregoire]

For anybody arriving here from Google:

const generateCSV = () => {
  const wb = XLSX.utils.book_new()
  const ws = XLSX.utils.json_to_sheet([{ a: 1, b: 2 }])
  XLSX.utils.book_append_sheet(wb, ws, 'test')
  XLSX.writeFile(wb, 'test.csv')
}

[michaelscheurer]

For anybody arriving here from Google:

const generateCSV = () => {
  const wb = XLSX.utils.book_new()
  const ws = XLSX.utils.json_to_sheet([{ a: 1, b: 2 }])
  XLSX.utils.book_append_sheet(wb, ws, 'test')
  XLSX.writeFile(wb, 'test.csv')
}

@ggregoire Do you know how to set the csv delimiter to a semicolon? I wasn't able to find a way to write the csv with semicolons instead of comma.

Thanks for your help

[schalivendri]

@michaelscheurer Was you able to set semicolon instead of a comma? I have a similar usecase. Please let me know. Thanks!

[justLbee]

@michaelscheurer Was you able to set semicolon instead of a comma? I have a similar usecase. Please let me know. Thanks!

Hello!
if you or anyone stiil trying to find the solution for csv files with ';' delimiters, here is my solution:

import XLSX     from 'xlsx';
import {saveAs} from 'file-saver';

const arr = [{id: 1, name: 'test1'}, {id: 2, name: 'test2'}];
const ws = XLSX.utils.json_to_sheet(arr);

const csv = XLSX.utils.sheet_to_csv(ws, {FS: ';'});

const blob = new Blob([csv], {type: 'text/plain;charset=UTF-8'});
saveAs(blob, `test.csv`);

If you are using сyrillic letters, you should add blob to your csv and use updated csv:

...
const new_csv = '\uFEFF' + csv;
const blob = new Blob([new_csv], {type: 'text/plain;charset=UTF-8'});

...