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 id="userBillForm" style="display: none;" method="post" action="${ctx}/bus/userBillSerailAction/doExport">
<div style="margin-top: 5px;">
<!-- 刷新按钮-->
<button class="btn btn-orange" data-icon="refresh">刷新</button>
<button class="btn btn-blue" data-icon="sign-out" 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 id="BaseResultMapExt" 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 id="getUserBillDetailsByParam" 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;
}
}