CODEWITHSUNDEEP
pythonxlwings
NeverTooOldToLearn
NeverTooOldToLearn

Reputation: 21

Python - xlwings - How to rename sheet and add value to the name

I have a list of Sheets and for each sheet I need to SUM float values from the column A and the SUM outcome should be added to the Sheet name. For example: Sheet1 --> Sheet1 (123). Please advice. Thank you.

import xlwings as xw
wb = xw.Book(filepath)
sheet_names = wb.sheets

for x in sheet_names:
    /missing summary calculation here
    wb.sheets[x].name = wb.sheets[x] + str('123') # ('123') should be the 
    outcome of the SUM so str(SUM)
    
wb.save()
wb.close()
xw.App().quit()

Upvotes: 0

Views: 9055

Answers (1)

boxhot
boxhot

Reputation: 209

Assuming a simple sheet like the one below:

enter image description here

I have converted the sheet to a pandas dataframe to compute the sum of the first column. To avoid errors in the summation, I have converted the column to numeric values, returning NaN for non-numeric ones. See code below.

import xlwings as xw
import pandas as pd

app = xw.App(visible=False) # opens wb without making it visible
wb = xw.Book('Book1.xlsx')
sheet_names = wb.sheets

for x in sheet_names:
    # get pandas dataframe of total sheet
    sheet1 = wb.sheets[x].used_range.value
    df = pd.DataFrame(sheet1)
    # find column A
    colA = df.iloc[:,0]
    # convert values in column A to numerical values, return NaN for non-numeric values
    colA_numeric = pd.to_numeric(colA, errors='coerce')
    # compute sum of colA
    sum_floats = colA_numeric.sum(skipna=True)
    # add sum to sheet name
    wb.sheets[x].name = wb.sheets[x].name + ' ('+str(sum_floats)+')'

wb.save()
wb.close()
app.quit()

The result is:

enter image description here

Note: running the script multiple times will append the sheet name, which will look something like Sheet 1 (30.0) (30.0). You can avoid this by changing the way you obtain the sheet name to be more generic.

Upvotes: 0

Related Questions