数据转换excel的java程序

 要用到jxl的包
具体实例
1.生成excel文件
package com.createexcel;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.ResultSetMetaData;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.bean.DataInfo;

public class CreateExcel {
  
 public void CreateExcel(String targetfile,String start,String end) {
    

            String worksheet = "日志输出";       //输出的excel文件工作表名
     GetDataInfo data=new GetDataInfo();
     List list=new ArrayList();
     list=data.getdata(start,end);
     if(list.size()!=0){
        int colCount=0;
        TestDao test=new TestDao();
        ResultSet  rs = test.selectSql("SELECT * FROM datainfo");                                        
       

            WritableWorkbook workbook;
      try{

 System.out.println("begin");
 //创建可写入的Excel工作薄,运行生成的文件在tomcat/bin下
 //workbook = Workbook.createWorkbook(new File("output.xls"));
 OutputStream os=new FileOutputStream(targetfile);
 workbook=Workbook.createWorkbook(os);
 WritableSheet sheet = workbook.createSheet(worksheet, 0); 
 //WritableSheet sheet1 = workbook.createSheet("MySheet1", 1);//可添加第二个工作

 jxl.write.Label label;  
 WritableFont font = new WritableFont(WritableFont.ARIAL,8,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.DARK_RED2);
 WritableCellFormat cFormat = new WritableCellFormat(font);   //设置表格式

 //cFormat.setBackground(Colour.GREY_50_PERCENT);
 
   java.sql.ResultSetMetaData md=rs.getMetaData();   //读出数据库的字段名
   int nColumn=md.getColumnCount();
   for(int i=0;i<nColumn;i++)
  { 
  //System.out.println(md.getColumnLabel(i+1));         //md.getColumnLabel(n)n是从1开始的
  label = new jxl.write.Label(i, 0, md.getColumnLabel(i+1),cFormat); //第i+1列，第1行开始
  sheet.addCell(label);  
  }
 
 
    int size=list.size();
    int iRow=1;                         //控制行数，从第二行开始写
    for(int i=0;i<size;i++){    //写入各条记录，每条记录对应Excel中的一行，i控制的是查询出来的时间段内的里list的链数
    int j=0;
    //依次插入一行（iRow）中的每一列(j)
    label = new jxl.write.Label(j++, iRow, String.valueOf(((DataInfo) list.get(i)).getNodeid()));//int型向string的转换
    sheet.setColumnView(j,20);      //列的宽度
    sheet.addCell(label);  
   
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getCpu());
    sheet.setColumnView(j,20);     
    sheet.addCell(label);
   
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getMem());
    sheet.setColumnView(j,20);     
    sheet.addCell(label);
   
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getTotalhd());
    sheet.setColumnView(j,20);     
    sheet.addCell(label);
   
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getUsedhd());
    sheet.setColumnView(j,20);     
    sheet.addCell(label);
   
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getPrehd());
    sheet.setColumnView(j,20);    
    sheet.addCell(label);
   
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getNet());
    sheet.setColumnView(j,20);     
    sheet.addCell(label);
   
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getIp());
    sheet.setColumnView(j,20);   
    sheet.addCell(label);
   
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getPort());
    sheet.setColumnView(j,20);    
    sheet.addCell(label);
   
    label = new jxl.write.Label(j++, iRow, String.valueOf(((DataInfo) list.get(i)).getNodestate()));
    sheet.setColumnView(j,20);     
    sheet.addCell(label);  
  
    label = new jxl.write.Label(j++, iRow, ((DataInfo) list.get(i)).getTime());
    sheet.setColumnView(j,20);   
    sheet.addCell(label);
   
    iRow++;
    }  

 jxl.write.WritableCellFormat wcsB = new jxl.write.WritableCellFormat();
 wcsB.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THICK);

 workbook.write();

 workbook.close();
 os.close();
 }catch(Exception e)
 {
 e.printStackTrace();
 }
 System.out.println("end");
     }
 //return t;
 }
 }

2.从数据库中得到某个时间段的想要转换成excel的数据

package com.createexcel;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.bean.DataInfo;

public class GetDataInfo {

 public List getdata(String start,String end){
   DataInfo datainfo=null;
   List list=new ArrayList();
      String sql="select * from datainfo where time between '"+start+"' and '"+end+"'";
//      String sql="select * from datainfo"+
//   " where time between date_format('"+start+"','%Y-%m-%d% H:%i:%s') and date_format('"+end+"','%Y-%m-%d% H:%i:%s')";
      TestDao test=new TestDao();
   System.out.println(sql);
  
   ResultSet rs=test.selectSql(sql);
      try {
    while(rs.next()){
    datainfo=new DataInfo();
    datainfo.setNodeid(rs.getInt("nodeid"));
    datainfo.setCpu(rs.getString("cpu"));
    datainfo.setIp(rs.getString("ip"));
    datainfo.setMem(rs.getString("mem"));
    datainfo.setNet(rs.getString("net"));
    datainfo.setNodestate(rs.getInt("nodestate"));
    datainfo.setPort(rs.getString("port"));
    datainfo.setPrehd(rs.getString("prehd"));
    datainfo.setTime(rs.getString("time"));
    datainfo.setTotalhd(rs.getString("totalhd"));
    datainfo.setUsedhd(rs.getString("usedhd"));
    list.add(datainfo);
    int i=0;
    i++;
            }
    rs.close();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
   return list;
  }
}

3.用到的数据库bean函数DataInfo

package com.bean;
public class DataInfo {
 private int nodeid;
 private String cpu;
 private String mem;
 private String totalhd;
 private String usedhd;
 private String prehd;
 private String net;
 private String ip;
 private String port;
 private String time;
 private int nodestate;
 public int getNodeid() {
  return nodeid;
 }
 public String getCpu() {
  return cpu;
 }
 public String getMem() {
  return mem;
 }
 public String getTotalhd() {
  return totalhd;
 }
 public String getUsedhd() {
  return usedhd;
 }
 public String getPrehd() {
  return prehd;
 }
 public String getNet() {
  return net;
 }
 public String getIp() {
  return ip;
 }
 public String getPort() {
  return port;
 }
 public String getTime() {
  return time;
 }
 public int getNodestate() {
  return nodestate;
 }
 public void setNodeid(int nodeid) {
  this.nodeid = nodeid;
 }
 public void setCpu(String cpu) {
  this.cpu = cpu;
 }
 public void setMem(String mem) {
  this.mem = mem;
 }
 public void setTotalhd(String totalhd) {
  this.totalhd = totalhd;
 }
 public void setUsedhd(String usedhd) {
  this.usedhd = usedhd;
 }
 public void setPrehd(String prehd) {
  this.prehd = prehd;
 }
 public void setNet(String net) {
  this.net = net;
 }
 public void setIp(String ip) {
  this.ip = ip;
 }
 public void setPort(String port) {
  this.port = port;
 }
 public void setTime(String time) {
  this.time = time;
 }
 public void setNodestate(int nodestate) {
  this.nodestate = nodestate;
 }
} 
4.测试程序
package com.createexcel;

public class test {

 
 public static void main(String[] args) {
  new CreateExcel().CreateExcel("C://test.xls", "2009-11-03 11:13:28", "2009-11-03 11:15:59");

 }

}