Bug: autofit too small on datetime columns with custom format

[Christoph-AK]

Current behavior

Expected behavior

Sample code to reproduce

    let mut xls = Workbook::new();
    let sheet1 = xls.add_worksheet();
    sheet1.write(0, 0, "date")?;

    let format = Format::new().set_num_format("dd\\.mm\\.yyyy hh:mm:ss");

    let now = SystemTime::now();
    let now: DateTime<Utc> = now.into();
    let now = now.naive_local();

    sheet1.write_with_format(1, 0, &now, &format)?;

    sheet1.set_freeze_panes(1, 0)?;
    sheet1.autofit();
    xls.save(filename)?;

Environment

on latest main

[jmcnamara]

This is a limitation more than a bug. rust_xlsxwriter doesn't currently try to apply number formatting when calculating the autofit width. A future version will try to include it in the calculation but even then it will be approximate.

The docs need to call this out more clearly so that I can at least say it is a "known" limitation.

In the meantime you should apply a fixed width based on the width of the custom format to the date columns using worksheet.set_column_width() or worksheet.set_column_width_pixels(). The autofit() won't override that unless the calculated width is bigger.

P.S., I see that you are already using the Table feature.

[Christoph-AK]

Thanks for looking into this!

This might be a language/region thing, but if I remember correctly the date doesn't get displayed correctly even without a custom formatting in de-de. For now I'm just setting the width to 20 manually, works ok.

Yea, living on the edge :D Love the table feature so far, was just a little bit disappointed that exisiting table header text fields get replaced with 'ColumnX' instead of getting converted to table headers automagically, but I can totally work around that :)

[jmcnamara]

was just a little bit disappointed that exisiting table header text fields get replaced with 'ColumnX' instead of getting converted to table headers automagically

Could you explain that a bit. Did you have headers already written and then expected them to be in the table when you inserted it. Something like that?

[Christoph-AK]

Yes! Would love to be able to do

    sheet1.write(0, 0, "header")?;
    sheet1.write(1, 0, "value1")?;
    sheet1.write(2, 0, "value2")?;

    sheet1.write(0, 1, "header2")?;
    sheet1.write(1, 1, "value3")?;
    sheet1.write(2, 1, "value4")?;

    sheet1.add_table(0, 0, 2, 1, &Table::new())?;

instead of

    sheet1.write(1, 0, "value1")?;
    sheet1.write(2, 0, "value2")?;
    sheet1.write(1, 1, "value3")?;
    sheet1.write(2, 1, "value4")?;

    let mut table = Table::new();
    table.set_columns(&vec![
        TableColumn::new().set_header("header1"),
        TableColumn::new().set_header("header2")
    ]);

    sheet1.add_table(0, 0, 2, 1, &table)?;

[jmcnamara]

Would love to be able to do

That is not a bad idea. I'll look into it. Not for the first table release but maybe the subsequent one.

[jmcnamara]

Closing this issue as won't fix (for now). I've improved the documentation around this feature: https://docs.rs/rust_xlsxwriter/latest/rust_xlsxwriter/struct.Worksheet.html#method.autofit

[jmcnamara]

was just a little bit disappointed that exisiting table header text fields get replaced with 'ColumnX' instead of getting converted to table headers automagically

This feature is now available on the rust_xlsxwriter main and should be available in v0.43.0 in the next 1-2 days:

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

fn main() -> Result<(), XlsxError> {
    let mut workbook = Workbook::new();
    let sheet1 = workbook.add_worksheet();

    sheet1.write(0, 0, "header")?;
    sheet1.write(1, 0, "value1")?;
    sheet1.write(2, 0, "value2")?;

    sheet1.write(0, 1, "header2")?;
    sheet1.write(1, 1, "value3")?;
    sheet1.write(2, 1, "value4")?;

    sheet1.add_table(0, 0, 2, 1, &Table::new())?;

    sheet1.autofit();

    workbook.save("gh42.xlsx")?;

    Ok(())
}

Output:

[Christoph-AK]

Woa, brilliant. Thanks alot!

[jmcnamara]

This secondary feature request for automatic headers is now available in rust_xlsxwriter v0.43.0. Thanks for the prompt.