How to copy a range from one sheet to another as values using openpyxl in python
16,837
Solution 1
You can try with something like:
for i in range(1, 100):
for j in range(1, 26):
sheet1.cell(row=i,column=j).value = sheet.cell(row=i,column=j).value
wb1.save('file2.xlsx')
Solution 2
Giving another way to do using pandas
import pandas as pd
excel = pd.read_excel('file1.xlsx', header=None)
writer = pd.ExcelWriter('file2.xlsx')
excel.loc[:99, :25].to_excel(writer, 'sheet1', index=False, header=False)
Add an openpyxl solution with data-only
wb = openpyxl.load_workbook('file1.xlsx', data_only=True)
wb1 = openpyxl.load_workbook('file2.xlsx')
sheet = wb['R']
sheet1 = wb1['Rt']
for row in sheet['A1':'Z100']:
for cell in row:
sheet1[cell.coordinate].value = cell.value
wb1.save('file2.xlsx')
EDIT
cell.coordinate returns value like 'A1' ,then sheet1['A1'].value is the value of cell A1
Related videos on Youtube
Author by
qwww
Updated on June 04, 2022Comments
-
qwww almost 2 years
I have to copy a range from A1:Z100 from sheet onw of workbook 1 to sheet 1 of workbook 2?
My code :
wb = openpyxl.load_workbook('file1.xlsx') wb1 = openpyxl.load_workbook('file2.xlsx') sheet = wb["R"] sheet1 = wb1["Rt"] sheet1.cell(row=1,column=1).value = sheet.cell(row=1,column=1).value
This is not working properly. How to copy this range to that sheet?
-
Charlie Clark over 5 yearsWhat is not working? You are currently copying the value of only one cell.
-
-
qwww over 5 yearswhat is srcfile?
-
qwww over 5 yearsHow can i copy only values and not with formulas?
-
Joe over 5 yearsCan this help? stackoverflow.com/questions/50941203/…
-
James Liu over 5 years@Joe range(1:100) will not work use range(1,100) instead
-
qwww over 5 yearssheet1[cell.coordinate].value what this does?
-
James Liu over 5 years@qwww cell.coordinate returns value like 'A1' ,then sheet1['A1'].value is the value of cell A1