use of org.apache.poi.hssf.util.CellReference in project poi by apache.
the class TestFormulas method operationalRefVerify.
/**
* Opens the sheet we wrote out by binomialOperator and makes sure the formulas
* all match what we expect (x operator y)
*/
private static void operationalRefVerify(String operator, HSSFWorkbook wb) {
HSSFSheet s = wb.getSheetAt(0);
HSSFRow r = null;
HSSFCell c = null;
//get our minimum values
r = s.getRow(0);
c = r.getCell(1);
//get our minimum values
assertTrue("minval Formula is as expected A2" + operator + "A3 != " + c.getCellFormula(), (("A2" + operator + "A3").equals(c.getCellFormula())));
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
r = s.getRow(x);
for (int y = 1; y < 256 && y > 0; y++) {
int refx1;
int refy1;
int refx2;
int refy2;
if (x + 50 < Short.MAX_VALUE) {
refx1 = x + 50;
refx2 = x + 46;
} else {
refx1 = x - 4;
refx2 = x - 3;
}
if (y + 50 < 255) {
refy1 = y + 50;
refy2 = y + 49;
} else {
refy1 = y - 4;
refy2 = y - 3;
}
c = r.getCell(y);
CellReference cr = new CellReference(refx1, refy1, false, false);
String ref = cr.formatAsString();
ref = cr.formatAsString();
cr = new CellReference(refx2, refy2, false, false);
String ref2 = cr.formatAsString();
assertTrue("loop Formula is as expected " + ref + operator + ref2 + "!=" + c.getCellFormula(), (("" + ref + operator + ref2).equals(c.getCellFormula())));
}
}
//test our maximum values
r = s.getRow(0);
c = r.getCell(0);
assertEquals("B1" + operator + "IV255", c.getCellFormula());
}
use of org.apache.poi.hssf.util.CellReference in project ocvn by devgateway.
the class XExcelFileReader method getDataRow.
private String[] getDataRow() throws XMLStreamException {
List<String> rowValues = new ArrayList<String>();
while (xmlReader.hasNext()) {
xmlReader.next();
if (xmlReader.isStartElement()) {
if (xmlReader.getLocalName().equals("c")) {
CellReference cellReference = new CellReference(xmlReader.getAttributeValue(null, "r"));
// Fill in the possible blank cells!
while (rowValues.size() < cellReference.getCol()) {
rowValues.add("");
}
String cellType = xmlReader.getAttributeValue(null, "t");
rowValues.add(getCellValue(cellType));
}
} else if (xmlReader.isEndElement() && xmlReader.getLocalName().equals("row")) {
break;
}
}
return rowValues.toArray(new String[rowValues.size()]);
}
use of org.apache.poi.hssf.util.CellReference in project drools by kiegroup.
the class XLSEventDataCollector method fulfillExpectation.
private void fulfillExpectation(int currentRowCtr, int currentColCtr, Object cellValue, Class expectedClass) throws ScorecardParseException {
List<DataExpectation> dataExpectations = resolveExpectations(currentRowCtr, currentColCtr);
CellReference cellRef = new CellReference(currentRowCtr, currentColCtr);
Method method = null;
for (DataExpectation dataExpectation : dataExpectations) {
try {
if (dataExpectation != null && dataExpectation.object != null) {
if (cellValue == null || StringUtils.isEmpty(cellValue.toString())) {
if (dataExpectation.errorMessage != null && !StringUtils.isEmpty(dataExpectation.errorMessage)) {
parseErrors.add(new ScorecardError(cellRef.formatAsString(), dataExpectation.errorMessage));
return;
}
}
String setter = "set" + Character.toUpperCase(dataExpectation.property.charAt(0)) + dataExpectation.property.substring(1);
method = getSuitableMethod(cellValue, expectedClass, dataExpectation, setter);
if (method == null) {
if (cellValue != null && !StringUtils.isEmpty(cellValue.toString())) {
parseErrors.add(new ScorecardError(cellRef.formatAsString(), "Unexpected Value! Wrong Datatype?"));
}
return;
}
if (method.getParameterTypes()[0] == Double.class) {
cellValue = Double.parseDouble(cellValue.toString());
}
if (method.getParameterTypes()[0] == Boolean.class) {
cellValue = Boolean.valueOf(cellValue.toString());
}
if (method.getParameterTypes()[0] == String.class && !(cellValue instanceof String) && cellValue != null) {
cellValue = cellValue.toString();
}
method.invoke(dataExpectation.object, cellValue);
if (dataExpectation.object instanceof Extension && ("cellRef".equals(((Extension) dataExpectation.object).getName()))) {
((Extension) dataExpectation.object).setValue(cellRef.formatAsString());
}
// dataExpectations.remove(dataExpectation);
}
} catch (Exception e) {
throw new ScorecardParseException(e);
}
}
}
use of org.apache.poi.hssf.util.CellReference in project my_curd by qinyou.
the class ExcelHelper method excelFileConvertToList.
/**
* @param filename
* @return
* @throws Exception
*/
public List<List> excelFileConvertToList(String filename) throws Exception {
Workbook wb = null;
// 此处为兼容2003 与2oo7
String ext = getExtensionName(filename);
if (ext.toLowerCase().equals("xlsx")) {
wb = new XSSFWorkbook(filename);
} else {
wb = WorkbookFactory.create(new FileInputStream(filename));
}
Sheet sheet = wb.getSheetAt(0);
List<List> rows = new ArrayList<List>();
for (Row row : sheet) {
List<Object> cells = new ArrayList<Object>();
for (Cell cell : row) {
Object obj = null;
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
switch(cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
obj = cell.getRichStringCellValue().getString();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
obj = new JDateTime(cell.getDateCellValue());
} else {
obj = cell.getNumericCellValue();
}
break;
case Cell.CELL_TYPE_BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
obj = cell.getNumericCellValue();
break;
default:
obj = null;
}
cells.add(obj);
}
rows.add(cells);
}
return rows;
}
Aggregations