single quotes around work sheet names
Created originally on Bitbucket by ADROB
Hello guys,
I have a problem with the sheet names in openpyxl:
If the sheet name contains a dash ("-") or a white space (" ") Excel adds it with a single quote to the list of work sheets. Although the work sheet's title is shown without single quote in the GUI.
When you right click on the cell "A2 > Edit Hyperlink" (see example bellow) you will see what I mean
Openpyxl will return the sheet name as displayed - without the single quotes. Nevertheless, if you address cells in other sheets you will have to use the sheet name with ticks. For instance:
works: ='does-not-work'!A1
does not work =does-not-work!A1
My proposal:
-
one could improve the the title setter in class Worksheet or the regular expression 'bad_title_char_re' (bad_title_char_re = re.compile(r"^'|[\*?:/[]]|'$") should do the job)
It's nice that openpyxl returns the work sheet name as shown in the GUI. Nevertheless, I think that it would be better to return the name with single quotes since this is the way it will be used in Excel. In the cases where openpyxl uses the sheet name as a parameter it could strip single quotes at the end or the start of the name.
import openpyxl
def _hyperlink(target_ws, target_cell="A1"):
return "#{target_ws}!{target_cell}".format(target_ws=target_ws,
target_cell=target_cell)
wb = openpyxl.Workbook()
wb.create_sheet(title="works")
wb.create_sheet(title="does-not-work")
wb.create_sheet(title="'does-not-work'")
# The last command will create a messed up sheet!
# I know that excel does not allow work sheet names
# which start or end with a tick "'"
ws = wb.get_active_sheet()
currentCell = ws.cell(row=0, column=0)
currentCell.value = "go to sheet works"
currentCell.hyperlink = _hyperlink("hyperlink works")
currentCell = ws.cell(row=0, column=1)
currentCell.value = "<- reference works"
currentCell = ws.cell(row=1, column=0)
currentCell.value = "go to sheet does-not-work"
currentCell.hyperlink = _hyperlink("does-not-work") # must be "'does-not-work'"
currentCell = ws.cell(row=1, column=1)
currentCell.value = "<- hyperlink does not work"
currentCell = ws.cell(row=2, column=0)
currentCell.value = "go to sheet 'does-not-work'"
currentCell.hyperlink = _hyperlink("'does-not-work'")
currentCell = ws.cell(row=2, column=1)
currentCell.value = "<- hyperlink works but will link to worksheet does-not-work and not 'does-not-work'."
ws.append(["Sheet name", "Sheet title"])
for sheet_name in wb.get_sheet_names():
ws.append([sheet_name,
wb.get_sheet_by_name(sheet_name).title])
wb.save("C:\\test.xlsx")
PS:
I know that there is an easy fix for my specific problem. I could check for " " and "-" in _hyperlink(.) for myself.