Applying a data format to a style for one cell affects other cells?

[Mark Hansen]

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,

[Mark Hansen]

Post by Mark Hansen
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
[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,

[Winarto]

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:***@mehconsulting.com]
Sent: Friday, 9 January 2009 07:46
To: ***@poi.apache.org
Subject: Re: Applying a data format to a style for one cell affects
other cells?

Post by Mark Hansen
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
[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-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org

[Mark Hansen]

Post by Winarto
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,

[Anthony Andrews]

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 <***@mehconsulting.com> wrote:
From: Mark Hansen <***@mehconsulting.com>
Subject: RE: Applying a data format to a style for one cell affects other cells?
To: ***@poi.apache.org
Date: Thursday, January 8, 2009, 8:39 PM

Post by Winarto
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

Post by Winarto
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,

[Mark Hansen]

Post by Anthony Andrews
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.

Post by Anthony Andrews
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.

Post by Anthony Andrews
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 <***@mehconsulting.com> wrote:
From: Mark Hansen <***@mehconsulting.com>
Subject: RE: Applying a data format to a style for one cell affects other
cells?
To: ***@poi.apache.org
Date: Thursday, January 8, 2009, 8:39 PM

Post by Anthony Andrews
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

Post by Anthony Andrews
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-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org

[Anthony Andrews]

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 <***@mehconsulting.com> wrote:
From: Mark Hansen <***@mehconsulting.com>
Subject: RE: Applying a data format to a style for one cell affects other cells?
To: ***@poi.apache.org
Date: Friday, January 9, 2009, 9:12 AM

Post by Anthony Andrews
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.

Post by Anthony Andrews
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.

Post by Anthony Andrews
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 <***@mehconsulting.com> wrote:
From: Mark Hansen <***@mehconsulting.com>
Subject: RE: Applying a data format to a style for one cell affects other
cells?
To: ***@poi.apache.org
Date: Thursday, January 8, 2009, 8:39 PM

Post by Anthony Andrews
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

Post by Anthony Andrews
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-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org

[Mark Hansen]

Post by Anthony Andrews
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.

Post by Anthony Andrews
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.
Subject: RE: Applying a data format to a style for one cell affects other cells?
Date: Friday, January 9, 2009, 9:12 AM

Post by Anthony Andrews
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.

Post by Anthony Andrews
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.

Post by Anthony Andrews
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

Post by Anthony Andrews
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.
Subject: RE: Applying a data format to a style for one cell affects other cells?
Date: Thursday, January 8, 2009, 8:39 PM

Post by Anthony Andrews
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

Post by Anthony Andrews
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

Post by Anthony Andrews
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,
--
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.
---------------------------------------------------------------------
--
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.
---------------------------------------------------------------------

[David Fisher]

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

Post by Mark Hansen

Post by Anthony Andrews
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.

Post by Anthony Andrews
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.
Subject: RE: Applying a data format to a style for one cell affects other
cells?
Date: Friday, January 9, 2009, 9:12 AM

Post by Anthony Andrews
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.

Post by Anthony Andrews
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.

Post by Anthony Andrews
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

Post by Anthony Andrews
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.
Subject: RE: Applying a data format to a style for one cell affects other
cells?
Date: Thursday, January 8, 2009, 8:39 PM

Post by Anthony Andrews
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

Post by Anthony Andrews
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

Post by Anthony Andrews
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,
--
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.
---------------------------------------------------------------------
--
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.
---------------------------------------------------------------------

--
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.
---------------------------------------------------------------------

[Mark Hansen]

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,

Post by David Fisher
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

[David Fisher]

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.

Post by Mark Hansen
Is it going to be removed in a later release?

I doubt it. It should be deprecated first before it is removed.

Dave

Post by Mark Hansen
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,

Post by David Fisher
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.
---------------------------------------------------------------------

[Anthony Andrews]

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 <***@mehconsulting.com> wrote:
From: Mark Hansen <***@mehconsulting.com>
Subject: Applying a data format to a style for one cell affects other cells?
To: ***@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,