struts+jxl

robbln

浏览: 18164 次
性别:
来自: 四川成都

最近访客更多访客>>

xingfuhongyun

han2163534

注孤一掷

heyongqiang1205

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Struts Excel Oracle OS SQL

TestXL.jsp




DownLoad

<script>
function printAll(){
location.href="<%=request.getContextPath()%>/downloadAction.do";
}
</script>

ExcelBean:

import java.io.*;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.util.*;
import javax.servlet.http.HttpServletResponse;

/*
用于Struts结构中的Excel文件写操作，当但击“导出按钮”时可以直接在客户端导出Excel文件并提示保存
使用方法：
在action类中加入：
ExcelBean eb = new ExcelBean();
if(vect.size()>0){
eb.exportToExcel(response, fname, sheetname, titles, vect); //调用生成excel文件bean
}
参数：
response:action类中的HttpServletResponse;
filename:要生成的保存的缺省的Excel文件名
sheetname:要保存的工作表的名称
titles:工作表中的表格横向标题
vect:数据行
*/

public class ExcelBean {
public ExcelBean() {}
public boolean exportToExcel(HttpServletResponse response,String filename,String sheetname,String[] titles,Vector vect) throws Exception {
OutputStream os = null;
boolean b1 = true ;
try {
os = response.getOutputStream(); //取得输出流
response.reset(); //清空输出流
response.setHeader("Content-disposition","attachment; filename=" + filename + ".xls"); //设定输出文件头
response.setContentType("application/msexcel"); //定义输出类型
} catch (IOException ex) {
b1= false;
System.out.println("流操作错误:"+ex.getMessage());
}
WritableWorkbook workbook = null;
try {
//创建新的Excel 工作簿
workbook = Workbook.createWorkbook(os);
//在Excel工作簿中建一工作表，其名为:第一页
jxl.write.WritableSheet wsheet = workbook.createSheet(sheetname, 0); //sheet();
WritableFont font = new WritableFont(WritableFont.ARIAL, 14,
WritableFont.BOLD, false,
jxl.format.UnderlineStyle.
NO_UNDERLINE,
jxl.format.Colour.BLACK);
WritableCellFormat format = new WritableCellFormat(font);
for (int i = 0; i < titles.length; i++) {
Label wlabel1 = new Label(i, 0, titles[i], format); //行、列、单元格中的文本、文本格式
wsheet.addCell(wlabel1);
}
font = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 12,
WritableFont.NO_BOLD, false,
jxl.format.UnderlineStyle.
NO_UNDERLINE,
jxl.format.Colour.BLACK);
format = new jxl.write.WritableCellFormat(font);
for (int i = 0; i < vect.size(); i++) { // 在索引0的位置创建行（最顶端的行）
String[] sdata = (String[]) vect.elementAt(i);
for (int j = 0; j < sdata.length; j++) { //在索引0的位置创建单元格（左上端）
Label wlabel1 = new Label(j, i, sdata[j], format); //行、列、单元格中的文本、文本格式
wsheet.addCell(wlabel1);
System.out.println(sdata[j] + ":::" + sdata[j]);
}
}
workbook.write(); //写入文件
} catch (WriteException ex1) {
b1 = false;
System.out.println("WriteException:"+ex1.getMessage());
} catch (IOException ex2) {
b1 = false;
System.out.println("IOException:"+ex2.getMessage());
}
workbook.close();
os.close();
return b1;
}
}

DownloadAction:

import org.apache.struts.action.*;
import javax.servlet.http.*;
import com.segsec.base.ExcelBean;
import com.segsec.data.basedata;
import java.util.Vector;

/**
*

Title:DownloadAction

Description: QRRSMMS

Company: jiahansoft

* @author wanghw
* @version 1.0
*/

public class DownloadAction extends Action {
public ActionForward execute(ActionMapping mapping,
ActionForm form,
HttpServletRequest request,
HttpServletResponse response) throws Exception {
basedata databean = new basedata();
Vector vect = new Vector();
boolean b1= false;
try {
String fname = "test"; //Excel文件名
String[] titles = {"车牌号", "车辆类型", "用户帐号", "车台号"}; //标题
String sheetname = "车辆列表";
ExcelBean eb = new ExcelBean();
vect = databean.QuerySimilarVehicleInfoByAdminer();
if(vect.size()>0){
b1 = eb.exportToExcel(response, fname, sheetname, titles, vect); //调用生成excel文件bean
}
} catch (Exception e) {
System.out.println(e);
}
if(b1){
return mapping.findForward("display");
}else{
return mapping.findForward("failure");
}
}
}

basedata:

import java.sql.PreparedStatement;
import java.util.Vector;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Connection;

public class basedata {
public Vector QuerySimilarVehicleInfoByAdminer(){
Vector vect = new Vector();
int i = 0;
Connection conn = null;
PreparedStatement pstam =null;
ResultSet rs =null;
String url = "jdbc:oracle:thin:@90.0.8.2:1521:ORCL";
String username = "gisap";
String password = "1";
String drivers = "oracle.jdbc.driver.OracleDriver";

String sqlStr = "SELECT V.NUMBER_PLATE,v.vehicletype,A.ADMINER,U.CALL_LETTER "
+" FROM T_ADMINER_UNIT AU,T_ADMINER A,T_VEHICLE V,T_UNIT U "
+" WHERE A.ADMINERID=AU.ADMINERID"
+" AND A.ADMINER ='master' "
+" AND AU.UNITID = U.UNITID "
+" AND U.UNITID = V.UNITID "
+" ORDER BY V.NUMBER_PLATE ";
try{
Class.forName(drivers);
conn = DriverManager.getConnection(url, username, password);
pstam = conn.prepareStatement(sqlStr);
rs = pstam.executeQuery();
int collength = rs.getMetaData().getColumnCount();
while (rs.next()) {
collength = rs.getMetaData().getColumnCount();
String[] rowdata = new String[collength];
for (int k = 1; k <= collength; k++) {
rowdata[k - 1] = rs.getString(k); //new String(rs.getString(k).getBytes("ISO-8859-1"), "GBK"); //
}
vect.add(rowdata);
}
}catch(Exception e){
System.out.println("QuerySimilarVehicleInfoByAdminer Error!"+e.getMessage());
}
finally{
try{
rs.close();
pstam.close();
conn.close();
}catch(Exception e){}
}
return vect;
}
public static void main(String[] args) {
basedata basedata = new basedata();
}
}

分享到：