今天遇到了新的需求,需要将不同的结果集数据一次性导入Excel的不同sheet工作表中。之前写的代码完成不了这样的功能,只能将一个结果集导入一张sheet工作表。今天扩展了之前的JavaExcelUtils类,添加了以下的方法,此方法能基本满足上面的需求。
public static boolean createExcelFile(OutputStream output,
String[]
sheetNames, ResultSet[] rsArray) {
boolean bFlag=false;
if(output==null){
return bFlag;
}
//ResultSet数量必须同Excel sheet工作表的数量相同
if(sheetNames.length!=rsArray.length){
return bFlag;
}
WritableWorkbook writableWorkbook=null;
ResultSetMetaData rsmd=null;
try {
writableWorkbook=Workbook.createWorkbook(output);//创建可写工作簿
for(int sheetNum=0;sheetNum
if(sheetNames[sheetNum]==null || "".equals(sheetNames[sheetNum])){
sheetNames[sheetNum]="sheet"+sheetNum;
}
if(rsArray[sheetNum]==null){
continue;
}
//创建可写工作表
WritableSheet
writableSheet=writableWorkbook.createSheet(sheetNames[sheetNum],
0);
try {
rsmd=rsArray[sheetNum].getMetaData();
Object
objData=null;
int rowNum=0;
while(rsArray[sheetNum].next()){
for(int columnNum=0;columnNum
objData=rsArray[sheetNum].getObject(columnNum+1);
JavaExcelUtils.insertData(writableSheet,rowNum,columnNum,objData);
}
rowNum++;
}
}
catch (SQLException e) {
e.printStackTrace();
}
}
bFlag=true;
} catch (IOException e) {
e.printStackTrace();
}
try{
writableWorkbook.write();//真正将数据写入Excel工作簿中
if(writableWorkbook!=null){
writableWorkbook.close();
}
}catch(Exception e){
e.printStackTrace();
bFlag=false;
}
return bFlag;
}
下面的是我写的一个调用的Demo
language="java" import="java.util.*,java.sql.*,java.text.*,java.io.*"
pageEncoding="GBK"%>
import="jxl.*,jxl.format.*,jxl.write.*"%>
import="excel.JavaExcelUtils"%>
import="excel.ExcelUtils"%>
/p>
HTML 4.01 Transitional//EN">
My JSP 'writeToExcelBean.jsp' startingpage
response.reset();
response.setContentType("application/vnd.ms-excel");
java.io.OutputStream output=response.getOutputStream();
%>
private static final String DBDRIVER="com.mysql.jdbc.Driver";
private static final String DBURL="jdbc:mysql://localhost/hedacj";
private static final String DBADMIN="root";
private static final String DBPASS="mysql";
%>
Connection conn = null;
PreparedStatement
pstmt1 = null;
PreparedStatement
pstmt2 = null;
PreparedStatement
pstmt3 = null;
ResultSet rs1 =
null;
ResultSet rs2 =
null;
ResultSet rs3 =
null;
String
sql1="SELECT
id,Householdid,name,sex,idcard,age,address,"
+"relations,communityID FROM
personbaseinfo";
String
sql2="select site,area,id,iffree,toward,communityID
from availability";
String
sql3="select
ID,FLOWNAME,DATAMODNAME,DISPLAYNAME,DATASOURCE,PANELTYPE,"+
"FLOWCHNNAME,FLOWORDER,SETTYPE,CTRSTATUSNAME,OPTROLES,OPTPERSON
from tflowcontrolset";
try {
Class.forName(DBDRIVER);
System.out.println("加载驱动器类成功!");
conn =
DriverManager.getConnection(DBURL,DBADMIN,DBPASS);
System.out.println("获得Connection对象成功!");
pstmt1=conn.prepareStatement(sql1);
pstmt2=conn.prepareStatement(sql2);
pstmt3=conn.prepareStatement(sql3);
System.out.println("获得PreparedStatement对象成功!");
rs1=pstmt1.executeQuery();
rs2=pstmt2.executeQuery();
rs3=pstmt3.executeQuery();
String[] sheetNames={"表一","表二","表三"};
ResultSet[] rsArray={rs1,rs2,rs3};
boolean bFlag=JavaExcelUtils.createExcelFile(output,sheetNames,rsArray);
System.out.println(bFlag==true?"数据导出成功!":"数据导出失败!");
} catch (Exception e) {
e.printStackTrace();
} finally {
try{
if(rs1!= null){
rs1.close();
System.out.println("ResultSet对象1已关闭");
}
if(rs2!= null){
rs2.close();
System.out.println("ResultSet对象2已关闭");
}
if(rs3!= null){
rs3.close();
System.out.println("ResultSet对象3已关闭");
}
if(pstmt1!=null){
pstmt1.close();
System.out.println("PreparedStatement对象1已关闭");
}
if(pstmt2!=null){
pstmt2.close();
System.out.println("PreparedStatement对象2已关闭");
}
if(pstmt3!=null){
pstmt3.close();
System.out.println("PreparedStatement对象3已关闭");
}
if(conn!=null){
conn.close();
System.out.println("Connection对象已关闭");
}
}catch(Exception e){
e.printStackTrace();
}
}
%>