import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement; i
mport jxl.*;
public class ImportExcel {
public static void main(String[] args) {
File importExcel = new File("D:\\test\\test.xls");
try {
// 数据库连接 Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/datebaseTest?characterEncoding=UTF-8", "root", "");
PreparedStatement prep = conn .prepareStatement("insert into importe (id,name) values (?,?)");
Workbook workBook = Workbook.getWorkbook(importExcel);
Sheet[] sheet = workBook.getSheets();
int sheet_i_num = 0; String id = "";
String name = "";
if (sheet != null && sheet.length > 0) {
for (int sheetNum = 0; sheetNum < sheet.length; sheetNum++) {
sheet_i_num = sheet[sheetNum].getRows();
for (int rowNum = 1; rowNum < sheet_i_num; rowNum++) {
Cell[] cells = sheet[sheetNum].getRow(rowNum);
id = cells[0].getContents(); name = cells[1].getContents();
prep.setInt(1, Integer.parseInt(id)); prep.setString(2, name);
prep.executeUpdate(); System.out.println(id + "--------" + name);
}
}
}
workBook.close(); prep.close(); conn.close();
} catch (Exception e) {
System.out.println(e.getMessage()); } finally { } } }
在mysql中建立数据表
CREATE TABLE importe ( Id int(11) NOT NULL auto_increment, name varchar(50) default NULL, ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
______________________________________________________________________
向数据库中插入时间,将Excel中时间的格式dd/MM/yyyy插入数据库中,我认为这需要格式转换为yyyy-MM-dd格式。
对于Mysql 建立表格 create table test( id int(10), dataFrom date);
在Excel中,建立表格,保存为2003的Excel。
Id Data
1 25/11/2009
2 26/11/2009
3 27/11/2009
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date; import jxl.*;
/* * 直接使用JDBC将Excel插入Mysql数据,插入date的类型 * */
public class ImportExcel3 {
public static void main(String[] args) {
File importExcel = new File("D:\\test\\test2.xls"); try { /
/ 数据库连接 Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/dme?characterEncoding=UTF-8", "root", "");
PreparedStatement prep = conn .prepareStatement("insert into testDate (id,dataFrom) values (?,?)");
Workbook workBook = Workbook.getWorkbook(importExcel);
Sheet[] sheet = workBook.getSheets();
int sheet_i_num = 0;
String id = "";
String dataFrom = "";
if (sheet != null && sheet.length > 0)
{ Date date = null;
for (int sheetNum = 0; sheetNum < sheet.length; sheetNum++){
java.text.DateFormat df = new java.text.SimpleDateFormat("dd/MM/yyyy");
java.text.DateFormat df2 = new java.text.SimpleDateFormat("yyyy-MM-dd");
sheet_i_num = sheet[sheetNum].getRows();
for (int rowNum = 1;
rowNum < sheet_i_num; rowNum++){
Cell[] cells = sheet[sheetNum].getRow(rowNum);
id = cells[0].getContents();
dataFrom = cells[1].getContents();
date=df.parse(dataFrom);
dataFrom=df2.format(date);
prep.setInt(1, Integer.parseInt(id));
prep.setString(2, dataFrom);
prep.executeUpdate();
System.out.println(id + "--------" + dataFrom);
} } }
workBook.close(); prep.close(); conn.close();
} catch (Exception e) { System.out.println(e.getMessage()); } finally { } } }