使用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);//取第一个sheetint 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();}}}