Application reads some template XLS, fills it with data, customizes styles and saves to output file. When file opened by excel or open office all contents looks fine. But when file saved from excel with another name and then reopened contents became quite a mess, because of styles (i think they excenged randomly one with other). Same actions with open office produces absolutely normal file, so only msoffice is affected.
Can you attach the template file and sample code to reproduce the behavior? Yegor
Created attachment 23381 [details] Full example (style mess after "open"-"save as") Full example
After data filled in the template, sheet copied cell-by-cell to the new workbook (actually some templates concatenated together). To archive authenticity cells copied with styles. Styles copied with following functions. I suggest that this is source of a problem public static boolean fontsEquals(HSSFFont font1, HSSFFont font2) { return (font1.getItalic() == font2.getItalic()) && (font1.getStrikeout() == font2.getStrikeout()) && (font1.getBoldweight() == font2.getBoldweight()) && (font1.getFontHeightInPoints() == font2.getFontHeightInPoints()) && (font1.getColor() == font2.getColor()) && (font1.getFontName() != null && font1.getFontName().equals(font2.getFontName())) && (font1.getTypeOffset() == font2.getTypeOffset()) && (font1.getUnderline() == font2.getUnderline()); } public static HSSFFont copyFontTo(HSSFFont sourceFont, HSSFWorkbook targetWb) { if (sourceFont == null) { return null; } for (short i = 0; i < targetWb.getNumberOfFonts(); ++i) { HSSFFont font = targetWb.getFontAt(i); if (fontsEquals(font, sourceFont)) { return font; } } HSSFFont targetFont = targetWb.createFont(); targetFont.setBoldweight(sourceFont.getBoldweight()); targetFont.setColor(sourceFont.getColor()); targetFont.setFontHeightInPoints(sourceFont.getFontHeightInPoints()); targetFont.setFontName(sourceFont.getFontName()); targetFont.setItalic(sourceFont.getItalic()); targetFont.setStrikeout(sourceFont.getStrikeout()); targetFont.setTypeOffset(sourceFont.getTypeOffset()); targetFont.setUnderline(sourceFont.getUnderline()); return targetFont; } public static boolean styleEquals( HSSFWorkbook wb1, HSSFCellStyle style1, HSSFWorkbook wb2, HSSFCellStyle style2) { return (style1.getAlignment() == style2.getAlignment()) && (style1.getBorderBottom() == style2.getBorderBottom()) && (style1.getBorderTop() == style2.getBorderTop()) && (style1.getBorderLeft() == style2.getBorderLeft()) && (style1.getBorderRight() == style2.getBorderRight()) && (fontsEquals(style1.getFont(wb1), style2.getFont(wb2))) && (style1.getFillBackgroundColor() == style2.getFillBackgroundColor()) && (style1.getFillForegroundColor() == style2.getFillForegroundColor()) && (style1.getIndention() == style2.getIndention()) && (style1.getRotation() == style2.getRotation()) && (style1.getVerticalAlignment() == style2.getVerticalAlignment()) && (style1.getWrapText() == style2.getWrapText()); } public static HSSFCellStyle copyStyleTo(HSSFWorkbook sourceWb, HSSFCellStyle sourceStyle, HSSFWorkbook targetWb) { if (sourceStyle == null) { return null; } for (short i = 0; i < targetWb.getNumCellStyles(); ++i) { HSSFCellStyle style = targetWb.getCellStyleAt(i); if (styleEquals(sourceWb, sourceStyle, targetWb, style)) { return style; } } HSSFCellStyle targetStyle = targetWb.createCellStyle(); targetStyle.setAlignment(sourceStyle.getAlignment()); targetStyle.setBorderBottom(sourceStyle.getBorderBottom()); targetStyle.setBorderTop(sourceStyle.getBorderTop()); targetStyle.setBorderLeft(sourceStyle.getBorderLeft()); targetStyle.setBorderRight(sourceStyle.getBorderRight()); HSSFFont targetFont = copyFontTo(sourceStyle.getFont(sourceWb), targetWb); if (targetFont != null) { targetStyle.setFont(targetFont); } targetStyle.setFillBackgroundColor(sourceStyle.getFillBackgroundColor()); targetStyle.setFillForegroundColor(sourceStyle.getFillForegroundColor()); targetStyle.setIndention(sourceStyle.getIndention()); targetStyle.setRotation(sourceStyle.getRotation()); targetStyle.setVerticalAlignment(sourceStyle.getVerticalAlignment()); targetStyle.setWrapText(sourceStyle.getWrapText()); return targetStyle; }
I confirmed the trouble. For an unknown reason Excel inserts an extra font record and it shifts the font indexes. I'm not sure if it is a problem of POI or another weird feature of Excel. Your code looks good and should work just fine. Interestingly, if I create all styles and fonts in advance then everything is OK and fonts survive across read / write. See a modified version of your program where copyStyles is called before creating the output. Hope this "workaround" helps you. Regards, Yegor
Created attachment 23387 [details] a workaround