导出EXCEL（带数据）

/*
* 导出EXCEL
* @param req
* @param resp
* @param model
* @param info
* @return
*/
@RequestMapping(value = { "exportExcel" })
@ResponseBody
public String exportExcel(HttpServletRequest req,HttpServletResponse resp, Model model, ClearCheckErr info) {
List<ClearCheckErr> list = ClearCheckErrService.queryList(info);
String sumAmt=ClearCheckErrService.getSumAmt(info);
//String size=list==null?"0":String.valueOf(list.size());
//合计
String coretranSumAmt="0";
String acqtranSumAmt="0";
String sumFee="0";
if(list!=null&&list.size()>0){
if(sumAmt!=null&&!"".equals(sumAmt)&&sumAmt.contains("#")){
coretranSumAmt=sumAmt.split("#")[0].toString();
sumFee=sumAmt.split("#")[1].toString();
acqtranSumAmt=sumAmt.split("#")[2].toString();
}
}resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
resp.setCharacterEncoding("UTF-8");
try{
String headName="财务报表—差错流水表 ("+this.getNowDt("yyyyMMddHHmmss",0)+")";
// 选择模板文件：
String path =req.getSession().getServletContext().getRealPath("/")+ "reportTemp/clearCheckErrReport.xls"; // excel模板
InputStream in = new FileInputStream(new File(path));
HSSFWorkbook work = new HSSFWorkbook(in);
// 得到excel的第1个sheet
HSSFSheet sheet =work.getSheetAt(0);//设置表头和制表时间
HSSFRow row = sheet.createRow((short) 1);
HSSFCellStyle style = work.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = work.createFont();
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);// 设置字体大小
style.setFont(font);
POIUtils.createCell(row, (short) 9, headName,style);
row.setHeight((short)(27 * 20));int startRow=3;//表头往上多少行,开始循环填数据行数，从0开始
//获取样式
row = sheet.getRow(startRow);
style = row.getCell(2).getCellStyle();
ClearCheckErr cce = null;
for (int i = 0; i < list.size(); i++) {
cce = list.get(i);
if(cce!=null){
String stlFlag=cce.getStlFlag();
if("0".equals(stlFlag)){
stlFlag="初登记";
}else if("1".equals(stlFlag)){
stlFlag="需要结算";
}else{
stlFlag="无需结算";
}
String adjustFlag=cce.getAdjustFlag();
if("0".equals(adjustFlag)){
adjustFlag="初登记";
}else if("1".equals(adjustFlag)){
adjustFlag="需要调账";
}else{
adjustFlag="无需调账";
}
String status=cce.getStatus();
if("0".equals(status)){
status="初登记";
}else if("1".equals(status)){
status="一审完成";
}else if("2".equals(status)){
status="二审完成";
}else{
status="作废";
}row = sheet.createRow(startRow+i);
row.setHeight((short)(25 * 20));POIUtils.createCell(row, (short) 0,i+1+"",style);
POIUtils.createCell(row, (short) 1,cce.getMerNo(),style);
POIUtils.createCell(row, (short) 2,cce.getMerName()==null||"".equals(cce.getMerName())?"":cce.getMerName(),style);
POIUtils.createCell(row, (short) 3,cce.getTermNo()==null||"".equals(cce.getTermNo())?"":cce.getTermNo(),style);
POIUtils.createCell(row, (short) 4,cce.getCardNo()==null||"".equals(cce.getCardNo())?"":cce.getCardNo(),style);
POIUtils.createCell(row, (short) 5,"0".equals(cce.getTranType())?"消费":"退货",style);
POIUtils.createCell(row, (short) 6,cce.getTranDate()==null||"".equals(cce.getTranDate())?"":cce.getTranDate(),style);
POIUtils.createCell(row, (short) 7,cce.getTranTime()==null||"".equals(cce.getTranTime())?"":cce.getTranTime(),style);
POIUtils.createCell(row, (short) 8,cce.getCoretranAmt()==null?"":cce.getCoretranAmt().toString(),style);
POIUtils.createCell(row, (short) 9,cce.getAcqtranAmt()==null?"":cce.getAcqtranAmt().toString(),style);
POIUtils.createCell(row, (short) 10,cce.getFee()==null?"":cce.getFee().toString(),style);
POIUtils.createCell(row, (short) 11,cce.getCheckTime(),style);
POIUtils.createCell(row, (short) 12,stlFlag,style);
POIUtils.createCell(row, (short) 13,adjustFlag,style);
POIUtils.createCell(row, (short) 14,"D".equals(cce.getDcFlag())?"借记":"贷记",style);
POIUtils.createCell(row, (short) 15,"0".equals(cce.getGenType())?"系统录入":"手工录入",style);
POIUtils.createCell(row, (short) 16,status,style);
POIUtils.createCell(row, (short) 17,cce.getCorebatNo()==null?"":cce.getCorebatNo().toString(),style);
POIUtils.createCell(row, (short) 18,cce.getCorejonlNo()==null?"":cce.getCorejonlNo().toString(),style);
POIUtils.createCell(row, (short) 19,cce.getCoreRrn()==null||"".equals(cce.getCoreRrn())?"":cce.getCoreRrn(),style);
POIUtils.createCell(row, (short) 20,cce.getAcqbatNo()==null?"":cce.getAcqbatNo().toString(),style);
POIUtils.createCell(row, (short) 21,cce.getAcqjonlNo()==null?"":cce.getAcqjonlNo().toString(),style);
POIUtils.createCell(row, (short) 22,cce.getAcqRrn()==null||"".equals(cce.getAcqRrn())?"":cce.getAcqRrn(),style);
POIUtils.createCell(row, (short) 23,cce.getComments()==null?"":cce.getComments().toString(),style);
POIUtils.createCell(row, (short) 24,cce.getComments1()==null?"":cce.getComments1().toString(),style);
POIUtils.createCell(row, (short) 25,cce.getComments2()==null?"":cce.getComments2().toString(),style);
}
}//设置合计交易金额、手续费金额和条数
row = sheet.createRow(list.size()+startRow);
row.setHeight((short)(27 * 20));
POIUtils.createCell(row, (short) 2, "合计 卡核心交易金额："+coretranSumAmt+" 收单交易金额："+acqtranSumAmt+" 交易手续费："+sumFee,null);
/**************************** 输出流 *****************************************/
OutputStream os = resp.getOutputStream();// 取得输出流
String filename = POIUtils.encodeFileName(req, headName);
resp.setHeader("Content-disposition","attachment;filename=" + filename+ ".xls");
work.write(os);
if(in!=null){
in.close();
}
os.close();
}catch (Exception e) {
if (log.isDebugEnabled())
e.printStackTrace();
log.error("ClearCheckErrController.exportExcel()调用出现异常。");
}return null;
}