Re: subject: POI style routines failing

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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