Copy Excel as a template and Rename the file. By the way, I want to rewrite only some values of the cover sheet.
Based on a certain template for work I have to prepare some similar files, Only some cell values need to be changed.
If you just want to duplicate the file, you can use bat, It is troublesome to open Excel and rewrite the value change. VBA is fine, but I don't want to use it too much. If possible, I also want to practice Python.
If you define the file name and change value in csv It's easy to change and it's nice to be able to reuse it.
cpFileCopyNameByCSVlist.py
import os
import sys
import pandas as pd
import shutil
import openpyxl
csvname = './cpfilelist.csv'
resultFolder = './cpResult/'
templatefilename = './template.xlsx'
shutil.rmtree(resultFolder)
os.mkdir(resultFolder)
#read list from csv
data = pd.read_csv(csvname).values.tolist()
wsname = 'header'
for row in data:
#copy template file renamed by list.csv
print(row[0] +":"+row[1])
newFileName = resultFolder + row[0] +'.xlsx'
newTitleName = row[1]
shutil.copyfile(templatefilename,newFileName)
# change cell.value from csv valye
wb = openpyxl.load_workbook(newFileName)
ws=wb[wsname]
ws['B5'] = newTitleName
wb.save(newFileName)
wb.close()
sys.exit(0)
Pandas are convenient.
Recommended Posts