jxl操作excel文件---使用模板
<%@ page contentType="text/html;charset=UTF8" language="java"%>
<%@ include file="/commons/taglibs.jsp"%>
<%@ page errorPage="/jsp/errorpage.jsp"%>
<%@ page import="hfmpBean.util.*"%>
<%@ page import="hfmpBean.data.*"%>
<%@ page import="java.util.*"%>
<%@ page session="false"%>
<%@ page import="jxl.Workbook"%>
<%@ page import="jxl.write.WritableCellFormat"%>
<%@ page import="jxl.write.WritableSheet"%>
<%@ page import="jxl.write.WritableFont"%>
<%@ page import="jxl.CellView"%>
<%@ page import="jxl.write.Label"%>
<%@ page import="jxl.write.WritableWorkbook"%>
<%@ page import="jxl.write.WriteException"%>
<%@ page import="java.text.SimpleDateFormat"%>
<%@ page import="com.join.utils.*"%>
<%@ page import="java.io.OutputStream"%>
<%@ page import="java.io.File"%>
<jsp:useBean id="getData" scope="page" />
<jsp:useBean id="fileData" />
<jsp:useBean id="getBldData" />
<jsp:useBean id="fileBldData" />
<html>
<head>
<title>无标题文档</title>
<style>
.xlsText {
mso-number-format: "\@";
}
</style>
</head>
<%
String regionNo = Convert.convertNull(request
.getParameter("regionNo"));
String regionNameTemp = Convert.convertNull(request
.getParameter("regionName"));
String regionName = Convert.convertGBK(request
.getParameter("regionName"));
String bldNo = Convert.convertNull(request.getParameter("bldNo"));
String bldName = Convert
.convertGBK(request.getParameter("bldName"));
String bldNameTemp = Convert.convertNull(request
.getParameter("bldName"));
String returnback = Convert.convertNull(request
.getParameter("returnback"));
String houseAttr = "";
String houseUse = "";
String struArea = "0.0";//建筑面积
String mainArea = "0.0";//主体面积
String annexeArea = "0.0";//附属面积
String houseNo = "";//房屋编号（15位）：[2]区县编号+[6]自然幢+[2]单元+[2]楼层+[3]户
String houseSite = "";
String firstMode = "";//初缴模式
String accNo = "";
String name = "";
String zoneCode = "";
String bankCode = "";
String agentNoName = "";
String houseAttrName = "";
String houseUseName = "";
String firstModeName = "";
getBldData = fileBldData.getR_bldInfo(" a.bldNo='" + bldNo + "'");
zoneCode = getBldData.getZoneName();
bankCode = getBldData.getBankName();
agentNoName = getBldData.getAgentName();
int startBld = Code.getHouseNO_SQL(0)[0];
int endBld = Code.getHouseNO_SQL(0)[1];
String sqlStr = " substr(houseNo," + startBld + "," + endBld
+ ")='" + bldNo + "' and a.state='9' order by houseNo";
ArrayList rsDatas = fileData.getR_psnInfos(sqlStr);
int num = 0;//序号
String subRegionName = Convert.convertNull(getBldData
.getSubRegionName());
String titleName = regionName + " " + subRegionName + " " + bldName;
%>
<%
//response.setHeader("Content-disposition","inline; filename="+regionNameTemp+"-"+bldNameTemp+".xls");
%>
<%
String excelFile = request.getSession().getServletContext()
.getRealPath("/templates/export_psninfo.xls");
// List result = PlayService.xlsMembers();//一个简单的方法,列出Member类的所有实例
// 设置资源头信息
out.clearBuffer();
out = pageContext.pushBody();
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "filename="
+ regionNameTemp + "-" + bldNameTemp + ".xls");//attachment
// WritableWorkbook是JexcelApi的一个类。
// 以下可以理解为创建一个excel文件，然后在excel里面创建一个表
OutputStream os = response.getOutputStream();//取得输出流
Workbook book = Workbook.getWorkbook(new File(excelFile));
WritableWorkbook workbook = Workbook.createWorkbook(os, book);
//WritableSheet sheet = workbook.createSheet("First Sheet", 0);
WritableSheet sheet = workbook.getSheet("sheet1");
// Workbook wb = Workbook.getWorkbook(new File(excelFile));
// 第二步：通过模板得到一个可写的Workbook：第一个参数是一个输出流对象,第二个参数代表了要读取的模板
// File targetFile = new File("D:/test1.xls");
//WritableWorkbook workbook = Workbook.createWorkbook(targetFile, wb);
// 第三步：选择模板中名称为StateResult的Sheet：
//WritableSheet sheet = workbook.getSheet(1);
// 第四步：选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：
// 组织excel文件的内容
jxl.write.Label label = null;
//SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd");
WritableCellFormat cellFormat1 = new WritableCellFormat();
cellFormat1.setLocked(false);
WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setLocked(true);
WritableCellFormat cellWidth = new WritableCellFormat();
WritableFont font2 = new WritableFont(WritableFont.TIMES, 12,
WritableFont.BOLD);
WritableCellFormat format2 = new WritableCellFormat(font2);
format2.setAlignment(jxl.format.Alignment.CENTRE);
int excelCol = 0;
int row = 2;
try {
//生成第一行的信息
label = new jxl.write.Label(0, 0, "注：<b>业主类别：</b>1.自然人；2.法人 证件类别： 1.身份证；2.军官证；3.护照；4.法人代表证 ,99.其它 日期格式: 为YYYY-MM-RR(如:2007-1-1) 请填入对应的数字",format2);
sheet.addCell(label);
sheet.mergeCells(0,0,20,0); //将第一行合并
//生产第二行的信息
label = new jxl.write.Label(4,1, regionName+bldName,format2);
sheet.addCell(label);
sheet.mergeCells(4,1,5,1);
label = new jxl.write.Label(6, 1, "业主账户",format2);
sheet.addCell(label);
label = new jxl.write.Label(7, 1, "明细表",format2);
sheet.addCell(label);
//生成第三行的信息，
label = new jxl.write.Label(excelCol++, row, "序号");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "账号");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "房屋位置");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "主体面积");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "附属面积");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "业主姓名");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "业主类别");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "证件类别");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "证件号码");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "联系电话");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "房屋售价");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "购房日期");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "应缴维修金");
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, "收缴日期");
sheet.addCell(label);
//循环生成 excel表的数据
int j=3;
for(int i=0;i<rsDatas.size();i++){
R_psnInfo rr = (R_psnInfo) rsDatas.get(i);
excelCol = 0;
row =j;
label = new jxl.write.Label(excelCol++, row, String.valueOf(i),cellFormat);
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row,rr.getAccNo(),cellFormat);
sheet.addCell(label);
// label.getString().se
label = new jxl.write.Label(excelCol++, row, rr.getHouseSite(),cellFormat1);
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, rr.getStruArea(),cellFormat1);
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row, rr.getAnnexeArea(),cellFormat1);
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
sheet.addCell(label);
label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1); //身份证号
// sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
// sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
// sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
// sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
// sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
// sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
// sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
// sheet.addCell(label);
// label = new jxl.write.Label(excelCol++, row,"",cellFormat1);
// sheet.addCell(label);
j++;
//label = new jxl.write.Label(excelCol, row, formatDate.format(rr.getCreateTime()));
//sheet.addCell(label);
//
}
// sheet.getSettings().setProtected(true); //设置xls的保护，单元格为只读的
// sheet.getSettings().setPassword("123"); //设置xls的密码
} catch (Exception e) {
e.printStackTrace();
} finally {
// 生成excel文件
workbook.write();
workbook.close();
os.close();
}
%>
</html>
