easy excel parsing API

I am looking for an easy and simple excel parsing API.

Here is what I intend to do in my excel file.




my logic and pseudo code
String date = read second row , second column (B2) // its a date in excel
String location=read third row , second column (B3) // its a location


loop row 6 to end of  the  last record row
 {
  String text1 = row[i].get("col1");
  String text2 = row[i].get("col1");
  String text3 = row[i].get("col1");
   .........................
  String text39 = row[i].get("col39");
  i=i+1;
 }

Open in new window



Please suggest me  the easiest workaround.

However I am worried how to   detect the end of records row ?
cofactorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
The easiest thing of all would be to save it as CSV and just parse it that way
0
cofactorAuthor Commented:
I can not save that in CSV.   end users has a excel format and they are uploading data in excel (xlsx)format .
0
CEHJCommented:
OK, then you're going to have to use something like Apache POI
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

cofactorAuthor Commented:
Thanks.
0
cofactorAuthor Commented:
I am using Apache POI.   Two doubts where I need some help


Q1.  How to know the number of rows with data  and number of  last column  in any row with data  in the excel ?

Q2. I want to start parsing from row 6 till last row with data.   Can we specify a row number in a loop ?



here is my current code.
Workbook workbook; 
		File file = new File("C:/data.xlsx");
		workbook = new XSSFWorkbook(new FileInputStream(file));
		XSSFSheet worksheet = (XSSFSheet) workbook.getSheetAt(0);
		System.out.println("worksheet="+worksheet);
		
		
		for (Row row : worksheet) {
			 System.out.println("row start");
		      for (Cell cell : row) {
		    	  System.out.println("cell="+cell);
		      }
		      System.out.println("row end");
		    }

Open in new window

0
cofactorAuthor Commented:
>>>>https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#getLastRowNum()

This is giving me 495 rows in my excel.

But I only have 9 rows with data ....rest are blank.

How do I get 9 rows ?
0
CEHJCommented:
I'm not sure how it's meant to be done but of course you could just iterate until you hit the first empty row
0
cofactorAuthor Commented:
>>>>I'm not sure how it's meant to be done but of course you could just iterate until you hit the first empty >>row

how do you detect a first empty row ?  Do we need to loop over cells again in that row  to check  if any cell has data or not.
0
CEHJCommented:
Yes
0
cofactorAuthor Commented:
>>>Yes

This is very much nasty way of doing it .  Is it  Apache POI  API  hurdle only.

Is there any other API ..which  handles  this stuff internally .
0
CEHJCommented:
Well i have some sympathy. They could have given you a Utils class that can determine an empty row. See

http://stackoverflow.com/questions/12217047/how-to-determine-empty-row
0
cofactorAuthor Commented:
Thanks CHEJ,

will give that a try ....thats good.

However ...this is making some trouble

for (XSSFRow row : worksheet.getRow(7)) {

Open in new window


error: Type mismatch: cannot convert from element type Cell to XSSFRow

Whats the issue in this code ?
I  need rows to iterate .....I am stuck here.
0
CEHJCommented:
for (XSSFRow row : worksheet.getRow(7)) {

Open in new window

What are you attempting there? That returns ONE row, so there's no loop applicable
0
cofactorAuthor Commented:
>>>What are you attempting there? That returns ONE row, so there's no loop applicable

You are right.  I went a wrong way.

my intention was to form a loop  after row 7  and onwards.

like this ..

for (row = 7 , row < last-row;  row ++)
{
//process every row's cell data
}


What is the proper way to do this ?  for (row = 7 , row < last-row;  row ++)  using  XSSFRow  ?
0
CEHJCommented:
Try something like

int lastRowNum = worksheet.getLastRowNum();
for(int r = 7;r <=lastRowNum;r++) {
   XSSFRow currentRow = worksheet.getRow(r);
   // Do it
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
Can you post a sample of your excel spreadsheet and what you want to accomplish?
0
awking00Commented:
Something like the following may help -
Iterator rows = worksheet.rowIterator();
while (row.hasNext()) {
 Row row = (Row) rows.next();
 int rownum = row.getRowNum();
 //start at row 7 and go to last row
 if (rownum >= 6) {  for (int i = 0; i <= row.getLastCellNum(); i++) {
   //start at first cell and go to last cell in row
   Cell cell = row.getCell(i);
    <do something with each cell in row as needed>
  }
 }
}
0
cofactorAuthor Commented:
Excellent
0
CEHJCommented:
:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.