poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From yehogold <yehog...@yahoo.com>
Subject Re: Weird cells appearing after modifying the format of a cell
Date Fri, 14 Aug 2009 14:15:32 GMT

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-tp24923092p24972448.html
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org


Mime
View raw message