Norbert Berens wrote:
>
> Paolo wrote:
>
>>Thanks Antony, following your suggestion I've added this macro to my
workbook
>>
>>Private Sub Workbook_Open()
>>    For Each ws In Worksheets>
>>        ws.UsedRange.Calculate
>>        ws.Calculate
>>    Next ws
>>    Application.Calculation = xlCalculationAutomatic
>>    Application.Calculate
>>    Application.CalculateFull
>>    Application.CalculateFullRebuild
>>End Sub
>>
>>This way when the file is opened after being modified with poi it
>>tries to recalculate every formulas.
>
> This solution worked fine but when you open the excel file you are asked
> to save the changes, that are actually made by the recalculation.
> We solved this by introducing the following macro:
>
> Sub workbook_open()
>     If Tabelle3.Cells(1, 1).Value = 1 Then
>         ' Application.CalculateFullRebuild is definitely enough
>         Application.CalculateFullRebuild
>         Tabelle3.Cells(1, 1).Value = 0
>         Application.DisplayAlerts = False
>         ActiveWorkbook.Save
>         Application.DisplayAlerts = True
>     End If
> End Sub
>
> From the POI side, we write a value of 1 into a well defined cell in a
> well defined sheet (here Tabelle3(1,1)), so recalculation is only
> performed the first time you open the workbook and is automatically saved
> without user interaction.
>

By accident I found a solution without an excel macro for the update
problem. I do not quite understand why it works but it works for me: You
only have to read the formula off the cell and write it back again. You do
not even have to evaluate the formula. If you then open Excel, the formulas
are calculated. I did it with the following methods:

        public void updateFormulas(HSSFWorkbook wb) {
int numSheets = wb.getNumberOfSheets();
for( int i = 0; i < numSheets; i++) {
HSSFSheet sheet = wb.getSheetAt(i);
updateFormulas(sheet, wb);
}
}

public void updateFormulas(HSSFSheet sheet, HSSFWorkbook wb) {

HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);

int firstrow = sheet.getFirstRowNum();

int lastrow = sheet.getLastRowNum();

for (int i = firstrow; i <= lastrow; i++) {

HSSFRow row = sheet.getRow(i);
if( row == null ) {
continue;
}

short firstcell = row.getFirstCellNum();
short lastcell = row.getLastCellNum();

for(int j = firstcell; j <= lastcell; j++) {
HSSFCell cell = row.getCell((short) j);

if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {

String formel = cell.getCellFormula();


/*
                                        evaluator.setCurrentRow(row);

try {
evaluator.evaluateInCell(cell);
} catch (Exception ex) {
                                                // This always throwed a
ClassCastException !!

}*/

cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);

cell.setCellFormula(formel);

}
}

}

}



This seems to be a work around for the problem within poi-3.0.1. Any
explanations are welcome.

Regards, Norbert
--
View this message in context: http://www.nabble.com/Excel-2003-does-not-update-formula-tf3188604.html#a13098001
Sent from the POI - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi....
For additional commands, e-mail: user-help@poi....