I have written the below which doesnt seem to be working. I am defining dataframe as d3, to be a columnn with 20 rows with the comment “my comment”. I am then trying to add that into an existing saved down excel doc in cell L2. The dataframe is printing and looks right in the IDE, but just doesnt overwrite in the excel. any help would be greatly appreciated, as im just starting out with python!
from openpyxl import load_workbook
import pandas as pd
import xlsxwriter
#create dataframe to be 19 lines with comment "my comment"
comment = "my comment"
df3 = pd.DataFrame([comment]* 20)
print(df3)
wb = load_workbook(r'H:myfile.xlsx')
writer = pd.ExcelWriter(r'H:myfile', engine='xlsxwriter')
df3.to_excel(writer,sheet_name='mysheet', startrow = 2, startcol = 12)
wb.save(r'H:myfile.xlsx')
print("done")
Advertisement
Answer
As far as I can tell, you are doing multiple things at once which negate each other’s effect. Why are youn even using openpyxl?
You load the workbook from the excel file with openpyxl and load_workbook, then you override you excel file with the pandas DataFrame, at which point the output should be the one you want. But after that, you again override the excel file with the content of the loaded workbook which you loaded before the changes you made with pandas, which restores the excel file to its original state.
Get rid of every line involving openpyxl and it should work.
import pandas as pd
import xlsxwriter
#create dataframe to be 19 lines with comment "my comment"
comment = "my comment"
df3 = pd.DataFrame([comment]* 20)
print(df3)
writer = pd.ExcelWriter(r'H:myfile', engine='xlsxwriter')
df3.to_excel(writer,sheet_name='mysheet', startrow = 2, startcol = 12)
print("done")
If you want to modify your excel file with pandas, you don’t need openpyxl, if you want to modify you excel with openpyxl, you don’t need pandas.