Converting to CSV, JSON and HTML

These helpers turn a worksheet into other forms. They return values. They do not write files.

Helper	Result
`utils.sheet_to_json(ws, opts)`	an array of objects, or an array of arrays
`utils.sheet_to_csv(ws, opts)`	CSV text
`utils.sheet_to_txt(ws, opts)`	tab-separated text (UTF-16 with a byte order mark)
`utils.sheet_to_html(ws, opts)`	an HTML document with one table
`utils.sheet_to_formulae(ws)`	one line per cell: `A1=value` or `A1=formula`

All examples on this page use this workbook:

const XLSX = require("@agent-sheet/wasm");
const wb = XLSX.read("name,qty\nbolt,10\nnut,20", { type: "string" });
const ws = wb.Sheets.Sheet1;
console.log(XLSX.utils.sheet_to_csv(ws));
console.log(XLSX.utils.sheet_to_formulae(ws));

name,qty
bolt,10
nut,20
[ "A1='name", "B1='qty", "A2='bolt", 'B2=10', "A3='nut", 'B3=20' ]

JSON

By default each row after the first becomes an object. The first row gives the keys.

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.read("name,qty\nbolt,10\nnut,20", { type: "string" }).Sheets.Sheet1;

console.log(XLSX.utils.sheet_to_json(ws));
console.log(XLSX.utils.sheet_to_json(ws, { header: 1 }));    // arrays, header row included
console.log(XLSX.utils.sheet_to_json(ws, { header: "A" }));  // column letters as keys
console.log(XLSX.utils.sheet_to_json(ws, { range: 1, header: 1 })); // skip the first row

[ { name: 'bolt', qty: 10 }, { name: 'nut', qty: 20 } ]
[ [ 'name', 'qty' ], [ 'bolt', 10 ], [ 'nut', 20 ] ]
[ { A: 'name', B: 'qty' }, { A: 'bolt', B: 10 }, { A: 'nut', B: 20 } ]
[ [ 'bolt', 10 ], [ 'nut', 20 ] ]

Option	Effect
`header`	`1` for arrays, `"A"` for column letters, or an array of key names
`range`	a range, or a start row number
`defval`	value for empty cells (they are skipped otherwise)
`raw`	`false` returns formatted text instead of raw values
`blankrows`	keep blank rows (default `false` for objects, `true` for `header: 1`)
`dateNF`	number format for dates
`skipHidden`	leave out hidden rows and columns

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.utils.json_to_sheet([{ a: 1, b: 2 }, { a: 3, c: 4 }]);
console.log(XLSX.utils.sheet_to_json(ws));
console.log(XLSX.utils.sheet_to_json(ws, { defval: null }));

[ { a: 1, b: 2 }, { a: 3, c: 4 } ]
[ { a: 1, b: 2, c: null }, { a: 3, b: null, c: 4 } ]

CSV and text

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.read("name,qty\nbolt,10\nnut,20", { type: "string" }).Sheets.Sheet1;
console.log(XLSX.utils.sheet_to_csv(ws, { FS: ";", RS: "|" }));
console.log(JSON.stringify(XLSX.utils.sheet_to_csv(ws, { FS: "\t" })));
console.log(XLSX.utils.sheet_to_csv(ws, { forceQuotes: true }));

name;qty|bolt;10|nut;20
"name\tqty\nbolt\t10\nnut\t20"
"name","qty"
"bolt","10"
"nut","20"

Option	Effect
`FS`, `RS`	field and record separators (default `,` and newline)
`strip`	remove trailing separators
`blankrows`	keep blank rows (default `true`)
`skipHidden`	leave out hidden rows and columns
`forceQuotes`	quote every field
`rawNumbers`	write raw numbers instead of formatted text
`dateNF`	number format for dates

The write function with bookType: "csv" also writes CSV. Byte output (type: "buffer" or "array") and writeFile add a UTF-8 byte order mark; type: "string" returns text without it. See Writing files.

HTML

const XLSX = require("@agent-sheet/wasm");
const ws = XLSX.read("name,qty\nbolt,10", { type: "string" }).Sheets.Sheet1;
const html = XLSX.utils.sheet_to_html(ws, { id: "parts" });
console.log(html.includes('<table'), html.includes('id="parts"'), html.includes(">bolt<"));

true true true

Options: id (table id), editable (add contenteditable), header and footer (HTML before and after the table) and gridcolor. Cell styles are exported as inline CSS when the cells carry styles.

Convert a whole file

A common task is to convert one file format to another:

const XLSX = require("@agent-sheet/wasm");
XLSX.writeFile(XLSX.read("a,b\n1,2", { type: "string" }), "input.xlsx");

const wb = XLSX.readFile("input.xlsx");
XLSX.writeFile(wb, "output.ods");
XLSX.writeFile(wb, "output.csv");
console.log(XLSX.readFile("output.ods").Sheets.Sheet1.B2.v);