Apache poi. Formula evalution. evaluteAll vs setForceFormulaRecalculation

Apache poi. Formula evalution. evaluteAll vs setForceFormulaRecalculation


[better quality]:. http://imageshack.us/photo/my-images/51/v5cg.png/
Problem: i use formulas in my workBook.xlsx. Depending on the
circumstances i change value (3,3). At this example i changed 10 to 20.
And after that i want to calculate the formula, which use this cell. When
it has finished, it will override the value of the cells, remove formula
and record the value. This cell no longer be used for the calculations
again.
Code:
public class ReaderXlsx {
public List<List<String>> ReaderXlsx(String sfilename, int
firstColumn, int columnsCount, int rowsCount, int moduleCount){
int lastColumn=firstColumn+columnsCount;
List<List<String>> rowsContent = new ArrayList<List<String>>();
try ( FileInputStream fileInputStream = new
FileInputStream("C:\\Users\\student3\\"+sfilename+".xlsx");)
{
XSSFWorkbook workBook = new XSSFWorkbook(fileInputStream);
XSSFSheet sheet = workBook.getSheetAt(0);
int firstRow = findOneInFirstColumn(sheet,50);
setModuleCount(sheet,moduleCount);
workBook.getCreationHelper().createFormulaEvaluator().evaluateAll();
toNewLine:
for (int lineId=firstRow;lineId<rowsCount;lineId++) {
List<String> columnsContent = new ArrayList<String>();
Row row = sheet.getRow(lineId);
if (row==null){continue toNewLine;}
if (row.getCell(2)==null ||
row.getCell(2).getStringCellValue().equals("") ) {continue
toNewLine;}
for (int
columnId=firstColumn+1;columnId<lastColumn;columnId++)
{
Cell cell = row.getCell(columnId-1);
if ((cell==null)) { continue toNewLine;}
cell.setCellType(Cell.CELL_TYPE_STRING);
if ((columnId==0 &
cell.getStringCellValue().equals(""))) {continue
toNewLine;}
if ((columnId==5 &
cell.getStringCellValue().equals("")) ||
(columnId==6 &
cell.getStringCellValue().equals("")))
cell.setCellValue("0");
columnsContent.add(cell.getStringCellValue());
}
rowsContent.add(columnsContent);
}
try(FileOutputStream out = new
FileOutputStream("C:\\Users\\student3\\Used"+sfilename+".xlsx");
) {
workBook.write(out);
out.close(); }
}
catch (IOException e) {
e.printStackTrace();
}
return rowsContent;
}
private Integer findOneInFirstColumn(XSSFSheet sheet, Integer maxRows){
int result=0;
toNextLine:
for (int lineId=0;lineId<maxRows;lineId++){
Row row = sheet.getRow(lineId);
if (row==null | row.getCell(0)==null) {result++; continue
toNextLine; }
else{
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
if
(row.getCell(0).getStringCellValue().equals("1")){return
result;}
else {result++;}
}
}
return result;
}
private void setModuleCount(XSSFSheet sheet, Integer moduleCount){
Row row = sheet.getRow(1);
if (moduleCount==0) {}
if (moduleCount>0)
{row.createCell(2).setCellValue(moduleCount.toString());}
}
}
Now i use 2 files, becouse i need to save my formulas. I want to use only
1 (source) and update him correctly.