Add new sheet with graph

userGuel1 0
Viewed 41

newby question. I would like to add a new sheet to an existing wb that I've created with xlwings. It seems that when I try to add e write the 2nd sheet the 1st one going to be overwritten.

Here the code :

import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
import seaborn as sns # library for visualization
sns.set() # this command sets the seaborn chart style as the default
import xlwings as xw
from datetime import datetime  
df=pd.read_excel('aspire_1909.xls')
df2=df.drop([0,2])
new_header = df2.iloc[1]
df2 = df2[2:]
df2.columns = new_header
df2=df2.set_index('User')
wb = xw.Book()
sht = wb.sheets[0]
sht.name = "Aspire Manager Graph"
sht.range('R1').value = df3
started=len(df3.loc[df3['Manager Review'] == 'Started'])
complete = len(df3.loc[df3['Manager Review'] == 'Complete'])
complete_reopened = len(df3.loc[df3['Manager Review'] == 'Complete (Reopened)'])
not_started = len(df3.loc[df3['Manager Review'] == 'Not Started'])
past_due = len(df3.loc[df3['Manager Review'] == 'Past Due'])
def insert_heading(rng,text):
    rng.value = text
    rng.font.bold = True
    rng.font.size = 24
    rng.font.color = (0,0,139)
insert_heading(sht.range("A2"),f"ASPIRE YEAR END REVIEW - MANAGER STATUS del {datetime.today().strftime('%d-%m-%Y')}")
data = {'Not Started':not_started, 'Started':started, 'Completed':complete,'Reopened' : complete_reopened,'Past Due ' : past_due  }

status = list(data.keys())
values = list(data.values())
x_labels = list(a + ' ' + str(b) for (a, b) in zip(status, values))
 
fig = plt.figure(figsize = (10, 5))

# creating the bar plot

fig, ax = plt.subplots(figsize=(15, 15))
bars = ax.bar(status, values, color =['red','blue','green','yellow','violet'],
    width = 0.4)
ax.bar_label(bars, fmt="%d", fontsize=26, rotation=0, padding=3)
plt.bar(status, values, color =['red','blue','green','yellow','violet'],
        width = 0.4)
plt.xticks(status, x_labels)
plt.xticks(rotation = 45, fontsize = 13)
plt.xlabel("Year End Review Completion Status")
plt.ylabel("No Users",rotation=45,fontsize = 13)
plt.title("Aspire Mgr Year End Review")


plt.show()

sht.pictures.add(fig,
                 name = "Aspire Mgr Status Graph",
                 update = True,
                 left =sht.range("A4").left,
                 top = sht.range("A4").top,
                 height= 500,
                 width= 700)

sht1 = wb.sheets[0]
wb.sheets.add('Aspire Employees Graph')
sht1.range('R1').value = df2

started=len(df2.loc[df2['Aspire year-end reflection (FY22)'] == 'Started'])
complete = len(df2.loc[df2['Aspire year-end reflection (FY22)'] == 'Complete'])
complete_reopened = len(df2.loc[df2['Aspire year-end reflection (FY22)'] == 'Complete (Reopened)'])
not_started = len(df2.loc[df2['Aspire year-end reflection (FY22)'] == 'Not Started'])
past_due = len(df2.loc[df2['Aspire year-end reflection (FY22)'] == 'Past Due'])

def insert_heading(rng,text):
    rng.value = text
    rng.font.bold = True
    rng.font.size = 24
    rng.font.color = (0,0,139)
insert_heading(sht1.range("A2"),f"ASPIRE YEAR END REVIEW EMPLOYEE STATUS del {datetime.today().strftime('%d-%m-%Y')}")

data = {'Not Started':not_started, 'Started':started, 'Completed':complete,'Reopened' : complete_reopened,'Past Due ' : past_due  }

status = list(data.keys())
values = list(data.values())
x_labels = list(a + ' ' + str(b) for (a, b) in zip(status, values))
 
fig = plt.figure(figsize = (10, 5))

# creating the bar plot

fig, ax = plt.subplots(figsize=(15, 15))
bars = ax.bar(status, values, color =['red','blue','green','yellow','violet'],
    width = 0.4)
ax.bar_label(bars, fmt="%d", fontsize=26, rotation=0, padding=3)
plt.bar(status, values, color =['red','blue','green','yellow','violet'],
        width = 0.4)
plt.xticks(status, x_labels)
plt.xticks(rotation = 45, fontsize = 13)
plt.xlabel("Year End Review Completion Status")
plt.ylabel("Nb. Users",rotation=45,fontsize = 13)
plt.title("Aspire Employee Year End Review")


plt.show()
sht1.pictures.add(fig,
                 name = "Aspire Employee Status Graph",
                 update = True,
                 left =sht.range("A4").left,
                 top = sht.range("A4").top,
                 height= 500,
                 width= 700)

Could someone would be able to help me get what Is wrong ? ( I know ,almost everything :-) ) Thanks a lot in advance

xlwings dataframe pandas excel python
1 Answers

In the second half of the code you have:

sht1 = wb.sheets[0]
wb.sheets.add('Aspire Employees Graph')
sht1.range('R1').value = df2

What wb.sheets[0] is returning is the very first sheet of the workbook. Towards the beginning you have the first section, which is:

sht = wb.sheets[0]
sht.name = "Aspire Manager Graph"
sht.range('R1').value = df3

As you use wb.sheets[0] both times, but haven't inserted a sheet at the beginning, you are just referring to the same sheet. The addition of the new sheet is correct, but you haven't set that as variable sht1.

Instead, for the second section, you could re-write to the following, combining the two lines into one so that the variable is the correct sheet:

sht1 = wb.sheets.add('Aspire Employees Graph')
sht1.range('R1').value = df2

Edit

To change the colour of the sheet tab:

sht1.api.Tab.ColorIndex = 3

The full list of colours can be found in the VBA ColorIndex documentation.

For more specific colours, see the answers to this question.

Rawson0
Related