?
Solved

easy excel parsing API

Posted on 2014-08-05
20
Medium Priority
?
225 Views
Last Modified: 2014-08-23
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 ?
0
Comment
Question by:cofactor
  • 9
  • 9
  • 2
20 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 40240694
The easiest thing of all would be to save it as CSV and just parse it that way
0
 

Author Comment

by:cofactor
ID: 40240724
I can not save that in CSV.   end users has a excel format and they are uploading data in excel (xlsx)format .
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 40240739
OK, then you're going to have to use something like Apache POI
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:cofactor
ID: 40243075
Thanks.
0
 

Author Comment

by:cofactor
ID: 40243144
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 40243158
0
 

Author Comment

by:cofactor
ID: 40243259
>>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 40243276
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
 

Author Comment

by:cofactor
ID: 40243294
>>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 40243325
Yes
0
 

Author Comment

by:cofactor
ID: 40243444
>>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 40243614
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
 

Author Comment

by:cofactor
ID: 40243669
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 40243823
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
 

Author Comment

by:cofactor
ID: 40245496
>>>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
 
LVL 86

Accepted Solution

by:
CEHJ earned 2000 total points
ID: 40245556
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
 
LVL 32

Expert Comment

by:awking00
ID: 40253905
Can you post a sample of your excel spreadsheet and what you want to accomplish?
0
 
LVL 32

Expert Comment

by:awking00
ID: 40254017
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
 

Author Closing Comment

by:cofactor
ID: 40280688
Excellent
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 40280732
:)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses
Course of the Month16 days, 20 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question