XlsxReader

An XLSX reader in Elixir.

Features:

• Accepts XLSX data located on the file system or in memory
• Automatic type conversions (numbers, date & times, booleans)
• Optional support for arbitrary precision decimal numbers
• Straightforward architecture: no ETS tables, no race-conditions, no manual resource management

The docs can be found at https://hexdocs.pm/xlsx_reader.

Installation

Add xlsx_reader as a dependency in your mix.exs:

def deps do
  [
    {:xlsx_reader, "~> 0.9.0"}
  ]
end

Run mix deps.get in your shell to fetch and compile XlsxReader.

Examples

Loading from the file system

{:ok, package} = XlsxReader.open("test.xlsx")

XlsxReader.sheet_names(package)
# ["Sheet 1", "Sheet 2", "Sheet 3"]

{:ok, rows} = XlsxReader.sheet(package, "Sheet 1")
# [
#   ["Date", "Temperature"],
#   [~D[2019-11-01], 8.4],
#   [~D[2019-11-02], 7.5],
#   ...
# ]

Loading from memory

blob = File.read!("test.xlsx")

{:ok, package} = XlsxReader.open(blob, source: :binary)

Loading all sheets at once

{:ok, sheets} = XlsxReader.sheets(package)
# [
#   {"Sheet 1", [["Date", "Temperature"], ...]},
#   {"Sheet 2", [...]},
#   ...
# ]

Loading sheets selectively

{:ok, sheets} = XlsxReader.sheets(package, only: ["Parameters", ~r/Sheet \d+/], except: ["Sheet 2"])
# [
#   {"Parameters", [...]},
#   {"Sheet 1", [...]},
#   {"Sheet 3", [...]},
#   {"Sheet 4", [...]},
#   ...
# ]

Loading all sheets at once concurrently

{:ok, sheets} = XlsxReader.async_sheets(package)
# [
#   {"Sheet 1", [["Date", "Temperature"], ...]},
#   {"Sheet 2", [...]},
#   ...
# ]

Using arbitrary precision numbers

{:ok, rows} = XlsxReader.sheet(package, "Sheet 1", number_type: Decimal)
# [
#   ["Date", "Temperature"],
#   [~D[2019-11-01], %Decimal{coef: 84, exp: -1, sign: 1}],
#   [~D[2019-11-02], %Decimal{coef: 75, exp: -1, sign: 1}],
#   ...
# ]

Access cell formulas and background color

{:ok, rows} = XlsxReader.sheet(package, "Sheet 1", cell_data_format: :cell)
# [
#   [%Cell{value: 1234.0, formula: "SUM(B1, B10)", ref: "A1", bg_color: "FFFF0000"}, ...],
#   ...
# ]

The background color is given as an ARGB string. It is nil for cells without fill as well as for gradient fills and for colors which cannot be resolved from the stylesheet alone (theme and automatic colors). For cells filled with a pattern rather than a solid color, it is an approximation of the color a spreadsheet application displays.

Development

Benchmarking

1. mix run benchmark/init.exs to create the benchmarking dataset
2. mix run benchmark/run.exs to run the Benchee suite

Contributors

In order of appearance:

• Xavier Defrang (xavier)
• Darragh Enright (darraghenright)
• Patryk Woziński (patrykwozinski)
• Evaldo Bratti (evaldobratti)
• Kian-Meng Ang (kianmeng)
• Zach Liss (ZachLiss)
• Paranojik
• Juan Barrios (03juan)
• Dylan Harness (dharness)
• Victor Rodrigues (rodrigues)
• Jose Valim (josevalim)
• Vinicius Moraes (ding-an-sich)
• Gladear
• Alessandro Agnelli
• Wei Huang
• Eric Saxby (sax)
• Aleksei Matiushkin (am-kantox)
• rigzad

License

Copyright 2020 Xavier Defrang

Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.