XLSX (Excel) spreadsheet reader
README.md

xlsx #

Read Excel spreadsheets in OCaml, in both the .xlsx and the older .xls format.

Statistics offices and procurement sites still publish tables as Excel files, some in a format twenty years old, and without a reader somebody ends up converting each one to CSV by hand. xlsx reads both formats into the same workbook AST, so one codec works for either.

An .xlsx file is an OOXML SpreadsheetML package (ECMA-376, also ISO/IEC 29500): a ZIP archive with a few XML parts, namely xl/workbook.xml, xl/sharedStrings.xml, xl/worksheets/sheet*.xml and xl/_rels/workbook.xml.rels. The .xls files that Excel 97 to 2003 wrote, and that data portals set up back then still serve, are BIFF8 records inside an OLE2 compound file. The compound file is a small FAT file system in one file: 512-byte sectors, an allocation table chaining them, a directory of named streams. BIFF8 (Binary Interchange File Format 8) fills the Workbook stream with typed records, each starting with a 2-byte type and a 2-byte length.

You get typed records out of the AST with codec combinators. An Xlsx.Codec.t is a GADT value indexed by the OCaml type it decodes to, and you build it with combinators like Xlsx.Codec.col and Xlsx.Codec.sheet. This is the finally tagged style of Daniel Bünzli's Jsont: the codec is plain data that interpreters walk, either to decode or to pretty-print, so one description can be both run and inspected. There is also a cursor for moving around the tree, and two exports, Xlsx.Csv.of_sheet for RFC 4180 CSV bytes and Xlsx.Grid.of_sheet for a plain string array array. Every node carries a Loc.Meta.t, which is how a decode error can point at a byte offset in the sheet XML.

Installation #

$ opam install xlsx

If opam cannot find the package, it is not in the public opam-repository yet. Add the overlay repository first:

$ opam repo add samoht https://tangled.org/gazagnaire.org/opam-overlay.git
$ opam update
$ opam install xlsx

Usage #

(* Read a workbook, dump the first sheet as CSV. *)
let dump_csv xlsx_bytes =
  match Xlsx.of_string Xlsx.Codec.Value.t xlsx_bytes with
  | Error e -> Fmt.epr "xlsx: %a@." Xlsx.Error.pp e
  | Ok wb ->
      let sheet = List.hd wb.Xlsx.Value.sheets in
      print_string (Xlsx.Csv.of_sheet sheet)
(* Decode a typed column from a named sheet. *)
let user_emails xlsx_bytes =
  let codec = Xlsx.Codec.(sheet_named "Users" (sheet (col "B" text))) in
  match Xlsx.of_string codec xlsx_bytes with
  | Ok emails -> emails
  | Error e ->
      Fmt.epr "xlsx: %a@." Xlsx.Error.pp e;
      []

Decoders return (_, Loc.Error.t) result; the _exn variants raise Loc.Error.Error instead. The spreadsheet-specific cases (Sort_mismatch, Missing_column, Missing_sheet, Container_error, Xml_error, Unsupported_format) are added to Loc.Error.kind by Xlsx.Error.

Cell types #

Xlsx.Value.cell has one constructor per SpreadsheetML cell type:

Constructor Wire format
Empty <c/> or <c> with no <v> child
Bool t="b", value "0" or "1"
Number default or t="n", float (XLSX stores all numerics as 64-bit doubles)
Text t="s" (shared-strings index, resolved during decode), t="inlineStr", or t="str"
Cell_error t="e", the literal token ("#DIV/0!", "#REF!", ...)

Dates come back as a Number, because that is what the file stores: a serial day count. In the default 1900 date system 1 is 1900-01-01, and the fractional part is the time of day. The system also counts a 29 February 1900 that never happened, as serial 60. A workbook flagged 1904 counts from 1904-01-01 instead (ECMA-376 Part 1, section 18.17.4). Xlsx.Value.date1904 wb says which system wb counts in, and Xlsx.Value.date_of_serial ~date1904 n is the (year, month, day) of the serial n: the day of its floor, so a time late in the day stays on its date. Xlsx.Value.serial_of_date ~date1904 (y, m, d) goes the other way, to the serial of the start of that day.

# Xlsx.Value.date_of_serial 45658.75;;
- : (int * int * int) option = Some (2025, 1, 1)
# Xlsx.Value.serial_of_date (2025, 1, 1);;
- : float option = Some 45658.

Formulas are not parsed. A formula cell on the wire looks like <c><f>SUM(A1:A10)</f><v>55</v></c> and decodes to the cached 55. If a file was saved with "needs recalculate" set, or written by a tool that omits <v> for formula cells (LibreOffice, notably), there is no cached value and the cell decodes as Empty. Open it in Excel and save it again to get the values back.

Legacy .xls #

Xlsx.Cfb reads the OLE2 container and Xlsx.Xls reads BIFF8, and you can use either alone. Together they give the cell values of every sheet in workbook order: text through the shared string table, booleans, error codes, blank cells, the last cached result of each formula, and numbers in every RK packing. RK is BIFF8's compact 32-bit number. Bit 1 says whether the top 30 bits are a signed integer or the top 30 bits of an IEEE double whose low 34 bits are zero, and bit 0 says whether to divide by 100, which is how 12.34 gets stored as the integer 1234.

test/interop/xlrd checks every cell of six xlwt-written workbooks against xlrd, Python's .xls reader for twenty years. The workbooks go after the easy mistakes: the RK packing, a shared string table long enough to spill into CONTINUE records, a string split across that boundary, and a formula's cached result.

A password-protected workbook is refused. Every value after its FILEPASS record is encrypted, so the cells would be garbage.

Files that are not spreadsheets #

Plenty of files called .xlsx or .xls are something else. Rather than fail on the ZIP header with "bad magic", the decoder returns Unsupported_format naming what the file really is, because a caller treats a file in the wrong format (convert it) differently from a broken download (fetch it again). The first bytes are enough to spot a bare BIFF5 stream, a SpreadsheetML XML file or an HTML table; Excel writes the last two when told to save a web page or an XML workbook with an .xls name. An OLE2 compound file with no workbook stream, a .doc or a .ppt for instance, is named after the container is opened. Inside a ZIP, the parts give away .docx, .pptx, .ods and .xlsb. Any other ZIP is broken or unknown, and you get Container_error.

Limits #

  • Memory use grows with the workbook. The whole archive is decoded into a Value.t before any codec runs. That is fine for configuration tables, reference data and small reports, and too much for sheets of several gigabytes. A streaming decoder that does not hold the whole tree is planned.
  • There is no writer. To publish data again, export it with the CSV or grid sinks.
  • Formulas are not evaluated. You get the cached <v> value from Excel's last save.
  • Formatting is ignored, so a date stays the serial number stored in the file.
  • BIFF5 and BIFF7 (Excel 5 and 95) are detected and named, but not read. They use an older string encoding than BIFF8, and guessing it would give garbled text with no error.

API #

The signatures are in xlsx.mli: Xlsx.Sort (the closed enum of node categories), Xlsx.Value (the workbook AST), Xlsx.Codec (the combinators), Xlsx.Cursor (the zipper), Xlsx.Error, and the Xlsx.Csv and Xlsx.Grid sinks.

License #

ISC. See LICENSE.md.