• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

to read uploaded Excel Sheet ROWS in java

Hi
i wish to extract some information from a uploaded excel sheet.I can upload the file and also read the columns.And my process will work only if there are no blanks in excel boxes.
So i plan to read from a predefined row(as the file which is being uploaded will be in the format I have to the clients who just enter the information and upload)

But issue is i am not able to read the rows from the initial top rows.I wish to point to a specific row from where i will extract my required data.
I have also given my  java code below.
please correct with any suggestions if required or provide any other sample information to do this

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.test.Exceldata.StudentDataBean;


public class ExcelSheetReader {
 Connection con=null;
 Statement st=null;
 ArrayList lst=new ArrayList();
    private void readExcelFile(String fileName) {
        
        List cellDataList = new ArrayList();
        try {
	            FileInputStream fileInputStream = new FileInputStream(fileName);
	            POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);          
	            HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
	            HSSFSheet hssfSheet = workBook.getSheetAt(0);
	   	        Iterator rowIterator = hssfSheet.rowIterator();
	            List cellTempList = new ArrayList();			   	   
			   /*
			actualy i want to except  more than two rows from ExcelSheet & start iterator from next position.
            how can i except before iterating the rowIterator ...
			   */
			while (rowIterator.hasNext()) { 
            	      HSSFRow hssfRow = (HSSFRow) rowIterator.next();
					      //i have excepted the column from excelsheet by using  getcell(values)
                        HSSFCell  password = hssfRow.getCell((short)3);
		                HSSFCell  ENGLISH = hssfRow.getCell((short)4);
		                HSSFCell  LANGUAGE = hssfRow.getCell((short)5);
		                HSSFCell  MATHEMATICS = hssfRow.getCell((short)6);
		                HSSFCell  SOCIAL = hssfRow.getCell((short)7);
	              System.out.println(hssfRow.getFirstCellNum());
	                     String pass=password.toString();
	                     String eng=ENGLISH.toString();
						 String IIlang=LANGUAGE.toString();
						 String maths=MATHEMATICS.toString();
						 String social=SOCIAL.toString();
	            //System.out.println(pass+"\t.."+eng+"\t.."+IIlang+"\t.."+maths+"\t"+social);
                 try{
	                    Class.forName("com.mysql.jdbc.Driver").newInstance();
	                    Connection con = DriverManager.getConnection("jdbc:mysql://192.168.1.109:3306/test", "nnn", "mmm");
	                    PreparedStatement pst=con.prepareCall("call insert_student_marks(?,?,?,?,?)");
                        pst.setString(1,pass);
                        pst.setString(2,eng);
                        pst.setString(3,IIlang);
                        pst.setString(4,maths);
                        pst.setString(5,social);                                       
                      int i= pst.executeUpdate();
                       System.out.println(i);
                      // System.out.println("Data is inserted");
                        pst.close();
                        con.close();
                   }
                        catch(Exception e){}
                 cellDataList.add(cellTempList);
              }
        } catch (Exception e) {
            e.printStackTrace();
        }
       
        printToConsole(cellDataList);
    }

    
    private void printToConsole(List cellDataList) {
    	
        for (int i = 0; i < cellDataList.size(); i++) {
              List cellTempList = (List) cellDataList.get(i);
            for (int j = 0; j < cellTempList.size(); j++) {
		            	StudentDataBean bean=new StudentDataBean();
		            	HSSFCell hssfCell = (HSSFCell) cellTempList.get(j);
		                String stringCellValue = hssfCell.toString();
		                System.out.print(stringCellValue + "\t");
		                lst.add(stringCellValue);
           		}
            	System.out.println();
        	}
    }
	public static void main(String[] args) {
        String fileName = "c://test//read.xls"; 
        new ExcelSheetReader().readExcelFile(fileName);
    }
}

Open in new window



thanks
0
darien_software
Asked:
darien_software
  • 4
  • 3
1 Solution
 
darien_softwareAuthor Commented:
actualy i want to except  more than two rows from ExcelSheet & start iterator from next position. how can i do this?
0
 
CEHJCommented:
Fairly certain i've answered that in another q of yours: you start at whatever row index you want
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
darien_softwareAuthor Commented:
where to keep this method, row iterator iterates a row value using sheet then how ?

                HSSFSheet hssfSheet = workBook.getSheetAt(0);

                  HSSFRow rowIndex=   hssfSheet.getRow(3);

                   Iterator rowIterator = hssfSheet.rowIterator();
                
                
                 List cellTempList = new ArrayList();
                
            while (rowIterator.hasNext()) {
    //getting cell data using rows
}
0
 
CEHJCommented:
Don't use Iterator. Just getRow
0
 
darien_softwareAuthor Commented:
using this getLastRowNum()  method, this method is reading upto the last Row but last record is not inserted into a database....
Any Suggestions ....
0
 
CEHJCommented:
:)

but last record is not inserted into a database....
Any Suggestions ....
Could be any number of reasons. Post code in new question please
0
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now