JXL 数据库向Excel导入数据

package com.neusoft.serviceImp;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.neusoft.daoImp.PeoplesDaoImp;
import com.neusoft.entity.Peoples;

import jxl.*;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

/**
* 对Excel表进行操作
*
* @author ********
*
*/
public class ExcelUtil {
private static Log log = LogFactory.getLog(ExcelUtil.class);

public boolean DB2Excel(String path) {
  boolean flag = false;
  WritableWorkbook workbook = null;
  WritableSheet sheet = null;
  Label label = null;

  // 创建Excel表
  try {
    workbook = Workbook.createWorkbook(new File(path));
   //workbook = Workbook.createWorkbook(os);
   // 创建Excel表中的sheet
   sheet = workbook.createSheet("First Sheet", 0);

   // 定义表中的数据
   // Label label = new Label(0, 0, "k k 中国人*");
   // Label label1 = new Label(0, 1, "A label record");
   // Label label3 = new Label(0, 2, "3.14159");
   // Number number = new Number(1, 1, 3.1459);

   // 向Excel中添加数据\
   System.out.println("1!!!!!");
   ApplicationContext factory = new ClassPathXmlApplicationContext("applicationContext.xml");
   System.out.println("1@@@@@@");
   PeoplesDaoImp dao=(PeoplesDaoImp) factory.getBean("peoplesDaoImp");
   System.out.println("1###########");
   List list = dao.find();
   System.out.println(list.size());
   int columnCount = list.size();

   // ResultSetMetaData rsmd = rs.getMetaData();
   // int columnCount = rsmd.getColumnCount();
   String colName = null;
   int row = 0;
   // 添加标题
   // for (int i = 0; i < columnCount; i++) {
   // colName = rsmd.getColumnName(i + 1);
   // label = new Label(i, row, colName);
   // // log.info("标题:"+i+"---"+row +"---"+ colName);
   // sheet.addCell(label);
   // }
   Label label1 = new Label(0, row, "群组名称");
   Label label2 = new Label(1, row, "姓名");
   Label label3 = new Label(2, row, "生日");
   Label label4 = new Label(3, row, "电话");
   Label label5 = new Label(4, row, "邮箱");
   Label label6 = new Label(5, row, "地址");
   sheet.addCell(label1);
   sheet.addCell(label2);
   sheet.addCell(label3);
   sheet.addCell(label4);
   sheet.addCell(label5);
   sheet.addCell(label6);

   row++;
   log.info("写入标题成功");
  
   for(int i=0;i<columnCount; i++){
    Peoples p = (Peoples)list.get(i);
    Label label11 = new Label(0, row,p.getGroupname() );
    Label label21 = new Label(1, row,p.getName() );
    Label label31 = new Label(2, row,p.getBirthday() );
    Label label41 = new Label(3, row,p.getTelephone() );
    Label label51 = new Label(4, row,p.getEmail() );
    Label label61 = new Label(5, row,p.getAddress() );
    sheet.addCell(label11);
    sheet.addCell(label21);
    sheet.addCell(label31);
    sheet.addCell(label41);
    sheet.addCell(label51);
    sheet.addCell(label61);
    row++;
   }
  
//   while (rs.next()) {
//    for (int i = 0; i < columnCount; i++) {
//     label = new Label(i, row, rs.getString(i + 1));
//     // log.info("行:"+i+"---"+row +"---"+ rs.getString(i+1));
//     sheet.addCell(label);
//    }
//    row++;
//   }
   log.info("写入内容成功");
  
   System.out.println("AAAAAAAAAAAAAAAAAAA");
   // 关闭文件
   workbook.write();
   workbook.close();
   log.info("数据成功写入Excel");
   flag = true;
  } catch (RowsExceededException e) {
   log.info(e.getMessage());
  } catch (WriteException e) {
   log.info(e.getMessage());
  } catch (IOException e) {
   log.info(e.getMessage());
  } finally {
   try {
    workbook.close();
   } catch (Exception e) {
   }
  }
  return flag;
}

public boolean Excel() throws RowsExceededException, WriteException {
  boolean flag = false;
  WritableWorkbook workbook = null;
  WritableSheet sheet = null;
  Label label = null;
  try {
   workbook = Workbook.createWorkbook(new File("d:/output.xls"));
   // 创建Excel表中的sheet
   sheet = workbook.createSheet("First Sheet", 0);
   // 定义表中的数据
   label = new Label(0, 0, "k k 中国人*");
   Label label1 = new Label(0, 1, "A label record");
   Label label3 = new Label(0, 2, "3.14159");
   // jxl.write.Number number=new jxl.write.Number(1, 1, 3.1459);
   sheet.addCell(label);
   // sheet.addCell(number);
   sheet.addCell(label1);
   sheet.addCell(label3);
   // Number number = new Number(1, 1, 3.1459);
   workbook.write();
   workbook.close();
   System.out.println("SSSSSSSSSSSSSSSSSSSSSSSS");
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return false;
}

public static void main(String[] args) {
  try {

   // Class.forName("net.sourceforge.jtds.jdbc.Driver");
   // Connection con = DriverManager.getConnection(
   // "jdbc:jtds:sqlserver://localhost:1433/abc", "sa", "sa");
   // Statement st = con.createStatement();
   // ResultSet rs = st.executeQuery("select * from abc.dbo.sss");
   // // if (!new ExcelUtil().DB2Excel(rs)){
   // // log.info("数据写入失败");
   // // }
   // rs.close();
   // st.close();
   // con.close();
   ExcelUtil e = new ExcelUtil();
   e.DB2Excel("d:/output.xls");

  } catch (Exception e) {
   e.printStackTrace();
  }
}

}