今天做了从从数据表中，把数据导到excel文件中，感觉很爽。
步骤如下：
1.jxl.jar包不是JDK提供的包，需要自己下。
下载地址:http://www.andykhan.com/jexcelapi/jexcelapi_2_6_3.tar.gz
2.建立文件：
TestXL.jsp
<%@page contentType="text/html; charset=GBK"%>
<%@taglib uri="/WEB-INF/struts-html.tld" prefix="html"%>
<html:html>
<html:button property="button" οnclick="printAll()">DownLoad</html:button>
</html:html>
<script language='javascript' type="text/javascript">
function printAll(){
location.href="<%=request.getContextPath()%>/downloadAction.do";
}
</script>
welcome.jsp
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>无标题文档</title>
</head>
<body>
恭喜你!!从数据库中导出数据成功!!!!
</body>
</html>
basedata.java
package com;
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:mysql://localhost/test";
String username = "root";
String password = "1984";
String drivers = "org.gjt.mm.mysql.Driver";
/*
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 ";*/
String sqlStr ="select * from nba" ;
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();
}*/
}
DownloadAction.java
package com;
import org.apache.struts.action.*;
import javax.servlet.http.*;
import java.util.Vector;
/**
* <p>Title:DownloadAction </p>
* <p>Description: QRRSMMS </p>
* <p>Copyright: Copyright (c) 2004 jiahansoft</p>
* <p>Company: jiahansoft</p>
* @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{
System.out.print("shibai");
return mapping.findForward("failure");
}
}
}
ExcelBean.java
package com;
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;
}
}
1507
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
