Java - Reading an Excel

Discussion in 'OT Technology' started by illskills719, Jan 27, 2009.

  1. illskills719

    illskills719 Im lost.

    Joined:
    Sep 21, 2003
    Messages:
    567
    Likes Received:
    0
    Location:
    New York
    My problem: I am trying to import an Excel file into Java
    I need to take a specific column of information (thousands of rows of data) - the data is time. I need to compare these times to my specified times (to see if a time is considered short, medium or long) and if they pass the check keep the information the same in that row, otherwise I need to change another column of information (which contains the label of what that time was (short medium or long -> to the new information based on the test created)

    Example:

    First Column
    Long
    Second Column
    Time ( 3.2 seconds)
    Third Column
    Process

    If the 2nd column is <= 20 seconds, the time is considered short
    if >20 and <= 50, time is considered medium
    if >50, time is considered long..

    New output:
    First Column
    Short
    [rest remains the same]

    If anyone knows how I can take an excel in.. then the best method of how to read this information I would appreciate it..
    I'm guessing the rows would have to be put into an array of some sort anyone know how?

    Thanks
     
  2. CodeX

    CodeX Guest

    Look up the excel file format, then develop a routine to parse the file... Same technique when doing any file IO...
     
  3. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    What kind of moron writes his own Excel parser? Why not invent your own language too, that would really slow things down! Hey, I know... write it in Assembly! Are you going for 'worst advice ever, or just having an off day? :big grin: :rofl::rofl::rofl:

    That open source 'crap code' stuff, by that bunch of crap script kiddies at... the Apache Foundation, sure might save him time:

    http://poi.apache.org/

    http://poi.apache.org/spreadsheet/index.html
     
  4. GOGZILLA

    GOGZILLA Double-Uranium Member

    Joined:
    Jan 16, 2003
    Messages:
    10,760
    Likes Received:
    3
    Location:
    Plantation, FL
    yeah just did a google search for this and theres tons of shit out there dude. definitely do not try to do this yourself
     
  5. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    I gotta ask, why not store the data in a comma-separated text file? That would make it stupid-easy to read.
     
  6. illskills719

    illskills719 Im lost.

    Joined:
    Sep 21, 2003
    Messages:
    567
    Likes Received:
    0
    Location:
    New York
    Sorry I stated the problem wrong.

    I have imported as a CSV

    My problem comes when I am trying to compare the rows and specific columns which run for thousands of rows worth of data, for simplicity I am only taking 10 rows for now.

    Breaking the problem down further (or at least a start) how can I make a 2d array which I declared as a String.. into an Int when I am trying to compare that specific column.

    if you check my example I am having a problem where I made the text red. I just need to know how to make a specific array an integer so I can simply compare.

    example:
    //Running through the rows of data and checking for short queries
    for (int i=0; i<numbers.length; i++)
    {
    for (int j=0; j<numbers.length; j++)
    {
    System.out.println(numbers[j]);

    String [][] x = new String [j];
    x = numbers; //numbers was declared in my main as a 2d String array

    if (x[8] < 1) //problem is here, how can I specifically make the array x[8] an INTEGER to compare to??
    numbers[2] = "Short";
    }
    System.out.println("-----");
    }
     
  7. CodeX

    CodeX Guest

    I like programming, and it wouldn't be difficult.

    Glad to see you hate programming so much that you go out of your way to avoid doing it.
     
  8. CodeX

    CodeX Guest

    You see Peyomp, I see a problem and think, "How can I solve this in code, and potentially learn something along the way?"... you see a problem and think "How can I download someone elses work to do this for me?"
     
  9. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    a lot of people are attached to excel. it is used all over at my work and some of the apps i wrote make and read excel files as well. but as others mentioned i just used an already created excel reader/writer object for php which made it "stupid easy".

    and reading a comma-separated text file isn't just as easy as splitting on commas, what if the fields contain commas? then you have to start making specific rules such as containing fields in quotes and back-slashing out quotes and all that garbage, or you'll get a csv parser class to do it for you. but as long as he's getting a third-party class he might as well get one that works with excel files like he wants.
     
  10. illskills719

    illskills719 Im lost.

    Joined:
    Sep 21, 2003
    Messages:
    567
    Likes Received:
    0
    Location:
    New York
    I have imported as a CSV

    My problem comes when I am trying to compare the rows and specific columns which run for thousands of rows worth of data, for simplicity I am only taking 10 rows for now.

    Breaking the problem down further (or at least a start) how can I make a 2d array which I declared as a String.. into an Int when I am trying to compare that specific column.

    if you check my example I am having a problem where I made the text red. I just need to know how to make a specific array an integer so I can simply compare.

    example:
    //Running through the rows of data and checking for short queries
    for (int i=0; i<numbers.length; i++)
    {
    for (int j=0; j<numbers.length; j++)
    {
    System.out.println(numbers[j]);

    String [][] x = new String [j];
    x = numbers; //numbers was declared in my main as a 2d String array

    if (x[8] < 1) //problem is here, how can I specifically make the array x[8] an INTEGER to compare to??
    numbers[2] = "Short";
    }
    System.out.println("-----");
    }

    Sorry figured that out as well.. I just Integer.parseInt on X array.

    Anyone know how I can spit this CSV out to a new file/??
     
    Last edited: Jan 27, 2009
  11. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    You idiot, it's not about hating programming, it's about getting a solution to market. If we reinvented the wheel every time we need to move something, we'd still be dragging shit on sleds.

    If you feel compelled to re-develop things that are cut-and dried just to have something hard to work on, you need to tackle some bigger projects.

    (this coming from the guy who wrote his own XML parser, granted, but they didn't have one for VB6. :hs:)
     
  12. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    Maybe that's because Peyomp does this shit for a living. It's different when you have deadlines to meet.
     
  13. CodeX

    CodeX Guest

    Oh I don't?

    It's different in the embedded world. Can't just add libraries left and right with no regard to resource consumption. I just finished writing my own jpeg compression algorithm to run on-board our instrument to facilitate saving images from our scope probe (which reads ntsc signal from a magnifying probe and displays it on the instruments screen interleaved at 30hz). Ended up using less code AND RAM than any other solution I could find, and can run on the fly in between video frames allowing output of sequential frames.
     
  14. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    very true. i don't think my boss would be very pleased to hear some project i was working on was delayed because i wanted to make an excel reader to prove to myself that i could.
     
  15. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0

    I don't know if Java has a premade function for this purpose, so I'm going to suggest writing your own. It will parse each character in the string, calculate the integer value of that character, and add it to the integer value of all the other characters already parsed.

    function StringToInt(string){
    StringToInt = 0

    for x = length(string) to 1 step -1
    StringToInt = (StringToInt * 10) + ( Asc(mid(string,x,1)) - Asc('0') )
    next x

    return StringToInt
    }

    Call that any time you need to get the value of a string that you know contains only digits.
     
  16. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    hey, thanks for more irrelevant but obvious info.
     
  17. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Even when I do it for fun, its not about... reinventing the wheel. Its about building a better mousetrap, and that never involves duplicating apache projects.
     
  18. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    So dig through the libraries and pull out stuff you don't need, but don't write the functions from scratch when they already exist. I wrote a lot of my own code for my process scheduler program, but I still based a lot of my code on code other people wrote, so I didn't have to figure out how to implement everything all over again -- I just took their code and reshaped it to do what I needed. Saved me weeks of reading and learning "the hard way", when all I really needed was an example.

    OP: Scroll up for my suggestion.
     
  19. illskills719

    illskills719 Im lost.

    Joined:
    Sep 21, 2003
    Messages:
    567
    Likes Received:
    0
    Location:
    New York
    Ahh guys chill out I need help.

    Anyone know how to output my information into a new csv file? thanks
     
  20. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    Well you could just print it out... to STDOUT even, and redirect output to a file. There's probably a lib but its probably overkill?
     
  21. illskills719

    illskills719 Im lost.

    Joined:
    Sep 21, 2003
    Messages:
    567
    Likes Received:
    0
    Location:
    New York
    I'm not a programmer I used to program years ago but I have forgotten everything.. a
     
  22. Peyomp

    Peyomp New Member

    Joined:
    Jan 11, 2002
    Messages:
    14,017
    Likes Received:
    0
    I believe it goes "System.out.println" ?
     
  23. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    Java doesn't have the FSTREAM library? In C++, you just include that file and then declare a variable "ifstream fin" or "ofstream fout", and then you use those variables exactly the same as cin/cout.
     
  24. zanyspy_dude

    zanyspy_dude King of teh n00bz

    Joined:
    Aug 29, 2002
    Messages:
    4,473
    Likes Received:
    0
    Location:
    Indianapolis, IN
    I like doing shit like

    String buffer = "";
    String delim = ",";

    String line = reader.readlin();
    while(line != null){
    //Do your work on line
    workOnLine();
    buffer += line + delim;
    line = reader.readline();
    }

    then just write the buffer when your done and you're good.
     
  25. illskills719

    illskills719 Im lost.

    Joined:
    Sep 21, 2003
    Messages:
    567
    Likes Received:
    0
    Location:
    New York
    what do you mean workOnLine()?
     

Share This Page