package com.insertExcel; import java.io.BufferedOutputStream; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import java.util.Map; import jxl.Workbook; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; import com.chem.is.util.DateUtil; public class Test { public ResultSet test(){ Connection conn= null; Statement stmt=null; ResultSet rs =null; // List rsall = new ArrayList(); // Map rsTree; try{ String insertSql = ""; String selectSql= "Select eng_name,ch_name,prefix_code,category_order from category_info"; Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://192.168.1.106:3306/bide_system?user=root&useUnicode=true&characterEncoding=utf8"; String user =""; String password=""; conn= DriverManager.getConnection(url,user,password); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE); rs = stmt.executeQuery(selectSql); // ResultSetMetaData rsmd = rs.getMetaData();//取数据库的列名 我觉得名比1,2,3..更好用 // int numberOfColumns = rsmd.getColumnCount(); //列数 // System.out.println("numberOfColumns 列数= "+numberOfColumns); // while(rs.next()){ // rsTree=new HashMap(numberOfColumns); // for(int r=1;r<numberOfColumns-1;r++ ){ // rsTree.put(rsmd.getColumnName(r),rs.getObject(r)); // } // rsall.add(rsTree); // } // System.out.println("rsall.size======"+rsall.size()); }catch(Exception e){ e.printStackTrace(); }finally{ } return rs; } public String createFileName() { //得到当前时间 java.sql.Timestamp nowTime = DateUtil.getNowTime(); System.out.println("nowTime = "+nowTime); String noeTimeString = nowTime.toString(); String replaceOne = noeTimeString.replace("-", ""); String replaceTwo = replaceOne.replace(":", ""); String replaceThree=replaceTwo.replace(" ", ""); String replaceFore= replaceThree.replace(".",""); System.out.println("replaceFore ==="+replaceFore); return replaceFore; } public void exportClassroom() throws Exception { try { String fileName= "D:\\"+createFileName()+".xls"; System.out.println("fileName ====="+fileName); FileOutputStream fos = new FileOutputStream(fileName); BufferedOutputStream bos = new BufferedOutputStream(fos); WritableWorkbook wbook = Workbook.createWorkbook(bos); //建立excel文件 WritableSheet wsheet = wbook.createSheet("产品类别", 0); //工作表名称 //设置Excel字体 WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat titleFormat = new WritableCellFormat(wfont); String[] title = { "英文名称", "中文名称", "类别前缀", "显示顺序" }; //设置Excel表头 for (int i = 0; i < title.length; i++) { Label excelTitle = new Label(i, 0, title[i], titleFormat); wsheet.addCell(excelTitle); } int c = 1; //用于循环时Excel的行号 //返回rs ResultSet rs = test(); //返回list // //List list=test(); // for (int i = 0; i < title.length; i++) { // // } while (rs.next()) { Label content1 = new Label(0, c, rs.getString("eng_name")); Label content2 = new Label(1, c, rs.getString("ch_name")); Label content3 = new Label(2, c, rs.getString("prefix_code")); Label content4 = new Label(3, c, rs.getString("category_order")); //返回list // CategoryInfo categoryInfo = (CategoryInfo)list.next(); // Label content1 = new Label(0, c,categoryInfo.getEngName() ); wsheet.addCell(content1); wsheet.addCell(content2); wsheet.addCell(content3); wsheet.addCell(content4); c++; System.out.println("第"+c+"行"); } wbook.write(); //写入文件 wbook.close(); bos.close(); fos.close(); } catch (Exception e) { e.printStackTrace(); throw new Exception("导出文件出错"); } } public static void main(String[] args) throws Exception { Test t = new Test(); // t.createFileName(); t.exportClassroom(); } }