CODEWITHSUNDEEP
pythonpython-3.xopenpyxl
Tinkinc
Tinkinc

Reputation: 454

Rename Excel Tabs

I am trying to rename tabs in an excel spreadsheet however it for one of the tabs it produces this name every time: '# of Behavioral Health Reps (5)1' Not sure why. There is only 1 of these matching tabs in each file. It also does not do it to any other tab. 

text_search =  pd.DataFrame(
{'text_to_search':
    [
'Retro'
,'Prior'
,'Concurr'
,'Rate'
,'claims pd'
,'health reps'
,'External']
, 
'Replace':
[
'UR - Retrospective (1)'
,'UR - Prior Auth Req (2)'
,'UR - Concurrent Auth Req (2)'
,'Rate of First Level Appeals (3)'
,'Pct of Claims Paid (4)'
,'# of Behavioral Health Reps (5)' 
,'External Appeals (9)']})


for mco in files:
    wb = xl.load_workbook(mco, data_only=True)
    for sheet in wb.sheetnames:
        for index, row in text_search[0:].iterrows():           
            #print(row['text_to_search'],row['Replace'])
            if re.search(row['text_to_search'], sheet, re.IGNORECASE):
                worksheet = wb[sheet]
                worksheet.title = row['Replace']
    wb.save(mco)
    wb.close()```

Upvotes: 1

Views: 65

Answers (1)

Charlie Clark
Charlie Clark

Reputation: 19537

If it looks like a mapping, then use a mapping:

l1 = [
'Retro'
,'Prior'
,'Concurr'
,'Rate'
,'claims pd'
,'health reps'
,'External']

l2 =[
'UR - Retrospective (1)'
,'UR - Prior Auth Req (2)'
,'UR - Concurrent Auth Req (2)'
,'Rate of First Level Appeals (3)'
,'Pct of Claims Paid (4)'
,'# of Behavioral Health Reps (5)' 
,'External Appeals (9)']

mapping = {k:v for k,v in zip(l1, l2)}

for ws in wb:
     if s.title in mapping:
          s.title = mapping[s.title]

If case is likely to an issue then convert to lowercase:

l1 = [v.lower() for v in l1]
if s.title.lower() in mapping:

Upvotes: 3

Related Questions