[Webtest] [webtest][JIRA] Closed: (WT-425) dataDriven excel cell with formula shows incorrect value

Marc Guillemot (JIRA) Marc Guillemot (JIRA)" <noreply@canoo.com
Wed, 9 Jul 2008 15:42:57 +0200 (CEST)


     [ 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