Autosize of Columns not working with Excel365

[ScomataoPABA]

Hi,
recently i have updated the source codes. Since then method get_row_dimension() of class zcl_excel_worksheet has been deprecated.
I used this method to set auto size for certain columns.
Now i have to use the auto_size method of class zcl_excel_column. Unfortunately the columns are not sized in Excel 365 anymore.
Also the columns in files generated with demo reports ZDEMO_EXCEL30 and ZDEMO_EXCEL31 are not autosized (Autofit in Excel) any more. Is is any solution or workaround available?
31_AutosizeWithDifferentFontSizes.xlsx
BR

[SciLor]

I can replicate that. Is there any workaround?

[gregorwolf]

We're open for pull requests that correct this issue.

[SciLor]

I have just analyzed my result generated xlsx and it is a mistake in my code.
But I found a difference in the xlsx from office 365 and the one generated by abap2xlsx.
office365 sets true values to "1" while abap2xlsx to "true".
ZDEMO_EXCEL30 works fine for me so it would be important that the creator of the issue would provide the generated xlsx to check it.

ZDEMO_EXCEL31 only works partly for me. For the last 2-3 rows the columns are to short. The height looks allright.

[ScomataoPABA]

Attached you may find the output of program ZDEMO_EXCEL30:
30_CellDataTypes.xlsx
Thx

[rmulig]

I was doing some research on this issue and discovered this forum. I am watching this for updates hoping we can get this feature working again.

[AndreaBorgia-Abo]

I have the same issue with demo 30 and 31 on Excel 2013: the columns are autosized only when double-clicking on the separators in the header. Both with embedded Excel and saved file. Tested with demo 31 on Excel 2013 on Windows and 16.16.27 (201012) on Mac.
EDIT: I'm not entirely clear if this is a bug at all

[dbausch85]

I think ZCL_EXCEL_WRITER_2007->CREATE_XL_SHEET (Row 461) is wrong.

IF NOT lo_column_iterator IS BOUND

should be changed to:

IF lo_column_iterator IS BOUND

Same game for ZCL_EXCEL_WRITER_HUGE_FILE (Row 306).

[AndreaBorgia-Abo]

@dbausch85 why would it be wrong? Granted, I did not debug this particular bit of code but to me it looks like it first attempts to bind an existing iterator and, when it fails, a new one is created.

[gregorwolf]

Was fixed with #744

[Kidokann]

I think ZCL_EXCEL_WRITER_2007->CREATE_XL_SHEET (Row 461) is wrong.

IF NOT lo_column_iterator IS BOUND

should be changed to:

IF lo_column_iterator IS BOUND

Same game for ZCL_EXCEL_WRITER_HUGE_FILE (Row 306).

I have debbuged this case on my side, having the same behaviour. You are totally right. This should be the code change applied in ZCL_EXCEL_WRITER_2007->CREATE_XL_SHEET

" Calculate col
IF NOT lo_column_iterator IS BOUND.
lo_column_iterator = io_worksheet->get_columns_iterator( ).
ENDIF.
io_worksheet->calculate_column_widths( ).

Same for ZCL_EXCEL_WRITER_HUGE_FILE.
No other suggested patches work.

[sandraros]

Just a few facts to assert this issue is not an issue:

• Only this can make auto-size the columns: lo_worksheet->calculate_column_widths( ). (preceded with set_auto_size for concerned columns).
• Consequently, it won't work for ZDEMO_EXCEL30, but it will work for ZDEMO_EXCEL31 (https://github.com/abap2xlsx/demos/blob/dac8e0934d3d43221eede34483637f08e1fff8d5/src/zdemo_excel31.prog.abap#L58-L64):

• Note that '1' and 'true' are both accepted and equivalent (I tested).

The logic of calculate_column_widths for calculating the width of columns is based on Font Metrics loaded in the transaction code SE73. So, if something doesn't work well, it's linked to the Font Metrics.

I think it's the best we can do, as far as I know.

If anyone can find evidence of a feature in Excel to auto-size the columns, please let us know.

Also, please open a new ticket only if you have additional arguments.

[raphaels]

I did it this way for auto sizing all columns from fieldcatalog:

DATA(lv_columns) = lines( lt_field_catalog ) + 1.
WHILE lv_columns > 1.
column_dimension = lo_worksheet->get_column_dimension( ip_column = lv_columns ). "make date field a bit wider
column_dimension->set_auto_size( abap_true ).
lv_columns = lv_columns - 1.
ENDWHILE.

[AiolosYang]

get_column_dimension is out of date.
how can you set auto size for all columns? the only way is loop all columns ?

[sandraros]

@AiolosYang See my answer above (June 22nd, 2022).