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