Excel export auto fit column width not working as expected

Excel export auto fit column width not working as expected

chocchoc Posts: 100Questions: 12Answers: 8

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?

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    edited November 27 Answer ✓

    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

  • chocchoc Posts: 100Questions: 12Answers: 8

    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.

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

  • chocchoc Posts: 100Questions: 12Answers: 8

    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.

Sign In or Register to comment.