Weird cells appearing after modifying the format of a cell

[yehogold <ye...@yahoo.com>]

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-tp24923092p24923092.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

Just dug around a little bit more and it seems as if the default colour Excel
associates with the 'automatic' value is black. That would explain why you
saw that odd appearance but does raise the question of why POI returns this
value for both the backgrond and foreground fill colours after recovering
the style from a cell.

If I have the time, I will use BiffViwer to look alittle more closely into
the data POI is recovering from the files.

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-tp24923092p24970223.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[yehogold <ye...@yahoo.com>]

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-tp24923092p25030851.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

Tut tut, you know that I never make a mistake;-)

Anyway, had a thought at about midnight last night whilst listening to some
classic sci-fi on BBC Radio 7 and I may have a solution to both the
copying/cloning of styles and pooling the same. I need to to dig around a
bit further but I think that it would be possible to get all of the styles
properties and assemble them into a comma separated String. That could be
used as the key for a HashMap ot Hashtable that is the style pool. To check
if there is already a style in existence to accomplish what we require,
create the String by getting some of the porperties of an existing style and
'adding' any that will be modified then see if the Collection contains that
key, if it does return that style, if not create the style. As we 'know'
what each element of the key String relates to, that could be pulled apart
using a StringTokenizer for example and used as the basis for creating a new
style. Should be fun playing around with this today and I will let you know
if I make any progress.

No, the fact that you are using the ss classes ought to make no difference
at all, all I was doing was looking for a handle to begin searching for the
reason why the code seemed to be failing for you.

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-tp24923092p24981808.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[yehogold <ye...@yahoo.com>]

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-tp24923092p24975287.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

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-tp24923092p24974113.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[yehogold <ye...@yahoo.com>]

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-tp24923092p24973927.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

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-tp24923092p24973403.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[yehogold <ye...@yahoo.com>]

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-tp24923092p24972448.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

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-tp24923092p24967065.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

Sorted I think. All I did was to pick at random a cell from the workbook you
posted - Sheet2 Cell B3 - cloned it's style, modified the clone and applied
that to another cell on the same sheet - Cell B7. It seems that the key is
to set the background and the forground colours explicitly following the
clone. Looking at the foreground and background colour values, on my PC both
were reported as being 64 and both were copied faithfully by the clone
method. This value - 64 - equates with the 'automatic' colour setting and so
it was Excel that 'decided' to choose a black background for the cell for
some reason that I cannot even begin to geuss at.

Anyway, have a look at the code below which I have run against the workbook
you posted.

    public void cloneTest(String filename) throws IOException,
FileNotFoundException {
        File file = null;
        FileInputStream fis = null;
        FileOutputStream fos = null;
        HSSFWorkbook workbook = null;
        HSSFSheet sheet = null;
        HSSFCell cell = null;
        HSSFCellStyle originalStyle = null;
        HSSFCellStyle clonedStyle = null;
        try {
            // OPen the workbook and get cell B£ from sheet 2.
            file = new File(filename);
            fis = new FileInputStream(file);
            workbook = new HSSFWorkbook(fis);
            fis.close();
            fis = null;
            sheet = workbook.getSheetAt(1);
            cell = sheet.getRow(2).getCell(1);

            // Grab that cells style and then clone it
            originalStyle = cell.getCellStyle();
            clonedStyle = workbook.createCellStyle();
            clonedStyle.cloneStyleFrom(originalStyle);

            // Set the forground colour to red and the background colour to
            // white. Should not really need to do this but it does solve
the
            // problem.
            clonedStyle.setFillForegroundColor(HSSFColor.RED.index);
            clonedStyle.setFillBackgroundColor(HSSFColor.WHITE.index);

            // Set fill pattern to diagonal bars.
            clonedStyle.setFillPattern(HSSFCellStyle.THIN_BACKWARD_DIAG);

            // Get another cell on the same sheet - this time B7 - and apply
            // the cloned, modified style to it.
            cell = sheet.getRow(6).getCell(1);
            cell.setCellStyle(clonedStyle);

            // 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
                }
            }
        }
    }

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-tp24923092p24969984.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[yehogold <ye...@yahoo.com>]

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-tp24923092p24958590.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

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-tp24923092p24957509.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[yehogold <ye...@yahoo.com>]

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-tp24923092p24955769.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: Weird cells appearing after modifying the format of a cell

[MSB <ma...@tiscali.co.uk>]

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-tp24923092p24949484.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org