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: [email protected]
For additional commands, e-mail: [email protected]
