Checker Help

[th3b3n]

Hi every one, new poster here and new to Java as well.

I'm working on a java program that will reads an excel file extracts the data and prints it to console. I've gotten that part to work in the below code, however I'm starting the next phase of the program where the data extracted needs to be compared to certain values, if the comparison returns true then it prints the data.

This is where I'm stuck. I can't figure out what data type my listCollector 2d array is getting from the hssfCell.toString() command. i thought it was getting string values, but when comparing it to string[] checker, every thing equates to false, even though I know the 1 value in checker will/is inside the listCollector.

private void printToConsole(List cellDataList){
		
		int row = 0;
		int col = 0;
			
		String[] checker = {"PONUMBER"};
				
		for (int x = 0; x < cellDataList.size(); x++){  
			List cellTempList = (List) cellDataList.get(x);
			row = cellDataList.size();
			col = cellTempList.size();
			String[][] listCollector = new String[row][col];
			for (int y = 0; y < cellTempList.size(); y++){
				HSSFCell hssfCell = (HSSFCell) cellTempList.get(y);  
				listCollector[x][y] = hssfCell.toString();
				if(listCollector[0][y] == checker[0]){
				System.out.print(listCollector[x][y] + "\t");
				}
			}  
		}
	}

[stultuske]

well ... toString returns a String method, that's ... basically why the method 's named to'String'.

of course, if some 'funny dude' decided to write a toString method that returns a Double (which I doubt), it can be anything.

I think this:

if(listCollector[0][y] == checker[0]){

needs to be

if(listCollector[x][y] == checker[0]){

though.

what is in cellDataList? your current check goes to check them all against "PONUMBER", what if it contains other values?

also, very important (with capital I), you don't compare the values of (String, or any other) objects by ==, you need to use the .equals() method to check for equality.

[th3b3n]

Ok, i thought I had the right idea about to string.

I used

if(listCollector[0][y] == checker[0]){

because the variables I wish to check; which are strings headings like PoNumber, Vendor ID, Currency, PolicyNumber, etc, are found in the first row of every column. a little print check trial and error gave me this information.

cellDataList is a passed in variable from the reader code I used. I felt breaking it up the reader and writer sections would be better for trouble shooting, I was right too because the reader works flawlessly.

I was using the == operator to do the comparision because its what I would use in C, C# and Mel(AutoDesk Maya purpriatary language based on c) to check if items where equal. as PoNUMBER and PONUMBER.

If you could provide an example of how .equals() method works It would be greatly appriciated.

[stultuske]

well ... since you are trying to compare the value of two String objects, say, String a and b, call the equals method from the a String and pass the b String as a parameter:

a.equals(b)

[th3b3n]

Made the change viewed below.

private void printToConsole(List cellDataList){
		
		int row = 0;
		int col = 0;
			
		String[] checker = {"PONUMBER"};
				
		for (int x = 0; x < cellDataList.size(); x++){  
			List cellTempList = (List) cellDataList.get(x);
			row = cellDataList.size();
			col = cellTempList.size();
			String[][] listCollector = new String[row][col];
			for (int y = 0; y < cellTempList.size(); y++){
				HSSFCell hssfCell = (HSSFCell) cellTempList.get(y);  
				listCollector[x][y] = hssfCell.toString();
		     changed -> if(listCollector[0][y].equals(checker[0])){
				System.out.print(listCollector[x][y] + "\t");
				}
			}  
		}
	}

returns PONUMBER in the conslue, plus 3 exceptions in main. 1 in my printToConsole method, my Reader method and .main.

I think I need to work on the if(.equals) part a bit more. or come up with a new loop. The check worked, but the only line printed was the cell that contained PONUMBER and not its subsiquent data.

[stultuske]

well, you only compare against PONUMBER, which I already mentioned, so it won't return anything else.

I don't think there's anything wrong with your if and equals (except that you should put more values in checker and should replace the 0's by a dynamic index.

[th3b3n]

Eventually there will be more variables in the checker, they will be created by a gui, but to get the core of the code working, i'm using a simple static.

I guess I didn't word it properly. the Fields I want to check are all in the 0.x row of the excel sheet (0.0 is cell A1, 0.1 is b1, 0.2 is c1, etc) where the headings for the data are. If I change the if statement to be dynamic, say y.x, instead of only checking the heading for the columns I want(say 0.2 for C1) it will check the entire column (0.2-> 9.2 or c1-> C10) and thus only return the column heading.

[stultuske]

well, OK, but you can still use a dynamic index for checker, and add some more values in there if you want more values to test, which can still be a simple static.

what errors are you getting? normally the exception message tells what the error is and on what line it occurs. if you post the stack trace here, I'll take a look at it.

[th3b3n]

How do I post the stack trace and what is it? I'll change the tracker to be dynamic shortly, which my have just solved anotehr problem I was having, mainly because it will need a loop.

[stultuske]

the stack trace is what you get in your command prompt (or somewhere in your logs, depending on your exception handling).
it gives information about what exception occurs, and what causes it to occur.

if you'll post your entire code, I'll run it here and check for it here.

[th3b3n]

import java.io.FileNotFoundException;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
//import org.apache.poi.hssf.usermodel.HSSFCellStyle;
//import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
//import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.*;
/**
 * @author 
 *
 */
public class BPSepcExporter {
	//GUI for choosing what file to export, name to save it under and where
	/**interface exporterGUI(){
	
	}*/

	//Main is the call for all other nested functions
	public static void main(String[] args) {
		
              //static file name, will be dynamic once gui is implimented.
		String file2Read = "NotConverted2.xls"; 
		new BPSepcExporter().BPSReader(file2Read);		
	}
	
	public void BPSReader(String fileName){
		//Input control variable- take from GUI options
		List cellDataList = new ArrayList();  
		try {  
  			//Create a new instance for FileInputStream class  
  			FileInputStream fileInputStream = new FileInputStream(fileName);  
   			// Create a new instance for POIFSFileSystem class  
  
  			POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);  
  
  			//Create a new instance for HSSFWorkBook Class  
  
  			HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);  
  			HSSFSheet hssfSheet = workBook.getSheetAt(0);  
   
		 //Iterate the rows and cells of the spreadsheet to get all the datas.  
  			Iterator rowIterator = hssfSheet.rowIterator();  
  				while (rowIterator.hasNext()){  
  					HSSFRow hssfRow = (HSSFRow) rowIterator.next();  
  					Iterator iterator = hssfRow.cellIterator();  
  					List cellTempList = new ArrayList();  
  						while (iterator.hasNext()){  
  							HSSFCell hssfCell = (HSSFCell) iterator.next();  
  							cellTempList.add(hssfCell);
  						}  
  				cellDataList.add(cellTempList);
  				}
  		}  
		catch (Exception e){  
		e.printStackTrace();  
		}
	 printToConsole(cellDataList);
	}

	private void printToConsole(List cellDataList){
		
		int row = 0;
		int col = 0;
			
		String[] checker = {"PONUMBER"};
				
		for (int x = 0; x < cellDataList.size(); x++){  
			List cellTempList = (List) cellDataList.get(x);
			row = cellDataList.size();
			col = cellTempList.size();
			String[][] listCollector = new String[row][col];
			for (int y = 0; y < cellTempList.size(); y++){
				HSSFCell hssfCell = (HSSFCell) cellTempList.get(y);  
				listCollector[x][y] = hssfCell.toString();
				if(listCollector[0][y].equals(checker[0])){
				System.out.print(listCollector[x][y] + "\t");
				}
			}
			
		}
	}  
}

Thats the code, you should know the NotConverted.xls is a excel document that is located in the workspace\pbsConverter project folder. That I cannot provide for you because of its contents.

[stultuske]

can't you provide an Excell file with that structure, but different contents?

[th3b3n]

I think that should do it. had to zip, .xls isn't a supported attachment type for some reason.

[stultuske]

propably because it can contain macro's, so, it could contain 'harmful malware' :)
I'll take a look at it, but I don't have all the packages you're using, so might take a while :)

[th3b3n]

the only non default package i used was POI for the reading of excel documents, google poi and you should have it in a few minutes. but I get off work in 10, so if you do come up with something I won't see it till tomorrow.

[stultuske]

well, it's 22.40 here, so my working hours are .. :D
nja, I know, I'm adding poi now, but have some processes in the background running which are slowing me down a tad.

[stultuske]

I've tested this (added an extra print, so it'll show you what it compares to what)

import java.io.File;
    import java.io.FileInputStream;
    import java.util.ArrayList;
    import java.util.Iterator;
    import java.util.List;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    //import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    //import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    //import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.poifs.filesystem.*;
    /**
     * @author
     *
     */
    public class BPSepcExporter {
    //GUI for choosing what file to export, name to save it under and where
    /**interface exporterGUI(){
     
    }*/
     
    //Main is the call for all other nested functions
    public static void main(String[] args) {
     
    //static file name, will be dynamic once gui is implimented.
    String file2Read = "NotConverted2.xls";
    new BPSepcExporter().BPSReader(file2Read);
    }
     
    public void BPSReader(String fileName){
    //Input control variable- take from GUI options
    List cellDataList = new ArrayList();
    try {
    //Create a new instance for FileInputStream class
    FileInputStream fileInputStream = new FileInputStream(fileName);
    // Create a new instance for POIFSFileSystem class
     
    POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
     
    //Create a new instance for HSSFWorkBook Class
     
    HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
    HSSFSheet hssfSheet = workBook.getSheetAt(0);
     
    //Iterate the rows and cells of the spreadsheet to get all the datas.
    Iterator rowIterator = hssfSheet.rowIterator();
    while (rowIterator.hasNext()){
    HSSFRow hssfRow = (HSSFRow) rowIterator.next();
    Iterator iterator = hssfRow.cellIterator();
    List cellTempList = new ArrayList();
    while (iterator.hasNext()){
    HSSFCell hssfCell = (HSSFCell) iterator.next();
    cellTempList.add(hssfCell);
    }
    cellDataList.add(cellTempList);
    }
    }
    catch (Exception e){
    e.printStackTrace();
    }
    printToConsole(cellDataList);
    }
     
    private void printToConsole(List cellDataList){
     
    int row = 0;
    int col = 0;
     
    String[] checker = {"PONUMBER"};
     
    for (int x = 0; x < cellDataList.size(); x++){
    List cellTempList = (List) cellDataList.get(x);
    row = cellDataList.size();
    col = cellTempList.size();
    String[][] listCollector = new String[row][col];
    for (int y = 0; y < cellTempList.size(); y++){
    HSSFCell hssfCell = (HSSFCell) cellTempList.get(y);
    listCollector[0][y] = hssfCell.toString();
    System.out.println(y + " = listCollector[0][y]: \t" + listCollector[0][y] + "\t <<->>   checker[0]: " + checker[0]);
    if( listCollector[0][y].equals(checker[0])){
    System.out.print(listCollector[0][y] + "\t\n");
    }
    }
     
    }
    }
    }

but I did have to download an extra .jar file that was needed for one of the poi classes, maybe that's your problem?

[th3b3n]

it could be, what .jar did you need to get that I didn't include?

[th3b3n]

With your print statement I noticed that the 2nd for loop was running 27 times when it should only run 26. producing a null comparison in the code. I thought the exception was being caused by this so I changed up the for loops. They went from

x/y < variable.size()

to

x/y <= variable.size()

. This simple change produced a new exception:

Exception in thread "main" java.lang.IndexOutOfBoundsException: Index: 26, Size: 26
at java.util.ArrayList.rangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at BPSepcExporter.printToConsole(BPSepcExporter.java:84)
at BPSepcExporter.BPSReader(BPSepcExporter.java:68)
at BPSepcExporter.main(BPSepcExporter.java:34)
Since the range and get are from Unknown sources I took a look at my line 84 which is:

HSSFCell hssfCell = (HSSFCell) cellTempList.get(y);

Now if I'm correct, the first time the loops run, there are 26 columns, then 23 then 20, so the 2nd loops .size() changes several times. the First loop's .size() stays constant at 9 presently(# of rows in the spread sheet)

[th3b3n]

removing the checker and just having the code print everything from the excel sheet removes any exceptions and runs just fine.

also once the loops run, if I try to see whats in any of address's inside of the listCollector I get null values. This is another issue as I need a 2d array to be dynamic so that it can catch all the data, but a 2d array has to have its size declared.

[stultuske]

the code that I posted above worked perfectly with the excell file you gave me.
it was the commons-codec.jar I used (there are other jars too, that have the class that I needed, but that was the fastest one I got

[stultuske]

all arrays need to have either their size declared or be initially filled with data that determines the size.
if you don't know the size up front, you can always use an ArrayList

as for the nr of times it ran ..
well, I didn't know how many times it should have ran, so I didn't check on that, I just worked out the problems that made the code to fail.

[th3b3n]

I'm going to mark this as solved, but I'm going to scrap the code for now, and try something else else a bit latter. For now I've got the reader working, so I'll work on the gui and necessary filter attributes, and then come back to the writer and checker section.