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