You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Mark Hansen <na...@mehconsulting.com> on 2009/01/09 00:24:58 UTC
Applying a data format to a style for one cell affects other cells?
I'm using POI 3.2-FINAL on Windows/XP SP3.
My program is opening an Excel template file (.xlt - 2003 version) and
creating rows/cells to fill the work sheet with data which comes from a
separate system.
The feature allows my customer to export there data to .xls, so the customer
creates the Excel template file, which includes some column headers (in row
1) as well as some background colors/boarders on cells, etc.
What I want to do is go through my customer's data and create a row in the
worksheet for each row of data.
When the data is alphanumeric, I just use "setCellValue()" to set the value
and it comes out fine. When the data is a date value, I want to apply a
Date-based data format to the cell. So that I don't lose the background
color (and other formatting) applied to the template by the customer, I get
the cell's style (using getCellStyle()), apply my Date format to it, then
set it back, as follows:
[QUOTE]
...
HSSFDataFormat dataFormat = wb.createDataFormat();
short dateFormat = dataFormat.getFormat("m/d/yy");
...
HSSFCell cell = ... use row.getCell(cell-number) to get the cell
cell.setCellValue(my java.util.Date value);
HSSFStyle localStyle = cell.getCellStyle()
localStyle.setDataFormat(dateFormat);
cell.setCellStyle(localStyle);
[/QUOTE]
The above code does set the date value into the cell, and sets the format
for the cell to Date
with a format of "m/d/yy" as desired. However, subsequent cells in the row
which contain
numeric values end up with the same Date formatting.
When I have a numeric value, I just use cell.setCellValue(my double value) -
because it looks to me like setCellValue(double) will set the cell type
appropriately.
Why when I set a style on one cell, it is picked-up by a subsequent column?
I can't just create a generic style for use by all cells, because the
customer will set cell-specific styles (like background color, etc.) on
individual cells, and I don't want to overwrite those.
Can anyone explain what I need to do?
Thanks,
--
View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21362472.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
Re: Applying a data format to a style for one cell affects other cells?
Posted by David Fisher <df...@jmlafferty.com>.
If it is working go ahead. The code I quoted from is from a special
class of ours that we use to manage Styles in our XLS creation process.
It helps to remind everyone that styles are stored in the workbook and
then later associated with a cell. It really helps to manage your
styles as quickly as possible, and that is what you seem to be doing
since you are basing things the column header style.
Think of it as if the Excel workbook had CSS. Any attribute difference
requires another class.
> Is it going to be removed in a later release?
I doubt it. It should be deprecated first before it is removed.
Dave
On Jan 9, 2009, at 1:59 PM, Mark Hansen wrote:
>
> Thank you for your help, David. Are you saying that I should not
> close the
> style using
> the cloneStyleFrom() method on the HSSFCellStyle object? This method
> is in
> the
> 3.2-FINAL release I'm using and seems to be working.
>
> Is it going to be removed in a later release?
>
> Thanks,
>
>
> David Fisher wrote:
>>
>> You can clone using something like:
>>
>> public static HSSFCellStyle cloneStyle(HSSFWorkbook wb,
>> HSSFCellStyle src){
>> HSSFCellStyle style = wb.createCellStyle();
>> style.setFont(wb.getFontAt(src.getFontIndex()));
>> style.setAlignment(src.getAlignment());
>> style.setBorderBottom(src.getBorderBottom());
>> style.setBorderLeft(src.getBorderLeft());
>> style.setBorderRight(src.getBorderRight());
>> style.setBorderTop(src.getBorderTop());
>> style.setBottomBorderColor(src.getBottomBorderColor());
>> style.setDataFormat(src.getDataFormat());
>> style.setFillBackgroundColor(src.getFillBackgroundColor());
>> style.setFillForegroundColor(src.getFillForegroundColor());
>> style.setFillPattern(src.getFillPattern());
>> style.setHidden(src.getHidden());
>> style.setIndention(src.getIndention());
>> style.setLeftBorderColor(src.getLeftBorderColor());
>> style.setLocked(src.getLocked());
>> style.setRightBorderColor(src.getRightBorderColor());
>> style.setRotation(src.getRotation());
>> style.setTopBorderColor(src.getTopBorderColor());
>> style.setVerticalAlignment(src.getVerticalAlignment());
>> style.setWrapText(src.getWrapText());
>> return style;
>> }
>>
>> Save all your new and different formats in an Array or HashMap.
>>
>> Add variations as you need them anything different in the style is
>> another HSSFCellStyle object attached to the HSSFWorkbook. Use Clone
>> to add new variations.
>>
>> To set the style of a cell just call
>> HSSFCell.setCellStyle(HSSFCellStyle style).
>>
>> You can use HSSFWorkbook.getNumCellStyles() and
>> HSSFWorkbook.getCellStyleAt(short idx) to retrieve the styles that
>> are
>> already in the file.
>>
>> Good luck.
>>
>> Regards,
>> Dave
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21379470.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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Applying a data format to a style for one cell affects other
cells?
Posted by Mark Hansen <na...@mehconsulting.com>.
Thank you for your help, David. Are you saying that I should not close the
style using
the cloneStyleFrom() method on the HSSFCellStyle object? This method is in
the
3.2-FINAL release I'm using and seems to be working.
Is it going to be removed in a later release?
Thanks,
David Fisher wrote:
>
> You can clone using something like:
>
> public static HSSFCellStyle cloneStyle(HSSFWorkbook wb,
> HSSFCellStyle src){
> HSSFCellStyle style = wb.createCellStyle();
> style.setFont(wb.getFontAt(src.getFontIndex()));
> style.setAlignment(src.getAlignment());
> style.setBorderBottom(src.getBorderBottom());
> style.setBorderLeft(src.getBorderLeft());
> style.setBorderRight(src.getBorderRight());
> style.setBorderTop(src.getBorderTop());
> style.setBottomBorderColor(src.getBottomBorderColor());
> style.setDataFormat(src.getDataFormat());
> style.setFillBackgroundColor(src.getFillBackgroundColor());
> style.setFillForegroundColor(src.getFillForegroundColor());
> style.setFillPattern(src.getFillPattern());
> style.setHidden(src.getHidden());
> style.setIndention(src.getIndention());
> style.setLeftBorderColor(src.getLeftBorderColor());
> style.setLocked(src.getLocked());
> style.setRightBorderColor(src.getRightBorderColor());
> style.setRotation(src.getRotation());
> style.setTopBorderColor(src.getTopBorderColor());
> style.setVerticalAlignment(src.getVerticalAlignment());
> style.setWrapText(src.getWrapText());
> return style;
> }
>
> Save all your new and different formats in an Array or HashMap.
>
> Add variations as you need them anything different in the style is
> another HSSFCellStyle object attached to the HSSFWorkbook. Use Clone
> to add new variations.
>
> To set the style of a cell just call
> HSSFCell.setCellStyle(HSSFCellStyle style).
>
> You can use HSSFWorkbook.getNumCellStyles() and
> HSSFWorkbook.getCellStyleAt(short idx) to retrieve the styles that are
> already in the file.
>
> Good luck.
>
> Regards,
> Dave
>
>
>
--
View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21379470.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
Re: Applying a data format to a style for one cell affects other cells?
Posted by David Fisher <df...@jmlafferty.com>.
You can clone using something like:
public static HSSFCellStyle cloneStyle(HSSFWorkbook wb,
HSSFCellStyle src){
HSSFCellStyle style = wb.createCellStyle();
style.setFont(wb.getFontAt(src.getFontIndex()));
style.setAlignment(src.getAlignment());
style.setBorderBottom(src.getBorderBottom());
style.setBorderLeft(src.getBorderLeft());
style.setBorderRight(src.getBorderRight());
style.setBorderTop(src.getBorderTop());
style.setBottomBorderColor(src.getBottomBorderColor());
style.setDataFormat(src.getDataFormat());
style.setFillBackgroundColor(src.getFillBackgroundColor());
style.setFillForegroundColor(src.getFillForegroundColor());
style.setFillPattern(src.getFillPattern());
style.setHidden(src.getHidden());
style.setIndention(src.getIndention());
style.setLeftBorderColor(src.getLeftBorderColor());
style.setLocked(src.getLocked());
style.setRightBorderColor(src.getRightBorderColor());
style.setRotation(src.getRotation());
style.setTopBorderColor(src.getTopBorderColor());
style.setVerticalAlignment(src.getVerticalAlignment());
style.setWrapText(src.getWrapText());
return style;
}
Save all your new and different formats in an Array or HashMap.
Add variations as you need them anything different in the style is
another HSSFCellStyle object attached to the HSSFWorkbook. Use Clone
to add new variations.
To set the style of a cell just call
HSSFCell.setCellStyle(HSSFCellStyle style).
You can use HSSFWorkbook.getNumCellStyles() and
HSSFWorkbook.getCellStyleAt(short idx) to retrieve the styles that are
already in the file.
Good luck.
Regards,
Dave
On Jan 9, 2009, at 11:41 AM, Mark Hansen wrote:
>
>
>
> Anthony Andrews wrote:
>>
>> Must admit Mark that I have seen some problems with regard to Date/
>> Time
>> cells that is very similar to your 'setting type before value'
>> problem.
>> Also, there was a cloneStyleFrom() method added to the
>> HSSFCellStyle class
>> some time ago and it seemed to disappear again. If it is still
>> there, then
>> it could be useful to you - allowing you to more readily create
>> styles
>> that are based upon existing ones but that differ in one aspect - say
>> background colour.
>>
>
> Of course, I can't create a style for every cell, because I then run
> into
> the problem of too many styles. However, I'm hopeful that using a
> single
> style for each "column" will work for my case, and am still in the
> process
> of coding it up to test.
>
> I was planning to use the method to close the existing style, so I
> hope it's
> still there and working :-)
> I see it in the javadoc on the HSSFCellStyle class.
>
>
> Anthony Andrews wrote:
>>
>> Good luck with the code. Any problems then do not hesitate to post
>> questions here. The beauty of these lists is that loads of people
>> look at
>> them and there is often someone who faced exactly the same problem
>> as you.
>> Also, this lisst seems to be regularly visited by the people who
>> created
>> and maintain the API, never a bad thing IMO.
>>
>> --- On Fri, 1/9/09, Mark Hansen <na...@mehconsulting.com> wrote:
>> From: Mark Hansen <na...@mehconsulting.com>
>> Subject: RE: Applying a data format to a style for one cell affects
>> other
>> cells?
>> To: user@poi.apache.org
>> Date: Friday, January 9, 2009, 9:12 AM
>>
>>
>> Anthony Andrews wrote:
>>>
>>>
>>> Hello Mark,
>>>
>>> Can we have a look at some code please? We need to know how you are
>>> getting the cell styles from the template in the firast place, how
>>> you
>>> are
>>> storing them, what you are doing when you create a cell, etc.
>>>
>>>
>>
>> I will work on creating an example...
>> But for now, I was going through the cells and calling
>> getCellStyle() to
>> get
>> the style for each cell, then applying the data format to the style
>> and
>> setting the style back into the cell using setCellStyle(). What I
>> didn't
>> realize is that without creating new styles, I would be applying
>> the data
>> format to a style which is used by many (if not all) cells, not
>> just the
>> cell on which I was operating.
>>
>>
>> Anthony Andrews wrote:
>>>
>>>
>>> In your case, I would create a pool of cell styles - use one of the
>>> collections classes such as the HashMap that allows you to associate
>>> column numbers with cell styles. Typically, you would populate this
>>> collection once only, at the start of the sheet creation process.
>>> It is
>>> then possible to write some code that says, in effect, what is the
>>> column
>>> number, get me that cell style and apply it.
>>>
>>>
>>
>> In thinking about a single column of cells, I was assuming that I
>> would
>> need
>> to honor each cell's style when applying my desired data format. For
>> example, rows 1-10 may have one back ground color while rows 11-20
>> may
>> have
>> something different. However, I don't think that will ever be the
>> situation
>> in my case, so I think I should be able to create a style based on
>> the
>> style
>> used by the cell in row 1, and apply that style to the cell in all
>> rows.
>>
>>
>> Anthony Andrews wrote:
>>>
>>>
>>> Secondly, where does the data come from? You really do need, if it
>>> is
>>> possible, to place data of the correct type into a cell by setting
>>> the
>>> cells type and calling the correct method to set it's value.
>>> Assuming
>> that
>>> you are reading a series of Strings from something like a CSV
>>> file, have
>>> you considered using Regular Expressions to test the data type,
>>> convert
>>> it
>>> appropriately and then place it into a cell of the appropriate
>>> data type?
>>> Somewhere, I have some code that does this - that is to read a CSV
>>> file,
>>> test each item against a regular expression to determine type and
>>> then
>>> populate a worksheet. If you want, I can post it for you later
>>> today,
>>> just
>>> let me know.
>>>
>>>
>>
>> Actually, I found that if I set the cell's type before setting the
>> cell's
>> value, the POI code can throw an exception. It seems that the code
>> which
>> is
>> called when you set the cell's style has a side effect of getting,
>> then
>> setting the cell's value. Without having set the cell's value yet,
>> there
>> seems to be garbage in there which the type-specific setting logic
>> doesn't
>> like.
>>
>> However, when setting the cell's value, the code sets the cell's type
>> based
>> on the data type of the parameter.
>>
>> In any case, I do know the data type of the data associated with each
>> column, so I do convert the data into the proper type, then call the
>> type-specific setCellValue() method when applying the value to the
>> cell. I
>> believe I used the setting of a date value in my original example.
>>
>> I'm going to try reorganizing my code as mentioned above, and will
>> post
>> back
>> with my results.
>>
>> Thanks for all the time you've put into this. I really need to get
>> this
>> working soon.
>>
>>
>> --- On Thu, 1/8/09, Mark Hansen <na...@mehconsulting.com> wrote:
>> From: Mark Hansen <na...@mehconsulting.com>
>> Subject: RE: Applying a data format to a style for one cell affects
>> other
>> cells?
>> To: user@poi.apache.org
>> Date: Thursday, January 8, 2009, 8:39 PM
>>
>>
>>
>> Winarto-2 wrote:
>>>
>>> Hi Mark,
>>>
>>> As far as I understand, style is available in workbook level, and
>>> hence
>>> when you're doing "HSSFStyle localStyle =
>> cell.getCellStyle()" you're
>>> actually getting the style reference from the workbook that is
>>> applied
>>> to the particular cell. So If you get the cell style and modify it,
>>> you're actually modifying the style of all cells in the workbook
>>> that
>> is
>>> using that style.
>>>
>>> Cheers,
>>> Winarto
>>>
>>>
>>
>> Hello and thank you for your response.
>>
>> Can you please tell me then how I am supposed to create styles for
>> each of
>> the cells
>> in the sheet? If I create a new style for each cell I lose the
>> formatting
>> applied to the
>> cell in the template and I get an error that I've created too many
>> styles.
>>
>> All I really want to do is change the data formatting on a per-cell
>> basis
>> (actually, the
>> formatting will be the same for that cell in every row). Is there a
>> way I
>> can apply formatting
>> to a cell without having it affect all cells - without creating a
>> new cell
>> style for each cell?
>>
>> Thanks,
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21366134.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
>>
>>
>>
>>
>>
>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21376394.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
>>
>>
>>
>>
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21376950.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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: Applying a data format to a style for one cell affects other
cells?
Posted by Mark Hansen <na...@mehconsulting.com>.
Anthony Andrews wrote:
>
> Must admit Mark that I have seen some problems with regard to Date/Time
> cells that is very similar to your 'setting type before value' problem.
> Also, there was a cloneStyleFrom() method added to the HSSFCellStyle class
> some time ago and it seemed to disappear again. If it is still there, then
> it could be useful to you - allowing you to more readily create styles
> that are based upon existing ones but that differ in one aspect - say
> background colour.
>
Of course, I can't create a style for every cell, because I then run into
the problem of too many styles. However, I'm hopeful that using a single
style for each "column" will work for my case, and am still in the process
of coding it up to test.
I was planning to use the method to close the existing style, so I hope it's
still there and working :-)
I see it in the javadoc on the HSSFCellStyle class.
Anthony Andrews wrote:
>
> Good luck with the code. Any problems then do not hesitate to post
> questions here. The beauty of these lists is that loads of people look at
> them and there is often someone who faced exactly the same problem as you.
> Also, this lisst seems to be regularly visited by the people who created
> and maintain the API, never a bad thing IMO.
>
> --- On Fri, 1/9/09, Mark Hansen <na...@mehconsulting.com> wrote:
> From: Mark Hansen <na...@mehconsulting.com>
> Subject: RE: Applying a data format to a style for one cell affects other
> cells?
> To: user@poi.apache.org
> Date: Friday, January 9, 2009, 9:12 AM
>
>
> Anthony Andrews wrote:
>>
>>
>> Hello Mark,
>>
>> Can we have a look at some code please? We need to know how you are
>> getting the cell styles from the template in the firast place, how you
>> are
>> storing them, what you are doing when you create a cell, etc.
>>
>>
>
> I will work on creating an example...
> But for now, I was going through the cells and calling getCellStyle() to
> get
> the style for each cell, then applying the data format to the style and
> setting the style back into the cell using setCellStyle(). What I didn't
> realize is that without creating new styles, I would be applying the data
> format to a style which is used by many (if not all) cells, not just the
> cell on which I was operating.
>
>
> Anthony Andrews wrote:
>>
>>
>> In your case, I would create a pool of cell styles - use one of the
>> collections classes such as the HashMap that allows you to associate
>> column numbers with cell styles. Typically, you would populate this
>> collection once only, at the start of the sheet creation process. It is
>> then possible to write some code that says, in effect, what is the column
>> number, get me that cell style and apply it.
>>
>>
>
> In thinking about a single column of cells, I was assuming that I would
> need
> to honor each cell's style when applying my desired data format. For
> example, rows 1-10 may have one back ground color while rows 11-20 may
> have
> something different. However, I don't think that will ever be the
> situation
> in my case, so I think I should be able to create a style based on the
> style
> used by the cell in row 1, and apply that style to the cell in all rows.
>
>
> Anthony Andrews wrote:
>>
>>
>> Secondly, where does the data come from? You really do need, if it is
>> possible, to place data of the correct type into a cell by setting the
>> cells type and calling the correct method to set it's value. Assuming
> that
>> you are reading a series of Strings from something like a CSV file, have
>> you considered using Regular Expressions to test the data type, convert
>> it
>> appropriately and then place it into a cell of the appropriate data type?
>> Somewhere, I have some code that does this - that is to read a CSV file,
>> test each item against a regular expression to determine type and then
>> populate a worksheet. If you want, I can post it for you later today,
>> just
>> let me know.
>>
>>
>
> Actually, I found that if I set the cell's type before setting the
> cell's
> value, the POI code can throw an exception. It seems that the code which
> is
> called when you set the cell's style has a side effect of getting, then
> setting the cell's value. Without having set the cell's value yet,
> there
> seems to be garbage in there which the type-specific setting logic doesn't
> like.
>
> However, when setting the cell's value, the code sets the cell's type
> based
> on the data type of the parameter.
>
> In any case, I do know the data type of the data associated with each
> column, so I do convert the data into the proper type, then call the
> type-specific setCellValue() method when applying the value to the cell. I
> believe I used the setting of a date value in my original example.
>
> I'm going to try reorganizing my code as mentioned above, and will post
> back
> with my results.
>
> Thanks for all the time you've put into this. I really need to get this
> working soon.
>
>
> --- On Thu, 1/8/09, Mark Hansen <na...@mehconsulting.com> wrote:
> From: Mark Hansen <na...@mehconsulting.com>
> Subject: RE: Applying a data format to a style for one cell affects other
> cells?
> To: user@poi.apache.org
> Date: Thursday, January 8, 2009, 8:39 PM
>
>
>
> Winarto-2 wrote:
>>
>> Hi Mark,
>>
>> As far as I understand, style is available in workbook level, and hence
>> when you're doing "HSSFStyle localStyle =
> cell.getCellStyle()" you're
>> actually getting the style reference from the workbook that is applied
>> to the particular cell. So If you get the cell style and modify it,
>> you're actually modifying the style of all cells in the workbook that
> is
>> using that style.
>>
>> Cheers,
>> Winarto
>>
>>
>
> Hello and thank you for your response.
>
> Can you please tell me then how I am supposed to create styles for each of
> the cells
> in the sheet? If I create a new style for each cell I lose the formatting
> applied to the
> cell in the template and I get an error that I've created too many styles.
>
> All I really want to do is change the data formatting on a per-cell basis
> (actually, the
> formatting will be the same for that cell in every row). Is there a way I
> can apply formatting
> to a cell without having it affect all cells - without creating a new cell
> style for each cell?
>
> Thanks,
>
> --
> View this message in context:
> http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21366134.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
>
>
>
>
>
>
>
> --
> View this message in context:
> http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21376394.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
>
>
>
>
>
>
--
View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21376950.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
RE: Applying a data format to a style for one cell affects other cells?
Posted by Anthony Andrews <py...@yahoo.com>.
Must admit Mark that I have seen some problems with regard to Date/Time cells that is very similar to your 'setting type before value' problem. Also, there was a cloneStyleFrom() method added to the HSSFCellStyle class some time ago and it seemed to disappear again. If it is still there, then it could be useful to you - allowing you to more readily create styles that are based upon existing ones but that differ in one aspect - say background colour.
Good luck with the code. Any problems then do not hesitate to post questions here. The beauty of these lists is that loads of people look at them and there is often someone who faced exactly the same problem as you. Also, this lisst seems to be regularly visited by the people who created and maintain the API, never a bad thing IMO.
--- On Fri, 1/9/09, Mark Hansen <na...@mehconsulting.com> wrote:
From: Mark Hansen <na...@mehconsulting.com>
Subject: RE: Applying a data format to a style for one cell affects other cells?
To: user@poi.apache.org
Date: Friday, January 9, 2009, 9:12 AM
Anthony Andrews wrote:
>
>
> Hello Mark,
>
> Can we have a look at some code please? We need to know how you are
> getting the cell styles from the template in the firast place, how you are
> storing them, what you are doing when you create a cell, etc.
>
>
I will work on creating an example...
But for now, I was going through the cells and calling getCellStyle() to get
the style for each cell, then applying the data format to the style and
setting the style back into the cell using setCellStyle(). What I didn't
realize is that without creating new styles, I would be applying the data
format to a style which is used by many (if not all) cells, not just the
cell on which I was operating.
Anthony Andrews wrote:
>
>
> In your case, I would create a pool of cell styles - use one of the
> collections classes such as the HashMap that allows you to associate
> column numbers with cell styles. Typically, you would populate this
> collection once only, at the start of the sheet creation process. It is
> then possible to write some code that says, in effect, what is the column
> number, get me that cell style and apply it.
>
>
In thinking about a single column of cells, I was assuming that I would need
to honor each cell's style when applying my desired data format. For
example, rows 1-10 may have one back ground color while rows 11-20 may have
something different. However, I don't think that will ever be the situation
in my case, so I think I should be able to create a style based on the style
used by the cell in row 1, and apply that style to the cell in all rows.
Anthony Andrews wrote:
>
>
> Secondly, where does the data come from? You really do need, if it is
> possible, to place data of the correct type into a cell by setting the
> cells type and calling the correct method to set it's value. Assuming
that
> you are reading a series of Strings from something like a CSV file, have
> you considered using Regular Expressions to test the data type, convert it
> appropriately and then place it into a cell of the appropriate data type?
> Somewhere, I have some code that does this - that is to read a CSV file,
> test each item against a regular expression to determine type and then
> populate a worksheet. If you want, I can post it for you later today, just
> let me know.
>
>
Actually, I found that if I set the cell's type before setting the
cell's
value, the POI code can throw an exception. It seems that the code which is
called when you set the cell's style has a side effect of getting, then
setting the cell's value. Without having set the cell's value yet,
there
seems to be garbage in there which the type-specific setting logic doesn't
like.
However, when setting the cell's value, the code sets the cell's type
based
on the data type of the parameter.
In any case, I do know the data type of the data associated with each
column, so I do convert the data into the proper type, then call the
type-specific setCellValue() method when applying the value to the cell. I
believe I used the setting of a date value in my original example.
I'm going to try reorganizing my code as mentioned above, and will post
back
with my results.
Thanks for all the time you've put into this. I really need to get this
working soon.
--- On Thu, 1/8/09, Mark Hansen <na...@mehconsulting.com> wrote:
From: Mark Hansen <na...@mehconsulting.com>
Subject: RE: Applying a data format to a style for one cell affects other
cells?
To: user@poi.apache.org
Date: Thursday, January 8, 2009, 8:39 PM
Winarto-2 wrote:
>
> Hi Mark,
>
> As far as I understand, style is available in workbook level, and hence
> when you're doing "HSSFStyle localStyle =
cell.getCellStyle()" you're
> actually getting the style reference from the workbook that is applied
> to the particular cell. So If you get the cell style and modify it,
> you're actually modifying the style of all cells in the workbook that
is
> using that style.
>
> Cheers,
> Winarto
>
>
Hello and thank you for your response.
Can you please tell me then how I am supposed to create styles for each of
the cells
in the sheet? If I create a new style for each cell I lose the formatting
applied to the
cell in the template and I get an error that I've created too many styles.
All I really want to do is change the data formatting on a per-cell basis
(actually, the
formatting will be the same for that cell in every row). Is there a way I
can apply formatting
to a cell without having it affect all cells - without creating a new cell
style for each cell?
Thanks,
--
View this message in context:
http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21366134.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
--
View this message in context:
http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21376394.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
RE: Applying a data format to a style for one cell affects other
cells?
Posted by Mark Hansen <na...@mehconsulting.com>.
Anthony Andrews wrote:
>
>
> Hello Mark,
>
> Can we have a look at some code please? We need to know how you are
> getting the cell styles from the template in the firast place, how you are
> storing them, what you are doing when you create a cell, etc.
>
>
I will work on creating an example...
But for now, I was going through the cells and calling getCellStyle() to get
the style for each cell, then applying the data format to the style and
setting the style back into the cell using setCellStyle(). What I didn't
realize is that without creating new styles, I would be applying the data
format to a style which is used by many (if not all) cells, not just the
cell on which I was operating.
Anthony Andrews wrote:
>
>
> In your case, I would create a pool of cell styles - use one of the
> collections classes such as the HashMap that allows you to associate
> column numbers with cell styles. Typically, you would populate this
> collection once only, at the start of the sheet creation process. It is
> then possible to write some code that says, in effect, what is the column
> number, get me that cell style and apply it.
>
>
In thinking about a single column of cells, I was assuming that I would need
to honor each cell's style when applying my desired data format. For
example, rows 1-10 may have one back ground color while rows 11-20 may have
something different. However, I don't think that will ever be the situation
in my case, so I think I should be able to create a style based on the style
used by the cell in row 1, and apply that style to the cell in all rows.
Anthony Andrews wrote:
>
>
> Secondly, where does the data come from? You really do need, if it is
> possible, to place data of the correct type into a cell by setting the
> cells type and calling the correct method to set it's value. Assuming that
> you are reading a series of Strings from something like a CSV file, have
> you considered using Regular Expressions to test the data type, convert it
> appropriately and then place it into a cell of the appropriate data type?
> Somewhere, I have some code that does this - that is to read a CSV file,
> test each item against a regular expression to determine type and then
> populate a worksheet. If you want, I can post it for you later today, just
> let me know.
>
>
Actually, I found that if I set the cell's type before setting the cell's
value, the POI code can throw an exception. It seems that the code which is
called when you set the cell's style has a side effect of getting, then
setting the cell's value. Without having set the cell's value yet, there
seems to be garbage in there which the type-specific setting logic doesn't
like.
However, when setting the cell's value, the code sets the cell's type based
on the data type of the parameter.
In any case, I do know the data type of the data associated with each
column, so I do convert the data into the proper type, then call the
type-specific setCellValue() method when applying the value to the cell. I
believe I used the setting of a date value in my original example.
I'm going to try reorganizing my code as mentioned above, and will post back
with my results.
Thanks for all the time you've put into this. I really need to get this
working soon.
--- On Thu, 1/8/09, Mark Hansen <na...@mehconsulting.com> wrote:
From: Mark Hansen <na...@mehconsulting.com>
Subject: RE: Applying a data format to a style for one cell affects other
cells?
To: user@poi.apache.org
Date: Thursday, January 8, 2009, 8:39 PM
Winarto-2 wrote:
>
> Hi Mark,
>
> As far as I understand, style is available in workbook level, and hence
> when you're doing "HSSFStyle localStyle =
cell.getCellStyle()" you're
> actually getting the style reference from the workbook that is applied
> to the particular cell. So If you get the cell style and modify it,
> you're actually modifying the style of all cells in the workbook that
is
> using that style.
>
> Cheers,
> Winarto
>
>
Hello and thank you for your response.
Can you please tell me then how I am supposed to create styles for each of
the cells
in the sheet? If I create a new style for each cell I lose the formatting
applied to the
cell in the template and I get an error that I've created too many styles.
All I really want to do is change the data formatting on a per-cell basis
(actually, the
formatting will be the same for that cell in every row). Is there a way I
can apply formatting
to a cell without having it affect all cells - without creating a new cell
style for each cell?
Thanks,
--
View this message in context:
http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21366134.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
--
View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21376394.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
RE: Applying a data format to a style for one cell affects other cells?
Posted by Anthony Andrews <py...@yahoo.com>.
Hello Mark,
Can we have a look at some code please? We need to know how you are getting the cell styles from the template in the firast place, how you are storing them, what you are doing when you create a cell, etc.
In your case, I would create a pool of cell styles - use one of the collections classes such as the HashMap that allows you to associate column numbers with cell styles. Typically, you would populate this collection once only, at the start of the sheet creation process. It is then possible to write some code that says, in effect, what is the column number, get me that cell style and apply it.
Secondly, where does the data come from? You really do need, if it is possible, to place data of the correct type into a cell by setting the cells type and calling the correct method to set it's value. Assuming that you are reading a series of Strings from something like a CSV file, have you considered using Regular Expressions to test the data type, convert it appropriately and then place it into a cell of the appropriate data type? Somewhere, I have some code that does this - that is to read a CSV file, test each item against a regular expression to determine type and then populate a worksheet. If you want, I can post it for you later today, just let me know.
--- On Thu, 1/8/09, Mark Hansen <na...@mehconsulting.com> wrote:
From: Mark Hansen <na...@mehconsulting.com>
Subject: RE: Applying a data format to a style for one cell affects other cells?
To: user@poi.apache.org
Date: Thursday, January 8, 2009, 8:39 PM
Winarto-2 wrote:
>
> Hi Mark,
>
> As far as I understand, style is available in workbook level, and hence
> when you're doing "HSSFStyle localStyle =
cell.getCellStyle()" you're
> actually getting the style reference from the workbook that is applied
> to the particular cell. So If you get the cell style and modify it,
> you're actually modifying the style of all cells in the workbook that
is
> using that style.
>
> Cheers,
> Winarto
>
>
Hello and thank you for your response.
Can you please tell me then how I am supposed to create styles for each of
the cells
in the sheet? If I create a new style for each cell I lose the formatting
applied to the
cell in the template and I get an error that I've created too many styles.
All I really want to do is change the data formatting on a per-cell basis
(actually, the
formatting will be the same for that cell in every row). Is there a way I
can apply formatting
to a cell without having it affect all cells - without creating a new cell
style for each cell?
Thanks,
--
View this message in context:
http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21366134.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
RE: Applying a data format to a style for one cell affects other
cells?
Posted by Mark Hansen <na...@mehconsulting.com>.
Winarto-2 wrote:
>
> Hi Mark,
>
> As far as I understand, style is available in workbook level, and hence
> when you're doing "HSSFStyle localStyle = cell.getCellStyle()" you're
> actually getting the style reference from the workbook that is applied
> to the particular cell. So If you get the cell style and modify it,
> you're actually modifying the style of all cells in the workbook that is
> using that style.
>
> Cheers,
> Winarto
>
>
Hello and thank you for your response.
Can you please tell me then how I am supposed to create styles for each of
the cells
in the sheet? If I create a new style for each cell I lose the formatting
applied to the
cell in the template and I get an error that I've created too many styles.
All I really want to do is change the data formatting on a per-cell basis
(actually, the
formatting will be the same for that cell in every row). Is there a way I
can apply formatting
to a cell without having it affect all cells - without creating a new cell
style for each cell?
Thanks,
--
View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21366134.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
RE: Applying a data format to a style for one cell affects other cells?
Posted by Winarto <wi...@fermat.eu>.
Hi Mark,
As far as I understand, style is available in workbook level, and hence
when you're doing "HSSFStyle localStyle = cell.getCellStyle()" you're
actually getting the style reference from the workbook that is applied
to the particular cell. So If you get the cell style and modify it,
you're actually modifying the style of all cells in the workbook that is
using that style.
Cheers,
Winarto
-----Original Message-----
From: Mark Hansen [mailto:nabble@mehconsulting.com]
Sent: Friday, 9 January 2009 07:46
To: user@poi.apache.org
Subject: Re: Applying a data format to a style for one cell affects
other cells?
Mark Hansen wrote:
>
> I'm using POI 3.2-FINAL on Windows/XP SP3.
> My program is opening an Excel template file (.xlt - 2003 version) and
> creating rows/cells to fill the work sheet with data which comes from
a
> separate system.
> The feature allows my customer to export there data to .xls, so the
> customer creates the Excel template file, which includes some column
> headers (in row 1) as well as some background colors/boarders on
cells,
> etc.
>
> What I want to do is go through my customer's data and create a row in
the
> worksheet for each row of data.
>
> When the data is alphanumeric, I just use "setCellValue()" to set the
> value and it comes out fine. When the data is a date value, I want to
> apply a Date-based data format to the cell. So that I don't lose the
> background color (and other formatting) applied to the template by the
> customer, I get the cell's style (using getCellStyle()), apply my Date
> format to it, then set it back, as follows:
>
> [QUOTE]
> ...
> HSSFDataFormat dataFormat = wb.createDataFormat();
> short dateFormat = dataFormat.getFormat("m/d/yy");
> ...
> HSSFCell cell = ... use row.getCell(cell-number) to get the cell
> cell.setCellValue(my java.util.Date value);
> HSSFStyle localStyle = cell.getCellStyle()
> localStyle.setDataFormat(dateFormat);
> cell.setCellStyle(localStyle);
> [/QUOTE]
>
> The above code does set the date value into the cell, and sets the
format
> for the cell to Date
> with a format of "m/d/yy" as desired. However, subsequent cells in the
row
> which contain
> numeric values end up with the same Date formatting.
>
> When I have a numeric value, I just use cell.setCellValue(my double
value)
> - because it looks to me like setCellValue(double) will set the cell
type
> appropriately.
>
> Why when I set a style on one cell, it is picked-up by a subsequent
> column?
>
> I can't just create a generic style for use by all cells, because the
> customer will set cell-specific styles (like background color, etc.)
on
> individual cells, and I don't want to overwrite those.
>
> Can anyone explain what I need to do?
>
> Thanks,
>
>
By the way, I did see the following in the HOW TO under the topic of
'Creating Date Cells':
// we style the second cell as a date (and time). It is important
to
// create a new cell style from the workbook otherwise you can end
up
// modifying the built in style and effecting not only this cell but
other cells.
So I guess my code is resulting in the style being applied to all
cells?
I can't create a new style for every cell because:
1. It results in too many styles and I get an error, and
2. I lose the existing formatting made to the cell by the author of
the
Excel template file
Is there a way I can apply a format to an individual cell without the
above
two problems?
Thanks,
--
View this message in context:
http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-aff
ects-other-cells--tp21362472p21363304.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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Applying a data format to a style for one cell affects other
cells?
Posted by Mark Hansen <na...@mehconsulting.com>.
Mark Hansen wrote:
>
> I'm using POI 3.2-FINAL on Windows/XP SP3.
> My program is opening an Excel template file (.xlt - 2003 version) and
> creating rows/cells to fill the work sheet with data which comes from a
> separate system.
> The feature allows my customer to export there data to .xls, so the
> customer creates the Excel template file, which includes some column
> headers (in row 1) as well as some background colors/boarders on cells,
> etc.
>
> What I want to do is go through my customer's data and create a row in the
> worksheet for each row of data.
>
> When the data is alphanumeric, I just use "setCellValue()" to set the
> value and it comes out fine. When the data is a date value, I want to
> apply a Date-based data format to the cell. So that I don't lose the
> background color (and other formatting) applied to the template by the
> customer, I get the cell's style (using getCellStyle()), apply my Date
> format to it, then set it back, as follows:
>
> [QUOTE]
> ...
> HSSFDataFormat dataFormat = wb.createDataFormat();
> short dateFormat = dataFormat.getFormat("m/d/yy");
> ...
> HSSFCell cell = ... use row.getCell(cell-number) to get the cell
> cell.setCellValue(my java.util.Date value);
> HSSFStyle localStyle = cell.getCellStyle()
> localStyle.setDataFormat(dateFormat);
> cell.setCellStyle(localStyle);
> [/QUOTE]
>
> The above code does set the date value into the cell, and sets the format
> for the cell to Date
> with a format of "m/d/yy" as desired. However, subsequent cells in the row
> which contain
> numeric values end up with the same Date formatting.
>
> When I have a numeric value, I just use cell.setCellValue(my double value)
> - because it looks to me like setCellValue(double) will set the cell type
> appropriately.
>
> Why when I set a style on one cell, it is picked-up by a subsequent
> column?
>
> I can't just create a generic style for use by all cells, because the
> customer will set cell-specific styles (like background color, etc.) on
> individual cells, and I don't want to overwrite those.
>
> Can anyone explain what I need to do?
>
> Thanks,
>
>
By the way, I did see the following in the HOW TO under the topic of
'Creating Date Cells':
// we style the second cell as a date (and time). It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but
other cells.
So I guess my code is resulting in the style being applied to all cells?
I can't create a new style for every cell because:
1. It results in too many styles and I get an error, and
2. I lose the existing formatting made to the cell by the author of the
Excel template file
Is there a way I can apply a format to an individual cell without the above
two problems?
Thanks,
--
View this message in context: http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21363304.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
Re: Applying a data format to a style for one cell affects other cells?
Posted by Anthony Andrews <py...@yahoo.com>.
OK Mark, here is one very simple example - well in the attached files there is.
If you look first at the Excel temple file, you will see that it contains five columns each with a title in the topmost cell. The cell in row two of each column has been formatted - a date, an integer, a floating point number, a piece of text and a currency value; I have kept the formats simple but there is nothing preventing you from changing font, colour, etc, etc. Nothing at all surprising there. I have attached it as it illustrates that part of the solution lies with the templates. If they are well ordered and predictable then that is more than half the battle won.
Next have a look at the Java code - sorry about the name of the class by the way; I was making use of some test code put together for a different purpose.
The first thing to note is that the data is supplied by a two dimensional array of Strings. So, we know what sort of data will be coming from each element of the array. If this does not match your case then you will need to look at ways to identify the data's type; Regular Expressions are probably the best technique IMO.
Overall, the technique is very simple, I open the template file, read the second row as it contains cells with formatting data. Next, the code iterates through the cells on the second row and stores references to the associated HSSFCellStyle into one ArrayList and the integral value that determines the cells type into a second ArrayList.
Nested for loops are used to populate the worksheet. The first simply creates new rows and gets the data from the array. The inner for loop is where more of the action takes place. Using the index number of the for loop, it is possible to create a new cell, get the type of the cell and it's style from the ArrayLists and it's data from the array. Note that the String can be converted appropriately - in the switch statement.
Hope this helps a little. If you need an example that makes use of Regular Expressions, just let me know. I was working on some demonstration code that used specifications contained within an xml file to identify the data type and set the format for the cell. It was intended to be a part of an application that created workbooks from CSV files but I never submitted it.
--- On Thu, 1/8/09, Mark Hansen <na...@mehconsulting.com> wrote:
From: Mark Hansen <na...@mehconsulting.com>
Subject: Applying a data format to a style for one cell affects other cells?
To: user@poi.apache.org
Date: Thursday, January 8, 2009, 3:24 PM
I'm using POI 3.2-FINAL on Windows/XP SP3.
My program is opening an Excel template file (.xlt - 2003 version) and
creating rows/cells to fill the work sheet with data which comes from a
separate system.
The feature allows my customer to export there data to .xls, so the customer
creates the Excel template file, which includes some column headers (in row
1) as well as some background colors/boarders on cells, etc.
What I want to do is go through my customer's data and create a row in the
worksheet for each row of data.
When the data is alphanumeric, I just use "setCellValue()" to set the
value
and it comes out fine. When the data is a date value, I want to apply a
Date-based data format to the cell. So that I don't lose the background
color (and other formatting) applied to the template by the customer, I get
the cell's style (using getCellStyle()), apply my Date format to it, then
set it back, as follows:
[QUOTE]
...
HSSFDataFormat dataFormat = wb.createDataFormat();
short dateFormat = dataFormat.getFormat("m/d/yy");
...
HSSFCell cell = ... use row.getCell(cell-number) to get the cell
cell.setCellValue(my java.util.Date value);
HSSFStyle localStyle = cell.getCellStyle()
localStyle.setDataFormat(dateFormat);
cell.setCellStyle(localStyle);
[/QUOTE]
The above code does set the date value into the cell, and sets the format
for the cell to Date
with a format of "m/d/yy" as desired. However, subsequent cells in
the row
which contain
numeric values end up with the same Date formatting.
When I have a numeric value, I just use cell.setCellValue(my double value) -
because it looks to me like setCellValue(double) will set the cell type
appropriately.
Why when I set a style on one cell, it is picked-up by a subsequent column?
I can't just create a generic style for use by all cells, because the
customer will set cell-specific styles (like background color, etc.) on
individual cells, and I don't want to overwrite those.
Can anyone explain what I need to do?
Thanks,
--
View this message in context:
http://www.nabble.com/Applying-a-data-format-to-a-style-for-one-cell-affects-other-cells--tp21362472p21362472.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