Skip to content
TabularJS

How to convert Excel to JSON in JavaScript

“Excel to JSON” can mean two different shapes. Sometimes you want the grid as it is, an array of rows. More often you want an array of objects, one per row, keyed by the column headers. This guide shows how to get both from .xlsx and .xls files with TabularJS, how to handle workbooks with several sheets, what happens to dates and formulas, and how to write the result to .json files.

The examples run in Node.js. In the browser the conversion code is the same: only the input changes (a File instead of a path), as shown in Read an Excel file in JavaScript.

Install

Section titled “Install”
Terminal window
npm install tabularjs

The sample workbook

Section titled “The sample workbook”

orders.xlsx has two sheets. Orders:

Order Customer Date Qty Price Total Paid
1001 Acme Corp 2024-03-15 3 19.99 =D2*E2 TRUE
1002 Globex 2024-03-18 8 5.5 =D3*E3 FALSE
1003 Initech 2024-03-20 14:30 12 2.25 =D4*E4 TRUE

And Customers, with Name and Country columns.

JSON as an array of rows

Section titled “JSON as an array of rows”

This is what TabularJS gives you directly. Each worksheet has a data property, a 2D array where data[row][col] is a cell value:

import tabularjs from 'tabularjs';


const result = await tabularjs('./data/orders.xlsx');
const sheet = result.worksheets[0];


console.log(JSON.stringify(sheet.data));
[
  ["Order", "Customer", "Date", "Qty", "Price", "Total", "Paid"],
  [1001, "Acme Corp", 45366, 3, 19.99, "=D2*E2", true],
  [1002, "Globex", 45369, 8, 5.5, "=D3*E3", false],
  [1003, "Initech", 45371.60416666666, 12, 2.25, "=D4*E4", true]
]

Values keep their Excel types: numbers are numbers, TRUE/FALSE are booleans, text is a string. Empty cells are '' and every row has the same length, so the array is a clean rectangle. The header is the first row; nothing is removed.

Arrays of rows are the right shape when the column positions matter more than their names (feeding a grid component, comparing two sheets cell by cell) or when the sheet has no header row at all. They are also compact: the column names are not repeated on every row.

The full result has more than data: column widths, styles, merged cells, comments. See Output structure if you need them.

JSON as an array of objects

Section titled “JSON as an array of objects”

To key each row by its header, take the first row as the keys and map the rest:

import tabularjs from 'tabularjs';


function toObjects(rows) {
  const [header = [], ...body] = rows;
  const keys = header.map((h, i) => String(h).trim() || `column${i + 1}`);


  return body
    .filter((row) => row.some((v) => v !== '' && v != null)) // skip empty rows
    .map((row) => Object.fromEntries(keys.map((key, i) => [key, row[i] ?? null])));
}


const { worksheets } = await tabularjs('./data/orders.xlsx');
console.log(toObjects(worksheets[0].data));
[
  { Order: 1001, Customer: 'Acme Corp', Date: 45366, Qty: 3, Price: 19.99, Total: '=D2*E2', Paid: true },
  { Order: 1002, Customer: 'Globex', Date: 45369, Qty: 8, Price: 5.5, Total: '=D3*E3', Paid: false },
  { Order: 1003, Customer: 'Initech', Date: 45371.60416666666, Qty: 12, Price: 2.25, Total: '=D4*E4', Paid: true }
]

Details that matter with real files:

  • String(h) because headers are not always text. A header like 2024 comes back as the number 2024.
  • .trim() because trailing spaces in headers are common and invisible in Excel.
  • Columns without a header get a generated key (column8) instead of ''.
  • Fully empty rows are dropped. Excel users often leave blank rows between blocks.

The header is not always in the first row. Reports frequently start with a title and a blank line. Find the header row first, then pass the rows from there:

const start = sheet.data.findIndex((row) => row[0] === 'Order');
const orders = toObjects(sheet.data.slice(start));

There is no header option in TabularJS; the mapping is yours, which also means you decide how to name, rename or skip columns.

Convert every sheet

Section titled “Convert every sheet”

A workbook usually has more than one sheet. Build an object keyed by sheet name, reusing toObjects from above:

import tabularjs from 'tabularjs';


const result = await tabularjs('./data/orders.xlsx');


const workbook = Object.fromEntries(
  result.worksheets
    .filter((sheet) => !sheet.worksheetState) // skip hidden sheets
    .map((sheet) => [sheet.worksheetName, toObjects(sheet.data)])
);


console.log(JSON.stringify(workbook, null, 2));
{
  "Orders": [
    { "Order": 1001, "Customer": "Acme Corp", "Date": 45366, "Qty": 3, "Price": 19.99, "Total": "=D2*E2", "Paid": true },
    { "Order": 1002, "Customer": "Globex", "Date": 45369, "Qty": 8, "Price": 5.5, "Total": "=D3*E3", "Paid": false },
    { "Order": 1003, "Customer": "Initech", "Date": 45371.60416666666, "Qty": 12, "Price": 2.25, "Total": "=D4*E4", "Paid": true }
  ],
  "Customers": [
    { "Name": "Acme Corp", "Country": "US" },
    { "Name": "Globex", "Country": "DE" }
  ]
}

The sheet name is worksheetName. Hidden sheets are included in worksheets with worksheetState: 'hidden' or 'veryHidden'; they often hold lookup tables or scratch data, so the filter above leaves them out. Remove the filter if you want them.

To get a single sheet by name: result.worksheets.find((s) => s.worksheetName === 'Customers'). There is no option to parse only one sheet; all of them are read.

Dates

Section titled “Dates”

The Date column above holds 45366, not a date. That is how Excel stores dates: the number of days since 1899-12-30, displayed as a date because of the cell’s number format. JSON has no date type either, so you need to decide on a representation. ISO 8601 strings are the usual choice.

TabularJS gives you the number format of each cell in worksheet.cells, keyed by address ('C2'). You can use it to find date cells and convert them:

import tabularjs from 'tabularjs';


function columnName(index) {
  let name = '';
  for (let n = index + 1; n > 0; n = Math.floor((n - 1) / 26)) {
    name = String.fromCharCode(65 + ((n - 1) % 26)) + name;
  }
  return name;
}


// A number format with day or year parts, ignoring quoted text and [colors]
const isDateFormat = (format) => /[dy]/i.test(format.replace(/"[^"]*"|\[[^\]]*\]/g, ''));


const excelDateToISO = (serial) => new Date(Math.round((serial - 25569) * 86400 * 1000)).toISOString();


function toObjectsWithDates(sheet) {
  const [header, ...body] = sheet.data;
  return body.map((row, r) =>
    Object.fromEntries(header.map((key, c) => {
      let value = row[c] ?? null;
      const format = sheet.cells?.[columnName(c) + (r + 2)]?.format;
      if (typeof value === 'number' && format && isDateFormat(format)) {
        value = excelDateToISO(value);
      }
      return [key, value];
    }))
  );
}


const [orders] = (await tabularjs('./data/orders.xlsx')).worksheets;
console.log(toObjectsWithDates(orders));
[
  { Order: 1001, Customer: 'Acme Corp', Date: '2024-03-15T00:00:00.000Z', Qty: 3, Price: 19.99, Total: '=D2*E2', Paid: true },
  { Order: 1002, Customer: 'Globex', Date: '2024-03-18T00:00:00.000Z', Qty: 8, Price: 5.5, Total: '=D3*E3', Paid: false },
  { Order: 1003, Customer: 'Initech', Date: '2024-03-20T14:30:00.000Z', Qty: 12, Price: 2.25, Total: '=D4*E4', Paid: true }
]

r + 2 turns the body index into the Excel row number: one for the header row and one because Excel rows start at 1. The format check looks for d or y in the format code (yyyy-mm-dd, d-mmm-yy, m/d/yyyy h:mm) and ignores quoted literals and color codes, so currency formats like "$"#,##0.00 are not mistaken for dates. Pure time formats (h:mm) are left as fractions of a day.

If you already know which columns are dates, skip the format check and convert those columns by name; it is simpler and faster.

Two more things about Excel dates: they have no time zone, so treat the result as UTC (use .slice(0, 10) if you only want 2024-03-15). And workbooks from old Mac versions of Excel can use the 1904 date system, where serials are 1462 days smaller.

Formulas

Section titled “Formulas”

Formula cells contain the formula text, with the leading =:

import tabularjs from 'tabularjs';


const [sheet] = (await tabularjs('./data/orders.xls')).worksheets;


const isFormula = (v) => typeof v === 'string' && v.startsWith('=');


console.log(sheet.data[1]);
// [ 1001, 'Acme Corp', 45366, 3, 19.99, '=D2*E2', true ]
console.log(sheet.data[1].filter(isFormula));
// [ '=D2*E2' ]

TabularJS does not calculate formulas and does not return the value Excel last displayed. That makes it safe on untrusted files and keeps cross-sheet references, named ranges and array formulas intact (see the XLS formulas example), but it matters for JSON exports:

  • If your JSON should contain the computed Total, compute it in JavaScript from the source columns (Qty * Price), which also lets you validate the inputs.
  • If the formulas themselves are the point (auditing a model, migrating to another tool), keep them as they are.
  • If the values come from complex models you cannot reproduce, load the result into a spreadsheet engine that evaluates formulas, such as Jspreadsheet, or have the file exported with values only.

Write the JSON to files

Section titled “Write the JSON to files”

In Node.js, write one file per sheet with fs/promises (toObjects is the helper from above):

import { writeFile } from 'node:fs/promises';
import tabularjs from 'tabularjs';


const result = await tabularjs('./data/orders.xlsx');


for (const sheet of result.worksheets) {
  const file = `./${sheet.worksheetName}.json`;
  await writeFile(file, JSON.stringify(toObjects(sheet.data), null, 2));
  console.log('wrote', file);
}
// wrote ./Orders.json
// wrote ./Customers.json

Sheet names can contain characters that are not valid in file names on every system (/, :, ?). Sanitize them, for example with sheet.worksheetName.replace(/[^\w.-]+/g, '_'), if your workbooks come from users. Drop the null, 2 arguments for compact output.

To convert a whole folder of workbooks, see batch-convert a folder. In the browser, offer the JSON as a download:

const blob = new Blob([JSON.stringify(workbook, null, 2)], { type: 'application/json' });
const link = Object.assign(document.createElement('a'), {
  href: URL.createObjectURL(blob),
  download: 'orders.json',
});
link.click();
URL.revokeObjectURL(link.href);

Common pitfalls

Section titled “Common pitfalls”

Merged header cells. A header that spans two columns (Acceptance criteria over Lower and Upper) puts the text in the first cell only; the others are ''. You get a column8 key or a header row that is really two rows. Build the keys by hand for such reports, using worksheet.mergeCells ({ G4: [2, 1] }, colspan and rowspan) if you want to automate it.

Duplicate headers. Two columns named Total collapse into one key with Object.fromEntries. Suffix duplicates (Total, Total_2) when building the keys.

Numbers stored as text. A column of IDs typed as text in Excel stays a string, while the same IDs typed as numbers come back as numbers. Normalize the columns you compare: String(row.Order).

CSV in the same pipeline. If you also accept .csv, the same code works, but every CSV value is a string and there are no cell formats, so dates must be parsed from text. See Parse CSV in JavaScript.

Huge workbooks as one JSON string. JSON.stringify on hundreds of thousands of objects builds one large string in memory. For big exports, write JSON Lines (one object per line) instead, which is also easier to stream into a database.

Section titled “Related”