Tuesday 7 February 2017

How to import the Excel file into Oracle Table Using Java


Please find the below code to import the Excel File into the Oracle Table



/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author Aravind
 */
public class App {
    public static void main(String args[]){
        try { 
                Class.forName("oracle.jdbc.driver.OracleDriver");  
                Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","system","system");
                System.out.println("");
                con.setAutoCommit(false);
                PreparedStatement pstm = null;
                String destDir="C:\\Users\\Desktop\\test.xlsx";
                System.out.println("destDir==> "+destDir); 
                XSSFWorkbook workBook = new XSSFWorkbook(destDir); 
                XSSFSheet sheet = workBook.getSheetAt(0); 
                int totalRows = sheet.getPhysicalNumberOfRows(); 
                System.out.println("total no of rows >>>>"+totalRows); 
                Row row;
                for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                    row = (Row) sheet.getRow(i);
                    String name = row.getCell(0).getStringCellValue();
                    String add = row.getCell(1).getStringCellValue();
                    int  contact = (int) row.getCell(2).getNumericCellValue();
                    String email = row.getCell(3).getStringCellValue();
                    System.out.println(name+":My EMAIL address is "+email);
                    String sql = "INSERT INTO TEST_EMPLOYEE (NAME, ADDRESS, CONTACT, EMAIL) VALUES('" + name + "','" + add + "'," + contact + ",'" + email + "')";
                    pstm = (PreparedStatement) con.prepareStatement(sql);
                    pstm.execute();
                    System.out.println("Import rows " + i);
                }
                con.commit();
                pstm.close();
                con.close();
                System.out.println("Success import excel to my table");
        }
        catch(Exception e){
        }
    }
}
    

8 comments:

  1. Hi Aravind ,While i am running this code i am getting following error can you please tell me what are the poi files required for this please send me if any changes required k.eswar456@gmail.com

    destDir==> H:\data.xlsx
    Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
    at upload.App.main(App.java:24)
    Caused by: java.lang.ClassNotFoundException: org.apache.xmlbeans.XmlException
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    ... 1 more

    ReplyDelete
    Replies
    1. Hi Eswar,

      You have not added the sufficient jars that are required that's what you are getting this exception.For the error that you have mentioned you have to add the xmlbeans-2.6.0.jar

      Delete
    2. Thank you soo much Aravind.I want to do same task in JSP or Servlet,can you suggest me how to change change this code for servlet or Jsp.

      Delete
  2. Hi Aravind ,While i am running this code i am getting following error can you please tell me what are the poi files required for this please send me if any changes required anitha.payyavula1997@gmail.com

    ReplyDelete
    Replies
    1. Hi Could you please update what is the error that you are facing.So that I can help you accordingly

      Delete
  3. I want to say thanks to you. Most importantly I have bookmarked your site for future updates.
    Class 2 Digital Signature in Delhi

    ReplyDelete
  4. Pretty great post. I simply stumbled upon your weblog and wanted to say that I have really loved browsing your blog posts.
    Best Digital Signature Provider in Delhi

    ReplyDelete
  5. The blog is absolutely fantastic. Lots of great information and inspiration, both of which we all need. Thanks for such a continuous great postings.
    Class 2 Digital Signature in Delhi

    ReplyDelete