001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: package org.apache.poi.hssf.usermodel.contrib;
019:
020: import org.apache.commons.beanutils.PropertyUtils;
021: import org.apache.commons.lang.StringUtils;
022: import org.apache.commons.lang.exception.NestableException;
023: import org.apache.poi.hssf.usermodel.*;
024:
025: import java.util.HashMap;
026: import java.util.Iterator;
027: import java.util.Map;
028:
029: /**
030: * Various utility functions that make working with a cells and rows easier. The various
031: * methods that deal with style's allow you to create your HSSFCellStyles as you need them.
032: * When you apply a style change to a cell, the code will attempt to see if a style already
033: * exists that meets your needs. If not, then it will create a new style. This is to prevent
034: * creating too many styles. there is an upper limit in Excel on the number of styles that
035: * can be supported.
036: *
037: *@author Eric Pugh epugh@upstate.com
038: */
039:
040: public class HSSFCellUtil {
041:
042: private static HashMap unicodeMappings = new HashMap();
043:
044: /**
045: * Get a row from the spreadsheet, and create it if it doesn't exist.
046: *
047: *@param rowCounter The 0 based row number
048: *@param sheet The sheet that the row is part of.
049: *@return The row indicated by the rowCounter
050: */
051: public static HSSFRow getRow(int rowCounter, HSSFSheet sheet) {
052: HSSFRow row = sheet.getRow((short) rowCounter);
053: if (row == null) {
054: row = sheet.createRow((short) rowCounter);
055: }
056:
057: return row;
058: }
059:
060: /**
061: * Get a specific cell from a row. If the cell doesn't exist, then create it.
062: *
063: *@param row The row that the cell is part of
064: *@param column The column index that the cell is in.
065: *@return The cell indicated by the column.
066: */
067: public static HSSFCell getCell(HSSFRow row, int column) {
068: HSSFCell cell = row.getCell((short) column);
069:
070: if (cell == null) {
071: cell = row.createCell((short) column);
072: }
073: return cell;
074: }
075:
076: /**
077: * Creates a cell, gives it a value, and applies a style if provided
078: *
079: * @param row the row to create the cell in
080: * @param column the column index to create the cell in
081: * @param value The value of the cell
082: * @param style If the style is not null, then set
083: * @return A new HSSFCell
084: */
085:
086: public static HSSFCell createCell(HSSFRow row, int column,
087: String value, HSSFCellStyle style) {
088: HSSFCell cell = getCell(row, column);
089:
090: cell.setCellValue(value);
091: if (style != null) {
092: cell.setCellStyle(style);
093: }
094:
095: return cell;
096: }
097:
098: /**
099: * Create a cell, and give it a value.
100: *
101: *@param row the row to create the cell in
102: *@param column the column index to create the cell in
103: *@param value The value of the cell
104: *@return A new HSSFCell.
105: */
106: public static HSSFCell createCell(HSSFRow row, int column,
107: String value) {
108: return createCell(row, column, value, null);
109: }
110:
111: /**
112: * Take a cell, and align it.
113: *
114: *@param cell the cell to set the alignment for
115: *@param workbook The workbook that is being worked with.
116: *@param align the column alignment to use.
117: *@exception NestableException Thrown if an error happens.
118: *
119: * @see HSSFCellStyle for alignment options
120: */
121: public static void setAlignment(HSSFCell cell,
122: HSSFWorkbook workbook, short align)
123: throws NestableException {
124: setCellStyleProperty(cell, workbook, "alignment", new Short(
125: align));
126: }
127:
128: /**
129: * Take a cell, and apply a font to it
130: *
131: *@param cell the cell to set the alignment for
132: *@param workbook The workbook that is being worked with.
133: *@param font The HSSFFont that you want to set...
134: *@exception NestableException Thrown if an error happens.
135: */
136: public static void setFont(HSSFCell cell, HSSFWorkbook workbook,
137: HSSFFont font) throws NestableException {
138: setCellStyleProperty(cell, workbook, "font", font);
139: }
140:
141: /**
142: * This method attempt to find an already existing HSSFCellStyle that matches
143: * what you want the style to be. If it does not find the style, then it
144: * creates a new one. If it does create a new one, then it applyies the
145: * propertyName and propertyValue to the style. This is nessasary because
146: * Excel has an upper limit on the number of Styles that it supports.
147: *
148: *@param workbook The workbook that is being worked with.
149: *@param propertyName The name of the property that is to be
150: * changed.
151: *@param propertyValue The value of the property that is to be
152: * changed.
153: *@param cell The cell that needs it's style changes
154: *@exception NestableException Thrown if an error happens.
155: */
156: public static void setCellStyleProperty(HSSFCell cell,
157: HSSFWorkbook workbook, String propertyName,
158: Object propertyValue) throws NestableException {
159: try {
160: HSSFCellStyle originalStyle = cell.getCellStyle();
161: HSSFCellStyle newStyle = null;
162: Map values = PropertyUtils.describe(originalStyle);
163: values.put(propertyName, propertyValue);
164: values.remove("index");
165:
166: // index seems like what index the cellstyle is in the list of styles for a workbook.
167: // not good to compare on!
168: short numberCellStyles = workbook.getNumCellStyles();
169:
170: for (short i = 0; i < numberCellStyles; i++) {
171: HSSFCellStyle wbStyle = workbook.getCellStyleAt(i);
172: Map wbStyleMap = PropertyUtils.describe(wbStyle);
173: wbStyleMap.remove("index");
174:
175: if (wbStyleMap.equals(values)) {
176: newStyle = wbStyle;
177: break;
178: }
179: }
180:
181: if (newStyle == null) {
182: newStyle = workbook.createCellStyle();
183: newStyle.setFont(workbook.getFontAt(originalStyle
184: .getFontIndex()));
185: PropertyUtils.copyProperties(newStyle, originalStyle);
186: PropertyUtils.setProperty(newStyle, propertyName,
187: propertyValue);
188: }
189:
190: cell.setCellStyle(newStyle);
191: } catch (Exception e) {
192: e.printStackTrace();
193:
194: throw new NestableException(
195: "Couldn't setCellStyleProperty.", e);
196: }
197: }
198:
199: /**
200: * Looks for text in the cell that should be unicode, like α and provides the
201: * unicode version of it.
202: *
203: *@param cell The cell to check for unicode values
204: *@return transalted to unicode
205: */
206: public static HSSFCell translateUnicodeValues(HSSFCell cell) {
207:
208: String s = cell.getStringCellValue();
209: boolean foundUnicode = false;
210:
211: for (Iterator i = unicodeMappings.entrySet().iterator(); i
212: .hasNext();) {
213: Map.Entry entry = (Map.Entry) i.next();
214: String key = (String) entry.getKey();
215: if (s.toLowerCase().indexOf(key) != -1) {
216: s = StringUtils.replace(s, key, ""
217: + entry.getValue().toString() + "");
218: foundUnicode = true;
219: }
220: }
221: if (foundUnicode) {
222: cell.setEncoding(HSSFCell.ENCODING_UTF_16);
223: cell.setCellValue(s);
224: }
225: return cell;
226: }
227:
228: static {
229: unicodeMappings.put("α", "\u03B1");
230: unicodeMappings.put("β", "\u03B2");
231: unicodeMappings.put("γ", "\u03B3");
232: unicodeMappings.put("δ", "\u03B4");
233: unicodeMappings.put("ε", "\u03B5");
234: unicodeMappings.put("ζ", "\u03B6");
235: unicodeMappings.put("η", "\u03B7");
236: unicodeMappings.put("θ", "\u03B8");
237: unicodeMappings.put("ι", "\u03B9");
238: unicodeMappings.put("κ", "\u03BA");
239: unicodeMappings.put("λ", "\u03BB");
240: unicodeMappings.put("μ", "\u03BC");
241: unicodeMappings.put("ν", "\u03BD");
242: unicodeMappings.put("ξ", "\u03BE");
243: unicodeMappings.put("ο", "\u03BF");
244: }
245:
246: }
|