Read && Modify a excel cell value in HSSF

[sapatos]

hi,

I've hit a stumbling block. My app happily reads in my spreadsheet, however a new request has been made that after reaching the end of a record the cellNum((short) 0) should be updated to "P".

This would sound a simple task, however I can't get it to work for the life of me. At the point I want to update cell 0 I am at cell 26. Is there any way to open a fileinputstream and fileoutputstream on the same workbook so that you can modify and read at the same time.

the apache website is very limited in what it offers as the way of tutorials.

A segment of what i'm doing is below:

POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream(fileName));
HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(1);

HSSFRow row = sheet.getRow(1);

for (Iterator cit = row.cellIterator(); cit.hasNext() {
HSSFCell cell = (HSSFCell) cit.next();

...processing

if (cell.getCellNum() == 26) {update cell(0) to value "P"}

Many thanks if anyone can point me in the right direction.

 

[nirajj]

You should be able to use the Fileinputstream and fileoutputstream on the same file simultaneously. Have you tried it?

 

[sapatos]

thats what i thought as well. but when i open a FileOutputStream on the same filename it creates an empty file of 0 bytes.

 

[nirajj]

In that case my next approach would be to use RandomAccessFile.

I would also try something like creating a new temp file for write and once you are done then delete the original file and rename the temp file.

PS: I am just throwing random ideas here. I have not really tried them.

 

[sapatos]

thanks for the ideas, that one was actually something i was experimenting with. However keeping all the formatting etc from the original and re-writing is more work than its worth for the job in hand.

 

[TonHu]

Have a look at JExcelApi over here:

http://www.andykhan.com/jexcelapi/

HTH
TonHu

 

[sapatos]

I read a comparison review of apache POI and this one and POI came out on top which was why I went for that option. How have you found this api? Any issues at all?

Sapatos

 

[TonHu]

Hm, I read a comparion review, obviously somewhere else, that favored jxl, so we went for it after a functional evaluation.
Implementation is quite straightforward, but we added a wrapper around it to avoid implementation specifics, so we can switch later if better solutions arise .

HTH
TonHu