Duplicate the workbook with the invoice number list-CodePudding

I have an two excel sheet,

Format.xlsx (having 2 tabs)

Tab 1 - Summary annexure

Tab 2 - Annexure A

Invoice list - Having unique list of invoices

I want to duplicate "Format.xlsx" (i.e copy and paste) number of times in the invoice list and excel to be named as per relevant invoice number.

eg say I have two invoices with numbers 123 and 456, I want the format to be copy-pasted twice, to an excel sheet, having named changed to 123 and 456.

Also, without changing the format of the excel template.

I tried the below code

invoice_num_list = pd.read_csv('Invoice List.csv') #List of invoices
print(invoice_num_list.shape)

# Output (140, 1)

Format = pd.read_excel('Format.xlsx') #Template having 2 tabs

for invoice_num in invoice_num_list:
    Format.to_excel(f'{invoice_num}.xlsx')

But the above code is not duplicating the sheet 140 times, but only once.

Also, the format of the excel template was changed and formula placed is completely erased.

The intention is to copy-paste the format 140 times (for respective invoice numbers) having relevant formula in Tab 1, later on placing new data in Tab 2, Tab 1 would be automatically updated.

Help would be much appreciated!

Link to sample data -

https://drive.google.com/drive/folders/1inrofeT6v9P0ISEcmbswvpxMMCq5TaV0?usp=sharing

CodePudding user response：

I think the sample code below will do what you want.

Note that it uses openpyxl.load_workbook() instead of pd.read_excel(), which allows all tabs, formulas and formatting in the existing workbook to be preserved.

Also note that the for loop in your code needed to be adjusted slightly to correctly iterate through the invoices in the other file.

import pandas as pd
import openpyxl
invoice_num_list = pd.read_csv('Invoice List.csv') #List of invoices
print(invoice_num_list.shape)

# Here we use only the first 5 invoices, as a test
invoice_num_list = invoice_num_list[invoice_num_list.index < 5]
print(invoice_num_list)

Format = openpyxl.load_workbook('Format.xlsx') #Template having 2 tabs

for invoice_num_index, invoice_num in invoice_num_list.itertuples():
    print(f"{invoice_num}")
    Format.save(f'{invoice_num}.xlsx')

Output:

(140, 1)
    Invoice number
0  KA-AVSR-Feb22-1
1  KA-AVSR-Feb22-2
2  KA-AVSR-Feb22-3
3  KA-AVSR-Feb22-4
4  KA-AVSR-Feb22-5
KA-AVSR-Feb22-1
KA-AVSR-Feb22-2
KA-AVSR-Feb22-3
KA-AVSR-Feb22-4
KA-AVSR-Feb22-5