JAVA將執行sql結果匯入excel

標籤：jxl   java   excel   sql   匯出   

Java實現將查詢的sql結果集匯入excel,用到jxl.jar包可在http://download.csdn.net/detail/qq8618/8304057 下載

public String queryResultToExcel(String sql,String filename,OutputStream os) {Connection conn = null;Statement sm = null;ResultSet rs = null;try {conn = getConnection();sm = conn.createStatement();rs = sm.executeQuery(sql);ResultSetMetaData rsmd = rs.getMetaData();WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel檔案WritableSheet sheet = wwb.createSheet(filename, 10); // 建立一個工作表// 設定儲存格的文字格式WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);WritableCellFormat wcf = new WritableCellFormat(wf);wcf.setVerticalAlignment(VerticalAlignment.CENTRE);wcf.setAlignment(Alignment.LEFT);// 格式化資料--NUMBER類型NumberFormat numberFormat = new NumberFormat("###0.0#######");  WritableCellFormat cellFormatNumber = new WritableCellFormat(numberFormat); cellFormatNumber.setVerticalAlignment(VerticalAlignment.CENTRE);cellFormatNumber.setAlignment(Alignment.RIGHT);NumberFormat numberFormat2 = new NumberFormat("###0");  WritableCellFormat cellFormatNumber2 = new WritableCellFormat(numberFormat2);  cellFormatNumber2.setVerticalAlignment(VerticalAlignment.CENTRE);cellFormatNumber2.setAlignment(Alignment.RIGHT);// 格式化資料--DATE類型DateFormat dateFormat=new DateFormat("yyyy-MM-dd");WritableCellFormat cellFormatDate = new WritableCellFormat(dateFormat);  cellFormatDate.setVerticalAlignment(VerticalAlignment.CENTRE);cellFormatDate.setAlignment(Alignment.CENTRE);// 格式化資料--文本            WritableCellFormat  cellTextFormat = new WritableCellFormat(NumberFormats.TEXT);            cellTextFormat.setAlignment(Alignment.CENTRE);            cellTextFormat.setVerticalAlignment(VerticalAlignment.CENTRE);            // 表體資料boolean flag = true;int r = 1;int c = 0;int columns = rsmd.getColumnCount();//緩衝最大標題寬度Vector<Integer> colWidth = new Vector<Integer>();for(int i = 1; i <= columns; i++){colWidth.add(0);}while (rs.next()) {for (int i = 1; i <= columns; i++) {//添加表頭資料if(flag){String key = rsmd.getColumnName(i).toLowerCase();sheet.setColumnView(c, key.getBytes("GBK").length + 4);sheet.addCell(new Label(c, 0, key,wcf));colWidth.set(i-1, key.getBytes("GBK").length); //緩衝每列第一行資料的寬度}//設定列寬--如果下一列的資料比前一列寬，則儲存最大寬度if(rs.getString(i) != null){if(colWidth.get(i-1)<rs.getString(i).length()){colWidth.set(i-1, rs.getString(i).length());}sheet.setColumnView(c, colWidth.get(i-1) + 4); //設定寬度}//判斷資料類型if(rsmd.getColumnTypeName(i).equalsIgnoreCase("NUMBER")){if(rs.getString(i)!=null){if (rs.getString(i).indexOf(".")==-1) {sheet.addCell(new Number(c, r, rs.getDouble(i),cellFormatNumber2));}else{sheet.addCell(new Number(c, r, rs.getDouble(i),cellFormatNumber));}}else{sheet.addCell(new Number(c, r, 0,cellFormatNumber2));}}else if(rs.getString(i)!=null && rsmd.getColumnTypeName(i).equalsIgnoreCase("DATE")){sheet.addCell(new DateTime(c, r,rs.getDate(i),cellFormatDate));}else {sheet.addCell(new Label(c, r, rs.getString(i),cellTextFormat));}//列數c++;}flag = false;r++;c = 0;}wwb.write();wwb.close();return r+"_"+c;} catch (SQLException e) {e.printStackTrace();return e.getLocalizedMessage();} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();return e.getLocalizedMessage();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();return e.getLocalizedMessage();} catch (WriteException e) {// TODO Auto-generated catch blocke.printStackTrace();return e.getLocalizedMessage();} finally {try {rs.close();sm.close();conn.close();} catch (SQLException e) {}}}

JAVA將執行sql結果匯入excel