POI导出数据到Excel

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 NumberFormatException cell.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 = "已支付"; else temp = "未支付"; 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 block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } System.out.println("--------------------export end------------------------"); return null;//add for "cannot forward after response has been commit" } }