Setting column width affect other columns

Python
#1

Hello.

I am doing a exercise sheet in Excel using openpyxl.
It is for my daughter to use for exercise multiplication tables.

So far everything is good. I got everything working as I want to. I also added code to set width and height of the cells.
However when I set a width for column “A” it affects also columns “B”, “C” and “D”.
But column “E” is not affected. It can be set individually
This “behaviour” repeat it self at columns “F” to “I”
Why is three other columns affected by one?

from random import randint
from openpyxl import Workbook
from openpyxl import load_workbook

# wb = Workbook()
# Used when creating a new file

wb = load_workbook("multitest.xlsx")
ws = wb.active


# slump = random
def slump_a():
    a = randint(6,9)
    return a

def slump_b():
    b = randint(1,10)
    return b

for i in range(1, 31):
    ws.cell(row=i, column=1, value=slump_a())
    ws.cell(row=i, column=2, value="x")
    ws.cell(row=i, column=3, value=slump_b())
    ws.cell(row=i, column=4, value="=")
    ws.cell(row=i, column=5, value="___")

    ws.cell(row=i, column=6, value=slump_a())
    ws.cell(row=i, column=7, value="x")
    ws.cell(row=i, column=8, value=slump_b())
    ws.cell(row=i, column=9, value="=")
    ws.cell(row=i, column=10, value="___")

ws.column_dimensions['A'].width = 4
# Above also set width for column B, C, and D. (!?)
ws.column_dimensions['E'].width = 12

ws.column_dimensions['F'].width = 4
# Above also set width for column G, H, and I. (!?)
ws.column_dimensions['J'].width = 12

# row_dimensions
# rader = rows, rad = row
rader = ws.max_row + 1
for rad in range(1, rader):
    ws.row_dimensions[rad].height = 20

wb.save("multitest.xlsx")

Output looks like:

6 x 9 = __ 7 x 3 = __
9 x 3 = __ 9 x 4 = __
and so on for 15 rows
Total of 30 questions.

#2

Hi @ulfen69
I don’t work in openpyxl and didn’t found any post related to your problem. But I found the following in the openpyxl documentation:

Link

I don’t know if it helps but try to define the width with integers for example 4.0 instead of 4.

Cheers

#3

And try to use lower case letters like you did in

ws.cell(row=i, column=10, value="___")

Maybe that helps too.

#4

Hello Didier.

Do you mean something like:

ws.column_dimensions['a'].width = 4.0  # ?

If so I will try as soon as I get home.
Thanks for reply

Ulf

#5

Yes! That is what I mean. I don’t know why this should help but give it a try! :nerd_face:

#6

I also noticed that you might mix columns and rows like here:

for i in range(1, 31):
    ws.cell(row=i, column=1, value=slump_a())
    ws.cell(row=i, column=2, value="x")
    ws.cell(row=i, column=3, value=slump_b())
    ws.cell(row=i, column=4, value="=")
    ws.cell(row=i, column=5, value="___")

There are no numbers for columns. Colums are specified with letters in excel. See here:
grafik

I think the code must look like this:

for i in range(1, 31):
    ws.cell(row=i, column='a', value=slump_a())
    ws.cell(row=i, column='b', value="x")
    ws.cell(row=i, column='c', value=slump_b())
    ws.cell(row=i, column='d', value="=")
    ws.cell(row=i, column='e', value="___")

but maybe I’m wrong.

Have a look if the width of your columns is also affected if you comment out the lines ws.column_dimensions and see if the width is also affected.

#7

Hello @DidierCH

I could not wait until I got home. And I wanted to try on a Windows machine also.
At home I have got Ubuntu and Libre office. I will try the same when I got home.

It worked :slight_smile: At least at work.

I am a bit ashamed to say, but I think you are wrong about

ws.cell(row=i, column=1, value=slump_a())

When one use the cell function one set the row and column by number + the value to fill the cell with.
There is another way to set the value of a cell:

ws[“A1”] = "hello!

Now its time to go home and try this on my Linux machine.
I will report the outcome of that later.

Thanks a lot for your reply.

#8

Hi @DidierCH

It was just the value for the width that had to be changed to float.
This works fine now:

ws.column_dimensions['A'].width = 4.0
ws.column_dimensions['B'].width = 4.0
ws.column_dimensions['C'].width = 4.0
ws.column_dimensions['D'].width = 4.0
ws.column_dimensions['E'].width = 8.0

Works fine now.
With a little help from my friends.

:+1:

1 Like
#9

Awesome @ulfen69 that you get it to work. It was just guessing from my side. But I’m glad that I could help.

#10

This is like the coolest thread ever.