Win a copy of Cross-Platform Desktop Applications: Using Node, Electron, and NW.js this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Passing objects to Excel cells  RSS feed

 
Eric Graham
Greenhorn
Posts: 5
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a program that reads in an Excel (.xls) and stores and parses it out in an ArrayList from a FileInputStream. All that works fine. But now I want to pass each "element" to a method to write it to a New .xls file.
Pseudocode: for each element/object in the ArrayList, pass it to OutputFile cell. - Meaning InputFile cell/column 0,0 pass it to cell/column 0,0 to OutputFile...cell/column 0,1...etc



I can pass the List to the "write" class/method I am using to parse.. (to console currently) but I can not:
1. write to console single cell by single cell,
2. pass from the FileInputStream to the parsing method as a single object.

I am using org.apache.poi.hssf library (first time using) and not sure if .jxl or javaexcelapi is better...of if using an ArrayList to store the FileInputStream is a bad idea..not sure why it would be other than a better iterating way to get each element out to pass to my parser for outputting the individual cells. - Just looking for some ideas to be able to pass in singel cell values into the cell.setCellValue() that are not hard coded.
Thanks to all
 
Tony Docherty
Bartender
Posts: 3264
81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
An excel spreadsheet has 2 dimensions but an ArrayList has only 1 dimension so how are you storing the spreadsheet in the ArrayList?
ie is it an ArrayList of ArrayLists, are you storing it by concatenated rows or by concatenated columns etc? And if it is concatenated data what format is it in?
 
Eric Graham
Greenhorn
Posts: 5
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tony, good point. Guess I was in a rush and did not think of the "ArrayList" being one dimensional. So a multi dimensional array should be suffice. (to get the column/row). Then iterate through the array and pull out the needed elements. - But with no experience with Java and Excel, using the array as described, you could pass in the element into the cell.setCellValue()? Not sure right off hand if it would take such value as an arguement.
 
Paul Clapham
Sheriff
Posts: 22374
42
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you don't have any experience with Excel (really? you never used Excel?), you should really take half an hour or so to get some. You aren't going to be able to write programs which output Excel documents unless you understand Excel documents properly. Either that, or you have somebody who does understand them and is going to write detailed specs for you to program from.

Anyway I will tell you that you can put a number or a string into a cell. Or other single things like dates, I think. But you can't put a list of things into a cell.
 
Tony Docherty
Bartender
Posts: 3264
81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's a while since I used HSSF but from memory you have to pass in an object of the appropriate type for type the cell is set to. ie if it expects a boolean then you must pass in a Boolean, if it expects a string then you must pass in a String.
 
Paul Clapham
Sheriff
Posts: 22374
42
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, that's right, I just looked up the docs and there are "setCellType" and "setCellValue" methods.
 
Eric Graham
Greenhorn
Posts: 5
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul, I have writen some very simple Excel sheets before. But never had to combine Java code and Excel before. I have managed to read in the .xls file, but never used Poi or jxl or any of those. I was asked by a co-worker that is on a client project, that it would be a great tool to have a Java program that queries a DB, and prints the results (in a certain format) to a external Excel file for documentation for other client resources. So I not have a ton of time to study all I would like to study and completely understand. But due to the lack of time, I have to try and get this up and running ASAP (and I'll figure all the ends and outs later as it were).
So I just didn't know how these libraries like Poi and Jxl and such use Cell cell, Row row and how they except data, and data types. I konw you can pass hard coded "String like this", but passing objects, elements, things like that, I do not have previous knowledge and wanted some heads-up before waisting a lot of time I do not have. That's the basics on my end. Thanks for you comment(s). Any info is greatly appreciated
 
Tony Docherty
Bartender
Posts: 3264
81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I konw you can pass hard coded "String like this"

That is passing a String object in ie

is the same as:
 
Paul Clapham
Sheriff
Posts: 22374
42
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, yeah, I assumed you already knew enough about Java programming to know there would be no difference between a string literal and a string variable when you pass it as a method parameter. So that made your question look like you were trying to pass some kind of a list object and put it into a cell in the spreadsheet.
 
Eric Graham
Greenhorn
Posts: 5
Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Exactly. Which to my understanding, String string = "some string I just wrote"; is "hard coded". Little off track of my original topic of passing in a value (from .xls that is input, stored in a ?, parsed out in single values. As in: Cell 0,0 = header one, Cell 0,1 = header two..... ((and parse out meaning, print to console to verify, then pass the value if correct to a external .xls file. Just did not know the "best" process due to never working with Java AND Excel and especial writing to .xls files.
So, input a Excel file (check) store it in a multi dimensional array(?), pass it to a parsing method that pulls it element by element(?) This is what I was getting at. - Thanks for all the imput and help guys!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!