CODEWITHSUNDEEP
excelapache-poi
Sam
Sam

Reputation: 6395

How to format a column instead of cell in POI hssf

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);

Upvotes: 2

Views: 23817

Answers (3)

Sam
Sam

Reputation: 6395

Found the answer here

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

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

Sam

Upvotes: 5

Daniel
Daniel

Reputation: 28074

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.

Upvotes: 7

Swapnil
Swapnil

Reputation: 11

As you have not defined you CellStyle object outside Loop so it is creating every time new object for the Workbook so creating multiple object every time throws error.

So create CellStyle object at higher level in class & refer to same object in Loop with wb.createCellStyle();

Upvotes: 0

Related Questions