Java操作Excel.rar

package org.test.excel; import jxl.*; import jxl.write.*; import jxl.format.*; import java.io.*; import java.sql.*; import java.util.*; import java.text.*; public class DbToExcel { List records=null; String column_name[]=null; int column_count=0; SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); //初始化数据库连接，并将所有数据转换成List类型 public DbToExcel() { try { Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); Connection con=DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;databaseName=excel","sa","ok"); PreparedStatement ps=con.prepareStatement("select * from employee"); ResultSet rs=ps.executeQuery(); ResultSetMetaData rsmd=rs.getMetaData(); column_count=rsmd.getColumnCount(); column_name=new String[column_count]; for (int i=0; i<column_count; i++) { column_name[i]=rsmd.getColumnName(i+1); } this.records=new ArrayList(); while(rs.next()) { int id=rs.getInt(1); String name=rs.getString(2); int age=rs.getInt(3); String date=rs.getString(4).trim(); //将字符串型的日期进行转换 java.util.Date birthday=sdf.parse(date); float salary=rs.getFloat(5); Employee dto=new Employee(); dto.setId(id); dto.setName(name); dto.setBirthday(birthday); dto.setAge(age); dto.setSalary(salary); records.add(dto); } } catch (Exception ex) { ex.printStackTrace(); } } //将转换好的List类型的数据对象转换到Excel public void toExcel() { try { //使用数据库名作为Excel的文件名 WritableWorkbook wwb=Workbook.createWorkbook(new File("excel.xls")); //创建Excel工作表，使用数据表名作为工作表名 WritableSheet ws=wwb.createSheet("employee",0); //设置所有表头 for (int i=0; i<column_count; i++) { //第一个参数为列，第二个参数为行，从0开始计算 Label label=new Label(i,0,column_name[i]); ws.addCell(label); } //添加数据 for (int i=1; i<=records.size(); i++) { Employee emp=(Employee)records.get(i-1); int id=emp.getId(); String name=emp.getName(); java.util.Date birthday=emp.getBirthday(); int age=emp.getAge(); float salary=emp.getSalary(); System.out.println (emp); //行不动，列动 ws.addCell(new Label(0,i,String.valueOf(id))); ws.addCell(new Label(1,i,name)); ws.addCell(new Label(3,i,String.valueOf(age))); ws.addCell(new Label(2,i,sdf.format(birthday))); ws.addCell(new Label(4,i,String.valueOf(salary))); } //清空缓存 wwb.write(); wwb.close(); } catch (Exception ex) { ex.printStackTrace(); } } public static void main(String args[]) { new DbToExcel().toExcel(); // for(int i=0;i<3;i++) // { // for(int j=0;j<4;j++) // { // System.out.println (j+""+i); // } // } } }