Worksheet quoting in chart series

Issue #820 resolved
Aaron Thompson
created an issue

It would seem a similar issue has been observed in the past, and a simple solution was implemented:

https://bitbucket.org/openpyxl/openpyxl/issues/287/single-quotes-around-work-sheet-names

It would seem however that this does not account for all cases where excel requires a workbook name to be quoted, as I have run across a series of sheet names that also require quoting. I have not determined exactly what the rule is, however I will provide some of the specific sheetnames this applied to. This ultimately caused Series objects to not appear on scatter charts, resulting in blank charts in the output excel file.

Needs quoting but are passed over by simple check:

if "-" in sheetname or " " in sheetname:
  • bn12.131.02
  • bn12.131.14
  • bn13.45.07
  • bn13.77.03R

Needs quoting, but have a "-" in their sheetname already

  • bn12.155.14-1
  • bn12.107.01-1
  • bn12.131.05-1

From testing does not need quotes

  • test.sheet
  • Test.sheet.no.1

I am currently using openpyxl 2.4.1 and excel 2016 (windows) but I believe this also should apply to openpyxl 2.5 (adds ',' to check but nothing else)

Comments (10)

  1. Aaron Thompson reporter

    I've been testing a bit, and the rules are not straightforward. would it be a terrible thing to just add periods to the list of characters that need quoting? (even though they don't always need it)

  2. Aaron Thompson reporter

    I made the change I suggested in my previous comment to my client version and it worked as expected. I haven't encountered any side effects, but I'll update if I do.

    openpyxl / openpyxl / utils / cell.py - old

    def quote_sheetname(sheetname):
        """
        Add quotes around sheetnames if they contain spaces.
        """
        if (" " in sheetname
            or "-" in sheetname):
            sheetname = u"'{0}'".format(sheetname)
        return sheetname
    

    openpyxl / openpyxl / utils / cell.py - new

    def quote_sheetname(sheetname):
        """
        Add quotes around sheetnames if they contain spaces.
        """
        if (" " in sheetname
            or "-" in sheetname
            or "," in sheetname  #exists in openpyxl 2.5
            or "." in sheetname):  #my addition
            sheetname = u"'{0}'".format(sheetname)
        return sheetname
    
  3. CharlieC

    Thanks for the report and the investigation. It's beginning to look like we could do with a regex for this, although I suspect the most important thing is to find some documentation on when quoting is required.

  4. CharlieC

    Looks like § 18.17.2.3 Cell References contains the normative form for this. Interestingly the period is not included in the list of special characters. :-/

    sheet-name-character-special=
    apostrophe, apostrophe |
    character  (apostrophe | "*" | "[" | "]" | "\" | ":" | "/"
    | "?") ;
    (* any character, including operator, except  *, [, ], \, :, /, or ? *)
    
  5. CharlieC

    An update on this: while the quoting is correct, it looks like Excel has problems with the sheetname and will, for example, not let you add charts to sheets with periods in the sheetname.

  6. CharlieC

    Further investigation indicates that this is a quirk or bug in Excel that is related to the name "bn12.131.02". Other combinations such as "Sheet12.131.02" do not exhibit the same problems. I will ask the OOXML Working Group about the quoting.

  7. CharlieC

    It seems that this is a bug in Excel that was silently fixed in about November 2017. At least in Excel 2016, it persists in older versions. I have asked Microsoft via the OOXML for a list of reserved names and other characters that need quoting and will update the libary if this information is ever forthcoming.

  8. Log in to comment