• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 639
  • 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
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.

 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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