jxl 实现根据sql语句导出excel文件
/** * 导出Excel * @param filePath导出文件（模板）路径 * @param sql导出的SQL语句 * @param startLine起始行 * @param printTitle是否打印标题 * @throws Exception */public void doExport(String filePath , String sql , int startLine , boolean printTitle) throws Exception {Class.forName("oracle.jdbc.driver.OracleDriver");Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:yz", "scott", "tiger"); //获取数据库连接 WritableWorkbook book = null ;WritableSheet sheet = null ; if(new File(filePath).exists()){Workbook wb = Workbook.getWorkbook(new File(filePath)); book = Workbook.createWorkbook(new File(filePath), wb); // 添加一个工作表sheet = book.getSheet(0);}else {book = Workbook.createWorkbook(new File(filePath)); // 第一步sheet = book.createSheet("第一页", 0); // 创建Sheet}PreparedStatement pstm = conn.prepareStatement(sql);ResultSet rs = pstm.executeQuery();//获取数据集ResultSetMetaData rsmd = rs.getMetaData();//获取表头int colCnt = rsmd.getColumnCount();//获取数据集的列数if(printTitle){/** * 定义单元格样式 */WritableFont wf = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK); // 定义格式 字体 下划线 斜体 粗体 颜色WritableCellFormat wcf = new WritableCellFormat(wf); // 单元格定义wcf.setBackground(jxl.format.Colour.BLACK); // 设置单元格的背景颜色wcf.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式sheet.setRowView(startLine, 1000); // 设置行的高度//打印标题头for(int k = 1; k < colCnt + 1; k++){String title = rsmd.getColumnName(k);Label labelTitle = new Label( k-1 , startLine , title , wcf);sheet.setColumnView(k-1 , 30); // 设置列的宽度sheet.addCell(labelTitle);}startLine++ ;}//打印sql语句查出来的数据while (rs.next()) {for (int j = 1; j < colCnt+1; j++) {String colName = rsmd.getColumnName(j);String colValue = rs.getString(colName);Label label = new Label(j-1, startLine, colValue);sheet.addCell(label);}startLine++;} book.write();book.close();pstm.close();conn.close();}?