openpyxl - adjust column width size

Question

I have following script which is converting a CSV file to an XLSX file  but my column size is very narrow  Each time I have to drag them with mouse to read data  Does anybody know how to set column width in openpyxl    Here is the code I am using      usr bin python2 6 import csv from openpyxl import Workbook from openpyxl cell import get column letter  f   open  users info cvs txt    rU    csv register dialect  colons   delimiter       reader   csv reader f  dialect  colons    wb   Workbook   dest filename   r account info xlsx   ws   wb worksheets 0  ws title    Users Account Information   for row index  row in enumerate reader       for column index  cell in enumerate row           column letter   get column letter  column index   1           ws cell   s s   column letter   row index   1    value   cell  wb save filename   dest filename

User · Answer

Since in openpyxl 2 6 1  it requires the column letter  not the column number  when setting the width   for column in sheet columns      length   max len str cell value   for cell in column      length   length if length  lt   16 else 16     sheet column dimensions column 0  column letter  width   length

User · Answer

This is my version referring  Virako  s code snippet  def adjust column width from col ws  min row  min col  max col            column widths               for i  col in                   enumerate                      ws iter cols min col min col  max col max col  min row min row                                  for cell in col                  value   cell value                 if value is not None                       if isinstance value  str  is False                          value   str value                       try                          column widths i    max column widths i   len value                       except IndexError                          column widths append len value            for i  width in enumerate column widths                col name   get column letter min col   i              value   column widths i    2             ws column dimensions col name  width   value   And how to use is as follows   adjust column width from col ws  1 1  ws max column

User · Answer

After update from openpyxl2 5 2a to latest 2 6 4  final version for python 2 x support   I got same issue in configuring the width of a column   Basically I always calculate the width for a column  dims is a dict maintaining each column width    dims cell column    max  dims get cell column  0   len str cell value       Afterwards I am modifying the scale to something shortly bigger than original size  but now you have to give the  Letter  value of a column and not anymore a int value  col below is the value and is translated to the right letter    worksheet column dimensions get column letter col   width   value  1    This will fix the visible error and assigning the right width to your column     Hope this help

User · Answer

This is a dirty fix  But openpyxl actually supports auto fit  But there is no method to access the property  import openpyxl from openpyxl utils import get column letter  wb   openpyxl load workbook  quot Example xslx quot   ws   wb  quot Sheet1 quot   for i in range 1  ws max column 1       ws column dimensions get column letter i   bestFit   True     ws column dimensions get column letter i   auto size   True

User · Answer

My variation of Bufke s answer  Avoids a bit of branching with the array and ignores empty cells   columns   Now fixed for non-string cell values   ws   your current worksheet dims      for row in ws rows      for cell in row          if cell value              dims cell column    max  dims get cell column  0   len str cell value         for col  value in dims items        ws column dimensions col  width   value   As of openpyxl version 3 0 3 you need to use    dims cell column letter    max  dims get cell column letter  0   len str cell value       as the openpyxl library will raise a TypeError if you pass column dimensions a number instead of a column letter  everything else can stay the same

User · Answer

A slight improvement of the above accepted answer  that I think is more pythonic  asking for forgiveness is better than asking for permission   column widths      for row in workSheet iter rows        for i  cell in enumerate row           try              column widths i    max column widths i   len str cell value            except IndexError              column widths append len str cell value     for i  column width in enumerate column widths       workSheet column dimensions get column letter i   1   width   column width

User · Answer

We can convert numbers to their ASCII values and give it to column dimension parameter  import openpyxl as xl  work book   xl load workbook  file location   sheet   work book  Sheet1   column number   2 column   str chr 64   column number   sheet column dimensions column  width   20 work book save  file location

User · Answer

All the above answers are generating an issue which is that col 0  column is returning number while worksheet column dimensions column  accepts only character such as  A    B    C  in place of column  I ve modified  Virako s code and it is working fine now   import re import openpyxl    for col in  ws columns      max lenght   0     print col 0       col name   re findall   w d   str col 0        col name   col name 0      col name   re findall   w   str col name   0      print col name      for cell in col          try              if len str cell value    gt  max lenght                  max lenght   len cell value          except              pass     adjusted width    max lenght 2       ws column dimensions col name  width   adjusted width

User · Answer

Even more pythonic way to set the width of all columns that works at least in openpyxl version 2 4 0   for column cells in worksheet columns      length   max len as text cell value   for cell in column cells      worksheet column dimensions column cells 0  column  width   length   The as text function should be something that converts the value to a proper length string  like for Python 3   def as text value       if value is None          return        return str value

User · Answer

I had to change  User3759685 above answer to this when the openpxyl updated  I was getting an error  Well  phihag reported this in the comments as well  for column cells in ws columns      new column length   max len as text cell value   for cell in column cells      new column letter    openpyxl utils get column letter column cells 0  column       if new column length  gt  0          ws column dimensions new column letter  width   new column length   1

User · Answer

Here is an answer for Python 3 8 and OpenPyXL 3 0 0   I tried to avoid using the get column letter function but failed    This solution uses the newly introduced assignment expressions aka  walrus operator    import openpyxl from openpyxl utils import get column letter  workbook   openpyxl load workbook  myxlfile xlsx    worksheet   workbook  Sheet1    MIN WIDTH   10 for i  column cells in enumerate worksheet columns  start 1       width             length         if  length    max len str cell value  if  cell value    cell value  is not None else                               for cell in column cells    gt   MIN WIDTH         else MIN WIDTH           worksheet column dimensions get column letter i   width   width

User · Answer

I have a problem with merged cells and autosize not work correctly  if you have the same problem  you can solve with the next code   for col in worksheet columns      max length   0     column   col 0  column   Get the column name     for cell in col          if cell coordinate in worksheet merged cells    not check merge cells             continue         try    Necessary to avoid error on empty cells             if len str cell value    gt  max length                  max length   len cell value          except              pass     adjusted width    max length   2    1 2     worksheet column dimensions column  width   adjusted width

User · Answer

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   gt  i              if len cell   gt  column widths i                   column widths i    len cell          else              column widths     len cell    for i  column width in enumerate column widths       worksheet column dimensions get column letter i 1   width   column width   A bit of a hack but your reports will be more readable

User · Answer

With openpyxl 3 0 3 the best way to modify the columns is with the DimensionHolder object  which is a dictionary that maps each column to a ColumnDimension object  ColumnDimension can get parameters as bestFit  auto size  which is an alias of bestFit  and width  Personally  auto size doesn t work as expected and I had to use width and fogured out that the best width for the column is len cell value    1 23   To get the value of each cell it s necessary to iterate over each one  but I personally didn t use it because in my project I just had to write worksheets  so I got the longest string in each column directly on my data   The example below just shows how to modify the column dimensions   import openpyxl from openpyxl worksheet dimensions import ColumnDimension  DimensionHolder from openpyxl utils import get column letter  wb   openpyxl load workbook  Example xslx   ws   wb  Sheet1    dim holder   DimensionHolder worksheet ws   for col in range ws min column  ws max column   1       dim holder get column letter col     ColumnDimension ws  min col  max col  width 20   ws column dimensions   dim holder

[python] openpyxl - adjust column width size

Examples related to python

Examples related to openpyxl