poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Reeve, Adam" <adam.re...@gs.com>
Subject RE: creating second hssf sheet
Date Wed, 09 Jul 2003 08:53:06 GMT
I had the exact same problem, i.e. as each cell was created I had to set
it's style (and number format) based on it's contents. This meant the sample
code's simplistic approach of creating a few styles outside the population
loop and using them inside wouldn't work as I couldn't tell ahead of time
which styles I'd need. So I wrote a class to provide a cache of styles - the
cell creation code requests a HSSFCellStyle object from the cache specifying
the properties it needs, the cache either gives it one it has previously
created or creates a new one and caches it. It works very well, my sheets
with 4000 cells which used to have 4000 styles (and so refuse to load) now
have between 15 and 25 styles depending on the exact content. The file size
is smaller and it takes less time to generate.

I strongly recommend you take a similar approach! The class is a bit big to
just copy & paste in here, but I have put some of the useful bits below.
Note - the version I use currently only uses the horizontal alignment and
number format fields to differentiate between styles, as they are the only
things which change in my application. You can easily add extra fields to
the getStyle() method and InternKey class as required.

     * Get a reference to a HSSFCellStyle from the cache. If an appropriate
style does not yet exist in the cache it
     * will first be created.
     * <p>
     * As the object returned will be shared between many cells, it is
imperative that the caller not modify any of it's
     * properties.
     * @param  dataFormatMask      the Excel format string used when the
cell has numeric contents
     * @param  horizontalAlignment see {@link HSSFCellStyle#ALIGN_CENTER},
{@link HSSFCellStyle#ALIGN_LEFT} etc
     * @return                     a properly configured HSSFCellStyle
    public HSSFCellStyle getStyle(String dataFormatMask, short
horizontalAlignment) {
        InternKey key = new InternKey(dataFormatMask, horizontalAlignment);
        Object value = internCache.get(key);
        if (value != null) {
            return (HSSFCellStyle) value;
        } else {
            logger.fine("Creating and interning new HSSFCellStyle : " +
            HSSFCellStyle newStyle = createStyle(key);
            internCache.put(key, newStyle);		// internCache is a
            logger.fine("Current cache size is " + internCache.size());
            return newStyle;

     * Creates a new style object to match the supplied key
     * @param  key                 the key specifying the properties of the
required style
     * @return                     a new HSSFCellStyle object
    private HSSFCellStyle createStyle(InternKey key) {
        HSSFCellStyle style = workbook.createCellStyle();
        return style;

     * Static inner class used as a key in the cache map. Holds the values
of the distinguishing properties of the
     * underlying HSSFCellStyle objects.
    private static class InternKey {
        private String dataFormatMask;
        private short horizontalAlignment;

         * Create a new key
         * @param  dataFormatMask      the Excel format string used when the
cell has numeric contents
         * @param  horizontalAlignment see {@link
HSSFCellStyle#ALIGN_CENTER}, {@link HSSFCellStyle#ALIGN_LEFT} etc
        public InternKey(String dataFormatMask, short horizontalAlignment) {
            this.dataFormatMask = dataFormatMask;
            this.horizontalAlignment = horizontalAlignment;

         * Override equals to check for equivalence of all the
distinguishing fields.
         * @param o an instance of InternKey
         * @return true iff all the properties of o match those of this
        public boolean equals(Object o) {
            if (this == o) return true;
            if (!(o instanceof InternKey)) return false;

            final InternKey internKey = (InternKey) o;

            if (horizontalAlignment != internKey.horizontalAlignment) return
            if (dataFormatMask != null ?
!dataFormatMask.equals(internKey.dataFormatMask) : internKey.dataFormatMask
!= null) return false;

            return true;

         * Override hashCode() to match equals()
        public int hashCode() {
            int result;
            result = (dataFormatMask != null ? dataFormatMask.hashCode() :
            result = 29 * result + (int) horizontalAlignment;
            return result;


-----Original Message-----
From: Singh, Arshi (Indsys) [mailto:Arshi.Singh@geind.ge.com] 
Sent: Wednesday, July 09, 2003 6:51 AM
To: POI Users List
Subject: RE: creating second hssf sheet

  I went through the sample code in FAQ. My requirement is such that i need
a no. of fonts n styles. Thats why i was creating a new style n font
everytime i needed one. But instead if i try to use the same style by
changing all the values back to normal, then the latest style n font gets
populated for the previous cells also.
HSSFCellStyle cell_style = wb.createCellStyle();

	Now its the ALIGN_GENERAL (second style) that gets set for the first
cell also. I believe there is a font table for storing all the workbook
fonts. Is there a way to delete fonts from this, after one sheet is
made...so that space for more fonts can be made available.

-----Original Message-----
From: Michael Zalewski [mailto:zalewski@optonline.net]
Sent: Wednesday, July 09, 2003 9:30 AM
To: POI Users List
Subject: RE: creating second hssf sheet

It probably doesn't help much, cuz the real URL is


And the short answer is that you have to limit the number of HSSFStyle
objects that you create. If you create a new one for each cell, you make too
many for Excel to handle. The FAQ contains good sample code.

-----Original Message-----
From: Avik Sengupta [mailto:avik@apache.org]
Sent: Tuesday, July 08, 2003 11:32 AM
To: POI Users List
Subject: Re: creating second hssf sheet

Does this help?

On Tue, 2003-07-08 at 18:46, Singh, Arshi (Indsys) wrote:
> Hi,
>    I have created a new workbook using POI hssf and made one excel 
> sheet containing a number of fonts, styles etc. After that when i try 
> to create
> second sheet , it gives problems.
>       If i try to create a small sheet 2 with a few lines of different 
> fonts then the fonts do not show as desired in sheet 2 and if i try to 
> create a big sheet 2 having tables and orientations then the my output
> file does not even open up and shows the error "Too Many Different 
> Cell Formats".
>       Can somebody please help me detect the error. Could it be 
> because of some buffer in memory ???? Thanks,
> Arshi.

[useless trailers snipped]

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

ADDRESSEE and may contain confidential and privileged information. If the
reader of this message is not the intended recipient, you are notified that
any dissemination, distribution or copy of this 
communication is strictly Prohibited. 
If you have received this message by error, please notify us 
immediately, return the original mail to the sender and delete the 
message from your system."

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

View raw message