How to set "Default Style" or "Range Style" in Excel

Sep 8, 2010 at 5:34 AM

I have tested the cell , row, col styles. They all are great !! What about the default style and range style ?

I understand that these can be done using multiple cells, however, when I open the file with older version of Excel, I receive "Too many cells format" error.

Any ideas ?

 

Oct 29, 2010 at 6:27 AM

I second this question.

I have a sheet with thousands of rows and many date columns. I get "Too many different cell formats" error too.

I have no knowledge of the BIFF format, but i thought it worked a bit like the xlsx format (and html/css) where there can be a global styles list, then each of the cells can just reference the one little list.

Ideas anyone?

Nov 8, 2010 at 10:12 PM

Hi, it is exactly like you described, there is a global list of styles, all Versions of Excel have a limit of possible Styles, i assume the limit for 2003 is 128 or 256 (test it!).

If you create a style you can find it in the list of styles of the workbook (you can access them via several methods of hssfworkbook or hold your reference list). Do not create the same style over and over again:

 

Wrong :


for (int i = 0; i < 10000; i++) {

Row row = sheet.createRow(i);

Cell cell = row.createCell((short) 0);

CellStyle style = workbook.createCellStyle();

Font font = workbook.createFont();

font.setBoldweight(Font.BOLDWEIGHT_BOLD);

style.setFont(font);

cell.setCellStyle(style);

}



Correct:


CellStyle style = workbook.createCellStyle();

Font font = workbook.createFont();

font.setBoldweight(Font.BOLDWEIGHT_BOLD);

style.setFont(font);

for (int i = 0; i < 10000; i++) 
{

	Row row = sheet.createRow(i);

	Cell cell = row.createCell((short) 0);

	cell.setCellStyle(style);

}

 

Sample from: 
http://www.eggheadcafe.com/software/aspnet/33364876/getting-maximum-number-of-fonts-have-been-exceeded-error.aspx 

 

Nov 11, 2010 at 12:32 AM

I've seen this problem a million times, people creating tons of style because they were created per/cell versus per/workbook or worksheet.  If you are going to reuse a rule multiple times, create it once and reference that exact same rule each time.  You can cause this exact same error if you like to do lots of formatting in Excel.