package com.demo.day20140228;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
public class DictExcelDemo {
public static void main(String[] args) {
DictExcelDemo ded = new DictExcelDemo();
Connection conn = ded.getConnection();
ded.readExcel_(conn);
}
private Connection getConnection(){
Connection conn = null;
try {
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection("jdbc:sqlite:database.db");
Statement stat = conn.createStatement();
stat.executeUpdate("create table if not exists dictionary(enword varchar(200), cnword varchar(200));");// create 1 Two tables, two columns
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
private void readExcel_(Connection conn) {
try {
Workbook book = Workbook.getWorkbook(new File("English.xls"));
PreparedStatement prep = conn.prepareStatement("insert into dictionary(enword,cnword) values(?,?);");
for (int a = 0; a < 26; a++) {
// For the first 1 Worksheet objects
Sheet sheet = book.getSheet(a);
// Get the first 1 The column first 1 Row cell
// Get the first 1 The column first 1 Row cell
int columnum = sheet.getColumns();// Get the number of columns
int rownum = sheet.getRows();// Get the number of rows
for (int i = 1; i < rownum; i++)// Loop read and write
{// line
String key = "";
String value = "";
for (int j = 0; j < columnum; j++) {// column
Cell cell1 = sheet.getCell(j, i);
String result = cell1.getContents();
if (j == 0) {
key += result;
} else {
value += result;
}
}
// System.out.println(key+"=="+value);
prep.setString(1, key);
prep.setString(2, value);
prep.addBatch();
}
}
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
conn.close();
book.close();
} catch (Exception e) {
System.out.println(e);
}
}
}