Struct Xlsx 

pub struct Xlsx<RS> { /* private fields */ }

A struct representing xml zipped excel file Xlsx, Xlsm, Xlam

Implementations

impl<RS: Read + Seek> Xlsx<RS>

pub fn has_1904_epoch(&self) -> bool

Check if the workbook uses the 1904 date system.

Returns true if the workbook uses the 1904 date epoch (used by some older Mac versions of Excel), or false if it uses the standard 1900 date epoch.

Examples

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

fn main() -> Result<(), Error> {
    let workbook: Xlsx<_> = open_workbook("tests/date_1904.xlsx")?;
    let is_1904 = workbook.has_1904_epoch();
    println!("Uses 1904 date system: {}", is_1904);
    Ok(())
}

pub fn pivot_tables(&mut self) -> Result<PivotTables, XlsxError>

where RS: Read + Seek,

Get all Pivot Tables in a workbook.

Note

This function is required before working with Pivot Table Data due to reliance on metadata in PivotTableRef.

pub fn pivot_table_data( &mut self, pivot_tables: &PivotTables, sheet_name: &str, pivot_table_name: &str, ) -> Result<PivotCacheIter<'_, RS>, XlsxError>

Get an iterator over a pivot table’s cached data.

Invalid Pivot Table names will return None.

Examples

An example of retrieving pivot data for a Pivot Table named PivotTable1 on sheet PivotSheet1.

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

fn main() -> Result<(), Error> {

    let path = "tests/pivots.xlsx";

    // Open the workbook.
    let mut workbook: Xlsx<_> = open_workbook(path)?;

    // Must retrieve necessary metadata before reading Pivot Table data.
    let pivot_tables = workbook.pivot_tables()?;

   // Get the Pivot Table data by referencing the pivot table name and the worksheet it resides.
    for row in workbook.pivot_table_data(&pivot_tables, "PivotSheet1", "PivotTable1")? {
            // Do something.
    }

    Ok(())
}

pub fn load_tables(&mut self) -> Result<(), XlsxError>

Load the worksheet tables from the XLSX file.

Tables in Excel are a way of grouping a range of cells into a single entity that has common formatting or that can be referenced in formulas. In calamine, tables can be read as a Table object and converted to a data Range for further processing.

Calamine does not automatically load table data from a workbook to avoid unnecessary overhead. Instead you must explicitly load the table data using the Xlsx::load_tables() method. Once the tables have been loaded the following methods can be used to extract and work with individual tables:

• Xlsx::table_by_name().
• Xlsx::table_by_name_ref().
• Xlsx::table_names().
• Xlsx::table_names_in_sheet().

These methods are explained below. See also the Table struct for additional methods that can be used when working with tables.

Errors

• XlsxError::XmlAttr.
• XlsxError::XmlEof.
• XlsxError::Xml.

pub fn table_names(&self) -> Vec<&String>

Get the names of all the tables in the workbook.

Read all the table names in the workbook. This can be used in conjunction with Xlsx::table_by_name() to iterate over the tables in the workbook.

Panics

Panics if tables have not been loaded via Xlsx::load_tables().

Examples

An example of getting the names of all the tables in an Excel workbook.

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

fn main() -> Result<(), Error> {
    let path = "tests/table-multiple.xlsx";

    // Open the workbook.
    let mut workbook: Xlsx<_> = open_workbook(path)?;

    // Load the tables in the workbook.
    workbook.load_tables()?;

    // Get all the table names in the workbook.
    let table_names = workbook.table_names();

    // Check the table names.
    assert_eq!(
        table_names,
        vec!["Inventory", "Pricing", "Sales_Bob", "Sales_Alice"]
    );

    Ok(())
}

pub fn table_names_in_sheet(&self, sheet_name: &str) -> Vec<&String>

Get the names of all the tables in a worksheet.

Read all the table names in a worksheet. This can be used in conjunction with Xlsx::table_by_name() to iterate over the tables in the worksheet.

Parameters

• sheet_name: The name of the worksheet to get the table names from.

Panics

Panics if tables have not been loaded via Xlsx::load_tables().

Examples

An example of getting the names of all the tables in an Excel workbook, sheet by sheet.

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

fn main() -> Result<(), Error> {
    let path = "tests/table-multiple.xlsx";

    // Open the workbook.
    let mut workbook: Xlsx<_> = open_workbook(path)?;

    // Get the names of all the sheets in the workbook.
    let sheet_names = workbook.sheet_names();

    // Load the tables in the workbook.
    workbook.load_tables()?;

    for sheet_name in &sheet_names {
        // Get the table names in the current sheet.
        let table_names = workbook.table_names_in_sheet(sheet_name);

        // Print the associated table names.
        println!("{sheet_name} contains tables: {table_names:?}");
    }

    Ok(())
}

Output:

Sheet1 contains tables: ["Inventory"]
Sheet2 contains tables: ["Pricing"]
Sheet3 contains tables: ["Sales_Bob", "Sales_Alice"]

pub fn table_by_name( &mut self, table_name: &str, ) -> Result<Table<Data>, XlsxError>

Get a worksheet table by name.

This method retrieves a Table from the workbook by its name. The table will contain an owned copy of the worksheet data in the table range.

Parameters

• table_name: The name of the table to retrieve.

Errors

• XlsxError::TableNotFound.
• XlsxError::NotAWorksheet.

Panics

Panics if tables have not been loaded via Xlsx::load_tables().

Examples

An example of getting an Excel worksheet table by its name. The file in this example contains 4 tables spread across 3 worksheets. This example gets an owned copy of the worksheet data in the table area.

use calamine::{open_workbook, Data, Error, Xlsx};

fn main() -> Result<(), Error> {
    let path = "tests/table-multiple.xlsx";

    // Open the workbook.
    let mut workbook: Xlsx<_> = open_workbook(path)?;

    // Load the tables in the workbook.
    workbook.load_tables()?;

    // Get the table by name.
    let table = workbook.table_by_name("Inventory")?;

    // Get the data range of the table. The data type is `&Range<Data>`.
    let data_range = table.data();

    // Do something with the data using the `Range` APIs. In this case
    // we will just check for a cell value.
    assert_eq!(
        data_range.get((0, 1)),
        Some(&Data::String("Apple".to_string()))
    );

    Ok(())
}

pub fn table_by_name_ref( &mut self, table_name: &str, ) -> Result<Table<DataRef<'_>>, XlsxError>

Get a worksheet table by name, with referenced data.

This method retrieves a Table from the workbook by its name. The table will contain an borrowed/referenced copy of the worksheet data in the table range. This is more efficient than Xlsx::table_by_name() for large tables.

Parameters

• table_name: The name of the table to retrieve.

Errors

• XlsxError::TableNotFound.
• XlsxError::NotAWorksheet.

Panics

Panics if tables have not been loaded via Xlsx::load_tables().

Examples

An example of getting an Excel worksheet table by its name. The file in this example contains 4 tables spread across 3 worksheets. This example gets a borrowed/referenced copy of the worksheet data in the table area.

use calamine::{open_workbook, DataRef, Error, Xlsx};

fn main() -> Result<(), Error> {
    let path = "tests/table-multiple.xlsx";

    // Open the workbook.
    let mut workbook: Xlsx<_> = open_workbook(path)?;

    // Load the tables in the workbook.
    workbook.load_tables()?;

    // Get the table by name.
    let table = workbook.table_by_name_ref("Inventory")?;

    // Get the data range of the table. The data type is `&Range<DataRef<'_>>`.
    let data_range = table.data();

    // Do something with the data using the `Range` APIs. In this case
    // we will just check for a cell value.
    assert_eq!(
        data_range.get((0, 1)),
        Some(&DataRef::SharedString("Apple"))
    );

    Ok(())
}

pub fn merge_cells_by_sheet_name( &mut self, name: &str, ) -> Result<Vec<Dimensions>, XlsxError>

Get the merged cells/regions in a worksheet by sheet name.

Merged cells in Excel are a range of cells that have been merged to act as a single cell. It is often used to create headers or titles that span multiple columns or rows.

The function returns a vector of Dimensions for the merged cells in the named worksheet.

Parameters

• name: The name of the worksheet.

Errors

• XlsxError::WorksheetNotFound if no worksheet with the given name exists.
• XlsxError::Xml if the worksheet XML cannot be parsed.

Examples

An example of getting the merged cells in an Excel workbook by sheet name.

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

fn main() -> Result<(), Error> {
    let path = "tests/merged_range.xlsx";

    // Open the workbook.
    let mut workbook: Xlsx<_> = open_workbook(path)?;

    // Get the names of all the sheets in the workbook.
    let sheet_names = workbook.sheet_names();

    for sheet_name in &sheet_names {
        println!("{sheet_name}:");

        // Get the merged cells in the current sheet.
        let merge_cells = workbook.merge_cells_by_sheet_name(sheet_name)?;

        // Print the dimensions of each merged region.
        for dimension in &merge_cells {
            println!("    {dimension:?}");
        }
    }

    Ok(())
}

Output:

Sheet1:
    Dimensions { start: (0, 7), end: (1, 7) }
    Dimensions { start: (0, 0), end: (1, 0) }
    Dimensions { start: (0, 1), end: (1, 1) }
    Dimensions { start: (0, 2), end: (1, 3) }
    Dimensions { start: (2, 2), end: (2, 3) }
    Dimensions { start: (3, 2), end: (3, 3) }
    Dimensions { start: (0, 4), end: (1, 4) }
    Dimensions { start: (0, 5), end: (1, 5) }
    Dimensions { start: (0, 6), end: (1, 6) }
Sheet2:
    Dimensions { start: (0, 0), end: (3, 0) }
    Dimensions { start: (2, 2), end: (3, 3) }
    Dimensions { start: (0, 5), end: (3, 7) }
    Dimensions { start: (0, 1), end: (1, 1) }
    Dimensions { start: (0, 2), end: (1, 3) }
    Dimensions { start: (0, 4), end: (1, 4) }

pub fn merge_cells_by_sheet_id( &mut self, id: usize, ) -> Result<Vec<Dimensions>, XlsxError>

Get the merged cells/regions in a worksheet by sheet index.

Merged cells in Excel are a range of cells that have been merged to act as a single cell. It is often used to create headers or titles that span multiple columns or rows.

The function returns a vector of Dimensions for the merged cells in the worksheet at the given index.

Parameters

• id: The zero-based index of the worksheet.

Errors

• XlsxError::WorksheetNotFound if id is out of range.
• XlsxError::Xml if the worksheet XML cannot be parsed.

Examples

An example of getting the merged cells in an Excel workbook by sheet index.

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

fn main() -> Result<(), Error> {
    let path = "tests/merged_range.xlsx";

    // Open the workbook.
    let mut workbook: Xlsx<_> = open_workbook(path)?;

    // Get the merged cells in the first worksheet.
    let merge_cells = workbook.merge_cells_by_sheet_id(0)?;

    // Print the dimensions of each merged region.
    for dimension in &merge_cells {
        println!("{dimension:?}");
    }

    Ok(())
}

Output:

Dimensions { start: (0, 7), end: (1, 7) }
Dimensions { start: (0, 0), end: (1, 0) }
Dimensions { start: (0, 1), end: (1, 1) }
Dimensions { start: (0, 2), end: (1, 3) }
Dimensions { start: (2, 2), end: (2, 3) }
Dimensions { start: (3, 2), end: (3, 3) }
Dimensions { start: (0, 4), end: (1, 4) }
Dimensions { start: (0, 5), end: (1, 5) }
Dimensions { start: (0, 6), end: (1, 6) }

pub fn hyperlinks_by_sheet_name( &mut self, name: &str, ) -> Result<Vec<Hyperlink>, XlsxError>

Get the hyperlinks defined on a worksheet by sheet name.

Hyperlinks in Excel can point to external URLs, email addresses, files, or to internal locations such as another cell or named range. Each hyperlink is anchored to a cell or cell range on the worksheet and may have an optional displayed text string and tooltip.

The function returns a vector of Hyperlink values for the named worksheet. An empty vector is returned for worksheets that do not declare any hyperlinks.

External hyperlink targets are resolved from the worksheet’s relationships file (xl/worksheets/_rels/sheetN.xml.rels). Purely internal hyperlinks (those declared with only a location attribute) have None for Hyperlink::target.

Parameters

• name: The name of the worksheet to get the hyperlinks from.

Errors

• XlsxError::WorksheetNotFound if no worksheet with the given name exists.
• XlsxError::Xml if the worksheet XML cannot be parsed.

Examples

An example of reading the hyperlinks from a worksheet.

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

fn main() -> Result<(), Error> {
    let path = "tests/hyperlinks.xlsx";

    // Open the workbook.
    let mut workbook: Xlsx<_> = open_workbook(path)?;

    // Get the hyperlinks defined on the first sheet.
    let hyperlinks = workbook.hyperlinks_by_sheet_name("Links")?;
    for hyperlink in &hyperlinks {
        println!("{:?} -> {:?}", hyperlink.range, hyperlink.target);
    }

    Ok(())
}

pub fn hyperlinks_by_sheet_id( &mut self, id: usize, ) -> Result<Vec<Hyperlink>, XlsxError>

Get the hyperlinks defined on a worksheet by sheet index.

See Xlsx::hyperlinks_by_sheet_name() for the per-sheet variant keyed by sheet name. This method is a convenience that resolves the sheet name from the zero-based id and forwards to it.

Parameters

• id: The zero-based index of the worksheet to get the hyperlinks from.

Errors

• XlsxError::WorksheetNotFound if no worksheet with the given index exists.
• XlsxError::Xml if the worksheet XML cannot be parsed.

Examples

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

fn main() -> Result<(), Error> {
    let path = "tests/hyperlinks.xlsx";

    let mut workbook: Xlsx<_> = open_workbook(path)?;

    let hyperlinks = workbook.hyperlinks_by_sheet_id(0)?;
    for hyperlink in &hyperlinks {
        println!("{:?} -> {:?}", hyperlink.range, hyperlink.target);
    }

    Ok(())
}

impl<RS: Read + Seek> Xlsx<RS>

pub fn worksheet_cells_reader<'a>( &'a mut self, name: &str, ) -> Result<XlsxCellReader<'a, RS>, XlsxError>

Get a reader over all used cells in the given worksheet cell reader

Trait Implementations

impl<RS: Read + Seek> Reader<RS> for Xlsx<RS>

type Error = XlsxError

Error specific to file type

fn new(reader: RS) -> Result<Self, XlsxError>

Creates a new instance.

fn with_header_row(&mut self, header_row: HeaderRow) -> &mut Self

Set header row (i.e. first row to be read) If header_row is None, the first non-empty row will be used as header row

fn vba_project(&mut self) -> Result<Option<VbaProject>, XlsxError>

Gets VbaProject

fn metadata(&self) -> &Metadata

Initialize

fn worksheet_range(&mut self, name: &str) -> Result<Range<Data>, XlsxError>

Read worksheet data in corresponding worksheet path

fn worksheet_formula(&mut self, name: &str) -> Result<Range<String>, XlsxError>

Read worksheet formula in corresponding worksheet path

fn worksheets(&mut self) -> Vec<(String, Range<Data>)>

Fetch all worksheet data & paths

fn pictures(&self) -> Option<Vec<(String, Vec<u8>)>>

Available on crate feature picture only.

Get the raw data of the pictures in a workbook.

fn pictures_with_metadata(&self) -> Vec<Picture>

Available on crate feature picture only.

Get workbook picture/image metadata.

fn sheet_names(&self) -> Vec<String>

Get all sheet names of this workbook, in workbook order

fn sheets_metadata(&self) -> &[Sheet]

Fetch all sheets metadata

fn defined_names(&self) -> &[(String, String)]

Get all defined names (Ranges names etc)

fn worksheet_range_at( &mut self, n: usize, ) -> Option<Result<Range<Data>, Self::Error>>

Get the nth worksheet. Shortcut for getting the nth worksheet name, then the corresponding worksheet.

impl<RS: Read + Seek> ReaderRef<RS> for Xlsx<RS>

fn worksheet_range_ref<'a>( &'a mut self, name: &str, ) -> Result<Range<DataRef<'a>>, XlsxError>

Get worksheet range where shared string values are only borrowed.

fn worksheet_range_at_ref( &mut self, n: usize, ) -> Option<Result<Range<DataRef<'_>>, Self::Error>>

Get the nth worksheet range where shared string values are only borrowed. Shortcut for getting the nth worksheet name, then the corresponding worksheet.