0

I am using openpyxl to modify an existing Excel file and save it to a new Excel file after the modification. The source Excel file has some 60 columns and 5000 rows.

Here is the code, first the imports and workbook loading/activating:

import openpyxl
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.utils import range_boundaries

wb_source = load_workbook(EXCEL.xlsx")
source_sheets = wb_source.sheetnames
ws1 = wb_source[source_sheets[0]]

wb_target = Workbook()
ws2 = wb_target.active

I have trouble in this part of the code where I would use my excelcolumns list to iterate through the first ~30 columns to copy them to the new Excel file. For some reason, the "for cell in..." part only accepts hard coded ws1['column_number:column_number'] and I am not able to pass it a list item created in another list "columnlist", shown below.

This one works fine and writes the first column, A, into the new Excel file:

for cell in ws1['A:A']:
    print('Printing cells from ' + str(cell.column) + str(cell.row))
    ws2.cell(row=cell.row, column=1, value=cell.value)

wb_target.save(EXCEL_target.xlsx')

But this one produces an AttributeError:

excelcolumns = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W","X", "Y", "Z",     "AA", "AB", "AC", "AD"]

columnlist = []
for i in excelcolumns:
    columnlist.append(("ws1" + "['" + i + ":" + i + "']"))

for cell in columnlist[0]:
    print('Printing cells from ' + str(cell.column) + str(cell.row))
    ws2.cell(row=cell.row, column=1, value=cell.value)

---> Traceback (most recent call last):
File "c:/...PATH.../", line 50, in <module>
print('Printing cells from ' + str(cell.column) + str(cell.row))
AttributeError: 'str' object has no attribute 'column'

My question is: how could I pass the "for cell in..." part every item in my columnlist in a for loop, so that I would not have to write separate lines for every column (meaning I do not want to do it like this: 

for cell in ws1['A:A']:... & for cell in ws1['B:B']:... 
& for cell in ws1['C:C']:... etc.

)?

jeppoo1
  • 650
  • 1
  • 10
  • 23
  • 1
    https://openpyxl.readthedocs.io/en/stable/tutorial.html#accessing-one-cell – Joe Dec 18 '19 at 17:42
  • https://stackoverflow.com/questions/12902621/getting-the-row-and-column-numbers-from-coordinate-value-in-openpyxl – Joe Dec 18 '19 at 17:43
  • 1
    Variable and function names should follow the `lower_case_with_underscores` style. – AMC Dec 18 '19 at 18:23
  • 1
    @stovfl Yes! The part "Iteration over all rows, only column 2" solved my problem. Thank you! – jeppoo1 Dec 19 '19 at 08:47
  • @Joe for some reason I cannot get the cell_range = ws['A1':'C2'] to work with multiple columns. My code is for cell in ws1['A1' : 'D5']: ws2.cell(row=cell.row, column=cell.column, value=cell.value) and it always ends up with some kind of AttributeError, such as "AttributeError: 'tuple' object has no attribute 'row'". The above code only works if the column letters match (meaning only one column) (such as for cell in ws1['A1' : 'A5']:). "for cell in ws1['C1:D555']:" does not work either. – jeppoo1 Dec 19 '19 at 08:47
  • @Joe the stackoverflow link would definitely be helpful for me in getting the column letters/numbers. I may try that some time! But the problem seems to lie with the Worksheet.cell() method not accepting multiple different columns. – jeppoo1 Dec 19 '19 at 08:53
  • @AMC You mean that excelcolumns should be excel_columns and columnlist should be column_list? That is a good point, thank you! – jeppoo1 Dec 19 '19 at 08:54
  • Forgot to mention that I had to set "column=cell.column" to get my code to work: for cell in col_cells: ws2.cell(row=cell.row, column=cell.column, value=cell.value) – jeppoo1 Dec 19 '19 at 10:46

0 Answers0