欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

java导出Excel 博客分类: 实战技术 java导出Excel 

程序员文章站 2024-03-18 10:38:10
...
package com.xxxpiao.datacompare.action;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang.StringUtils;
import org.apache.struts2.ServletActionContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import com.xxxpiao.admin.action.BaseAction;
import com.xxxpiao.admin.service.gplantop.PlanManagerService;
import com.xxxpiao.common.entity.enumerated.Game;
import com.xxxpiao.common.entity.issue.BetGameIssue;
import com.xxxpiao.common.entity.ticket.BetTicketChannel;
import com.xxxpiao.common.service.ticket.TicketChannelService;
import com.xxxpiao.common.utils.DateUtil;
import com.xxxpiao.datacompare.entity.AccountTicketCondition;
import com.xxxpiao.datacompare.entity.AccountTicketTotal;
import com.xxxpiao.datacompare.service.AccountTicketService;
import com.ibm.icu.math.BigDecimal;
import com.opensymphony.xwork2.ActionContext;
@Controller
@Scope("prototype")
public class AccountTicketAction extends BaseAction<Object> {

    private static final long serialVersionUID = 3903998863096688228L;
    private Logger logger = LoggerFactory.getLogger(getClass());
	@Autowired
	private PlanManagerService planManagerService;
	private AccountTicketCondition accountTicketCondition;
	private List<Game> games;
	/** 显示最近20期 */
	private static final int ISSUE_SHOW_SIZE = 20;
	
	/***************************固化数组************************************/
	private Integer[] gameIds;
	private Integer[] ids;
	private String[] issueNos;
	private String[] ticketChannelNames;
	private Integer[] ticketChannelIds;
	private Calendar[] printDates;
	private Calendar[] sendDates;
	private Long[] ownTicketCnts;
	private Double[] ownAmounts;
	private Long[] thirdTicketCnts;
	private Double[] thirdAmounts;
//	private Double[] ownPretaxPrizes;
	private Double[] ownPostaxPrizes;
//	private Double[] thirdPretaxPrizes;
	private Double[] thirdPostaxPrizes;
	/****************************固化数组**********************************/
	@Autowired
	private AccountTicketService accountTicketService;
	private List<AccountTicketTotal> accountTicketTotalList;
	@Autowired
	private TicketChannelService ticketChannelService;
	private List<BetTicketChannel> ticketChannelList;
//	private List<BetGameIssue> betGameIssues;
	
	private Double ownTicketCntTotal; 
	private Double ownAmountTotal;
//	private Double ownPretaxPrizeTotal; 
	private Double ownPostaxPrizeTotal; 
	private Double thirdTicketCntTotal; 
	private Double thirdAmountTotal; 
//	private Double thirdPretaxPrizeTotal; 
	private Double thirdPostaxPrizeTotal; 
	private Double ownThirdTicketCntDifferTotal;
	private Double ownThirdAmountDifferTotal;
	private Double ownThirdPostaxPrizeDifferTotal;
	
	/**
	 * 进入出票汇总查询首页
	 * @return
	 */
	public String list(){
		games=Game.getAll();
//		betGameIssues=planManagerService.getBetGameIssueList(
//				Game.findByIndex(accountTicketCondition.getGameId()), ISSUE_SHOW_SIZE);
		ticketChannelList=ticketChannelService.queryAllChannel();
		return SUCCESS;
	}
	/**
	 * 查询出票汇总
	 * @return
	 */
    public String findTicketTotal(){
    	games=Game.getAll();
//    	if(accountTicketCondition.getGameId()!=null&&accountTicketCondition.getGameId()!=-1){
//    		betGameIssues=planManagerService.getBetGameIssueList(
//    				Game.findByIndex(accountTicketCondition.getGameId()), ISSUE_SHOW_SIZE);
//    	}
		ticketChannelList=ticketChannelService.queryAllChannel();
    	Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("ticketChannelId", accountTicketCondition.getTicketChannelId());
		if(accountTicketCondition.getGroupType()!=null){
			paramMap.put("groupType", accountTicketCondition.getGroupType());
		}
		if(accountTicketCondition.getIssueNoStart()!=null){
			paramMap.put("issueNoStart", accountTicketCondition.getIssueNoStart());
		}
		if(accountTicketCondition.getIssueNoEnd()!=null){
			paramMap.put("issueNoEnd", accountTicketCondition.getIssueNoEnd());
		}
		if(accountTicketCondition.getGameId()!=null){
			paramMap.put("gameId", accountTicketCondition.getGameId());
		}
		if(accountTicketCondition.getPrintTimeStart()!=null){
			paramMap.put("printTimeStart", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accountTicketCondition.getPrintTimeStart().getTime()));
//			paramMap.put("printTimeStart", DateUtil.toYYYY_MM_DD(accountTicketCondition.getPrintTimeStart()));
		}
		if(accountTicketCondition.getPrintTimeEnd()!=null){
			paramMap.put("printTimeEnd", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accountTicketCondition.getPrintTimeEnd().getTime()));
//			paramMap.put("printTimeEnd", DateUtil.toYYYY_MM_DD(DateUtil.add(accountTicketCondition.getPrintTimeEnd(), Calendar.DATE, 1)));
		}
		if(accountTicketCondition.getSendTimeStart()!=null){
			paramMap.put("sendTimeStart", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accountTicketCondition.getSendTimeStart().getTime()));
//			paramMap.put("sendTimeStart", DateUtil.toYYYY_MM_DD(accountTicketCondition.getSendTimeStart()));
		}
		if(accountTicketCondition.getSendTimeEnd()!=null){
			paramMap.put("sendTimeEnd", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accountTicketCondition.getSendTimeEnd().getTime()));
//			paramMap.put("sendTimeEnd", DateUtil.toYYYY_MM_DD(DateUtil.add(accountTicketCondition.getSendTimeEnd(), Calendar.DATE, 1)));
		}
		accountTicketTotalList=accountTicketService.queryAccountTicketTotal(paramMap);
		BigDecimal ownCnt =new BigDecimal(0);
		BigDecimal ownAmount =new BigDecimal(0);
//		BigDecimal ownPretaxPrize =new BigDecimal(0);
		BigDecimal ownPostaxPrize =new BigDecimal(0);
		BigDecimal thirdCnt =new BigDecimal(0);
		BigDecimal thirdAmount =new BigDecimal(0);
//		BigDecimal thirdPretaxPrize =new BigDecimal(0);
		BigDecimal thirdPostaxPrize =new BigDecimal(0);
		for(AccountTicketTotal accountTicketTotal:accountTicketTotalList){
			ownCnt=ownCnt.add(new BigDecimal(accountTicketTotal.getOwnTicketCnt()));
			ownAmount=ownAmount.add(new BigDecimal(accountTicketTotal.getOwnAmount()));
//			ownPretaxPrize=ownPretaxPrize.add(new BigDecimal(accountTicketTotal.getOwnPretaxPrize()));
			ownPostaxPrize=ownPostaxPrize.add(new BigDecimal(accountTicketTotal.getOwnPostaxPrize()));
			thirdCnt=thirdCnt.add(new BigDecimal(accountTicketTotal.getThirdTicketCnt()));
			thirdAmount=thirdAmount.add(new BigDecimal(accountTicketTotal.getThirdAmount()));
//			thirdPretaxPrize=thirdPretaxPrize.add(new BigDecimal(accountTicketTotal.getThirdPretaxPrize()));
			thirdPostaxPrize=thirdPostaxPrize.add(new BigDecimal(accountTicketTotal.getThirdPostaxPrize()));
			BigDecimal ownCount =new BigDecimal(accountTicketTotal.getOwnTicketCnt());
			BigDecimal ownAmt =new BigDecimal(accountTicketTotal.getOwnAmount());
			BigDecimal ownPtxPrize =new BigDecimal(accountTicketTotal.getOwnPostaxPrize());
			
			BigDecimal thirdCount =new BigDecimal(accountTicketTotal.getThirdTicketCnt());
			BigDecimal thirdAmt =new BigDecimal(accountTicketTotal.getThirdAmount());
			BigDecimal thirdPtxPrize =new BigDecimal(accountTicketTotal.getThirdPostaxPrize());
			accountTicketTotal.setOwnThirdCntsDiffer(ownCount.subtract(thirdCount).doubleValue());
			accountTicketTotal.setOwnThirdAmountsDiffer(ownAmt.subtract(thirdAmt).doubleValue());
			accountTicketTotal.setOwnThirdPostaxPrizesDiffer(ownPtxPrize.subtract(thirdPtxPrize).doubleValue());
		}
		ownTicketCntTotal=ownCnt.doubleValue();
		thirdTicketCntTotal=thirdCnt.doubleValue();
		ownThirdTicketCntDifferTotal=ownCnt.subtract(thirdCnt).doubleValue();
		ownAmountTotal=ownAmount.doubleValue();
		thirdAmountTotal=thirdAmount.doubleValue();
		ownThirdAmountDifferTotal=ownAmount.subtract(thirdAmount).doubleValue();
//		ownPretaxPrizeTotal=ownPretaxPrize.doubleValue();
//		thirdPretaxPrizeTotal=thirdPretaxPrize.doubleValue();
		ownPostaxPrizeTotal=ownPostaxPrize.doubleValue();
		thirdPostaxPrizeTotal=thirdPostaxPrize.doubleValue();
		ownThirdPostaxPrizeDifferTotal=ownPostaxPrize.subtract(thirdPostaxPrize).doubleValue();
		
    	return SUCCESS;
    }
    
    public String ajaxTicketTotalExport(){
    	Double[] totals= new Double[9];
    	if(accountTicketCondition.getGameId()==-1){
    	Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("ticketChannelId", accountTicketCondition.getTicketChannelId());
		if(accountTicketCondition.getGroupType()!=null){
			paramMap.put("groupType", accountTicketCondition.getGroupType());
		}
		if(accountTicketCondition.getIssueNoStart()!=null){
			paramMap.put("issueNoStart", accountTicketCondition.getIssueNoStart());
		}
		if(accountTicketCondition.getIssueNoEnd()!=null){
			paramMap.put("issueNoEnd", accountTicketCondition.getIssueNoEnd());
		}
		if(accountTicketCondition.getGameId()!=null){
			paramMap.put("gameId", accountTicketCondition.getGameId());
		}
		if(accountTicketCondition.getPrintTimeStart()!=null){
			paramMap.put("printTimeStart", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accountTicketCondition.getPrintTimeStart().getTime()));
//			paramMap.put("printTimeStart", DateUtil.toYYYY_MM_DD(accountTicketCondition.getPrintTimeStart()));
		}
		if(accountTicketCondition.getPrintTimeEnd()!=null){
			paramMap.put("printTimeEnd", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accountTicketCondition.getPrintTimeEnd().getTime()));
//			paramMap.put("printTimeEnd", DateUtil.toYYYY_MM_DD(DateUtil.add(accountTicketCondition.getPrintTimeEnd(), Calendar.DATE, 1)));
		}
		if(accountTicketCondition.getSendTimeStart()!=null){
			paramMap.put("sendTimeStart", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accountTicketCondition.getSendTimeStart().getTime()));
//			paramMap.put("sendTimeStart", DateUtil.toYYYY_MM_DD(accountTicketCondition.getSendTimeStart()));
		}
		if(accountTicketCondition.getSendTimeEnd()!=null){
			paramMap.put("sendTimeEnd", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(accountTicketCondition.getSendTimeEnd().getTime()));
//			paramMap.put("sendTimeEnd", DateUtil.toYYYY_MM_DD(DateUtil.add(accountTicketCondition.getSendTimeEnd(), Calendar.DATE, 1)));
		}
		accountTicketTotalList=accountTicketService.queryAccountTicketTotal(paramMap);
		BigDecimal ownCnt =new BigDecimal(0);
		BigDecimal ownAmount =new BigDecimal(0);
//		BigDecimal ownPretaxPrize =new BigDecimal(0);
		BigDecimal ownPostaxPrize =new BigDecimal(0);
		BigDecimal thirdCnt =new BigDecimal(0);
		BigDecimal thirdAmount =new BigDecimal(0);
//		BigDecimal thirdPretaxPrize =new BigDecimal(0);
		BigDecimal thirdPostaxPrize =new BigDecimal(0);
		for(AccountTicketTotal accountTicketTotal:accountTicketTotalList){
			ownCnt=ownCnt.add(new BigDecimal(accountTicketTotal.getOwnTicketCnt()));
			ownAmount=ownAmount.add(new BigDecimal(accountTicketTotal.getOwnAmount()));
//			ownPretaxPrize=ownPretaxPrize.add(new BigDecimal(accountTicketTotal.getOwnPretaxPrize()));
			ownPostaxPrize=ownPostaxPrize.add(new BigDecimal(accountTicketTotal.getOwnPostaxPrize()));
			thirdCnt=thirdCnt.add(new BigDecimal(accountTicketTotal.getThirdTicketCnt()));
			thirdAmount=thirdAmount.add(new BigDecimal(accountTicketTotal.getThirdAmount()));
//			thirdPretaxPrize=thirdPretaxPrize.add(new BigDecimal(accountTicketTotal.getThirdPretaxPrize()));
			thirdPostaxPrize=thirdPostaxPrize.add(new BigDecimal(accountTicketTotal.getThirdPostaxPrize()));
			BigDecimal ownCount =new BigDecimal(accountTicketTotal.getOwnTicketCnt());
			BigDecimal ownAmt =new BigDecimal(accountTicketTotal.getOwnAmount());
			BigDecimal ownPtxPrize =new BigDecimal(accountTicketTotal.getOwnPostaxPrize());
			
			BigDecimal thirdCount =new BigDecimal(accountTicketTotal.getThirdTicketCnt());
			BigDecimal thirdAmt =new BigDecimal(accountTicketTotal.getThirdAmount());
			BigDecimal thirdPtxPrize =new BigDecimal(accountTicketTotal.getThirdPostaxPrize());
			accountTicketTotal.setOwnThirdCntsDiffer(ownCount.subtract(thirdCount).doubleValue());
			accountTicketTotal.setOwnThirdAmountsDiffer(ownAmt.subtract(thirdAmt).doubleValue());
			accountTicketTotal.setOwnThirdPostaxPrizesDiffer(ownPtxPrize.subtract(thirdPtxPrize).doubleValue());
		}
		totals[0]=ownCnt.doubleValue();
		totals[1]=thirdCnt.doubleValue();
		totals[2]=ownCnt.subtract(thirdCnt).doubleValue();
		totals[3]=ownAmount.doubleValue();
		totals[4]=thirdAmount.doubleValue();
		totals[5]=ownAmount.subtract(thirdAmount).doubleValue();
//		ownPretaxPrizeTotal=ownPretaxPrize.doubleValue();
//		thirdPretaxPrizeTotal=thirdPretaxPrize.doubleValue();
		totals[6]=ownPostaxPrize.doubleValue();
		totals[7]=thirdPostaxPrize.doubleValue();
		totals[8]=ownPostaxPrize.subtract(thirdPostaxPrize).doubleValue();
		 
//		totals[0]=ownCnt.doubleValue();
//		totals[1]=ownAmount.doubleValue();
//		totals[2]=ownPretaxPrize.doubleValue();
//		totals[3]=ownPostaxPrize.doubleValue();
//		totals[4]=thirdCnt.doubleValue();
//		totals[5]=thirdAmount.doubleValue();
//		totals[6]=thirdPretaxPrize.doubleValue();
//		totals[7]=thirdPostaxPrize.doubleValue();
//		totals[8]=thirdPostaxPrize.doubleValue();
    	}else{
    		accountTicketTotalList=new ArrayList<AccountTicketTotal>();
    		if(gameIds==null||gameIds.length<1){
    			return null;
    		}
    		for(int i=0;i<gameIds.length;i++){
        		AccountTicketTotal accountTicketTotal=new AccountTicketTotal(gameIds[i], issueNos[i],
        				ticketChannelNames[i], ownTicketCnts[i], 
        				ownAmounts[i],
        				ownPostaxPrizes[i], thirdTicketCnts[i], 
        				thirdAmounts[i], 
        				thirdPostaxPrizes[i], printDates[i],
        				sendDates[i], ticketChannelIds[i]);
        		BigDecimal ownCount =new BigDecimal(ownTicketCnts[i]);
    			BigDecimal ownAmt =new BigDecimal(ownAmounts[i]);
    			BigDecimal ownPtxPrize =new BigDecimal(ownPostaxPrizes[i]);
    			
    			BigDecimal thirdCount =new BigDecimal(thirdTicketCnts[i]);
    			BigDecimal thirdAmt =new BigDecimal(thirdAmounts[i]);
    			BigDecimal thirdPtxPrize =new BigDecimal(thirdPostaxPrizes[i]);
    			accountTicketTotal.setOwnThirdCntsDiffer(ownCount.subtract(thirdCount).doubleValue());
    			accountTicketTotal.setOwnThirdAmountsDiffer(ownAmt.subtract(thirdAmt).doubleValue());
    			accountTicketTotal.setOwnThirdPostaxPrizesDiffer(ownPtxPrize.subtract(thirdPtxPrize).doubleValue());
        		accountTicketTotalList.add(accountTicketTotal);
        	}
    		totals[0]=ownTicketCntTotal;
    		totals[1]=thirdTicketCntTotal;
    		totals[2]=ownThirdTicketCntDifferTotal;
    		totals[3]=ownAmountTotal;
    		totals[4]=thirdAmountTotal;
    		totals[5]=ownThirdAmountDifferTotal;
//    		ownPretaxPrizeTotal=ownPretaxPrize.doubleValue();
//    		thirdPretaxPrizeTotal=thirdPretaxPrize.doubleValue();
    		totals[6]=ownPostaxPrizeTotal;
    		totals[7]=thirdPostaxPrizeTotal;
    		totals[8]=ownThirdPostaxPrizeDifferTotal;
    	}
		this.exportTicketTotalReport(accountTicketTotalList, totals);
		return null;
    }
    /**
     * 保存汇总结果
     * @return
     */
    public String saveTotalResult(){
    	final List<AccountTicketTotal> accountTicketTotals=new ArrayList<AccountTicketTotal>();
    	long conditionId=accountTicketService.generateConditionSequence();
    	accountTicketCondition.setId(conditionId);
    	accountTicketCondition.setCreateUser(getCurrentAccount());
    	accountTicketCondition.setStatus(0);
    
    	if(accountTicketCondition.getIssueNoStart()!=null
    			&&!"".equals(accountTicketCondition.getIssueNoStart())
    			&&accountTicketCondition.getIssueNoEnd()!=null
    			&&!"".equals(accountTicketCondition.getIssueNoEnd())){
    		accountTicketCondition.setIssueNo(accountTicketCondition.getIssueNoStart()+"--"+accountTicketCondition.getIssueNoEnd());
    	}
    	final AccountTicketCondition tempCondition=accountTicketCondition;
    	for(int i=0;i<gameIds.length;i++){
    		AccountTicketTotal accountTicketTotal=new AccountTicketTotal(gameIds[i], issueNos[i],
    				ticketChannelNames[i], ownTicketCnts[i], 
    				ownAmounts[i],
    				ownPostaxPrizes[i], thirdTicketCnts[i], 
    				thirdAmounts[i], 
    				thirdPostaxPrizes[i], printDates[i],
    				sendDates[i], ticketChannelIds[i]);
    		accountTicketTotal.setConditionId(conditionId);
    		accountTicketTotal.setCreateUser(getCurrentAccount());
    		accountTicketTotal.setStatus(0);
    		accountTicketTotals.add(accountTicketTotal);
    	}
    	accountTicketService.saveConditionAndTotals(tempCondition, accountTicketTotals);
    	games=Game.getAll();
//		betGameIssues=planManagerService.getBetGameIssueList(
//				Game.findByIndex(accountTicketCondition.getGameId()), ISSUE_SHOW_SIZE);
		ticketChannelList=ticketChannelService.queryAllChannel();
    	return SUCCESS;
    }
    /**
     * 进入出票汇总结果管理首页
     * @return
     */
    public String enterTicketResultManager(){
    	games=Game.getAll();
    	return SUCCESS;
    }
    /**
     * 查询出票汇总条件
     * @return
     */
    public String queryTicketCondition(){
    	games=Game.getAll();
    	page=accountTicketService.queryTicketConditionDataPage(accountTicketCondition, getPage());
    	return SUCCESS;
    }
    
    /**
     * 修改出票汇总结果数据
     */
    public  String editTicketResultUI(){
    	accountTicketCondition=accountTicketService.queryTicketConditionById(accountTicketCondition.getId());
    	accountTicketTotalList=accountTicketService.queryTicketTotalByConditionId(accountTicketCondition.getId());
    	return SUCCESS;
    }
    /**
     * 修改出票汇总结果数据
     */
    public  String editTicketResult(){
    	final List<AccountTicketTotal> accountTicketTotals=new ArrayList<AccountTicketTotal>();
    	for(int i=0;i<ids.length;i++){
    		AccountTicketTotal accountTicketTotal=new AccountTicketTotal();
    		accountTicketTotal.setId(ids[i]);
    		accountTicketTotal.setThirdAmount(thirdAmounts[i]);
    		accountTicketTotal.setThirdTicketCnt(thirdTicketCnts[i]);
//    		accountTicketTotal.setThirdPretaxPrize(thirdPretaxPrizes[i]);
    		accountTicketTotal.setThirdPostaxPrize(thirdPostaxPrizes[i]);
    		accountTicketTotals.add(accountTicketTotal);
    	}
    	accountTicketService.updateTicketTotals(accountTicketTotals);
    	accountTicketCondition=accountTicketService.queryTicketConditionById(accountTicketCondition.getId());
    	accountTicketTotalList=accountTicketService.queryTicketTotalByConditionId(accountTicketCondition.getId());
    	return SUCCESS;
    }
    /**
     * 修改出票汇总结果数据
     */
    public  String confirmTicketResult(){
    	final List<AccountTicketTotal> accountTicketTotals=new ArrayList<AccountTicketTotal>();
    	final AccountTicketCondition tempCondition=accountTicketCondition;
    	tempCondition.setStatus(2);
    	for(int i=0;i<ids.length;i++){
    		AccountTicketTotal accountTicketTotal=new AccountTicketTotal();
    		accountTicketTotal.setId(ids[i]);
    		accountTicketTotal.setStatus(2);
    		accountTicketTotals.add(accountTicketTotal);
    	}
    	accountTicketService.updateTicketTotalStatuss(accountTicketTotals,tempCondition);
    	accountTicketCondition=accountTicketService.queryTicketConditionById(accountTicketCondition.getId());
    	accountTicketTotalList=accountTicketService.queryTicketTotalByConditionId(accountTicketCondition.getId());
    	return SUCCESS;
    }
    
    /**
     * 出票汇总详情
     */
    public  String viewTicketResult(){
    	accountTicketCondition=accountTicketService.queryTicketConditionById(accountTicketCondition.getId());
    	accountTicketTotalList=accountTicketService.queryTicketTotalByConditionId(accountTicketCondition.getId());
    	return SUCCESS;
    }
    
	/**
	 * 通过AJAX方式获取彩种期数
	 * 
	 * @return
	 */
	public String ajaxGetIssue() {
		Game game = Game.findByIndex(accountTicketCondition.getGameId());
		List<BetGameIssue> issueList = planManagerService.getBetGameIssueList(
				game, ISSUE_SHOW_SIZE);
		List<String> issueStr = new ArrayList<String>(issueList.size());	
		for (BetGameIssue bgi : issueList) {
			issueStr.add(bgi.getIssueNo());
		}		
		ActionContext ctx = ActionContext.getContext();
		HttpServletResponse response = (HttpServletResponse) ctx
				.get(ServletActionContext.HTTP_RESPONSE);
		try {
			response.getWriter().write(StringUtils.join(issueStr, ","));
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
     * 删除出票汇总结果数据
     */
    public  String deleteTicketResult(){
    	final List<AccountTicketTotal> accountTicketTotals=accountTicketService.queryTicketTotalByConditionId(accountTicketCondition.getId());
    	final AccountTicketCondition tempCondition=accountTicketCondition;
    	tempCondition.setStatus(1);
    	for(AccountTicketTotal accountTicketTotal:accountTicketTotals){
    		accountTicketTotal.setStatus(1);
    	}
    	accountTicketService.updateTicketTotalStatuss(accountTicketTotals,tempCondition);
    	games=Game.getAll();
    	return SUCCESS;
    }
	
    /**
     * 表格首行样式
     * @return
     * @throws Exception
     * @create_time 2013-8-29 下午5:52:44
     */
    private WritableCellFormat getHeaderFmt() throws Exception {
        WritableFont font = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD);// 定义字体 
        WritableCellFormat format = new WritableCellFormat(font);
        format.setAlignment(Alignment.CENTRE);// 左右居中 
        format.setVerticalAlignment(VerticalAlignment.CENTRE);// 上下居中 
        format.setBackground(Colour.SEA_GREEN);// 背景 
        format.setWrap(true);
        return format;
    }

    /**
     * 表格主体样式
     * @return
     * @throws Exception
     * @create_time 2013-8-29 下午5:55:45
     */
    private WritableCellFormat getBodyFmt() throws Exception {
        WritableFont font = new WritableFont(WritableFont.TIMES, 12);// 定义字体 
        WritableCellFormat format = new WritableCellFormat(font);
        format.setVerticalAlignment(VerticalAlignment.CENTRE);// 上下居中
        return format;
    }

    /**
     * 表格尾行样式
     * @return
     * @throws Exception
     * @create_time 2013-8-29 下午5:52:44
     */
    private WritableCellFormat getFooterFmt() throws Exception {
        WritableFont font = new WritableFont(WritableFont.TIMES, 14);// 定义字体 
        WritableCellFormat format = new WritableCellFormat(font);
        format.setVerticalAlignment(VerticalAlignment.CENTRE);// 上下居中 
        format.setBackground(Colour.ORANGE);
        return format;
    }
    
    
    /**
     * 出票汇总Excel导出
     * @create_time 2014-04-22 
     */
    private void exportTicketTotalReport(List<AccountTicketTotal> accountTicketTotals,Double[] totals) {
        try {
            HttpServletResponse response = ServletActionContext.getResponse();
            response.setContentType("application/vnd.ms-excel");
            ServletOutputStream os = response.getOutputStream();
            // 创建EXCEL文档
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            // 生成第一个工作簿
            WritableSheet sheet = workbook.createSheet("出票汇总", 0);
            WritableCellFormat format = getHeaderFmt();
            int rowIndex = 0;
            int cellIndex = 0;
            sheet.addCell(new Label(cellIndex, rowIndex, "彩种", format));
            sheet.setColumnView(cellIndex, 15);
            sheet.addCell(new Label(++cellIndex, rowIndex, "彩期", format));
            sheet.setColumnView(cellIndex, 15);
            sheet.addCell(new Label(++cellIndex, rowIndex, "出票商", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "打票日期", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "送票日期", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "系统出票总数量", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "第三方出票总数量", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "出票数量差异", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "系统出票总金额", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "第三方出票总金额", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "出票金额差异", format));
            sheet.setColumnView(cellIndex, 20);
//            sheet.addCell(new Label(++cellIndex, rowIndex, "系统税前中奖总金额", format));
//            sheet.setColumnView(cellIndex, 20);
//            sheet.addCell(new Label(++cellIndex, rowIndex, "第三方税前中奖总金额", format));
//            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "系统税后中奖总金额", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "第三方税后中奖总金额", format));
            sheet.setColumnView(cellIndex, 20);
            sheet.addCell(new Label(++cellIndex, rowIndex, "税后中奖金额差异", format));
            sheet.setColumnView(cellIndex, 20);
            
            format = getBodyFmt();
            if (accountTicketTotals != null && accountTicketTotals.size()>0) {
                for (AccountTicketTotal accountTicketTotal: accountTicketTotals) {
                    rowIndex++;
                    sheet.setRowView(rowIndex, 350);
                    cellIndex = 0;
                    sheet.addCell(new Label(cellIndex, rowIndex,Game.findByIndex(accountTicketTotal.getGameId()).getDescription(), format));
                    sheet.addCell(new Label(++cellIndex, rowIndex,accountTicketTotal.getIssueNo(), format));
                    sheet.addCell(new Label(++cellIndex, rowIndex,accountTicketTotal.getTicketChannelName(), format));
                    sheet.addCell(new Label(++cellIndex, rowIndex,accountTicketTotal.getPrintDate()==null?"":DateUtil.toYYYY_MM_DD(accountTicketTotal.getPrintDate()), format));
                    sheet.addCell(new Label(++cellIndex, rowIndex,accountTicketTotal.getSendDate()==null?"":DateUtil.toYYYY_MM_DD(accountTicketTotal.getSendDate()), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getOwnTicketCnt(), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getThirdTicketCnt(), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getOwnThirdCntsDiffer(), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getOwnAmount(), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getThirdAmount(), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getOwnThirdAmountsDiffer(), format));
//                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getOwnPretaxPrize(), format));
//                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getThirdPretaxPrize(), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getOwnPostaxPrize(), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getThirdPostaxPrize(), format));
                    sheet.addCell(new Number(++cellIndex, rowIndex, accountTicketTotal.getOwnThirdPostaxPrizesDiffer(), format));
                }
                format = getFooterFmt();
                rowIndex++;
                cellIndex = 0;
                sheet.addCell(new Label(cellIndex, rowIndex,"", format));
                sheet.addCell(new Label(++cellIndex, rowIndex,"", format));
                sheet.addCell(new Label(++cellIndex, rowIndex,"", format));
                sheet.addCell(new Label(++cellIndex, rowIndex,"", format));
                sheet.addCell(new Label(++cellIndex, rowIndex,"合计:", format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[0], format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[1], format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[2], format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[3], format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[4], format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[5], format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[6], format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[7], format));
                sheet.addCell(new Number(++cellIndex, rowIndex, totals[8], format));
            }
            
            workbook.write();
            workbook.close();
            os.flush();
            os.close();
            os=null; 
        } catch (Exception e) {
            logger.error("导出出票汇总数据EXCEL报表异常", e);
        }
    }

	
	
	
	public PlanManagerService getPlanManagerService() {
		return planManagerService;
	}

	public void setPlanManagerService(PlanManagerService planManagerService) {
		this.planManagerService = planManagerService;
	}

	public AccountTicketCondition getAccountTicketCondition() {
		return accountTicketCondition;
	}

	public void setAccountTicketCondition(
			AccountTicketCondition accountTicketCondition) {
		this.accountTicketCondition = accountTicketCondition;
	}

	public List<Game> getGames() {
		return games;
	}

	public void setGames(List<Game> games) {
		this.games = games;
	}

	public Integer[] getGameIds() {
		return gameIds;
	}

	public void setGameIds(Integer[] gameIds) {
		this.gameIds = gameIds;
	}

	public String[] getIssueNos() {
		return issueNos;
	}

	public void setIssueNos(String[] issueNos) {
		this.issueNos = issueNos;
	}

	public String[] getTicketChannelNames() {
		return ticketChannelNames;
	}

	public void setTicketChannelNames(String[] ticketChannelNames) {
		this.ticketChannelNames = ticketChannelNames;
	}

	public Integer[] getTicketChannelIds() {
		return ticketChannelIds;
	}

	public void setTicketChannelIds(Integer[] ticketChannelIds) {
		this.ticketChannelIds = ticketChannelIds;
	}

	public Calendar[] getPrintDates() {
		return printDates;
	}

	public void setPrintDates(Calendar[] printDates) {
		this.printDates = printDates;
	}

	public Calendar[] getSendDates() {
		return sendDates;
	}

	public void setSendDates(Calendar[] sendDates) {
		this.sendDates = sendDates;
	}

	public Long[] getOwnTicketCnts() {
		return ownTicketCnts;
	}

	public void setOwnTicketCnts(Long[] ownTicketCnts) {
		this.ownTicketCnts = ownTicketCnts;
	}

	

	public Long[] getThirdTicketCnts() {
		return thirdTicketCnts;
	}

	public void setThirdTicketCnts(Long[] thirdTicketCnts) {
		this.thirdTicketCnts = thirdTicketCnts;
	}



	public Double[] getOwnAmounts() {
		return ownAmounts;
	}

	public void setOwnAmounts(Double[] ownAmounts) {
		this.ownAmounts = ownAmounts;
	}

	public Double[] getThirdAmounts() {
		return thirdAmounts;
	}

	public void setThirdAmounts(Double[] thirdAmounts) {
		this.thirdAmounts = thirdAmounts;
	}

//	public Double[] getOwnPretaxPrizes() {
//		return ownPretaxPrizes;
//	}
//
//	public void setOwnPretaxPrizes(Double[] ownPretaxPrizes) {
//		this.ownPretaxPrizes = ownPretaxPrizes;
//	}

	public Double[] getOwnPostaxPrizes() {
		return ownPostaxPrizes;
	}

	public void setOwnPostaxPrizes(Double[] ownPostaxPrizes) {
		this.ownPostaxPrizes = ownPostaxPrizes;
	}

//	public Double[] getThirdPretaxPrizes() {
//		return thirdPretaxPrizes;
//	}
//
//	public void setThirdPretaxPrizes(Double[] thirdPretaxPrizes) {
//		this.thirdPretaxPrizes = thirdPretaxPrizes;
//	}

	public Double[] getThirdPostaxPrizes() {
		return thirdPostaxPrizes;
	}

	public void setThirdPostaxPrizes(Double[] thirdPostaxPrizes) {
		this.thirdPostaxPrizes = thirdPostaxPrizes;
	}

	public List<AccountTicketTotal> getAccountTicketTotalList() {
		return accountTicketTotalList;
	}

	public void setAccountTicketTotalList(
			List<AccountTicketTotal> accountTicketTotalList) {
		this.accountTicketTotalList = accountTicketTotalList;
	}

	public List<BetTicketChannel> getTicketChannelList() {
		return ticketChannelList;
	}

	public void setTicketChannelList(List<BetTicketChannel> ticketChannelList) {
		this.ticketChannelList = ticketChannelList;
	}

	public TicketChannelService getTicketChannelService() {
		return ticketChannelService;
	}

	public void setTicketChannelService(TicketChannelService ticketChannelService) {
		this.ticketChannelService = ticketChannelService;
	}


	public AccountTicketService getAccountTicketService() {
		return accountTicketService;
	}

	public void setAccountTicketService(AccountTicketService accountTicketService) {
		this.accountTicketService = accountTicketService;
	}

//	public List<BetGameIssue> getBetGameIssues() {
//		return betGameIssues;
//	}
//	public void setBetGameIssues(List<BetGameIssue> betGameIssues) {
//		this.betGameIssues = betGameIssues;
//	}
	public Integer[] getIds() {
		return ids;
	}
	public void setIds(Integer[] ids) {
		this.ids = ids;
	}
	public Double getOwnTicketCntTotal() {
		return ownTicketCntTotal;
	}
	public void setOwnTicketCntTotal(Double ownTicketCntTotal) {
		this.ownTicketCntTotal = ownTicketCntTotal;
	}
	
	public Double getThirdTicketCntTotal() {
		return thirdTicketCntTotal;
	}
	public void setThirdTicketCntTotal(Double thirdTicketCntTotal) {
		this.thirdTicketCntTotal = thirdTicketCntTotal;
	}
	public Double getOwnAmountTotal() {
		return ownAmountTotal;
	}
	public void setOwnAmountTotal(Double ownAmountTotal) {
		this.ownAmountTotal = ownAmountTotal;
	}
//	public Double getOwnPretaxPrizeTotal() {
//		return ownPretaxPrizeTotal;
//	}
//	public void setOwnPretaxPrizeTotal(Double ownPretaxPrizeTotal) {
//		this.ownPretaxPrizeTotal = ownPretaxPrizeTotal;
//	}
	public Double getOwnPostaxPrizeTotal() {
		return ownPostaxPrizeTotal;
	}
	public void setOwnPostaxPrizeTotal(Double ownPostaxPrizeTotal) {
		this.ownPostaxPrizeTotal = ownPostaxPrizeTotal;
	}
	public Double getThirdAmountTotal() {
		return thirdAmountTotal;
	}
	public void setThirdAmountTotal(Double thirdAmountTotal) {
		this.thirdAmountTotal = thirdAmountTotal;
	}
//	public Double getThirdPretaxPrizeTotal() {
//		return thirdPretaxPrizeTotal;
//	}
//	public void setThirdPretaxPrizeTotal(Double thirdPretaxPrizeTotal) {
//		this.thirdPretaxPrizeTotal = thirdPretaxPrizeTotal;
//	}
	public Double getThirdPostaxPrizeTotal() {
		return thirdPostaxPrizeTotal;
	}
	public void setThirdPostaxPrizeTotal(Double thirdPostaxPrizeTotal) {
		this.thirdPostaxPrizeTotal = thirdPostaxPrizeTotal;
	}
	public Double getOwnThirdTicketCntDifferTotal() {
		return ownThirdTicketCntDifferTotal;
	}
	public void setOwnThirdTicketCntDifferTotal(Double ownThirdTicketCntDifferTotal) {
		this.ownThirdTicketCntDifferTotal = ownThirdTicketCntDifferTotal;
	}
	public Double getOwnThirdAmountDifferTotal() {
		return ownThirdAmountDifferTotal;
	}
	public void setOwnThirdAmountDifferTotal(Double ownThirdAmountDifferTotal) {
		this.ownThirdAmountDifferTotal = ownThirdAmountDifferTotal;
	}
	public Double getOwnThirdPostaxPrizeDifferTotal() {
		return ownThirdPostaxPrizeDifferTotal;
	}
	public void setOwnThirdPostaxPrizeDifferTotal(
			Double ownThirdPostaxPrizeDifferTotal) {
		this.ownThirdPostaxPrizeDifferTotal = ownThirdPostaxPrizeDifferTotal;
	}
	


	
 
}

相关标签: java 导出 Excel