Re: subject: POI style routines failing

Jorge

One technique you might consider for finding the problem is to open the XLSX file with something like 7-Zip or Winzip and extract the sheet1.xml.

Then do a binary kind of search for the problem - there is a section in the XML called <Sheetdata> - remove the 2nd half, adjust the dimension element that is near to top.

Put that adjusted sheet1.xml back in the XLSX - I keep it open in 7-Zip, then drag the XML into it in that window. That will update the workbook.

Then go back to open the workbook again - if it does not have the error message, then the problem is in the 1st half. Go back to the XML, test again with the 2nd half of the 1st half, keep going until you narrow things down. It's called a binary technique because you are dividing in halves.

Microsoft does have a diagnostic tool for Office Open XML - it's free and sometimes gives you better information. You can also go to any element, it can validate it - I still think the binary method I use will be faster, though.

Excel does sometimes tell you a line and position where the problem was detected - that will be somewhere in the sheet XML - the name and location will be in the message.

Still, as you noticed, there is often very little information to point you to the problem.

Regards and good hunting!
Vern

On 10/2/2020 6:31 AM, Jorge Ubeda wrote:

Solved. It was the numeric format passed as a string to ss_style API.
Indeed it was improperly formatted (I will review formats with users here
for future cases). After reviewing dozens of incidents inside POI or other
sources, a couple of rules to take in account:
1, Excel does not explain this type of issues. Neither code errors, which
are different and a lot referring this problem. It appears with a dialog
message like " *excel found some unreadable content, do you want ro repair*?"
Worksheet only works if you select to repair. After repairing, you receive
some diagnostic, like
"Removed Records: Style from /xl/styles.xml part (Styles)"
Then, you have lost your styles (all).
2, it happens for several different reasons, not only styles, but bad
formatted names for files or sheets, number of styles, out of limits cells
or char data, etc. Your better guess is the Removed records sentence and
the associated xml expansion (three lines, approx). It's your
responsibility to find the origin/cause.
Another lesson learned to me.
Thanks all of you, even for publishing my issue.
Jorge Ubeda