Read Cell Format (e.g. column width)

[mschnell1]

With version 0.4 I was able to read the cell content (as a string) After setting fill and fg_color and bg_color in formats.rs and color.rs to general pub, I do get some seemingly sensible value for the foreground color (as RGB as a String ) . 👍 👍 👍 To display the table or convert it in some other file format I need the column width (row height would obviously also be of interest) I found that the cols vector does not yet get filled with data from the xlsx file and hence the column widths are unknown. (Of course to be usable the appropriate values need to get published by pub declarations or get_..() functions. )

[MortalreminderPT]

Adding the feature to read Cols is a significant task, involving rewriting the storage structure and updating the APIs. It may take considerable time. Luckily, I've found it closely related to issue #6, potentially allowing simultaneous resolution.

[mschnell1]

Do you plan this for v0.4 ? (I am eager to test...)

[mschnell1]

Just to let you know: I checked that in your example, get_column_width((1, 1, 1, 16384)) does show the column widths. In my example I get an empty vector.

[MortalreminderPT]

Just to let you know: I checked that in your example, get_column_width((1, 1, 1, 16384)) does show the column widths. In my example I get an empty vector.

I can see your confusion because I didn't elaborate on the method. In fact, get_column_width() will only get the modified width, it will not return the default width

[mschnell1]

Hmm. I simply tested

    let widths = reading_sheet.get_column_width((1, 1, 1, 16384))?;
    println!("{:?}", widths);

i.e. the unmodified sheet, in the original example. And this does work.

Yep. It's rather weird that modified values should be seen while "original" values from the xlsx file are not. I suppose a final version will unify that to allow for consistent reading and updating xlxs tables, hiding the supposed complexity of updating (such as widthvs custom_width ?) Thanks for providing this crate !

[MortalreminderPT]

Hmm. I simply tested

    let widths = reading_sheet.get_column_width((1, 1, 1, 16384))?;
    println!("{:?}", widths);

i.e. the unmodified sheet, in the original example. And this does work.

Yep. It's rather weird that modified values should be seen while "original" values from the xlsx file are not. I suppose a final version will unify that to allow for consistent reading and updating xlxs tables, hiding the supposed complexity of updating (such as widthvs custom_width ?) Thanks for providing this crate !

Yes, the default width is hidden in office 365 and the default width is 8.11 after my test. Also, thank you for your valuable suggestion, I will carefully consider your suggestion and do my best to design an easy to use api. (In fact, these temporary reading apis are also very rudimentary and ugly in my opinion, but this is necessary for testing.)

[mschnell1]

I am looking forward to testing any intermediate state of the project ... and finally some beautiful final release.

[MortalreminderPT]

I've released version 0.4.1 which provides reading text and styles, you can see the usage in examples/read_and_copy. As for reading default row heights and column widths, I've provided get_default_row and get_default_column methods, which can be used to change the default of them with set_default... methods.

[mschnell1]

I cloned the new main from github and locally modified the read_and-copy example and it does show floating point values for columns width (12.81640625 / 26.81640625 / 12.36328125 ).

I do see the get_default_column() function but same does not seem to provide the current width of any column of an existing shet in an xlsx file.

get_columns_with_format() still gives me an empty vector, while I correctly can see the .max_column() value and the cells' text content.

Edit ..... Maybe I simply did not touch the column width when creating the sheet in Excel ....

Yep this seems to work. For a more "friendly" version maybe it would make sense to fill the vector with the default values, but maybe not ....

[mschnell1]

I now can collect the column widths.

But what exactly is the stringtyped first element in the result of the get_columns_with_format() function ?

in your example this is "A:A" / "C:L" / "B:B" (in this sequence)

I would need the numerical value of the column position. Supposedly I can extract it from the string. Is there any function or documentation to do that ? ( to_col() (I cloned it) did not seem to work for me ?!?! )

In fact a Location or similar type in that place seems more appropriate.

Later.... Ooops. is this a range of columns ???? Is there some function converting same to a Rust range ?

[mschnell1]

Yep. works with Range and to_col()

[MortalreminderPT]

I now can collect the column widths.

But what exactly is the stringtyped first element in the result of the get_columns_with_format() function ?

in your example this is "A:A" / "C:L" / "B:B" (in this sequence)

I would need the numerical value of the column position. Supposedly I can extract it from the string. Is there any function or documentation to do that ? ( to_col() (I cloned it) did not seem to work for me ?!?! )

In fact a Location or similar type in that place seems more appropriate.

Later.... Ooops. is this a range of columns ???? Is there some function converting same to a Rust range ?

I use string as the key of the result map because string can be used as an input into set_columns_xxx methods, also because string is the most intuitive way to see the columns and their corresponding values. I think when people do column processing, they tend to process multiple columns at once, and it also fits the xlsx storage structure. However, if you insist on using this method to get the width of a column individually and want to pass it in as a number, I think it's possible, but not recommended.

for col in 1..16384 {
    let cols = worksheet.get_columns_width((1, col, 1, col))?;
    let customed_col = cols.values().last();
    let width = match customed_col {
        Some(Some(w)) => *w,
        _ => worksheet.get_default_column(),
    };
    println!("{width}");
}

The good news is that this method is not completely unacceptable due to the maximum width limit of xlsx (16384) and the fact that the time complexity of the method (logn) is not very high.

[MortalreminderPT]

Yep. works with Range and to_col()

Yes, this method is meant to do the conversion between string and numeric forms of the column IDs, but it's meant to work only for the project itself (and it does look like you're working on a complex project)

[MortalreminderPT]

I cloned the new main from github and locally modified the read_and-copy example and it does show floating point values for columns width (12.81640625 / 26.81640625 / 12.36328125 ).

I do see the get_default_column() function but same does not seem to provide the current width of any column of an existing shet in an xlsx file.

get_columns_with_format() still gives me an empty vector, while I correctly can see the .max_column() value and the cells' text content.

Edit ..... Maybe I simply did not touch the column width when creating the sheet in Excel ....

Yep this seems to work. For a more "friendly" version maybe it would make sense to fill the vector with the default values, but maybe not ....

My intention is to allow users to modify them without side effects after using get_columns_xxx and easily update them using set_columns_xxx. However, if the default values are forced to be populated, it's hard for the user to tell if these field were modified by them or just populated by default when they make an update. Regarding your comment about default column widths, it seems that when a default column width is not defined in the xlsx file, it is determined by Excel itself. So simply filling in a certain value is not appropriate. I should have thought of that, maybe I should have changed the return value of this method to Option.

---

I reread excelfileformat and focused on the definition of default column widths, which is determined by fonts when they don't exist in the xlsx file, but the documentation doesn't define the method column widths are calculated from fonts

[mschnell1]

In fact with my example, the default column with is perfectly provided by your function. I only did not interpret the result appropriately. Now I get the correct width of all columns in my sheet.