• Post Reply Bookmark Topic Watch Topic
  • New Topic

Accessing excel cells in Java  RSS feed

 
Nikhil Shanbhag
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello All,

I have to write a script in Java, where there are 3 columns on an excel sheet.

The first row of the second column, of the excel sheet, is searched in an .inx file and if/when a match is found, the first row of the third column replaces it in the .inx file.

To achieve this, I plan on accessing the cells of first row, second column, in the excel in an orderly format (using 2 for loops) and search this in the .inx file.

When/if found, come back to access the first row, third column, in the excel to replace it.

As I might have made it clear, 1 row, 2 column to be replaced by 1 row, 3 column
Similarly, 2 row, 2 column to be replaced by 2 row, 3 column
3 row, 2 column to be replaced by 3 row, 3 column .... and so on

What would we the possible logic behind this?

Also, what is the format in Java to identify if that file (excel sheet or .inx file) is present in the given location? So far, I have used if loops to identify if the given file exists, but it is not very flexible as I have hard coded it, but need to find a way for it to search the entire computer or even network.

Also, when I try to access the excel cell contents, I only manage to pull the formula behind the cell rather than the data it holds (in this case, the last modified data). Any ideas what I might be doing wrong?

Apologies for this long drawn request!

Thank you for any help in kind anticipation.

Best,
Nikhil
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nikhil Shanbhag wrote:I have to write a script in Java, where there are 3 columns on an excel sheet.

Well, first: you don't write scripts in Java, you write programs - or are you writing JavaScript? The two are NOT the same, despite the similar name.

Second: Are you using Apache POI? Because if so, this question might do better in one of our more targeted forums.

Moving to 'Java in General' for now, as this question is too advanced for the Beginners forum.

Winston
 
Nikhil Shanbhag
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dear Winston,

When I say Script, I mean, I don't intend to create a GUI. So I need only a program, with no GUI but in Java and not JavaScript as there is no server involved.

Secondly, apologies for my ignorance, as I'm not aware of Apache POI. However, I don't see any servers being used or coded for.

Also, I was not sure it was an advanced question as I was only wondering how to access excel data rather than the formula behind the cells.

If you feel it's better in the Java in General, then I'm sure you know best.

Thanks in advance.

Nikhil
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nikhil Shanbhag wrote:Also, I was not sure it was an advanced question as I was only wondering how to access excel data rather than the formula behind the cells.

Well, Excel is a proprietary file format, closely guarded by Microsoft, so I doubt if you'll have much success just trying to "read" an Excel file - although I honestly don't know because I've never tried it.

Apache POI is a popular library for reading and manipulating data in Excel spreadsheets. The website is here.

Secondly, apologies for my ignorance, as I'm not aware of Apache POI. However, I don't see any servers being used or coded for.

No need to apologise. However, POI is quite advanced, so I'm quite surprised you're tackling it if you're still a beginner.

Is this a class assignment? If so, it might be useful for us to see the wording of the assignment exactly as it was given to you.

Winston
 
Richard Tookey
Bartender
Posts: 1166
17
Java Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I prefer http://jexcelapi.sourceforge.net to POI but am not religious about it.
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Richard Tookey wrote:I prefer http://jexcelapi.sourceforge.net to POI but am not religious about it.

Hmm. Thanks for that; never heard of it before. Bookmarked.

Winston.
 
Mark Beardsley
Ranch Hand
Posts: 32
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When we still worked with Excel, I started out using JExcelapi, but it does have two problems. The first is that it is no longer actively maintained by Andy Khan, it's original author, and the second is that it only supports the binary (BIFF8) file format. POI on the other hand is the target of continually development, it does support the more recent OOXML (zipped xml) file format and there is an active user list that you can post questions to.

POI works in a very hierarchical fashion; you open the file, and from it get a sheet. With the sheet in hand, you get a row from it and then a cell or cells from the row. Once you have the cell, it is possible to check it's type and then call the appropriate method to retrieve it's contents as a number, date, string, etc. The best place to start in my opinion is with the quick guide; http://poi.apache.org/spreadsheet/quick-guide.html

One last point I should mention is that POI contains a unifying module that allows you to code once and forget the file's format. It is called the ss usermodel and you will see it often in the examples with lines like this;

Workbook workbook = WorkbookFactory.create(new File("C:/temp/your file.xlsx"));
 
Winston Gutkowski
Bartender
Posts: 10575
66
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mark Beardsley wrote:One last point I should mention is that POI contains a unifying module that allows you to code once and forget the file's format. It is called the ss usermodel and you will see it often in the examples with lines like this;

Thanks Mark. A very nice and comprehensive post that earns you a cow (don't worry; that's good).

Winston
 
Gaurav Varma
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I recently wrote a program similar to what you are writing. I used POI.
I have to show few graphs so I couldn't add new rows/columns. Instead, I maintained constants for row/column mappings (A true hardcoding) and then editing values in those to reflect in my graph.
Let me know if any part of my could help you.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!