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