rust_xlsxwriter Roadmap

Question

rust_xlsxwriter Roadmap

jmcnamara opened this issue 2 years ago · 23 comments

Answer 1 · 2022-09-24T20:23:25.000Z

Initial performance data:

$ hyperfine target/release/examples/app_perf_test ./c_perf_test "python py_perf_test.py"
Benchmark 1: target/release/examples/app_perf_test
  Time (mean ± σ):     447.2 ms ±   8.3 ms    [User: 402.8 ms, System: 39.4 ms]
  Range (min … max):   431.5 ms … 460.7 ms    10 runs

Benchmark 2: ./c_perf_test
  Time (mean ± σ):     362.5 ms ±   6.4 ms    [User: 305.2 ms, System: 53.0 ms]
  Range (min … max):   353.2 ms … 371.9 ms    10 runs

Benchmark 3: python py_perf_test.py
  Time (mean ± σ):      2.899 s ±  0.023 s    [User: 2.787 s, System: 0.088 s]
  Range (min … max):    2.868 s …  2.934 s    10 runs

Summary
  './c_perf_test' ran
    1.23 ± 0.03 times faster than 'target/release/examples/app_perf_test'
    8.00 ± 0.16 times faster than 'python py_perf_test.py'

Or in other words, the C version is the fastest and if we take that as 1 then the rust version is 1.2x (or 20%) slower and the Python version is 8x slower.

The Rust version is ~6.5x faster than the Python version.

$ hyperfine target/release/examples/app_perf_test "python py_perf_test.py"
Benchmark 1: target/release/examples/app_perf_test
  Time (mean ± σ):     450.8 ms ±   5.5 ms    [User: 406.8 ms, System: 39.1 ms]
  Range (min … max):   443.5 ms … 459.1 ms    10 runs

Benchmark 2: python py_perf_test.py
  Time (mean ± σ):      2.942 s ±  0.040 s    [User: 2.821 s, System: 0.090 s]
  Range (min … max):    2.877 s …  3.014 s    10 runs

Summary
  'target/release/examples/app_perf_test' ran
    6.53 ± 0.12 times faster than 'python py_perf_test.py'

Answer 2 · 2023-01-11T06:19:55.000Z

Is polars support planned? I saw the python xlsxwriter have pandas support, I wonder if the rust xlsxwriter have any plans to support polars.

Answer 3 · 2023-01-11T09:24:50.000Z

I wonder if the rust xlsxwriter have any plans to support polars.

Good suggestion. That was something that I was thinking about. I wrote the initial xlsxwriter integration into Pandas. I'll have a look in their GitHub issues/requests and see if there is any planned work.

Answer 4 · 2023-04-14T21:52:43.000Z

Is it possible to use rayon or std thread scope (parallelism) to do each worksheet and at the end add in the final workbook?

This code was just a vain attempt:
https://github.com/claudiofsr/rust-sped/blob/master/src/excel_alternative.rs

Answer 5 · 2023-04-14T22:10:35.000Z

Is it possible to use rayon or std:: thread ::scope (parallelism) to do each worksheet and at the end add in the final workbook?

It wouldn't be easy. I've thought a good bit about this in the past in relation to the other language version of the library. The main issue is that the xlsx file format has a lot of interlinked "relationships" stored in .rel files. Worksheet strings are also stored in a shared hash table and referenced by id. These, more or less, need to be worked out sequentially and/or with some locking.

However, I would like the library to have the best performance possible (within the limits of the design and file format) so I'll take a look at what can be done.

Update: some backend parallelism was added in v0.44.0

Answer 6 · 2023-05-12T12:57:14.000Z

Hi and thanks for the library! This is a really aweswome upgrade to the previous binding to the C lib.
Just wanting to drop I would be really happy to see the tables functionality added. Would love to use that.

Answer 7 · 2023-05-12T13:22:29.000Z

I would be really happy to see the tables functionality added

That will be the next major feature after I complete more of the chart feature.

Answer 8 · 2023-05-26T23:46:56.000Z

@Christoph-AK see #41 for initial table support.

Update: completed in v0.40.0

Answer 9 · 2023-07-23T06:32:21.000Z

Hi.. Thanks for the library!! I was in need of an active excel writer library in rust for my new project and this project seems to be most active and promising.

My project may depend on a lot of existing excel templates. I understand from the readme that currently editing an existing excel file is not supported. Any chances that this could be added in the future?

Answer 10 · 2023-07-23T20:16:50.000Z

Any chances that this could be added in the future?

Unfortunately no, I don’t plan to tackle reading or rewriting XLSX files.

The XLSX structure is difficult to parse and rewrite for anything beyond basic data (and even that it can be hard for elements like dates).

Instead I’m going to concentrate my efforts to try give Rust a best in class XLSX writing library.

For additional context here is a reply that I gave to a similar request to the Python version of the library: jmcnamara/XlsxWriter#653 (comment)

Hopefully someone will step up at some point to combine one of the Rust XLSX readers with rust_xlsxwriter for a templating/rewriting solution.

Answer 11 · 2023-08-20T20:09:50.000Z

I've uploaded a new crate called polars_excel_writer for serializing Polars dataframes into Excel Xlsx files using rust_xlsxwriter as a backend engine.

It provides two interfaces for writing a Polars Rust dataframe to an Excel Xlsx file:

ExcelWriter a simple Excel serializer that implements the Polars SerWriter trait to write a dataframe to an Excel Xlsx file. This is similar to the CsvWriter interface.
PolarsXlsxWriter a more configurable Excel serializer that resembles the interface options provided by the Polars Python write_excel() dataframe method. There is still work in progress for this interface.

One useful feature of PolarsXlsxWriter is that you can mix Polars and rust_xlsxwriter code to access Excel features not available in the current interface.

Answer 12 · 2023-11-21T21:20:46.000Z

I've added support for Conditional Formatting. See Working with Conditional Formats in the docs. #58

Answer 13 · 2023-12-09T09:52:40.000Z

I have added support for Serde serialization in v0.57.0. See Working with Serde in the rust_xlsxwriter docs and the discussion thread #61.

Some additional serialisation features and helpers will be added in upcoming releases.

Answer 14 · 2024-02-01T12:03:39.000Z

Do you have any plans regarding reaching version 1.0? You seem to make a new 0.x release every week or so. Many of them don't have any breaking changes, but still require me to manually bump the version in Cargo.toml to make sure that I don't miss any new bug fixes. It would be nice to actually utilize semantic versioning and indicate non-breaking releases with 1.x bumps. This way, my app would be able to depend on the major version and automatically receive library updates. Note that I'm not asking for any new stability commitments, you can release 2.0 as soon as you want to make a change. This model would already be more comfortable than a breaking bump on every release.

In theory, I assume that major numbers would also make the life easier for library authors who depend on rust_xlsxwriter. Currently, they have to either pin it to a very narrow minor version or define a range with an explicit upper bound and bump it every week.

Answer 15 · 2024-02-01T12:05:55.000Z

Obligatory mention of https://github.com/obi1kenobi/cargo-semver-checks - might be worth including.

Answer 16 · 2024-02-01T13:14:58.000Z

Do you have any plans regarding reaching version 1.0?

I plan to release a 1.x.x version once the feature set is ~ 100% of the Python feature set. Based on the task list above the current feature set is 25/33 features and based on ported integration test cases it is ~ 700/900. I would hope to get to 1.0.0 by the end of the year. Some of the remaining tasks are reasonable big though.

You seem to make a new 0.x release every week or so.

That will probably continue through this year (with an upcoming pause of 1-2 months while I work on some of the other language libraries/features).

Many of them don't have any breaking changes, but still require me to manually bump the version in Cargo.toml to make sure that I don't miss any new bug fixes.

Yes. Some, or many, of those could be patch level releases but most contain a reasonable level of new functionality.

The semver docs say:

Minor version Y (x.Y.z | x > 0) MUST be incremented if new, backward compatible functionality is introduced to the public API. It MUST be incremented if any public API functionality is marked as deprecated. It MAY be incremented if substantial new functionality or improvements are introduced within the private code. It MAY include patch level changes. Patch version MUST be reset to 0 when minor version is incremented.

I am usually in the "MAY" category and sometimes in the "MUST".

Note that I'm not asking for any new stability commitments, you can release 2.0 as soon as you want to make a change. This model would already be more comfortable than a breaking bump on every release.

I think I would end up incrementing a large number of major versions as well. I don't know if that would be better or worse for the end user.

Anyway, overall I think you (and others) will just need to bear with me for the next year or so. The downside is that there will be several more bumps in minor versions but the (hopefully) upside is that there will be new substantive features added on a regular basis.

Answer 17 · 2024-02-26T08:41:30.000Z

I have released rust_xlsxwriter v0.63.0 with support for embedding images into worksheets. See the Embedded Images example in the docs.

This can be useful if you are building up a spreadsheet of products with a column of images of each product. Embedded images move with the cell so they can be used in worksheet tables or data ranges that will be sorted or filtered.

This functionality is the equivalent of Excel's menu option to insert an image using the option to "Place in Cell" which is available in Excel 365 versions from 2023 onwards. I was a frequently requested feature for Excel and for the xlsxwriter variants.

Answer 18 · 2024-03-19T08:04:17.000Z

I have released rust_xlsxwriter v0.64.0 with support for sparklines. See the Working with Sparklines section in the docs.

This is a somewhat niche feature and as far as I can tell not widely used in the Python version. However, it is needed for compatibility with Polars which is currently one of my priorities.

Answer 19 · 2024-05-08T17:39:28.000Z

A +1 for low memory mode, in case such feedback is useful :)

Answer 20 · 2024-05-08T18:26:57.000Z

A +1 for low memory mode, in case such feedback is useful :)

It is useful. :-)

Answer 21 · 2024-07-14T12:39:28.000Z

I've added support for Excel data validations to rust_xlsxwriter v0.70.0.

Data validation is a feature of Excel that allows you to restrict the data that a user enters in a cell and to display associated help and warning messages. It also allows you to restrict input to values in a dropdown list.

Here is an example:

use rust_xlsxwriter::{DataValidation, DataValidationRule, Workbook, XlsxError};

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

    worksheet.write(1, 0, "Enter rating in cell D2:")?;

    let data_validation = DataValidation::new()
        .allow_whole_number(DataValidationRule::Between(1, 5))
        .set_input_title("Enter a star rating!")?
        .set_input_message("Enter rating 1-5.\nWhole numbers only.")?
        .set_error_title("Value outside allowed range")?
        .set_error_message("The input value must be an integer in the range 1-5.")?;

    worksheet.add_data_validation(1, 3, 1, 3, &data_validation)?;

    // Save the file.
    workbook.save("data_validation.xlsx")?;

    Ok(())
}

Output:

See DataValidation for details.

Answer 22 · 2024-07-19T23:36:21.000Z

I've added support for adding VBA Macros to rust_xlsxwriter using files extracted from Excel files. This isn't very useful and it is also a little kludgy but it is a reasonably popular feature of the Python library and it has utility in some circumstance.

Also, this lays some of the groundwork for adding cell comments (now called Notes by Excel).

Explanation

An Excel xlsm file is structurally the same as an xlsx file except that it contains an additional vbaProject.bin binary file containing VBA functions and/or macros.

Unlike other components of an xlsx/xlsm file this data isn't stored in an XML format. Instead the functions and macros as stored as a pre-parsed binary format. As such it wouldn't be feasible to programmatically define macros and create a vbaProject.bin file from scratch (at least not in the remaining lifespan and interest levels of the author).

Instead, as a workaround, the Rust vba_extract utility is used to extract vbaProject.bin files from existing xlsm files which can then be added to rust_xlsxwriter files.

See Working with VBA Macros.

Here is an example:

use rust_xlsxwriter::{Button, Workbook, XlsxError};

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

    // Add the VBA macro file.
    workbook.add_vba_project("examples/vbaProject.bin")?;

    // Add a worksheet and some text.
    let worksheet = workbook.add_worksheet();

    // Widen the first column for clarity.
    worksheet.set_column_width(0, 30)?;

    worksheet.write(2, 0, "Press the button to say hello:")?;

    // Add a button tied to a macro in the VBA project.
    let button = Button::new()
        .set_caption("Press Me")
        .set_macro("say_hello")
        .set_width(80)
        .set_height(30);

    worksheet.insert_button(2, 1, &button)?;

    // Save the file to disk. Note the `.xlsm` extension. This is required by
    // Excel or it raise a warning.
    workbook.save("macros.xlsm")?;

    Ok(())
}

Output:

Answer 23 · 2024-07-26T09:28:31.000Z

I've added support for cell Notes (previously called Comments) in v0.72.0.

See https://docs.rs/rust_xlsxwriter/latest/rust_xlsxwriter/struct.Note.html

Here is an example:

use rust_xlsxwriter::{Note, Workbook, XlsxError};

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

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

    // Widen the first column for clarity.
    worksheet.set_column_width(0, 16)?;

    // Write some data.
    let party_items = [
        "Invitations",
        "Doors",
        "Flowers",
        "Champagne",
        "Menu",
        "Peter",
    ];
    worksheet.write_column(0, 0, party_items)?;

    // Create a new worksheet Note.
    let note = Note::new("I will get the flowers myself").set_author("Clarissa Dalloway");

    // Add the note to a cell.
    worksheet.insert_note(2, 0, &note)?;

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

    Ok(())
}

And the output:

I didn't port some of the Python Note/Comment features such as note positioning since they weren't widely used and Excel's implementation tends to surprise people. If people ask of them I'll add them. The infrastructure is already in place.

Note, in versions of Excel prior to Office 365 Notes were referred to as "Comments". The name Comment is now used for a newer style threaded comment and Note is used for the older non threaded version. The newer Threaded Comments are unlikely to be added to rust_xlsxwriter due to fact that it relies on company specific metadata to identify the comment author.

As an aside the internal traits that I had put in place for other worksheet objects (images, charts, buttons) made this feature relatively easy to add. I really like this aspect of Rust where some of the abstractions can give very clean and easy to maintain/refactor code. Overall I really enjoy using Rust as a language.

rust_xlsxwriter Roadmap

Phase 1: Basic functionality

Phase 2: Medium functionality

Phase 3: Larger functionality

Initial performance data:

Explanation