I wanted to propose the introduction for a warning for a rare (yet very annoying) edge case, which affects the consumption of files written with openxlsx by MS Excel.
Note: the mentioned edge case does not affect LibreOffice, so I understand that a warning only for non-free Excel is maybe not welcome. However, I would argue that many end users might use Excel.
The Problem that is approached
MS Excel flags a xlsx file as "corrupt" and goes in auto repair mode, if the xlsx contains a table (as created with writeDataTable) where at least one header cell exceeds the length of 255 characters. This is an edge case as there are many arguments against making table headers of that size (moreover, one might question the sanity of any design where this is the case). However, it still might occur that this is necessary due to demands out of the developers decision.
The auto repair process of Excel then simply truncates any table column name to 255 chars.
What this PR would add
In the rare case a user would have to write workbooks containing this edge case, there would be a warning about the Excel limitations / fixable incompatibility with Excel standard. It would ease the process of resolving the problem.
Other solutions I considered / problematic aspects
Directly truncating the values would also be an option, however this would be too invasive in my mind, as this does only affect MS Excel consumers, and not LibreOffice Calc, for example.
From the same line of argument, even a warning could be considered too much. However, I'd say the case with such long headers will affect very little cases, so most developers working with openxlsx might never see it.
Checklist
[x] Added test
[x] Added comments to explain the comments
[ ] NEWS entry
[ ] Version bump
[ ] Complied to package code style
Thank you very much for considering, and the the great package in general!
ycphs
commented
1 year ago
Hello dear maintainers,
I wanted to propose the introduction for a warning for a rare (yet very annoying) edge case, which affects the consumption of files written with
openxlsxby MS Excel.Note: the mentioned edge case does not affect LibreOffice, so I understand that a warning only for non-free Excel is maybe not welcome. However, I would argue that many end users might use Excel.
The Problem that is approached
MS Excel flags a xlsx file as "corrupt" and goes in auto repair mode, if the xlsx contains a table (as created with
writeDataTable) where at least one header cell exceeds the length of 255 characters. This is an edge case as there are many arguments against making table headers of that size (moreover, one might question the sanity of any design where this is the case). However, it still might occur that this is necessary due to demands out of the developers decision.The auto repair process of Excel then simply truncates any table column name to 255 chars.
What this PR would add
In the rare case a user would have to write workbooks containing this edge case, there would be a warning about the Excel limitations / fixable incompatibility with Excel standard. It would ease the process of resolving the problem.
Other solutions I considered / problematic aspects
openxlsxmight never see it.Checklist
Thank you very much for considering, and the the great package in general!
Kind regards, Malte