You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by zephod <ze...@tlen.pl> on 2013/02/04 00:39:01 UTC
How to make new Excel 2007 functions like SUMIFS work in Apache POI?
I using Apache POI for Java to set formulas in Excel cells. I'm using the following code to make the formulas appear as formulas rather than as Strings:
// "cell" object previously created or looked upString StrFormula = "SUM(\"A1:A10\")";cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);cell.setCellFormula(strFormula);The setCellType(HSSFCell.CELL_TYPE_FORMULA) makes the trick for most formulas but not all. There are some functions (introduced in Excel 2007) like SUMIFS, COUNTIFS. IFERROR, AVERAGEIFS that cause the cell to contain a String. It is necessary to click+enter every such cell in Excel for the formula to get recognized. Is there any trick to make those functions work properly?
PS. Why don't you make the setCellFormula() implicitly call the setCellType(HSSFCell.CELL_TYPE_FORMULA)? This cost me some time to figure out this was need and made bad impression.
Kind regards,
Jacek
Re: How to make new Excel 2007 functions like SUMIFS work inApachePOI?
Posted by zephod <ze...@tlen.pl>.
I think I solved my problem by switching to XSSFWorkbook. Previously I used HSSF as the tutorials do, which did not work with the new functions. Reevaluating values was not necessary.
Cheers,
Jacek
Dnia 5 lutego 2013 13:05 Nick Burch <ap...@gagravarr.org> napisał(a):
>
> > On Tue, 5 Feb 2013, zephod wrote:
> >> Are you triggering a formula recalculation at the end of the processing
> >> of the file? Excel caches formula values, so you'll need to update
> >> those when you're done making changes
> >>
> > No, I'm just constructing a spreadsheet with formulas and store it in a
> > file. Formulas with some functions work OK, but the ones with the new
> > functions that I listed don't. How would I trigger the recalculation you
> > suggest? Why some formulas work and others not?
>
> When you're done making changes to the file, you need to re-evaluate.
> Otherwise, Excel will show old/confused values until you force a refresh
> (it caches values). See http://poi.apache.org/spreadsheet/eval.html
>
> Nick
>
> ---------------------------------------------------------------------
> 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
Re: How to make new Excel 2007 functions like SUMIFS work in
ApachePOI?
Posted by Nick Burch <ap...@gagravarr.org>.
On Tue, 5 Feb 2013, zephod wrote:
>> Are you triggering a formula recalculation at the end of the processing
>> of the file? Excel caches formula values, so you'll need to update
>> those when you're done making changes
>>
> No, I'm just constructing a spreadsheet with formulas and store it in a
> file. Formulas with some functions work OK, but the ones with the new
> functions that I listed don't. How would I trigger the recalculation you
> suggest? Why some formulas work and others not?
When you're done making changes to the file, you need to re-evaluate.
Otherwise, Excel will show old/confused values until you force a refresh
(it caches values). See http://poi.apache.org/spreadsheet/eval.html
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to make new Excel 2007 functions like SUMIFS work in ApachePOI?
Posted by zephod <ze...@tlen.pl>.
Dnia 4 lutego 2013 12:36 Nick Burch <ap...@gagravarr.org> napisał(a):
>
> > On Mon, 4 Feb 2013, zephod wrote:
> > I using Apache POI for Java to set formulas in Excel cells. I'm using
> > the following code to make the formulas appear as formulas rather than
> > as Strings:
> >
> > // "cell" object previously created or looked up
> > String StrFormula = "SUM(\"A1:A10\")";
> > cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
> > cell.setCellFormula(strFormula);
>
> You shouldn't need to set the cell type, the setCellFormula call will do
> that for you
>
> > There are some functions (introduced in Excel 2007) like SUMIFS,
> > COUNTIFS. IFERROR, AVERAGEIFS that cause the cell to contain a String.
> > It is necessary to click+enter every such cell in Excel for the formula
> > to get recognized.
>
> Are you triggering a formula recalculation at the end of the processing of
> the file? Excel caches formula values, so you'll need to update those when
> you're done making changes
>
No, I'm just constructing a spreadsheet with formulas and store it in a file. Formulas with some functions work OK, but the ones with the new functions that I listed don't. How would I trigger the recalculation you suggest? Why some formulas work and others not?
Kind regards,
Jacek
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to make new Excel 2007 functions like SUMIFS work in Apache
POI?
Posted by Nick Burch <ap...@gagravarr.org>.
On Mon, 4 Feb 2013, zephod wrote:
> I using Apache POI for Java to set formulas in Excel cells. I'm using
> the following code to make the formulas appear as formulas rather than
> as Strings:
>
> // "cell" object previously created or looked up
> String StrFormula = "SUM(\"A1:A10\")";
> cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
> cell.setCellFormula(strFormula);
You shouldn't need to set the cell type, the setCellFormula call will do
that for you
> There are some functions (introduced in Excel 2007) like SUMIFS,
> COUNTIFS. IFERROR, AVERAGEIFS that cause the cell to contain a String.
> It is necessary to click+enter every such cell in Excel for the formula
> to get recognized.
Are you triggering a formula recalculation at the end of the processing of
the file? Excel caches formula values, so you'll need to update those when
you're done making changes
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: How to make new Excel 2007 functions like SUMIFS work in Apache POI?
Posted by zephod <ze...@tlen.pl>.
Sorry for the formating. Below a corrected version:
I using Apache POI for Java to set formulas in Excel cells. I'm using the following code to make the formulas appear as formulas rather than as Strings:
// "cell" object previously created or looked up
String StrFormula = "SUM(\"A1:A10\")";
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(strFormula);
The setCellType(HSSFCell.CELL_TYPE_FORMULA) makes the trick for most formulas but not all. There are some functions (introduced in Excel 2007) like SUMIFS, COUNTIFS. IFERROR, AVERAGEIFS that cause the cell to contain a String. It is necessary to click+enter every such cell in Excel for the formula to get recognized. Is there any trick to make those functions work properly?
PS. Why don't you make the setCellFormula() implicitly call the setCellType(HSSFCell.CELL_TYPE_FORMULA)? This cost me some time to figure out this was need and made bad impression.
Kind regards,
Jacek
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org