Yes, if you change the color of a cell style it will change for all cells that use that style. There is also a limit on the number of styles that you can create in the workbook. Typically, I save styles that are used and create new ones if the need arises.
I have included an excerpt of my code that should help you out.
}
}
}
* to display the numbers in black. Red on red is not visible.
*/
||
.getBuiltinFormat(
}
}
}
} publicvoidcolorCell(intcell, intcolor) throwsException { if(key== null) { key= newKey(); if(styleMap== null) { styleMap= newStyleMap(); if(dataFormat== null) { dataFormatWorkBook= newWorkbook(); dataFormat= newHSSFDataFormat(dataFormatWorkBook); current= row.getCell(cell).getCellStyle(); /* If the current cell color equals red overide the data format if(dataFormat.getFormat(current.getDataFormat()).equals( "($#,##0.00_);[Red]($#,##0.00)") dataFormat.getFormat(current.getDataFormat()).equals( "($#,##0_);[Red]($#,##0)")) { if(Color.RED== color) { if(blackDollarCellStyle== null) { blackDollarCellStyle= workBook.createCellStyle(); blackDollarCellStyle.setDataFormat(HSSFDataFormat "($#,##0.00);($#,##0.00)")); row.getCell(cell).setCellStyle(blackDollarCellStyle); current= row.getCell(cell).getCellStyle(); key.setDataFormat(dataFormat.getFormat(current.getDataFormat())); key.setColor(Color.getColorName(color));
key.setFontType(Integer.toString(current.getFontIndex())); if(styleMap.containsStyle(key.toString())) { newStyle= styleMap.getStyle(key.toString()); else{ newStyle= clone(current); newStyle.setFillForegroundColor((short) color); newStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleMap.add(key.toString(), newStyle); // System.out.println(key.toString()); }
} row.getCell(cell).setCellStyle(newStyle);
* This method clones a copy of the current cell. This method is a work
* around because HSSFCellStyle does not implement Clonable.
*/
}
* Creates an excel row.
*/
createSheet(
+
}
}
}
}
} /* privateHSSFCellStyle clone(HSSFCellStyle style) { returnStyle= workBook.createCellStyle(); returnStyle.setAlignment(style.getAlignment()); returnStyle.setBorderBottom(style.getBorderBottom()); returnStyle.setBorderLeft(style.getBorderLeft()); returnStyle.setBorderRight(style.getBorderRight()); returnStyle.setBorderTop(style.getBorderTop()); returnStyle.setBottomBorderColor(style.getBottomBorderColor()); returnStyle.setDataFormat(style.getDataFormat()); returnStyle.setFillBackgroundColor(style.getFillBackgroundColor()); returnStyle.setFillForegroundColor(style.getFillForegroundColor()); returnStyle.setFillPattern(style.getFillPattern()); returnStyle.setFont(workBook.getFontAt(style.getFontIndex())); returnStyle.setHidden(style.getHidden()); returnStyle.setIndention(style.getIndention()); returnStyle.setLeftBorderColor(style.getLeftBorderColor()); returnStyle.setLocked(style.getLocked()); returnStyle.setRightBorderColor(style.getRightBorderColor());
returnStyle.setRotation(style.getRotation()); returnStyle.setTopBorderColor(style.getTopBorderColor()); returnStyle.setVerticalAlignment(style.getVerticalAlignment()); returnStyle.setWrapText(style.getWrapText()); returnreturnStyle; /* privatevoidmakeRow() { try{ if(rowNumber>= 65536) { this.sheetName+ "_"workBook.getSheetIndex(sheetName) + 1); row= sheet.createRow(rowNumber); catch(Exception e) { errorMessage= e.getMessage(); /*
* The StyleMap class is a holding location for styles that are created within the workbook.
* Excel will bark if you create too many styles. This class provides a container for styles
* that have been previously created and allows you to access them by key.
*/class
}
}
}
}
} StyleMap { privateHashMap styleMap; publicStyleMap() { styleMap= newHashMap(); publicvoidadd(String key, HSSFCellStyle style) { styleMap.put(key, style); publicbooleancontainsStyle(String key) { returnstyleMap.containsKey(key); publicHSSFCellStyle getStyle(String key) { return(HSSFCellStyle) styleMap.get(key); /*
* A simple style key for the StyleMap class.
*/class
}
}
}
}
}
}
}
Key that = (Key) obj;
&&
}
}
StringBuffer toString =
toString.append(
toString.append(
toString.append(
toString.append(
toString.append(
}
} Key { privateString dataFormat; privateString fontType; privateString color; publicKey() { publicString getDataFormat() { returndataFormat; publicvoidsetDataFormat(String dataFormat) { this.dataFormat= dataFormat; publicString getFontType() { returnfontType; publicvoidsetFontType(String fontType) { this.fontType= fontType; publicString getColor() { returncolor; publicvoidsetColor(String color) { this.color= color; publicbooleanequals(Object obj) { return(this.dataFormat== that.dataFormatthis.fontType== that.fontType&& this.color== that.color); publicinthashCode() { returnthis.hashCode(); publicString toString() { newStringBuffer(); this.dataFormat); " - "); this.fontType); " - "); this.color); returntoString.toString();
________________________________
From: James Perkins <jrperkinsjr@xxxxxxxxx>
To: Java Programming on and around the iSeries / AS400 <java400-l@xxxxxxxxxxxx>
Sent: Wednesday, August 5, 2009 1:06:41 PM
Subject: Re: Problem in POI.jar - unreferenced cells changing color
You would have to create a new cell style. I've not dug into this part of
the POI code before, but I think there is a limit of 255 cell styles per
workbook. (I'm going off memory here, so my number could be off.)
Basically, the cell style is attached to the workbook. So if you change a
cell style to have a font of red, then any cell that uses that cell style
will have a red font. It would probably be best to create a error_cell_style
field.
You might also look at HSSFCell.setCellType(HSSFCell.CELL_TYPE_ERROR). You
might be able to work with something there.
--
James R. Perkins
http://twitter.com/the_jamezp
On Wed, Aug 5, 2009 at 09:21, Marvin Radding <
marvin.radding@xxxxxxxxxxxxxxxxxxxxx> wrote:
Hi all,
I am writing a program to read a excel spreadsheet and validate the
data. When an error is detected, I change the color of the font to red
and insert an error message in a cell further down the row.
The problem I am having is that when I change a cell's font color, the
adjacent cells also change color. I guess all these cells have the same
cell style and changing one will change them all.
What I need to do is retrieve the current cell style, change the font
color, then change the cell without changing all the adjacent cells.
Here is the RPG/LE code. This code is part of a subprocedure that
handles changing the cell color and inserting the error message. This
code deals with changing the cell color. I have to do it this way
because some of the cells have special formatting, like a date format
for instance.
// get cell style
cell_style = HSSFCell_getCellStyle(cell);
// change font to red letter
HSSFCellStyle_setFont(cell_style: red_font);
// set style of cell - red letter
HSSFCell_setCellStyle(cell:cell_style);
I am certain that the program only changes one cell. What I don't
understand is how and why the others are changing.
I have done a test but I don't know what it means. I changed the color
of all the surrounding cells to different colors and then only the cell
in error changed to red and not the adjacent ones.
In another test, I created another cell style and used that cell style
to change the font color of the cell but that didn't work either. The
adjacent cells also changed color.
Does anyone have any ideas on what is happening and how I can change
only the cell I want to change?
Thanks in advance.
Marvin
--
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.
midrange.com
