You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Rob Y <ry...@yahoo.com> on 2008/10/15 20:34:09 UTC
HSSFCellStyle - global styles vs individual cell styles
In Apache POI-HSSF, it seems like the HSSFCellStyle is global to the
workbook.
I assume that means that in order to, say, color cells as you add them, creating
a new HSSFCellStyle for each would be hugely inefficient.
In fact I've seen lots of messages here saying that Excel will complain if you
create too many cell styles.
But if my data source makes it difficult to know all possible cell styles
upfront (especially, since cell styles are combinations of font, color, border,
etc), how can I know when I need to create a new one?
I suppose I could keep a table of all the style parameter combinations I've
encountered, but doesn't the library already do this?
And if I did create a sheet where all the green cells shared a single cell
style, would a user changing a single cell's color end up changing them all?
Or would Excel be smart enough to detect that the cell now needs a new style
created?
I guess this boils down to a few questions:
Is there a way to query your workbook while building it to find out whether a
cell style you want to use matches one that's already in the workbook and to use
that instead?
If so, wouldn't it be nice to automate that and allow you to just set individual
styles without incurring the inefficiency?
I can't imagine Excel has a limit on how many unique combinations of cell
attributes you can have.
If that's true, does xls have some other mechanism for coloring cells, etc that
isn't supported by poi?
Rob
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
> And if I did create a sheet where all the green cells shared a single cell
> style, would a user changing a single cell's color end up changing them all?
> Or would Excel be smart enough to detect that the cell now needs a new style
> created?
I tried the following:
Created 2 cell styles, a green one and an orange one.
Set column 0 to the green style and set all the individual cells in another
column to the orange style.
In both cases, when I recolor individual cells in excel, the other cells of the
same color are not affected.
So excel must be allocating new cell styles in order to handle the change - or
excel has another, simpler mechanism for coloring individual cells.
Anybody know which it is?
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
Sorry if this is a duplicate post - my last one hasn't shown up.
I could certainly do a 2-pass approach and build my own table of styles.
I had just hoped that, since the workbook already has such a table and
a mechanism for iterating through it, it would make sense to use that.
That eliminates the need to build 2-pass logic into my app, and it also
produces something more generic that could be used in other apps.
The only problem seems to be the implementation of 'equals' in
HSSFCellStyle. I see the explicit index test there. I guess they
really want 'equals' to mean 'is the same HSSFCellStyle object'.
So I'd have to code my own 'equivalent' test.
I guess I could almost clone it from the HSSFCellStyle.java (or the
ExtendedFormatRecord.java source which really seems to do the testing),
but then again most of the stuff tested there is probably not public.
Anyway, something like that.
On the regex stuff, I'm familiar with regex syntax - not the java regex
class, but I guess I can pick that up.
You referenced an email and a java attachment. Am I missing something?
Does this forum support attachments? Or did you attempt to send me a
private email? If so, I didn't get it. I had attempted to send you a
private email last week. Did you ever get that?
Thanks,
Rob
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by David Fisher <df...@jmlafferty.com>.
Hi Rob,
I've discussed this with Yegor who is currently focused on XSSF. The
HSSF methods were in progress when we made the decision to help with
OOXML efforts:
>> Dave:
>> There has been a lot of interest in style table management on the
>> user list lately. What are your plans?
>> Will the XSSF version of StyleTables be available in HSSF?
> Yegor:
> Certainly there will be improvements in HSSF style management. One
> of the things to make the caching more effective is to implement the
> standard Java "equals", "clone" and "hashCode" methods. Then we can
> leverage Java containers to store styles and fulfill many of user
> requests.
>
> But all these improvement will be after 3.5-FINAL. I just don't have
> time to work on both HSSF and XSSF.
If anyone wants to volunteer to get these into HSSF for 3.5-FINAL (in
a month) please go ahead and submit patches to Bugzilla!
Thanks.
Regards,
Dave
On Oct 21, 2008, at 12:38 PM, Rob Y wrote:
>
> Hey wait. The same thing's true for HSSFCellStyle:cloneStyleFrom.
> It's
> documented at the online site, but not in the 3.1 or 3.2-FINAL
> distros.
>
> I'm guessing that the online documentation must be for a yet-to-be-
> released
> version. Odd, though, that they'd have the default online
> documentation not
> apply to the current stable version.
>
> Andrew, didn't you say in one of your previous posts that you had used
> cloneStyleFrom in the past?
>
>
> --
> View this message in context: http://www.nabble.com/HSSFCellStyle---global-styles-vs-individual-cell-styles-tp19999572p20095528.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: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
Yes, cloneStyleFrom() was a method a couple of us used when copying sheets across from one workbook to another. I cannot remember who it was but someone asked Nick who recommended that we try one or other of the nightly builds available from the Torchbox site that included that method - I am sure that I have the relevant archive here somewhere but did not continue to use it as the 'final' releases are obviously fully tested and intended for production environments.
--- On Tue, 10/21/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: Re: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Tuesday, October 21, 2008, 10:38 AM
Hey wait. The same thing's true for HSSFCellStyle:cloneStyleFrom.
It's
documented at the online site, but not in the 3.1 or 3.2-FINAL distros.
I'm guessing that the online documentation must be for a yet-to-be-released
version. Odd, though, that they'd have the default online documentation
not
apply to the current stable version.
Andrew, didn't you say in one of your previous posts that you had used
cloneStyleFrom in the past?
--
View this message in context:
http://www.nabble.com/HSSFCellStyle---global-styles-vs-individual-cell-styles-tp19999572p20095528.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: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
Hey wait. The same thing's true for HSSFCellStyle:cloneStyleFrom. It's
documented at the online site, but not in the 3.1 or 3.2-FINAL distros.
I'm guessing that the online documentation must be for a yet-to-be-released
version. Odd, though, that they'd have the default online documentation not
apply to the current stable version.
Andrew, didn't you say in one of your previous posts that you had used
cloneStyleFrom in the past?
--
View this message in context: http://www.nabble.com/HSSFCellStyle---global-styles-vs-individual-cell-styles-tp19999572p20095528.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: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
> Quite a few people have asked for a method to set the style for a row but it
does not yet exist as far as I am aware.
> Quite possibly there is a plan to add this into HSSF and that a stub has been
added to the API but not yet been implemented.
That'd be nice. The weird thing is that the documentation of these methods
only appears in the online docs at http://poi.apache.org/apidocs/index.html.
They're not documented in the source or bin distros. Not even in the new
3.2-FINAL distro that just came out last Sunday.
Go figure...
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
Hello again Rob.
Quite a few people have asked for a method to set the style for a row but it does not yet exist as far as I am aware. Quite possibly there is a plan to add this into HSSF and that a stub has been added to the API but not yet been implemented.
--- On Mon, 10/20/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: Re: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Monday, October 20, 2008, 3:31 PM
Thanks. I didn't get your email, but I was able to download your sample
from the URL you provided. The sample works kind of like what I've coded
as a first cut to play around with hssf.
I pre-allocated styles for date cells and 'orange' cells, then applied
the
styles on an as needed basis (odd-numbered columns in orange and auto-
detected dates).
Where it gets really complicated is when you start combining individual
cell attributes. E.g. a date cell that's colored orange and displayed in
italics with a border at the top only. All those things are elements of
a cell style. To pre-allocate them, you need to build a table of all the
combinations in your data set. Not just a 'date' cell, but every combo
of type, color, font, border, etc. Scanning that table seems the same as
scanning all the styles in the workbook. Sure, it's inefficient, but at
some point you're gonna need to do it.
Hopefully, using column styles and row styles would pare down the list
significantly, so that scanning wouldn't be too bad.
Anyway, I think I know what my options are. Either maintain my own list
or write code to compare existing styles to what a cell's requesting.
Thanks for all the help.
By the way. I posted another question today that hasn't shown up yet.
The 3.1 final docs say that HSSFRow has a setRowStyle method, but it's not
really there in HSSFRow.java. As I said above, having row and column
styles will cut down on the number of times I have to locate a style in
my list. If there's no setRowStyle, though, then it becomes more efficient
to maintain the style list outside the workbook.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
Thanks. I didn't get your email, but I was able to download your sample
from the URL you provided. The sample works kind of like what I've coded
as a first cut to play around with hssf.
I pre-allocated styles for date cells and 'orange' cells, then applied the
styles on an as needed basis (odd-numbered columns in orange and auto-
detected dates).
Where it gets really complicated is when you start combining individual
cell attributes. E.g. a date cell that's colored orange and displayed in
italics with a border at the top only. All those things are elements of
a cell style. To pre-allocate them, you need to build a table of all the
combinations in your data set. Not just a 'date' cell, but every combo
of type, color, font, border, etc. Scanning that table seems the same as
scanning all the styles in the workbook. Sure, it's inefficient, but at
some point you're gonna need to do it.
Hopefully, using column styles and row styles would pare down the list
significantly, so that scanning wouldn't be too bad.
Anyway, I think I know what my options are. Either maintain my own list
or write code to compare existing styles to what a cell's requesting.
Thanks for all the help.
By the way. I posted another question today that hasn't shown up yet.
The 3.1 final docs say that HSSFRow has a setRowStyle method, but it's not
really there in HSSFRow.java. As I said above, having row and column
styles will cut down on the number of times I have to locate a style in
my list. If there's no setRowStyle, though, then it becomes more efficient
to maintain the style list outside the workbook.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
Hello Rob
I just replied to one of your previous messages and I think it went to a yahoo account. To save you waiting any longer, I have uploaded the files I spoke of to Rapidshare. All you need to do is follow this link, choose the free option and download the zip file;
http://rapidshare.com/files/155856446/New_ALZip_zip_File.zip.html
There is a class that reads a CSV file and loads it into an Excel workbook along with a small CSV file containing the sort of data I used to test the file. Forgive the quality of the code please, it was early on a Saturday morning and I am certain it could be refactored.
Talking again about the styles, if you think about it, you already have to identify the different types of data to write them into cells so it is easier and quicker in my opinion to determine which style to apply at the same time. Creating the styles you require at the start of the process will not take too long and it has to be quicker than creating a style as you encounter a value read from the CSV file, then comparing it with all of the others you already have to determine if a suitable style already exists and then setting the style for the cell. Comparing styles is not so difficult but it is inefficient IMO, the technique I used was simply to call the getXXX() methods for the styles I wanted to compare and then actually test the values returned against one another!! Not pretty but it did work. Finally, you could use the workbooks list if you like as it is possible to call the getCellStyleAt(index) method but you would still need to remember - or
rather your application would - which index value would return you a particular style.
--- On Mon, 10/20/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: Re: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Monday, October 20, 2008, 8:28 AM
> After getting up early to watch the qualifying for the next Grand Prix,
I had a few hours to think about the application you are working on and to
throw together a bit of code.Am I correct in assuming that you are reading
in data from a CSV file and using that to populate an Excel spreadsheet?
If so then you may not have any problems with cell styles at all.Typically,
in such an application, you will know in advance just how many different
sorts of cell you will be dealing with and, therefore, how many different
cell styles you need to create. Usually, the problem becomes identifying
the different data types and populating the spreadsheet accordingly.
Yeah, I suppose I could do that. But my point was that *something's* gonna
have to maintain a list of all the styles I need. I just thought it would
be neater, since the workbook already maintains a list of all the styles
it's been presented with, if I could use the workbook's list instead of
maintaining my own list.
That technique would be more general-purpose. Also, it would save me from
having to run a 2-pass process. My spreadheet is *mostly* populated from
csv data, but there will be other stuff too.
> Take a look at the Java file I have attached to this e-mail. It processes
the CSV file that I have also attached and populates a spreadsheet with the
data.
I don't see an attachment here. Did you send me a personal email? If so,
I didn't get it.
I'm pretty familiar with regular expression syntax (years of 'vi'
use'll
do that), I just don't know the Java regex library, but I guess I'll
need
to learn that eventually...
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
> After getting up early to watch the qualifying for the next Grand Prix,
I had a few hours to think about the application you are working on and to
throw together a bit of code.Am I correct in assuming that you are reading
in data from a CSV file and using that to populate an Excel spreadsheet?
If so then you may not have any problems with cell styles at all.Typically,
in such an application, you will know in advance just how many different
sorts of cell you will be dealing with and, therefore, how many different
cell styles you need to create. Usually, the problem becomes identifying
the different data types and populating the spreadsheet accordingly.
Yeah, I suppose I could do that. But my point was that *something's* gonna
have to maintain a list of all the styles I need. I just thought it would
be neater, since the workbook already maintains a list of all the styles
it's been presented with, if I could use the workbook's list instead of
maintaining my own list.
That technique would be more general-purpose. Also, it would save me from
having to run a 2-pass process. My spreadheet is *mostly* populated from
csv data, but there will be other stuff too.
> Take a look at the Java file I have attached to this e-mail. It processes
the CSV file that I have also attached and populates a spreadsheet with the
data.
I don't see an attachment here. Did you send me a personal email? If so,
I didn't get it.
I'm pretty familiar with regular expression syntax (years of 'vi' use'll
do that), I just don't know the Java regex library, but I guess I'll need
to learn that eventually...
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
Rob
After getting up early to watch the qualifying for the next Grand Prix, I had a few hours to think about the application you are working on and to throw together a bit of code.
Am I correct in assuming that you are reading in data from a CSV file and using that to populate an Excel spreadsheet? If so then you may not have any problems with cell styles at all.
Typically, in such an application, you will know in advance just how many different sorts of cell you will be dealing with and, therefore, how many different cell styles you need to create. Usually, the problem becomes identifying the different data types and populating the spreadsheet accordingly. Take a look at the Java file I have attached to this e-mail. It processes the CSV file that I have also attached and populates a spreadsheet with the data. If you look at the code, you will be able to see that it uses regular expressions to determine the data type and sets the cell type and style accordingly.
The regular expressions bit does look intimidating at first but there is lots of help on the internet to help you define expressions that work properly and all you need to do is become used to the syntax. To get you started, my code uses this regular expression to check for dates;
(0[1-9]|[12][0-9]|3[01]) [- /.] (0[1-9]|1[012]) [- /.] (((19|20)\\d\\d)|(\\d\\d))
Look for this pattern in that string - [- /.] - you should see it twice because it is checking for the separator which in this case could be a hyphen, a space, a foward slash or a full stop. Now look at the sub-expression that precedes the first separator pattern;
(0[1-9] | [12][0-9] | 3[01])
This will ensure that the day portion of the date is valid. As with Java, the | character can be read as 'or' so again, you can break this down into sections 0[1-9], [12][0-9] and 3[01]. The first checks for values in the range 0, 1, 2, 3, 4, 5, 6, 7, 8 and 9, the second for values in the range 10 to 29 and the final portion for 30 and 31. Looking at the first section in more detail, it says, in effect, a leading zero followed by a single digit in the range 1 to 9 is valid. In similar fashion, the third section says in effect that a 3 followed be either a zero or a one is valid. Luckilly the second section becomes easier to understand once you grasp this basic syntax, it says that the first digit can be either a 1 or a 2 and the second digit and number in the range 0 to 9.
Have a look and see if it makes sense to you. Also, think about the cell styles issue as I do not think that you need to worry about comparing styles at all, you can simply set them up before processing the data and apply accordingly. The challenge then becomes determining when to apply which style.
--- On Fri, 10/17/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: Re: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Friday, October 17, 2008, 11:56 AM
> Of course I will Rob. At home now so it will take me a few hours to search
the
code out. There is one limit
> however, I was working with just a single workbook and comparing styles
within
the workbook so to speak. I
> know that problems can arise if you are using more than one workbook - for
example if you are copying over
> from one workbook to another - with fonts colours etc.
I'm in luck - one workbook, one worksheet - for now ;)
For yucks, I tried writing a function that createsappropriate cell types
from a string. Pretty easy (not ready to try regex's yet, but...):
// Add a new cell
public void addCell(int rownum, int colnum, String text)
{
HSSFRow r = sheet.getRow((short) rownum);
HSSFCell c = r.createCell((short) colnum);
// Determine data type. Try numeric first.
try
{
double value = java.lang.Double.parseDouble(text);
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
c.setCellValue(value);
}
catch (NumberFormatException nfe)
{
// Not numeric, try date.
try
{
SimpleDateFormat df = new SimpleDateFormat("MM/d/yy");
Date date = df.parse(text);
c.setCellStyle(datestyle);
c.setCellValue(date);
}
catch (ParseException pe)
{
// Not date, default to text
HSSFRichTextString s = new HSSFRichTextString(text);
c.setCellStyle(orangecell);
c.setCellType(HSSFCell.CELL_TYPE_STRING);
c.setCellValue(s);
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
> Of course I will Rob. At home now so it will take me a few hours to search the
code out. There is one limit
> however, I was working with just a single workbook and comparing styles within
the workbook so to speak. I
> know that problems can arise if you are using more than one workbook - for
example if you are copying over
> from one workbook to another - with fonts colours etc.
I'm in luck - one workbook, one worksheet - for now ;)
For yucks, I tried writing a function that createsappropriate cell types
from a string. Pretty easy (not ready to try regex's yet, but...):
// Add a new cell
public void addCell(int rownum, int colnum, String text)
{
HSSFRow r = sheet.getRow((short) rownum);
HSSFCell c = r.createCell((short) colnum);
// Determine data type. Try numeric first.
try
{
double value = java.lang.Double.parseDouble(text);
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
c.setCellValue(value);
}
catch (NumberFormatException nfe)
{
// Not numeric, try date.
try
{
SimpleDateFormat df = new SimpleDateFormat("MM/d/yy");
Date date = df.parse(text);
c.setCellStyle(datestyle);
c.setCellValue(date);
}
catch (ParseException pe)
{
// Not date, default to text
HSSFRichTextString s = new HSSFRichTextString(text);
c.setCellStyle(orangecell);
c.setCellType(HSSFCell.CELL_TYPE_STRING);
c.setCellValue(s);
}
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Nick Burch <ni...@torchbox.com>.
On Fri, 17 Oct 2008, Rob Y wrote:
> That's the way I was going to do it too. I was just hoping that I could
> use equals to simplify the check for 'style already present'.
Alas HSSFCellStyle.equals() won't do what you want here - it checks that
the style indexes are the same, and that the format records are the same.
For the case where you're creating a new HSSFCellStyle and setting some
properties onto it, these two won't be the case
You might have some luck with checking if the underlying
ExtendedFormatRecords match, but I can't be sure
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
Of course I will Rob. At home now so it will take me a few hours to search the code out. There is one limit however, I was working with just a single workbook and comparing styles within the workbook so to speak. I know that problems can arise if you are using more than one workbook - for example if you are copying over from one workbook to another - with fonts colours etc.
--- On Fri, 10/17/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: Re: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Friday, October 17, 2008, 8:16 AM
> The method I have used in the past is to create a cell, add it to a row,
insert the data, create a new cell style
> and then check to see if I already have one with the relevant attributes.
If
so, apply that to the cell, if not
> apply the new one to the cell and add it to the pool.
That's the way I was going to do it too. I was just hoping that I could
use
equals to simplify the check for 'style already present'.
Would you mind sharing the code you use to compare all the style attributes?
Do you use the PropertyList class that the cell utility uses? Again, I'm
new to java and apache, so I don't know what's available in the
libraries of
either.
Thanks again,
Rob
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
> The method I have used in the past is to create a cell, add it to a row,
insert the data, create a new cell style
> and then check to see if I already have one with the relevant attributes. If
so, apply that to the cell, if not
> apply the new one to the cell and add it to the pool.
That's the way I was going to do it too. I was just hoping that I could use
equals to simplify the check for 'style already present'.
Would you mind sharing the code you use to compare all the style attributes?
Do you use the PropertyList class that the cell utility uses? Again, I'm
new to java and apache, so I don't know what's available in the libraries of
either.
Thanks again,
Rob
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
The cloneFromStyle() method works fine, I have made use of it myself recently. As for the equlas() method, after taking a look at the javadoc - not the source so I cannot be certain and could very well be wrong - I think this method has been inherited not overridden. Therefore, it may only check for object equality and it is up to the users of the API to monitor the individual attributes of the style.
The method I have used in the past is to create a cell, add it to a row, insert the data, create a new cell style and then check to see if I already have one with the relevant attributes. If so, apply that to the cell, if not apply the new one to the cell and add it to the pool.
--- On Thu, 10/16/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: Re: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Thursday, October 16, 2008, 1:03 PM
> Sorry if you wasted any time with the cell utility class, I only spotted it
whilst having a quick break and did
> not have the opportunity to test it out.
Not at all.
It was useful, if only to confirm that others have attempted to solve the
problem. And in a similar way.
I contacted the author of the cell utility class to see if he wants to work
together to extend it to do more.
Question about scanning the existing styles, though. You said you'd have
to
check each attribute individually. I had thought I could use cloneStyleFrom
to create a temporary style, fill it in, and then use the 'equals'
operator
of HSSFCellStyle to just compare all the attributes at once when scanning
the list.
Do you think that won't work? If so, why?
Rob
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
> Sorry if you wasted any time with the cell utility class, I only spotted it
whilst having a quick break and did
> not have the opportunity to test it out.
Not at all.
It was useful, if only to confirm that others have attempted to solve the
problem. And in a similar way.
I contacted the author of the cell utility class to see if he wants to work
together to extend it to do more.
Question about scanning the existing styles, though. You said you'd have to
check each attribute individually. I had thought I could use cloneStyleFrom
to create a temporary style, fill it in, and then use the 'equals' operator
of HSSFCellStyle to just compare all the attributes at once when scanning
the list.
Do you think that won't work? If so, why?
Rob
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
Ah well, got too excited about both of these issues it seems.
Martin's suggestion of using regular expressions to sort out the data type of the String you read from the CSV file is looking like the best bet for you on the one hand it seems. Further, I reckon you will have to handle style pooling yourself. I did something similar for copying worksheets from one workbook to another and it is a quite simple task - if a little long-winded as you will have to compare each style attribute.
Sorry if you wasted any time with the cell utility class, I only spotted it whilst having a quick break and did not have the opportunity to test it out.
--- On Thu, 10/16/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: Re: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Thursday, October 16, 2008, 9:34 AM
> I have just been looking through the api docs and come across a class
called
HSSFCellUtil. One if it's
> methods allows you to create a cell from a String and to pass a style
object
in addition.
Thanks for pointing me to this. It doesn't do what I want, but it verfies
that
I can do it myself in the way I thought I'd have to.
Unfortunately, the createCell method just applies a style to the cell.
Presumably the caller's responsible for creating the style, leaving the
issue
of sharing styles unaddressed:
Here's the createCell code from HSSFCellUtil:
public static HSSFCell createCell( HSSFRow row, int column, String value,
HSSFCellStyle style )
{
HSSFCell cell = getCell( row, column );
cell.setCellValue(new HSSFRichTextString(value));
if ( style != null )
{
cell.setCellStyle( style );
}
return cell;
}
The other method, setCellStyleProperty kind of does what I want.
It scans all the styles in the workbook, looking for one with the property you
asked for. If it finds one, it uses it. If not, it allocates a new one.
The problem with this method is that it only supports one property at a time.
So if you wanted to set font, color, data type, etc. You'd end up creating
a
series of styles that accumulate these properties one at a time.
Only the last style would actually end up referenced by the cell, but the
others would still be there in the workbook - kind of silly.
What I want is to create a reference style (via cloneStyleFrom?), set all the
properties I want and then pass that in to an API that does what
setCellStyleProperty does, but based on matching all the properties in a style
at once.
Sound right?
Here's the setCellStyleProperty code from HSSFCellUtil:
public static void setCellStyleProperty( HSSFCell cell, HSSFWorkbook
workbook, String propertyName, Object propertyValue )
throws NestableException
{
try
{
HSSFCellStyle originalStyle = cell.getCellStyle();
HSSFCellStyle newStyle = null;
Map values = PropertyUtils.describe( originalStyle );
values.put( propertyName, propertyValue );
values.remove( "index" );
// index seems like what index the cellstyle is in the list of
styles for a workbook.
// not good to compare on!
short numberCellStyles = workbook.getNumCellStyles();
for ( short i = 0; i < numberCellStyles; i++ )
{
HSSFCellStyle wbStyle = workbook.getCellStyleAt( i );
Map wbStyleMap = PropertyUtils.describe( wbStyle );
wbStyleMap.remove( "index" );
if ( wbStyleMap.equals( values ) )
{
newStyle = wbStyle;
break;
}
}
if ( newStyle == null )
{
newStyle = workbook.createCellStyle();
newStyle.setFont( workbook.getFontAt(
originalStyle.getFontIndex() ) );
PropertyUtils.copyProperties( newStyle, originalStyle );
PropertyUtils.setProperty( newStyle, propertyName,
propertyValue );
}
cell.setCellStyle( newStyle );
}
catch ( Exception e )
{
e.printStackTrace();
throw new NestableException( "Couldn't
setCellStyleProperty.", e );
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Rob Y <ry...@yahoo.com>.
> I have just been looking through the api docs and come across a class called
HSSFCellUtil. One if it's
> methods allows you to create a cell from a String and to pass a style object
in addition.
Thanks for pointing me to this. It doesn't do what I want, but it verfies that
I can do it myself in the way I thought I'd have to.
Unfortunately, the createCell method just applies a style to the cell.
Presumably the caller's responsible for creating the style, leaving the issue
of sharing styles unaddressed:
Here's the createCell code from HSSFCellUtil:
public static HSSFCell createCell( HSSFRow row, int column, String value,
HSSFCellStyle style )
{
HSSFCell cell = getCell( row, column );
cell.setCellValue(new HSSFRichTextString(value));
if ( style != null )
{
cell.setCellStyle( style );
}
return cell;
}
The other method, setCellStyleProperty kind of does what I want.
It scans all the styles in the workbook, looking for one with the property you
asked for. If it finds one, it uses it. If not, it allocates a new one.
The problem with this method is that it only supports one property at a time.
So if you wanted to set font, color, data type, etc. You'd end up creating a
series of styles that accumulate these properties one at a time.
Only the last style would actually end up referenced by the cell, but the
others would still be there in the workbook - kind of silly.
What I want is to create a reference style (via cloneStyleFrom?), set all the
properties I want and then pass that in to an API that does what
setCellStyleProperty does, but based on matching all the properties in a style
at once.
Sound right?
Here's the setCellStyleProperty code from HSSFCellUtil:
public static void setCellStyleProperty( HSSFCell cell, HSSFWorkbook
workbook, String propertyName, Object propertyValue )
throws NestableException
{
try
{
HSSFCellStyle originalStyle = cell.getCellStyle();
HSSFCellStyle newStyle = null;
Map values = PropertyUtils.describe( originalStyle );
values.put( propertyName, propertyValue );
values.remove( "index" );
// index seems like what index the cellstyle is in the list of
styles for a workbook.
// not good to compare on!
short numberCellStyles = workbook.getNumCellStyles();
for ( short i = 0; i < numberCellStyles; i++ )
{
HSSFCellStyle wbStyle = workbook.getCellStyleAt( i );
Map wbStyleMap = PropertyUtils.describe( wbStyle );
wbStyleMap.remove( "index" );
if ( wbStyleMap.equals( values ) )
{
newStyle = wbStyle;
break;
}
}
if ( newStyle == null )
{
newStyle = workbook.createCellStyle();
newStyle.setFont( workbook.getFontAt(
originalStyle.getFontIndex() ) );
PropertyUtils.copyProperties( newStyle, originalStyle );
PropertyUtils.setProperty( newStyle, propertyName, propertyValue );
}
cell.setCellStyle( newStyle );
}
catch ( Exception e )
{
e.printStackTrace();
throw new NestableException( "Couldn't setCellStyleProperty.", e );
}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
Ignore all of this message.
I have just been looking through the api docs and come across a class called HSSFCellUtil. One if it's methods allows you to create a cell from a String and to pass a style object in addition. To quote from the documentation;
Various utility functions that make working with a cells and rows easier. The various
methods that deal with style's allow you to create your HSSFCellStyles as you need them.
When you apply a style change to a cell, the code will attempt to see if a style already
exists that meets your needs. If not, then it will create a new style. This is to prevent
creating too many styles. there is an upper limit in Excel on the number of styles that
can be supported.
The section I liked was 'the code will attempt to see if a style already exists.......'
--- On Thu, 10/16/08, Anthony Andrews <py...@yahoo.com> wrote:
From: Anthony Andrews <py...@yahoo.com>
Subject: Re: HSSFCellStyle - global styles vs individual cell styles
To: "POI Users List" <us...@poi.apache.org>
Date: Thursday, October 16, 2008, 12:10 AM
You can easilly add this feature Rob.
Think about simple 'pooling' of which there are many examples in the
core API; all you need to do is create some way of keeping track of the cell
styles as you create them. As a crude example, you could use an ArrayList to
hold references to all of your cell styles. As you come across a requirement for
a new cell style simply query the ArrayList to see if one with those attributes
already exists and if it does grab the exiting reference to it. If a suitable
cell style does not exist, add the new one to the list...etc. Or, you could
simply have a method called something like removeDuplicateStyles() that you call
prior to saving the workbook away. This method would iterate thropugh the sheet
removing any duplicate cell styles. I am not too sure about this and you would
have to test it but I do believ that the equals() method the the HSSFCellStyle
class has been over-ridden to make it easy to compare cell styles for equality.
I do have to agree with you
however that this would incur some overhead but I do not feel that there is
anyway to avoid it.
By the way, the limit on cell styles IS imposed by Excel and POI merely has to
work within the constraint. HSSF is a community project and I am pretty sure
that if you want to contribute a feature or features that could improve this
aspect of the API, then the development team would respond positivly if you were
to approach them with an idea.
--- On Wed, 10/15/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Wednesday, October 15, 2008, 11:34 AM
In Apache POI-HSSF, it seems like the HSSFCellStyle is global to the
workbook.
I assume that means that in order to, say, color cells as you add them,
creating
a new HSSFCellStyle for each would be hugely inefficient.
In fact I've seen lots of messages here saying that Excel will complain if
you
create too many cell styles.
But if my data source makes it difficult to know all possible cell styles
upfront (especially, since cell styles are combinations of font, color, border,
etc), how can I know when I need to create a new one?
I suppose I could keep a table of all the style parameter combinations I've
encountered, but doesn't the library already do this?
And if I did create a sheet where all the green cells shared a single cell
style, would a user changing a single cell's color end up changing them
all?
Or would Excel be smart enough to detect that the cell now needs a new style
created?
I guess this boils down to a few questions:
Is there a way to query your workbook while building it to find out whether a
cell style you want to use matches one that's already in the workbook and
to use
that instead?
If so, wouldn't it be nice to automate that and allow you to just set
individual
styles without incurring the inefficiency?
I can't imagine Excel has a limit on how many unique combinations of cell
attributes you can have.
If that's true, does xls have some other mechanism for coloring cells, etc
that
isn't supported by poi?
Rob
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSFCellStyle - global styles vs individual cell styles
Posted by Anthony Andrews <py...@yahoo.com>.
You can easilly add this feature Rob.
Think about simple 'pooling' of which there are many examples in the core API; all you need to do is create some way of keeping track of the cell styles as you create them. As a crude example, you could use an ArrayList to hold references to all of your cell styles. As you come across a requirement for a new cell style simply query the ArrayList to see if one with those attributes already exists and if it does grab the exiting reference to it. If a suitable cell style does not exist, add the new one to the list...etc. Or, you could simply have a method called something like removeDuplicateStyles() that you call prior to saving the workbook away. This method would iterate thropugh the sheet removing any duplicate cell styles. I am not too sure about this and you would have to test it but I do believ that the equals() method the the HSSFCellStyle class has been over-ridden to make it easy to compare cell styles for equality. I do have to agree with you
however that this would incur some overhead but I do not feel that there is anyway to avoid it.
By the way, the limit on cell styles IS imposed by Excel and POI merely has to work within the constraint. HSSF is a community project and I am pretty sure that if you want to contribute a feature or features that could improve this aspect of the API, then the development team would respond positivly if you were to approach them with an idea.
--- On Wed, 10/15/08, Rob Y <ry...@yahoo.com> wrote:
From: Rob Y <ry...@yahoo.com>
Subject: HSSFCellStyle - global styles vs individual cell styles
To: user@poi.apache.org
Date: Wednesday, October 15, 2008, 11:34 AM
In Apache POI-HSSF, it seems like the HSSFCellStyle is global to the
workbook.
I assume that means that in order to, say, color cells as you add them,
creating
a new HSSFCellStyle for each would be hugely inefficient.
In fact I've seen lots of messages here saying that Excel will complain if
you
create too many cell styles.
But if my data source makes it difficult to know all possible cell styles
upfront (especially, since cell styles are combinations of font, color, border,
etc), how can I know when I need to create a new one?
I suppose I could keep a table of all the style parameter combinations I've
encountered, but doesn't the library already do this?
And if I did create a sheet where all the green cells shared a single cell
style, would a user changing a single cell's color end up changing them
all?
Or would Excel be smart enough to detect that the cell now needs a new style
created?
I guess this boils down to a few questions:
Is there a way to query your workbook while building it to find out whether a
cell style you want to use matches one that's already in the workbook and
to use
that instead?
If so, wouldn't it be nice to automate that and allow you to just set
individual
styles without incurring the inefficiency?
I can't imagine Excel has a limit on how many unique combinations of cell
attributes you can have.
If that's true, does xls have some other mechanism for coloring cells, etc
that
isn't supported by poi?
Rob
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org