java把Excel文件数据导入数据库详解编程语言

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; import org.apache.poi.poifs.filesystem.POIFSFileSystem; public class ReadxlXLSToDB { // 定义总列数 private int columnNum; public int getColumnNum() { return columnNum; public void setColumnNum(int columnNum) { this.columnNum = columnNum; private static Connection conn = null; private static Statement stmt = null; static String dbUrl = "jdbc:mysql://localhost:3306/test?user=root password=blue useUnicode=true characterEncoding=utf8"; private final static String driver = "com.mysql.jdbc.Driver"; private static boolean connectionDB() { try { Class.forName(driver); conn = DriverManager.getConnection(dbUrl); stmt = conn.createStatement(); } catch (ClassNotFoundException cnfex) { System.err.println("加载数据库驱动失败！"); cnfex.printStackTrace(); return false; } catch (SQLException sqle) { System.err.println("无法连接数据库！"); sqle.printStackTrace(); return false; } catch (Exception e) { System.err.println("错误"); return false; return true; public void readSheet() { POIFSFileSystem fs = null; HSSFWorkbook wb = null; String sql = "", sql1 = "", sql2 = ""; try { fs = new POIFSFileSystem(new FileInputStream("d://1.xls")); wb = new HSSFWorkbook(fs); } catch (IOException e) { e.printStackTrace(); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; String name = ""; int rowNum, cellNum; int i, j; // 获取总行数 rowNum = sheet.getLastRowNum(); for (i = 0; i = rowNum; i++) { row = sheet.getRow(i); cellNum = row.getLastCellNum(); for (j = 0; j cellNum; j++) { cell = row.getCell((short) j); name = cell.getStringCellValue(); sql1 = sql1 + "num" + (j + 1) + ","; sql2 = sql2 + "" + name + ","; sql = "insert into xls (" + sql1.subSequence(0, sql1.lastIndexOf(",")) + ") values (" + sql2.substring(0, sql2.lastIndexOf(",")) + ")"; System.out.println(sql); try { stmt.executeUpdate(sql); } catch (SQLException e) { e.printStackTrace(); System.err.println("在插入数据时第" + (i + 1) + "失败！"); sql1 = ""; sql2 = ""; public void readOut() { connectionDB(); String sql = "select * from xls"; try { ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { for (int i = 1; i = columnNum; i++) System.out.print(rs.getString(i) + "/t"); System.out.println(); } catch (SQLException e) { System.err.println("无法查询！"); e.printStackTrace(); public void deleteDB() { connectionDB(); String sql = "drop table xls"; try { stmt.executeUpdate(sql); } catch (SQLException e) { System.err.println("无法删除数据表！"); e.printStackTrace(); public void creatTable(int columnNum) { int i; String sql = "", sql1 = ""; for (i = 1; i = columnNum; i++) sql1 = sql1 + "`" + "num" + i + "` varchar(50),"; sql = "create table xls(`id` int(11) NOT NULL auto_increment," + sql1 + " PRIMARY KEY (`id`))ENGINE=MyISAM DEFAULT CHARSET=utf8"; try { stmt.executeUpdate(sql); System.out.println(sql); } catch (SQLException e) { System.err.println("无法创建数据表！"); e.printStackTrace(); public static void main(String args[]) { ReadxlXLSToDB db = new ReadxlXLSToDB(); db.setColumnNum(5); if (ReadxlXLSToDB.connectionDB()) { db.creatTable(db.getColumnNum()); db.readSheet(); } else { System.out.println("不好意思，连接不成功！你失败了！！！"); db.readOut(); }

原创文章，作者：Maggie-Hunter，如若转载，请注明出处：https://blog.ytso.com/10778.html

cjavamysql