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