An Excel/OpenDocument Spreadsheets file reader/deserializer, in pure Rust.
calamine is a pure Rust library to read and deserialize any spreadsheet file:
- excel like (
xls,xlsx,xlsm,xlsb,xla,xlam) - opendocument spreadsheets (
ods)
As long as your files are simple enough, this library should just work. For anything else, please file an issue with a failing test or send a pull request!
It is as simple as:
use calamine::{open_workbook,Error,Xlsx,Reader,RangeDeserializerBuilder};fnexample() -> Result<(),Error>{let path = format!("{}/tests/temperature.xlsx", env!("CARGO_MANIFEST_DIR"));letmut workbook:Xlsx<_> = open_workbook(path)?;let range = workbook.worksheet_range("Sheet1").ok_or(Error::Msg("Cannot find 'Sheet1'"))??;letmut iter = RangeDeserializerBuilder::new().from_range(&range)?;ifletSome(result) = iter.next(){let(label, value):(String,f64) = result?;assert_eq!(label,"celsius");assert_eq!(value,22.2222);Ok(())}else{Err(From::from("expected at least one record but got none"))}}Note if you want to deserialize a column that may have invalid types (i.e. a float where some values may be strings), you can use Serde's deserialize_with field attribute:
use serde::Deserialize;use calamine::{RangeDeserializerBuilder,Reader,Xlsx};#[derive(Deserialize)]structExcelRow{metric:String,#[serde(deserialize_with = "de_opt_f64")]value:Option<f64>,}// Convert value cell to Some(f64) if float or int, else Nonefnde_opt_f64<'de,D>(deserializer:D) -> Result<Option<f64>,D::Error>whereD: serde::Deserializer<'de>,{let data_type = calamine::DataType::deserialize(deserializer)?;ifletSome(float) = data_type.as_f64(){Ok(Some(float))}else{Ok(None)}}fnmain() -> Result<(),Box<dyn std::error::Error>>{let path = format!("{}/tests/excel.xlsx", env!("CARGO_MANIFEST_DIR"));letmut excel:Xlsx<_> = open_workbook(path)?;let range = excel
.worksheet_range("Sheet1").ok_or(calamine::Error::Msg("Cannot find Sheet1"))??;let iter_result =
RangeDeserializerBuilder::with_headers(&COLUMNS).from_range::<_,ExcelRow>(&range)?;}use calamine::{Reader,Xlsx, open_workbook};letmut excel:Xlsx<_> = open_workbook("file.xlsx").unwrap();ifletSome(Ok(r)) = excel.worksheet_range("Sheet1"){for row in r.rows(){println!("row={:?}, row[0]={:?}", row, row[0]);}}Let's assume
- the file type (xls, xlsx ...) cannot be known at static time
- we need to get all data from the workbook
- we need to parse the vba
- we need to see the defined names
- and the formula!
use calamine::{Reader, open_workbook_auto,Xlsx,DataType};// opens a new workbooklet path = ...;// we do not know the file typeletmut workbook = open_workbook_auto(path).expect("Cannot open file");// Read whole worksheet data and provide some statisticsifletSome(Ok(range)) = workbook.worksheet_range("Sheet1"){let total_cells = range.get_size().0* range.get_size().1;let non_empty_cells:usize = range.used_cells().count();println!("Found {} cells in 'Sheet1', including {} non empty cells",
total_cells, non_empty_cells);// alternatively, we can manually filter rowsassert_eq!(non_empty_cells, range.rows().flat_map(|r| r.iter().filter(|&c| c != &DataType::Empty)).count());}// Check if the workbook has a vba projectifletSome(Ok(mut vba)) = workbook.vba_project(){let vba = vba.to_mut();let module1 = vba.get_module("Module 1").unwrap();println!("Module 1 code:");println!("{}", module1);for r in vba.get_references(){if r.is_missing(){println!("Reference {} is broken or not accessible", r.name);}}}// You can also get defined names definition (string representation only)for name in workbook.defined_names(){println!("name: {}, formula: {}", name.0, name.1);}// Now get all formula!let sheets = workbook.sheet_names().to_owned();for s in sheets {println!("found {} formula in '{}'",
workbook
.worksheet_formula(&s).expect("sheet not found").expect("error while getting formula").rows().flat_map(|r| r.iter().filter(|f| !f.is_empty())).count(),
s);}dates: Add date related fn toDataType.picture: Extract picture data.
Browse the examples directory.
As calamine is readonly, the comparisons will only involve reading an excel xlsx file and then iterating over the rows. Along with calamine, three other libraries were chosen, from three different languages:
The benchmarks were done using this dataset, a 186MBxlsx file when the csv is converted. The plotting data was gotten from the sysinfo crate, at a sample interval of 200ms. The program samples the reported values for the running process and records it.
The programs are all structured to follow the same constructs:
calamine:
use calamine::{open_workbook,Reader,Xlsx};fnmain(){// Open workbook letmut excel:Xlsx<_> =
open_workbook("NYC_311_SR_2010-2020-sample-1M.xlsx").expect("failed to find file");// Get worksheetlet sheet = excel
.worksheet_range("NYC_311_SR_2010-2020-sample-1M").unwrap().unwrap();// iterate over rowsfor _row in sheet.rows(){}}excelize:
package main
import (
"fmt""github.com/xuri/excelize/v2"
)
funcmain() {
// Open workbookfile, err:=excelize.OpenFile(`NYC_311_SR_2010-2020-sample-1M.xlsx`)
iferr!=nil {
fmt.Println(err)
return
}
deferfunc() {
// Close the spreadsheet.iferr:=file.Close(); err!=nil {
fmt.Println(err)
}
}()
// Select worksheetrows, err:=file.Rows("NYC_311_SR_2010-2020-sample-1M")
iferr!=nil {
fmt.Println(err)
return
}
// Iterate over rowsforrows.Next() {
}
}ClosedXML:
usingClosedXML.Excel;internalclassProgram{privatestaticvoidMain(string[]args){// Open workbookusingvarworkbook=newXLWorkbook("NYC_311_SR_2010-2020-sample-1M.xlsx");// Get Worksheet// "NYC_311_SR_2010-2020-sample-1M"varworksheet=workbook.Worksheet(1);// Iterate over rowsforeach(varrowinworksheet.Rows()){}}}openpyxl:
fromopenpyxlimportload_workbook# Open workbookwb=load_workbook(
filename=r'NYC_311_SR_2010-2020-sample-1M.xlsx', read_only=True)
# Get worksheetws=wb['NYC_311_SR_2010-2020-sample-1M']
# Iterate over rowsforrowinws.rows:
_=row# Close the workbook after readingwb.close()The benchmarking was done using hyperfine with --warmup 3 on an AMD RYZEN 9 5900X @ 4.0GHz running Windows 11. Both calamine and ClosedXML were built in release mode.
0.22.1 calamine.exe
Time (mean ± σ): 25.278 s ± 0.424 s [User: 24.852 s, System: 0.470 s]
Range (min … max): 24.980 s … 26.369 s 10 runs
v2.8.0 excelize.exe
Time (mean ± σ): 44.254 s ± 0.574 s [User: 46.071 s, System: 7.754 s]
Range (min … max): 42.947 s … 44.911 s 10 runs
0.102.1 closedxml.exe
Time (mean ± σ): 178.343 s ± 3.673 s [User: 177.442 s, System: 2.612 s]
Range (min … max): 173.232 s … 185.086 s 10 runs
3.0.10 openpyxl.py
Time (mean ± σ): 238.554 s ± 1.062 s [User: 238.016 s, System: 0.661 s]
Range (min … max): 236.798 s … 240.167 s 10 runscalamine is 1.75x faster than excelize, 7.05x faster than ClosedXML, and 9.43x faster than openpyxl.
The spreadsheet has a range of 1,000,001 rows and 41 columns, for a total of 41,000,041 cells in the range. Of those, 28,056,975 cells had values.
Going off of that number:
calamine=> 1,122,279 cells per secondexcelize=> 633,998 cells per secondClosedXML=> 157,320 cells per secondopenpyxl=> 117,612 cells per second
As stated, the filesize on disk is 186MB:
calamine=>186MBClosedXML=>208MB.openpyxl=>192MB.excelize=>1.5GB.
When asking one of the maintainers of excelize, I got this response:
To avoid high memory usage for reading large files, this library allows user-specific UnzipXMLSizeLimit options when opening the workbook, to set the memory limit on the unzipping worksheet and shared string table in bytes, worksheet XML will be extracted to the system temporary directory when the file size is over this value, so you can see that data written in reading mode, and you can change the default for that to avoid this behavior.
- xuri
As seen in the previous section, excelize is writting to disk to save memory. The others don't employ that kind of mechanism.
Note
ClosedXML was reporting a constant 2.5TB of virtual memory usage, so it was excluded from the chart.
The stepping and falling for calamine is from the grows of Vecs and the freeing of memory right after, with the memory usage dropping down again. The sudden jump at the end is when the sheet is being read into memory. The others, being garbage collected, have a more linear climb all the way through.
Very noisy chart, but excelize's spikes must be from the GC?
Many (most) part of the specifications are not implemented, the focus has been put on reading cell values and vba code.
The main unsupported items are:
- no support for writing excel files, this is a read-only library
- no support for reading extra contents, such as formatting, excel parameter, encrypted components etc ...
- no support for reading VB for opendocuments
Thanks to xlsx-js developers! This library is by far the simplest open source implementation I could find and helps making sense out of official documentation.
Thanks also to all the contributors!
MIT