Java tutorial
/* * 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 tournoi; /** * * @author vincent */ import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; import java.text.ParseException; import java.text.SimpleDateFormat; 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; public class ExcelToBDD { private Connection con; private DBConnection db; private PreparedStatement pst; //private final String filePath = "/Users/vincent/Dropbox/M2IFA/Java/tournoi/tournoi/test.xls"; public boolean insertJoueur(String filePath) { boolean flag = true; db = new DBConnection(); con = db.getConnection(); try { // ??excel FileInputStream fin = new FileInputStream(filePath); // HSSFWorkbook workbook = new HSSFWorkbook(fin);// HSSFSheet sheet = workbook.getSheetAt(0);// HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; HSSFCell cell3 = null; HSSFCell cell4 = null; HSSFCell cell5 = null; HSSFCell cell6 = null; HSSFCell cell7 = null; int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_joueur = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); String nom = cell2.getRichStringCellValue().toString(); cell3 = row.getCell(2); String prenom = cell3.getRichStringCellValue().toString(); cell4 = row.getCell(3); int national_point = (int) cell4.getNumericCellValue(); cell5 = row.getCell(4); String categorie = cell5.getRichStringCellValue().toString(); cell6 = row.getCell(5); String federation = cell6.getRichStringCellValue().toString(); cell7 = row.getCell(6); String ligne = cell7.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO joueurs VALUES('" + id_joueur + "','" + nom + "','" + prenom + "','" + national_point + "','" + categorie + "','" + federation + "','" + ligne + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; } public boolean insertArbitre(String filePath) { boolean flag = true; db = new DBConnection(); con = db.getConnection(); try { // ??excel FileInputStream fin = new FileInputStream(filePath); // HSSFWorkbook workbook = new HSSFWorkbook(fin); //Get the workbook instance for XLS file HSSFSheet sheet = workbook.getSheetAt(1);//Get first sheet from the workbook System.out.println(workbook.getSheetName(1)); HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; HSSFCell cell3 = null; int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_arbitre = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); String nom = cell2.getRichStringCellValue().toString(); cell3 = row.getCell(2); String prenom = cell3.getRichStringCellValue().toString(); // cell4 = row.getCell(3); // int national_point = (int) cell4.getNumericCellValue(); // cell5 = row.getCell(4); // String categorie = cell5.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO arbitres VALUES('" + id_arbitre + "','" + nom + "','" + prenom + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; } public boolean insertTable(String filePath) { boolean flag = true; db = new DBConnection(); con = db.getConnection(); try { // ??excel FileInputStream fin = new FileInputStream(filePath); // HSSFWorkbook workbook = new HSSFWorkbook(fin); //Get the workbook instance for XLS file HSSFSheet sheet = workbook.getSheetAt(2);//Get first sheet from the workbook System.out.println(workbook.getSheetName(2)); HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; //HSSFCell cell3 = null; int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_table = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); int id_salle = (int) cell2.getNumericCellValue(); // cell3 = row.getCell(2); // String prenom = cell3.getRichStringCellValue().toString(); // cell4 = row.getCell(3); // int national_point = (int) cell4.getNumericCellValue(); // cell5 = row.getCell(4); // String categorie = cell5.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO tables VALUES('" + id_table + "','" + id_salle + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; } //pas bon encore public boolean insertJoueur(String filePath, String table) { boolean flag = true; db = new DBConnection(); con = db.getConnection(); try { // ??excel FileInputStream fin = new FileInputStream(filePath); HSSFWorkbook workbook = new HSSFWorkbook(fin);// HSSFSheet sheet = workbook.getSheetAt(0);// HSSFRow row = null;// excel HSSFCell cell = null; HSSFCell cell2 = null; HSSFCell cell3 = null; HSSFCell cell4 = null; HSSFCell cell5 = null; HSSFCell cell6 = null; HSSFCell cell7 = null; int totalCol = sheet.getDefaultColumnWidth();// obtenir le nbr de column for (int i = 1; i <= totalCol; i++) { } int totalRow = sheet.getLastRowNum();// excel? System.out.println(totalRow); // ? for (int i = 1; i <= totalRow; i++) { row = sheet.getRow(i); cell = row.getCell(0); int id_joueur = (int) cell.getNumericCellValue(); cell2 = row.getCell(1); String nom = cell2.getRichStringCellValue().toString(); cell3 = row.getCell(2); String prenom = cell3.getRichStringCellValue().toString(); cell4 = row.getCell(3); int national_point = (int) cell4.getNumericCellValue(); cell5 = row.getCell(4); String categorie = cell5.getRichStringCellValue().toString(); cell6 = row.getCell(5); String federation = cell6.getRichStringCellValue().toString(); cell7 = row.getCell(6); String ligne = cell7.getRichStringCellValue().toString(); //String sql = "INSERT INTO text1(ID,BM,AQ,CQ,DQ) VALUES (?,?,?,?,?)"; // " String sql = "INSERT INTO joueurs VALUES('" + id_joueur + "','" + nom + "','" + prenom + "','" + national_point + "','" + categorie + "','" + federation + "','" + ligne + "')"; pst = con.prepareStatement(sql); pst.execute(); System.out.println("Import rows " + i); } //con.commit(); fin.close(); System.out.println("Success import excel to mysql table"); } catch (FileNotFoundException e) { flag = false; System.out.println("MYSQL ERROR:" + e.getMessage()); } catch (IOException ex) { flag = false; System.out.println("MYSQL ERROR:" + ex.getMessage()); } catch (SQLException exx) { flag = false; System.out.println("MYSQL ERROR:" + exx.getMessage()); } finally { try { pst.close(); con.close(); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } return flag; } public void deleteTable(String filePath, String table) { try { db = new DBConnection(); con = db.getConnection(); Statement st = con.createStatement(); String ma_requete = "TRUNCATE " + table + ";"; st.executeUpdate(ma_requete); } catch (SQLException e) { System.out.println("MYSQL ERROR:" + e.getMessage()); } } public static void main(String args[]) { // String filePath = "/Users/vincent/Dropbox/M2IFA/Java/tournoi/tournoi/test.xls"; // String table = "joueurs"; // ExcelToBDD toDB = new ExcelToBDD(); // toDB.deleteTable(filePath, table); // toDB.insertJoueur(filePath); } }