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
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
