Szilárd Herczeg

← All tools

Excel column widths in LibreOffice Calc

If you generate .xlsx files with NPOI, Apache POI or openpyxl and check them in LibreOffice, you've hit this: Calc shows column widths in centimetres, Excel in characters. This extension shows the Excel number, live, and lets you set it.

Download ExcelColumnWidth.oxt View on GitHub

What it shows

A small panel follows your selection and updates as you resize columns:

┌ Excel column width ──────────────────┐
│ C                                    │
│ Excel 8.43   NPOI 2340   64 px       │
│                                      │
│ Set Excel: [ 8.43 ] [Apply] [Refresh]│
└──────────────────────────────────────┘
FieldMeaning
ExcelThe number Excel shows in Format → Column Width (characters)
NPOIThe value for sheet.SetColumnWidth(col, n), in 1/256 of a character
pxWidth in pixels at 96 DPI

Select several columns to see each width. To resize, type an Excel width (8.43 or 8,43) in Set Excel and press Enter.

Install

  1. Download ExcelColumnWidth.oxt from the latest release. Keep that exact file name.
  2. In LibreOffice open Tools → Extension Manager → Add… and pick the file, or with LibreOffice closed: 
    unopkg add ExcelColumnWidth.oxt
  3. Restart LibreOffice.

On Ubuntu and Debian, Python macros need an extra package: sudo apt install libreoffice-script-provider-python

Open the panel with Ctrl+Shift+W, from the Extension tab, or from Tools → Add-Ons → Excel Column Width.

Why SetColumnWidth(col, 20 * 256) isn't 20 in Excel

Excel measures width in characters of the default font, based on its widest digit: 7 px for Calibri 11pt. It also adds 5 px of padding in the UI. So:

Excel (UI) = (px − 5) / 7
NPOI value = trunc(px * 256 / 7)

That's why the default column is 8.43 in Excel but 2340 in NPOI, and why 20 * 256 shows up as about 19.29. The extension does this math for you.

The 7 px digit width is right for Calibri 11pt and Carlito. For another default font, change MDW_PX in src/python/excel_width.py.