Page tree
Skip to end of metadata
Go to start of metadata

This functionality was added in ExcelWriter 8.5 and is only available for XLSX or XLSM workbooks.

The data markers in sheet tab names follow the same syntax rules as the data markers in cells, as explained in Creating Data Markers. However there are certain considerations when using data markers in sheet tabs.

In Excel, worksheet names have the following restrictions:

  • They cannot exceed 31 characters.
  • They cannot contain the following characters: ':' '\' '/' '?' '*' '[' ']'
  • They cannot be the same name as another worksheet in the workbook
  • They cannot be left blank

If the value bound to a data marker in a worksheet tab does not comply with the above restrictions, ExcelWriter will take one of the following actions in order to generate a valid worksheet name in Excel:

Value exceeds 31 characters

If the value exceeds 31 characters, it will be truncated to 28 characters and the last 3 characters will be replaced with "...". For example, the 36-character long value, "ThisIsAVeryVeryVeryLongWorksheetName", will become "ThisIsAVeryVeryVeryLongWorks...".

Value contains invalid characters

If the value contains any invalid characters (':' '\' '/' '?' '*' '[' ']'), those will be replaced with an underscore '_'. For example, the sheet name value "Sheet:Name" will become "Sheet_Name"

Worksheet with the same name already exists

If there is already a worksheet with the same name in the workbook, a unique number in parentheses will be appended to the end, just as Excel does. For example, for an existing sheet name "Sales", the value will become "Sales (2)". If there are more tabs that are bound to the same value, the number in the suffix will be incremented as such "Sales (3)", "Sales (4)" and so on.

If the sheet name with the unique number exceeds the 31 character limit, then the sheet name will be truncated.. For example, for the 30-character long sheet name value "ThisIsATooTooLongWorksheetName", when the suffix " (2)" is added, the length will become 34, which would exceed the limit. To fit the sheet name to 31-character limit, the sheet name will be truncated and become "ThisIsATooTooLongWorksheetN (2)". No ellipsis "..." is appended to the name.

Value is an empty string

If the value in the data source is an empty string, then the data marker will be replaced with the string "_BLANK".

No values are bound to data markers with ordinal syntax or the optional modifier

For data markers with ordinal syntax or the 'optional' modifier, if there was no column in the data source that matches the data marker, or if the matching column in the data source has a 'null' value, then those data markers will be replaced with the string "_UNBOUND".

If ExcelWriter would normally throw an exception because there is no matching data source, an exception will still be thrown if the data marker is in a tab name.

Value is an unsupported data type for data markers

If the data type of the value in the data source is an unsupport type for data markers, the data marker will be populated with a custom string:

o Binary type (mapping to an array of Byte type) will be replaced with or a "<InvalidType-BINARY>"or
o Variant-type (mapping to an Object) will be replaced with "<InvalidType-VARIANT>"

In the case there was already a worksheet with the same name, then a suffix of form " 2" (space + a number starting from 2, with no parenthesis) will be appended to it. For example, "<InvalidType-BINARY> 2".

  • No labels