apache poi导出excel

1.前端js

$(function() {
   
   //下载
    $("#downLoad").click("click",function () {
        downLoad();
    });

})


function downLoad(){
	if($("#startTime").val()!=null||$("#startTime").val()!=undefined){
		var startTime = $("#startTime").val().replace(/-/g, '');
		var endTime = $("#endTime").val().replace(/-/g, '');
	}
	var busType=$("#busType").val();
	var brType=$("#brType").val();
	$.ajax({
		url : newMngUrl,
		type : "post",
		timeout : timeoutAjax,
		async : true,
		data : $.toJSON({
			"opt" : "DOWNLOAD", 
			"startTime":startTime,
			"endTime" : endTime,
			"busType1" : busType,
			"brNo" : brType,
			"isAll" : $("#isAll").val(),
			"user_id":getCookie("user_id"),
			"branch_id" : getCookie("br_no"),
			"txcode" : "9000007",
            "dataType":$("#dataType").val(),
            "exactTime":$("#exactTime").val(),
		}),
		success : function(data) {
			layer.closeAll('loading');
			var base = $.parseJSON(data);
			if (base.SYS_HEAD.RET[0].RET_CODE == "0000") {
				var url=base.SYS_HEAD.RET[0].RET_MSG;
				window.open(url);
			} else{
				layer.alert(base.SYS_HEAD.RET[0].RET_MSG,{icon:0});
			}
		},
		error : function(e) {
			
			layer.alert("下载失败");

		},
		complete : function(XMLHttpRequest, status) {
			if (status == "timeout") {
				layer.alert("超时，请刷新重试！", {
					icon : 0
				});
			}
		}
	});
}

2.gatwayService

@Service("tradeReportService")
@GatewayService
public class TradeReportGatewayServiceImpl {
	private static final Logger LOGGER = LoggerFactory.getLogger(TradeReportGatewayServiceImpl.class);
	@Autowired
	private TradeReportService tradeReportService;
	
	@GatewayMapping(txCode = "9000007", txDesc="个人交易量统计")
	public CoreRes<TradeReportRes> dealTradeReport(CoreReq<TradeReportReq> request) {
		return tradeReportService.dealTradeReport( request);
	}
}

3.

public CoreRes<TradeReportRes>  dealTradeReport(@RequestBody CoreReq<TradeReportReq> request) {

//下载
if (head.getOpt() != null && head.getOpt().equals("DOWNLIST")) {
   // 下载数据
   String filePath = downLoadOldToNews(head,delist,body);
   throw new BizException("0000", filePath);
}

}

4.拼装HSSWorkbook 对象

private String downChangeOrAdd(ReqHead head, List<RatioBo> list, TradeReportReq body) {
      Map<String , String> chnelMap = new HashMap();
      chnelMap.put("MBNK","手机银行app");
      chnelMap.put("TBNK","柜面");
      // 创建一个webbook，对应一个Excel文件
      HSSFWorkbook wb = new HSSFWorkbook();
/****************************  样式设置  ************************************/
      // 创建字体
      HSSFFont titlefont = wb.createFont();
      titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
      titlefont.setFontHeightInPoints( (short)14);

      // 设置样式(标题)
      HSSFCellStyle titleCellStyle = wb.createCellStyle();
      titleCellStyle.setFont(titlefont);  //添加字体
      titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

      // 创建单元格，并设置值表头 设置表头居中
      HSSFCellStyle styleCenter = wb.createCellStyle();
      styleCenter.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
      styleCenter.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      styleCenter.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      styleCenter.setBorderRight(HSSFCellStyle.BORDER_THIN);
      styleCenter.setBorderTop(HSSFCellStyle.BORDER_THIN);

      HSSFCellStyle styleRight = wb.createCellStyle();
      styleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 创建一个居右格式
      styleRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      styleRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      styleRight.setBorderRight(HSSFCellStyle.BORDER_THIN);
      styleRight.setBorderTop(HSSFCellStyle.BORDER_THIN);
      /******************************   样式设置  **********************************/
      // 在webbook中添加一个sheet,对应Excel文件中的sheet
      HSSFSheet sheet = wb.createSheet("过渡数据统计");
      // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
      HSSFRow row = sheet.createRow(0);

      //第一行：标题
      HSSFCell cell = row.createCell(0);
      if (head.getOpt().equals("DNNXH")){
         cell.setCellValue(new HSSFRichTextString("新用户销户详细"));
      }
      else if (head.getOpt().equals("DNOXH")){
         cell.setCellValue(new HSSFRichTextString("老用户销户详细"));
      }
      else{
         cell.setCellValue(new HSSFRichTextString("转化或新增用户详细"));
      }
      cell.setCellStyle(titleCellStyle);
      CellRangeAddress cra=new CellRangeAddress(0, 0, 0, 4);//合并单元格
      sheet.addMergedRegion(cra);

      //第二行：th
      row = sheet.createRow(1);
      cell = row.createCell( 0);
      cell.setCellValue("序号");
      cell.setCellStyle(styleCenter);
      cell = row.createCell(1);
      cell.setCellValue("机构名称");
      cell.setCellStyle(styleCenter);
      cell = row.createCell(2);
      cell.setCellValue("客户名称");
      cell.setCellStyle(styleCenter);
      cell = row.createCell( 3);
      cell.setCellValue("注册渠道");
      cell.setCellStyle(styleCenter);
      cell = row.createCell(4);
      cell.setCellValue("注册日期");
      cell.setCellStyle(styleCenter);

      List<BrInfo> list2 = brInfoDao.listBy(null);//存放机构
      Map<String , String> brMap = new HashMap();
      for (BrInfo brInfo:list2
            ) {
         brMap.put(brInfo.getBrNo(),brInfo.getBrName());
      }
      brMap.put("20000","汇总");
      for (int i = 0; i < list.size(); i++) {
         row = sheet.createRow((int) i + 2);
         // 创建单元格，并设置值
         row.createCell((short) 0);
         row.getCell((short) 0).setCellValue((double)(i+1));
         row.getCell((short) 0).setCellStyle(styleCenter);//居中

         row.createCell((short) 1);
         if (brMap.get(list.get(i).getOpnBrNo())==null) {
            row.getCell((short) 1).setCellValue(list.get(i).getOpnBrNo());
         }else{
            row.getCell((short) 1).setCellValue(brMap.get(list.get(i).getOpnBrNo()));
         }
         row.getCell((short) 1).setCellStyle(styleCenter);//居中

         row.createCell((short) 2);
         row.getCell((short) 2).setCellValue(list.get(i).getUserName());
         row.getCell((short) 2).setCellStyle(styleCenter);//居中
         String registerChnel = list.get(i).getRegisterChnel().replaceAll(" ", "");
         row.createCell((short) 3);
         row.getCell((short) 3).setCellValue(chnelMap.get(registerChnel));
         row.getCell((short) 3).setCellStyle(styleCenter);//居中

         row.createCell((short) 4);
         row.getCell((short) 4).setCellValue(list.get(i).getRegisterDate());
         row.getCell((short) 4).setCellStyle(styleCenter);//居中
      }
      // 设置列宽
      sheet.setColumnWidth(1, 5360);
      sheet.setColumnWidth(2, 5360);
      sheet.setColumnWidth(3, 3360);
      sheet.setColumnWidth(4, 5360);
//        sheet.setColumnWidth(7, 5360);
      // 此处需要调用fastdfs，上传文件，并返回下载路径
      return upLoad(head,wb,body);
   }

5.上传到文件服务器，获得下载路径

public String upLoad(ReqHead head,HSSFWorkbook wb,TradeReportReq req){
   String serverPath= FastDFSUtils.getUrl("");
   String uploadFilePath =null;
   ByteArrayOutputStream os = new ByteArrayOutputStream();
   try {
      wb.write(os);
      byte[] bytes = os.toByteArray();
      uploadFilePath=FastDFSUtils.uploadFileFullName(bytes, ".xls");
   } catch (Exception e) {
      throw new BizException("E9999","上传失败");
   }
   uploadFilePath=serverPath+uploadFilePath;
   return uploadFilePath;
}

前端就可以使用window.open(url)下载文件了