Summary: | After "open"-"save as" XLS in MSExcel (2003, 2007) styles are messed up | ||
---|---|---|---|
Product: | POI | Reporter: | Kolobok <aaacmc> |
Component: | HSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED REMIND | ||
Severity: | normal | ||
Priority: | P2 | ||
Version: | 3.2-FINAL | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | Windows Server 2003 | ||
Attachments: |
Full example (style mess after "open"-"save as")
a workaround |
Description
Kolobok
2009-03-13 02:22:43 UTC
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
|