Column doesn't auto size expectedly in XLSX if values are too short

[cmontgomery-innovent]

BIRT 4.20

I have some tables with columns that have a long header label, but where the actual values are short (2-3 characters). I'm trying to get these columns to shrink to a reasonable size for those short values.

In the screenshot below, notice that A,B,C have sizing dependent on the header length, and columns D,E,F are consistently sized based on the column values. I'm trying to achieve something closer to D,E,F. Is this expected?

ColumnSizing.rptdesign.txt

I can improve it by forcing line breaks in the table headers, but have been trying to find a way to just let Excel handle the word-wrap on the headers as needed.

[speckyspooky]

May be you can try the following 2 options for excel:

The documentation of it is given at REDME.MD-file: https://github.com/eclipse-birt/birt/blob/master/engine/uk.co.spudsoft.birt.emitters.excel/src/uk/co/spudsoft/birt/emitters/excel/README.md

[speckyspooky]

What should be the result?
All columns should have the width according to the header cell content?

The widh-caclulation is very specific at excel an dependent at many points like font family and font size. Therefore there is no ideal solution at the end. This is the reason why I work with fixed column width and not with auto-calculation or recalculation.

[cmontgomery-innovent]

Those two options didn't work.

I'm working on upgrading from 4.4.2, which is setting columns to a minimum width for short values. This is much more manageable in cases of large numbers of columns with short values.
From 4.4.2:

I'm trying to maintain this look, without forcing the column width.
A,B,C are 80px
D,E,F resized to 97px

My general expectation is that with header/footer width calculation disabled (default), then only the values in the column should impact the width. It's understandable if there's a minimum size limit to that, but in case of hitting that minimum, I would expect the minimum allowable width to be set (e.g. 80px).

[hvbtup]

I don't like an automatic minimum width for short values.
And even more, I don't like the emitter-specific options for automatic width calculation.

We don't have auto column-width calculation in other emitters, so we should the Excel emitter support it?
In particular, for Excel output users can change the column width as they like afterwards, wich is not possible for the PDF emitter.

Automatic column width calculation is a very complex task.
And it is very subjective what the best width is.

I think I'm qualified to say this because I contributed the "LongTable optimization" to the ReportLab PDF library decades ago.

As a tool for user-defined column width calculation, it would help if BIRT offered a way to calculate the absolute width of a given text from script, something like calcTextWidth("pt", "This is a header", "Arial", "10pt", isBold, isItalics) which should return the text width in the specified units.

[cmontgomery-innovent]

I've at least found an explanation for the change in behavior, which is the calling of state.currentSheet.autoSizeColumn(col, true); during width calculations.

birt/engine/uk.co.spudsoft.birt.emitters.excel/src/uk/co/spudsoft/birt/emitters/excel/handlers/AbstractRealTableHandler.java

Lines 187 to 207 in c5f8722

	int columnWidth = state.currentSheet.getColumnWidth(col);
	if (forceAutoColWidths || columnWidth == defaultColumnWidth) {
	FilteredSheet filteredSheet = new FilteredSheet(state.currentSheet, autoWidthStartRow,
	Math.min(autoWidthEndRow, autoWidthStartRow + 12));
	double calcWidth = SheetUtil.getColumnWidth(filteredSheet, col, false);
	if (state.currentSheet instanceof XSSFSheet) {
	state.currentSheet.autoSizeColumn(col, true);
	}
	if (calcWidth > 1.0) {
	calcWidth *= 1.15 * 256; // The factor 1.15 is used to handle width differences of Apache POI.
	int maxColumnWidth = 255 * 256; // The maximum column width for an individual cell is 255
	// characters
	if (calcWidth > maxColumnWidth) {
	calcWidth = maxColumnWidth;
	}
	if (calcWidth > columnWidth) {
	state.currentSheet.setColumnWidth(col, (int) calcWidth);
	}
	}
	}

This is at odds with the ignore table header/footer properties, because autoSizeColumn does not size columns based on the subset of rows, like SheetUtil.getColumnWidth(filteredSheet, col, false); does. So we are first getting calcWidth excluding the header, then we call autoSizeColumn which calculates width including the header, and then we only override the width set by autoSizeColumn if calcWidth is larger than the default cell width.

If the width of the values is less than the default cell width, it sizes it based on the entire column including header/footer regardless of emitter setting. If the width of the values is larger than the default cell width, it properly ignores the header/footer.

[cmontgomery-innovent]

Is there a reason we've started using autoSizeColumn? Is it more performant than SheetUtil.getColumnWidth?

I have another issue tracing back to autoSizeColumn resulting in cells that are slightly too narrow for the text and causing overflow.

[speckyspooky]

Then may be it would make sense that you open a request that an option should be implemented/property "autoSizeColumnWithoutHeader" / "autoSizeColumnWithoutFooter".
If somebody has time a small research can be done if we have at this position the option to exclude header & footer.

The overflow of the text is exactly what Henning means, it is very special to get the correct width based at font-family and font-style.

[cmontgomery-innovent]

We currently have "excelAutoColWidthsIncludeTableHeader" and "excelAutoColWidthsIncludeTableFooter". But in effect, these properties get ignored if the calculated width of the column values is less than defaultColumnWidth.

Despite these properties existing and being set to false, it's not actually possible to build an auto sized table that reliably ignores headers for column sizing.

In my opinion, we should use the autoSizeColumn method in a way that respects these existing properties. Or we stick with the SheetUtil.getColumnWidth method, that already is respecting these properties. It's still not very clear to me why we're using both methods.

I do understand the width calculation is a complex process, but SheetUtil.getColumnWidth seems to be producing a width that sizes well for the text that it's given, likely thanks to the 1.15 factor that's tacked onto it. We don't do that with the autoSizeColumn result. Unless there's a specific reason for using autoSizeColumn, I feel we'd have more consistent behavior by relying just on the result from SheetUtil.getColumnWidth.

[cmontgomery-innovent]

Resolved with #2257