Why would you want to delete the style after copying/cloning it? If I
remember correctly, your original problem was that if you modified an
existing cell style to change the appearance of a cell then all other cells
that the style had been applied to also changed. If you remove a style then
that will also affect any others cells that style object may have been
applied to.
Here is the very basic first step that we developed;
/**
* Test code written to see if it is possible to change the style applied to
* a cell by recovering, copying and modifying an applied HSSFCellStyle
* instance. The class contains a possible simple style pool.
* @author Mark Beardsley [msb at apache.org]
/*
public class CopyCloneTest {
// Will need to experiment to find a 'safe' separator.
private static final String SEPARATOR = ",";
/**
* Recover the HSSFCellStyle object applied to a cell, specify
modifcations
* to it's foreground colour, background colour and fill pattern, check
to
* see if the worksheet already contains such a style and create a new
* HSSFCellStyle object if necessary.
*
* @param filename A String encapsulating the name of and path to a Word
* document. Currently, the code operates successfully
on
* binary (OLE2CDF) format files with the .doc or .dot
* extensions.
* @throws java.io.IOException Thrown if a problem occurs within the
* underlying file system whilst reading
from
* or writing to the file.
* @throws java.io.FileNotFoundException Thrown if the Word document
cannot
* be located.
*/
public void cloneTest(String filename) throws IOException,
FileNotFoundException {
File file = null;
FileInputStream fis = null;
FileOutputStream fos = null;
HSSFWorkbook workbook = null;
HSSFSheet sheet = null;
Hashtable<String, HSSFCellStyle> stylesCollection = null;
Iterator<Row> rowIter = null;
Iterator<Cell> cellIter = null;
Row row = null;
Cell cell = null;
HSSFCell hssfCell = null;
try {
// Instantiate the Hashtbale. This will contain all of the
styles
// that we create.
stylesCollection = new Hashtable<String, HSSFCellStyle>();
// Open the workbook and then get the first sheet. Of course, it
// would be straightforward to modify this so that each sheet
// were recovered from the workbook and processed in turn.
file = new File(filename);
fis = new FileInputStream(file);
workbook = new HSSFWorkbook(fis);
fis.close();
fis = null;
sheet = workbook.getSheetAt(0);
// Recover an Iterator to step through the rows the sheet
contains.
rowIter = sheet.rowIterator();
while(rowIter.hasNext()) {
row = rowIter.next();
// From the row, get an Iterator to step through the cells
the
// rows contain.
cellIter = row.cellIterator();
while(cellIter.hasNext()) {
cell = cellIter.next();
// Note the cast here from Cell to HSSFCell. The code
// may be changed so that it takes and works with Cells
// quite easilly.
hssfCell = (HSSFCell)cell;
// This is the heart of it all really. The copyStyle()
// method takes care of recovering the attributes from
the
// cells current style, including the changes required -
in
// this case limited to the background colour,
foreground
// colour and fill pattern in that order.
hssfCell.setCellStyle(copyStyle(workbook,
hssfCell.getCellStyle(),
stylesCollection,
HSSFColor.WHITE.index,
HSSFColor.LIGHT_BLUE.index,
HSSFCellStyle.THIN_FORWARD_DIAG));
}
}
// Save the workbook away.
fos = new FileOutputStream(file);
workbook.write(fos);
}
finally {
if(fis != null) {
try {
fis.close();
fis = null;
}
catch(IOException ioEx) {
// I G N O R E
}
}
if(fos != null) {
try {
fos.close();
fos = null;
}
catch(IOException ioEx) {
// I G N O R E
}
}
}
}
/**
* Checks to see whether a style with the required set of attributes has
* already been created. If so, it will return a reference to that
style, if
* not, it will create and return a new style object with the required
* attributes.
*
* Note: I did not make this method static for any technical reason,
just to
* make playing with the code alittle easier. Also note that the first
* parameter is a reference to a workbook; this must be the workbook the
* cell was originally recovered from as this method of copying styles
will
* work ONLY if a single workbook is involved. Depending on where you
sit
* along the 'cohesion' versus 'coupling' continuum, more that one of
these
* parameter could be removed; if, for example, the workbook and
* stylesCollection Hashtable were private instance variables and the
method
* was NOT static, then these parameters could be removed and the
variables
* directly accessed.
*
* @param workbook The workbook from which the cell was recovered.
* @param originalCellStyle The style object recovered from a cell.
* @param stylesCollection A Hashtable that contains the collection of
* styles created for use within the workbook.
* @param backgroundColour A primitive short containing the index of the
* required/desired background colour for the
cell.
* @param foregroundColour A primitive short containing the index of the
* required/desired foreground colour for the
cell.
* @param fillPattern A primitive short containing the index of the
* required/desired fill patter for the cell.
* @return A cell style either recovered from the collection or newly
* created that encapsulates the required set of attributes.
*/
private static HSSFCellStyle copyStyle(HSSFWorkbook workbook,
HSSFCellStyle originalCellStyle, Hashtable<String,
HSSFCellStyle> stylesCollection, short backgroundColour,
short foregroundColour, short fillPattern) {
HSSFCellStyle cellStyle = null;
// Convert the styles attributes into a delimited String; note that
// this is the stage where the required changes to the background
// colour, foreground colour and fill pattern are specified.
String styleKey = getStyleKey(originalCellStyle, backgroundColour,
foregroundColour, fillPattern);
// If the styles collection contains a suitable style, return that.
if(stylesCollection.contains(styleKey)) {
cellStyle = stylesCollection.get(styleKey);
}
else {
// If not, create a new style within the workbook.
cellStyle = workbook.createCellStyle();
// Set that styles attributes and add it to the collection.
setStyleAttributes(workbook, cellStyle, styleKey);
stylesCollection.put(styleKey, cellStyle);
}
return(cellStyle);
}
/**
* Set the attributes for the cell style.
*
* @param workbook A reference to the workbook that contains the cell.
* Required only to convert the font's index into an
* instance of the HSSFFont class to successfully set
that
* attributes. Again, the comments made with regard to
* coupling, cohesion and static methods applies as it
did
* above.
* @param cellStyle The cell style whose attributes are to be set.
* @param styleAttributes An instance of the String class encapsulating
a
* comma delimited String that contains the set
of
* attributes for a style.
* @throws java.lang.IllegalArgumentException Thrown if the number of
* attribute values recovered
* from the styleAtributes
* parameter is 'incorrect'.
*/
private static void setStyleAttributes(HSSFWorkbook workbook,
HSSFCellStyle cellStyle, String styleAttributes)
throws
IllegalArgumentException {
String[] attrArray = styleAttributes.split(SEPARATOR);
if(attrArray.length != 20 ) {
throw new IllegalArgumentException("Incorrect number of" +
" style attributes.");
}
cellStyle.setAlignment(Short.valueOf(attrArray[0]));
cellStyle.setBorderBottom(Short.valueOf(attrArray[1]));
cellStyle.setBorderLeft(Short.valueOf(attrArray[2]));
cellStyle.setBorderRight(Short.valueOf(attrArray[3]));
cellStyle.setBorderTop(Short.valueOf(attrArray[4]));
cellStyle.setBottomBorderColor(Short.valueOf(attrArray[5]));
cellStyle.setDataFormat(Short.valueOf(attrArray[6]));
// Javadoc stipulates setting foreground before background.
cellStyle.setFillForegroundColor(Short.valueOf(attrArray[8]));
cellStyle.setFillBackgroundColor(Short.valueOf(attrArray[7]));
cellStyle.setFillPattern(Short.valueOf(attrArray[9]));
cellStyle.setFont(workbook.getFontAt(Short.valueOf(attrArray[10])));
// Convert from numeric value to boolean
cellStyle.setHidden(Short.valueOf(attrArray[11]) == 1 ? true :
false);
cellStyle.setIndention(Short.valueOf(attrArray[12]));
cellStyle.setLeftBorderColor(Short.valueOf(attrArray[13]));
cellStyle.setLocked(Short.valueOf(attrArray[14]) == 1 ? true :
false);
cellStyle.setRightBorderColor(Short.valueOf(attrArray[15]));
cellStyle.setRotation(Short.valueOf(attrArray[16]));
cellStyle.setTopBorderColor(Short.valueOf(attrArray[17]));
cellStyle.setVerticalAlignment(Short.valueOf(attrArray[18]));
cellStyle.setWrapText(Short.valueOf(attrArray[19]) == 1 ? true :
false);
}
/**
* Creates a delimited 'list' of the set of attributes recovered from
* a cell style object.
*
* @param originalCellStyle The cell style object that will be
interrogated
* for it's attributes.
* @param backgroundColour A primitive short that contains the index for
* a colour within a workbook. Note that this is
the
* colour for the background of a cell.
* @param foregroundColour A primitive short that contains the index for
* a colour within a workbook. Note that this is
the
* colour for the foreground of a cell.
* @param fillPattern A primitive short that contains the index for
* a fill patter within a workbook.
* @return An instance of the String class encapsulating a delimited
list
* of the cell styles attributes.
*/
private static String getStyleKey(HSSFCellStyle originalCellStyle,
short backgroundColour, short foregroundColour, short
fillPattern) {
StringBuilder builder = new StringBuilder();
builder.append(originalCellStyle.getAlignment());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getBorderBottom());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getBorderLeft());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getBorderRight());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getBorderTop());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getBottomBorderColor());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getDataFormat());
builder.append(SEPARATOR);
// Rather than get what the cell contains for theses next three
// attributes - background colour, foreground colour and fill
pattern -
// substitute what we want to see instead.
builder.append(backgroundColour);
builder.append(SEPARATOR);
builder.append(foregroundColour);
builder.append(SEPARATOR);
builder.append(fillPattern);
builder.append(SEPARATOR);
builder.append(originalCellStyle.getFontIndex());
builder.append(SEPARATOR);
// Convert boolean values into numeric vaues; true will become 1 in
the
// list and false 0.
builder.append(originalCellStyle.getHidden() ? 1 : 0);
builder.append(SEPARATOR);
builder.append(originalCellStyle.getIndention());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getLeftBorderColor());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getLocked() ? 1 : 0);
builder.append(SEPARATOR);
builder.append(originalCellStyle.getRightBorderColor());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getRotation());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getTopBorderColor());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getVerticalAlignment());
builder.append(SEPARATOR);
builder.append(originalCellStyle.getWrapText() ? 1 : 0);
return(builder.toString());
}
}
It was designed to meet a very specific need - the changes that can be made
to the style recovered from an existing cell on a worksheet are limited to
foreground colour, background colur and fill patter but they could easilly
be extended. The styles are pooled using a Hashtable that is keyed on a
String obhject that contains what you could call the signature of the style.
The aim was to create something that would allow you to say, in effect, 'Has
a style with these attributes already been created?' If the answer is 'yes'
then that style will be returned, if 'no' then a style with the required
attributes will be created and returned. The key to the action is the
copyStyle() method.
I will not have the time to do so but was intending to develop something
along these lines as an example for POI. Originally I envisaged establishing
a baseline style - simply using Excel to create a worksheet with a single
cell on it, writing some text into that cell and then saving the workbook
away. Next, use POI to open the workbook and profile the cells style using
my code to establish what the signature of a basic, empty style is. This
would then serve as the basis for new styles and you could specify that it
be modified to, for example, set a border around the cell, change the colour
of that border, etc. It may be best to create another class that contains a
Hashtable of attributes - and I think there are twenty. These could be keyed
using names such as 'RIGHT_BORDER_COLOUR' and so on. Then when you wish to
create a new style, you set the values of the various attributes in an
instance of this class, pass that a method that would test to see if such a
style exists and, if not create and return one for you. That way you might
have much more flexibility.
This approach will however ONLY work if you are copying/cloning styles
within a workbook. If you want to copy styles between two or more workbooks
then you will need to use the cloneStyleFrom() method that has been defined
on the HSSFCellStyle class.
Yours
Mark B
Khanh Tran-3 wrote:
>
> Hello MSB,
> I understand your solution, but how to 'delete' the old cell style after
> clone it to new one, if any cell have his own style.
> I looked into the threads you mentioned and could not find the style pool
> implementation. Could you please send some example?
> Thanks
>
> -----Original Message-----
> From: MSB [mailto:[email protected]]
> Sent: Monday, August 17, 2009 3:09 PM
> To: [email protected]
> Subject: RE: How to set different types of fomat and style to different
> cell
>
>
> Can I check that all you want to do is to change the colour of a cell for
> which you have previously created a cell style without affecting any other
> cells that style may have been applied to?
>
> If that is the case, then the answer is simple, well fairly, get the style
> from the cell, clone or copy that style, modify the attribute(s) you want
> to
> change and then apply the modified style to the cell.
>
> If you look back through the posts made to the list recently, there is one
> by another member called yehogold I think. We have been working on
> something
> similar for him and that example also includes a possible style pool; not
> saying this IS the answer to your problem but I think it does point a way
> forward.
>
> Must go now, I am getting shouted at by my colleagues because we have a
> long
> day ahaead of us pulling himalyan balsam at a local nature reserve. I will
> be back later today and if you have any questions post them to the list
> and
> I will look at them then; please do not think that if I do not reply for
> while I am ignoring you.
>
> Yours
>
> Mark B
>
>
> Khanh Tran-3 wrote:
>>
>> Hello MSB,
>> Thannks for quick answer, but I still run into the next problem.
>>
>> Provided that I want to set cell color for a cell, then I:
>> cellStyle = workbook.createCellStyle();
>> ...
>> Cell.setCellStyle(cellStyle);
>>
>> And latter, I want to change the color for that cell,
>> I get back the cellstyle object that created before and change it's
>> properties:
>>
>> cellStyle = cell.getCellStyle();
>> ....
>>
>> The problem is that for some cell(blank cells), getCellStyle() return the
>> default cellstyle that are shared with many cells on workbook. Changing
>> this
>> will affect so many another cells that I don't' want to.
>>
>> How can we handle this?
>> Thanks
>>
>> -----Original Message-----
>> From: MSB [mailto:[email protected]]
>> Sent: Monday, August 17, 2009 12:37 PM
>> To: [email protected]
>> Subject: RE: How to set different types of fomat and style to different
>> cell
>>
>>
>> Yes, but would this not be the case if you did exactly the same with
>> Excel
>> itself? If not, I cannot imagine how the file would be structured; the
>> style
>> information has to be stored somewhere even if it is only the differences
>> from a notaional 'base' style that are stored.
>>
>> Yours
>>
>> Mark B
>>
>>
>> Khanh Tran-3 wrote:
>>>
>>> Hello,
>>>
>>> What if I programmatically format 500 cells with difference styles?
>>>
>>> The target spreadsheet now contains huge number of styles.
>>>
>>> Khanh
>>>
>>> -----Original Message----
>>>
>>>
>>>
>>> A cellstyle can be "shared" by many cells in a workbook. Currently you
>>> are
>>>
>>> modifying the "shared" cellstyle, and this affects all cells that use
>>> that
>>>
>>> style.
>>>
>>>
>>>
>>> Instead, try to create a new style using HSSFWorkbook.createCellStyle(),
>>> and
>>>
>>> then use cell.setCellStyle(HSSFCellStyle).
>>>
>>>
>>>
>>> In other words, replace:
>>>
>>>
>>>
>>> HSSFCellStyle cellStyle = cell.getCellStyle();
>>>
>>>
>>>
>>> with:
>>>
>>>
>>>
>>> HSSFCellStyle cellStyle = workbook.createCellStyle();
>>>
>>> cell.setCellStyle(cellStyle);
>>>
>>>
>>>
>>> - Rob
>>>
>>>
>>>
>>
>> --
>> View this message in context:
>>
> http://www.nabble.com/RE%3A-How-to-set-different-types-of-fomat-and-style-to
>> -different-cell-tp25000554p25001251.html
>> Sent from the POI - User mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>>
>>
>
> --
> View this message in context:
> http://www.nabble.com/RE%3A-How-to-set-different-types-of-fomat-and-style-to
> -different-cell-tp25000554p25002734.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
--
View this message in context:
http://www.nabble.com/RE%3A-How-to-set-different-types-of-fomat-and-style-to-different-cell-tp25000554p25019091.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
