Writing in existing excel(.xls) file

Hi All,
I want to write in an existing excel(in sheet1) file in a perticular cell(say row=1,cell=6). I'm using poi jar. But I could not write in that perticular cell.
String val= //value that I want to write;

String filePath = //getting the filepath. ;
File file=new File(filePath);
FileInputStream fis=new FileInputStream(file);

HSSFWorkbook wb=new HSSFWorkbook(fis);
HSSFSheet st=wb.getSheet("Sheet1");
HSSFRow row=st.getRow(1);
HSSFCell cell= row.getCell(6);
cell.setCellValue(val);
fis.close();

This should write val to sheet1, row 1 cell 6 of the perticular excel that I'm passing. In filepath I'm passing file path along with file name.

But this is not working though not throwing any errors.

Could someone please help?

From the POI quick guide:

Reading and Rewriting Workbooks
InputStream inp = new FileInputStream("workbook.xls"); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(2); Cell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a test"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();

Lines 8 and 9 might be important.

Cheers
Wim

This'll create a new cell and also will overwrite the existing file. Where as I want to only update my existing file inserting some value(String val in code) in perticular cell,row.

http://poi.apache.org/spreadsheet/how-to.html

Taken from the linked page above...

Modifying the file you have read in is simple. You retrieve the object via an assessor method, remove it via a parent object's remove method (sheet.removeRow(hssfrow)) and create objects just as you would if creating a new xls. When you are done modifying cells just call workbook.write(outputstream)

So basically the suggestion you have been given IS the solution that the makers of the POI library are also giving. Regardless of processes involved the outcome is still the same.

Your requirements:
- open an existing xls file
- update a particular cell with a value x

In your code, if after your 'fis.close()' statement you query (through java code!) the value of that specific cell (1:6), does it contain the value?

If yes, than your code has met your requirements.

If you need the new value to be in the xls file that you opened, some form of writing or overwriting will be necessary though.

Wim

Overwriting the existing file and updating the existing file are the same thing. You seem to see a distinction where none exists.

@Wim: I could not find the perticular value in cell(1:6) after fis.close(). Your understanding is right.
@Ulf: Whatever it may be, I want to have cell(1,6) value="x" without hampering other cells values.

Is there anyway I can do it?Can anyone tell me some way to do so??

Is it just me or has anyone else noticed that the code will not write anything because it uses a FileInputStream only?
At least I think it won't write anything, might be I'm just missing something here...

Too difficult a question for "beginning". Moving thread.

harry ganguly wrote:Is there anyway I can do it?Can anyone tell me some way to do so??

Take a careful look at all the steps that are taken in the code example I copied here from the POI quick guide.

1. open a file (with a certain path and filename)
2. create a workbook from it
3. navigate to the cell
4. create the cell if it doesn't exist yet
5. fill the cell with a value (of type x)
6. write the workbook to a file (to that same path and with the same filename; overwrite)

If you omit step 6 the update of the cell will never 'persist', will never be saved to the file. The new value will be available in memory though. As the following code demonstrates:

As pointed out by Jan Hoppman: FileInputStream will not be enough to have your update written to the file.

Wim

@Jan: FileOuputStream will create a new wb....which is not my reqmnt.

harry ganguly wrote:@Jan: FileOuputStream will create a new wb....which is not my reqmnt.

It is. You need to put your changes into the file, that is, write it. You should just overwrite your old file. Read Wim's post.

Hi Harry, I too worked many times on excel files. I use jxl.jar. That api consist the edit/(insert into) cell value method based on row and column.

::Good luck::

Hi chaitanya,
Can you please give me one example??Since i cant find cell.setCellValue(val) function if i use jxl.jar. How to set val in a cell???

It'd be really helpful if you can put some light on the same.

harry ganguly wrote:Hi chaitanya,
Can you please give me one example??Since i cant find cell.setCellValue(val) function if i use jxl.jar. How to set val in a cell???

It'd be really helpful if you can put some light on the same.

I'm asking in reference to jxl.jar. Please give an example..

I doubt it is the purpose of this forum to be a code generator, even though examples will in most cases clarify things. I get the feeling that in this case you can easily find the information you're looking for from the JExcelApi website. It is a different API so you will have to adapt your code accordingly. Most likely cell.SetCellValue(val) will have to be replaced by something else.

Let the tutorial guide you; if you get in trouble with the code while following the tutorial, I'm sure there's enough helpful hands around to get you on your way.

And a last little tap on the fingers: don't let a small hiccup with one API force you to go to the next one so soon. I feel that we have given quite a lot of pointers as to how to solve your initial problem. If you don't get a grip on that problem while using POI, chances are you won't get a grip with JExcelApi either.

Cheers
Wim