[ http://webtest-community.canoo.com/jira/browse/WT-425?page=all ]
Marc Guillemot closed WT-425:
-----------------------------
Fix Version: Future versions
Resolution: Fixed
Assign To: Marc Guillemot
Fixed in build 1711.
I haven't used the provided information as the format wasn't appropriate for a
merge (not provided as a diff) with the recent changes and at least one file
was missing (an xls file for the tests).
The development of the fix has been sponsored by Scan & Target. Many thanks.
> dataDriven excel cell with formula shows incorrect value
> --------------------------------------------------------
>
> Key: WT-425
> URL: http://webtest-community.canoo.com/jira/browse/WT-425
> Project: WebTest
> Type: New Feature
> Versions: 2.6
> Reporter: Yoo-Jin Lee
> Assignee: Marc Guillemot
> Fix For: Future versions
> Attachments: ExcelPropertyTable.java, ExcelTableTest.java, Number.xls,
> pom.xml
>
> The dataDriven step does not handle excel formulas. It consistently returns 1.
> POI has HSSFFormulaEvaluator class that can calculate over 100 formulas with
> more being added with every release.
> Some approaches are outlined in http://poi.apache.org/hssf/eval.html
> One approach is the following:
> 1. need to add poi scratchpad jar for POI releases before 3.0.3
> 2. updated ExcelPropertyTable
> a. add code to calculate formulas
> ================ pom.xml ================
> ....
> <dependency>
> <!-- excel -->
> <groupId>org.apache.poi</groupId>
> <artifactId>poi-scratchpad</artifactId>
> <version>3.0.2-FINAL</version>
> <scope>compile</scope>
> </dependency>
> ============ExcelPropertyTable===================
> package com.canoo.ant.table;
> import org.apache.log4j.Logger;
> import org.apache.poi.hssf.usermodel.*;
> import org.apache.poi.poifs.filesystem.POIFSFileSystem;
> import java.io.File;
> import java.io.FileInputStream;
> import java.io.FileNotFoundException;
> import java.io.IOException;
> import java.text.DecimalFormat;
> import java.util.ArrayList;
> import java.util.LinkedList;
> import java.util.List;
> import java.util.Properties;
> public class ExcelPropertyTable extends APropertyTable {
> private static final Logger LOG =
> Logger.getLogger(ExcelPropertyTable.class);
> private static final DecimalFormat NUMBER_FMT = new DecimalFormat("0");
> public ExcelPropertyTable() {
> }
> protected boolean hasJoinTable() {
> final Object sheet;
> try {
> sheet = getWorkbook().getSheet(KEY_JOIN);
> }
> catch (final IOException e) {
> throw new RuntimeException("Failed to read container: >" +
> getContainer() + "<", e);
> }
> return sheet != null;
> }
> private HSSFWorkbook getWorkbook() throws IOException {
> final File file = getContainer();
> if (!file.exists()) {
> throw new FileNotFoundException("File not found >" +
> file.getAbsolutePath() + "< " + getContainer());
> } else if (!file.isFile() || !file.canRead()) {
> throw new IllegalArgumentException("No a regular readable file:
> >" + file.getAbsolutePath() + "<");
> }
> final POIFSFileSystem excelFile = new POIFSFileSystem(new
> FileInputStream(file));
> return new HSSFWorkbook(excelFile);
> }
> protected List read(final String sheetName) throws IOException {
> final HSSFWorkbook workbook = getWorkbook();
> final HSSFSheet sheet;
> if (sheetName == null) {
> sheet = workbook.getSheetAt(0); // no name specified, take the
> first sheet
> } else {
> sheet = workbook.getSheet(sheetName);
> }
> if (null == sheet) {
> String msg = "No sheet \"" + sheetName + "\" found in file " +
> getContainer() + ". Available sheets: ";
> for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
> if (i != 0)
> msg += ", ";
> msg += workbook.getSheetName(i);
> }
> throw new IllegalArgumentException(msg);
> }
> HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet,
> workbook);
> final int lastRowNum = sheet.getLastRowNum();
> final List header = new ArrayList();
> final HSSFRow headerRow = sheet.getRow(0);
> for (short i = 0; i < headerRow.getLastCellNum(); i++) {
> final HSSFCell cell = headerRow.getCell(i);
> if (cell != null)
> header.add(stringValueOf(cell));
> else
> header.add(null);
> }
> final List result = new LinkedList();
> for (int rowNo = 1; rowNo <= lastRowNum; rowNo++) { // last Row is
> included
> final HSSFRow row = sheet.getRow(rowNo);
> evaluator.setCurrentRow(row);
> if (row != null) // surprising, but row can be null
> {
> final Properties props = new Properties();
> for (short i = 0; i < header.size(); i++) {
> final String headerName = (String) header.get(i);
> if (headerName != null) // handle empty cols
> {
> final HSSFCell cell = row.getCell(i);
> evaluator.evaluateInCell(cell);
> final String value = stringValueOf(cell);
> putValue(value, headerName, props);
> }
> }
> result.add(props);
> }
> }
> return result;
> }
> protected void putValue(String value, Object key, Properties props) {
> if (!EMPTY.equals(value)) { // do not add empty values to allow
> proper default handling
> props.put(key, value);
> }
> }
> private String stringValueOf(final HSSFCell cell) {
> if (null == cell) {
> return EMPTY;
> }
> switch (cell.getCellType()) {
> case (HSSFCell.CELL_TYPE_STRING):
> return cell.getRichStringCellValue().getString();
> case (HSSFCell.CELL_TYPE_NUMERIC):
> return String.valueOf(cell.getNumericCellValue());
> case (HSSFCell.CELL_TYPE_BLANK):
> return "";
> case (HSSFCell.CELL_TYPE_BOOLEAN):
> return "" + cell.getBooleanCellValue();
> default:
> LOG.warn("Cell Type not supported: " + cell.getCellType());
> return EMPTY;
> }
> }
> }
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://webtest-community.canoo.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
_______________________________________________
WebTest mailing list
[email protected]
http://lists.canoo.com/mailman/listinfo/webtest
