- OCaml 86%
- C 10%
- Python 3%
- Shell <1%
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.tbefore 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.