Creating Excel Sheets In Same Workbook In Python

Need a suggestion in my code. I have a data frame in sheet1 of workbook: column 1 column 2 000A0000 2 000B0000 3 000A0001 5 000B000

Solution 1:

To add sheets to the same excel file use openpyxl module as follows:

import pandas as pd
import openpyxl

#reading the sheet1 using read_excel
df = pd.read_excel('workbook.xlsx', sheet_name='Sheet1')

#creating pandas ExcelWriter object and loading the excel file using `openpyxl`    
df_writer = pd.ExcelWriter('workbook.xlsx', engine='openpyxl')
excel = openpyxl.load_workbook('workbook.xlsx')
df_writer.book = excel

#checking string in column 1 and writing those to respective sheets in same workbookforstring in ['A','B']:
    df[df['column 1'].str.contains(string)].to_excel(df_writer,sheet_name=string)
#saving and closing writer
writer.save()
writer.close()

Solution 2:

to_excel would not append sheets to your existing file:

use openpyxl instead:(something like below)

import pandas
from openpyxl import load_workbook

book = load_workbook('path+filename_you_want_to_write_in.xlsx')
writer = pandas.ExcelWriter('path+filename_you_want_to_write_in.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, "Sheet_name_as_per_your_choice",index=False)

writer.save()

Also if you dynamically want to read through the sheets and not specific sheets:

f = pd.ExcelFile(file)
sheet_names = df.sheet_names
for i in list(sheet_names):
    df = pd.read_excel(f,i)

This iterates through all your sheets and provides a dataframe based on the sheets.

Solution 3:

Try using the xlsxwriter engine.

writer = pd.ExcelWriter('<< file_name >>', engine='xlsxwriter')

df.to_excel(writer, sheet_name='Sheet2')

writer.save()