KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > apache > poi > hssf > usermodel > contrib > HSSFCellUtil


1
2 /* ====================================================================
3    Copyright 2002-2004 Apache Software Foundation
4
5    Licensed under the Apache License, Version 2.0 (the "License");
6    you may not use this file except in compliance with the License.
7    You may obtain a copy of the License at
8
9        http://www.apache.org/licenses/LICENSE-2.0
10
11    Unless required by applicable law or agreed to in writing, software
12    distributed under the License is distributed on an "AS IS" BASIS,
13    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14    See the License for the specific language governing permissions and
15    limitations under the License.
16 ==================================================================== */
17         
18
19 package org.apache.poi.hssf.usermodel.contrib;
20
21
22 import org.apache.commons.beanutils.PropertyUtils;
23 import org.apache.commons.lang.StringUtils;
24 import org.apache.commons.lang.exception.NestableException;
25 import org.apache.poi.hssf.usermodel.*;
26
27 import java.util.HashMap JavaDoc;
28 import java.util.Iterator JavaDoc;
29 import java.util.Map JavaDoc;
30
31 /**
32  * Various utility functions that make working with a cells and rows easier. The various
33  * methods that deal with style's allow you to create your HSSFCellStyles as you need them.
34  * When you apply a style change to a cell, the code will attempt to see if a style already
35  * exists that meets your needs. If not, then it will create a new style. This is to prevent
36  * creating too many styles. there is an upper limit in Excel on the number of styles that
37  * can be supported.
38  *
39  *@author Eric Pugh epugh@upstate.com
40  */
41
42 public class HSSFCellUtil
43 {
44
45     private static HashMap JavaDoc unicodeMappings = new HashMap JavaDoc();
46
47
48     /**
49      * Get a row from the spreadsheet, and create it if it doesn't exist.
50      *
51      *@param rowCounter The 0 based row number
52      *@param sheet The sheet that the row is part of.
53      *@return The row indicated by the rowCounter
54      */
55     public static HSSFRow getRow( int rowCounter, HSSFSheet sheet )
56     {
57         HSSFRow row = sheet.getRow( (short) rowCounter );
58         if ( row == null )
59         {
60             row = sheet.createRow( (short) rowCounter );
61         }
62
63         return row;
64     }
65
66
67     /**
68      * Get a specific cell from a row. If the cell doesn't exist, then create it.
69      *
70      *@param row The row that the cell is part of
71      *@param column The column index that the cell is in.
72      *@return The cell indicated by the column.
73      */
74     public static HSSFCell getCell( HSSFRow row, int column )
75     {
76         HSSFCell cell = row.getCell( (short) column );
77
78         if ( cell == null )
79         {
80             cell = row.createCell( (short) column );
81         }
82         return cell;
83     }
84
85
86     /**
87      * Creates a cell, gives it a value, and applies a style if provided
88      *
89      * @param row the row to create the cell in
90      * @param column the column index to create the cell in
91      * @param value The value of the cell
92      * @param style If the style is not null, then set
93      * @return A new HSSFCell
94      */
95
96     public static HSSFCell createCell( HSSFRow row, int column, String JavaDoc value, HSSFCellStyle style )
97     {
98         HSSFCell cell = getCell( row, column );
99
100         cell.setCellValue( value );
101         if ( style != null )
102         {
103             cell.setCellStyle( style );
104         }
105
106         return cell;
107     }
108
109
110     /**
111      * Create a cell, and give it a value.
112      *
113      *@param row the row to create the cell in
114      *@param column the column index to create the cell in
115      *@param value The value of the cell
116      *@return A new HSSFCell.
117      */
118     public static HSSFCell createCell( HSSFRow row, int column, String JavaDoc value )
119     {
120         return createCell( row, column, value, null );
121     }
122
123
124     /**
125      * Take a cell, and align it.
126      *
127      *@param cell the cell to set the alignment for
128      *@param workbook The workbook that is being worked with.
129      *@param align the column alignment to use.
130      *@exception NestableException Thrown if an error happens.
131      *
132      * @see HSSFCellStyle for alignment options
133      */
134     public static void setAlignment( HSSFCell cell, HSSFWorkbook workbook, short align ) throws NestableException
135     {
136         setCellStyleProperty( cell, workbook, "alignment", new Short JavaDoc( align ) );
137     }
138
139     /**
140      * Take a cell, and apply a font to it
141      *
142      *@param cell the cell to set the alignment for
143      *@param workbook The workbook that is being worked with.
144      *@param font The HSSFFont that you want to set...
145      *@exception NestableException Thrown if an error happens.
146      */
147     public static void setFont( HSSFCell cell, HSSFWorkbook workbook, HSSFFont font ) throws NestableException
148     {
149         setCellStyleProperty( cell, workbook, "font", font );
150     }
151
152     /**
153      * This method attempt to find an already existing HSSFCellStyle that matches
154      * what you want the style to be. If it does not find the style, then it
155      * creates a new one. If it does create a new one, then it applyies the
156      * propertyName and propertyValue to the style. This is nessasary because
157      * Excel has an upper limit on the number of Styles that it supports.
158      *
159      *@param workbook The workbook that is being worked with.
160      *@param propertyName The name of the property that is to be
161      * changed.
162      *@param propertyValue The value of the property that is to be
163      * changed.
164      *@param cell The cell that needs it's style changes
165      *@exception NestableException Thrown if an error happens.
166      */
167     public static void setCellStyleProperty( HSSFCell cell, HSSFWorkbook workbook, String JavaDoc propertyName, Object JavaDoc propertyValue )
168             throws NestableException
169     {
170         try
171         {
172             HSSFCellStyle originalStyle = cell.getCellStyle();
173             HSSFCellStyle newStyle = null;
174             Map JavaDoc values = PropertyUtils.describe( originalStyle );
175             values.put( propertyName, propertyValue );
176             values.remove( "index" );
177
178             // index seems like what index the cellstyle is in the list of styles for a workbook.
179 // not good to compare on!
180 short numberCellStyles = workbook.getNumCellStyles();
181
182             for ( short i = 0; i < numberCellStyles; i++ )
183             {
184                 HSSFCellStyle wbStyle = workbook.getCellStyleAt( i );
185                 Map JavaDoc wbStyleMap = PropertyUtils.describe( wbStyle );
186                 wbStyleMap.remove( "index" );
187
188                 if ( wbStyleMap.equals( values ) )
189                 {
190                     newStyle = wbStyle;
191                     break;
192                 }
193             }
194
195             if ( newStyle == null )
196             {
197                 newStyle = workbook.createCellStyle();
198                 newStyle.setFont( workbook.getFontAt( originalStyle.getFontIndex() ) );
199                 PropertyUtils.copyProperties( newStyle, originalStyle );
200                 PropertyUtils.setProperty( newStyle, propertyName, propertyValue );
201             }
202
203             cell.setCellStyle( newStyle );
204         }
205         catch ( Exception JavaDoc e )
206         {
207             e.printStackTrace();
208
209             throw new NestableException( "Couldn't setCellStyleProperty.", e );
210         }
211     }
212
213
214     /**
215      * Looks for text in the cell that should be unicode, like &alpha; and provides the
216      * unicode version of it.
217      *
218      *@param cell The cell to check for unicode values
219      *@return transalted to unicode
220      */
221     public static HSSFCell translateUnicodeValues( HSSFCell cell )
222     {
223
224         String JavaDoc s = cell.getStringCellValue();
225         boolean foundUnicode = false;
226
227         for ( Iterator JavaDoc i = unicodeMappings.entrySet().iterator(); i.hasNext(); )
228         {
229             Map.Entry JavaDoc entry = (Map.Entry JavaDoc) i.next();
230             String JavaDoc key = (String JavaDoc) entry.getKey();
231             if ( s.toLowerCase().indexOf( key ) != -1 )
232             {
233                 s = StringUtils.replace( s, key, "" + entry.getValue().toString() + "" );
234                 foundUnicode = true;
235             }
236         }
237         if ( foundUnicode )
238         {
239             cell.setEncoding( HSSFCell.ENCODING_UTF_16 );
240             cell.setCellValue( s );
241         }
242         return cell;
243     }
244
245     
246     static {
247         unicodeMappings.put( "&alpha;", "\u03B1" );
248         unicodeMappings.put( "&beta;", "\u03B2" );
249         unicodeMappings.put( "&gamma;", "\u03B3" );
250         unicodeMappings.put( "&delta;", "\u03B4" );
251         unicodeMappings.put( "&epsilon;", "\u03B5" );
252         unicodeMappings.put( "&zeta;", "\u03B6" );
253         unicodeMappings.put( "&eta;", "\u03B7" );
254         unicodeMappings.put( "&theta;", "\u03B8" );
255         unicodeMappings.put( "&iota;", "\u03B9" );
256         unicodeMappings.put( "&kappa;", "\u03BA" );
257         unicodeMappings.put( "&lambda;", "\u03BB" );
258         unicodeMappings.put( "&mu;", "\u03BC" );
259         unicodeMappings.put( "&nu;", "\u03BD" );
260         unicodeMappings.put( "&xi;", "\u03BE" );
261         unicodeMappings.put( "&omicron;", "\u03BF" );
262     }
263
264 }
265
Popular Tags