Skip to main content

Xlsx

calamine_styles

Struct Xlsx 

Source 
pub struct Xlsx<RS> {
    pub styles: Vec<Style>,
    /* private fields */
}
Expand description

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

Fields§

§styles: Vec<Style>

Cell styles

Implementations§

Source§

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

Source

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.

Source

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(())
}
Source

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

Load the merged regions in the workbook.

A merged region in Excel is 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.

This method must be called before accessing the merged regions using the methods:

These methods are explained below.

§Errors
Source

pub fn merged_regions(&self) -> &Vec<(String, String, Dimensions)>

Get the merged regions for all the worksheets in a workbook.

The function returns a ref to a vector of tuples containing the sheet name, the sheet path, and the Dimensions of the merged region. The middle element of the tuple can generally be ignored.

The Xlsx::load_merged_regions() method must be called before calling this method.

§Examples

An example of getting all the merged regions in an Excel workbook.

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)?;

    // Load the merged regions in the workbook.
    workbook.load_merged_regions()?;

    // Get all the merged regions in the workbook.
    let merged_regions = workbook.merged_regions();

    // Print the sheet name and dimensions of each merged region.
    for (sheet_name, _, dimensions) in merged_regions {
        println!("{sheet_name}: {dimensions:?}");
    }

    Ok(())
}

Output:

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

pub fn merged_regions_by_sheet( &self, name: &str, ) -> Vec<(&String, &String, &Dimensions)>

Get the merged regions in a workbook by the sheet name.

The function returns a vector of tuples containing the sheet name, the sheet path, and the Dimensions of the merged region. The first two elements of the tuple can generally be ignored.

The Xlsx::load_merged_regions() method must be called before calling this method.

§Parameters
  • sheet_name: The name of the worksheet to get the merged regions from.
§Examples

An example of getting the merged regions in an Excel workbook, by individual worksheet.

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();

    // Load the merged regions in the workbook.
    workbook.load_merged_regions()?;

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

        // Get the merged regions in the current sheet.
        let merged_regions = workbook.merged_regions_by_sheet(sheet_name);

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

    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) }
Source

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:

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

§Errors
Source

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(())
}
Source

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"]
Source

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
§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(())
}
Source

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
§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(())
}
Source

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

Get the merged cells/regions in a workbook by the 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 of the merged region. This is wrapped in a Result and an Option.

§Parameters
  • sheet_name: The name of the worksheet to get the merged regions from.
§Errors
§Examples

An example of getting the merged regions/cells in an Excel workbook, by individual worksheet.

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.worksheet_merge_cells(sheet_name);

        if let Some(dimensions) = merge_cells {
            let dimensions = dimensions?;

            // Print the dimensions of each merged region.
            for dimension in &dimensions {
                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) }
Source

pub fn worksheet_merge_cells_at( &mut self, sheet_index: usize, ) -> Option<Result<Vec<Dimensions>, XlsxError>>

Get the merged cells/regions in a workbook by the 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 of the merged region. This is wrapped in a Result and an Option.

§Parameters
  • sheet_index: The zero index of the worksheet to get the merged regions from.
§Errors
§Examples

An example of getting the merged regions/cells in an Excel workbook, by worksheet 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.worksheet_merge_cells_at(0);

    if let Some(dimensions) = merge_cells {
        let dimensions = dimensions?;

        // Print the dimensions of each merged region.
        for dimension in &dimensions {
            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) }
Source

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

Get the cells reader for a worksheet.

This function returns a [XlsxCellReader] for the specified worksheet. The reader can be used to iterate over the cells in the worksheet.

§Parameters
  • name: The name of the worksheet to get the cells reader for.
§Errors
§Examples

An example of getting the cells reader for a worksheet.

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 cells reader for the first worksheet.
    let mut reader = workbook.worksheet_cells_reader("Sheet1")?;

    // Iterate over the cells in the worksheet.
    while let Some(cell) = reader.next_cell()? {
        println!("{:?}", cell);
    }

    Ok(())
}

Output:

Cell {
    row: 0,
    col: 0,
    val: "Hello, world!".to_string(),
    err: None,
    typ: DataType::String,
}
Source

pub fn worksheet_style(&mut self, name: &str) -> Result<StyleRange, XlsxError>

Get the styles for a worksheet.

Get worksheet styles as an RLE-compressed StyleRange.

This function returns styles for all cells with explicit formatting, stored in run-length encoded format for memory efficiency.

§Parameters
  • name: The name of the worksheet to get the styles for.
§Example
ⓘ
let styles = xlsx.worksheet_style("Sheet1")?;
println!("Unique styles: {}", styles.unique_style_count());
println!("Compression ratio: {:.1}x", styles.compression_ratio());
for (row, col, style) in styles.cells() {
    // process style
}
Source

pub fn worksheet_layout( &mut self, name: &str, ) -> Result<WorksheetLayout, XlsxError>

Get the layout for a worksheet.

This function returns a WorksheetLayout for the specified worksheet. The layout contains the column widths and row heights for the cells in the worksheet.

§Parameters
  • name: The name of the worksheet to get the layout for.
Source

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

Get all worksheets in the workbook.

This function returns a vector of tuples, where each tuple contains the name of a worksheet and the range of cells in the worksheet.

§Returns

A vector of tuples, where each tuple contains the name of a worksheet and the range of cells in the worksheet.

Trait Implementations§

Source§

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

Source§

type Error = XlsxError

Error specific to file type
Source§

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

Creates a new instance.
Source§

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
Source§

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

Gets VbaProject
Source§

fn metadata(&self) -> &Metadata

Initialize
Source§

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

Read worksheet data in corresponding worksheet path
Source§

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

Read worksheet formula in corresponding worksheet path
Source§

fn worksheet_style(&mut self, name: &str) -> Result<StyleRange, XlsxError>

Read worksheet styles as an RLE-compressed StyleRange. Read more
Source§

fn worksheet_layout(&mut self, name: &str) -> Result<WorksheetLayout, XlsxError>

Read worksheet layout information (column widths and row heights)
Source§

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

Fetch all worksheet data & paths
Source§

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

Available on crate feature picture only.
Get the raw data of the pictures in a workbook. Read more
Source§

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

Get all sheet names of this workbook, in workbook order Read more
Source§

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

Fetch all sheets metadata
Source§

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

Get all defined names (Ranges names etc)
Source§

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.
Source§

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

Source§

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. Read more
Source§

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. Read more

Auto Trait Implementations§

§

impl<RS> Freeze for Xlsx<RS>
where RS: Freeze,

§

impl<RS> RefUnwindSafe for Xlsx<RS>
where RS: RefUnwindSafe,

§

impl<RS> Send for Xlsx<RS>
where RS: Send,

§

impl<RS> Sync for Xlsx<RS>
where RS: Sync,

§

impl<RS> Unpin for Xlsx<RS>
where RS: Unpin,

§

impl<RS> UnsafeUnpin for Xlsx<RS>
where RS: UnsafeUnpin,

§

impl<RS> UnwindSafe for Xlsx<RS>
where RS: UnwindSafe,

Blanket Implementations§

Source§

impl<T> Any for T
where T: 'static + ?Sized,

Source§

fn type_id(&self) -> TypeId

Gets the TypeId of self. Read more
Source§

impl<T> Borrow<T> for T
where T: ?Sized,

Source§

fn borrow(&self) -> &T

Immutably borrows from an owned value. Read more
Source§

impl<T> BorrowMut<T> for T
where T: ?Sized,

Source§

fn borrow_mut(&mut self) -> &mut T

Mutably borrows from an owned value. Read more
Source§

impl<T> From<T> for T

Source§

fn from(t: T) -> T

Returns the argument unchanged.

Source§

impl<T, U> Into<U> for T
where U: From<T>,

Source§

fn into(self) -> U

Calls U::from(self).

That is, this conversion is whatever the implementation of From<T> for U chooses to do.

Source§

impl<T, U> TryFrom<U> for T
where U: Into<T>,

Source§

type Error = Infallible

The type returned in the event of a conversion error.
Source§

fn try_from(value: U) -> Result<T, <T as TryFrom<U>>::Error>

Performs the conversion.
Source§

impl<T, U> TryInto<U> for T
where U: TryFrom<T>,

Source§

type Error = <U as TryFrom<T>>::Error

The type returned in the event of a conversion error.
Source§

fn try_into(self) -> Result<U, <U as TryFrom<T>>::Error>

Performs the conversion.