Java -> 把Excel表格中的数据写入数据库与从数据库中读出到本地 （未完善）
写入：没有关闭流，容错并不完善。
private void insertFile(HttpServletRequest request, HttpServletResponse response) throws IOException { String path_member = request.getParameter("path_member"); List list = this.insert("f:/tmp001.xls", "gs_sale_members"); // url // table PrintWriter pw = response.getWriter(); pw.print("{\"result\":" + list + "}"); // 返回插入失败的行数 pw.close(); }
/** * * @param path * 要解析的excel文件路径 * @param dataTable * 要写入到数据库中的表名 * @throws BiffException * @throws IOException */ public List insert(String path, String dataTable) throws IOException, IOException { int a = 0; File file = new File(path); List list = new ArrayList(); HSSFWorkbook rwb = null; // 创建输入流 InputStream is = new FileInputStream(path); rwb = new HSSFWorkbook(is); // 得到工作簿 HSSFSheet sheet = rwb.getSheetAt(0); int rsRows = sheet.getLastRowNum();// 获取总行数 String simNumber = "";// 每个单元格中的数据 DBConn jdbc = new DBConn(); String str = "gs_salemen_seq,gs_salemen_name,gs_salemen_id,gs_salemen_papers_id,gs_salemen_jgid,gs_salemen_type,gs_salemen_status";// 拼接要插入的列 HSSFRow row = sheet.getRow(0); // 获取第一行 int rsColumns = row.getPhysicalNumberOfCells();// 列数 // for (short j = 0; j < rsColumns; j++) { // HSSFCell cell = row.getCell(j); // simNumber = cell.getStringCellValue(); // if (j == rsColumns - 1) { // // 最后一列不用加逗号 // str += simNumber; // } else { // str += simNumber + ","; // } // } for (short i = 0; i < rsRows; i++) { HSSFRow row1 = sheet.getRow(i); // 获取行 // 拼接sql String sql = "insert into " + dataTable + "(" + str + ") values("; for (short j = 0; j < rsColumns; j++) { HSSFCell cell = row1.getCell(j); if (cell != null) { row1.getCell(j).setCellType(cell.CELL_TYPE_STRING); } System.out.println(cell); simNumber = cell.getStringCellValue(); if (j == 0) { sql += base.createId("gs_salemen_seq") + ",'" + simNumber + "',"; } else if (j == 5) { sql += "'" + simNumber + "'"; } else { sql += "'" + simNumber + "',"; } } sql += " )"; a = jdbc.executeUpdate(sql);// 执行sql if (a == 0) { list.add(i); } // 查看拼的sql System.out.println("第" + (i + 1) + "行" + sql); } jdbc.closeStmt(); jdbc.closeConnection(); return list; }
package com.lj.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * Oracle数据库连接 * */ public class DBConn { private Connection conn = null; private Statement stmt = null; private ResultSet rs = null; /** Oracle数据库连接 URL */ private final static String DB_URL = "jdbc:oracle:thin:@192.168.1.7:1521:orcl"; /** Oracle数据库连接驱动 */ private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver"; /** 数据库用户名 */ private final static String DB_USERNAME = "scott"; /** 数据库密码 */ private final static String DB_PASSWORD = "tiger"; /** * 获取数据库连接 * * @return */ public Connection getConnection() { /** 声明Connection连接对象 */ Connection conn = null; try { /** 使用 Class.forName()方法自动创建这个驱动程序的实例且自动调用DriverManager来注册它 */ Class.forName(DB_DRIVER); /** 通过 DriverManager的getConnection()方法获取数据库连接 */ conn = DriverManager .getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); stmt = conn.createStatement(); } catch (Exception ex) { ex.printStackTrace(); } return conn; } /** * 查询数据部分 * * @return ResultSet */ public ResultSet executeQuery(String sqlStr) { if (sqlStr == null || sqlStr.length() == 0) return null; try { this.getConnection(); rs = stmt.executeQuery(sqlStr); return rs; } catch (SQLException ex) { ex.printStackTrace(); return null; } } /** * 更新数据部分 * * @return 更新是否成功 */ public int executeUpdate(String sqlStr) { if (sqlStr == null || sqlStr.length() == 0) return 0; try { this.getConnection(); stmt.executeUpdate(sqlStr); return 1; } catch (SQLException ex) { ex.printStackTrace(); return 0; } finally { try { if (stmt != null) { stmt.close(); } } catch (SQLException e) { e.printStackTrace(); } try { if (conn != null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } } public void closeStmt() { try { if (stmt != null) { stmt.close(); } } catch (Exception e) { e.printStackTrace(); } } /** * 关闭数据库连接 * * @param connect */ public void closeConnection() { try { if (conn != null) { /** 判断当前连接连接对象如果没有被关闭就调用关闭方法 */ if (!conn.isClosed()) { conn.close(); } } } catch (Exception ex) { ex.printStackTrace(); } } }
读出：
private void outExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { // 输出地址 String loc = request.getParameter("loc"); // 查询的表 String table = request.getParameter("table"); File file = new File(loc); if (!file.exists()) { file.createNewFile(); } String sql = "select * from " + table; List<Map<String, Object>> list = base.querySql(sql); write2excel(list, file); }
public static void write2excel(List<Map<String, Object>> list, File file) { HSSFWorkbook excel = new HSSFWorkbook(); HSSFSheet sheet = excel.createSheet("dept"); HSSFRow firstRow = sheet.createRow(0); HSSFCell cells[] = new HSSFCell[3]; String[] titles = new String[] { "deptno", "dname", "loc" }; for (int i = 0; i < 3; i++) { cells[0] = firstRow.createCell(i); cells[0].setCellValue(titles[i]); } for (int i = 0; i < list.size(); i++) { HSSFRow row = sheet.createRow(i + 1); // Computer computer = computers.get(i); HSSFCell cell = row.createCell(0); System.out.println(list.get(i).get("deptno")); cell.setCellValue(list.get(i).get("deptno").toString()); cell = row.createCell(1); cell.setCellValue((String) list.get(i).get("dname")); cell = row.createCell(2); cell.setCellValue((String) list.get(i).get("loc")); cell = row.createCell(3); } OutputStream out = null; try { out = new FileOutputStream(file); excel.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
