导出Excel

前台代码

$("#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();
}
}