Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Writing in existing excel(.xls) file

 
harry ganguly
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 96
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
From the POI quick guide:


Reading and Rewriting Workbooks


Lines 8 and 9 might be important.

Cheers
Wim
 
harry ganguly
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 96
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 42967
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Overwriting the existing file and updating the existing file are the same thing. You seem to see a distinction where none exists.
 
harry ganguly
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@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
Posts: 147
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...
 
Campbell Ritchie
Sheriff
Posts: 48909
58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Too difficult a question for "beginning". Moving thread.
 
Wim Vanni
Ranch Hand
Posts: 96
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Jan: FileOuputStream will create a new wb....which is not my reqmnt.

 
Jan Hoppmann
Ranch Hand
Posts: 147
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 806
Java MySQL Database Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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::
 
harry ganguly
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 96
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic