Easyui excel表格的导出

package com.caitong.activity.action;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.struts2.ServletActionContext;

import com.caitong.activity.util.DBUtil;
import com.opensymphony.xwork2.Action;

public class ExportExcelAction implements Action{
    /*
     * 生成excel文件
     */
    public void createExcel() {
        try{
            HttpServletRequest req = ServletActionContext.getRequest();
            HttpServletResponse response = ServletActionContext.getResponse();
            req.setCharacterEncoding("UTF-8");
            String title1 = req.getParameter("fileName");
            //最近看了一下，发现解决乱码不用关心浏览器，可以简单搞定  
              String downLoadName = new String(title1.getBytes("gbk"), "iso8859-1");  
              response.setContentType("octets/stream");  
              response.addHeader("Content-Type", "text/html; charset=utf-8");  
              response.setHeader("Content-Disposition", "attachment;filename=" + downLoadName+".xls");  
            //创建输出流
            OutputStream os = response.getOutputStream();
            //创建工作簿
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            //创建新的一页（说白了就是第一个表）
            WritableSheet sheet = workbook.createSheet("Sheet0", 0);
            //构造表头
            //添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
            sheet.mergeCells(0, 0, 7, 0);
            //设置字体的种类和黑体显示，字体为Arial字号的大小为10，采用黑体显示
            WritableFont bold = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
            //生成一个单元格样式控制对象
            WritableCellFormat titleFormate = new WritableCellFormat(bold);
            //单元格中的内容水平方向居中
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
            //单元格中的内容垂直方向居中
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
            Label title = new Label(0, 0, title1, titleFormate);
            //表头的内容填到设置的单元格中
            sheet.addCell(title);
            //设第一行的行高
            sheet.setRowView(0, 600, false);
            //设置单元格每一列的宽度
            for (int i = 0; i <= 7; i++) {
                sheet.setColumnView(i, 20);
            }
            //创建需要显示的具体内容
            WritableFont color = new WritableFont(WritableFont.ARIAL);//选择字体
            color.setColour(Colour.BLACK);//设置字体为BLACK颜色
            WritableCellFormat colorFormat = new WritableCellFormat(color);
            //终于开始写表头列
            Label agentId = new Label(0,1,"站点编号",colorFormat);
            sheet.addCell(agentId);
            Label phone = new Label(1,1,"手机号",colorFormat);
            sheet.addCell(phone);
            Label userId = new Label(2,1,"身份证号",colorFormat);
            sheet.addCell(userId);
            Label description = new Label(3,1,"奖金说明",colorFormat);
            sheet.addCell(description);
            Label bonusDetial = new Label(4,1,"奖金金额",colorFormat);
            sheet.addCell(bonusDetial);
            Label addTimestamp = new Label(5,1,"参与时间",colorFormat);
            sheet.addCell(addTimestamp);
            Label prizeFlag = new Label(6,1,"兑奖状态",colorFormat);
            sheet.addCell(prizeFlag);
            Label prizeTimestamp = new Label(7,1,"兑奖时间",colorFormat);
            sheet.addCell(prizeTimestamp);

            //调用存储过程
                //使用action上下文获取请求
                HttpServletRequest request = ServletActionContext.getRequest();
                //获取前台的的输入参数
                String luckyDrawId = request.getParameter("luckyDrawId");
                //获取数据库的链接
                Connection conn = DBUtil.getConnection();
                CallableStatement call = null;
                ResultSet rs = null;
                try {
                //调用oracle数据库中的存储过程
                    call  = conn.prepareCall("{call PK_REPORT.Qr_Excel_Data(?,?)}");
                    call.setObject(1, luckyDrawId);
                    call.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
                    call.execute();
                    rs = (ResultSet) call.getObject(2);
                    //设置价格（金额）的格式
                    java.text.DecimalFormat df = new java.text.DecimalFormat("##0.00");
                    //游标循环
                    int Row = 2;
                    while(rs.next()){
                        //因为列名不能重复，所以起名字的时候随意了一点
                        Label agentId1 = new Label(0,Row,rs.getString("Agent_ID"),colorFormat);
                        sheet.addCell(agentId1);
                        Label phone1 = new Label(1,Row,rs.getString("Phone"),colorFormat);
                        sheet.addCell(phone1);
                        Label personalId = new Label(2,Row,rs.getString("Personal_ID"),colorFormat);
                        sheet.addCell(personalId);
                        Label description1 = new Label(3,Row,rs.getString("Description"),colorFormat);
                        sheet.addCell(description1);
                        Label bonusValue = new Label(4,Row,rs.getString("Bonus_Value") == null?"0":String.valueOf(df.format(Float.valueOf(rs.getString("Bonus_Value"))/100)),colorFormat);
                        sheet.addCell(bonusValue);
                        Label joinTimestamp = new Label(5,Row,rs.getString("Join_Timestamp"),colorFormat);
                        sheet.addCell(joinTimestamp);
                        Label prizeFlag1 = new Label(6,Row,rs.getString("Prize_Flag"),colorFormat);
                        sheet.addCell(prizeFlag1);
                        Label prizeTimestamp1 = new Label(7,Row,rs.getString("Prize_Timestamp"),colorFormat);
                        sheet.addCell(prizeTimestamp1);
                        Row = Row + 1;
                    }
                }catch (Exception e) {
                    e.printStackTrace();
                }
            //把创建的内容写入到输出流中，并关闭输出流
            workbook.write();
            workbook.close();
            os.flush();
            os.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }


    @Override
    public String execute() throws Exception {
        // TODO Auto-generated method stub
        return null;
    }

}


----------
//前台jsp代码`
<!-- 触发事件的按钮 -->
            <div id="excel_toolbar">
                <a href="javascript:void(0);" class="easyui-linkbutton" 
                    iconCls="icon-table" plain="true" onclick="exptInfo()">
                    导出Excel
                </a>
            </div> 
            <!-- 隐藏的form表单 -->
            <form id='excelForm' action="ExportExcelAction.action"
                    method="post">
                    <input id="fileName" name='fileName' value="2016-07进店送彩票活动" type="hidden" /> 
                    <input id="luckyDrawId" name='luckyDrawId'  type="hidden" /> 
            </form>
             /* 获取页面参数目的向后台请求参数 */
            function exptInfo(){
                var fileName =  $("#luckyDrawId").combobox('getText');
                var luckyDrawId = $("#luckyDrawId").combobox('getValue');
                //给参数赋值
                document.forms["excelForm"].luckyDrawId.value = luckyDrawId;
                document.getElementById("excelForm").submit();
            }  

这里写代码片

----------
struts.xml中的配置

<action name="ExportExcelAction" class="com.caitong.activity.action.ExportExcelAction" method="createExcel">

            </action>

//oracle数据库中的存储过程
Procedure Qr_Excel_Data( iLucky_Draw_Id In Varchar2,
                         oInfo Out Sys_Refcursor ) Is
Begin
     Open oInfo For
          Select A.Agent_ID, --站点号
                 A.Phone, --手机号
                 A.Personal_ID, --身份证号
                 A.Description, --奖金说明
                 A.Bonus_Value, --奖金金额
                 To_Char( A.Join_Timestamp, 'YYYY-MM-DD HH24:MI:SS' ) Join_Timestamp, --参与时间
                 Decode( A.Prize_Flag, 1, '已兑奖', '未兑奖' ) Prize_Flag, --兑奖状态
                 To_Char( Prize_Timestamp, 'YYYY-MM-DD HH24:MI:SS' ) Prize_Timestamp --兑奖时间
                 From( Select A.Agent_ID, B.Phone, B.Personal_Id, A.Join_Timestamp, A.Prize_Timestamp, A.Prize_Flag,
                              C.Description, C.Bonus_Value,
                              Row_Number() Over( Order By A.Join_Timestamp Desc ) Row_Pos
                              From Tb_Lucky_Draw A,
                                   Tb_User_Info B,
                                   Tb_Dic_Bonus C
                              Where( A.Lucky_Draw_Id = iLucky_Draw_Id ) And
                                   ( A.Wechat_Id = B.Wechat_Id(+) ) And
                                   ( A.Lucky_Draw_Id = C.Lucky_Draw_Id ) And
                                   ( A.Bonus_Id = C.Bonus_Id ) ) A
                 Order By A.Row_Pos;

     Return;
End Qr_Excel_Data;

end PK_Report;