Resize columns in Excel document with Python

Question

I am currently working with creating an Excel document in Python. I create the excel document but I'm not sure what is wrong with the code that it is not resizing the columns correctly. Does anyone have any ideas?

def writerow(self, vals):
    ws = self.workbook.active
    this_row = self.numrows
    this_col = 1
    for v in vals:
        cell = ws.cell(row = this_row, column = this_col)
        cell.value = v
        if ws.column_dimensions[get_column_letter(this_col)] < len(str(v)):
            ws.column_dimensions[get_column_letter(this_col)] = len(str(v))
        this_col += 1
    self.numrows += 1
    self.worksheet = ws

Excel columns are not sized by number of letters. That's your problem. If you're using a monospace font you can estimate a scaling factor pretty well, but otherwise you'll have to guess the width value. — Mr. Mascaro
– Mr. Mascaro, Commented May 6, 2015 at 14:00
You should tag your question with the Python package that you're using to work with Excel files. (Or include the relevant import statements in your code snippet, or mention it in the text of your question.) — John Y
– John Y, Commented May 6, 2015 at 14:20

A-Dub · Accepted Answer · 2015-05-06 14:10:07Z

2

I found what I needed for what I am working on. I needed to add ".width" to the areas where I was checking or assigning column widths.

 def writerow(self, vals):
    ws = self.workbook.active
    this_row = self.numrows
    this_col = 1
    for v in vals:
        cell = ws.cell(row = this_row, column = this_col)
        cell.value = v
        print "Column Width:"
        print ws.column_dimensions[get_column_letter(this_col)].width
        if ws.column_dimensions[get_column_letter(this_col)].width < len(str(v)):
            ws.column_dimensions[get_column_letter(this_col)].width = len(str(v))
        this_col += 1
    self.numrows += 1
    self.worksheet = ws

answered May 6, 2015 at 14:10

A-Dub

7310 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Charlie Clark Over a year ago

Glad you got it working. FWIW I'd probably take the sizing code out of the loop and do it before or after. If you're wanting to get the width to match the size of the text then store this in a list and max() it afterwards. Sizing, as noted above, is always only best guess.

Collectives™ on Stack Overflow

Resize columns in Excel document with Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related