File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Java in General and the fly likes Writing in existing excel(.xls) file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "Writing in existing excel(.xls) file" Watch "Writing in existing excel(.xls) file" New topic
Author

Writing in existing excel(.xls) file

harry ganguly
Greenhorn

Joined: Dec 14, 2010
Posts: 13
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?
Wim Vanni
Ranch Hand

Joined: Apr 06, 2011
Posts: 96

From the POI quick guide:


Reading and Rewriting Workbooks


Lines 8 and 9 might be important.

Cheers
Wim
harry ganguly
Greenhorn

Joined: Dec 14, 2010
Posts: 13
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.

Joshua Barrett
Greenhorn

Joined: Sep 08, 2010
Posts: 27
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.
Wim Vanni
Ranch Hand

Joined: Apr 06, 2011
Posts: 96

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
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39543
    
  27
Overwriting the existing file and updating the existing file are the same thing. You seem to see a distinction where none exists.


Ping & DNS - updated with new look and Ping home screen widget
harry ganguly
Greenhorn

Joined: Dec 14, 2010
Posts: 13
@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??
Jan Hoppmann
Ranch Hand

Joined: Jul 19, 2010
Posts: 142

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...


Life is full of choices. Sometimes you make the good ones, and sometimes you have to kill all the witnesses.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 36465
    
  15
Too difficult a question for "beginning". Moving thread.
Wim Vanni
Ranch Hand

Joined: Apr 06, 2011
Posts: 96

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
harry ganguly
Greenhorn

Joined: Dec 14, 2010
Posts: 13
@Jan: FileOuputStream will create a new wb....which is not my reqmnt.

Jan Hoppmann
Ranch Hand

Joined: Jul 19, 2010
Posts: 142

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.
chaitanya karthikk
Ranch Hand

Joined: Sep 15, 2009
Posts: 800

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::


Love all, trust a few, do wrong to none.
harry ganguly
Greenhorn

Joined: Dec 14, 2010
Posts: 13
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
Greenhorn

Joined: Dec 14, 2010
Posts: 13
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..
Wim Vanni
Ranch Hand

Joined: Apr 06, 2011
Posts: 96

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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Writing in existing excel(.xls) file
 
Similar Threads
POI apache Reading Excel (strange case)
How tochange the values in an existing Excel file by using POI?
Writing in existing excel(.xls) file
Copying a sheet from an excel file to another excel file using Apache POI
Read a .xls,.xlsx file format using XSSF