public void exportMerchantPayExcel(HttpServletResponse response,
String hybh, String cardno, String time, String shbh, String qzt) {
T_Zx_Card t_Zx_Card = new T_Zx_Card();
String[] str_time = time.split("-"); // 时间分成开始与结束时间
if (!time.equals("")) {
Date startDate = DateUtil.format(str_time[0].replace("/",
"-").trim()
+ " 00:00:00");// 为日期增加时分秒（默认都为00:00:00）
Date endDate = DateUtil.format(str_time[1].replace("/",
"-").trim()
+ " 23:59:59");// 为日期增加时分秒（默认都为00:00:00）
t_Zx_Card.setStartDate(startDate);
t_Zx_Card.setEndDate(endDate);
}
t_Zx_Card.setCardno(cardno);
t_Zx_Card.setHybh(hybh);
t_Zx_Card.setQzt(qzt);
Subject currentUser = SecurityUtils.getSubject();
ShiroUser shiroUser = (ShiroUser) currentUser.getPrincipals()
.getPrimaryPrincipal();
List<T_Zx_Card> t_Zx_Cards = new ArrayList<T_Zx_Card>();
if (shiroUser != null) {
if (shiroUser.getUserType() == Config.SYSTEMADMIN) {
t_Zx_Card.setGid(shbh);
t_Zx_Cards = merchantCardsServiceI
.findMerchantCardExcel(t_Zx_Card);
} else {
t_Zx_Card.setGid(shiroUser.getShbh());
t_Zx_Cards = merchantCardsServiceI
.findMerchantCardExcel(t_Zx_Card);
}
}
for (T_Zx_Card t_Zx_Card2 : t_Zx_Cards) { // 计算余额
t_Zx_Card2.setCardye(t_Zx_Card2.getCzje()-t_Zx_Card2.getXfje());
}
List<T_Zx_Card> t_Zx_CardHz = merchantCardsServiceI
.findMerchantCardExcel(t_Zx_Card);
int czjehztemp = 0;
int xfjehztemp = 0;
int cardyehztemp = 0;
for (T_Zx_Card t_Zx_Card2 : t_Zx_CardHz) {
czjehztemp += t_Zx_Card2.getCzje();
xfjehztemp += t_Zx_Card2.getXfje();
cardyehztemp += t_Zx_Card2.getCzje()- t_Zx_Card2.getXfje();
}
T_Zx_Card Card = new T_Zx_Card();
String startTime = str_time[0];
String endTime = str_time[1];
Card.setCzje(czjehztemp);
Card.setXfje(xfjehztemp);
Card.setCardye(cardyehztemp);
Card.setStartTime(startTime); //导出时间起点
Card.setEndTime(endTime); //导出时间终点
t_Zx_Cards.add(Card); // 把合计放到t_Zx_Cards中
String fileName = "卡务管理列表.xls";
OutputStream os = null;
try {
// 设置文件头
response.setContentType("application/msexcel");
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(fileName.getBytes("GB2312"), "ISO-8859-1")
+ ";");
os = response.getOutputStream();
getpayExcel(t_Zx_Cards, os);
os.flush();
} catch (Exception e) {
}
}
public void getpayExcel(List<T_Zx_Card> t_Zx_Cards, OutputStream os) {
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet("卡务管理列表");
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth(20);
// 生成表格单元样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置表格单元样式
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成表格单元字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
// 把字体应用到当前的样式
style.setFont(font);
// 生成标题样式
HSSFCellStyle styletitle = workbook.createCellStyle();
styletitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
styletitle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styletitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styletitle.setBorderRight(HSSFCellStyle.BORDER_THIN);
styletitle.setBorderTop(HSSFCellStyle.BORDER_THIN);
styletitle.setFillBackgroundColor(HSSFColor.LIME.index);
HSSFFont fonttitle = workbook.createFont();
fonttitle.setColor(HSSFColor.BLACK.index);
fonttitle.setFontHeightInPoints((short) 14);
fonttitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到标题样式
styletitle.setFont(fonttitle);
// 生成表头样式
HSSFCellStyle stylehead = workbook.createCellStyle();
stylehead.setAlignment(HSSFCellStyle.ALIGN_CENTER);
stylehead.setFillBackgroundColor(HSSFColor.AQUA.index);
HSSFFont fonthead = workbook.createFont();
fonthead.setColor(HSSFColor.BLACK.index);
fonthead.setFontHeightInPoints((short) 13);
fonthead.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到表头样式
stylehead.setFont(fonthead);
// 查询出结果集
// String[] headers = { "设备名称", "检测状态" };
// 创建空白表格
for (int i = 0; i < t_Zx_Cards.size() + 3; i++) {// 行数(包括标题)
HSSFRow row = sheet.createRow(i);
if (i == 0 || i == 1 || i == 2) { // 设置表头的格式
row.setHeight((short) (15.625 * 28));
for (int j = 0; j < 8; j++) {// 列数
HSSFCell cell = row.createCell(j);
cell.setCellStyle(styletitle);
}
} else {
for (int j = 0; j < 8; j++) {// 列数
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
}
}
}
//sheet.setColumnWidth(1, (short) (35.7 * 230)); //单独设置列的宽度
//sheet.setColumnWidth(2, (short) (35.7 * 265));
// 填充数据
// 合并单元格
sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) 7)); // 设置表头
sheet.addMergedRegion(new CellRangeAddress(1, (short) 1, 5, (short) 7));
// sheet.addMergedRegion(new CellRangeAddress(0, (short) 1, 0, (short)
// 2));
HSSFCell celltitle = sheet.getRow(0).getCell(0);
celltitle.setCellValue("卡务管理明细");
//sheet.getRow(1).getCell(0).setCellValue("时间区间"+bean.getStartTime());
// 设置表头
sheet.getRow(2).getCell(0).setCellValue("商户编号");
sheet.getRow(2).getCell(1).setCellValue("商户名称");
sheet.getRow(2).getCell(2).setCellValue("卡号");
sheet.getRow(2).getCell(3).setCellValue("会员编号");
sheet.getRow(2).getCell(4).setCellValue("充值总金额");
sheet.getRow(2).getCell(5).setCellValue("消费总金额");
sheet.getRow(2).getCell(6).setCellValue("余额");
sheet.getRow(2).getCell(7).setCellValue("创建时间");
/*sheet.getRow(2).getCell(8).setCellValue("卡信息");
sheet.getRow(2).getCell(9).setCellValue("卡状态");*/
sheet.getRow(1).getCell(7).setCellValue("时间区间:");
for (int i = 3; i < t_Zx_Cards.size() + 3; i++) {// 行数
T_Zx_Card bean = new T_Zx_Card();
bean = (T_Zx_Card) t_Zx_Cards.get(i - 3);
for (int j = 0; j < 8; j++) {// 列数
switch (j) {
case 0:
if (bean.getGid() == null) {
sheet.getRow(i).getCell(j).setCellValue("");
sheet.getRow(1).getCell(5).setCellValue("时间区间: "+bean.getStartTime()+"—"+bean.getEndTime());
} else {
sheet.getRow(i).getCell(j).setCellValue(bean.getGid());
}
break;
case 1:
if (bean.getShname() == null) {
sheet.getRow(i).getCell(j).setCellValue("合计");
} else {
sheet.getRow(i).getCell(j)
.setCellValue(bean.getShname());
}
break;
case 2:
if (bean.getCardno() == null) {
sheet.getRow(i).getCell(j).setCellValue("");
} else {
sheet.getRow(i).getCell(j)
.setCellValue(bean.getCardno());
}
break;
case 3:
if (bean.getHybh() == null) {
sheet.getRow(i).getCell(j).setCellValue("");
} else {
sheet.getRow(i).getCell(j).setCellValue(bean.getHybh());
}
break;
case 4:
sheet.getRow(i).getCell(j)
.setCellValue((bean.getCzje() * 0.01));
break;
case 5:
sheet.getRow(i).getCell(j)
.setCellValue((bean.getXfje() * 0.01));
break;
case 6:
sheet.getRow(i).getCell(j)
.setCellValue((bean.getCardye() * 0.01));
break;
case 7:
if (bean.getCtime() == null) {
sheet.getRow(i).getCell(j).setCellValue("");
} else {
sheet.getRow(i).getCell(j)
.setCellValue(DateUtil.format(bean.getCtime()));
}
break;
/*case 8:
if (bean.getShname() == null) {
sheet.getRow(i ).getCell(j).setCellValue("");
} else if (bean.getHybh() == null) {
sheet.getRow(i).getCell(j).setCellValue("未发卡");
} else {
sheet.getRow(i ).getCell(j).setCellValue("已发卡");
}
break;
case 9:
if (bean.getZt() == null) {
sheet.getRow(i).getCell(j).setCellValue("");
} else if (bean.getZt() == 0) {
sheet.getRow(i).getCell(j).setCellValue("可用");
} else if (bean.getZt() == 1) {
sheet.getRow(i).getCell(j).setCellValue("未激活");
} else if (bean.getZt() == 2) {
sheet.getRow(i).getCell(j).setCellValue("注销");
} else if (bean.getZt() == 3) {
sheet.getRow(i).getCell(j).setCellValue("停用");
} else if (bean.getZt() == 4) {
sheet.getRow(i).getCell(j).setCellValue("挂失");
} else {
sheet.getRow(i).getCell(j).setCellValue("");
}
break;*/
}
}
}
try {
workbook.write(os);
} catch (IOException e) {
e.printStackTrace();
}
}
![]()
![]()
浙公网安备 33010602011771号