You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by mouss4rs <ou...@gmail.com> on 2012/05/02 11:15:30 UTC
The totals aren't recalculated
Hi,
i write in a cell normally like this:
/row2 = sheet2.getRow(Integer.parseInt(Quimper[1][y]));
cell2 = row2.createCell(66);
cell2.setCellValue(tabheures[1][z]/24.0);
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("[h]:mm:ss"));
cellStyle.setBorderBottom(BorderStyle.THIN);//bordure du bas
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(BorderStyle.THIN);//bordure gauche
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(BorderStyle.THIN);//bordure droite
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(BorderStyle.THIN);//bordure du haut
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell2.setCellStyle(cellStyle);/
But when i open my excel file, *i see that the totals are not recalculated*.
Are we obliged to rewrite the formula of calculation or Is there a simpler
solution ??
Thanks
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/The-totals-aren-t-recalculated-tp5680022.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: The totals aren't recalculated
Posted by Yegor Kozlov <ye...@dinom.ru>.
Put setForceFormulaRecalculation before wb2.write(fileOut);
On Wed, May 2, 2012 at 3:29 PM, mouss4rs <ou...@gmail.com> wrote:
> 1. recalculate formulas yourself using FormulaEvaluator => no because they
> are many many cells
> 2. Call workbook.setForceFormulaRecalculation(true); Excel will => I did
> this:
>
> / System.out.println("*******************SEMAINE 52*********************");
> for (int y=0; y<nbPrenomsQ; y++){
> if(Quimper[0][y]!=null){
> int z;
> repet :for(z = 0; z<nbPrenomsS; z++){
> if(Saphyr[0][z]!=null){
> if(semaineSaphyr==52){
> System.out.println(Quimper[0][y]+y+" = "+Saphyr[0][z]+z);
> if(Quimper[0][y].equals(Saphyr[0][z])){
> row2 = sheet2.getRow(Integer.parseInt(Quimper[1][y]));
> cell2 = row2.createCell(66);
> cell2.setCellValue(tabheures[1][z]/24.0);
> cellStyle = wb2.createCellStyle();
> cellStyle.setDataFormat(fmt.getFormat("[h]:mm:ss"));
> cellStyle.setBorderBottom(BorderStyle.THIN);//bordure du bas
> cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
> cellStyle.setBorderLeft(BorderStyle.THIN);//bordure gauche
> cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
> cellStyle.setBorderRight(BorderStyle.THIN);//bordure droite
> cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
> cellStyle.setBorderTop(BorderStyle.THIN);//bordure du haut
> cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
> cellStyle.setAlignment(HorizontalAlignment.CENTER);
> cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
> cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
> cell2.setCellStyle(cellStyle);
> System.out.println("SEMAINE 52: "+Quimper[0][y]+y+" =
> "+Saphyr[0][z]+z+"a fait: "+tabheures[1][z]);
> System.out.println("on écrit l'heure trouvée à la
> ligne"+Quimper[1][y]);
> z=0;
> break repet;
> }
> }
> }
> }
> z=0;
> }
> }
> // Write the output to a file
> FileOutputStream fileOut = new FileOutputStream(fichierQuimper);
> wb2.write(fileOut);
> fileOut.close();
> <b>wb2.setForceFormulaRecalculation(true); */
>
> but when i opening the workbook, it didn't re-calculate all formulas ???
>
>
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/The-totals-aren-t-recalculated-tp5680022p5680235.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
Re: The totals aren't recalculated
Posted by mouss4rs <ou...@gmail.com>.
Thanks !!
I found !
i did now this:
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(fichierQuimper);
*wb2.setForceFormulaRecalculation(true);*wb2.write(fileOut);
fileOut.close();
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/The-totals-aren-t-recalculated-tp5680022p5680298.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: The totals aren't recalculated
Posted by mouss4rs <ou...@gmail.com>.
1. recalculate formulas yourself using FormulaEvaluator => no because they
are many many cells
2. Call workbook.setForceFormulaRecalculation(true); Excel will => I did
this:
/ System.out.println("*******************SEMAINE 52*********************");
for (int y=0; y<nbPrenomsQ; y++){
if(Quimper[0][y]!=null){
int z;
repet :for(z = 0; z<nbPrenomsS; z++){
if(Saphyr[0][z]!=null){
if(semaineSaphyr==52){
System.out.println(Quimper[0][y]+y+" = "+Saphyr[0][z]+z);
if(Quimper[0][y].equals(Saphyr[0][z])){
row2 = sheet2.getRow(Integer.parseInt(Quimper[1][y]));
cell2 = row2.createCell(66);
cell2.setCellValue(tabheures[1][z]/24.0);
cellStyle = wb2.createCellStyle();
cellStyle.setDataFormat(fmt.getFormat("[h]:mm:ss"));
cellStyle.setBorderBottom(BorderStyle.THIN);//bordure du bas
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(BorderStyle.THIN);//bordure gauche
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(BorderStyle.THIN);//bordure droite
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(BorderStyle.THIN);//bordure du haut
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell2.setCellStyle(cellStyle);
System.out.println("SEMAINE 52: "+Quimper[0][y]+y+" =
"+Saphyr[0][z]+z+"a fait: "+tabheures[1][z]);
System.out.println("on écrit l'heure trouvée à la
ligne"+Quimper[1][y]);
z=0;
break repet;
}
}
}
}
z=0;
}
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream(fichierQuimper);
wb2.write(fileOut);
fileOut.close();
<b>wb2.setForceFormulaRecalculation(true); */
but when i opening the workbook, it didn't re-calculate all formulas ???
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/The-totals-aren-t-recalculated-tp5680022p5680235.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: The totals aren't recalculated
Posted by Yegor Kozlov <ye...@dinom.ru>.
POI does not re-calculate formulas automatically. You have two options:
1. recalculate formulas yourself using FormulaEvaluator,
2. Call workbook.setForceFormulaRecalculation(true); Excel will
re-calculate all formulas when opening the workbook.
Yegor
On Wed, May 2, 2012 at 1:15 PM, mouss4rs <ou...@gmail.com> wrote:
> Hi,
>
> i write in a cell normally like this:
>
> /row2 = sheet2.getRow(Integer.parseInt(Quimper[1][y]));
> cell2 = row2.createCell(66);
> cell2.setCellValue(tabheures[1][z]/24.0);
> cellStyle = wb2.createCellStyle();
> cellStyle.setDataFormat(fmt.getFormat("[h]:mm:ss"));
> cellStyle.setBorderBottom(BorderStyle.THIN);//bordure du bas
> cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
> cellStyle.setBorderLeft(BorderStyle.THIN);//bordure gauche
> cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
> cellStyle.setBorderRight(BorderStyle.THIN);//bordure droite
> cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
> cellStyle.setBorderTop(BorderStyle.THIN);//bordure du haut
> cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
> cellStyle.setAlignment(HorizontalAlignment.CENTER);
> cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);
> cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
> cell2.setCellStyle(cellStyle);/
>
> But when i open my excel file, *i see that the totals are not recalculated*.
>
> Are we obliged to rewrite the formula of calculation or Is there a simpler
> solution ??
>
> Thanks
>
> --
> View this message in context: http://apache-poi.1045710.n5.nabble.com/The-totals-aren-t-recalculated-tp5680022.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