Excel export auto fit column width not working as expected

choc

Link to test case: https://datatables.net/extensions/buttons/examples/initialisation/export.html
Description of problem:

From the document (https://datatables.net/reference/button/excelHtml5), it says that the default styling will auto sized to fit their contents mentioned in https://datatables.net/reference/button/excelHtml5#Customisation

Column widths are auto sized to fit their contents (min: 5, max: 52)

(also it seems that the document is outdated? From the source code, it says the min value is 6 and max value is 54)

But it seems that the column width is always wider than the auto-fit width of Excel. To reproduce this, export the Excel file from the example link above and open it with Excel.

After clicking Cells->Format->AutoFit Column Width

Is this by design or a bug?

allan

also it seems that the document is outdated?

Thank you. Fix committed and will be deploed soon.

Is this by design or a bug?

Kind of by design. The auto column width is an estimation based on the string length. Since the font is variable character width and employs kerning, it is always going to be an estimation. For example 'mmmmm' is much wider than 'iiiiii' despite having fewer characters.

If there is a better way of doing it I would be interested. Once upon a time I did write values into the DOM to get their exact width, but goodness me it was slow. That was backed out a patch version later.

Allan

choc

Thank you Allan for the explanation! That’s indeed tricky to calculate the width than I thought.

Found an answer from https://stackoverflow.com/a/48172630/6836931 that uses DOM/Canvas, which you might be interested in.

allan

That's a clever idea. I think it will have a significant impact on performance of large exports, but it might be worth considering under a "exactColumnWidth" option in future.

Allan

choc

Sorry for the typo

Found an answer from https://stackoverflow.com/a/48172630/6836931 that uses DOM/Canvas, which you might be interested in.

it doesn't use DOM/Canvas.

Yes. It's fine to use the estimated column width in normal case. I noticed this problem because I have a table where all the column headers contain a very long text that all column width accumulates into a very wide view in Excel. But that's fine, because Excel settings can vary (like font, fontsize, etc) and we can always use the built-in AutoFit Column Width function.