POI导出到Excel

来源:互联网 发布:个性淘宝店名大全 编辑:程序博客网 时间:2024/05/16 18:31
if (billForm.getButtonName() != null) {if(billForm.getButtonName().equals("export")){System.out.println("-------------------export start--------------------");HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet = wb.createSheet("new sheet");HSSFRow row = sheet.createRow((short) 0);HSSFCell cell = row.createCell((short) 0);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("手机号");cell = row.createCell((short) 1);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("汇款人");cell = row.createCell((short) 2);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("收款人");cell = row.createCell((short) 3);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("收款人卡号");cell = row.createCell((short) 4);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("开户行");cell = row.createCell((short) 5);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("发送日期");cell = row.createCell((short) 6);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("金额");cell = row.createCell((short) 7);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("手续费");cell = row.createCell((short) 8);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("小计");cell = row.createCell((short) 9);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("终端号码");cell = row.createCell((short) 10);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("支付卡号");cell = row.createCell((short) 11);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("支付状态");DetachedCriteria detachedCriteria = DetachedCriteria.forClass(WtPepBillpay.class);        detachedCriteria.createAlias("id", "id");                String start = billForm.getDateStart();String end = billForm.getDateEnd();String remitName=billForm.getRemitName();//汇款人String bankName=billForm.getBankName();//收款人String termid=billForm.getTermid();//手机号String zonePhone=billForm.getZonePhone();//终端号String payCode = billForm.getPayCodeSelect();//支付状态if(StringUtils.isNotEmpty(start)&&StringUtils.isNotEmpty(end)){detachedCriteria.add(Restrictions.between(WtPepBillpay.PEPDATE, start, end));billForm.setDateStart("");//清空billForm.setDateEnd("");//清空}if (StringUtils.isNotEmpty(bankName)) {String temp = bankName.trim();//detachedCriteria.add(Restrictions.between(WtPepBillpay.BANKNAME, billForm.getBankName().trim(), temp));String keyWord="%" + temp + "%";detachedCriteria.add(Restrictions.like(WtPepBillpay.BANKNAME, keyWord));billForm.setBankName("");//清空}if (StringUtils.isNotEmpty(termid)) {String temp = termid.trim();//detachedCriteria.add(Restrictions.between(WtPepBillpay.TERMID, billForm.getTermid().trim(), temp));String keyWord="%" + temp + "%";detachedCriteria.add(Restrictions.like(WtPepBillpay.BANKNAME, keyWord));billForm.setTermid("");//清空}if (StringUtils.isNotEmpty(zonePhone)) {if(zonePhone.indexOf("-")!=-1){String[] splitArray = zonePhone.split("-");String zone = splitArray[0].trim();String phone = splitArray[1].trim();//detachedCriteria.add(Restrictions.between(WtPepBillpay.ZONE, splitArray[0].trim(), temp0));//detachedCriteria.add(Restrictions.between(WtPepBillpay.PHONE, splitArray[1].trim(), temp1));String keyWord0="%" + zone + "%";String keyWord1="%" + phone + "%";detachedCriteria.add(Restrictions.like(WtPepBillpay.ZONE, keyWord0));detachedCriteria.add(Restrictions.like(WtPepBillpay.PHONE, keyWord1));}else{String temp = zonePhone.trim();//detachedCriteria.add(Restrictions.between(WtPepBillpay.PHONE, billForm.getZonePhone().trim(), temp));String keyWord="%" + temp + "%";detachedCriteria.add(Restrictions.like(WtPepBillpay.PHONE, keyWord));}billForm.setZonePhone("");//清空}if (payCode!=null){if(payCode.equals("SS")){}else if(payCode.equals("00")){//00已支付detachedCriteria.add(Restrictions.eq(WtPepBillpay.BILLPAYCODE, "00"));}else if(payCode.equals("FF")){detachedCriteria.add(Restrictions.eq(WtPepBillpay.BILLPAYCODE, "FF"));}}//排序detachedCriteria.addOrder(Order.desc("pepdate"));detachedCriteria.addOrder(Order.desc("peptime"));List exportList = this.getBillService().getListByCriteria(detachedCriteria);System.out.println("exportList.size="+exportList.size());Iterator itr = exportList.iterator();WtPepBillpay wtp;short rowNum = 1;String temp = "";while (itr.hasNext()) {wtp = (WtPepBillpay) itr.next();row = sheet.createRow(rowNum);cell = row.createCell((short) 0);cell.setCellValue(wtp.getTermid());cell = row.createCell((short) 1);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue("华势");cell = row.createCell((short) 2);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(wtp.getBankName());cell = row.createCell((short) 3);cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(wtp.getBankCard());cell = row.createCell((short) 4);cell.setEncoding(HSSFCell.ENCODING_UTF_16);if (wtp.getOpeningBank()== null || wtp.getOpeningBank().equals(""))wtp.setOpeningBank("—");cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(wtp.getOpeningBank());cell = row.createCell((short) 5);temp = wtp.getPepdate().substring(0, 4) + "-"+ wtp.getPepdate().substring(4, 6) + "-"+ wtp.getPepdate().substring(6, 8);cell.setCellValue(temp);cell = row.createCell((short) 6);/*temp = Integer.parseInt(wtp.getTranamt().substring(0, 10))+ "." + wtp.getTranamt().substring(10, 12);*/temp = "¥" + Util.FormatSum(wtp.getTranamt());//modify for NumberFormatExceptioncell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(temp);cell = row.createCell((short) 7);temp = "¥" + Util.FormatSum(wtp.getFee().trim());cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(temp);cell = row.createCell((short) 8);long total = Long.parseLong(wtp.getTranamt())+Long.parseLong(Util.KillZero(wtp.getFee().trim()));temp = "¥" + Util.FormatSum(String.valueOf(total));cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(temp);cell = row.createCell((short) 9);if(wtp.getZone()!=null){cell.setCellValue(wtp.getZone().trim() + "-"+ wtp.getPhone());}else{cell.setCellValue(wtp.getPhone());}cell = row.createCell((short) 10);if (!wtp.getBillPaycode().equals("00")){temp = "—";}else{temp = wtp.getPayCard();}cell.setEncoding(HSSFCell.ENCODING_UTF_16);cell.setCellValue(temp);cell = row.createCell((short) 11);cell.setEncoding(HSSFCell.ENCODING_UTF_16);if (wtp.getBillPaycode().equals("00"))temp = "已支付";elsetemp = "未支付";cell.setCellValue(temp);rowNum++;}try {response.setContentType("octets/stream");response.addHeader("Content-Disposition","attachment;filename=today.xls");OutputStream fileOut = response.getOutputStream();wb.write(fileOut);fileOut.close();} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}System.out.println("--------------------export end------------------------");return null;//add for "cannot forward after response has been commit"}}
0 0
原创粉丝点击