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]
