read-excel-file

read-excel-file

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

It also supports parsing spreadsheet rows into JSON objects using a schema.

Demo

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

Install

npm install read-excel-file --save

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

Use

If your .xlsx file only has a single "sheet", or if you only need to read a single "sheet", or if you don't care what a "sheet" is, use readSheet() function.

For example, consider the following .xlsx file:

Here's how to read 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]
]

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

It also has an optional second argument — sheet — which could be a sheet number (starting from 1) or a sheet name. By default, it reads the first sheet.

But if you need to read all available "sheets" in a file, use the default exported function:

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

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

The result is a non-empty array of "sheets". Each "sheet" is an object with properties:

• sheet — Sheet name.
  • Example: "Sheet1"
• data — Sheet data. An array of rows. Each row is an array of values — string, number, boolean or Date.
  • Example: [ ['Name','Age'], ['John Smith',30], ['Kate Brown',15] ]

Also, a very common use case is to read a list of JSON objects from an .xlsx file. To do that, pass a schema parameter to readSheet() function.

Import

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

Browser

read-excel-file/browser

It can read 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)

Node.js

read-excel-file/node

It can read 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'))

Universal

read-excel-file/universal

This one works both in a web browser and Node.js. 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)

Note: the /universal export can't use workers so it's inherently "single-threaded" and "blocking".

Worker

When reading extremely large .xlsx files — say, starting from a few megabytes in size — there's a slight inconvenience of freezing the application during the "XML parsing" phase or "schema parsing" phase while reading the file.

To work around this minor issue in a web browser, one could read .xlsx files in a separate Web Worker using read-excel-file/web-worker export.

const worker = new Worker(new URL('worker.js', import.meta.url))

worker.onmessage = (event) => {
  // File has been read.
  console.log('Sheet data', event.data)
}

worker.onerror = (event) => {
  // Handle errors here.
  console.error(event.error)
}

// "Choose file" button.
const input = document.getElementById('input')

// When user chooses a file, send it to the Web Worker.
input.addEventListener('change', async () => {
  const file = await event.target.files[0].arrayBuffer()
  // Send the `.xlsx` file to the worker.
  // (advanced) One could also pass `transferList` argument here.
  worker.postMessage(file)
})

import { readSheet } from 'read-excel-file/web-worker'

onmessage = async (event) => {
  postMessage(await readSheet(event.data))
}

Strings

By default, it automatically trims all string values. To disable this behavior, pass trim: false option.

readExcelFile(file, { trim: false })

Dates

Because .xlsx file format has no type for dates, it stores them as regular numbers but with a date-specific formatting template. By looking at the template, one could guess if it's a number or a date. This package seems to guess 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 calculate the formula. Instead, it "cheats" by returning the value that is already pre-computed by the spreadsheet editor application. And that works for everyone.

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 bother pre-computing formulas, which is totally allowed by the specification, in which case such cells will simply be interpreted as empty ones.

Also, sometimes formulas can't be precomputed by a spreadsheet editor application 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. Such errors will be silently ignored and the cells will be interpreted as empty ones.

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

Performance

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

To run the benchmark in Node.js, clone the repository, download 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.

Schema

Oftentimes, the task is not just to read the "raw" spreadsheet data but also to convert each row of that data to a JSON object having a certain structure. Because it's such a common task, this package provides an easy way to do that — just pass a schema parameter when calling readSheet() function and it will automatically parse sheet data into an array of JSON objects according to that schema (which basically describes all properties of the object and which column should be mapped to which property).

The only requirement is that the sheet data should adhere to a simple structure: the first row should be a header row with just column titles, and each following row should specify the values for those columns.

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

const schema = {
  name: {
    column: 'Name',
    type: String
  },
  dateOfBirth: {
    column: 'Date of Birth',
    type: Date
  },
  married: {
    column: 'Married',
    type: Boolean
  },
  kids: {
    column: 'Kids',
    type: Number
  }
}

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

if (errors) {
  console.error(errors)
} else {
  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
    }
  ]
}

The result is { 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.

schema should describe the structure of the resulting JSON objects. A slightly more complex example of a schema is provided at the end of this section.

Specifically, a schema should be an object having the same keys as a resulting JSON object, with values being nested objects having the following properties:

• column — The title of the column to read the value from.
  • If the column does not exist, the property value will be undefined.
    • This can be overridden by passing propertyValueWhenColumnIsMissing option. Is undefined by default.
  • If the column exists but is empty, the property value will be null.
    • This can be overridden by passing propertyValueWhenCellIsEmpty option. Is null by default.
• 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 — A function returning true or false depending on the other properties.
• validate(value) — (optional) Validates the value. Is only called for non-empty cells. If the value is invalid, this function should throw an error.
• schema — (optional) If the value is going to be a nested object, schema should describe that nested object.
  • If when parsing such nested object, all of its properties are parsed as undefined or null then the nested object itself will be set to null.
    • This can be overridden by passing transformEmptyObject(object, { path? }) function as an option. By default, it returns null.
    • This applies both to nested objects and to the top-level object itself.
  • A nested object could be marked as required: false — this will allow it to be completely absent from the spreadsheet, even if some of its properties are defined with required: true flag. But if at least one property of such object is found in the spreadsheet then the required: false flag on the object has no longer any effect and any required: true properties of the object are now required to exist.
    • Any other value except false is not allowed.
• type — (optional) If the value is not going to be a nested object, the expected type of the value could be specified in the type property, and then it will parse/validate the value according to that type.
  • Valid types:
    • Standard types:
      • String
      • Number
      • Boolean
      • Date
    • One of the "utility" types that're exported from this package:
      • Integer
      • Email
      • URL
    • Custom type:
      • A function that receives a cell value and returns any kind of a parsed value. Returning undefined will have same effect as returning null. If the value is invalid, it should throw an error.
  • If the cell value is comprised of comma-separated values (example: "a, b, c") and if it should be parsed as an array of such values, then the property type could be specified as an array — type: [elementType] — where elementType could be any valid type described above. For example, if a property is defined as { type: [String] } and the cell value is "a, b, c" then the property value will be parsed as ["a", "b", "c"].
    • If the 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 of a cell value will be trimmed.

If there're any errors during the conversion process, the errors property returned from the function will be a non-empty array (by default, it's an empty array). Each error object has properties:

• error (string) — Error code. Examples: "required", "invalid".
  • If a custom validate() function is defined and it throws a new Error(message) then the error property will be the same as the message argument.
  • If a custom type() function is defined and it throws a new Error(message) then the error property will be the same as the message argument.
• reason?: string — An optional secondary error code providing more details about the error. I.e. "error.error happened specifically because of error.reason". Currently, it could only be returned for the standard 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, when present.
• type — The type of the property, as defined in the schema.

Example:

// An example .xlsx document:
// --------------------------------------------------------------------------------------------------------
// | START DATE | SEATS |   STATUS  |    CONTACT     | COURSE TITLE  | COURSE CATEGORY   | COURSE IS FREE |
// --------------------------------------------------------------------------------------------------------
// | 03/24/2018 |   10  | SCHEDULED | (123) 456-7890 | Basic Algebra | Math, Arithmetic  |     TRUE       |
// --------------------------------------------------------------------------------------------------------

const schema = {
  startDate: {
    column: 'START DATE',
    type: Date
  },
  seats: {
    column: 'SEATS',
    type: Number,
    required: true
  },
  status: {
    column: 'STATUS',
    type: String,
    // An example of using `oneOf`
    oneOf: [
      'SCHEDULED',
      'STARTED',
      'FINISHED'
    ]
  },
  contact: {
    column: 'CONTACT',
    required: true,
    // An example of using a custom `type`
    type: PhoneNumber
  },
  // Nested object example
  course: {
    // A nested object could be declared as completely optional by specifying `required: false`.
    // In that case, when all of its properties are missing from the input data, it wouldn't throw any error
    // regardless of whether some of its properties are declared as `required: true` or not.
    required: false,
    schema: {
      title: {
        column: 'COURSE TITLE',
        type: String,
        // When course data is present, the course title must be specified.
        required: true
      },
      categories: {
        column: 'COURSE CATEGORY',
        // An example of parsing comma-separated values.
        type: [String]
      },
      isFree: {
        column: 'COURSE IS FREE',
        type: Boolean
      }
    }
  }
}

// If this code was written in TypeScript, `schema` would've been declared as:
// const schema: Schema<Object, ColumnTitle> = { ... }

// Read `data` from an `.xlsx` file and parse it using a `schema`.
const { objects, errors } = await readSheet(file, { schema })

// There have been no errors when parsing the sheet data, so `errors` is `undefined`.
// Should there have been any errors when parsing the sheet data, `errors` would've been
// an array of items having shape: `{ row, column, error, reason?, value?, type? }`.
errors === undefined

// There's one data row in the `.xlsx` file.
objects.length === 1

// The parsed data row.
objects[0] === {
  startDate: new Date(Date.UTC(2018, 3 - 1, 24)),
  seats: 10,
  status: 'SCHEDULED',
  contact: '+11234567890',
  course: {
    title: 'Basic Algebra',
    categories: ['Math', 'Arithmetic']
    isFree: true
  }
}

// An example of a custom `type` parser function.
// It will parse the cell value when it's not empty.
function PhoneNumber(value) {
  const number = parsePhoneNumber(value)
  if (!number) {
    throw new Error('invalid')
  }
  return number
}

Also, for convenience, this package exports the same feature as a separate function — 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)
}

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)
}

function ErrorsList({ errors }) {
  return (
    <ul>
      {errors.map((error, i) => (
        <li key={i}>
          <ErrorItem error={error}>
        </li>
      ))}
    </ul>
  )
}

function ErrorItem({ error }) {
  const {
    error: errorMessage,
    reason,
    row,
    column,
    columnIndex,
    value,
    type
  } = error

  // Error summary.
  return (
    <div>
      <code>"{errorMessage}"</code>
      {reason && ' '}
      {reason && <code>("{reason}")</code>}
      {' for value '}
      <code>{stringifyValue(value)}</code>
      {' in column '}
      <code>"{column}"</code>
      {' in data row '}
      <code>{row}</code>
      {' of the spreadsheet'}
    </div>
  )
}

function stringifyValue(value) {
  // Wrap strings in quotes.
  if (typeof value === 'string') {
    return '"' + value + '"'
  }
  return String(value)
}

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@9.x/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).

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