自用备份
源码是网上找的具体地址找不到了,做了一些修改。
导入导出工具类
package com.jxl.test; import java.io.File; import java.util.ArrayList; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Set; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class JxlUtils { //导出到excel @SuppressWarnings("rawtypes") public static void jxlListToExl(List<Map> tableContent,String outPutFileName) { WritableWorkbook book = null; try { File os = new File(System.getProperty("user.dir") + outPutFileName); if (!os.exists()) { // 如果指定文件不存在,则新建该文件 os.createNewFile(); } book = Workbook.createWorkbook(os);// 创建一个新的写入工作簿 WritableSheet sheet = book.createSheet("sheet1", 1); List<String> tableHeader = new ArrayList<String>(); if (tableContent.size() >= 1) { Map map = tableContent.get(0); Set keySet = map.keySet(); for (Object keyName : keySet) { tableHeader.add(keyName.toString()); System.out.println(keyName); } }else{ return; } // 第一行写入表头 for (int i = 0; i < tableHeader.size(); i++) { Label lable = new Label(i, 0, tableHeader.get(i)); sheet.addCell(lable); } // 后续行写入数据 for (int i = 0; i < tableContent.size(); i++) { Map map = tableContent.get(i); for (int j = 0; j < tableHeader.size(); j++) { System.out.println(map.get(tableHeader.get(j))); Label lable = new Label(j, i + 1, map.get( tableHeader.get(j)).toString()); sheet.addCell(lable); } } book.write(); System.out.println("工作簿写入数据成功!"); book.close();// 关闭 } catch (Exception e) { e.printStackTrace(); } } //导入到mysql @SuppressWarnings({ "rawtypes", "unchecked" }) public static List<Map> jxlExlToList(String inPutFileName) { Workbook book = null; List<Map> list = null; try { File os = new File(System.getProperty("user.dir") + inPutFileName); if (!os.exists()) { // 如果指定文件不存在,则新建该文件 os.createNewFile(); } book = Workbook.getWorkbook(os);// 创建一个新的写入工作簿 Sheet sheet = book.getSheet(0); int totalRows = sheet.getRows(); int totalColumns = sheet.getColumns(); Cell[] cell = sheet.getRow(0); if(totalColumns<=0){ return null; } //读取第一行作为Map中的key List tableHeaderlist = new ArrayList(); for (int i = 0; i < totalColumns; i++) { tableHeaderlist.add(cell[i].getContents()); } //将每一行存为Map集合,然后存为list list = new ArrayList(); Map rowData = new LinkedHashMap(); for (int i = 1; i < totalRows; i++) { cell = sheet.getRow(i); rowData = new LinkedHashMap(totalColumns); for (int j = 0; j < totalColumns; j++) { rowData.put(tableHeaderlist.get(j), cell[j].getContents()); } list.add(rowData); } System.out.println("工作簿读取数据成功!"); book.close();// 关闭 } catch (Exception e) { e.printStackTrace(); } return list; } }
rs结果集工具类
package com.jxl.test; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.util.ArrayList; import java.util.Collections; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; public class DbUtils { public static List<Map> rsToList(ResultSet rs) throws java.sql.SQLException { if (rs == null) return Collections.EMPTY_LIST; ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息,比如字段数、字段名等 int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数 List<Map> list = new ArrayList(); Map rowData = new LinkedHashMap(); while (rs.next()) { rowData = new LinkedHashMap(columnCount); for (int i = 1; i <= columnCount; i++) { rowData.put(md.getColumnName(i), rs.getObject(i)); } list.add(rowData); } return list; } }
两个方法
package com.jxl.test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Set; public class ExceloutAndIn { 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 void exportDbToExl(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<Map> 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 void importExlToDb(String dbName, String inPutFileName) { List<Map> 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<String> columnslist = new ArrayList<String>(); List<String> valueslist = new ArrayList<String>(); // 提取表头 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("导入数据库完成"); } }
测试
package com.jxl.test; public class Test { public static void main(String[] args) { //例如dbName = "student";outPutfileName="\\output.xls" //ExceloutAndIn.exportDbToExl("dbuser", "\\output3.xls"); ExceloutAndIn.importExlToDb("book", "\\inner.xls"); } }