用jxl Workbook 建立，修改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{

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 {
                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;
                int a = 0;
                double b = 0.0;
                double c = 0.0;
                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_Desc"),colorFormat);
                    sheet.addCell(prizeFlag1);
                    Label prizeTimestamp1 = new Label(7,Row,rs.getString("Prize_Timestamp"),colorFormat);
                    sheet.addCell(prizeTimestamp1);
                    Row = Row + 1;

                    a=a+1;
                    String flag = rs.getString("Prize_Flag");
                    b = b+ Double.parseDouble(rs.getString("Bonus_Value")== null?"0":String.valueOf(df.format(Float.valueOf(rs.getString("Bonus_Value"))/100)));
                    if("1".equals(flag))
                    {
                        c = c+ Double.parseDouble(rs.getString("Bonus_Value")== null?"0":String.valueOf(df.format(Float.valueOf(rs.getString("Bonus_Value"))/100)));
                    }
                }
                Label Summary = new Label(0,Row, "合計：共" + String.valueOf(a)  + "筆，其中應兌" + String.valueOf(b) + "元，實兌" + String.valueOf(c) + "元",colorFormat);
                sheet.addCell(Summary);
            }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;
}
           

}