前台代码
$("#downloadClearDetailInfo").click(function(){
$("#form").attr("action","clearDetailInfo!downloadClearDetailInfo.action").submit(); })后台Action
public void downloadClearDetailInfo(){
try { List<BusClearDetailInfo> list = clearDetailInfoService.query(clearDetailInfo, pageObj); String[] lineNames ={"付款路径","处理状态","处理结果","业务类型","批次号","批次号序号", "调拨批次号","是否垫付","汇率","付款账号","付款名称","付款币种", "付款金额","收款账号","收款名称","收款币种","收款金额","发送次数", "发送时间","扣款时间","失败原因","操作人","操作时间"}; String exportFileName = "clear_detail_Info_query"; // 导出文件名 Map<String, String> current = queryCurrent(); if (Util.notEmpty(list) && list.size() >30000) { addActionMessage("不能大于30000条!"); }else{ ProcessExcel.exportExcel("明细导出",exportFileName,list,lineNames,current); } } catch (Exception e) { doLog("导出明细信息 出错", log, e); } }
/**导出明细
* @param fileName * @param exportFileName 导出的文件名称 * @param list 数据库中数据 * @param sheet * @param lineNames */ public static void exportExcel(String fileName,String exportFileName,List<BusClearDetailInfo> list, String[] lineNames,Map<String, String> current){ HSSFWorkbook myexcel = null; HSSFSheet sheet = null; HSSFRow row = null; HSSFCell cell = null; HSSFCellStyle centerstyle = null; myexcel = new HSSFWorkbook(); sheet = myexcel.createSheet(fileName); HSSFCellStyle sheetStyle = myexcel.createCellStyle(); // 背景色的设定 sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // 前景色的设定 sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 填充模式 sheetStyle.setFillPattern(CellStyle.FINE_DOTS); // 另一个字体样式 HSSFFont columnHeadFont = myexcel.createFont(); columnHeadFont.setFontName("宋体"); columnHeadFont.setFontHeightInPoints((short) 10); columnHeadFont.setBoldweight(Font.BOLDWEIGHT_BOLD); // 列头的样式 HSSFCellStyle columnHeadStyle = myexcel.createCellStyle(); columnHeadStyle.setFont(columnHeadFont); columnHeadStyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中 columnHeadStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 columnHeadStyle.setLocked(true); columnHeadStyle.setWrapText(true); columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色 columnHeadStyle.setBorderLeft((short) 1);// 边框的大小 columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色 columnHeadStyle.setBorderRight((short) 1);// 边框的大小 columnHeadStyle.setBorderBottom(CellStyle.BORDER_THIN); // 设置单元格的边框为粗体 columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色 // 设置单元格的背景颜色(单元格的样式会覆盖列或行的样式) columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index); HSSFFont font = myexcel.createFont(); font.setFontName("宋体"); font.setFontHeightInPoints((short) 10); // 普通单元格样式 HSSFCellStyle style = myexcel.createCellStyle(); style.setFont(font); style.setAlignment(CellStyle.ALIGN_LEFT);// 左右居中 style.setVerticalAlignment(CellStyle.VERTICAL_TOP);// 上下居中 style.setWrapText(true); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderLeft((short) 1); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderRight((short) 1); style.setBorderBottom(CellStyle.BORDER_THIN); // 设置单元格的边框为粗体 style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色. style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色. // 另一个样式 centerstyle = myexcel.createCellStyle(); centerstyle.setFont(font); centerstyle.setAlignment(CellStyle.ALIGN_CENTER);// 左右居中 centerstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中 centerstyle.setWrapText(true); centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderLeft((short) 1); centerstyle.setRightBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderRight((short) 1); centerstyle.setBorderBottom(CellStyle.BORDER_THIN); // 设置单元格的边框为粗体 centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色. centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色. row = sheet.createRow(0); for (int i = 0; i < lineNames.length; i++) { cell = row.createCell(i); cell.setCellValue(lineNames[i]); sheet.setColumnWidth(i, 5000); cell.setCellStyle(columnHeadStyle); } Map<String, String> payPaths = InitTypesServlet.getTypeList("BUS_CLEAR_DETAIL_INFOPAY_PATH"), trxTypes = InitTypesServlet.getTypeList("BP_OPENTRX_INFOBUS_TYPE"), yesOrnos = InitTypesServlet.getTypeList("BP_OPENTRX_INFOYESORNO"), fundStatuss = InitTypesServlet.getTypeList("BUS_CLEAR_DETAIL_INFOFUND_STATUS"), fundResults = InitTypesServlet.getTypeList("BUS_CLEAR_DETAIL_INFOFUND_RESULT"); SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); BusClearDetailInfo obj; for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i + 1); obj = list.get(i); for (int j = 0; j < lineNames.length; j++) { cell = row.createCell(j); cell.setCellType(Cell.CELL_TYPE_STRING);///设置 列为文本类型 cell.setCellStyle(centerstyle); switch (j) { case 0: String payPath = payPaths.get(obj.getPayPath()); cell.setCellValue(payPath); break; case 1: String fundStatus = fundStatuss.get(obj.getFundStatus()); cell.setCellValue(fundStatus); break; case 2: String fundResult = fundResults.get(obj.getFundResult()); cell.setCellValue(fundResult); break; case 3: String trxType = trxTypes.get(obj.getTrxType()); cell.setCellValue(trxType); break; case 4: cell.setCellValue(obj.getBatchNo()); break; case 5: cell.setCellValue(obj.getBatchDetailNo()); break; case 6: cell.setCellValue(obj.getPayPath().equals("00") ? "无需调拨" : obj.getBatchAllotNo()); break; case 7: String isHelpPay = yesOrnos.get(obj.getIsHelpPay()); cell.setCellValue(isHelpPay); break; case 8: cell.setCellValue(obj.getExchangeRate()); break; case 9: cell.setCellValue(obj.getPayAcctNo()); break; case 10: cell.setCellValue(obj.getPayAcctName()); break; case 11: String payCurrency = current.get(obj.getPayCurrency()); cell.setCellValue(payCurrency); break; case 12: cell.setCellValue(obj.getPayAmount()); break; case 13: cell.setCellValue(obj.getRecvAcctNo()); break; case 14: cell.setCellValue(obj.getRecvAcctName()); break; case 15: String recvCurrency = current.get(obj.getRecvCurrency()); cell.setCellValue(recvCurrency); break; case 16: cell.setCellValue(obj.getRecvAmount()); break; case 17: cell.setCellValue(obj.getSendTotal()); break; case 18: Date sendTime = obj.getSendTime(); cell.setCellValue(Util.notEmpty(sendTime) ? sf.format(sendTime) : ""); break; case 19: Date chargeTime = obj.getChargeTime(); cell.setCellValue(Util.notEmpty(chargeTime) ? sf.format(chargeTime) : ""); break; case 20: cell.setCellValue(obj.getErrReason()); break; case 21: cell.setCellValue(obj.getCreateBy()); break; case 22: cell.setCellValue(sf.format(obj.getCreateDt())); break; default: break; } } } exportFileName = ProcessExcel.toUtf8String(exportFileName); // 初始化HttpServletResponse对象 HttpServletResponse response = ServletActionContext.getResponse(); OutputStream outputStream = null; try { outputStream = response.getOutputStream(); response.setHeader("Content-disposition", "attachment; filename="+ exportFileName + ProcessExcel.getSysData() + ".xls"); // 设置类型 response.setContentType("application/msexcel;charset=UTF-8"); // 设置头 response.setHeader("Pragma", "public"); // 设置头 response.setHeader("Cache-Control", "max-age=0"); response.setDateHeader("Expires", 0); } catch (IOException e) { e.printStackTrace(); } try { myexcel.write(outputStream); } catch (IOException e) { e.printStackTrace(); } }