Crate rust_xlsxwriter

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

Other external documentation:

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

LibraryRelative to CRelative to Rust
C/libxlsxwriter1.00
rust_xlsxwriter1.141.00
Python/XlsxWriter4.363.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
Changelog
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.