read-excel-file

read-excel-file

Read .xlsx files in a browser or Node.js.

Demo

Also check out write-excel-file for writing .xlsx files.

Install

npm install read-excel-file

Alternatively, it could be included on a web page directly via a <script/> tag.

How to Use

Suppose there's an .xlsx file:

That file could be represented as an array of rows, each row being an array of cells — they call it a "2D array":

[
  ['Name', 'Date of Birth', 'Married', 'Kids'],
  ['John Smith', 1995-01-01T00:00:00.000Z, true, 3],
  ['Kate Brown', 2010-03-01T00:00:00.000Z, false, 0]
]

Or it could be represented as an array of JSON objects:

[
  {
    name: 'John Smith',
    dateOfBirth: 1995-01-01T00:00:00.000Z,
    married: true,
    kids: 3
  },
  {
    name: 'Kate Brown',
    dateOfBirth: 2010-03-01T00:00:00.000Z,
    married: false,
    kids: 0
  }
]

So we could read it both ways.

First, we'll look at the first approach, then the second.

Read as 2D Array

Let's say you prefer reading an .xlsx file as a 2D array.

Here's how to do it using readSheet() function:

import { readSheet } from 'read-excel-file/node'

await readSheet(file) ===
[
  ['Name', 'Date of Birth', 'Married', 'Kids'],
  ['John Smith', 1995-01-01T00:00:00.000Z, true, 3],
  ['Kate Brown', 2010-03-01T00:00:00.000Z, false, 0]
]

* We import the function from read-excel-file/node because we plan to run it in Node.js. See all supported environments.

The result is an array of rows. Each row is an array of values — string, number, boolean or Date.

If an .xlsx file has multiple sheets, then you could pass a second argument — sheet — which could be a sheet number (starting from 1) or a sheet name. By default, it always reads the first sheet.

If an .xlsx file has multiple sheets and you need to read all of them, use the default exported function instead of readSheet().

import readExcelFile from 'read-excel-file/node'

await readExcelFile(file) ===
[{
  sheet: 'Sheet1',
  data: [
    ['Name', ...],
    ['John Smith', ...],
    ['Kate Brown', ...]
  ]
}, {
  sheet: 'Sheet2',
  data: ...
}]

Read as Objects

Reading an .xlsx file as a 2D array is perfectly fine for a machine or an AI agent but not really convenient for a human. Humans are more comfortable accessing the data in the form of JSON objects with named properties rather than just "raw" 2D arrays.

So let's say that a sheet that you'd like to read has a simple "table" structure:

• The first row is a "header" row (column titles)
• The other rows are "data" rows (values for those columns)

Like this:

Then you just define a "schema" for the desired JSON object, i.e. you outline the shape of the desired object, telling it to pull each property from a specific column. Like this:

const schema = {
  name: 'Name',
  dateOfBirth: 'Date of Birth',
  married: 'Married',
  kids: 'Kids'
}

And then pass that schema to the readSheet() function, making it return the data as JSON objects:

const { objects } = await readSheet(file, { schema })

objects === [
  {
    name: 'John Smith',
    dateOfBirth: 1995-01-01T00:00:00.000Z,
    married: true,
    kids: 3
  },
  {
    name: 'Kate Brown',
    dateOfBirth: 2010-03-01T00:00:00.000Z,
    married: false,
    kids: 0
  }
]

There're also a few more "advanced" features to schema such as: support for nested objects, required property validation, value type validation, etc. Read more if you're interested.

Import

This package provides a separate import path for each different environment, as described below.

read-excel-file/browser

Reads an .xlsx file in a browser from a File, a Blob or an ArrayBuffer.

Example 1: Read from a selected file.

<input type="file" id="input" />

import { readSheet } from 'read-excel-file/browser'

const input = document.getElementById('input')

input.addEventListener('change', () => {
  const data = await readSheet(event.target.files[0])
})

Example 2: Read from a URL.

import { readSheet } from 'read-excel-file/browser'

const response = await fetch('https://example.com/spreadsheet.xlsx')
const blob = await response.blob()
const data = await readSheet(blob)

read-excel-file/node

Reads an .xlsx file in Node.js from a file path, a Stream, a Buffer or a Blob.

Example 1: Read from a file path.

import { readSheet } from 'read-excel-file/node'

const data = await readSheet('/path/to/file')

Example 2: Read from a Stream

import { readSheet } from 'read-excel-file/node'

const data = await readSheet(fs.createReadStream('/path/to/file'))

read-excel-file/universal

Reads an .xlsx file in any supported environment, such as a browser or Node.js, but comes with a limitation: it can only read from a Blob or an ArrayBuffer, which could be a bit less convenient for general use.

import { readSheet } from 'read-excel-file/universal'

const data = await readSheet(blob)

Strings

By default, text cell values will be trimmed. To prevent that, pass trim: false option.

readExcelFile(file, { trim: false })

Dates

.xlsx file format has no type for dates. It simply stores dates as regular numbers, and then assigns a date-specific formatting template to those numbers. So telling a number from a date is not always a trivial task, but so far it seems to work correctly.

Numbers

When reading an .xlsx file, any numeric values are parsed from a string to a javascript number. And that works for everyone, except when you work in science or finance or banking where numbers absolutely need to be 100% precise, in which case this section is for you, otherwise don't even bother reading it.

import Decimal from 'decimal.js'

readExcelFile(file, {
  parseNumber: (string) => new Decimal(string)
})

import Decimal from 'decimal.js'

readExcelFile(file, {
  parseNumber: (string) => string
})

Formulas

When reading cells that use formulas to calculate their value, it doesn't really evaluate the formula. Instead, it "cheats" by returning the value that is already pre-computed by the spreadsheet editor application. And because this pre-computed value always exists, it just works.

Although I could hypothetically imagine a situation when a file is created not by a spreadsheet editor application, but rather by some hand-made script that doesn't respect this convention and doesn't bother pre-computing formulas, which is totally allowed by the specification, in which case it will simply read such cells as empty ones.

Finally, if a formula can't be evaluated due to an error, such as invalid syntax, or division by zero, or trying to add text to a number, or referenced row or column not found, etc, then such error will be ignored and the cell will be read as empty.

Errors

InvalidInputError

Sometimes people confuse .xlsx files with legacy binary .xls ones. The difference might be tricky to spot, so this package explicitly throws an InvalidInputError in such (and some other) cases.

• name — "InvalidInputError"
• code — One of:
  • "INPUT_TYPE_NOT_SUPPORTED" — The input argument is not of a supported type.
  • "XLS_FILE_NOT_SUPPORTED" — The input is a legacy binary .xls file (OLE2 Compound File Binary format), which is not supported. Such files should be re-saved in .xlsx format in order to be readable by this package.
  • "FILE_NOT_SUPPORTED" — The input is neither .xlsx nor .xls file.
  • "INVALID_ZIP" — The input seems to be an .xlsx file, and an .xlsx file must be a valid ZIP archive, which it isn't.
  • "NO_DATA" — The input is empty.

InvalidSpreadsheetError

Will be thrown if there's something wrong with the .xlsx file contents while attempting to parse it.

• name — "InvalidSpreadsheetError"

SheetNotFoundError

Will be thrown if a requested sheet doesn't exist.

• name — "SheetNotFoundError"
• sheet — Sheet name or sheet number
• sheets — Available sheet names

Schema

Mapping

In its most basic form, a schema just maps column titles to property names.

For example, to read a file dogs.xlsx:

One could use the following schema:

const schema = {
  name: 'Name',
  breed: 'Breed',
  gender: 'Gender',
  // the owner's info will be put in a nested object
  owner: {
    name: 'Owner Name',
    phone: 'Owner Phone',
    address: 'Owner Address'
  }
}

Just pass it to readSheet() function:

const { objects } = await readSheet('dogs.xlsx', { schema })

And it will return the following objects:

objects === [

  // objects[0]:
  {
    name: 'Rex',
    breed: 'German Shepherd',
    gender: 'M',

    owner: {
      name: 'John Smith',
      phone: '+12133734253',
      address: 'East 26th Street'
    }
  },

  // objects[1]:
  {
    name: 'Luna',
    breed: 'Siberian Husky',
    gender: 'F',

    owner: {
      name: 'Kate Brown',
      phone: '+19177568000',
      address: 'West 42nd Street'
    }
  }

]

For a given column, the mapping algorithm is:

• If a cell is not empty, cell value becomes the propery value.

• If a cell is empty, the property is set to null.

  • This can be overridden by passing propertyValueWhenCellIsEmpty option, which is null by default.

• If the column is missing, the property is set to undefined.

  • This can be overridden by passing propertyValueWhenColumnIsMissing option, which is undefined by default.

For any object:

• If all properties of such object are undefined or null then the object itself will be set to null.
  • This can be overridden by passing transformEmptyObject(object, { path? }) function as an option, which by default always returns null.
  • This rule applies both to the top-level object, and any of its nested objects.

Validation

Aside from just mapping, there're also "advanced" features of schema such as data validation.

In that case, readSheet() returns an object of shape { objects, errors }:

• If there were any errors, objects will be undefined and errors will be a list of errors.
• If there were no errors, errors will be undefined and objects will be a list of objects.

For example, suppose that we'd like to validate that all cell values have the correct type, and all required cells are filled in. All these validation rules will be specified in the schema:

const schema = {
  name: {
    column: 'Name',
    type: String, // must be a string
    required: true // is required
  },
  gender: {
    column: 'Gender',
    oneOf: ['M', 'F'] // must be "M" or "F"
  },
  dateOfBirth: {
    column: 'Date of Birth',
    type: Date // must be a date
  }
}

In other words, where it has previously been just a column title, it's now an object with the following properties:

• column — Column title.
• type — (optional) The expected type of the value.
  • Basic types:
    • String
    • Number
    • Boolean
    • Date
  • "Utility" types that're exported from this package:
    • Integer
    • Email
    • URL
  • Custom type:
    • A function that takes a cell value and returns any kind of a parsed value.
    • If it returns null or undefined, no property value will be set.
    • It should throw an error if it finds the value invalid.
• oneOf — (optional) A list of possible values.
• validate(value) — (optional) A function that will be called on the parsed value, when the value is present. It should throw if it finds the value invalid.
• required — (optional) Is the value required? Could be one of:
  • true — The column must exist and the cell value must not be empty.
  • false — The column can be missing and the cell value can be empty.
  • (object) => boolean — Could be required or not, depending on the other properties of the object. Must be resolved at runtime.

If it finds any errors when validating the data, the errors property returned from the function will be a non-empty array, with each error having properties:

• error (string) — Error code.
  • "required" — when the property is required but the value is missing.
  • "invalid" — when the property type doesn't match the observed cell value type.
  • "invalid" — when the value doesn't pass oneOf validation.
  • If the column uses a custom validate function, then the error will be same as the message of the error that was thrown by the validate function.
  • If the column uses a custom type, then the error will be same as the message of the error that was thrown by the type.
• reason (string) — An optional secondary error code that provides some more details about the error. I.e. "error.error happened specifically because of error.reason". Currently, it could only be returned from non-custom types.
  • Example: { error: "invalid", reason: "not_a_number" } for a type: Number property means that "the cell value is invalid because it's not a number".
• row (number) — Data row number, starting from 1.
  • row: 1 means "first row of data", etc.
  • The header row is ignored.
• column (string) — Column title.
• columnIndex (number) — Column index.
  • columnIndex: 0 means "first column", etc.
• value — Cell value.
• type — The type of the property, as defined in the schema.

import type {
  Schema,
  CellValue,
  ParseSheetDataError,
  ParseSheetDataCustomType,
  ParseSheetDataCustomTypeErrorMessage
} from 'read-excel-file/node'

type ColumnTitle = 'COLUMN TITLE 1' | 'COLUMN TITLE 2'

type CustomTypeValue = string

function CustomType(value: CellValue): CustomTypeValue {
  if (typeof value !== 'string') {
    throw new Error('not_a_string')
  }
  return '~' + value + '~'
}

type CustomTypeErrorMessage<Type extends ParseSheetDataCustomType<unknown>> =
  Type extends typeof CustomType
    ? 'not_a_string'
    : never

// type CustomTypeErrorReason<
//   Type extends ParseSheetDataCustomType<unknown>,
//   ErrorMessage extends ParseSheetDataCustomTypeErrorMessage<Type>
// > =
//   Type extends typeof CustomType
//     ? (ErrorMessage extends 'not_a_string' ? undefined : never)
//     : never

type PossibleError = ParseSheetDataError<
  ColumnTitle,
  typeof CustomType,
  CustomTypeErrorMessage<typeof CustomType>
  // CustomTypeErrorReason<typeof CustomType, CustomTypeErrorMessage<typeof CustomType>>
>

interface Object {
  property1: CustomTypeValue;
  property2?: string;
}

const schema: Schema<Object, ColumnTitle> = {
  property1: {
    column: 'COLUMN TITLE 1',
    type: CustomType,
    required: true
  },
  property2: {
    column: 'COLUMN TITLE 2',
    type: String
  }
}

const { objects, errors } = parseSheetData<Object, ColumnTitle, PossibleError>([
  ['COLUMN TITLE 1', 'COLUMN TITLE 2'],
  ['Value 1', 'Value 2']
], schema)

if (errors) {
  for (const error of errors) {
    console.error('Error in data row', error.row, 'column', error.column, ':', error.error, error.reason || '')
  }
} else {
  console.log('Objects', objects)
}

Validation of Nested Objects

A nested object can be marked as required: false. This will allow it to be absent from the sheet, even if some of its properties are defined as required: true.

For example, a schema could define an optional secondOwner nested object using required: false flag. If there's no second owner info, that's ok. But if there is, then the second owner's name must be specified, which is expressed by adding required: true flag on that property. This will guard against the cases when, for example, the second owner's phone is filled in their name is left blank — in such case it will throw an error.

const schema = {
  name: { column: 'Name', type: String, required: true },
  breed: { column: 'Breed', type: String },

  owner: {
    name: { column: 'Owner Name', type: String, required: true },
    phone: { column: 'Owner Phone', type: String }
  },

  secondOwner: {
    required: false,
    schema: {
      name: { column: 'Second Owner Name', type: String, required: true },
      phone: { column: 'Second Owner Phone', type: String }
    }
  }
}

Note how in order to be able to define a nested object as required: false, its structure has to be moved to a separate property called schema.

While a nested object can be defined as required: false, it can't be defined as required: true because it wouldn't really make sense — just mark the relevant properties of the nested object as required: true and that will make the nested object itself required as well.

Parsing Comma-Separated Values

If some column includes comma-separated values — such as "a, b, c" — then you could specify the type of the column as an array, and it will first split the value into substrings, and then parse each substring using the specified type.

For example, if a property is defined as { type: [String] } and the cell value is "a, b, c" then the property value will become ["a", "b", "c"].

Tips:

• If a cell is empty, or if every element of the parsed array is null or undefined, then the property value itself will be set to null.
  • This can be overridden by passing transformEmptyArray(array, { path }) function as an option. By default, it returns null.
• The separator could be specified by passing arrayValueSeparator option. By default, it's ",".
• The separated parts will be trimmed.

Advanced Validation

This package adheres to the Unix-way philosophy in that it intentionally focuses on a single task — reading an .xlsx file — and doesn't wander too far. That's why it only includes the basic validation features, leaving the rest to specialized packages — such as zod — without leaning toward any particular one.

For example, one could first use read-excel-file with a schema to read the contents of an .xlsx file into an array of JSON objects, and then pass those JSON objects to something like zod for data validation. Decoupling validation logic from the task of reading an .xlsx file not only unties your hands in using advanced validation features but also opens new possibilities, such as reusing the validation logic on the server side for non-.xlsx input.

And in order to map those validation errors back to the spreadsheet's rows and columns, one could use the exported function called getColumnTitleForProperty(path, schema) which transforms a property path into a column title, such as: path: ["owner", "name"] → column: "Owner Name". A column title could then be transformed to column index by looking it up in the columns array that is returned from readSheet() function.

import { readSheet, getColumnTitleForProperty } from 'read-excel-file'
import * as z from 'zod'

// `read-excel-file` mapping schema
const schema = {
  name: 'NAME',
  age: 'AGE'
}

// `zod` schema, which repeats `read-excel-file` mapping schema
const Person = z.object({
  name: z.string(),
  age: z.number().optional().nullable()
})

// Read the contents of an `.xlsx` file into an array of JSON objects
const { objects, errors, columns } = await readSheet('persons.xlsx', { schema })

// If there were any errors while reading JSON objects from the `.xlsx` file
if (errors) {
  // Report just the first error (for simplicity)
  const { error: code, row, columnIndex, column: columnTitle } = errors[0]
  reportError(code, row, columnIndex + 1, columnTitle)
}

// Reports an error
function reportError(code, row, column, columnTitle) {
  throw new Error(`Row ${row}. Column ${column}. Column Title "${columnTitle}". Code: "${code}"`)
}

// Validate the JSON objects using `zod` schema
let row = 2 // skips the header row
for (const object of objects) {
  try {
    Person.parse(object)
  } catch (error) {
    if (error instanceof z.ZodError) {
      // Convert `zod` error to `read-excel-file` error.
      // Report just the first error (for simplicity)
      const { code, path } = error.issues[0]
      const columnTitle = getColumnTitleForProperty(path, schema)
      if (columnTitle) {
        const column = columns[columnTitle] + 1 // column number
        reportError(code, row, column, columnTitle)
      }
    }
    // For any other errors
    throw error
  }
  row++
}

Standalone API

For convenience, this package exports the schema transformation feature as a separate function called parseSheetData(sheetData, schema).

import { readSheet, parseSheetData } from 'read-excel-file/node'

const schema = { ... }
const sheetData = await readSheet(file)
const { objects, errors } = parseSheetData(sheetData, schema)
if (errors) {
  console.error(errors)
} else {
  console.log(objects)
}

Performance

Here're the results of reading sample .xlsx files of different size:

To run the benchmark in Node.js, clone the repository, download any sample .xlsx files to ./test/benchmark folder, run npm install and then npm run test:benchmark:node.

To run the benchmark in a web browser, open the demo page, open the console and choose an .xlsx file.

Browser Support

An .xlsx file is just a .zip archive with an .xslx file extension. This package uses fflate for .zip decompression. See fflate's browser support for further details.

CDN

To include this library directly via a <script/> tag on a page, one can use any npm CDN service, e.g. unpkg.com or jsdelivr.com

<script src="https://unpkg.com/read-excel-file@[version]/bundle/read-excel-file.min.js"></script>

<script>
  var input = document.getElementById('input')
  input.addEventListener('change', function() {
    readXlsxFile(event.target.files[0]).then(function(rows) {
      // `rows` is an array of rows
      // each row being an array of cells.
    })
  })
</script>

Dependencies

• fflate — Unzips .zip archives in web browsers.
• unzipper-esm — Unzips .zip archives in Node.js using stream API.
• saxen — Parses XML in a streaming fashion.

Contributors

• Stian Jensen — Use fflate unzipper on server side (1, 2)
• Etienne Prothon — Reject non .xlsx files, including the legacy binary .xls files (1). Fix parsing of "encoded" characters (1).
• Osama Ansar — Decode _x-encoded strings (1)

GitHub

On March 9th, 2020, GitHub, Inc. silently banned my account (erasing all my repos, issues and comments, even in my employer's private repos) without any notice or explanation. Because of that, all source codes had to be promptly moved to GitLab. The GitHub repo is now only used as a backup (you can star the repo there too), and the primary repo is now the GitLab one. Issues can be reported in any repo.

License

MIT