Bug 58974 - HSSFCellStyle - style lost when open in Excel 2013
Summary: HSSFCellStyle - style lost when open in Excel 2013
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.13-FINAL
Hardware: PC All
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-02-05 10:47 UTC by alencai
Modified: 2017-08-21 09:55 UTC (History)
1 user (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description alencai 2016-02-05 10:47:30 UTC
The program generates excel using POI and set style using HSSFCellStyle, such as fill background color, set border or display the number in thousand or million unit. The generated excel file's style works fine in Excel 2010 version. However, when open the same file using Excel 2013, some cells' style is lost whilst some are still there. Further we found that the program totally creates around 3000 HSSFCellStyle objects. If we reduce the number of instances of HSSFCellStyle objects. It helps to ease the issue, less cells' style lost.

Is it a known issue ? Is there in place solution already ?

Sample,

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

HSSFCellStyle lvHcs = wb.createCellStyle(); //wb's class is HSSFWorkbook
lvHcs.setAlignment(HSSFCellStyle.ALIGN_GENERAL);
lvHcs.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
lvHcs.setWrapText(true);
lvHcs.setFont(getFont(false, DEFAULT_FONT_SIZE));
lvHcs.setBorderTop(pBorderType);
lvHcs.setTopBorderColor(pBorderColor);
lvHcs.setBorderLeft(pBorderType);
lvHcs.setLeftBorderColor(pBorderColor);
lvCell.setCellStyle(lvHcs); //lvCell's class is HSSFCell
Comment 1 Nick Burch 2016-02-05 13:11:54 UTC
Microsoft Office imposes a limit on the number of styles that a Workbook can maintain. That's why you ideally need to create your styles once and re-use them, or failing that optimise the styles after creation. (The limit imposed by office is quite a bit lower than what the file format can actually cope with). See https://support.office.com/en-GB/article/Excel-specifications-and-limits-16c69c74-3d6a-4aaf-ba35-e6eb276e8eaa
Comment 2 manisha 2017-08-21 09:55:29 UTC
Hi Nick Burch, even I am having the same problem and the link of MS Office Excel you have shared, shows same number of, maximum number of cell styles (64,000) in MS Excel 2010 and MS Excel 2013. In that case, how is the maximum of cell styles limiting MS Excel 2013 to show some cell styles while MS Excel 2010 is able to show those cell styles?