Crate rust_xlsxwriter

Expand description

rust_xlsxwriter is a Rust library for writing Excel files in the xlsx format.

The rust_xlsxwriter crate can be used to write text, numbers, dates, and formulas to multiple worksheets in a new Excel 2007+ .xlsx file. It has a focus on performance and fidelity with the file format created by Excel. It cannot be used to modify an existing file.

rust_xlsxwriter is a rewrite of the Python XlsxWriter library in Rust by the same author, with additional Rust-like features and APIs. The supported features are:

Support for writing all basic Excel data types.
Full cell formatting support.
Formula support, including new Excel 365 dynamic functions.
Charts.
Hyperlink support.
Page/Printing Setup support.
Merged ranges.
Conditional formatting.
Data validation.
Cell Notes.
Textboxes.
Checkboxes.
Sparklines.
Worksheet PNG/JPEG/GIF/BMP images.
Rich multi-format strings.
Outline groupings.
Defined names.
Autofilters.
Worksheet Tables.
Serde serialization support.
Support for macros.
Memory optimization mode for writing large files.

§Table of Contents

Tutorial: A getting started and tutorial guide.
Cookbook: Examples of using rust_xlsxwriter.

Workbook: The entry point for creating an Excel workbook with worksheets.
Working with Workbooks: A higher-level introduction to creating and working with workbooks.

Worksheet: The main spreadsheet canvas for writing data and objects to a worksheet.
Working with Worksheets: A higher-level introduction to creating and working with worksheets.

Chart struct: The interface for creating worksheet charts.
Working with Charts: A higher-level introduction to creating and using charts.

Format: The interface for adding formatting to worksheets and other objects.
Table: The interface for worksheet tables. 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.
Image: The interface for images used in worksheets.
Conditional Formats: Working with conditional formatting in worksheets.
DataValidation: Working with data validation in worksheets.
Note: Adding Notes to worksheet cells.
Shape: Adding Textbox shapes to worksheets.
Macros: Working with Macros.
Sparklines: Working with Sparklines.
ExcelDateTime: A type to represent dates and times in Excel format.
Formula: A type for Excel formulas.
Url: A type for URLs/Hyperlinks used in worksheets.
DocProperties: The interface used to create an object to represent document metadata properties.

Changelog: Release notes and changelog.
Performance: Performance characteristics of rust_xlsxwriter.

§Example

Sample code to generate the Excel file shown above.

use rust_xlsxwriter::*;

fn main() -> Result<(), XlsxError> {
    // Create a new Excel file object.
    let mut workbook = Workbook::new();

    // Create some formats to use in the worksheet.
    let bold_format = Format::new().set_bold();
    let decimal_format = Format::new().set_num_format("0.000");
    let date_format = Format::new().set_num_format("yyyy-mm-dd");
    let merge_format = Format::new()
        .set_border(FormatBorder::Thin)
        .set_align(FormatAlign::Center);

    // Add a worksheet to the workbook.
    let worksheet = workbook.add_worksheet();

    // Set the column width for clarity.
    worksheet.set_column_width(0, 22)?;

    // Write a string without formatting.
    worksheet.write(0, 0, "Hello")?;

    // Write a string with the bold format defined above.
    worksheet.write_with_format(1, 0, "World", &bold_format)?;

    // Write some numbers.
    worksheet.write(2, 0, 1)?;
    worksheet.write(3, 0, 2.34)?;

    // Write a number with formatting.
    worksheet.write_with_format(4, 0, 3.00, &decimal_format)?;

    // Write a formula.
    worksheet.write(5, 0, Formula::new("=SIN(PI()/4)"))?;

    // Write a date.
    let date = ExcelDateTime::from_ymd(2023, 1, 25)?;
    worksheet.write_with_format(6, 0, &date, &date_format)?;

    // Write some links.
    worksheet.write(7, 0, Url::new("https://www.rust-lang.org"))?;
    worksheet.write(8, 0, Url::new("https://www.rust-lang.org").set_text("Rust"))?;

    // Write some merged cells.
    worksheet.merge_range(9, 0, 9, 1, "Merged cells", &merge_format)?;

    // Insert an image.
    let image = Image::new("examples/rust_logo.png")?;
    worksheet.insert_image(1, 2, &image)?;

    // Save the file to disk.
    workbook.save("demo.xlsx")?;

    Ok(())
}

See the Cookbook for more examples.

§Motivation

The rust_xlsxwriter crate was designed and implemented based around the following design considerations:

Fidelity with the Excel file format. The library uses its own XML writer module in order to be as close as possible to the format created by Excel. It also contains a test suite of over 1,000 tests that compare generated files with those created by Excel. This has the advantage that it rarely creates a file that isn’t compatible with Excel, and also that it is easy to debug and maintain because it can be compared with an Excel sample file using a simple diff.
Performance. The library is designed to be as fast and efficient as possible. It also supports a constant memory mode for writing large files, which keeps memory usage to a minimum.
Comprehensive documentation. In addition to the API documentation, the library has extensive user guides, a tutorial, and a cookbook of examples. It also includes images of Excel with the output of most of the example code.
Feature richness. The library supports a wide range of Excel features, including charts, conditional formatting, data validation, rich text, hyperlinks, images, and even sparklines. It also supports new Excel 365 features like dynamic arrays and spill ranges.
Write only. The library only supports writing Excel files, and not reading or modifying them. This allows it to focus on doing one task as comprehensively as possible.
A family of libraries. The rust_xlsxwriter library has sister libraries written in C (libxlsxwriter), Python (XlsxWriter), and Perl (Excel::Writer::XLSX), by the same author. Bug fixes and improvements in one get transferred to the others.
No FAQ section. The Rust implementation seeks to avoid some of the required workarounds and API mistakes of the other language variants. For example, it has a save() function, automatic handling of dynamic functions, a much more transparent Autofilter implementation, and was the first version to have Autofit.

§Performance

As mentioned above the rust_xlsxwriter library has sister libraries written natively in C, Python, and Perl.

A relative performance comparison between the C, Rust, and Python versions is shown below. The Perl performance is similar to the Python library, so it has been omitted.

Library	Relative to C	Relative to Rust
C/libxlsxwriter	1.00
`rust_xlsxwriter`	1.14	1.00
Python/XlsxWriter	4.36	3.81

The C version is the fastest: it is 1.14 times faster than the Rust version and 4.36 times faster than the Python version. The Rust version is 3.81 times faster than the Python version.

See the Performance section for more details.

§Crate Features

The following is a list of the features supported by the rust_xlsxwriter crate.

Default

default: This includes all the standard functionality. The only dependency is the zip crate.

Optional features

These are all off by default.

constant_memory: Keeps memory usage to a minimum when writing large files. See Constant Memory Mode.
serde: Adds support for Serde serialization.
chrono: Adds support for Chrono date/time types to the API. See IntoExcelDateTime.
jiff: Adds support for Jiff date/time types to the API. See IntoExcelDateTime.
zlib: Improves performance of the zlib crate but adds a dependency on zlib and a C compiler. This can be up to 1.5 times faster for large files.
polars: Adds support for mapping between PolarsError and rust_xlsxwriter::XlsxError to make code that handles both types of errors easier to write. See also polars_excel_writer.
wasm: Adds a dependency on js-sys and wasm-bindgen to allow compilation for wasm/JavaScript targets. See also wasm-xlsxwriter.
rust_decimal: Adds support for writing the rust_decimal Decimal type with Worksheet::write(), provided it can be represented by f64.
ryu: Adds a dependency on ryu. This speeds up writing numeric worksheet cells for large data files. It gives a performance boost for more than 300,000 numeric cells and can be up to 30% faster than the default number formatting for 5,000,000 numeric cells.

A rust_xlsxwriter feature can be enabled in your Cargo.toml file as follows:

cargo add rust_xlsxwriter -F constant_memory

Modules§

changelog: Changes and fixes in the rust_xlsxwriter library.
chart: Working with Charts
conditional_format: Working with Conditional Formats
cookbook: A cookbook of example programs using rust_xlsxwriter.
macros: Working with VBA Macros
performance: Performance characteristics of rust_xlsxwriter
serializerserde: Working with Serde
sparkline: Working with Sparklines
tutorial: A getting-started tutorial for rust_xlsxwriter.
utility: Utility functions for rust_xlsxwriter.
workbook: Working with Workbooks
worksheet: Working with Worksheets

Structs§

Button: The Button struct represents a worksheet button object.
CustomProperty: The CustomProperty struct represents data types used in Excel’s custom document properties.
DataValidation: The DataValidation struct represents a data validation in Excel.
DocProperties: The DocProperties struct is used to create an object to represent document metadata properties.
ExcelDateTime: The ExcelDateTime struct is used to represent an Excel date and/or time.
FilterCondition: The FilterCondition struct is used to define autofilter rules.
FilterData: The FilterData struct represents data types used in Excel’s filters.
Format: The Format struct is used to define cell formatting for data in a worksheet.
Formula: The Formula struct is used to define a worksheet formula.
Image: The Image struct is used to create an object to represent an image that can be inserted into a worksheet.
Note: The Note struct represents a worksheet note object.
ProtectionOptions: The ProtectionOptions struct is used to set protected elements in a worksheet.
Shape: The Shape struct represents a worksheet shape object.
ShapeFont: The ShapeFont struct represents the font format for shape objects.
ShapeFormat: The ShapeFormat struct represents formatting for various shape objects.
ShapeGradientFill: The ShapeGradientFill struct represents a gradient fill for a shape element.
ShapeGradientStop: The ShapeGradientStop struct represents a gradient fill data point.
ShapeLine: The ShapeLine struct represents a shape line/border.
ShapePatternFill: The ShapePatternFill struct represents a the pattern fill for a shape element.
ShapeSolidFill: The ShapeSolidFill struct represents a the solid fill for a shape element.
ShapeText: The ShapeText struct represents the text options for a shape element.
Table: The Table struct represents a worksheet table.
TableColumn: The TableColumn struct represents a table column.
Url: The Url struct is used to define a worksheet URL.

Enums§

Color: The Color enum defines Excel colors that can be used throughout the rust_xlsxwriter APIs.
DataValidationErrorStyle: The DataValidationErrorStyle enum defines the type of error dialog that is shown when there is and error in a data validation.
DataValidationRule: The DataValidationRule enum defines the data validation rule for DataValidation.
FilterCriteria: The FilterCriteria enum defines logical filter criteria used in an autofilter.
FormatAlign: The FormatAlign enum defines the vertical and horizontal alignment properties of a Format.
FormatBorder: The FormatBorder enum defines the Excel border types that can be added to a Format pattern.
FormatDiagonalBorder: The FormatDiagonalBorder enum defines Format diagonal border types.
FormatPattern: The FormatPattern enum defines the Excel pattern types that can be added to a Format.
FormatScript: The FormatScript enum defines the Format font superscript and subscript properties.
FormatUnderline: The FormatUnderline enum defines the font underline type in a Format.
HeaderImagePosition: The HeaderImagePosition enum defines the image position in a header or footer.
ObjectMovement: The ObjectMovement enum defines the movement of worksheet objects such as images and charts.
ShapeGradientFillType: The ShapeGradientFillType enum defines the gradient types of a ShapeGradientFill.
ShapeLineDashType: The ShapeLineDashType enum defines the Shape line dash types.
ShapePatternFillType: The ShapePatternFillType enum defines the Shape pattern fill types.
ShapeTextDirection: The ShapeTextDirection enum defines the text direction for Shape text.
ShapeTextHorizontalAlignment: The ShapeTextHorizontalAlignment enum defines the horizontal alignment for Shape text.
ShapeTextVerticalAlignment: The ShapeTextVerticalAlignment enum defines the vertical alignment for Shape text.
TableFunction: The TableFunction enum defines functions for worksheet table total rows.
TableStyle: The TableStyle enum defines the worksheet table styles.
XlsxError: The XlsxError enum defines the error values for the rust_xlsxwriter library.

Traits§

IntoCustomDateTime: Trait to map user date types to an Excel custom property date.
IntoCustomProperty: Trait to map different Rust types into Excel data types used in custom document properties.
IntoDataValidationValue: Trait to map rust types into data validation types
IntoExcelDateTime: Trait to map user date/time types to an Excel serial datetime.
IntoFilterData: Trait to map different Rust types into Excel data types used in filters.
IntoShapeFormat: Trait to map types into a ShapeFormat.

Derive Macros§

XlsxSerializeserde: The XlsxSerialize derived trait is used in conjunction with rust_xlsxwriter serialization.