github-starred/libxlsxwriter

Fork 0

mirror of https://github.com/jmcnamara/libxlsxwriter.git synced 2026-05-15 14:15:54 -06:00

[GH-ISSUE #256] worksheet->vba_name allows illegal worksheet codeName #204

New issue

Closed

opened 2026-05-05 11:54:49 -06:00 by gitea-mirror · 10 comments

gitea-mirror commented

2026-05-05 11:54:49 -06:00

Owner

Originally created by @mewalig on GitHub (Dec 19, 2019).
Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/256

Originally assigned to: @jmcnamara on GitHub.

Hi,

Worksheet codenames seem to have tighter limitations than worksheet names, and because codenames are, at the moment, set to the same value as the worksheet name, under certain conditions, a generated XLSM file will throw errors when opened.

Two examples of this are:

if a worksheet name starts with a number
if a worksheet name contains Asian characters

The below line appears to require modification to address this issue:

worksheet_set_vba_name(worksheet, worksheet->name)

I am not sure exactly what the codeName limitations are, but at the least, codenames that begin with A-Za-z and only contain alphanumeric ASCII chars, up to a total of 31 characters, seem to work (and of course they must be unique across worksheets within the same wb). Obviously it is somewhat desirable to try to retain a codename similar to the actual worksheet name, so perhaps something that at least tries to use alphanumerics that are in the ws name would be ideal, along the lines of:

first char = first char of name, if A-Za-z, else 'z'
subsequent chars = corresponding char of name, if A-Za-z0-9, else '_'

last several chars may need to be _XXXX where XXXX is a number, in order to ensure uniqueness

I could take a crack at this if you'd like

Originally created by @mewalig on GitHub (Dec 19, 2019). Original GitHub issue: https://github.com/jmcnamara/libxlsxwriter/issues/256 Originally assigned to: @jmcnamara on GitHub. Hi, Worksheet codenames seem to have tighter limitations than worksheet names, and because codenames are, at the moment, set to the same value as the worksheet name, under certain conditions, a generated XLSM file will throw errors when opened. Two examples of this are: - if a worksheet name starts with a number - if a worksheet name contains Asian characters The below line appears to require modification to address this issue: ``` worksheet_set_vba_name(worksheet, worksheet->name) ``` I am not sure exactly what the codeName limitations are, but at the least, codenames that begin with A-Za-z and only contain alphanumeric ASCII chars, up to a total of 31 characters, seem to work (and of course they must be unique across worksheets within the same wb). Obviously it is somewhat desirable to try to retain a codename similar to the actual worksheet name, so perhaps something that at least tries to use alphanumerics that are in the ws name would be ideal, along the lines of: first char = first char of name, if A-Za-z, else 'z' subsequent chars = corresponding char of name, if A-Za-z0-9, else '_' last several chars may need to be _XXXX where XXXX is a number, in order to ensure uniqueness I could take a crack at this if you'd like

gitea-mirror

2026-05-05 11:54:49 -06:00

closed this issue
added the
bug

ready to close
labels

gitea-mirror commented

2026-05-05 11:54:55 -06:00

Author

Owner

@RaFaeL-NN commented on GitHub (Dec 19, 2019):

Form and control names must start with a letter and can be a maximum of 40 characters — including letters, numbers, and underscores

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/not-a-legal-object-nameitem

But, it can contain not-ASCII chars

@RaFaeL-NN commented on GitHub (Dec 19, 2019): _Form and control names must start with a letter and can be a maximum of 40 characters — including letters, numbers, and underscores_ https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/not-a-legal-object-nameitem But, it can contain not-ASCII chars

gitea-mirror commented

2026-05-05 11:54:58 -06:00

Author

Owner

@mewalig commented on GitHub (Dec 19, 2019):

It seems to have a limit of 31 chars for the property that corresponds to the XML property "codeName"-- when I manually try to change it in Excel, the following works:

z123456789012345678901234567890

and the following fails:

z1234567890123456789012345678901

@mewalig commented on GitHub (Dec 19, 2019): It seems to have a limit of 31 chars for the property that corresponds to the XML property "codeName"-- when I manually try to change it in Excel, the following works: ``` z123456789012345678901234567890 ``` and the following fails: ``` z1234567890123456789012345678901 ``` ![image](https://user-images.githubusercontent.com/8260442/71208485-12cb7e00-2277-11ea-85ee-d546060b21ad.png)

gitea-mirror commented

2026-05-05 11:55:00 -06:00

Author

Owner

@jmcnamara commented on GitHub (Dec 19, 2019):

@mewalig What was the original sheetname you encountered that raised this issue?

@jmcnamara commented on GitHub (Dec 19, 2019): @mewalig What was the original sheetname you encountered that raised this issue?

gitea-mirror commented

2026-05-05 11:55:04 -06:00

Author

Owner

@jmcnamara commented on GitHub (Dec 23, 2019):

I am not sure exactly what the codeName limitations are, but at the least, codenames that begin with A-Za-z and only contain alphanumeric ASCII chars, up to a total of 31 characters, seem to work

I think the limit may be as low as 22 characters for the VBA name as stored in Excel. I came across that once before:

https://github.com/jmcnamara/XlsxWriter/issues/628#issuecomment-494979289

I could take a crack at this if you'd like

Thanks but this issue doesn't happen often enough to try fix it like this. I'd prefer, for now, to just handle this, and other error case, using the debug steps in docs "What to do if it doesn't work":

https://libxlsxwriter.github.io/working_with_macros.html#ww_macros_debugging

@jmcnamara commented on GitHub (Dec 23, 2019): > I am not sure exactly what the codeName limitations are, but at the least, codenames that begin with A-Za-z and only contain alphanumeric ASCII chars, up to a total of 31 characters, seem to work I think the limit may be as low as 22 characters for the VBA name as stored in Excel. I came across that once before: https://github.com/jmcnamara/XlsxWriter/issues/628#issuecomment-494979289 > I could take a crack at this if you'd like Thanks but this issue doesn't happen often enough to try fix it like this. I'd prefer, for now, to just handle this, and other error case, using the debug steps in docs "What to do if it doesn't work": https://libxlsxwriter.github.io/working_with_macros.html#ww_macros_debugging

gitea-mirror commented

2026-05-05 11:55:06 -06:00

Author

Owner

@mewalig commented on GitHub (Dec 23, 2019):

@jmcnamara The sheet name in my use case where this issue came up was "1785_data_compare". Having sheet names that start with numbers is actually extremely common for our use cases.

I understand, it's easy enough for the caller to be aware and just reset the name using workbook_set_vba_name().

@mewalig commented on GitHub (Dec 23, 2019): @jmcnamara The sheet name in my use case where this issue came up was "1785_data_compare". Having sheet names that start with numbers is actually extremely common for our use cases. I understand, it's easy enough for the caller to be aware and just reset the name using ```workbook_set_vba_name()```.

gitea-mirror commented

2026-05-05 11:55:09 -06:00

Author

Owner

@jmcnamara commented on GitHub (Dec 23, 2019):

@mewalig

The sheet name in my use case where this issue came up was "1785_data_compare".

What was the corresponding VBA name used by Excel in this case?

I tried to replicate it but the worksheet VBA names were "Sheet1", "Sheet2", etc.

On a Unix-like system you can find the codename as follows (just change myfile to whatever filename you have):

$ unzip myfile.xlsm -d myfile
$ xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName"

@jmcnamara commented on GitHub (Dec 23, 2019): @mewalig > The sheet name in my use case where this issue came up was "1785_data_compare". What was the corresponding VBA name used by Excel in this case? I tried to replicate it but the worksheet VBA names were "Sheet1", "Sheet2", etc. On a Unix-like system you can find the codename as follows (just change myfile to whatever filename you have): ```bash $ unzip myfile.xlsm -d myfile $ xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName" ```

gitea-mirror commented

2026-05-05 11:55:11 -06:00

Author

Owner

@mewalig commented on GitHub (Dec 25, 2019):

The workbook did not exist before being created by libxlsxwriter, which by default set the equal the sheet name ("1785_data_compare"), which Excel would then throw a repair-able error upon opening. Creating from scratch using Excel, I tried creating a sheet in an XLSM, and also tried creating one in XLSX and then converting to XLSM, and in all cases I could come up with, the codeName was always set to SheetN (unless/until I later manually changed it).

@mewalig commented on GitHub (Dec 25, 2019): The workbook did not exist before being created by libxlsxwriter, which by default set the equal the sheet name ("1785_data_compare"), which Excel would then throw a repair-able error upon opening. Creating from scratch using Excel, I tried creating a sheet in an XLSM, and also tried creating one in XLSX and then converting to XLSM, and in all cases I could come up with, the codeName was always set to SheetN (unless/until I later manually changed it).

gitea-mirror commented

2026-05-05 11:55:13 -06:00

Author

Owner

@jmcnamara commented on GitHub (Dec 27, 2019):

in all cases I could come up with, the codeName was always set to SheetN (unless/until I later manually changed it).

Same here. So that should probably be the default. I'll re-open this as a bug and fix it.

@jmcnamara commented on GitHub (Dec 27, 2019): > in all cases I could come up with, the codeName was always set to SheetN (unless/until I later manually changed it). Same here. So that should probably be the default. I'll re-open this as a bug and fix it.

gitea-mirror commented

2026-05-05 11:55:14 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jan 11, 2020):

@mewalig I've pushed a fix to master for the issue with the incorrect default code name. Try it out if you get a chance.

There will still be cases where the user has to override the default but this should cover the majority of the default cases.

@jmcnamara commented on GitHub (Jan 11, 2020): @mewalig I've pushed a fix to master for the issue with the incorrect default code name. Try it out if you get a chance. There will still be cases where the user has to override the default but this should cover the majority of the default cases.

gitea-mirror commented

2026-05-05 11:55:15 -06:00

Author

Owner

@jmcnamara commented on GitHub (Jan 13, 2020):

Upstreamed in 0.9.2/0.9.3.

@jmcnamara commented on GitHub (Jan 13, 2020): Upstreamed in 0.9.2/0.9.3.