用WritableSheet编辑多个sheet下载的问题

来源：互联网发布：房子短租软件编辑：程序博客网时间：2024/06/06 18:24

//四个sheet要下载的内容

//结算单

List<TblMchtShareSumDto> dtoList1 = termBO.downMerchantProfitStatisticsByForm2(form,session);
//分润相关交易明细
List<TblMchtShareSumDto> dtoList2 = termBO.downMerchantProfitStatisticsByForm2(form,session);
//分润日结下发明细
List<TblMchtShareSumDto> dtoList3 = termBO.downMerchantProfitStatisticsByForm2(form,session);
//其他调账数据明细
List<TblMchtShareSumDto> dtoList4 = termBO.downMerchantProfitStatisticsByForm2(form,session);

//创建文件本地文件
String time = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()); //时间
String fileName = "E:\\"+time+"分润确认单.xlsx";

WritableWorkbook wwb = null;
try {
wwb = Workbook.createWorkbook(new File(fileName));
} catch (IOException e1) {
e1.printStackTrace();
}
File dbfFile = new File(fileName);
if (!dbfFile.exists() || dbfFile.isDirectory()) {
try {
dbfFile.createNewFile();
} catch (IOException e) {
e.printStackTrace();
}
}

int sum=0;
for (int i = 0; i < 4; i++) {
WritableSheet ws = null;
//创建表格
if(i==0){
ws = wwb.createSheet("结算单", 0);
sum = dtoList1.size();
}else if(i==1){
ws = wwb.createSheet("分润相关交易明细", 1);
sum = dtoList2.size();
}else if(i==2){
ws = wwb.createSheet("分润日结下发明细", 2);
sum = dtoList3.size();
}else if(i==3){
ws = wwb.createSheet("其他调账数据明细", 3);
sum = dtoList4.size();
}
//设置表格第i列宽度
ws.setColumnView(1, 20);
ws.setColumnView(2, 20);
ws.setColumnView(3, 20);
ws.setColumnView(4, 20);
ws.setColumnView(5, 20);

int index = 0;
for (int m = 0; m < sum; m++) {
try {
if(i==0){
//合并单元格
ws.mergeCells(1, 0, 4, 0);
ws.mergeCells(1, 1, 4, 1);
ws.mergeCells(0, 3, 0, 9);

ws.mergeCells(1, 10, 4, 10);
ws.mergeCells(1, 11, 4, 11);
ws.mergeCells(1, 12, 4, 12);
ws.mergeCells(1, 13, 4, 13);
ws.mergeCells(1, 14, 4, 14);
ws.mergeCells(5, 2, 5, 9);
//Label(列, 行, 内容)
//给表格填充值
ws.addCell(new Label(0, 0, "序号"));
ws.addCell(new Label(0, 1,"1"));
ws.addCell(new Label(1, 3,"银联卡刷卡（贷记卡）"));

ws.addCell(new Label(2, 3,dtoList1.get(0).getMchtCd()));

ws.addCell(new Label(3, 3,dtoList1.get(0).getTransFee()));

ws.addCell(new Label(4, 3,dtoList1.get(0).getMchtCd()));
ws.addCell(new Label(1, 4,"银联卡刷卡（借记卡）"));
ws.addCell(new Label(2, 4,dtoList1.get(1).getMchtCd()));
ws.addCell(new Label(3, 4,dtoList1.get(1).getTransFee()));
ws.addCell(new Label(4, 4,dtoList1.get(1).getMchtCd()));
ws.addCell(new Label(1, 5,"外卡刷卡"));
ws.addCell(new Label(2, 5,dtoList1.get(2).getMchtCd()));
ws.addCell(new Label(3, 5,dtoList1.get(2).getTransFee()));
ws.addCell(new Label(4, 5,dtoList1.get(2).getMchtCd()));
ws.addCell(new Label(1, 6,"快捷支付"));
ws.addCell(new Label(2, 6,dtoList1.get(3).getMchtCd()));
ws.addCell(new Label(3, 6,dtoList1.get(3).getTransFee()));
ws.addCell(new Label(4, 6,dtoList1.get(3).getMchtCd()));
ws.addCell(new Label(1, 7,"微信扫码"));
ws.addCell(new Label(2, 7,dtoList1.get(4).getMchtCd()));
ws.addCell(new Label(3, 7,dtoList1.get(4).getTransFee()));
ws.addCell(new Label(4, 7,dtoList1.get(4).getMchtCd()));
ws.addCell(new Label(1, 8,"支付宝扫码"));
ws.addCell(new Label(2, 8,dtoList1.get(5).getMchtCd()));
ws.addCell(new Label(3, 8,dtoList1.get(5).getTransFee()));
ws.addCell(new Label(4, 8,dtoList1.get(5).getMchtCd()));
ws.addCell(new Label(1, 9,"银联扫码"));
ws.addCell(new Label(2, 9,dtoList1.get(6).getMchtCd()));
ws.addCell(new Label(3, 9,dtoList1.get(6).getTransFee()));
ws.addCell(new Label(4, 9,dtoList1.get(6).getMchtCd()));
ws.addCell(new Label(0, 10,"2"));
ws.addCell(new Label(5, 10,dtoList1.get(m).getMchtCd()));
ws.addCell(new Label(0, 11,"3"));
ws.addCell(new Label(5, 11,dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(0, 12,"4"));
ws.addCell(new Label(5, 12,dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(0, 13,"5"));
ws.addCell(new Label(5, 13,dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(0, 14,"总计"));
ws.addCell(new Label(5, 14,dtoList2.get(m).getMchtCd()));
//设置单元格内容对齐方式
WritableCellFormat headerFormat = new WritableCellFormat(NumberFormats.TEXT);
headerFormat.setAlignment(Alignment.CENTRE);
// 设置边框颜色
// headerFormat.setBorder(Border.ALL, BorderLineStyle.THICK,jxl.format.Colour.BLACK);
Label lable1 = new Label(1,0,"项目",headerFormat);
Label lable2 = new Label(1,1,"交易分润",headerFormat);
Label lable3 = new Label(1,10,"活动返现",headerFormat);
Label lable4 = new Label(1,11,"已下发分润（日结）",headerFormat);
Label lable5 = new Label(1,12,"风险扣款",headerFormat);
Label lable6 = new Label(1,13,"其他调账",headerFormat);
Label lable7 = new Label(1,2,"项目",headerFormat);
Label lable8 = new Label(2,2,"交易金额",headerFormat);
Label lable9 = new Label(3,2,"交易笔数",headerFormat);
Label lable10 = new Label(4,2,"分润金额",headerFormat);
Label lable11 = new Label(5,0,"金额",headerFormat);
ws.addCell(lable1);
ws.addCell(lable2);
ws.addCell(lable3);
ws.addCell(lable4);
ws.addCell(lable5);
ws.addCell(lable6);
ws.addCell(lable7);
ws.addCell(lable8);
ws.addCell(lable9);
ws.addCell(lable10);
ws.addCell(lable11);
}else if(i==1){
ws.addCell(new Label(0, 0, "序号"));
ws.addCell(new Label(1, 0, "参考号"));
ws.addCell(new Label(2, 0, "交易日期"));
ws.addCell(new Label(3, 0, "交易时间"));
ws.addCell(new Label(4, 0, "交易类型"));
ws.addCell(new Label(5, 0, "卡类型"));
ws.addCell(new Label(6, 0, "商户号"));
ws.addCell(new Label(7, 0, "交易金额"));
ws.addCell(new Label(8, 0, "商户交易手续费率"));
ws.addCell(new Label(9, 0, "商户借记卡交易手续费封底"));
ws.addCell(new Label(10, 0, "商户交易手续费"));
ws.addCell(new Label(11, 0, "结算类型"));
ws.addCell(new Label(12, 0, "商户结算服务费"));
ws.addCell(new Label(13, 0, "代理交易低价费率"));
ws.addCell(new Label(14, 0, "代理交易封顶低价值"));
ws.addCell(new Label(15, 0, "交易手续费分润值"));
ws.addCell(new Label(16, 0, "代理结算服务费分润比例"));
ws.addCell(new Label(17, 0, "代理结算服务费分润值"));
ws.addCell(new Label(18, 0, "代理分润合计"));
ws.addCell(new Label(19, 0, "直属关系"));
ws.addCell(new Label(20, 0, "层级关系"));
ws.addCell(new Label(0, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(1, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(2, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(3, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(4, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(5, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(6, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(7, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(8, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(9, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(10, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(11, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(12, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(13, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(14, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(15, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(16, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(17, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(18, index + 1, dtoList2.get(m).getInstDate()));
ws.addCell(new Label(19, index + 1, dtoList2.get(m).getMchtCd()));
ws.addCell(new Label(20, index + 1, dtoList2.get(m).getInstDate()));
}else if(i==2){
ws.addCell(new Label(0, 0, "序号"));
ws.addCell(new Label(1, 0, "代理商编号"));
ws.addCell(new Label(2, 0, "代理商名称"));
ws.addCell(new Label(3, 0, "分润账期"));
ws.addCell(new Label(4, 0, "成功下发日期"));
ws.addCell(new Label(5, 0, "下发数额"));
ws.addCell(new Label(6, 0, "下发流水号"));
ws.addCell(new Label(7, 0, "层级关系"));
ws.addCell(new Label(0, index + 1, dtoList3.get(m).getMchtCd()));
ws.addCell(new Label(1, index + 1, dtoList3.get(m).getTxnCount()));
ws.addCell(new Label(2, index + 1, dtoList3.get(m).getMchtCd()));
ws.addCell(new Label(3, index + 1, dtoList3.get(m).getTxnCount()));
ws.addCell(new Label(4, index + 1, dtoList3.get(m).getMchtCd()));
ws.addCell(new Label(5, index + 1, dtoList3.get(m).getTxnCount()));
ws.addCell(new Label(6, index + 1, dtoList3.get(m).getMchtCd()));
ws.addCell(new Label(7, index + 1, dtoList3.get(m).getTxnCount()));
}else if(i==3){
ws.addCell(new Label(0, 0, "序号"));
ws.addCell(new Label(1, 0, "数据跟踪号"));
ws.addCell(new Label(2, 0, "代理商编号"));
ws.addCell(new Label(3, 0, "调账项目类别"));
ws.addCell(new Label(4, 0, "调账项目名称"));
ws.addCell(new Label(5, 0, "调账金额"));
ws.addCell(new Label(6, 0, "说明"));
ws.addCell(new Label(7, 0, "备注"));
ws.addCell(new Label(0, index + 1, dtoList4.get(m).getMchtCd()));
ws.addCell(new Label(1, index + 1, dtoList4.get(m).getMchtCd()));
ws.addCell(new Label(2, index + 1, dtoList4.get(m).getSelfProfits()));
ws.addCell(new Label(3, index + 1, dtoList4.get(m).getMchtCd()));
ws.addCell(new Label(4, index + 1, dtoList4.get(m).getSelfProfits()));
ws.addCell(new Label(5, index + 1, dtoList4.get(m).getMchtCd()));
ws.addCell(new Label(6, index + 1, dtoList4.get(m).getSelfProfits()));
ws.addCell(new Label(7, index + 1, dtoList4.get(m).getMchtCd()));
}

}catch (Exception e) {
e.printStackTrace();
}
index++;
}
}
try {
wwb.write();
wwb.close();
} catch (Exception e) {
e.printStackTrace();

}//关闭资源，释放内存

0 0