001: // Copyright © 2006-2007 ASERT. Released under the Canoo Webtest license.
002: package com.canoo.webtest.plugins.exceltest;
003:
004: import org.apache.poi.hssf.usermodel.HSSFCell;
005: import org.apache.poi.hssf.usermodel.HSSFRow;
006: import org.apache.poi.hssf.usermodel.HSSFCellStyle;
007: import org.apache.poi.hssf.util.CellReference;
008: import org.apache.log4j.Logger;
009: import com.canoo.webtest.engine.StepExecutionException;
010: import com.canoo.webtest.steps.Step;
011:
012: /**
013: * Util class for looking up string values of various parts of an Excel spreadsheet.<p>
014: *
015: * @author Rob Nielsen
016: */
017: public class ExcelCellUtils {
018: private static final Logger LOG = Logger
019: .getLogger(ExcelCellUtils.class);
020: public static final int TWELVE_POINT_FIVE_GRAY = 17;
021: public static final int SIX_POINT_TWO_FIVE_GRAY = 18;
022:
023: public static HSSFCell getExcelCellAt(
024: final AbstractExcelSheetStep step, final int row,
025: final short col) {
026: if (row == -1) {
027: return null;
028: }
029: final HSSFRow excelRow = step.getExcelSheet().getRow(row);
030: if (excelRow == null) {
031: return null;
032: }
033: return excelRow.getCell(col);
034: }
035:
036: public static String getCellValueAt(final HSSFCell cell) {
037: if (null == cell) {
038: return "";
039: }
040: switch (cell.getCellType()) {
041: case HSSFCell.CELL_TYPE_STRING:
042: return cell.getRichStringCellValue().getString();
043: case HSSFCell.CELL_TYPE_NUMERIC:
044: return asStringTrimInts(cell.getNumericCellValue());
045: case HSSFCell.CELL_TYPE_BLANK:
046: return "";
047: case HSSFCell.CELL_TYPE_BOOLEAN:
048: return String.valueOf(cell.getBooleanCellValue());
049: case HSSFCell.CELL_TYPE_FORMULA:
050: return cell.getCellFormula();
051: case HSSFCell.CELL_TYPE_ERROR:
052: return "Error Code "
053: + String.valueOf(cell.getErrorCellValue() & 0xFF);
054: ///CLOVER:OFF there are currently no other types. Potentially more in future?
055: default:
056: LOG.warn("Cell Type not supported: " + cell.getCellType());
057: return "";
058: ///CLOVER:ON
059: }
060: }
061:
062: private static String asStringTrimInts(final double value) {
063: if (value == (int) value) {
064: return String.valueOf((int) value);
065: }
066: return String.valueOf(value);
067: }
068:
069: public static CellReference getCellReference(final Step step,
070: final String cell, final String rowStr, final String colStr) {
071: if (cell != null) {
072: return getCellReference(step, cell);
073: } else {
074: try {
075: final int row = Integer.parseInt(rowStr);
076: if (row > 0) {
077: try {
078: final int col = Short.parseShort(colStr);
079: if (col > 0) {
080: return new CellReference(row - 1, col - 1);
081: }
082: } catch (NumberFormatException e) {
083: if (colStr.matches("[A-Z]+")) {
084: return new CellReference(colStr + rowStr);
085: }
086: }
087: throw new StepExecutionException(
088: "Can't parse '"
089: + colStr
090: + "' as a column reference (eg. 'A' or '1')",
091: step);
092:
093: }
094: } catch (NumberFormatException e) {
095: // fallthrough
096: }
097: throw new StepExecutionException("Can't parse '" + rowStr
098: + "' as a integer row reference.", step);
099: }
100: }
101:
102: public static CellReference getCellReference(final Step step,
103: final String cell) {
104: if (!cell.matches("[A-Z]+[0-9]+")) {
105: throw new StepExecutionException("Invalid cell reference: "
106: + cell, step);
107: }
108: return new CellReference(cell);
109: }
110:
111: static String getCellType(final int cellType) {
112: switch (cellType) {
113: case HSSFCell.CELL_TYPE_BLANK:
114: return "blank";
115: case HSSFCell.CELL_TYPE_BOOLEAN:
116: return "boolean";
117: case HSSFCell.CELL_TYPE_ERROR:
118: return "error";
119: case HSSFCell.CELL_TYPE_FORMULA:
120: return "formula";
121: case HSSFCell.CELL_TYPE_NUMERIC:
122: return "numeric";
123: case HSSFCell.CELL_TYPE_STRING:
124: return "string";
125: default:
126: return "unknown";
127: }
128: }
129:
130: public static String getAlignmentString(final short alignment) {
131: switch (alignment) {
132: case HSSFCellStyle.ALIGN_CENTER:
133: return "center";
134: case HSSFCellStyle.ALIGN_CENTER_SELECTION:
135: return "center-selection";
136: case HSSFCellStyle.ALIGN_FILL:
137: return "fill";
138: case HSSFCellStyle.ALIGN_GENERAL:
139: return "general";
140: case HSSFCellStyle.ALIGN_JUSTIFY:
141: return "justify";
142: case HSSFCellStyle.ALIGN_LEFT:
143: return "left";
144: case HSSFCellStyle.ALIGN_RIGHT:
145: return "right";
146: default:
147: return "unknown";
148: }
149: }
150:
151: public static String getVerticalAlignmentString(
152: final short verticalAlignment) {
153: switch (verticalAlignment) {
154: case HSSFCellStyle.VERTICAL_BOTTOM:
155: return "bottom";
156: case HSSFCellStyle.VERTICAL_CENTER:
157: return "center";
158: case HSSFCellStyle.VERTICAL_JUSTIFY:
159: return "justify";
160: case HSSFCellStyle.VERTICAL_TOP:
161: return "top";
162: default:
163: return "unknown";
164: }
165: }
166:
167: public static String getFillPattern(final short fillPattern) {
168: switch (fillPattern) {
169: case HSSFCellStyle.NO_FILL:
170: return "none";
171: case HSSFCellStyle.SOLID_FOREGROUND:
172: return "solid";
173: case HSSFCellStyle.FINE_DOTS:
174: return "50% gray";
175: case HSSFCellStyle.ALT_BARS:
176: return "75% gray";
177: case HSSFCellStyle.SPARSE_DOTS:
178: return "25% gray";
179: case HSSFCellStyle.THICK_HORZ_BANDS:
180: return "horizontal stripe";
181: case HSSFCellStyle.THICK_VERT_BANDS:
182: return "vertical stripe";
183: case HSSFCellStyle.THICK_BACKWARD_DIAG:
184: return "reverse diagonal stripe";
185: case HSSFCellStyle.THICK_FORWARD_DIAG:
186: return "diagonal stripe";
187: case HSSFCellStyle.BIG_SPOTS:
188: return "diagonal crosshatch";
189: case HSSFCellStyle.BRICKS:
190: return "thick diagonal crosshatch";
191: case HSSFCellStyle.THIN_HORZ_BANDS:
192: return "thin horizontal stripe";
193: case HSSFCellStyle.THIN_VERT_BANDS:
194: return "thin vertical stripe";
195: case HSSFCellStyle.THIN_BACKWARD_DIAG:
196: return "thin reverse diagonal stripe";
197: case HSSFCellStyle.THIN_FORWARD_DIAG:
198: return "thin diagonal stripe";
199: case HSSFCellStyle.SQUARES:
200: return "thin horizontal crosshatch";
201: case HSSFCellStyle.DIAMONDS:
202: return "thin diagonal crosshatch";
203: case TWELVE_POINT_FIVE_GRAY:
204: return "12.5% gray";
205: case SIX_POINT_TWO_FIVE_GRAY:
206: return "6.25% gray";
207: default:
208: return "unknown";
209: }
210: }
211:
212: public static String getBorder(final short border) {
213: switch (border) {
214: case HSSFCellStyle.BORDER_DASH_DOT:
215: return "dash dot";
216: case HSSFCellStyle.BORDER_DASH_DOT_DOT:
217: return "dash dot dot";
218: case HSSFCellStyle.BORDER_DASHED:
219: return "dashed";
220: case HSSFCellStyle.BORDER_DOTTED:
221: return "dotted";
222: case HSSFCellStyle.BORDER_DOUBLE:
223: return "double";
224: case HSSFCellStyle.BORDER_HAIR:
225: return "hair";
226: case HSSFCellStyle.BORDER_MEDIUM:
227: return "medium";
228: case HSSFCellStyle.BORDER_MEDIUM_DASH_DOT:
229: return "medium dash dot";
230: case HSSFCellStyle.BORDER_MEDIUM_DASH_DOT_DOT:
231: return "medium dash dot dot";
232: case HSSFCellStyle.BORDER_MEDIUM_DASHED:
233: return "medium dashed";
234: case HSSFCellStyle.BORDER_NONE:
235: return "none";
236: case HSSFCellStyle.BORDER_SLANTED_DASH_DOT:
237: return "slanted dash dot";
238: case HSSFCellStyle.BORDER_THICK:
239: return "thick";
240: case HSSFCellStyle.BORDER_THIN:
241: return "thin";
242: default:
243: return "unknown";
244: }
245: }
246: }
|