single quotes around work sheet names

Issue #287 resolved
ADROB created an issue

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

ticks_sheet_name.PNG

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:

1) 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)

2) 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.

Comments (6)

  1. CharlieC

    Thanks for the report. It's an interesting problem. FWIW you are referring to single quotes and not ticks. The quotes are obviously being used to escape the name within Excel so I think the solution really is how links are set. When reading existing files we don't do any special handling which makes me think names are not escaped when they're stored but I'd need to check.

    Unfortunately, the way we handle links (hyperlinks but also charts and images) is currently very flaky and fixing it is more important than anything else. So in the meantime you will have to solve this in client code. A pull request would be welcome.

  2. CharlieC

    I think the solution for this is to have a quote_sheetname function which can be used internally by any relevant functions and will ensure that worksheet names are quoted properly when saved. It's definitely wrong to expose the quoting that Excel uses within openpyxl.

  3. Log in to comment