Loop through a list to create multiple excel files

user_6351142 0
Viewed 1202

Hi I'm trying to loop through a list to create multiple excel files (not sheets). I was able to create separate dataframe in below codes, but I was wondering if anyone has example for creating multiple excel files by looping through a list and create multiple excel files with each company name.

from datetime import date, time
import xlsxwriter
import pandas as pd
from openpyxl import load_workbook
import openpyxl

#create a list of company - this list contains company names
dfList  = list(set(df['Company']))

#create a separate DF by each company
for i, row in enumerate(dfList):
    dfName = dfNames[i]
    dfNew = df1[df1['Company'] == row]
    globals()[dfName] = dfNew   

#create excel files, format cells, and export dataframe in each excel files
for i, row in enumerate(dfList):
    workbook = xlsxwriter.Workbook('C:/Users/user/row.xlsx')
    worksheet = workbook.add_worksheet()

    worksheet.write('C1', 'Work Location')
    worksheet.data_validation('C2:C100', {'validate': 'list','source':['WFH', 'Office']})
    workbook.close()

    #Update excel file created above with dataframe data
    book = load_workbook('C:/Users/user/row.xlsx')
    writer = pandas.ExcelWriter('C:/Users/user/row.xlsx', engine='openpyxl') 
    writer.book = book

    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

    row.to_excel(writer, "Sheet1", columns=['Company','Sales'])

    writer.save()
excel python
1 Answers

I found an answer and this worked for me

from datetime import date, time
import xlsxwriter
import pandas as pd
import os
from openpyxl import load_workbook
import openpyxl

#create a list of company - this list contains company names
dfList  = list(set(df['Company']))

#create a separate DF by each company
for i, row in enumerate(dfList):
    dfName = dfNames[i]
    dfNew = df1[df1['Company'] == row]
    globals()[dfName] = dfNew   

#create excel files, format cells, and export dataframe in each excel files
directory = r'C:/Users/user'
for i, row in enumerate(dfList):
    workbook = xlsxwriter.Workbook(os.path.join(directory, row))
    worksheet = workbook.add_worksheet()

    worksheet.write('C1', 'Work Location')
    worksheet.data_validation('C2:C100', {'validate': 'list','source':['WFH', 'Office']})
    workbook.close()

    #Update excel file created above with dataframe data
    book = load_workbook(os.path.join(directory, row))
    writer = pandas.ExcelWriter(os.path.join(directory, row), engine='openpyxl') 
    writer.book = book

    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

    row.to_excel(writer, "Sheet1", columns=['Company','Sales'])

    writer.save()
user_63511420
Related