java 表的记录集个数_将数个ResultSet结果集数据一次性写入Excel的数个sheet工作表...

今天遇到了新的需求，需要将不同的结果集数据一次性导入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' starting

page

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

}

}

%>