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;
}
}
上一篇： 被动web扫描器 xray的使用
下一篇： ES7、ES8、ES9、ES10新特性
推荐阅读
-
java导出Excel 博客分类： 实战技术 java导出Excel
-
java加密解密 博客分类： 实战技术 java互联网加密解密
-
数据库数据导入到excel,源代码 博客分类： java 数据库excel源代码
-
Gridview导出到EXCEL问题点 博客分类： ASP.NET asp.net
-
在UpdatePanel下导出excel出现无法分析从服务器收到的消息。之所以出现此错误，常见的原因是: 在通过调用 Response.Write() 修改响应 博客分类： ASP.NET excel
-
POI 导出Excel表格 输出数据流 博客分类： Java POI 导出Excel表格 输出数据流
-
java poi导出Excel Demo 博客分类： Java java poi导出Excel Demo
-
POI导出Excel表格，去掉数字框的左上角绿色的小三角 博客分类： Java POI导出Excel表格去掉数字框的左上角绿色的小三角
-
JSP设置头信息导出Excel 博客分类： J2EE exceljsp导出excelhtmljava
-
Java程序实现导出Excel的方法(支持IE低版本)