Copy file and rewrite cell value @python

Thing you want to do

Copy Excel as a template and Rename the file. By the way, I want to rewrite only some values of the cover sheet.

background

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.

Script shed

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)

Note

Pandas are convenient.

Recommended Posts

Copy file and rewrite cell value @python
File DL, byte value and delete in Python3
Python shallow copy and deep copy
Python shallow and deep copy
Python # About reference and copy
Python CSV file reading and writing
Python memo ① Folder and file operations
Write O_SYNC file in C and Python
See file and folder information on python
[python] Read html file and practice scraping
Script python file
Python file processing
Output python log to both console and file
Copy of python
[Python for Hikari-] Chapter 06-04 Functions (arguments and return value 3)
[Python of Hikari-] Chapter 06-02 Function (argument and return value 1)
Try creating a compressed file using Python and zlib
Make a copy of a Google Drive file from Python
File write speed comparison experiment between python 2.7.9 and pypy 2.5.0
Read json file with Python, format it, and output json
[Python for Hikari-] Chapter 06-03 Functions (arguments and return value 2)
[Python] Start a batch file from Python and pass variables.
[python] Compress and decompress
File operations in Python
Python and numpy tips
[Python] File / directory operations
[Python] pip and wheel
Batch design and python
Python iterators and generators
Rewrite Python2 code to Python3 (2to3)
Python packages and modules
Vue-Cli and Python integration
Automatically execute python file
Ruby, Python and map
Reading pyc file (Python 3.5.2)
File operations in Python
python input and output
Python and Ruby split
Python3, venv and Ansible
Read Python csv file
python external file reading
Python asyncio and ContextVar
[Python] Read the csv file and display the figure with matplotlib
Get the MIME type in Python and determine the file format
Python application: Data handling Part 1: Data formatting and file input / output