reading the records from a .xlsx file and storing those records in database table

Here is my requirement, I want to read the records from a .xlsx file and store that records in database table. I tried like this

public class StoreFile {
    public int storeFileRecords(String saveFile,ServletContext application){
        Connection con=null;
        Statement st=null;
        Properties props=null;
        String bytesToString=null;
        try {
            props=new Properties();
            props.load(application.getResourceAsStream("databasedetails.properties"));
            String url=props.getProperty("url");
            String userName=props.getProperty("userName");
            String password=props.getProperty("password");
            String driverClassName=props.getProperty("driverClassName");

                Class.forName(driverClassName);
                con = DriverManager.getConnection(url,userName,password);
                st=con.createStatement();
                FileInputStream fstream = new FileInputStream(saveFile);
                DataInputStream din = new DataInputStream(fstream);
                BufferedReader br = new BufferedReader(new InputStreamReader(din));
                String strLine;
                ArrayList<String> list=new ArrayList<String>();
                while ((strLine = br.readLine()) != null){
                    if(saveFile.endsWith(".xlsx")){
                        byte[] b=strLine.getBytes();
                        System.out.println("b: "+b);
                        bytesToString=new String(b);
                        System.out.println("bytesToString: "+bytesToString);
                        list.add(strLine);
                    }else{
                        strLine=strLine.toString();
                        System.out.println("strLine: "+strLine);
                        list.add(strLine);
                    }
                }
                Iterator<String> itr=null;
                System.out.println("list: "+list);
                int k=0;
                for (itr=list.iterator(); itr.hasNext(); ){
                    String str=itr.next().toString();
                    String [] splitSt=null;
                    if(saveFile.endsWith(".csv"))
                        splitSt =str.split(",");
                    if(saveFile.endsWith(".txt"))
                        splitSt =str.split(" ");
                    if(saveFile.endsWith(".xlsx"))
                        splitSt =str.split(",");
                    String id=splitSt[0];
                    String name=splitSt[1];
                    String age=splitSt[2];
                    System.out.println("id: "+id);
                    System.out.println("name: "+name);
                    System.out.println("age: "+age);
                    String query="insert into student values("+id.trim()+",'"+name.trim()+"',"+age.trim()+")";
                    System.out.println("query: "+query);
                    if(id.equalsIgnoreCase("sid")) continue;                
                    k+=st.executeUpdate(query);
                }               
                return k;
            } catch (ClassNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return 0;
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return 0;
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return 0;
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return 0;
            }
            finally{
                try {
                    st.close();
                    con.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }               
            }
    }   
}

Here the problem is after reading the records line by line the data is not converted to String. And i want to know what is the default seperator of xlsx file. Which seperator we have to use in case of xlsx file. I have used , as a seperator.

June 30, 2011 at 3:29 PM

Try the following code:

import  java.io.*;  
 import java.sql.*;
 import java.text.*;
 import  org.apache.poi.hssf.usermodel.HSSFSheet  ;
 import  org.apache.poi.hssf.usermodel.HSSFWorkbook ; 
 import  org.apache.poi.hssf.usermodel.HSSFRow  ;
 import  org.apache.poi.hssf.usermodel.HSSFCell ; 
 import org.apache.poi.ss.usermodel.*; 
 public class ReadExcelFile{
 public static void main(String[] args)throws Exception{
  short a=0;
  short b=1;
  short c=2;
  short d=3;
  int i=0;
  Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
         Statement st=conn.createStatement();

    String value1=" ", value2=" ",value3 =" ", value4 =" ";
    String filename  ="C:/hello.xlsx"; 
    if(filename != null && !filename.equals("")){
    try{
    FileInputStream fs =new FileInputStream(filename);
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    for(int k = 0; k < wb.getNumberOfSheets(); k++){
    int j=i+1;
    HSSFSheet sheet = wb.getSheetAt(k);
    int rows  = sheet.getPhysicalNumberOfRows();
    for(int r = 1; r < rows; r++){
    HSSFRow row   = sheet.getRow(r);
    int cells = row.getPhysicalNumberOfCells(); 
    HSSFCell cell1  = row.getCell(a);
      value1 = cell1.getStringCellValue();
    HSSFCell cell2  = row.getCell(b);
      value2 = cell2.getStringCellValue();
    HSSFCell cell3  = row.getCell(c);
      value3 = cell3.getStringCellValue();
    HSSFCell cell4  = row.getCell(d);
      value4 = cell4.getStringCellValue();

    System.out.println(value1+"\t"+value2+"\t"+value3+"\t"+value4);
    st.executeUpdate("insert into student(firstname,lastname,email,address) values('"+value1+"','"+value2+"','"+value3+"','"+value4+"')");
    }
        i++;
     }
    }
    catch(Exception e){
    System.out.println(e);
    }
   }
  }
 }