Glad that you replied and happy the code worked although I admit that the
colouring problem is quite confusing - have we hit that same problem where
Excel is reporting the colour index of 64 - automatic - again? Anyway, since
I last posted, I have dug around in the API a bit further and found a
contribution made by another developer called CellUtil. It does everything -
at least I think it does as I have not had the chance to play with it yet -
that we have been trying to achieve. I think it could be worth your having a
look at before finally nailing down the design of your code.
Yours
Mark B
yehogold wrote:
>
> Hi.
>
> I tried your code and the pooling function worked very well.
> Unfortunately, it still produced the odd looking cells. I now think that
> the problem is not cloning within a workbook, it is in how a the AUTOMATIC
> color is cloned.
>
> Because of all the problems I seem to be having with this, I decided to
> change the marking from changing the background and shading to changing
> font's style. I am still using your method for pooling, however. It is
> very useful and should perhaps be cleaned up and submitted to POI to be
> included in the interface.
>
> Thank you for all your help,
>
> yehogold
>
>
>
> MSB wrote:
>>
>> 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-tp24923092p25038710.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]
