一、列表页面的部分代码
- <input type="button" class="cxBut2" value="导出数据" onclick="_export()"/>
- function _export(){
- document.location.href="download.jsp?czId=<%=czId%>&czNum=<%=czNum%>&beginIssue=<%=beginIssue%>&endIssue=<%=endIssue%>&pageNum=<%=pageNum%>&czName="+$("#czId").find("option:selected").text();
- }
二、download.jsp
- <%@ page contentType="application/vnd.ms-excel" language="java" import="java.util.*" pageEncoding="UTF-8"%>
- <%@page import="com.zhcw.kaijiang.service.ExportExcal"%>
- <%@page import="java.io.OutputStream"%>
- <%
- String czNum = request.getParameter("czNum");
- String czId = request.getParameter("czId");
- String czName = request.getParameter("czName");
- String beginIssue = request.getParameter("beginIssue");
- String endIssue = request.getParameter("endIssue");
- String pageNum = request.getParameter("pageNum");
- response.resetBuffer();
- response.setHeader("Content-Disposition", "attachment;filename="+ new String(czName.getBytes("UTF-8"), "iso8859-1")+".xls");//指定下载的文件名
- response.setContentType("application/vnd.ms-excel");
- try{
- ExportExcal exportExcal = new ExportExcal(beginIssue, czId, czName, czNum, endIssue, pageNum);
- exportExcal.export(response.getOutputStream());
- }catch(Exception ex){
- ex.printStackTrace();
- }
- %>
注意:这两行代码必须要写:
- response.resetBuffer();<pre name="code" class="html">response.setContentType("application/vnd.ms-excel");</pre>
- <pre></pre>
- <p></p>
- <pre></pre>
- <p></p>
- <p></p>
- 三、后台处理类<pre name="code" class="java">package com.zhcw.kaijiang.service;
- import java.io.File;
- import java.io.FileOutputStream;
- import java.io.OutputStream;
- import java.text.DecimalFormat;
- import java.util.List;
- import jxl.Workbook;
- import jxl.format.Alignment;
- import jxl.format.Border;
- import jxl.format.BorderLineStyle;
- import jxl.write.Label;
- import jxl.write.WritableCellFormat;
- import jxl.write.WritableSheet;
- import jxl.write.WritableWorkbook;
- import com.common.PropertiesOperator;
- import com.crawler.entity.KaiJiangInfo;
- import com.zhcw.kaijiang.util.StringUtil;
- /**
- * 将查询出来的信息导出到excel中
- *
- * @author zhcw
- *
- */
- public class ExportExcal {
- private String czNum = "";
- private String czId = "";
- private String czName = "";
- private String beginIssue = "";
- private String endIssue = "";
- private String pageNum = "";
- public ExportExcal(String beginIssue, String czId, String czName, String czNum, String endIssue, String pageNum) {
- super();
- this.beginIssue = (beginIssue == null ? "" : beginIssue);
- this.endIssue = (endIssue == null ? "" : endIssue);
- this.czId = (czId == null ? "" : czId);
- this.czName = (czName == null ? "" : czName);
- this.czNum = (czNum == null || czNum.trim().equals("") || czNum.trim().equals("null")) ? "30" : czNum;
- this.pageNum = (pageNum == null || pageNum.trim().equals("") || pageNum.trim().equals("null")) ? "1" : pageNum;
- }
- public List<KaiJiangInfo> kaiJiangInfoList() {
- KaijiangInfoService kaijiangInfoService = new KaijiangInfoService();
- if (this.beginIssue!=null && this.beginIssue.trim().length() > 0) {
- return kaijiangInfoService.getSplitPageByCzIdNew(Long.valueOf(this.czId), Integer.valueOf(pageNum), 400, this.beginIssue, this.endIssue);
- } else {
- return kaijiangInfoService.getSplitPageByCzIdNew(Long.valueOf(this.czId), Integer.valueOf(pageNum), Integer.parseInt(czNum));
- }
- }
- public void export(OutputStream output) throws Exception {
- PropertiesOperator propertiesOperator = new PropertiesOperator();
- String cz_id_css_1 = propertiesOperator.getMessage("cz_id_css_1") == null ? "" : propertiesOperator.getMessage("cz_id_css_1").trim();
- String cz_id_css_2 = propertiesOperator.getMessage("cz_id_css_2") == null ? "" : propertiesOperator.getMessage("cz_id_css_2").trim();
- String cz_id_css_3 = propertiesOperator.getMessage("cz_id_css_3") == null ? "" : propertiesOperator.getMessage("cz_id_css_3").trim();
- String cz_id_css_4 = propertiesOperator.getMessage("cz_id_css_4") == null ? "" : propertiesOperator.getMessage("cz_id_css_4").trim();
- String cz_id_css_5 = propertiesOperator.getMessage("cz_id_css_5") == null ? "" : propertiesOperator.getMessage("cz_id_css_5").trim();
- if (StringUtil.contains(this.czId, cz_id_css_1)) {
- this.exportSSQExcel(output);
- } else if (StringUtil.contains(this.czId, cz_id_css_2)) {
- this.export3DExcel(output);
- }
- }
- private void exportExcel(String type,OutputStream output) throws Exception {
- WritableWorkbook workbook = Workbook.createWorkbook(output);// 创建工作薄
- WritableSheet sheet = workbook.createSheet(this.czName, 0);// 创建第一个工作表,name:工作表名称
- //设置列宽度
- sheet.setColumnView(0,7);
- sheet.setColumnView(1,15);
- sheet.setColumnView(2,13);
- sheet.setColumnView(3,25);
- sheet.setColumnView(4,15);
- sheet.setColumnView(6,15);
- sheet.setColumnView(8,15);
- sheet.setColumnView(10,15);
- sheet.setColumnView(11,15);
- WritableCellFormat format = new WritableCellFormat();
- format.setAlignment(Alignment.CENTRE);
- format.setBorder(Border.ALL, BorderLineStyle.THIN);
- int row = 0;
- //合并标题行
- sheet.mergeCells(0, row, 11, row);
- // 合并
- sheet.mergeCells(0, row + 1, 0, row + 2);
- sheet.mergeCells(1, row + 1, 1, row + 2);
- sheet.mergeCells(2, row + 1, 2, row + 2);
- sheet.mergeCells(3, row + 1, 3, row + 2);
- sheet.mergeCells(4, row + 1, 4, row + 2);
- sheet.mergeCells(11, row + 1, 11, row + 2);
- sheet.mergeCells(5, row + 1, 6, row + 1);
- sheet.mergeCells(7, row + 1, 8, row + 1);
- sheet.mergeCells(9, row + 1, 10, row + 1);
- Label label = null;// 用于写入文本内容到工作表中去
- // 开始写入第一行,即标题栏
- if(this.beginIssue.length()>0){
- label = new Label(0, row, czName+"从"+this.beginIssue+" 到 "+this.endIssue+"期的中奖信息",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- }else{
- label = new Label(0, row, czName+"的前 "+this.czNum+" 期的中奖信息",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- }
- //第二行写表头
- label = new Label(0, row + 1, "序号",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(1, row + 1, "开奖日期",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(2, row + 1, "期号",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(3, row + 1, "中奖号码",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(4, row + 1, "销售额(元)",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- if(type!=null && type.trim().equals("ssq")){
- label = new Label(5, row + 1, "一等奖",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(7, row + 1, "二等奖",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(9, row + 1, "三等奖",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- }else if(type!=null && type.trim().equals("3d")){
- label = new Label(5, row + 1, "直选",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(7, row + 1, "组三",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(9, row + 1, "组六",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- }
- //只有双色球有奖池金额
- if(type!=null && type.trim().equals("ssq")){
- label = new Label(11, row + 1, "奖池(元)",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- }
- label = new Label(5, row + 2, "注数",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(6, row + 2, "奖金",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(7, row + 2, "注数",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(8, row + 2, "奖金",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(9, row + 2, "注数",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(10, row + 2, "奖金",format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- List<KaiJiangInfo> list = this.kaiJiangInfoList();
- int index = 1;
- int num = 2;
- DecimalFormat df = new DecimalFormat("#0.00");
- for (KaiJiangInfo kaiJiangInfo : list) {
- num = num + 1;
- label = new Label(0, num, String.valueOf((Integer.parseInt(pageNum) - 1) * 400 + (index++)),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(1, num, kaiJiangInfo.getKjDate(),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(2, num, kaiJiangInfo.getIssue(),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- if(type!=null && type.trim().equals("ssq")){
- label = new Label(3, num, kaiJiangInfo.getKjZNum() + " " + kaiJiangInfo.getKjTNum(),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- }else if(type!=null && type.trim().equals("3d")){
- label = new Label(3, num, kaiJiangInfo.getKjZNum(),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- }
- label = new Label(4, num, df.format(kaiJiangInfo.getSales()),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(5, num, String.valueOf(kaiJiangInfo.getNoteOne()),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(6, num, df.format(kaiJiangInfo.getBonusOne()),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(7, num, String.valueOf(kaiJiangInfo.getNoteTwo()),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(8, num, df.format(kaiJiangInfo.getBonusTwo()),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(9, num, String.valueOf(kaiJiangInfo.getNoteThree()),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- label = new Label(10, num, df.format(kaiJiangInfo.getBonusThree()),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- //只有双色球有奖池金额
- if(type!=null && type.trim().equals("ssq")){
- label = new Label(11, num, df.format(kaiJiangInfo.getBonusPool()),format);// 参数依次代表列数、行数、内容
- sheet.addCell(label);// 写入单元格
- }
- }
- workbook.write();
- workbook.close();
- }
- private void exportSSQExcel(OutputStream output) throws Exception {
- this.exportExcel("ssq", output);
- }
- private void export3DExcel(OutputStream output) throws Exception {
- this.exportExcel("3d", output);
- }
- }
- </pre><br>
- <br>