calamine

纯Rust编写的Excel/OpenDocument电子表格文件读取器/反序列化器。

文档

描述

calamine 是一个纯Rust库，用于读取和反序列化任何类似Excel的电子表格文件

• (xls, xlsx, xlsm, xlsb, xla, xlam)
• opendocument电子表格(ods)

只要你的文件足够简单，这个库就应该可以正常工作。对于其他任何事情，请提交一个包含失败测试的issue或发送一个pull request！

示例

Serde反序列化

就像这样简单

use calamine::{open_workbook, Error, Xlsx, Reader, RangeDeserializerBuilder};

fn example() -> Result<(), Error> {
    let path = format!("{}/tests/temperature.xlsx", env!("CARGO_MANIFEST_DIR"));
    let mut workbook: Xlsx<_> = open_workbook(path)?;
    let range = workbook.worksheet_range("Sheet1")
        .ok_or(Error::Msg("Cannot find 'Sheet1'"))??;

    let mut iter = RangeDeserializerBuilder::new().from_range(&range)?;

    if let Some(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"))
    }
}

Calamine提供了处理无效类型值的辅助函数。例如，如果你想要反序列化一个应包含浮点数但可能也包含无效值（即字符串）的列，你可以使用Serde的deserialize_as_f64_or_none辅助函数与Serde的deserialize_with字段属性

use calamine::{deserialize_as_f64_or_none, open_workbook, RangeDeserializerBuilder, Reader, Xlsx};
use serde::Deserialize;

#[derive(Deserialize)]
struct Record {
    metric: String,
    #[serde(deserialize_with = "deserialize_as_f64_or_none")]
    value: Option<f64>,
}

fn main() -> Result<(), Box<dyn std::error::Error>> {
    let path = format!("{}/tests/excel.xlsx", env!("CARGO_MANIFEST_DIR"));
    let mut excel: Xlsx<_> = open_workbook(path)?;

    let range = excel
        .worksheet_range("Sheet1")
        .map_err(|_| calamine::Error::Msg("Cannot find Sheet1"))?;

    let iter_records =
        RangeDeserializerBuilder::with_headers(&["metric", "value"]).from_range(&range)?;

    for result in iter_records {
        let record: Record = result?;
        println!("metric={:?}, value={:?}", record.metric, record.value);
    }

    Ok(())
}

deserialize_as_f64_or_none函数将丢弃所有无效值，如果你想将它们作为String返回，可以使用deserialize_as_f64_or_string函数代替。

读取器：简单

use calamine::{Reader, Xlsx, open_workbook};

let mut excel: Xlsx<_> = open_workbook("file.xlsx").unwrap();
if let Some(Ok(r)) = excel.worksheet_range("Sheet1") {
    for row in r.rows() {
        println!("row={:?}, row[0]={:?}", row, row[0]);
    }
}

读取器：更复杂

假设

• 文件类型（xls, xlsx ...）在静态时间无法知道
• 我们需要从工作簿中获取所有数据
• 我们需要解析VBA
• 我们需要查看定义的名称
• 以及公式！

use calamine::{Reader, open_workbook_auto, Xlsx, DataType};

// opens a new workbook
let path = ...; // we do not know the file type
let mut workbook = open_workbook_auto(path).expect("Cannot open file");

// Read whole worksheet data and provide some statistics
if let Some(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 rows
    assert_eq!(non_empty_cells, range.rows()
        .flat_map(|r| r.iter().filter(|&c| c != &DataType::Empty)).count());
}

// Check if the workbook has a vba project
if let Some(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：向DataType添加与日期相关的函数。
• picture：提取图片数据。

其他

浏览 示例 目录。

性能

由于 calamine 是只读的，比较将仅涉及读取 Excel xlsx 文件，然后遍历行。除了 calamine 之外，还选择了三个来自不同语言的库

• excelize，用 go 编写
• ClosedXML，用 C# 编写
• openpyxl，用 python 编写

基准测试使用的是这个 数据集，一个当 csv 转换时为 186MB 的 xlsx 文件。绘图数据来自 sysinfo crate，采样间隔为 200ms。程序会采样运行进程报告的值并记录下来。

所有程序都遵循相同的结构

calamine:

use calamine::{open_workbook, Reader, Xlsx};

fn main() {
    // Open workbook 
    let mut excel: Xlsx<_> =
        open_workbook("NYC_311_SR_2010-2020-sample-1M.xlsx").expect("failed to find file");

    // Get worksheet
    let sheet = excel
        .worksheet_range("NYC_311_SR_2010-2020-sample-1M")
        .unwrap()
        .unwrap();

    // iterate over rows
    for _row in sheet.rows() {}
}

excelize:

package main

import (
        "fmt"
        "github.com/xuri/excelize/v2"
)

func main() {
        // Open workbook
        file, err := excelize.OpenFile(`NYC_311_SR_2010-2020-sample-1M.xlsx`)

        if err != nil {
                fmt.Println(err)
                return
        }

        defer func() {
                // Close the spreadsheet.
                if err := file.Close(); err != nil {
                        fmt.Println(err)
                }
        }()

        // Select worksheet
        rows, err := file.Rows("NYC_311_SR_2010-2020-sample-1M")
        if err != nil {
                fmt.Println(err)
                return
        }

        // Iterate over rows
        for rows.Next() {
        }
}

ClosedXML:

using ClosedXML.Excel;

internal class Program
{
        private static void Main(string[] args)
        {
                // Open workbook
                using var workbook = new XLWorkbook("NYC_311_SR_2010-2020-sample-1M.xlsx");

                // Get Worksheet
                // "NYC_311_SR_2010-2020-sample-1M"
                var worksheet = workbook.Worksheet(1);

                // Iterate over rows
                foreach (var row in worksheet.Rows())
                {

                }
        }
}

openpyxl:

from openpyxl import load_workbook

# Open workbook
wb = load_workbook(
    filename=r'NYC_311_SR_2010-2020-sample-1M.xlsx', read_only=True)

# Get worksheet
ws = wb['NYC_311_SR_2010-2020-sample-1M']

# Iterate over rows
for row in ws.rows:
    _ = row

# Close the workbook after reading
wb.close()

基准测试

基准测试使用 hyperfine 进行，使用 --warmup 3 在一个 AMD RYZEN 9 5900X @ 4.0GHz 上运行 Windows 11。两者 calamine 和 ClosedXML 都是在发布模式下构建的。

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 runs