Notes on the XlsxWriter implementation of
Version 3.0.6 of XlsxWriter added a
The implementation is a technical compromise and I wanted to write some notes about it.
First off let's start with the text that was in the FAQ:
Q. Is there an "AutoFit" option for columns?
Unfortunately, there is no way to specify "AutoFit" for a column in the Excel
file format. This feature is only available at runtime from within Excel. It
is possible to simulate "AutoFit" in your application by tracking the maximum
width of the data in the column as your write it and then adjusting the column
width at the end.
This is still true. There is no "autofit" flag in the Excel XLSX format that will trigger the same autofit that you get from Excel at runtime.
As a workaround I implemented a pixel calculation based on defined widths for all the characters in the ASCII range 32-126. You can see that here and here.
This matches Excel for strings up to around 100 pixels (and there are a number of
test_autofit??.py test cases that compare against Excel. After that Excel adds an additional pixel for every ~ 32 pixels additional length. For example in Excel the following strings have these pixel widths:
- "N" : 10
- "NNN": 30
- "NNNNN": 50
- "NNNNNNNN": 80
- "NNNNNNNNNN": 101
This may be due to some internal rounding (in Excel) or may be due to a conversion from character units to pixel widths.
Either way this isn't significant. The additional pixels added by Excel appear as extra padding and won't be substantively noticeable to the end user. At the same time the lack of these extra padding pixels from the XlsxWriter autofit shouldn't be noticeable either. I am mainly highlighting this so that hopefully someone won't submit a bug report about pixel difference between Excel and XlsxWriter.
Here is a visual example from the
autofit.py XlsxWriter program:
And the same file with an Excel autofit:
The pixels widths for the columns are:
| Program | A | B | C | D |
| ---------- | --- | --- | --- | --- |
| XlsxWriter | 50 | 63 | 113 | 147 |
| Excel | 50 | 63 | 114 | 150 |
Difference with Excel for macOS
Excel for Windows files appear differently in Excel for macOS. For example here is the same XlsxWriter file as above on the mac:
You will notice that the widths and padding are rendered differently. This is not an XlsxWriter issue. The same happens with any Excel file generated on Windows and rendered on the mac. For example:
| Program | A | B | C | D |
| ---------------- | --- | --- | --- | --- |
| Excel Win | 50 | 63 | 114 | 150 |
| Excel Win on Mac | 43 | 54 | 98 | 129 |
| Excel Mac | 39 | 55 | 83 | 111 |
This is quite a difference and if the Excel Mac autofit file is transferred back to Windows the columns no longer appear fitted.
So if you encounter this issue it isn't due to XlsxWriter.
Excel adds a 7 pixel padding to each cell. So a word like "Hello" has a width of 33 pixels but the column width (in Excel) will be 33+7=40 pixels.
- Fonts and font sizes aren't taken into account.
- Non-ASCII characters are given a default width of 8 pixels.
- Dates are assumed to have a width/format of
- Number formats aren't taken into account (and realistically won't be in the future).
- Autofilter dropdowns aren't taken into account.
- It is not supported in