Excel数据导入Mysql案例

注意这里必须要导入jxl这个jar包
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class test {

//public static void main(String[] args) throws Exception{
public void getExcelDate() throws Exception{
String path = "D:\\test.xls";// 你的excel文件
try {
InputStream is = new FileInputStream(path);
Workbook wb = Workbook.getWorkbook(is);
Sheet st = wb.getSheet(0);
System.out.println("该表含有" + st.getRows() + "行");
System.out.println("该表含有" + st.getColumns() + "列");
OutputStream os = new FileOutputStream("D:\\temp.xls");
WritableWorkbook wwk = Workbook.createWorkbook(os);
WritableSheet ws = wwk.createSheet("教师信息", 0);
boolean frist_o = true, frist_t = true, frist_s = true;
int sum = 0;
for (int j = 1; j < st.getRows(); j++) {
List list = new ArrayList();
for (int i = 1; i < st.getColumns(); i++) {

if (frist_o) {
System.out.println("开始读数据...");
frist_o = false;
}

Cell cell = st.getCell(i, j);
String contents = cell.getContents().trim();

list.add(contents);

if (frist_t) {
System.out.println("开始写数据...");
frist_t = false;
}

Label le = new Label(i - 1, j - 1, contents);
ws.addCell(le);
}
if (list.size() == 6) {
if (frist_s) {
System.out.println("开始把数据写入数据库...");
}
int ser = 0;
Iterator iterator = list.iterator();
Connection conn = conn();
System.out.println(conn);
Statement stmt = conn.createStatement();
System.out.println(stmt);
System.out.println("开始拼接SQL语句...");
StringBuffer vec = new StringBuffer();
vec.append("insert into zj_teacher (id,realname,cardid,unit,address,lastupdate,state) values (");
vec.append("" + null);
vec.append(",'");
vec.append(iterator.next().toString());
vec.append("','");
vec.append(iterator.next().toString());
vec.append("','");
vec.append(iterator.next().toString());
vec.append("','");
vec.append(iterator.next().toString());
vec.append("','");
vec.append(iterator.next().toString());
Date date = new Date(System.currentTimeMillis());
System.out.println(date);
SimpleDateFormat fomat = new SimpleDateFormat("yyyy:MM:dd:hh:ss:mm");
vec.append(fomat.format(date));
System.out.println(fomat.format(date));
vec.append("',");
vec.append(Integer.parseInt(iterator.next().toString()));
vec.append(")");
System.out.println(vec.length());
String sql = vec.toString();
System.out.println(sql);
    int result = stmt.executeUpdate(sql);
sum++;
}
}
System.out.println("写入数据库完毕！");
System.out.println("一共有" + sum + "条数据写入到服务器！");
System.out.println("操作成功！");

}  catch (Exception e) {
e.printStackTrace();
}

}

private static Object Date(String string) {
// TODO Auto-generated method stub
return null;
}

public static Connection conn() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url = "jdbc:mysql://localhost/zjrnl?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=utf-8";
// orcl为你的数据库的SID
String user = "root";
String password = "root";
Connection conn;
try {
conn = DriverManager.getConnection(url, user, password);
return conn;

} catch (SQLException e) {
e.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
}
return null;
}

}
注意jar在我的附件有下载

评论

1 楼 dufan168 2010-06-06  

您好
  为什么第一列的数据会取不出来
只能用null代替吗