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();
}
}

}