You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Hoppe, Sascha" <Sa...@lineas.de> on 2008/12/18 15:04:41 UTC

How to reuse cell styles?

Hi all,

usually I create cell styles for e.g. date cells this way:

HSSFCellStyle dateTimeStyle = workbook.createCellStyle();
dateTimeStyle.setDataFormat(format.getFormat("h:mm:ss"));
dateTimeStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cell.setCellStyle(dateTimeStyle);

If you do this for a large number of cells and you create a new instance of HSSFCellStyle all the time,
you will run into a "too many data formats" or something similar when you open the .xls. file. 
So usually I would use a cache-mechanism for the cell styles to reuse them and everything will be fine.

But what if a write the workbook to disk and later reopen it. I have a new instance of HSSFWorkbook now and would
like to append rows/cells to it. Of course I would like to reuse the cell styles that are already in the workbook. But I could not find a way to access them. HSSFDataFormat.getFormat(String format) won't do, because POI saves new DataFormats via index. How can my program "remember" how the Styles where stored in the spreadsheet to reuse them on reopening and appending?

thanks for any clues.


--------------------------------------------------------------
Diese E-Mail kann vertrauliche und/oder rechtlich geschützte
Informationen enthalten. Wenn Sie nicht der richtige Adressat sind
oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte
sofort den Absender und vernichten Sie diese Mail. Das unerlaubte
Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.

This e-mail may contain confidential and/or privileged information. If
you are not the intended recipient (or have received this e-mail in
error) please notify the sender immediately and destroy this e-mail.
Any unauthorized copying, disclosure or distribution of the material
in this e-mail is strictly forbidden.


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

Re: How to reuse cell styles?

Posted by Pierre Lavignotte <pi...@gmail.com>.
I remember that someone allready exposed this problem in the list. I'm not
sure but I think that a method to compare 2 styles have been coded
somewhere.
Take a look at the mailing list archive.

Pierre

Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com


On Thu, Dec 18, 2008 at 5:39 PM, Anthony Andrews <py...@yahoo.com>wrote:

> Take a look at the HSSFWorkbook class, and you will see two methods;
> getNumCellStyles() and getCellStyleAt(int index). Call the first to find out
> how many cell styles the Workbook contains then use a for loop to simply
> iterate through, calling the getCellStyleAt() method for each. Obviously,
> you will need to store the objects away someweher - an array, ArrayList,
> etc.
>
> You will still face one problem however. As far as I am aware, the equals()
> method has not be over-ridden for the HSSFCellStyle class. Therefore, you
> will have to devise some way to determine whether two cell styles are the
> same.
>
> --- On Thu, 12/18/08, Hoppe, Sascha <Sa...@lineas.de> wrote:
> From: Hoppe, Sascha <Sa...@lineas.de>
> Subject: How to reuse cell styles?
> To: user@poi.apache.org
> Date: Thursday, December 18, 2008, 6:04 AM
>
> Hi all,
>
> usually I create cell styles for e.g. date cells this way:
>
> HSSFCellStyle dateTimeStyle = workbook.createCellStyle();
> dateTimeStyle.setDataFormat(format.getFormat("h:mm:ss"));
> dateTimeStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
> cell.setCellStyle(dateTimeStyle);
>
> If you do this for a large number of cells and you create a new instance of
> HSSFCellStyle all the time,
> you will run into a "too many data formats" or something similar when
> you open the .xls. file.
> So usually I would use a cache-mechanism for the cell styles to reuse them
> and
> everything will be fine.
>
> But what if a write the workbook to disk and later reopen it. I have a new
> instance of HSSFWorkbook now and would
> like to append rows/cells to it. Of course I would like to reuse the cell
> styles that are already in the workbook. But I could not find a way to
> access
> them. HSSFDataFormat.getFormat(String format) won't do, because POI saves
> new DataFormats via index. How can my program "remember" how the
> Styles where stored in the spreadsheet to reuse them on reopening and
> appending?
>
> thanks for any clues.
>
>
> --------------------------------------------------------------
> Diese E-Mail kann vertrauliche und/oder rechtlich geschützte
> Informationen enthalten. Wenn Sie nicht der richtige Adressat sind
> oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte
> sofort den Absender und vernichten Sie diese Mail. Das unerlaubte
> Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
>
> This e-mail may contain confidential and/or privileged information. If
> you are not the intended recipient (or have received this e-mail in
> error) please notify the sender immediately and destroy this e-mail.
> Any unauthorized copying, disclosure or distribution of the material
> in this e-mail is strictly forbidden.
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
>

Re: How to reuse cell styles?

Posted by Anthony Andrews <py...@yahoo.com>.
Take a look at the HSSFWorkbook class, and you will see two methods; getNumCellStyles() and getCellStyleAt(int index). Call the first to find out how many cell styles the Workbook contains then use a for loop to simply iterate through, calling the getCellStyleAt() method for each. Obviously, you will need to store the objects away someweher - an array, ArrayList, etc.

You will still face one problem however. As far as I am aware, the equals() method has not be over-ridden for the HSSFCellStyle class. Therefore, you will have to devise some way to determine whether two cell styles are the same.

--- On Thu, 12/18/08, Hoppe, Sascha <Sa...@lineas.de> wrote:
From: Hoppe, Sascha <Sa...@lineas.de>
Subject: How to reuse cell styles?
To: user@poi.apache.org
Date: Thursday, December 18, 2008, 6:04 AM

Hi all,

usually I create cell styles for e.g. date cells this way:

HSSFCellStyle dateTimeStyle = workbook.createCellStyle();
dateTimeStyle.setDataFormat(format.getFormat("h:mm:ss"));
dateTimeStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
cell.setCellStyle(dateTimeStyle);

If you do this for a large number of cells and you create a new instance of
HSSFCellStyle all the time,
you will run into a "too many data formats" or something similar when
you open the .xls. file. 
So usually I would use a cache-mechanism for the cell styles to reuse them and
everything will be fine.

But what if a write the workbook to disk and later reopen it. I have a new
instance of HSSFWorkbook now and would
like to append rows/cells to it. Of course I would like to reuse the cell
styles that are already in the workbook. But I could not find a way to access
them. HSSFDataFormat.getFormat(String format) won't do, because POI saves
new DataFormats via index. How can my program "remember" how the
Styles where stored in the spreadsheet to reuse them on reopening and appending?

thanks for any clues.


--------------------------------------------------------------
Diese E-Mail kann vertrauliche und/oder rechtlich geschützte
Informationen enthalten. Wenn Sie nicht der richtige Adressat sind
oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte
sofort den Absender und vernichten Sie diese Mail. Das unerlaubte
Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.

This e-mail may contain confidential and/or privileged information. If
you are not the intended recipient (or have received this e-mail in
error) please notify the sender immediately and destroy this e-mail.
Any unauthorized copying, disclosure or distribution of the material
in this e-mail is strictly forbidden.


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