vector_ever
Mitglied
Hallo,
seit gestern versuchte ich die Attributen von excel zu Datenbank zu importiern
ich benutze POI Bibliothek um die Attributen von Excel zu lesen
Ich habe viel über das gelesen und viel beispiele angeschaut, aber jedes mal bekomme ich Fehler meldung
code Table erzeugen:
von excel zu Database
Fehler Meldung beim Ausführung :
und ich habe bemerkt dass die attributen sind mehr als 5 mal in mein Datenbank gespeichert, nicht nur einmal
excel Datei
ps: die erste lReihewird nicht Database gespeichert, deswegen kann man ignorieren it
Kennt jemand der fehler finden?
seit gestern versuchte ich die Attributen von excel zu Datenbank zu importiern
ich benutze POI Bibliothek um die Attributen von Excel zu lesen
Ich habe viel über das gelesen und viel beispiele angeschaut, aber jedes mal bekomme ich Fehler meldung
code Table erzeugen:
Code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
*
* @author Mirage
*/
public class CreateTable {
public static void main(String args[]) {
String url = "jdbc:mysql://localhost:3306/mkyongdb";
Connection con;
String createTableBooks = "CREATE TABLE movies.filme "
+ "(CD_ID double UNSIGNED NOT NULL,"
+"Albumtitel VARCHAR(45) NOT NULL,"
+"Interpret VARCHAR(25) NOT NULL,"
+ "CREATED_DATE double NOT NULL,"
+ "Track double NOT NULL,"
+ "Titel VARCHAR(255) NOT NULL)";
Statement stmt;
try {
Class.forName("com.mysql.jdbc.Driver");
} catch(java.lang.ClassNotFoundException e){
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url, "root","root");
stmt = con.createStatement();
stmt.executeUpdate(createTableBooks);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException: " +
ex.getMessage());
}
}
}von excel zu Database
Code:
package Mysql;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class test {
//static ArrayList cellArrayLisstHolder = new ArrayList();
public static void main(String[] args) throws Exception{
ArrayList dataHolder = readExcelFile();
saveToDatabase(dataHolder);
}
public static ArrayList readExcelFile(){
ArrayList medium = new ArrayList();
try {
FileInputStream file = new FileInputStream(new File("d:\\Filme.xls"));
//Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(file);
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while(rowIterator.hasNext()) {
Row row = rowIterator.next();
//display from the third row until 5th
if(row.getRowNum() > 0)
{
//For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
ArrayList small = new ArrayList();
while(cellIterator.hasNext()) {
//Getting the cell contents
Cell cell = cellIterator.next();
small.add(cell);
medium.add(small);
}
}
}
}catch (Exception e){e.printStackTrace();
}
return medium;
}
private static void saveToDatabase(ArrayList dataHolder) {
String url = "jdbc:mysql://localhost:3306/movies";
String username = "root";
String password = "root";
Connection con;
String query = "insert into filme values(?,?,?,?,?,?)";
PreparedStatement ps = null;
try {
////////////////////////make connection withthe database ///////////////////////////////
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(url, username, password);
////////////////////////////////// Excute SQL statment: ///////////////////////////////////////
ps = con.prepareStatement(query);
ArrayList cellStoreArrayList = null;
//For inserting into database
for (int i = 0; i < dataHolder.size(); i++) {
cellStoreArrayList = (ArrayList)dataHolder.get(i);
ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());
ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());
ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
ps.setString(4,((HSSFCell)cellStoreArrayList.get(3)).toString());
ps.setString(5,((HSSFCell)cellStoreArrayList.get(4)).toString());
ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).toString());
ps.executeUpdate();
}
ResultSet rs = ps.executeQuery(query);
System.out.println(" Filme :");
System.out.println(" ============== ");
/////////////////////////////handle the results: ///////////////////////////////////
while (rs.next()) {
double s = rs.getDouble("CD_ID");
String f = rs.getString("Albumtitel");
String i = rs.getString("Interpret");
double d = rs.getDouble("CREATED_DATE");
double n = rs.getDouble("Track");
String t = rs.getString("Titel");
System.out.println(s + " " + f + " " + i + " " + d + " " + n + " " + t);
}
ps.close();
con.close();
} catch(Exception ex) {
System.err.print("Exception: ");
System.err.println(ex.getMessage());
}
}
}Fehler Meldung beim Ausführung :
HTML:
Exception: Index: 5, Size: 5excel Datei
HTML:
CD_ID Albumtitel Interpret created Track Titel
--------------------------------------------------------------------------
4711 Not That Kind Anastacia 1999 1 Not That Kind
4710 Not That Kind Anastacia 1999 2 I’m Outta Love
4712 Not That Kind Anastacia 1999 3 Cowboys & Kisses
4722 Wish You Her Pink Floyd 1964 1 Shine On You Crazy Diamond
4713 Freak of Nature Anastacia 1999 1 Paid my DuesKennt jemand der fehler finden?
