HSSF: Write Excel-spreadsheets

[kk...@work09.softwing.de]

Hello togeher,

I've written an application using POI for creating and writing Excel spreadsheets.

Works fine so far but I've got following problem:

I've got an existing Excel spreadsheet which I'd like to customize, e.g.

first
A
1 0
2 0
3 0 (content of cell:=sum(A1..A2))

then after Java-processing the Excel spreadsheet

A
1 4
2 5
3 0 (content of cell:=sum(A1.A2))

The cell A3 with the sum expression isn't recallculatet by Excel, even pressing F8 (for manually recalculating the spreadsheet) doesn't work out. If I go with the cursur in the cell A3 and press return, then the correct number 9 is shown in the cell. It seems to be so that Excel doesn't realize that the cells A1 and A2 got new numbers via HSSF. The code looks like this:

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFRow row;
HSSFCell cell;

HSSFSheet sheet = wb.getSheetAt(0);

row = sheet.getRow((short)28);
cell = row.getCell((short)5);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(myValue));

Is there somthing like sheet.flush() which calculates the sum expressions new in the spreadsheet? I didn't find anything which this does. Perhaps anybody knows about this problem.

One thing more.

row = sheet.getRow((short)28);

getRow allows only a short value, so I can only address the lines from 0 to 32K, Excel prior to 2007 is able to address the lines till 64K. How can I address the lines starting from 32K+1 - 64K? I don't have any idea.

Thanx and best regards...

Karsten

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

Re: HSSF: Write Excel-spreadsheets

[Pierre Lavignotte <pi...@gmail.com>]

Hi Karsten,

Just call HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook) to refresh
the whole workbook formula results.

For you getRow() issue, I think this has been changed since POI 3.2.
In this version the method signature is :
HSSFRow org.apache.poi.hssf.usermodel.HSSFSheet.getRow(int rowIndex)


Pierre


Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com


On Fri, Jan 9, 2009 at 10:43 AM, <kk...@work09.softwing.de> wrote:

> Hello togeher,
>
> I've written an application using POI for creating and writing Excel
> spreadsheets.
>
> Works fine so far but I've got following problem:
>
> I've got an existing Excel spreadsheet which I'd like to customize, e.g.
>
> first
> A
> 1 0
> 2 0
> 3 0 (content of cell:=sum(A1..A2))
>
> then after Java-processing the Excel spreadsheet
>
> A
> 1 4
> 2 5
> 3 0 (content of cell:=sum(A1.A2))
>
> The cell A3 with the sum expression isn't recallculatet by Excel, even
> pressing F8 (for manually recalculating the spreadsheet) doesn't work out.
> If I go with the cursur in the cell A3 and press return, then the correct
> number 9 is shown in the cell. It seems to be so that Excel doesn't realize
> that the cells A1 and A2 got new numbers via HSSF. The code looks like this:
>
> HSSFWorkbook wb = new HSSFWorkbook(fs);
>
> HSSFRow row;
> HSSFCell cell;
>
> HSSFSheet sheet = wb.getSheetAt(0);
>
> row = sheet.getRow((short)28);
> cell = row.getCell((short)5);
> cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
> cell.setCellValue(myValue));
>
> Is there somthing like sheet.flush() which calculates the sum expressions
> new in the spreadsheet? I didn't find anything which this does. Perhaps
> anybody knows about this problem.
>
> One thing more.
>
> row = sheet.getRow((short)28);
>
> getRow allows only a short value, so I can only address the lines from 0 to
> 32K, Excel prior to 2007 is able to address the lines till 64K. How can I
> address the lines starting from 32K+1 - 64K? I don't have any idea.
>
> Thanx and best regards...
>
> Karsten
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>