Java Oracle 导入到 Excel 2013-08-22 #Excel #Java #Oracle Oracle数据库导入到Excel文件 这里的概况是讲Oracle的数据导入到Excel文件的一个公共类(运用的是POI,并且只支持较老的Excel版本) 附件:ExportExcelInst.java import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; 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; import com.fund.etrading.eccapp.dto.ExportExcelDto; /** * 导出EXCEL公共类 * @author Karl.luo at 2013-08-19 * */ public class ExportExcelInst { protected static final Log log = LogFactory.getLog(ExportExcelInst.class.getName()); /* * Excel文件后缀名 */ private static final String EXCEL_POSTFIX = ".xls"; /* * 所查询sequence id */ private String seq_id; /* * 传值map数据 */ private Map map = new HashMap(); /* * procedure 名称 */ private String pro_name; /***********************************DB config*****************************************/ private String classString="oracle.jdbc.driver.OracleDriver"; private String username="ec1016"; private String password="password"; private String url="java:oracle:thin:@192.168.1.171:1521:htfdbweb"; private Connection conn=null; private PreparedStatement ps = null; private CallableStatement cStmt = null; private ResultSet rs = null; /****************************************************************************/ public Connection getConnection() { try { Class.forName(classString); conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } return conn; } /** * 拿SEQ_REPORT_ID 最新的下一个ID NEXTVAL */ public void getSeq(){ conn = getConnection(); String sql = ""; try { sql = "SELECT SEQ_REPORT_ID.NEXTVAL FROM DUAL"; ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while(rs.next()){ seq_id = rs.getString(1); } } catch (Exception e) { e.printStackTrace(); } finally { try{ freeConnection(conn, ps, rs); }catch(java.sql.SQLException ex){ ex.printStackTrace(); } } } /** * 删除table数据 * @return */ public String delReportDateData(){ String result = "9999"; String sql = null; try { sql = "DELETE FROM REPORT_DATE WHERE REPORT_ID = " + seq_id; conn = getConnection(); ps = conn.prepareStatement(sql); ps.execute(); result = "0000"; } catch (Exception e) { e.printStackTrace(); } finally { try{ freeConnection(conn, ps, rs); }catch(java.sql.SQLException ex){ ex.printStackTrace(); } } return result; } private void freeConnection(Connection conn,PreparedStatement pStmt, ResultSet rs ) throws SQLException { try { if(rs != null){ rs.close(); rs = null; } if(pStmt != null){ pStmt.close(); pStmt = null; } if((conn != null) && (!conn.isClosed())){ conn.close(); conn = null; } } catch (java.sql.SQLException ex) { log.error("Exception : ",ex); throw ex; } } /** * 判断指定文件的路径是否是Excel文件 * @param filePath 文件的路径 * @return 是否是Excel文件 * true:是Excel文件 * false:不是Excel文件 */ public boolean isExcel(String filePath){ //获取文件后缀名 String postfix = getPostfix(filePath); if(!postfix.equals(ExportExcelInst.EXCEL_POSTFIX)){ return false; } return true; } /** * 返回一个文件的后缀 如.txt,.doc * * @param filename * @return */ public static String getPostfix(String filename) { int pos = filename.lastIndexOf('.'); if (pos == -1) return ""; return filename.substring(pos); } /** * 传入文件地址,copy到相应的新Excel文件 * @param filePath * @return */ public String createExcel(String filePath){ String newTotalPath = null; String cpre = "1"; //判断是否为Excel文件 if(!isExcel(filePath)){ System.out.println("文件必须是Excel文件,必须是xls格式的文件"); cpre = "0"; return ""; } //如果是,copy一个新的Excel文件,并用新的名字:fileName+yyyyMMdd if("1".equals(cpre)){ // 获取文件的存放文件夹 // 得到最后一个“/”位置 int filePos = filePath.lastIndexOf("/"); String newFilePath = filePath.substring(0, filePos + 1); // 拿到时间yyyyMMdd SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式 String dfDate = df.format(new Date()); // 新的excel 文件名 int fileNamePosBg = filePath.lastIndexOf("/"); int fileNamePosEnd = filePath.lastIndexOf("."); String excelName = filePath.substring(fileNamePosBg + 1,fileNamePosEnd); newTotalPath = newFilePath + excelName + dfDate + ".xls"; //判断文件是否已存在 if(new File(newTotalPath).exists()){ System.out.println("文件已存在"); return newTotalPath; } FileInputStream fis; FileOutputStream fos; try { fis = new FileInputStream(new File(filePath)); fos = new FileOutputStream(new File(newTotalPath)); int bytesRead; byte[] buf = new byte[4 * 1024]; // 4K while ((bytesRead = fis.read(buf)) != -1) { fos.write(buf, 0, bytesRead); } fos.flush(); fos.close(); fis.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } //返回新的文件地址 return newTotalPath; } /** * 查询table数据结果 * @return */ public List getTableData(){ List list = new ArrayList(); String sql = null; try { sql = "SELECT REPORT_ID, X_NUMBER, Y_NUMBER, VALUE_DATE FROM REPORT_DATE WHERE REPORT_ID = " + Integer.parseInt(seq_id); System.out.println(sql); conn = getConnection(); ps = conn.prepareStatement(sql); rs = ps.executeQuery(sql); ExportExcelDto dto; while(rs.next()){ dto = new ExportExcelDto(); dto.setExportId(rs.getInt("REPORT_ID")); dto.setX(rs.getInt("X_NUMBER")); dto.setY(rs.getInt("Y_NUMBER")); dto.setValueDate(rs.getString("VALUE_DATE")); list.add(dto); } return list; } catch (Exception ex) { log.error("(EC-Exception)异常:", ex); } finally { try{ freeConnection(conn, cStmt, rs); }catch(java.sql.SQLException ex){ ex.printStackTrace(); } } return null; } /** * 把list数据导入到excel文件 * @param path * @param list * @return */ public String exportToExcel(String path, List list){ POIFSFileSystem fs; HSSFWorkbook wb = null; HSSFSheet sheet = null; FileOutputStream fos = null; HSSFRow row = null; HSSFCell cell = null; String result = "9999"; try { fs = new POIFSFileSystem(new FileInputStream(path)); wb = new HSSFWorkbook(fs); sheet = wb.getSheetAt(0); for (int i = 0; i < list.size(); i++) { ExportExcelDto dto = (ExportExcelDto)list.get(i); row = sheet.createRow((short)dto.getY()); cell = row.createCell((short)dto.getX()); cell.setCellValue(dto.getValueDate()); } fos = new FileOutputStream(path); wb.write(fos); fos.close(); // 导出excel文件成功后,删除数据库数据 delReportDateData(); result = "0000"; } catch (Exception e) { e.printStackTrace(); } return result; } /** * 将相关数据插入表report_date作导出记录 * @return */ public String insertIntoTable(){ getSeq(); try { conn = getConnection(); String procedure = "{call " + pro_name + "(?,?,?,?,?)}"; cStmt = conn.prepareCall(procedure); log.debug("存储过程:" + procedure); log.debug(" opid :" + (String) map.get("opid")); log.debug(" reportname :" + (String) map.get("reportname")); log.debug(" reportid :" + (String) map.get("reportid")); cStmt.setString(1, (String) map.get("opid")); cStmt.setString(2, (String) map.get("reportname")); cStmt.setString(3, seq_id); cStmt.registerOutParameter(4, Types.VARCHAR); cStmt.registerOutParameter(5, Types.VARCHAR); cStmt.execute(); String errCod = cStmt.getString(4); String errMsg = cStmt.getString(5); log.debug("errCod: " + errCod); log.debug("errMsg: " + errMsg); return errCod + "," + errMsg; } catch (Exception ex) { log.error("(EC-Exception)异常:", ex); } finally { try{ freeConnection(conn, cStmt, rs); }catch(java.sql.SQLException ex){ ex.printStackTrace(); } } return null; } public Map getMap() { return map; } public void setMap(Map map) { this.map = map; } public String getPro_name() { return pro_name; } public void setPro_name(String pro_name) { this.pro_name = pro_name; } public static void main(String[] args) { String proName = "REOPRT_DSPAY_EXPORT"; ExportExcelInst x = new ExportExcelInst(); Map maps = new HashMap(); maps.put("opid", "9999"); maps.put("reportname", "demo"); x.setMap(maps); x.setPro_name(proName); String result = x.insertIntoTable(); if("0000".equals(result.split(",")[0])){ String filePath = x.createExcel("D:/HTF_space/newECCJSP2.0/WEB-INF/config/Excel/demo.xls"); System.out.println("新的文件地址:" + filePath); List list = x.getTableData(); String res = x.exportToExcel(filePath, list); System.out.print(res); } } } 骆林佳 Published under (CC) BY-NC-SA