导出excel需要引得jar包
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
后台代码实现:
public class OutExcel {
public String exportExcleuserbook(HttpServletRequest req, HttpServletResponse res) {
// TODO 导出表格
String name=req.getParameter("name");
String memberId=req.getParameter("memberId");
String formName="";
Map<String,Object> map=new TreeMap<String,Object>();
@SuppressWarnings("resource")
HSSFWorkbook wb = new HSSFWorkbook();
//TODO 会员交易记录
formName="会员交易记录";
HSSFSheet sheet = wb.createSheet("会员交易记录1");
HSSFRow row = sheet.createRow(0);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell(0);
cell.setCellValue("交易编号");
sheet.setColumnWidth(0, 30 * 250);
cell.setCellStyle(style);
sheet.setColumnWidth(1, 30 * 180);
cell = row.createCell( 1);
cell.setCellValue("手机号码");
cell.setCellStyle(style);
sheet.setColumnWidth(2, 30 * 150);
cell = row.createCell( 2);
cell.setCellValue("受理方");
cell.setCellStyle(style);
sheet.setColumnWidth(3, 30 * 150);
cell = row.createCell( 3);
cell.setCellValue("交易金额");
cell.setCellStyle(style);
sheet.setColumnWidth(4, 30 * 150);
cell = row.createCell(4);
cell.setCellValue("交易时间");
cell.setCellStyle(style);
sheet.setColumnWidth(5, 30 * 150);
cell = row.createCell(5);
cell.setCellValue("交易类型");
cell.setCellStyle(style);
//*****入参判断
if(StringUtils.isNotBlank(req.getParameter("orderNo"))){
map.put("orderNo", req.getParameter("orderNo"));
}
if(StringUtils.isNotBlank(req.getParameter("supplierName"))){
map.put("supplierName", req.getParameter("supplierName"));
}
String paymentStatus = req.getParameter("paymentStatus");
if(StringUtils.isNotBlank(req.getParameter("paymentStatus"))){
map.put("paymentStatus", req.getParameter("paymentStatus"));
}
if(StringUtils.isNotBlank(req.getParameter("memberMobile"))){
map.put("memberMobile", req.getParameter("memberMobile"));
}
if(StringUtils.isNotBlank(req.getParameter("start"))){
map.put("start",req.getParameter("start")+TimeCode.START_TIME);
}
if(StringUtils.isNotBlank(req.getParameter("end"))){
map.put("end",req.getParameter("end")+TimeCode.END_TIME);
}
map.put("memberId", memberId);
List<DataMap> list=null;
if(paymentStatus!=null&&!paymentStatus.equals("")){
if(paymentStatus.equals(RebateRecordStateCode.ON_LINE_CONSUMED_STRING)){
list=managerWorkRecordDao.OnLineManagerTransactionQueryRecordSelect(map);
}else{
list=managerWorkRecordDao.outLineManagerTransactionQueryRecordSelect(map);
}
}else{
list = managerWorkRecordDao.managerTransactionQueryRecordSelect(map);
}
//*************从数据库取数据,将数据赋值给表格
JSONArray jsonArray=JSONArray.fromObject(list);
for (int i = 0; i < jsonArray.size(); i++) {
JSONObject jobj = (JSONObject) jsonArray.get(i);
String orderNo=null;
String memberMobile=null;
String supplierName=null;
String allOrderMoney=null;
String orderStateTime=null;
String orderStateName=null;
if(MapUtils.jsonIsAnyBlank(jobj, "orderNo")){
orderNo="";
}else{orderNo=jobj.getString("orderNo");}
if(MapUtils.jsonIsAnyBlank(jobj, "memberMobile")){
memberMobile="";
}else{
memberMobile=(jobj.getString("memberMobile"));
}
if(MapUtils.jsonIsAnyBlank(jobj, "supplierName")){
supplierName="";
}else{supplierName=jobj.getString("supplierName");}
if(MapUtils.jsonIsAnyBlank(jobj, "allOrderMoney")){
allOrderMoney="";
}else{allOrderMoney=jobj.getString("allOrderMoney");}
if(MapUtils.jsonIsAnyBlank(jobj, "orderStateTime")){
orderStateTime="";
}else{orderStateTime=jobj.getString("orderStateTime");}
if(MapUtils.jsonIsAnyBlank(jobj, "orderType")){
orderStateName="";
}else{
orderStateName=jobj.getString("orderType");
}
row = sheet.createRow(i + 1);
row.createCell( 0).setCellValue(orderNo);
row.createCell( 1).setCellValue(memberMobile);
row.createCell( 2).setCellValue(supplierName);
row.createCell( 3).setCellValue(allOrderMoney);
row.createCell(4).setCellValue(orderStateTime);
row.createCell(5).setCellValue(orderStateName);
}
res.reset();
res.setContentType("application/x-msdownload");
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");
String pName = formName + "_" + df.format(new Date());
try {
res.setHeader("Content-Disposition","attachment; filename="+ new String(pName.getBytes(),"ISO-8859-1") + ".xls");
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
ServletOutputStream outStream = null;
try {
outStream = res.getOutputStream();
wb.write(outStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
outStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return pName;
}
判空的实体类:
/**
*
* @param JSON
* @param String
* @return boolean
* @see if(jsonObject,"") == null true
* @see if(jsonObject,null) == null true
* @see if(jsonObject,"null") == null true
* @see if(jsonObject,"key not exist") == null true
* @see if(jsonObject,"key==""") == null true
* @see if(jsonObject,"key==null") == null true
* @see others false
*/
public static boolean jsonIsAnyBlank(JSONObject jsonObject, String... keys) {
boolean flag = true;
if (jsonObject == null)
return flag;
if (jsonObject.isEmpty() || jsonObject.size() == 0)
return flag;
if (keys == null || keys.equals(""))
return flag;
for (String key : keys)
if (!jsonObject.containsKey(key))
return flag;
for (String key : keys)
if (jsonObject.get(key) == null || jsonObject.get(key).equals("null") || jsonObject.get(key).equals(""))
return flag;
return flag = false;
}
jsp中的实现:
<div class="col-sm-6">
<a id="exportTable" class="btn-search" style="display: block;">导出</a>
</div>
</script>//导出excel
$("#exportTable").on("click", function() {var url = 请求controller;$("#exportTable").attr('href',url);})</script>
这是我项目中的实现,还没有来的及抽成方法,希望能帮到各位