jxl导不出来_jxl 实现导入导出excel 自用备份

packagecom.jxl.test;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjava.util.Map;importjava.util.Set;public classExceloutAndIn {private static String driver="com.mysql.jdbc.Driver";private static String url="jdbc:mysql://localhost:3306/ebook?useUnicode=true&characterEncoding=utf-8";private static String user = "root";private static String password = "11111";//例如dbName = "student";outPutfileName="\\output.xls"

public static voidexportDbToExl(String dbName, String outPutfileName) {

Connection conn= null;

ResultSet rs= null;

PreparedStatement pstmt= null;

String sql= "select * from " +dbName;try{

Class.forName(driver);

conn=DriverManager.getConnection(url, user, password);

System.out.println("-------连接成功------");

pstmt=conn.prepareStatement(sql);

System.out.println(pstmt);

rs=pstmt.executeQuery();

ResultSetMetaData rm=rs.getMetaData();

List tableContent =DbUtils.rsToList(rs);

JxlUtils.jxlListToExl(tableContent, outPutfileName);

pstmt.close();

rs.close();

conn.close();

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(SQLException e) {

e.printStackTrace();

}

}public static voidimportExlToDb(String dbName, String inPutFileName) {

List list =JxlUtils.jxlExlToList(inPutFileName);

System.out.println(list);

Connection conn= null;

ResultSet rs= null;

PreparedStatement pstmt= null;

String sql= "insert into " + dbName + " ";//INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

List columnslist = new ArrayList();

List valueslist = new ArrayList();//提取表头

if (list.size() >= 1) {

Map map= list.get(0);

Set keySet=map.keySet();for(Object keyName : keySet) {

columnslist.add(keyName.toString());

valueslist.add("?");

System.out.println(keyName);

}

}else{return;

}

String columnsStr= columnslist.toString().substring(1,columnslist.toString().indexOf("]"));

String valuesStr= valueslist.toString().substring(1,valueslist.toString().indexOf("]"));

System.out.println(columnsStr);

System.out.println(valuesStr);//sql = sql + " (" + columnsStr + ") values (" + valuesStr + ")";

sql = sql + " values (" + valuesStr + ")";

System.out.println(sql);//写入数据库

for (int i = 0; i < list.size(); i++) {

Map map=list.get(i);try{

Class.forName(driver);

conn=DriverManager.getConnection(url, user, password);

System.out.println("-------连接成功------");

pstmt=conn.prepareStatement(sql);for (int j = 0; j < columnslist.size(); j++) {

System.out.println(map.get(columnslist.get(j)));

pstmt.setString(j+ 1, map.get(columnslist.get(j)).toString());

}

System.out.println(pstmt);

pstmt.executeUpdate();

pstmt.close();

conn.close();

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(SQLException e) {

e.printStackTrace();

}

}//导入数据库完成

System.out.println("导入数据库完成");

}

}