使用JDBC+POI把Excel中的数据导出到MySQL

　　POI是Apache的一套读MS文档的API，用它还是可以比较方便的读取Office文档的。目前支持Word，Excel，PowerPoint生成的文档，还有Visio和Publisher的。

　　http://poi.apache.org/download.html

　　具体的用法可以查阅文档里面您的quickguide，我给出我自己的范例，从xls文件把数据导出到mysql。

　　这里面我总是假定excel在第一个sheet并且第一行是字段名，能够自动从第一行读取字段名建立一个表然后导入数据。

package JDBCPractice; import java.io.*; import java.sql.*; import org.apache.poi.hssf.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; // 导入hssf来处理xls文件 import org.apache.poi.poifs.filesystem.POIFSFileSystem; // 使用poifs来读文件更加的轻松，当然也可以不用 public class main { /** * @param args */ public static void main(String[] args) { String addr = "/home/ulysess/Developer/T_user.XLS"; HSSFWorkbook wb = null; HSSFSheet contents = null; try { POIFSFileSystem exlf = new POIFSFileSystem(new FileInputStream(addr)); wb = new HSSFWorkbook(exlf); } catch (FileNotFoundException e) { System.out.println("文件不存在，请检查路径"); e.printStackTrace(); return; } catch (IOException e) { System.out.println("读取文件时发生IO错误"); e.printStackTrace(); return; } contents = wb.getSheetAt(0); //取第一个sheet int minColIdx, maxColIdx, maxRowIdx; maxRowIdx = contents.getLastRowNum(); HSSFRow xlrow = contents.getRow(0); //-----------------------建立MySQL连接------------------------- try { Class.forName("com.mysql.jdbc.Driver"); //建立一个mysql的driver的实例，并将其注册到DriversManager } catch (ClassNotFoundException e) { System.out.println("Unable load Driver"); e.printStackTrace(); } String name = "root"; String password = "moratorium"; String url = "jdbc:mysql://localhost/USERDATAS"; try { Connection con = DriverManager.getConnection(url, name, password); //建立连接 Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); try { /*stmt.execute("create table ORGDATAS( " + "USERID VARCHAR(24) NOT NULL PRIMARY KEY," + " USERNAME VARCHAR(24), SEX TINYINT, " + "PASSWORD VARCHAR(64), USERTYPE TINYINT, " + "FREEAUTHEN TINYINT, CERTIFICATETYPE TINYINT, " + "CERTIFICATENO VARCHAR(24), EDUCATION TINYINT, " + "POSTCODE VARCHAR(10), ADDRESS VARCHAR(128), " + "PHONENO VARCHAR(24), BIRTHDAY DATE, EMAIL VARCHAR(64) );");*/ //建表 HSSFCell cel, refcell; HSSFRow ferr = contents.getRow(1); String ctbsql = "create table TARGETTABLE ("; for(int i = xlrow.getFirstCellNum(); i < xlrow.getLastCellNum(); i++) { cel = xlrow.getCell(i); refcell = ferr.getCell(i); if(refcell == null) { ctbsql = ctbsql.concat(cel.getStringCellValue() + " VARCHAR(64)"); } else { switch(refcell.getCellType()) { case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_STRING: ctbsql = ctbsql.concat(cel.getStringCellValue() + " VARCHAR(64)"); break; case Cell.CELL_TYPE_NUMERIC: ctbsql = ctbsql.concat(cel.getStringCellValue() + " INT"); break; case Cell.CELL_TYPE_BOOLEAN: ctbsql = ctbsql.concat(cel.getStringCellValue() + " TINYINT"); break; default: ctbsql = ctbsql.concat(cel.getStringCellValue() + " VARCHAR(64)"); } } if(i < xlrow.getLastCellNum()-1) { if(i == 0 ) { ctbsql = ctbsql.concat(" NOT NULL PRIMARY KEY"); } ctbsql = ctbsql.concat(", "); }else { ctbsql = ctbsql.concat(");"); } } stmt.execute(ctbsql); //跟据前两行的内容来建表 } catch(SQLException e) { } ResultSet rs = stmt.executeQuery("select * from TARGETTABLE"); minColIdx = xlrow.getFirstCellNum(); maxColIdx = xlrow.getLastCellNum(); //设定每列的最小最大索引 int cnt =0 ; boolean infirstrow = true; //for each式遍历整个表 for (Row row : contents) { if(infirstrow) { infirstrow = false; continue; } rs.moveToInsertRow(); System.out.println("insert " + cnt++); for (Cell cell : row) { if(cell == null) { continue; } switch(cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: case Cell.CELL_TYPE_STRING: rs.updateString(cell.getColumnIndex() + 1, cell.getStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: rs.updateInt(cell.getColumnIndex() + 1, (int) cell.getNumericCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: rs.updateShort(cell.getColumnIndex() + 1, (short) cell.getNumericCellValue()); break; default: rs.updateString(cell.getColumnIndex() + 1, cell.getStringCellValue()); } } rs.insertRow(); } System.out.println("--------------------------"); } catch (SQLException e) { e.printStackTrace(); System.out.println("/n--- SQLException caught ---/n"); while (e != null) { System.out.println("Message: " + e.getMessage ()); System.out.println("SQLState: " + e.getSQLState ()); System.out.println("ErrorCode: " + e.getErrorCode ()); e = e.getNextException(); e.printStackTrace(); System.out.println(""); } } catch (IllegalStateException ie) { ie.printStackTrace(); } } }