001: // Copyright © 2006-2007 ASERT. Released under the Canoo Webtest license.
002: package com.canoo.webtest.plugins.exceltest;
003:
004: import com.canoo.webtest.engine.StepExecutionException;
005: import com.canoo.webtest.engine.StepFailedException;
006: import org.apache.poi.hssf.usermodel.HSSFCell;
007: import org.apache.poi.hssf.usermodel.HSSFCellStyle;
008: import org.apache.poi.hssf.usermodel.HSSFFont;
009: import org.apache.poi.hssf.util.CellReference;
010:
011: import java.util.*;
012:
013: /**
014: * @author Rob Nielsen
015: * @webtest.step category="Excel"
016: * name="excelVerifyCellStyle"
017: * alias="verifyCellStyle"
018: * description="This step verifies the style of a specified cell in an Excel spreadsheet. Only those properties set will be checked."
019: */
020: public class ExcelVerifyCellStyle extends AbstractExcelCellStep {
021: private static final int TOP = 0;
022: private static final int RIGHT = 1;
023: private static final int BOTTOM = 2;
024: private static final int LEFT = 3;
025: private static final String[] SIDES = { "top", "right", "bottom",
026: "left" };
027:
028: private String fType;
029: private String fFormat;
030: private String fAlign;
031: private String fValign;
032: private String fWrap;
033: private String fLocked;
034: private String fFontName;
035: private String fFontSize;
036: private String fFontStyle;
037: private String fBorder;
038: private String fBorderColor;
039: private String fTextColor;
040: private String fFillColor;
041: private String fFillBackgroundColor;
042:
043: private String fFillPattern;
044:
045: /**
046: * @param text
047: * @webtest.parameter
048: * required="no"
049: * description="The format to check for. eg 0.00 for two decimal places."
050: */
051: public void setFormat(final String text) {
052: fFormat = text;
053: }
054:
055: public String getFormat() {
056: return fFormat;
057: }
058:
059: /**
060: * @param text
061: * @webtest.parameter
062: * required="no"
063: * description="The expected cell type. One of <em>blank, numeric, formula, string, boolean or error.</em>"
064: */
065: public void setType(final String text) {
066: fType = text;
067: }
068:
069: public String getType() {
070: return fType;
071: }
072:
073: /**
074: * @param text
075: * @webtest.parameter
076: * required="no"
077: * description="The expected horizontal alignment. One of center, center-selection, fill, general, justify, left, right"
078: */
079: public void setAlign(final String text) {
080: fAlign = text;
081: }
082:
083: public String getAlign() {
084: return fAlign;
085: }
086:
087: /**
088: * @param text
089: * @webtest.parameter
090: * required="no"
091: * description="The expected vertical alignment. One of bottom, center, justify, top"
092: */
093: public void setValign(final String text) {
094: fValign = text;
095: }
096:
097: public String getValign() {
098: return fValign;
099: }
100:
101: /**
102: * @param text
103: * @webtest.parameter
104: * required="no"
105: * description="The expected wrap setting. true or false"
106: */
107: public void setWrap(final String text) {
108: fWrap = text;
109: }
110:
111: public String getWrap() {
112: return fWrap;
113: }
114:
115: /**
116: * @param text
117: * @webtest.parameter
118: * required="no"
119: * description="The expected font style. A combination of bold, italic, strikethrough, subscript, superscript, underline, underline-accounting, underline-double, underline-double-accounting separated by spaces, or normal"
120: */
121: public void setFontStyle(final String text) {
122: fFontStyle = text;
123: }
124:
125: public String getFontStyle() {
126: return fFontStyle;
127: }
128:
129: /**
130: * @param text
131: * @webtest.parameter
132: * required="no"
133: * description="The expected locked status. true or false"
134: */
135: public void setLocked(final String text) {
136: fLocked = text;
137: }
138:
139: public String getLocked() {
140: return fLocked;
141: }
142:
143: /**
144: * @param text
145: * @webtest.parameter
146: * required="no"
147: * description="The expected font name. eg. Arial or Times"
148: */
149: public void setFontName(final String text) {
150: fFontName = text;
151: }
152:
153: public String getFontName() {
154: return fFontName;
155: }
156:
157: /**
158: * @param text
159: * @webtest.parameter
160: * required="no"
161: * description="The expected font size in points. eg 10 or 12"
162: */
163: public void setFontSize(final String text) {
164: fFontSize = text;
165: }
166:
167: public String getFontSize() {
168: return fFontSize;
169: }
170:
171: /**
172: * @param text
173: * @webtest.parameter
174: * required="no"
175: * description="The expected border style. One of <em>dash dot, dash dot dot, dashed, dotted, double, hair, medium, medium dash dot, medium dash dot dot, medium dashed, slanted dash dot, thick, thin, none</em>. See below for details on specifying sides."
176: */
177: public void setBorder(final String text) {
178: fBorder = text;
179: }
180:
181: public String getBorder() {
182: return fBorder;
183: }
184:
185: /**
186: * @param text
187: * @webtest.parameter
188: * required="no"
189: * description="The expected border color. Specify as #rrggbb or use a standard name. See below for details on specifying sides."
190: */
191: public void setBorderColor(final String text) {
192: fBorderColor = text;
193: }
194:
195: public String getBorderColor() {
196: return fBorderColor;
197: }
198:
199: /**
200: * @param text
201: * @webtest.parameter
202: * required="no"
203: * description="The expected text color. Specify as #rrggbb or use a standard name."
204: */
205: public void setTextColor(final String text) {
206: fTextColor = text;
207: }
208:
209: public String getTextColor() {
210: return fTextColor;
211: }
212:
213: /**
214: * @param text
215: * @webtest.parameter
216: * required="no"
217: * description="The expected foreground cell fill color. Specify as #rrggbb or use a standard name."
218: */
219: public void setFillColor(final String text) {
220: fFillColor = text;
221: }
222:
223: public String getFillColor() {
224: return fFillColor;
225: }
226:
227: /**
228: * @param text
229: * @webtest.parameter
230: * required="no"
231: * description="The expected background cell fill color. Specify as #rrggbb or use a standard name."
232: */
233: public void setFillBackgroundColor(final String text) {
234: fFillBackgroundColor = text;
235: }
236:
237: public String getFillBackgroundColor() {
238: return fFillBackgroundColor;
239: }
240:
241: /**
242: * @param text
243: * @webtest.parameter
244: * required="no"
245: * description="The expected fill pattern. One of <em>none, solid, 50% gray, 75% gray, 25% gray, horizontal stripe, vertical stripe, reverse diagonal stripe, diagonal stripe, diagonal crosshatch, thick diagonal crosshatch, thin horizontal stripe, thin vertical stripe, thin reverse diagonal stripe, thin diagonal stripe, thin horizontal crosshatch, thin diagonal crosshatch, 12.5% gray, 6.25% gray</em>"
246: */
247: public void setFillPattern(final String text) {
248: fFillPattern = text;
249: }
250:
251: public String getFillPattern() {
252: return fFillPattern;
253: }
254:
255: public void doExecute() throws Exception {
256: final String[] border = separateSides(getBorder());
257: final String[] borderColor = separateSides(getBorderColor());
258: for (int i = 0; i < SIDES.length; i++) {
259: checkFormat(SIDES[i] + "Border", border[i],
260: getCellBorder(i));
261: checkFormat(SIDES[i] + "BorderColor", ExcelColorUtils
262: .lookupStandardColorName(borderColor[i]),
263: getCellBorderColor(i));
264: }
265: final HSSFCell excelCell = getExcelCell();
266: checkFormat(
267: "type",
268: getType(),
269: ExcelCellUtils
270: .getCellType(excelCell == null ? HSSFCell.CELL_TYPE_BLANK
271: : excelCell.getCellType()));
272: if (excelCell == null) {
273: if (cellNotRequired()) {
274: return;
275: } else {
276: throw new StepExecutionException("Can't find cell for "
277: + getCellReference(), this );
278: }
279: }
280: final HSSFCellStyle cellStyle = excelCell.getCellStyle();
281: checkFormat("format", getFormat(), getExcelWorkbook()
282: .createDataFormat()
283: .getFormat(cellStyle.getDataFormat()));
284: checkFormat("align", getAlign(), ExcelCellUtils
285: .getAlignmentString(cellStyle.getAlignment()));
286: checkFormat("valign", getValign(), ExcelCellUtils
287: .getVerticalAlignmentString(cellStyle
288: .getVerticalAlignment()));
289: checkFormat("wrap", getWrap(), String.valueOf(cellStyle
290: .getWrapText()));
291: checkFormat("locked", getLocked(), String.valueOf(cellStyle
292: .getLocked()));
293: checkFormat("fontName", getFontName(), getFont(cellStyle)
294: .getFontName());
295: checkFormat("fontSize", getFontSize(), String.valueOf(getFont(
296: cellStyle).getFontHeightInPoints()));
297: checkFormat("fontStyle", sortElements(getFontStyle()),
298: getFontStyle(getFont(cellStyle)));
299: checkFormat("fillColor", ExcelColorUtils
300: .lookupStandardColorName(getFillColor()),
301: ExcelColorUtils.getColorName(this , cellStyle
302: .getFillForegroundColor()));
303: checkFormat("fillBackgroundColor", ExcelColorUtils
304: .lookupStandardColorName(getFillBackgroundColor()),
305: ExcelColorUtils.getColorName(this , cellStyle
306: .getFillBackgroundColor()));
307: checkFormat("textColor", ExcelColorUtils
308: .lookupStandardColorName(getTextColor()),
309: ExcelColorUtils.getColorName(this , getFont(cellStyle)
310: .getColor()));
311: checkFormat("fillPattern", getFillPattern(), ExcelCellUtils
312: .getFillPattern(cellStyle.getFillPattern()));
313: }
314:
315: private static String sortElements(final String elements) {
316: if (elements == null) {
317: return null;
318: }
319: final List list = new ArrayList();
320: final StringTokenizer st = new StringTokenizer(elements, " ");
321: while (st.hasMoreTokens()) {
322: list.add(st.nextToken());
323: }
324: Collections.sort(list);
325: final StringBuffer sb = new StringBuffer();
326: for (Iterator it = list.iterator(); it.hasNext();) {
327: sb.append(it.next());
328: if (it.hasNext()) {
329: sb.append(" ");
330: }
331: }
332: return sb.toString();
333: }
334:
335: private static String getFontStyle(final HSSFFont font) {
336: final StringBuffer sb = new StringBuffer();
337: if (font.getBoldweight() == HSSFFont.BOLDWEIGHT_BOLD) {
338: sb.append("bold ");
339: }
340: if (font.getItalic()) {
341: sb.append("italic ");
342: }
343: if (font.getStrikeout()) {
344: sb.append("strikethrough ");
345: }
346: if (font.getTypeOffset() == HSSFFont.SS_SUB) {
347: sb.append("subscript ");
348: } else if (font.getTypeOffset() == HSSFFont.SS_SUPER) {
349: sb.append("superscript ");
350: }
351: switch (font.getUnderline()) {
352: case HSSFFont.U_NONE:
353: break;
354: case HSSFFont.U_SINGLE:
355: sb.append("underline ");
356: break;
357: case HSSFFont.U_SINGLE_ACCOUNTING:
358: sb.append("underline-accounting ");
359: break;
360: case HSSFFont.U_DOUBLE:
361: sb.append("underline-double ");
362: break;
363: case HSSFFont.U_DOUBLE_ACCOUNTING:
364: sb.append("underline-double-accounting ");
365: break;
366: default:
367: sb.append("underline-unknown ");
368: break;
369: }
370: if (sb.length() == 0) {
371: return "normal";
372: }
373: return sb.substring(0, sb.length() - 1);
374: }
375:
376: private boolean cellNotRequired() {
377: return fFormat == null && fAlign == null && fValign == null
378: && fWrap == null && fLocked == null
379: && fFontName == null && fFontSize == null
380: && fFontStyle == null && fFillColor == null
381: && fFillBackgroundColor == null && fFillPattern == null
382: && fTextColor == null;
383: }
384:
385: public String getCellBorder(final int dir) {
386: final HSSFCell excelCellAt = getExcelCell();
387: short border = HSSFCellStyle.BORDER_NONE;
388: if (excelCellAt != null) {
389: border = getBorder(excelCellAt.getCellStyle(), dir);
390: }
391: if (border == HSSFCellStyle.BORDER_NONE) {
392: final HSSFCell adjacentCell = getAdjacentCell(dir);
393: if (adjacentCell != null) {
394: border = getBorder(adjacentCell.getCellStyle(),
395: (dir + 2) % 4);
396: }
397: }
398: return ExcelCellUtils.getBorder(border);
399: }
400:
401: public String getCellBorderColor(final int dir) {
402: final HSSFCell excelCellAt = getExcelCell();
403: short border = HSSFCellStyle.BORDER_NONE;
404: if (excelCellAt != null) {
405: border = getBorderColor(excelCellAt.getCellStyle(), dir);
406: }
407: if (border == HSSFCellStyle.BORDER_NONE) {
408: final HSSFCell adjacentCell = getAdjacentCell(dir);
409: if (adjacentCell != null) {
410: border = getBorderColor(adjacentCell.getCellStyle(),
411: (dir + 2) % 4);
412: }
413: }
414: return ExcelColorUtils.getColorName(this , border);
415: }
416:
417: HSSFCell getAdjacentCell(final int dir) {
418: final CellReference cellReference = getCellReference();
419: short yofs = 0;
420: int xofs = 0;
421: switch (dir) {
422: case TOP:
423: yofs = -1;
424: break;
425: case RIGHT:
426: xofs = 1;
427: break;
428: case BOTTOM:
429: yofs = 1;
430: break;
431: case LEFT:
432: xofs = -1;
433: break;
434: default:
435: throw new IllegalArgumentException("Invalid side: " + dir);
436: }
437: return ExcelCellUtils.getExcelCellAt(this , cellReference
438: .getRow()
439: + yofs, (short) (cellReference.getCol() + xofs));
440: }
441:
442: short getBorder(final HSSFCellStyle cellStyle, final int index) {
443: switch (index) {
444: case TOP:
445: return cellStyle.getBorderTop();
446: case RIGHT:
447: return cellStyle.getBorderRight();
448: case BOTTOM:
449: return cellStyle.getBorderBottom();
450: case LEFT:
451: return cellStyle.getBorderLeft();
452: default:
453: throw new IllegalArgumentException("Invalid side: " + index);
454: }
455: }
456:
457: short getBorderColor(final HSSFCellStyle cellStyle, final int index) {
458: switch (index) {
459: case TOP:
460: return cellStyle.getTopBorderColor();
461: case RIGHT:
462: return cellStyle.getRightBorderColor();
463: case BOTTOM:
464: return cellStyle.getBottomBorderColor();
465: case LEFT:
466: return cellStyle.getLeftBorderColor();
467: default:
468: throw new IllegalArgumentException("Invalid side: " + index);
469: }
470: }
471:
472: private HSSFFont getFont(final HSSFCellStyle cellStyle) {
473: return getExcelWorkbook().getFontAt(cellStyle.getFontIndex());
474: }
475:
476: private void checkFormat(final String property,
477: final String expected, final String actual) {
478: if (expected == null || verifyStrings(expected, actual)) {
479: return;
480: }
481: throw new StepFailedException(
482: "Wrong cell style found for property '" + property
483: + "' in cell " + getCellReference(), expected,
484: actual, this );
485: }
486:
487: private String[] separateSides(final String border) {
488: final String[] result = new String[SIDES.length];
489: if (border != null) {
490: final StringTokenizer st = new StringTokenizer(border, ";");
491: while (st.hasMoreTokens()) {
492: final String s = st.nextToken();
493: final int colon = s.indexOf(':');
494: if (colon != -1) {
495: final String sides = s.substring(0, colon);
496: final String value = s.substring(colon + 1).trim();
497: final StringTokenizer st2 = new StringTokenizer(
498: sides, ",");
499: while (st2.hasMoreTokens()) {
500: final String side = st2.nextToken().trim();
501: int i = 0;
502: while (i < SIDES.length) {
503: if (side.equals(SIDES[i])) {
504: result[i] = value;
505: break;
506: }
507: i++;
508: }
509: if (i == SIDES.length) {
510: throw new StepExecutionException(
511: "Border side '"
512: + side
513: + "' unknown. Specify one of top, down, left right.",
514: this );
515: }
516: }
517: } else {
518: for (int i = 0; i < SIDES.length; i++) {
519: result[i] = s;
520: }
521: }
522: }
523: }
524: return result;
525: }
526:
527: }
|