Copy from xlsx to a specific sheet to another xlsx
I need help with python. Basically I have 2 files (for this example there will be files file1 and file2). File1 has multiple sheets inside, file2 just one sheet. So after doing some work on file2, I now have the DataFrame I need. And I need to insert this DataFrame into one specific sheet in file1.
File1
A B C D E F G
<data> <data> <data> <data> <formula> <formula> <formula>
<data> <data> <data> <data> <formula> <formula> <formula>
<data> <data> <data> <data> <formula> <formula> <formula>
<data> <data> <data> <data> <formula> <formula> <formula>
<data> <data> <data> <data> <formula> <formula> <formula>
<data> <data> <data> <data> <formula> <formula> <formula>
File2
A B C D
<Newdata> <Newdata> <Newdata> <Newdata>
<Newdata> <Newdata> <Newdata> <Newdata>
<Newdata> <Newdata> <Newdata> <Newdata>
<Newdata> <Newdata> <Newdata> <Newdata>
<Newdata> <Newdata> <Newdata> <Newdata>
<Newdata> <Newdata> <Newdata> <Newdata>
So now i need to update the file one with the new Data.
File1
A B C D E F G
<Newdata> <Newdata> <Newdata> <Newdata> <formula> <formula> <formula>
<Newdata> <Newdata> <Newdata> <Newdata> <formula> <formula> <formula>
<Newdata> <Newdata> <Newdata> <Newdata> <formula> <formula> <formula>
<Newdata> <Newdata> <Newdata> <Newdata> <formula> <formula> <formula>
<Newdata> <Newdata> <Newdata> <Newdata> <formula> <formula> <formula>
<Newdata> <Newdata> <Newdata> <Newdata> <formula> <formula> <formula>
So columns E, F and G have some formulas that are updated by the data in columns A, B, C, D.
I am trying different options for this. Combine both files and show the columns I am trying to load, load both files and create a new file with new information ... The main problem is that in file1 I have several sheets that I need to save because the columns are E. F and G (the one containing the formulas) will update the other sheets.
So if someone gives me a hand with this, please. Thanks, I will appreciate the help
source to share
No doubt this is the best way to do it, but I can do it:
from openpyxl import load_workbook
import os
os.chdir("Directory Path here")
wb = load_workbook('file.xlsx')
ws = wb.active
#or use the below to pick sheet as by name
# ws = wb.get_sheet_by_name
inde = []
val = []
for col in ws.iter_cols():
for cell in col:
h = cell.coordinate
inde.append(h)
v = cell.value
val.append(v)
diction = dict(zip(inde,val))
wb1=load_workbook('file1.xlsx')
ws1 = wb1.active
for i in diction.keys():
ws1[i] = diction[i]
wb1.save('file1.xlsx')
source to share