minimal program that demonstrates the problem

PrSumIf.java (text/plain), 4.93 KB, created by Axel Howind on 2018-12-08 17:33:14 UTC

(hide)

Description: minimal program that demonstrates the problem

Filename:

MIME Type:

Creator: Axel Howind

Created: 2018-12-08 17:33:14 UTC

Size: 4.93 KB

patch

obsolete

>package issues.poi; > >import java.io.IOException; >import java.nio.file.Files; >import java.nio.file.Paths; >import java.util.HashMap; >import java.util.Map; >import java.util.logging.ConsoleHandler; >import java.util.logging.Level; >import java.util.logging.Logger; > >import org.apache.poi.ss.usermodel.Cell; >import org.apache.poi.ss.usermodel.CellType; >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.usermodel.WorkbookFactory; >import org.apache.poi.ss.usermodel.Row.MissingCellPolicy; > >public class PrSumIf { > > private static final Logger LOG; > > static { > System.setProperty("java.util.logging.SimpleFormatter.format", > "[%1$tF %1$tT %1$tL] [%4$-7s] %5$s %6$s%n"); > > LOG = Logger.getLogger(PrSumIf.class.getName()); > > Level level = Level.ALL; > ConsoleHandler handler = new ConsoleHandler(); > handler.setLevel(level); > LOG.addHandler(handler); > LOG.setLevel(level); > Logger.getLogger("").setLevel(level); > } > > public static void main(String[] args) { > System.out.println("start"); > try { > test(); > } catch (Exception e) { > LOG.log(Level.SEVERE, "exception in test", e); > System.exit(1); > } > } > > public static void test() throws IOException { > LOG.fine("ceate workbook"); > Workbook wb = WorkbookFactory.create(true); > > LOG.fine("ceate evaluator"); > FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); > > LOG.fine("ceate sheet"); > Sheet sheet = wb.createSheet("test SUM_IF"); > > int r = 0; > > Row row = sheet.createRow(r++); > row.createCell(0).setCellValue("Key"); > row.createCell(1).setCellValue("Value"); > > LOG.fine("ceate value cells"); > double[] keys = { 1.75, 2.25, 3.25, 1.75, 3.25, 3.25, 0.9, 1.25 }; > double[] values = { 123, 456, 789, 10, 20, 30, 40, 50 }; > assert keys.length==values.length; > > final int n = keys.length; > Map<Double,Double> expected = new HashMap<>(); > > final int firstRow = r+1; > final int lastRow = r+n; > > for (int i=0; i<keys.length; i++) { > // write data to sheet > Double key = keys[i]; > Double value = values[i]; > > row = sheet.createRow(r++); > row.getCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(key); > row.getCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue(value); > > // update sums (if key is not present or mapped to null, store value. Otherwise store sum.) > expected.merge(key, value, (a,b) -> a+b); > } > > // insert a blank row > row = sheet.createRow(r++); > > LOG.fine("ceate formula cells"); > > String keyRange = String.format("%1$s%2$d:%1$s%3$d", "A", firstRow, lastRow); > LOG.finer(() -> "key range: "+keyRange); > String valueRange = String.format("%1$s%2$d:%1$s%3$d", "B", firstRow, lastRow); > LOG.finer(() -> "value range: "+keyRange); > > row = sheet.createRow(r++); > row.createCell(0).setCellValue("Key"); > row.createCell(1).setCellValue("Sum"); > row.createCell(2).setCellValue("POI"); > row.createCell(3).setCellValue("Exp"); > row.createCell(4).setCellValue("Status"); > > for (var entry: expected.entrySet()) { > row = sheet.createRow(r++); > > Cell cellKey = row.createCell(0); > Cell cellFormula = row.createCell(1); > Cell cellPoi = row.createCell(2); > Cell cellExpected = row.createCell(3); > Cell cellStatus = row.createCell(4); > > String formula = String.format("SUMIF(%s,%s,%s)", > keyRange, > cellKey.getAddress(), > valueRange); > LOG.finer(() -> "formula: "+formula); > > Double resultExpected = entry.getValue(); > > cellKey.setCellValue(entry.getKey()); > cellFormula.setCellFormula(formula); > > CellType resultType = evaluator.evaluateFormulaCell(cellFormula); > String status; > switch (resultType) { > case NUMERIC: > double resultPoi = cellFormula.getNumericCellValue(); > status = resultPoi==resultExpected ? "OK" : "FAIL"; > cellPoi.setCellValue(resultPoi); > break; > default: > cellPoi.setCellValue("???"); > status = "FAIL"; > break; > } > > cellExpected.setCellValue(resultExpected); > cellStatus.setCellValue(status); > } > > LOG.fine("writing workbook to file"); > try (var out = Files.newOutputStream(Paths.get("test.xlsx"))) { > wb.write(out); > } > } >} 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]