How to format a column instead of cell in POI hssf

Question

I'm facing an issue when opening a excel file with hundreds/thousands of rows of data, "Too Many Different Cell Formats"

More info about the error
http://excelzoom.com/2009/09/the-mystery-of-excels-too-many-different-cell-formats/

So I'm trying to format a column not a cell. The only way I can figure out how to format a column is by formating each individual cell; by implementing the code listed below. I would like to format a column instead of a row. I have hundreds/thousands of rows of data to format. Any suggestions would be very much appreciated.

Thanks,

HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFDataFormat dataFormat = wb.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("0.00"));

HSSFRow row = sheet.createRow(iRow);
HSSFCell cell = row.createCell((short)1);
cell.setCellStyle(cellStyle);

Answer 1

There is a much better way, at least when you work with the "new" Excel format. Use:

((XSSFSheet)sheet).getColumnHelper().setColDefaultStyle(col, myStyle);

Using this will ensure the styles continue even below the rows where you inserted data. And the resulting file will also be smaller.

Answer 2

Found the answer here

http://poi.apache.org/faq.html#faq-N1014B go to the section

13. I am using styles when creating a workbook in POI, but Excel refuses to open the file, complaining about "Too Many Styles".

Sam