test cases which demonstrate issue

PoiShifterIssue.java (text/plain), 2.32 KB, created by Dragan Jovanović on 2017-09-13 11:23:41 UTC

(hide)

Description: test cases which demonstrate issue

Filename:

MIME Type:

Creator: Dragan Jovanović

Created: 2017-09-13 11:23:41 UTC

Size: 2.32 KB

patch

obsolete

>package inventivio.tactile.msoffice; > >import org.apache.poi.ss.SpreadsheetVersion; >import org.apache.poi.ss.formula.FormulaParser; >import org.apache.poi.ss.formula.FormulaRenderer; >import org.apache.poi.ss.formula.FormulaShifter; >import org.apache.poi.ss.formula.FormulaType; >import org.apache.poi.ss.formula.ptg.Ptg; >import org.apache.poi.ss.usermodel.CellValue; >import org.apache.poi.ss.usermodel.FormulaEvaluator; >import org.apache.poi.xssf.usermodel.*; >import org.junit.Before; >import org.junit.Test; >import static org.junit.Assert.assertEquals; > >public class PoiShifterIssue { > private XSSFWorkbook wb; > private XSSFSheet sheet; > private XSSFCell c3; > private String initialFormula = "A1-A2"; > private final String expectedFormula = "#REF!-A1"; // from ms excel > private FormulaShifter formulaShifter; > > @Before > public void init(){ > wb = new XSSFWorkbook(); > sheet = (XSSFSheet)wb.createSheet("sheet1"); > sheet.createRow(0).createCell(0).setCellValue(1); // A1 > sheet.createRow(1).createCell(0).setCellValue(2); // A2 > c3 = sheet.createRow(2).createCell(2); > c3.setCellFormula(initialFormula); > FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); > CellValue cellValue = evaluator.evaluate(c3); > c3.setCellValue(cellValue.getNumberValue()); > } > > > @Test > public void shiftFormulaUp(){ > shiftFormulaUp(false, 2, 2); //A-A1; excel returns #REF!-A1 > > // other possibilities : > //shiftFormulaUp(true, 2, 2); //A1-#REF! > //shiftFormulaUp(true, 0, 2); //A-A1 > //shiftFormulaUp(false, 0, 5); // #REF!-#REF! > } > public void shiftFormulaUp(boolean forRowShiftElseCopy, int firstRowToShift, int lastRowToShift){ > if(forRowShiftElseCopy) > formulaShifter = FormulaShifter.createForRowShift(0, "sheet1", firstRowToShift, lastRowToShift, -1, SpreadsheetVersion.EXCEL2007); > else formulaShifter = FormulaShifter.createForRowCopy(0, "sheet1", firstRowToShift, lastRowToShift, -1, SpreadsheetVersion.EXCEL2007); > > XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create((XSSFWorkbook) wb); > Ptg[] ptgs = FormulaParser.parse(initialFormula, fpb, FormulaType.CELL, 0); > formulaShifter.adjustFormula(ptgs, 0); > String shiftedFmla = FormulaRenderer.toFormulaString(fpb, ptgs); > System.out.println(shiftedFmla); > assertEquals(expectedFormula, shiftedFmla); > } >} 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]