This morning, I had the chance to play around with some code and feel that I
may, that is MAY, have found a way forward. Please find attached some code
that I hope will explain what I am trying to do. It has been targetted at
the HSSF stream but I think that it will be quite easy to modify it to work
with the 'ss' classes; in one way, it is halfway there as I am using
Iterators to get at the cells on the sheet.
Perhaps the first aspect to take a look at is what I am doing with the cell
styles. As you will be able to see, I have chosen not to use the
cloneStyleFrom() method because you will be copying/cloning styles WITHIN a
workbook rather than between workbooks. As a result, lots of problems are
removed and so it is easier and better - for reasons I will explain below -
to not use the cloneStyleFrom() method here.
The reason I have written my own method to copy/clone the styles is that I
want to avoid adding styles into the workbook if a style with the required
attributes already exists. As a result of this decision, I have implemented
style pooling using a Hashtable to organise and manage the styles for me.
When the time arrives to 'modify' a cells style, this Hashtable is checked
to see if it contains one with the required set of attributes. If it does,
then this style will be reused and if the pool does not contain a suitable
style, a new one is created, written away to the Hashtable and then returned
for use.
The equals() method is overridden in both of the concrete implementations of
CellStyle (XSSFCellStyle and HSSCellStyle) so one option might have been to
hold the styles in an ArrayList rather than a Hashtable and simply step
through looking for a match using the equals method. The problem with this
is that it again means actually creating the style object we want in order
to perform the tests. To create the style means adding it to the workbook
and I cannot find a way to remove it once it has been added; though I
suspect there is a way using indices.
So, I am going to use the Hashtable approach and have everything keyed on a
long String. As you can see, if you look at the getStyleKeyMethod(), I am
simply creating a delimited list containing the values of the styles various
attributes - boolean values are converted into a short, 1 for true and 0 for
false. Once assembled, this String is both the 'signature' for the style and
the key for the Hashtable holding 'all' - well, any we create explicitly -
of the sheets styles. Checking simply consists of interrogating the style to
create this String and using that as the key in a call to the Hashtable's
contains() method. Creating the style if a new one is required can also be
accomplished from this String; simply strip it apart, convert each element
back to a sort or boolean value and use that to set an attribute of the new
style.
Testing had to be quite quick for me this morning - autumn is drawing on,
the garden is calling and for once, it is NOT raining - but I think this
approach could be promising. There are two points that do concern me
currently. The first concerns format strings and I need to dig around
further to make sure that it is possible to recover and reset these
correctly. The second concerns the delimiter character that is used to
separate items in the attribute String. Currently, I am using a comma but
can see that may cause problems in locales where this is the decimal
separator and if we start to copy and re-use formatting Strings as it could
well appear in one of those. I have tested a few other characters and
discovered that the split() method is not willing to use them all but I feel
that this may be more a problem with my understanding of regular expressions
than anyting else. Secondly, I am not testing to see if the cells existing
style ought to be replaced as you do in your code; rather my example simply
replaces the style of the cell with the one I have prescribed regardless.
However, I am confident you can see what to change to add the extra check in
and so I will not bother making that change.
With all of that said, here is the code - forgive me please as it is very
messy being test code and all that.
public class CopyCloneTest {
// Will need to experiment to find a 'safe' separator.
private static final String SEPARATOR = ",";
/**
* Test code written to see if it is possible to cahnge the style
applied to
* a cell.
*
* @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());
}
}
Yours
Mark B
yehogold wrote:
>
> I just realized that I had made an error when experimenting with your
> code. I had been looking for the modified cell on the wrong worksheet.
> The code actually did work. Sorry about that. :wistle:
>
> I am actually using the 'ss' for the program, if that makes a difference.
>
> Thank you,
>
> yehogold
>
>
> MSB wrote:
>>
>> I surprised to read this paragraph in your reply to my last message;
>>
>> "I experimented with your code and I've found that it works, assuming
>> that the cell we are cloning from is from a seperate sheet. If I clone
>> from the same sheet, even if it is a different cell, the program doesn't
>> change the style of the cell at all."
>>
>> because I tested the code using one sheet from the workbook you attached
>> to a previous message, I cloned the style from cell B3 on Sheet2,
>> modified it and then applied it to cell B7 also on Sheet2 if I remember
>> correctly so it's surprising that you are experiencing problems. Are you
>> using what I refer to as the ss (Workbook, Row, Clee, etc) classes so
>> that you can process either binary or OpenXML workbooks without having to
>> worry about the format? I wonder if this could be the cause; will
>> re-write the code to target these classes and test it again to see if I
>> am correct.
>>
>> One thing we could think of is writing our own clone method. The original
>> was added to support cloning styles from different workbooks and in this
>> case, there is no need to worry about whether the workbook has a specific
>> font installed, for example, we can assume it does. Will try to put
>> together something very quick and dirty this weekend to see how that
>> works; the added advantage of this is that we could also use it as tha
>> basis for the style pooling - look in the collection for a style with
>> these attributes and if one is not found create it, add it to the
>> collection and return.
>>
>> Yours
>>
>> Mark B
>>
>>
>> yehogold wrote:
>>>
>>> Depending on the workbook, it is possible that a large number of cells
>>> will need to be marked, although I am unsure if it will hit the limit of
>>> the number of styles (I think its in the 1000s). In any case, I was
>>> planning on eventually implementing some sort of 'style pooling' simply
>>> to keep the files from getting too big.
>>>
>>> I experimented with your code and I've found that it works, assuming
>>> that the cell we are cloning from is from a seperate sheet. If I clone
>>> from the same sheet, even if it is a different cell, the program doesn't
>>> change the style of the cell at all.
>>>
>>> For the application that I am writting, I was planning on cloning the
>>> style not only from the sheet I am marking, but from the exact cell that
>>> I am marking. The reason is that different cells may have different
>>> style in the workbook and I am trying to just simply add red strips to
>>> the current style of the cell. I'm starting to wonder if this is
>>> possible. Is there a restriction regaurding what cells you may set to a
>>> particular cloned cellStyle?
>>>
>>> Also, the WorkbookFactory did solve my other issue. Thank you for the
>>> advice.
>>>
>>> yehogold
>>>
>>>
>>>
>>> MSB wrote:
>>>>
>>>> OK, will have a look at the workbooks a little later.
>>>>
>>>> I do have one further question; do you have any idea of roughly how
>>>> many of these cells you will have to alter? The reason I ask is that
>>>> Excel places a limit on the number of different format/style objects
>>>> you can create - at least the .xls binary format does, I am not so
>>>> certain about the OpenXML based one. As a result, we may need to look
>>>> into 'style pooling' if you are going to be altering the format on a
>>>> reasonably large number of cells. This should be quite straightforward
>>>> to implement - use some form of Collections object to manage the styles
>>>> and check to see if one already exists with the attributes we require
>>>> before creating a new one - but it is an extra wrinkle to take into
>>>> consideration.
>>>>
>>>> If I have the time over the weekend, I will try to put something
>>>> together.
>>>>
>>>> Yours
>>>>
>>>> Mark B
>>>>
>>>> PS Did the WorkbookFactory solve your other problem?
>>>>
>>>>
>>>> yehogold wrote:
>>>>>
>>>>> It sounds like you've got it. I will be removing the highlighting
>>>>> applied to the cells using the POI. Here is the function I currently
>>>>> have written to do that:
>>>>>
>>>>> public void clearMark(Cell cell)
>>>>> {
>>>>> if (cell.getCellStyle().getFillPattern() ==
>>>>> CellStyle.THIN_BACKWARD_DIAG)
>>>>> {
>>>>> Workbook wb = cell.getSheet().getWorkbook();
>>>>> CellStyle clearStyle = wb.createCellStyle();
>>>>>
>>>>> clearStyle.cloneStyleFrom(cell.getCellStyle());
>>>>>
>>>>> clearStyle.setFillForegroundColor(Font.COLOR_NORMAL);
>>>>> clearStyle.setFillPattern(CellStyle.NO_FILL);
>>>>>
>>>>> cell.setCellStyle(clearStyle);
>>>>> }
>>>>> }
>>>>>
>>>>> I've tried testing it on its own, using cells that I marked manually
>>>>> with the red-strips using Excel. It mostly works, however it does
>>>>> cause two bugs.
>>>>>
>>>>> First, although it retains the font and text color of the cell, it
>>>>> does not retain the background color of the cell.
>>>>>
>>>>> Second, when run on a .xlsx file, Excel complains that it found
>>>>> unreadable content in the workbook. It can repair the problem and
>>>>> open the workbook, but gives you a warning message when you open it.
>>>>>
>>>>> I am attaching 4 versions of a test work book. The two book1's are
>>>>> identical workbooks before the above function was run on them in two
>>>>> different formats, book1_after's are the resultant files after the
>>>>> function was run.
>>>>>
>>>>> Thank you,
>>>>>
>>>>> yehogold
>>>>>
>>>>> http://www.nabble.com/file/p24972448/Book1.xls Book1.xls
>>>>> http://www.nabble.com/file/p24972448/Book1.xlsx Book1.xlsx
>>>>> http://www.nabble.com/file/p24972448/Book1_after.xls Book1_after.xls
>>>>> http://www.nabble.com/file/p24972448/Book1_after.xlsx Book1_after.xlsx
>>>>>
>>>>>
>>>>>
>>>>> MSB wrote:
>>>>>>
>>>>>> At the risk of repeating myself, I want to be completely clear about
>>>>>> this;
>>>>>>
>>>>>> You are reading in an existing workbook.
>>>>>> You want to clone the style from one of the cells of that workbook
>>>>>> and then modify it such that the forground consists of a pattern of
>>>>>> diagonal red bars. This format will be used to mark or highlight
>>>>>> cells that conform to a specific criteria and you will be
>>>>>> highlighting - applying the style to - them using POI.
>>>>>> Later, you may want to re-set or remove the highlighting applied to
>>>>>> the cells. How will this be done, through POI or manually. If done
>>>>>> through POI, how will you accomplish this? I only ask because if you
>>>>>> modify a style that has been applied to one or more cells, every cell
>>>>>> that style has been applied to will be affected - so it would be
>>>>>> better to change the style applied to another one.
>>>>>>
>>>>>> Will proceed on this premise but cannot promise anything soon.
>>>>>>
>>>>>> Yours
>>>>>>
>>>>>> Mark B
>>>>>>
>>>>>>
>>>>>> yehogold wrote:
>>>>>>>
>>>>>>> I am, indeed, cloning a cellStyle from the same workbook.
>>>>>>>
>>>>>>> The point of this portion of the program is to mark the cell of an
>>>>>>> existing workbook that was created using EXCEL, not the POI.
>>>>>>> Therefore, I am opening and reading in the workbook.
>>>>>>>
>>>>>>> What I would like to do is to mark the cell with red diagonal lines
>>>>>>> in the foreground. I would like the rest of the format to remain
>>>>>>> the same as it was before, allowing me to "unmark" it at a later
>>>>>>> point, returning it to its original format. Therefore, if the
>>>>>>> background is white, I would like it to stay white, if it is blue, I
>>>>>>> would like it to stay blue, if the font is bold, I would like it to
>>>>>>> stay bold, etc.
>>>>>>>
>>>>>>> Thank you again for spending so much time on this,
>>>>>>>
>>>>>>> yehogold
>>>>>>>
>>>>>>>
>>>>>>> MSB wrote:
>>>>>>>>
>>>>>>>> Sorry about BiffViewer. I was assuming that you were cloning a
>>>>>>>> style from one workbook for use in a different workbook. BiffViwere
>>>>>>>> would have allowed you to see what the differences were between the
>>>>>>>> original style and the clone had this been the case.
>>>>>>>>
>>>>>>>> Now, from what you have said, it seems as though you are cloning a
>>>>>>>> style within a workbook, can I ask, is this the case? If so, are
>>>>>>>> you building the workbook entirely using POI or are you opening and
>>>>>>>> then modifying an existing workbook? If the former, then the easy
>>>>>>>> way to get around the problem is to not clone and then modify a
>>>>>>>> style but to build it completely from scratch, even though this
>>>>>>>> does mean a few lines of repeated code.
>>>>>>>>
>>>>>>>> If I have the time tomorrow, I will experiment with the workbook
>>>>>>>> you attached to see if I can replicate the problem. Can I check to
>>>>>>>> make sure that the style you are looking for has a white background
>>>>>>>> to the cell and red diagonal bars in the foreground?
>>>>>>>>
>>>>>>>> Yours
>>>>>>>>
>>>>>>>> Mark B
>>>>>>>>
>>>>>>>>
>>>>>>>> yehogold wrote:
>>>>>>>>>
>>>>>>>>> I used the BiffViewer to look at the file, but I'm not sure how to
>>>>>>>>> read what I am looking at. I can see many different kinds of
>>>>>>>>> style objects and a couple of cell objects, but am not sure how
>>>>>>>>> you know what cell has what style.
>>>>>>>>> Is there anywhere where there are instructions on how to read the
>>>>>>>>> output of the BiffViewer?
>>>>>>>>>
>>>>>>>>> As shown in the code, the new cellStyle was created by cloning the
>>>>>>>>> cellStyle of the cell, modifying it, and setting the cellStyle of
>>>>>>>>> the cell to the newly modified clone.
>>>>>>>>>
>>>>>>>>> I don't remember what the original style of the cells were in the
>>>>>>>>> workbook attached to my first method. I am attaching a second
>>>>>>>>> workbook were I know that the original cellStyle of all the cells
>>>>>>>>> was the default, i.e. I did not modify the style before running
>>>>>>>>> the workbook through the program.
>>>>>>>>>
>>>>>>>>> Please let me know if anyone has any ideas.
>>>>>>>>>
>>>>>>>>> Thank again in advance for your time.
>>>>>>>>>
>>>>>>>>> Regaurds,
>>>>>>>>> yehogold
>>>>>>>>>
>>>>>>>>> http://www.nabble.com/file/p24955769/workbook.xls workbook.xls
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> MSB wrote:
>>>>>>>>>>
>>>>>>>>>> As a first step, I would reccomend that you investigate alittle
>>>>>>>>>> using the BiffViewer utility. That may tell you which attributes
>>>>>>>>>> of the cell style are either not being set correctly or
>>>>>>>>>> corrunpted by the clone process.
>>>>>>>>>>
>>>>>>>>>> Does the example workbook you have posted contain both the
>>>>>>>>>> corrupted cell style and the style that you are cloning to create
>>>>>>>>>> it in the first instance?
>>>>>>>>>>
>>>>>>>>>> Yours
>>>>>>>>>>
>>>>>>>>>> Mark B
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> yehogold wrote:
>>>>>>>>>>>
>>>>>>>>>>> Hi.
>>>>>>>>>>>
>>>>>>>>>>> I have the following code used to modify the pattern of a cell:
>>>>>>>>>>>
>>>>>>>>>>> Workbook wb = cell.getSheet().getWorkbook();
>>>>>>>>>>> CellStyle errorStyle = wb.createCellStyle();
>>>>>>>>>>>
>>>>>>>>>>> errorStyle.cloneStyleFrom(cell.getCellStyle());
>>>>>>>>>>>
>>>>>>>>>>> errorStyle.setFillForegroundColor(Font.COLOR_RED);
>>>>>>>>>>> errorStyle.setFillPattern(CellStyle.THIN_BACKWARD_DIAG);
>>>>>>>>>>>
>>>>>>>>>>> cell.setCellStyle(errorStyle);
>>>>>>>>>>>
>>>>>>>>>>> When I run it, I end up getting these weird looking black cells.
>>>>>>>>>>> Excel 2003 will also not let me directly change the format of
>>>>>>>>>>> the black cells. How would I format the cells withouth getting
>>>>>>>>>>> this problem?
>>>>>>>>>>>
>>>>>>>>>>> I am inclosing one of the workbooks. The messed up black cells
>>>>>>>>>>> are on sheet2.
>>>>>>>>>>>
>>>>>>>>>>> Thank you in advance for your help.
>>>>>>>>>>> http://www.nabble.com/file/p24923092/workbook2.xls
>>>>>>>>>>> workbook2.xls
>>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
--
View this message in context:
http://www.nabble.com/Weird-cells-appearing-after-modifying-the-format-of-a-cell-tp24923092p24983326.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]
