Unit test demonstrating that POI 3.17 still has the bug

ApachePoiBug61532Test.java (text/plain), 4.95 KB, created by Dave Neuer on 2017-12-08 20:09:08 UTC

(hide)

Description: Unit test demonstrating that POI 3.17 still has the bug

Filename:

MIME Type:

Creator: Dave Neuer

Created: 2017-12-08 20:09:08 UTC

Size: 4.95 KB

patch

obsolete

>package org.apache.poi; > >import static org.junit.Assert.*; > > >import org.junit.Test; > >import org.apache.poi.ss.SpreadsheetVersion; >import org.apache.poi.ss.usermodel.Cell; >import org.apache.poi.ss.usermodel.CellType; >import org.apache.poi.ss.usermodel.DataValidationHelper; >import org.apache.poi.ss.usermodel.FormulaEvaluator; >import org.apache.poi.ss.usermodel.Row; >import org.apache.poi.ss.usermodel.Sheet; >import org.apache.poi.ss.usermodel.Workbook; >import org.apache.poi.ss.util.CellRangeAddressList; >import org.apache.poi.xssf.usermodel.XSSFDataValidation; >import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint; >import org.apache.poi.xssf.streaming.SXSSFSheet; >import org.apache.poi.xssf.streaming.SXSSFWorkbook; > >/** > * Unit test to prove that Apache POI v 3.17 still contains > */ >public class ApachePoiBug61532Test >{ > > @Test > public void testFormulaEvaluatorEvaluateSimpleFormulaCell() throws Exception > { > final Workbook wb = new SXSSFWorkbook(); > try { > final Row row = wb.createSheet().createRow(0); > final Cell a1 = row.createCell(0, CellType.NUMERIC); > a1.setCellValue(1.0); > final Cell a2 = row.createCell(1, CellType.NUMERIC); > a2.setCellValue(2.0); > final Cell a3 = row.createCell(2, CellType.FORMULA); > final String formula = "SUM(A1:B1)"; > a3.setCellFormula(formula); > FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); > int resultType = evaluator.evaluateFormulaCell(a3); > double result = a3.getNumericCellValue(); > // result is correct > assertTrue(String.format("Expected %f to be greater than %f", result, 2.0), result > 2.0); > assertTrue(String.format("Expected %f to be less than %f", result, 4.0), result < 4.0); > // this works for SUM > assertEquals(CellType.FORMULA, a3.getCellTypeEnum()); > assertEquals(formula, a3.getCellFormula()); > } > finally { > wb.close(); > } > } > > @Test > public void testFormulaEvaluatorEvaluateVlookupFormulaCell() throws Exception > { > final Workbook wb = new SXSSFWorkbook(); > try { > final Sheet mainSheet = wb.createSheet("main"); > final Sheet otherSheet = wb.createSheet("other"); > final Row otherRow1 = otherSheet.createRow(0); > final Cell label1 = otherRow1.createCell(0, CellType.STRING); > label1.setCellValue("Thing One"); > final Cell id1 = otherRow1.createCell(1, CellType.STRING); > id1.setCellValue("1"); > final Row otherRow2 = otherSheet.createRow(1); > final Cell label2 = otherRow2.createCell(0, CellType.STRING); > label2.setCellValue("Thing Two"); > final Cell id2 = otherRow2.createCell(1, CellType.STRING); > id2.setCellValue("2"); > final DataValidationHelper dvHelper = mainSheet.getDataValidationHelper(); > final int maxRows = SpreadsheetVersion.EXCEL2007.getMaxRows() - 1; > final CellRangeAddressList addressList = new CellRangeAddressList(0, maxRows, 0, 0); > final String constraint = "='other'!$A:$A"; > final XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper > .createFormulaListConstraint(constraint); > final XSSFDataValidation dataValidation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, > addressList); > dataValidation.setShowErrorBox(true); > mainSheet.addValidationData(dataValidation); > ((SXSSFSheet) otherSheet).setRandomAccessWindowSize(-1); > wb.setSheetHidden(wb.getSheetIndex(otherSheet), true); > final Row row = mainSheet.createRow(0); > final Cell a1 = row.createCell(0, CellType.STRING); > a1.setCellValue("Thing Two"); > final Cell a2 = row.createCell(1, CellType.FORMULA); > final String formula = "VLOOKUP(A1,'other'!$A:$B,2,FALSE)"; > a2.setCellFormula(formula); > FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); > int resultType = evaluator.evaluateFormulaCell(a2); > // result is correct > String result = a2.getStringCellValue(); > assertEquals("2", result); > /* > * The following two assertions fail for VLOOKUP. Contrary to the documentation > * for FormulaEvaluator.evaluateFormulaCell(), the contents of the cell have been > * replaced and the type changed, which is what the separate method evaluateInCell() > * is supposed to do! > */ > assertEquals(CellType.FORMULA, a2.getCellTypeEnum()); > assertEquals(formula, a2.getCellFormula()); > } > finally { > wb.close(); > } > } >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]