Bug 48995

Summary: Excel found unreadable content in workbook when using applyFont(...) on RichTextString
Product: POI Reporter: sbrunot
Component: XSSFAssignee: POI Developers List <dev>
Status: RESOLVED WORKSFORME    
Severity: major CC: r_botto
Priority: P2    
Version: 3.13-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Source File for Sample main generating invalid worksheet
Invalid worksheet generated with sample code
Screenshot of PpenXMLSDK 2.0 Validation Tool for out.xslx
Converted filed by Excell 2013
OpenSDK 2.0 Validation tool for converted File

Description sbrunot 2010-03-26 14:11:03 UTC
When executing the following piece of code:

		XSSFWorkbook wb = new XSSFWorkbook();
		XSSFSheet sh = wb.createSheet("test");
		XSSFRow row = sh.createRow(0);
		XSSFCell cell = row.createCell(0, XSSFCell.CELL_TYPE_STRING);
		
		RichTextString data = new XSSFRichTextString("bold not bold");
		short fontHeight = 12;
		XSSFFont boldFont = wb.createFont();
		boldFont.setFontName("Arial");
		boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		boldFont.setColor(Font.COLOR_NORMAL);
		boldFont.setFontHeight(fontHeight);

		data.applyFont(0, 4, boldFont);
		
		cell.setCellValue(data);
		wb.write(new FileOutputStream("out.xlsx"));

and then opening the out.xlsx file in Microsoft Excel 2007, an error message is displayed: "Excel found unreadable content in 'out.xlsx'. Do you want to recover the contents of this workbook ? If you trust the source of this workbook, click Yes."
Comment 1 Nick Burch 2010-05-26 10:00:44 UTC
Could you please upload two files to help us with this bug?

Firstly, the output of your sample program which Excel is unable to read

Secondly, can you please create a file in excel with the same contents you were hoping POI could give you?

Once we have those two files, we can compare the differences and figure out what POI is doing wrong.
Comment 2 Rodolfo Botto 2016-02-03 17:26:31 UTC
Created attachment 33520 [details]
Source File for Sample main generating invalid worksheet
Comment 3 Rodolfo Botto 2016-02-03 17:27:07 UTC
Created attachment 33521 [details]
Invalid worksheet generated with sample code
Comment 4 Rodolfo Botto 2016-02-03 17:27:48 UTC
Created attachment 33522 [details]
Screenshot of PpenXMLSDK 2.0 Validation Tool for out.xslx
Comment 5 Rodolfo Botto 2016-02-03 17:28:16 UTC
Created attachment 33523 [details]
Converted filed by Excell 2013
Comment 6 Rodolfo Botto 2016-02-03 17:28:41 UTC
Created attachment 33524 [details]
OpenSDK 2.0 Validation tool for converted File
Comment 7 Rodolfo Botto 2016-02-03 17:35:54 UTC
I have the same problem reported in this incident, working with poi 3.13 also.

I take the sample code here, generate a main program and a sample worksheet that fails to validate on Excel 2010 and Excel 2013. Also fails on OpenXML SDK 2.0 Productivity Tool (ScreenShot provided).

If you see the reported error, Validation reports that elemnt sz is unexpected in current context.

Letting Excel 2013 to adjust the worksheet and saving it again, new worksheet validates with no problem. Comparing screenshots, you can see the only difference between two font definitions is element order. Invalid has (FontName, FontSize, Bold, Color). Valid one has (Bold, FontSize, Color, FontName)

The order that poi save the elements, is the order the setMethods are invoked on XSSFFont, (constructor invokes 2 methods, in bad order).

I've made a workaround in my case, changing the order of set (and adding somes) on constructor of XSSFFont, but is an incomplete solution, if not all properties are setted. 
I have no clue on how to affect serialization order on xmlbeans library, may be you can apply a more generic solution to this problem.

Thanks in advance,
Rodolfo Botto
Comment 8 Dominik Stadler 2017-10-09 17:56:38 UTC
Updated code to produce the test-file

        try (Workbook wb = new XSSFWorkbook()) {
            Sheet sh = wb.createSheet("test");
            Row row = sh.createRow(0);
            Cell cell = row.createCell(0, CellType.STRING);

            RichTextString data = new XSSFRichTextString("bold not bold");
            Font boldFont = wb.createFont();
            boldFont.setFontName("Arial");
            boldFont.setBold(true);
            boldFont.setColor(Font.COLOR_NORMAL);
            boldFont.setFontHeightInPoints((short) 12);

            data.applyFont(0, 4, boldFont);

            cell.setCellValue(data);
            try (OutputStream stream = new FileOutputStream("/tmp/48995.xlsx")) {
                wb.write(stream);
            }
        }
Comment 9 Dominik Stadler 2017-10-10 11:38:40 UTC
Verifed that this works for me now with current versions of Apache POI and Microsoft Excel. 

Please reopen and provide more details if this is still a problem for you.