JAVA to SQL

package abhijeet;

import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.Locale;

import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.WorkbookSettings; import jxl.read.biff.BiffException;

public class Abhijeet {

public void init(String filePath) {
    File fileXYZ = new File(filePath);
    FileInputStream fs = null;
    try {
        fs = new FileInputStream(fileXYZ);
        contentReading(fs);
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            fs.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

//Returns the Headings used inside the excel sheet
public void getHeadingFromXlsFile(Sheet sheet) {
    int columnCount = sheet.getColumns();
    for (int i = 0; i < columnCount; i++) {
        System.out.println("Coloumn Count : "+sheet.getCell(i, 0).getContents());
    }
}

public void contentReading(InputStream fileInputStream) {
    WorkbookSettings ws = null;
    Workbook workbook = null;
    Sheet s = null;
    Cell rowData[] = null;
    int rowCount = 0;
    int columnCount = 0;
    int totalSheet = 0;

    try {
        ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        workbook = Workbook.getWorkbook(fileInputStream, ws);

        totalSheet = workbook.getNumberOfSheets();
        if(totalSheet > 0) {
            System.out.println("Total Sheet Found:" + totalSheet);
            for(int j=0;j<totalSheet ;j++) {
                System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
            }
        }


        s = workbook.getSheet(0);


        System.out.println("Total Rows inside Sheet:" + s.getRows());
        rowCount = s.getRows();


        System.out.println("Total Column inside Sheet:" + s.getColumns());
        columnCount = s.getColumns();

        ArrayList arrMain = new ArrayList();
        for (int i = 0; i < rowCount; i++) {

            rowData = s.getRow(i);

            if (rowData[0].getContents().length() != 0) { // the first date column must not null
                for (int j = 0; j < columnCount ;j++) 
                {

                    ArrayList<String> arrData=new ArrayList();

                        switch (j) {

                    case 0:
                    {
                        System.out.println("AP Code: "+rowData[j].getContents());
                        arrData.add(rowData[j].getContents());
                    }

                    break;
                    case 1:
                    {
                        System.out.println("AP Name: "+rowData[j].getContents());
                        arrData.add(rowData[j].getContents());
                    }
                    break;
                    case 2:

                    {
                        System.out.println("City: "+rowData[j].getContents());
                        arrData.add(rowData[j].getContents());
                    }
                    break;
                    case 3:

                    {
                        System.out.println("Country: "+rowData[j].getContents());
                        arrData.add(rowData[j].getContents());
                    }





                            }
                        arrMain.add(arrData);
                        }


                }


            {
                workbook.close();       

        }


    }
    }
    catch (IOException e)
    {

        e.printStackTrace();

    }

    catch (BiffException e)
    {

        e.printStackTrace();
    }
}

    public int arrMain() {
    // TODO Auto-generated method stub
    return 0;
}

    public static void main(String[] args) {
    try {
        Abhijeet xlReader = new Abhijeet();
        String fileN = "D:/ProjectExcel/First_Project.xls"; 
        Connection conn =getSimpleConnection();
        Statement stmt = conn.createStatement();
        String InstQuery="Insert into First_Project (Airport_code,Airport_name,City,Country) values (arrData.add(Airport_code),arrData.add(Airport_name),arrData.add(City),arrData.add(Country))";
        xlReader.init(fileN);
        ArrayList arrMain = null;
        stmt.execute(InstQuery);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

private static void ArrayList() { // TODO Auto-generated method stub

static Connection getSimpleConnection() { //See your driver documentation for the proper format of this string : String DBCONNSTRING = "jdbc:mysql://localhot:3306/mysql"; //Provided by your driver documentation. In this case, a MySql driver is used : String DRIVERCLASSNAME = "com.mysql.jdbc.Driver"; String USER_NAME = "navin"; String PASSWORD = "n@v!n";

            Connection conn = null;

         /*   try {
               Class.forName(DRIVER_CLASS_NAME).newInstance();
            }
            catch (Exception ex){
               log("Check classpath. Cannot load db driver: " + DRIVER_CLASS_NAME);
            }

            try {
              conn = (Connection) DriverManager.getConnection(DB_CONN_STRING, USER_NAME, PASSWORD);
            }
            catch (SQLException e)
            {
               log( "Driver loaded, but cannot connect to db: " + DB_CONN_STRING);
            }*/
            return conn;
          }

}

plzzz help mw wid dis...

1: i hav read and Excel file in java and printed its contents

2: Now i want to redirect dat oupput to MYSQL manager 2005 so i have connectivity code wid but i m not getting n e error nor i m getting any output so plzzz kindly help me

View Answers

May 24, 2011 at 5:01 PM

import java.io.*;
import java.sql.*;
import java.util.*;
import jxl.Cell; 
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings; 
import jxl.read.biff.BiffException;

public class ReadExcelFile{
ArrayList<String> list1=new ArrayList<String>();
ArrayList<String> list2=new ArrayList<String>();
ArrayList<String> list3=new ArrayList<String>();
ArrayList<String> list4=new ArrayList<String>();

public void getHeadingFromXlsFile(Sheet sheet) {
    int columnCount = sheet.getColumns();
    for (int i = 0; i < columnCount; i++) {
        System.out.println("Coloumn Count : "+sheet.getCell(i, 0).getContents());
    }
}

public void contentReading() {
    WorkbookSettings ws = null;
    Workbook workbook = null;
    Sheet s = null;
    Cell rowData[] = null;
    int rowCount = 0;
    int columnCount = 0;
    int totalSheet = 0;

    try{
        ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        workbook = Workbook.getWorkbook(new File("C:/data.xls"), ws);

        totalSheet = workbook.getNumberOfSheets();
        if(totalSheet > 0) {
            System.out.println("Total Sheet Found:" + totalSheet);
            for(int j=0;j<totalSheet ;j++) {
            System.out.println("Sheet Name:" + workbook.getSheet(j).getName());
            }
        }
        s = workbook.getSheet(0);
        System.out.println("Total Rows inside Sheet:" + s.getRows());
        rowCount = s.getRows();
        System.out.println("Total Column inside Sheet:" + s.getColumns());
        columnCount = s.getColumns();
        for(int i = 1; i < rowCount; i++){
            rowData = s.getRow(i);
            if (rowData[0].getContents().length() != 0) { 
                for(int j = 0; j < columnCount ;j++){
                 switch (j) {
                    case 0:
                       System.out.println("Name: "+rowData[j].getContents());
                       list1.add(rowData[j].getContents());
                       break;
                    case 1:
                      System.out.println("Address: "+rowData[j].getContents());
                      list2.add(rowData[j].getContents());
                      break;
                    case 2:
                      System.out.println("Contact: "+rowData[j].getContents());
                      list3.add(rowData[j].getContents());
                      break;
                    case 3:
                      System.out.println("Email: "+rowData[j].getContents());
                     list4.add(rowData[j].getContents());
                     break;
                     }
                       }
                }
    }
    workbook.close(); 
    try{
   Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/roseindia", "root", "root");
         Statement st=conn.createStatement();
         for(int i=0;i<list1.size();i++){
             String name=list1.get(i).toString();
             String address=list2.get(i).toString();
             String contact=list3.get(i).toString();
             String email=list4.get(i).toString();
         st.executeUpdate("insert into employee(name,address,contactNo,email) values('"+name+"','"+address+"','"+contact+"','"+email+"')");
         }
        System.out.println("Inserted successfully!");
    }
    catch(Exception e){
    System.out.println(e);
    }
    }
    catch (Exception e){
        e.printStackTrace();
    }
}
    public static void main(String[] args) {
    try {
        ReadExcelFile xlReader = new ReadExcelFile();
        xlReader.contentReading();
            } catch (Exception e) {
        e.printStackTrace();
    }
}
}

JAVA to SQL

Questions by Category