The R1C1 reference style is not supported by POI. Try to use A1 references.
Yegor On Tue, Jul 2, 2013 at 3:15 PM, bhuvanpavan <[email protected]> wrote: > i got allocated updation of existing project .Previous developer used jcom > api to export data to excel sheets .but jcom api dont work with 64 bit > systems.I decided to change code and using apache poi api. I managed to done > many methods .My problem is array formulas . i Need to implement array > formulas using apache poi . Those formulas are posted below, Any help will > be more appreciable. Thanks in advance guys.. > String formula1 = "SUM(R[-2]C/2.000)";//vat calculation > String formula2 = "SUM(R[-1]C-R[1]C)"; > String formula3 = "SUM(R[-" + Integer.toString(listTypeTotals.size()+1) + > "]C:R[-2]C)"; > I tried to set that cell as formulatype and passing formula as string. > > setCellFormulaStyle(sheet, 4, i+2, formula2); > > public static void setCellFormulaStyle(HSSFSheet sheet,int row, int > column,String value) > { > HSSFRow temprow = null; > temprow =getRow_CreateRow(sheet, row); > temprow.createCell(column).setCellFormula(value); > } > > public static HSSFRow getRow_CreateRow(HSSFSheet sheet,int row) > { > HSSFRow excelrow=null; > excelrow = sheet.getRow(row); > if(excelrow==null) > { > excelrow =sheet.createRow(row); > return excelrow; > }else > return excelrow; > } > > I am getting following exception > > org.apache.poi.ss.formula.FormulaParseException: Specified named range 'R' > does not exist in the current workbook. at > org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:569) > at > org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:517) > at > org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268) > at > org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119) > at > org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079) > at > org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066) > at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426) at > org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526) > at > org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510) > at > org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467) > at > org.apache.poi.ss.formula.FormulaParser.Arguments(FormulaParser.java:1051) > at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:936) > at > org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:558) > at > org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:429) > at > org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:268) > at > org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1119) > at > org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1079) > at > org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1066) > at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1426) at > org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1526) > at > org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1510) > at > org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1467) > at > org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1447) > at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1568) at > org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:176) at > org.apache.poi.hssf.model.HSSFFormulaParser.parse(HSSFFormulaParser.java:72) > at org.apache.poi.hssf.usermodel.HSSFCell.setCellFormula(HSSFCell.java:594) > > > > -- > View this message in context: > http://apache-poi.1045710.n5.nabble.com/Need-help-regarding-array-formulas-implementation-in-apache-poi-api-tp5713162.html > Sent from the POI - User mailing list archive at Nabble.com. > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
