Re: HSSF Question

HSSF does provide cell-by-cell updating... in a manner of speaking.

HSSF provides routines that load the whole Excel workbook into memory where it's stored as a collection of Java objects. There are objects representing the workbook, each sheet in the workbook, each row in the sheet, and each cell in the row.

so to update a cell, you do something like (this is using my RPG service program to simplify a few things):

workbook = HSSF_open('/IFS/PATH/TO/FILE.XLS');
sheet = HSSF_getSheet(workbook: 'SheetName');

row = HSSFSheet_getRow(sheet: 5);
if (row = *null);
row = HSSFSheet_createRow(sheet: 5);
endif;

cell = HSSFRow_getCell(row: 0);
if (cell = *null);
cell = HSSFRow_createCell(row: 0);
endif;

HSSFCell_setCellType( cell: CELL_TYPE_NUMERIC);
HSSFCell_setCellValueD(cell: 12345);

HSSF_save(workbook: '/IFS/PATH/TO/FILE.XLS');

So... what this code essentially does is load a workbook into memory, retrieve teh sheet object, retrieve a row from that sheet (or, if not found, it creates it), retrieve a cell from the row (or, if not found, create it) and set the value of that cell.

HSSF numbers row/columns starting with 0. So the above retrieves row 5, cell 0. In Excel, that would be cell A6.

(Yes, it's a lot of code to set the value of a single cell -- but, you could put it in a subproc and just call that subproc when needed)

Using this technique you could open the workbook, update all of the cells you like, and then save it back to disk. You're right that this is all done in memory, and it's all saved to disk at once... but it does allow you to go cell-by-cell and make updates.

On 8/3/2011 6:20 PM, John Yeung wrote:

I am not familiar with HSSF personally, but I do deal with Excel files
a lot, and the file format is such that our intuitive concept of
"updating" is not applicable. You really copy all the data from the
Excel file into memory, then manipulate the data in memory as needed,
and then write a completely new file (even if this file has the same
name as the original). So I would be surprised (and impressed!) if
HSSF provides cell-by-cell updating.