Home > Mobile >  how can i set a value for X amount of cells in a worksheet?
how can i set a value for X amount of cells in a worksheet?

Time:08-05

i am reading the automate the boring stuff with python and wanted to modify one of the code used in the book. im trying to set a font for a range of cells and give those cells a value. but i keep getting a error

This is the code that i am trying:

import openpyxl as xl
from openpyxl.styles import Font 

wb = xl.Workbook()
sheet = wb["Sheet"]

italic24Font = Font(size=18, italic = True) # creating a font type, this returns a font object

for rowNum in range(1,100):
  cell = sheet.cell(row=rowNum, column=1)
  sheet[cell].font = italic24Font
  sheet[cell] = "what is happing?"

wb.save("idk.xlsx")

Error that i get:

Traceback (most recent call last):
  File "c:\Users\JO\Desktop\Automate the boaring stuff\Chapter_13_Excel\projects\other\testing.py", line 12, in <module>
 sheet[cell].font = italic24Font
  File "C:\Users\JO\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\openpyxl\worksheet\worksheet.py", line 290, in __getitem__    min_col, min_row, max_col, max_row = range_boundaries(key)
  File "C:\Users\JO\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.10_qbz5n2kfra8p0\LocalCache\local-packages\Python310\site-packages\openpyxl\utils\cell.py", line 133, in range_boundaries    
m = ABSOLUTE_RE.match(range_string)
TypeError: expected string or bytes-like object

CodePudding user response:

You're not accessing the cell correctly. You could access the first cell with sheet["A1"], but since you already have a reference to the cell, just change the font directly on the cell rather than via the sheet. You can also set the cell's value with sheet.cell().

import openpyxl as xl
from openpyxl.styles import Font 

wb = xl.Workbook()
sheet = wb["Sheet"]

italic24Font = Font(size=18, italic = True) # creating a font type, this returns a font object

for rowNum in range(1,100):
  cell = sheet.cell(row=rowNum, column=1, value="what is happing?")
  cell.font = italic24Font
  
wb.save("idk.xlsx")
  • Related