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."
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.
Created attachment 33520 [details] Source File for Sample main generating invalid worksheet
Created attachment 33521 [details] Invalid worksheet generated with sample code
Created attachment 33522 [details] Screenshot of PpenXMLSDK 2.0 Validation Tool for out.xslx
Created attachment 33523 [details] Converted filed by Excell 2013
Created attachment 33524 [details] OpenSDK 2.0 Validation tool for converted File
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
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); } }
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.