Excel数据使用jdbc直接插入Mysql数据库

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 { } } }