openpyxl – adjust column width size

by

You could estimate (or use a mono width font) to achieve this. Let’s assume data is a nested array like

[['a1','a2'],['b1','b2']]

We can get the max characters in each column. Then set the width to that. Width is exactly the width of a monospace font (if not changing other styles at least). Even if you use a variable width font it is a decent estimation. This will not work with formulas.

from openpyxl.utils import get_column_letter

column_widths = []
for row in data:
    for i, cell in enumerate(row):
        if len(column_widths) > i:
            if len(cell) > column_widths[i]:
                column_widths[i] = len(cell)
        else:
            column_widths += [len(cell)]
    
for i, column_width in enumerate(column_widths,1):  # ,1 to start at 1
    worksheet.column_dimensions[get_column_letter(i)].width = column_width

A bit of a hack but your reports will be more readable.

More Related Contents:

  1. Extract the data and modify an existing excel with openpyxl
  2. How to save a new sheet in an existing excel file, using Pandas?
  3. Read Excel cell value and not the formula computing it -openpyxl
  4. Save list of DataFrames to multisheet Excel spreadsheet
  5. Pandas: Looking up the list of sheets in an excel file
  6. How to access the real value of a cell using the openpyxl module for python
  7. copy cell style openpyxl
  8. Openpyxl 1.8.5: Reading the result of a formula typed in a cell using openpyxl
  9. How to concatenate three excels files xlsx using python?
  10. Password Protecting Excel file using Python
  11. How to copy worksheet from one workbook to another one using openpyxl?
  12. Modify an existing Excel file using Openpyxl in Python
  13. Copy pandas dataframe to excel using openpyxl
  14. How to find the last row in a column using openpyxl normal workbook?
  15. How to save XLSM file with Macro, using openpyxl
  16. python: why does replace not work?
  17. Fill cells with colors using openpyxl?
  18. how to write to a new cell in python using openpyxl
  19. Python openpyxl data_only=True returning None
  20. Insert row into Excel spreadsheet using openpyxl in Python
  21. openpyxl load_workbook() on a legit .xlsx file leads to a zipfile.BadZipFile error
  22. getting the row and column numbers from coordinate value in openpyxl
  23. xlsx and xlsm files return badzipfile: file is not a zip file
  24. Get cell color from .xlsx
  25. How to write a list to Excel column?
  26. No module named ‘openpyxl’ – Python 3.4 – Ubuntu
  27. Is it possible to get an Excel document’s row count without loading the entire document into memory?
  28. Openpyxl check for empty cell
  29. How to write to an existing excel file without breaking formulas with openpyxl?
  30. Setting styles in Openpyxl

Leave a Comment