You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by bhuvanpavan <bh...@gmail.com> on 2013/07/02 13:15:56 UTC
Need help regarding array formulas implementation in apache poi api
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: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Need help regarding array formulas implementation in apache poi api
Posted by Yegor Kozlov <ye...@dinom.ru>.
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 <bh...@gmail.com> 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: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org