SSM实现导出报表为Excel

报表主页面代码

<script type="text/javascript">
     function exportuserOrderExcel(obj){
      var form = $(obj).parents("form");
      var url = $('#userBillForm').attr("action");
      $('#userBillForm').attr("action", url + "?" + form.serialize());   
      $('#userBillForm').submit();
      $('#userBillForm').html("");

     }
    </script>

<form  style="display: none;" method="post" action="${ctx}/bus/userBillSerailAction/doExport">



<div style="margin-top: 5px;">
   <!-- 刷新按钮-->
   <button class="btn btn-orange" >刷新</button>
   <button class="btn btn-blue"  onclick="exportuserOrderExcel(this)">导出</button>
  </div>

action代码

@RequestMapping(value = "doExport", method=RequestMethod.POST)
 public void doExport(String paramCustomerName,Integer pageSize, Integer pageCurrent, String orderField, String orderDirection,
      HttpServletResponse response) {
  try {

   PageResult<BusUserBillserialExt> pageResult = PageUtil.pageSet(this.getClass(), pageSize, pageCurrent, orderField, orderDirection);
   pageResult.setPageSize(null);
   pageResult.setPageCurrent(null);
   pageResult.setOrderField("bub.recordTime");
   pageResult.setOrderDirection("desc");
   String fileName = "**账单流水-"+DateUtils.getDate("yyyyMMddHHmmss")+".xls";

   // 组装参数
   Map<String, Object> param = new HashMap<String, Object>();
         param.put("status", "0");
   if (StringUtils.isNotBlank(paramCustomerName)) {
    param.put("orderNumber", paramCustomerName);
   }
   // 分别查询结果集
   List<BusUserBillserialExt> billserials = new ArrayList<BusUserBillserialExt>();
   pageResult = this.billService.exportUserBillDetailsByParam(pageResult);
   if(pageResult.getList().size()>0) {
    billserials=pageResult.getList();
    }
   // 构造 sheet 标题
   List<String[]> columNames = new ArrayList<String[]>();
   List<String[]> fieldNames = new ArrayList<String[]>();
   List<String[]> groupNames = new ArrayList<String[]>();
   LinkedHashMap<String, List<?>> dataMap = new LinkedHashMap<String, List<?>>();
   List<String> titles = new ArrayList<String>();
   //
   if(billserials != null && billserials.size() > 0){
    columNames.add(new String[] {
      "商户流水ID","商户名称","订单类型","交易方式","交易金额",
      "是否使用红包","使用红包金额","交易账号","交易姓名",
      "交易时间","交易类型","备注" });
    fieldNames.add(new String[] {
      "id", "customerName","orderTypeName","payType", "payPrice",
      "isUseRedPackageName","useRedPackagePrice","transactionNum","transactionName",
      "transactionTimeChanged","transactionTypeName","remark"});
    dataMap.put("**账单", billserials);
    titles.add("**账单流水");
   }

   ExcelExportData setInfo = new ExcelExportData();
   setInfo.setDataMap(dataMap);
   setInfo.setFieldNames(fieldNames);
   String[] titlesArray = new String[titles.size()];
   setInfo.setTitles(titles.toArray(titlesArray));   
   setInfo.setGroupColumn(groupNames);
   setInfo.setColumnNames(columNames);

   ExportExcelMultiSheet.export2Client(response, fileName, setInfo); 

  } catch (Exception e) {
   LogService.getInstance(this).debug(e);
  }
 }

 

service代码

public PageResult<BusUserBillserialExt> exportUserBillDetailsByParam(PageResult<BusUserBillserialExt> pageResult);

serviceImpl代码

public PageResult<BusUserBillserialExt> exportUserBillDetailsByParam(PageResult<BusUserBillserialExt> pageResult){
  if(pageResult.getPageSize()!=null||pageResult.getPageCurrent()!=null) {
  pageResult.getParam().put("offset", pageResult.getPageSize() * (pageResult.getPageCurrent() - 1));
  pageResult.getParam().put("limit", pageResult.getPageSize());
  }
  if (!"".equals(pageResult.getOrderField())) { pageResult.getParam().put("orderColumn", pageResult.getOrderField()); }
  pageResult.getParam().put("orderTurn", pageResult.getOrderDirection());
  List<BusUserBillserialExt> data = dao.getUserBillDetailsByParam(pageResult.getParam());
  pageResult.setList(data);
  int totalSize = dao.countOfUserBillByParam(pageResult.getParam());
  pageResult.setTotal(totalSize);
  return pageResult;
 }

dao代码

public List<BusUserBillserialExt> getUserBillDetailsByParam(Map<String, Object> param);

mapper代码

<!-- 结果集 -->    
 <resultMap  type="**.model.ext.BusUserBillserialExt" extends="BaseResultMap">

  <result column="transactionTypeName" property="transactionTypeName" jdbcType="VARCHAR" />
  <result column="orderTypeName" property="orderTypeName" jdbcType="VARCHAR" />
  <result column="isUseRedPackageName" property="isUseRedPackageName" jdbcType="VARCHAR" />
 </resultMap>

<!-- 关联码表获得导出订单数据 -->
 <select  parameterType="java.util.Map" resultMap="BaseResultMapExt">
  select
  bub.*,sc.CodeName  transactionTypeName,
  sc1.CodeName orderTypeName,
  sc2.CodeName isUseRedPackageName
    FROM
     bus_user_billserial bub
     LEFT JOIN sys_code sc ON sc.codeType = "merchantTransactionType"
     AND sc.CodeValue = bub.TransactionType
   LEFT JOIN sys_code sc1 ON sc1.codeType = "orderType"
     AND sc1.CodeValue = bub.OrderType
   LEFT JOIN sys_code sc2 ON sc2.codeType = "signType"
     AND sc1.CodeValue = bub.IsUseRedPackage

 </select>

Model代码

public class BusUserBillserialExt extends BusUserBillserial implements java.io.Serializable{


 private static final long serialVersionUID = 1L;
 //码表字段
 private String transactionTypeName;
 private String orderTypeName;
 private String isUseRedPackageName;
 //格式化日期
 private String transactionTimeChanged;

 public String getOrderTypeName() {
  return orderTypeName;
 }
 public void setOrderTypeName(String orderTypeName) {
  this.orderTypeName = orderTypeName;
 }
 public String getTransactionTypeName() {
  return transactionTypeName;
 }
 public void setTransactionTypeName(String transactionTypeName) {
  this.transactionTypeName = transactionTypeName;
 }
 public String getIsUseRedPackageName() {
  return isUseRedPackageName;
 }
 public void setIsUseRedPackageName(String isUseRedPackageName) {
  this.isUseRedPackageName = isUseRedPackageName;
 }
 public String getTransactionTimeChanged() {
  if(getTransactionTime()!=null) {
   transactionTimeChanged=DateUtil.formatDate(getTransactionTime(),"yyyy-MM-dd HH:mm:ss");
  }
  return transactionTimeChanged;
 }
 public void setTransactionTimeChanged(String transactionTimeChanged) {
  this.transactionTimeChanged = transactionTimeChanged;
 }
}

 

效果