I have an excel sheet (with the first row having numbers) named template.xlsx.It contains data in the sheet named ‘data’ as follows
it also has a graph in the second sheet named graph like this
I have a csv file named input.csv which has data in the similar format as template.xlsx but has more columns than it and the first row doesn’t have the numbers.
I want to copy the data from input.csv to the first sheet of template.xlsx so that the graph according to the data in input.csv will be generated in second sheet.
For this, I am making another excel file output.xlsx which should first take in all the data from template.xlsx, empty itself (since number of columns in template.xlsx and input.csv are different) and copy data from input.csv in it and generate graph accordingly (I need to retain the template.xlsx and dont want to over write it,also, in the output.xlsx I want the first row to be there too).
I did this but I am not able to accomplish the required task.
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows
import openpyxl
from shutil import copyfile
template_file = 'template.xlsx' #Has numbers in row 1 already
output_file = 'output.xlsx' #What we are saving the template as
copyfile(template_file, output_file)
# Read in the data to be pasted into the termplate
df = pd.read_csv('input.csv') #to read the csv file
wb = openpyxl.load_workbook(output_file)
ws = wb.get_sheet_by_name('data')
for r in dataframe_to_rows(df, index=False, header=False):
ws.append(r)
wb.save(output_file)