Bug 46846

Summary: After "open"-"save as" XLS in MSExcel (2003, 2007) styles are messed up
Product: POI Reporter: Kolobok <aaacmc>
Component: HSSFAssignee: 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
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.
Comment 1 Yegor Kozlov 2009-03-13 04:37:19 UTC
Can you attach the template file and sample code to reproduce the behavior? 

Yegor
Comment 2 Kolobok 2009-03-13 07:23:48 UTC
Created attachment 23381 [details]
Full example (style mess after "open"-"save as")

Full example
Comment 3 Kolobok 2009-03-13 07:25:15 UTC
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;
	}
Comment 4 Yegor Kozlov 2009-03-16 08:19:07 UTC
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
Comment 5 Yegor Kozlov 2009-03-16 08:20:41 UTC
Created attachment 23387 [details]
a workaround