I am running a python code to separate text in Excel, for example we have in one cell (dimensions: 15mm) the code will take the word " dimensions" which is before the symbol ":" and add it as a column name then take "15mm" and add it as a value.

The issue is that the code only works if I specify the cell (such as A4). How can you request to go through all the cells for column A?

import openpyxl

book = openpyxl.load_workbook('C:/Users/zalka/Desktop/Python project/new13.xlsx')

sheet = book.active

U4 = sheet["A4"]
Column_base = 3
i = 0
a = sheet.cell(row=3, column=1)
tech_details = U4.value.splitlines()
print(U4.value.splitlines())

for tech_detail in tech_details:
    print(tech_detail)
    print(tech_detail.index(":"))
    s = tech_detail.index(":")
    name = tech_detail[:s]
    print(name)
    value = tech_detail[s 1:]
    print(value)
    sheet.cell(row=3, column=i Column_base).value = name
    sheet.cell(row=4, column=i Column_base).value = value
    i=i 1

book.save('C:/Users/zalka/Desktop/Python project/write2cell2.xlsx')

CodePudding user response：

Imagining all your text to split is in the column A. Can you try doing this:

import openpyxl

book = openpyxl.load_workbook('data.xlsx')
sheet = book['A']

for j,i in enumerate(sheet['A']):
    try:
        name = str(i.value).split(":")
        value = str(i.value).split(":")[1]  
        sheet.cell(row=j 1, column=2).value = name
        sheet.cell(row=j 1, column=3).value = value
    except:
        pass        

CodePudding user response：

1. Import module:

import openpyxl

2. Load excel with its path:

workbk = openpyxl.load_workbook("Book1.xlsx")
  
sh = workbk.active

3. Iterate through excel and display data:

for row in sh.iter_rows(min_row=1, min_col=1, max_row=12, max_col=3):
    for cell in row:
        print(cell.value, end=" ")