I have one header excel file (header.xlsx) with only one row, and one excel table file (station.xlsx) with multiple rows.
The aim is to extract first row from (station.xlsx), and then append the extracted first row to (header.xlsx), and then save them as new excel file called (1.xlsx).
After that, I want to extract second row from (station.xlsx), and then append the extracted second row to (header.xlsx), and then save them as (2.xlsx).
After that, I want to extract third row from (station.xlsx), and then append the extracted third row to (header.xlsx), and then save them as (3.xlsx)…
The loops will be continued until 167 rows from (station.xlsx) are extracted and append and save as new excel file individually.
# import module
import os
import openpyxl
# load excel with its path
wrkbk1 = openpyxl.load_workbook(r"C:Userskxz237Desktoptestheader.xlsx")
sh1 = wrkbk1.active
wrkbk2 = openpyxl.load_workbook(r"C:Userskxz237Desktopteststations.xlsx")
sh2 = wrkbk2.active
# iterate through excel and display data
for i in range(1, sh2.max_row+1):
output_file = r'C:Userskxz237Desktoptesti.xlsx'
sh1.append(sh2.row_values(i))
wrkbk1.save(output_file)
AttributeError: ‘Worksheet’ object has no attribute ‘row_values’
Could you help me fix the python code?
My original excel files are attached.enter link description here
3
It’s easy with pandas:
import pandas as pd
header = pd.read_excel(r"testheader.xlsx")
df = pd.read_excel(r"teststations.xlsx", header=None)
for i in df.index:
df.loc[i,:].to_frame().T.to_excel(f'test/{i}.xlsx')
0