java Excel导出功能之固定列表格

曾经终败给现在 2022-05-24 01:07 437阅读 0赞

实现列数不变的数据导出

DAO

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.vooledingtalk.dao.GoOutProcessMapper">
    	<resultMap type="java.util.Map" id="map"></resultMap>
    
    	<insert id="insertGooutProcess" parameterType="goOutProcessPOJO">
    		insert into goout_process(businessId, 
    										submitTime, 
    										timeSnippet, 
    										destination,
    										vehicle,
    										reason,
    										deptId,
    										userId,
    										status,
    										title,
    										result,
    										finishTime
    										)
    		values(#{businessId}, 
    				#{submitTime}, 
    				#{timeSnippet}, 
    				#{destination}, 
    				#{vehicle}, 
    				#{reason}, 
    				#{deptId}, 
    				#{userId}, 
    				#{status},
    				#{title},
    				#{result},
    				#{finishTime})
    	</insert>	
    	
    	
    	<select id="isExist" resultType="int" parameterType="goOutProcessPOJO">
    		select count(*) from goout_process where businessId = #{businessId}
    	</select>
    	
    	<select id="hasStatusResultChange" resultType="int" parameterType="goOutProcessPOJO">
    		select count(*) from goout_process 
    		where businessId = #{businessId}
    		and status = #{status}
    		and result = #{result}
    	</select>
    
    
     	
    	<update id="updateRecodeDate" parameterType="goOutProcessPOJO" >
    		update goout_process
    		set status = #{status}, result = #{result}
    		where businessId = #{businessId}
    	</update>
    	<select id="selectShowData" parameterType="map" resultType="map">
    		SELECT a.userName userName, 
    				a.userId userId, 
    				a.deptName deptName, 
    				g.timeSnippet timeSnippet, 
    				g.destination destination, 
    				g.reason reason, 
    				CASE g.status WHEN 'NEW' THEN '刚创建'
    					      WHEN 'RUNNING' THEN '运行中'
    					      WHEN 'TERMINATED' THEN '被终止'
    					      WHEN 'COMPLETED' THEN '完成'
    					      WHEN 'CANCELED' THEN '取消'
    					      ELSE '' END AS processStatus, 
    				CASE g.result WHEN 'agree'  THEN '同意'
    					      WHEN 'refuse' THEN '拒绝'
    					      ELSE '' END  AS processResult
    				FROM goout_process g
    				LEFT JOIN 		
    				(
    				SELECT u.userid userId , u.name userName, u.department deptId, d.name deptName
    				FROM ding_user u
    				LEFT JOIN
    				ding_dept d
    				ON u.department = d.id ) a
    				ON a.userId = g.userId 
    				<where>
    					<if test="processStatus!=null and processStatus!=''">
    						g.status in 
    							<foreach collection="processStatus" item="processStatus" open="(" separator="," close=")">
    								#{processStatus}
    							</foreach>
    					</if>
    					<if test="processResult!=null and processResult!=''">
    						AND g.result = #{processResult}
    					</if>
    					<if test="startDate!=null and startDate!=''">
    						AND g.submitTime <![CDATA[>=]]> #{startDate}
    					</if>
    					<if test="endDate!=null and endDate!=''">
    						AND g.submitTime <![CDATA[<=]]> #{endDate}
    					</if>
    					<if test="deptId!=null and deptId!=''">
    						AND FIND_IN_SET(a.deptId,getChildDepts(#{deptId})) 
    					</if>
    					<if test="userId!=null and userId!=''">
    						AND a.userid in 
    							<foreach collection="userId" item="userId" open="(" separator="," close=")">
    								#{userId}
    							</foreach>
    					</if>
    				</where>
    				limit #{firstPage}, #{rows}
    	</select>
    	<select id="countShowData" parameterType="map" resultType="int">
    
    		SELECT count(*)
    				FROM goout_process g
    				LEFT JOIN 		
    				(
    				SELECT u.userid userId , u.name userName, u.department deptId, d.name deptName
    				FROM ding_user u
    				LEFT JOIN
    				ding_dept d
    				ON u.department = d.id ) a
    				ON a.userId = g.userId 
    				<where>
    					<if test="processStatus!=null and processStatus!=''">
    						g.status in 
    							<foreach collection="processStatus" item="processStatus" open="(" separator="," close=")">
    								#{processStatus}
    							</foreach>
    					</if>
    					<if test="processResult!=null and processResult!=''">
    						AND g.result = #{processResult}
    					</if>
    					<if test="startDate!=null and startDate!=''">
    						AND g.submitTime <![CDATA[>=]]> #{startDate}
    					</if>
    					<if test="endDate!=null and endDate!=''">
    						AND g.submitTime <![CDATA[<=]]> #{endDate}
    					</if>
    					<if test="deptId!=null and deptId!=''">
    						AND FIND_IN_SET(a.deptId,getChildDepts(#{deptId})) 
    					</if>
    					<if test="userId!=null and userId!=''">
    						AND a.userid in 
    							<foreach collection="userId" item="userId" open="(" separator="," close=")">
    								#{userId}
    							</foreach>
    					</if>
    				</where>
    				
    	</select>
    	
    	<select id="selectExportData" parameterType="map" resultType="map">
    		SELECT a.userName userName, 
    				a.userId userId, 
    				a.deptName deptName, 
    				g.timeSnippet timeSnippet, 
    				g.destination destination, 
    				g.reason reason, 
    				CASE g.status WHEN 'NEW' THEN '刚创建'
    					      WHEN 'RUNNING' THEN '运行中'
    					      WHEN 'TERMINATED' THEN '被终止'
    					      WHEN 'COMPLETED' THEN '完成'
    					      WHEN 'CANCELED' THEN '取消'
    					      ELSE '' END AS processStatus, 
    				CASE g.result WHEN 'agree'  THEN '同意'
    					      WHEN 'refuse' THEN '拒绝'
    					      ELSE '' END  AS processResult
    				FROM goout_process g
    				LEFT JOIN 		
    				(
    				SELECT u.userid userId , u.name userName, u.department deptId, d.name deptName
    				FROM ding_user u
    				LEFT JOIN
    				ding_dept d
    				ON u.department = d.id ) a
    				ON a.userId = g.userId 
    				<where>
    					<if test="processStatus!=null and processStatus!=''">
    						g.status in 
    							<foreach collection="processStatus" item="processStatus" open="(" separator="," close=")">
    								#{processStatus}
    							</foreach>
    					</if>
    					<if test="processResult!=null and processResult!=''">
    						AND g.result = #{processResult}
    					</if>
    					<if test="startDate!=null and startDate!=''">
    						AND g.submitTime <![CDATA[>=]]> #{startDate}
    					</if>
    					<if test="endDate!=null and endDate!=''">
    						AND g.submitTime <![CDATA[<=]]> #{endDate}
    					</if>
    					<if test="deptId!=null and deptId!=''">
    						AND FIND_IN_SET(a.deptId,getChildDepts(#{deptId})) 
    					</if>
    					<if test="userId!=null and userId!=''">
    						AND a.userid in 
    							<foreach collection="userId" item="userId" open="(" separator="," close=")">
    								#{userId}
    							</foreach>
    					</if>
    				</where>
    	</select>
    </mapper>

package com.vooledingtalk.dao;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.stereotype.Repository;
    
    import com.vooledingtalk.pojo.GoOutProcessPOJO;
    
    
    @Repository
    public interface GoOutProcessMapper {
    	/**
    	 * 添加外出审批记录
    	 * @param DingCheckinRecord
    	 * @return
    	 */
    	int insertGooutProcess(GoOutProcessPOJO goOutProcessPOJO);
    	
    	/**
    	 * 根据businessId判断记录是否存在 
    	 * @param dingUser
    	 * @return
    	 */
    	int isExist(GoOutProcessPOJO goOutProcessPOJO);
    	
    	/**
    	 *  根据businessId status 和 result 判断记录是否有状态更新
    	 * @param goOutProcessPOJO
    	 * @return
    	 */
    	int hasStatusResultChange(GoOutProcessPOJO goOutProcessPOJO);
    	
    //	List<Map> selectAll();
    	
    	/*
    	 * 
    	 * 更新已有记录的 status 和 result 字段
    	 */
    	int updateRecodeDate(GoOutProcessPOJO goOutProcessPOJO);
    	
    	List<HashMap<String, Object>> selectShowData(HashMap<String, Object> map);
    	
    	int countShowData(HashMap<String, Object> map);
    	
    	List<Map> selectExportData(Map map);
    }

Service

package com.vooledingtalk.service;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import com.vooledingtalk.pojo.GoOutProcessPOJO;
    
    public interface GoOutProcessService {
    
    	List<HashMap<String, Object>> getTableData(HashMap<String, Object> map);
    	
    	int countTableData(HashMap<String, Object> map);
    	List<Map> selectExportData(Map map);
    }

package com.vooledingtalk.service.impl;
    
    import java.util.Arrays;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    import com.vooledingtalk.dao.GoOutProcessMapper;
    import com.vooledingtalk.service.GoOutProcessService;
    
    @Service
    public class GoOutProcessServiceImpl implements GoOutProcessService {
    	@Autowired
    	private GoOutProcessMapper goOutProcessMapper;
    	
    	
    	public List<HashMap<String, Object>> getTableData(HashMap<String, Object> map) {
    		return goOutProcessMapper.selectShowData(map);
    	}
    
    
    	public int countTableData(HashMap<String, Object> map) {		
    		return goOutProcessMapper.countShowData(map);
    	}
    
    
    	public List<Map> selectExportData(Map map) {
    		// TODO Auto-generated method stub
    		return goOutProcessMapper.selectExportData(map);
    	}
    
    }

Controller

package com.vooledingtalk.controller;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import com.fasterxml.jackson.core.JsonProcessingException;
    import com.fasterxml.jackson.databind.ObjectMapper;
    import com.vooledingtalk.common.Page;
    import com.vooledingtalk.pojo.GoOutProcessPOJO;
    import com.vooledingtalk.service.GoOutProcessService;
    
    import net.sf.json.JSONArray;
    import net.sf.json.JSONObject;
    
    
    @Controller
    public class GoOutProcessController {
    	@Autowired
    	private GoOutProcessService goOutProcessService;
    	
    	@RequestMapping("/goout")
    	public String toGoOutPrcess(){
    		return "goout";
    	}
    	
    	@RequestMapping("/goout/list")
    	@ResponseBody
    	public HashMap<String, Object> showTableData(@RequestParam(value="deptId", required=false,defaultValue="")String deptId,
    												@RequestParam(value="userIds", required=false,defaultValue="")String userIds,
    												@RequestParam(value="processStatus", required=false,defaultValue="")String processStatus,
    												@RequestParam(value="processResult",required=false,defaultValue="")String processResult,
    												@RequestParam(value="startDate", required=false, defaultValue="")String startDate,
    												@RequestParam(value="endDate", required=false, defaultValue="")String endDate,
    												@RequestParam(value="page", required=false)int page,
    												@RequestParam(value="rows", required=false)int rows) {
    
    		Page dataPage = new Page(page, rows);
    		HashMap<String, Object> map = new HashMap<String, Object>();
    		map.put("deptId", deptId);
    		map.put("userId", userIds);
    		map.put("processStatus", processStatus);
    		map.put("processResult", processResult);
    		map.put("startDate", startDate);
    		map.put("endDate", endDate);
    		map.put("firstPage", dataPage.getFirstPage());
    		map.put("rows", rows);
    		
    		int total = goOutProcessService.countTableData(map);
    		System.out.println("--total---" + total);
    
    		List<HashMap<String, Object>> data = goOutProcessService.getTableData(map);
    		HashMap<String, Object> resultMap = new HashMap<String, Object>();
    		resultMap.put("rows", data);
    		resultMap.put("total", total);
    		
    		return resultMap;
    	}
    	
    	//导出数据
    	@RequestMapping(value = "/exportgoout")
    	public String exportTaskCountReportData(HttpServletRequest request) throws Exception {
    		String conditionParams = java.net.URLDecoder.decode(request.getParameter("exportParams"), "UTF-8");
    		Map params = new HashMap();
    		JSONArray jsonArray = JSONArray.fromObject(conditionParams);
    		for (int i = 0; i < jsonArray.size(); i++) {
    			try {
    				JSONObject jsonObject = (JSONObject) jsonArray.get(i);
    				params.put(jsonObject.get("name"), jsonObject.get("value").toString().equals("[]") ? "" : jsonObject.get("value"));
    			} catch (Exception e) {
    				break;
    			}
    		}
    		List<Map> list = goOutProcessService.selectExportData(params);
    	    request.setAttribute("data", list);
    		return "export_goout";
    	}
    }

前端

`goout.jsp`

`<%@ page language="java" import="java.util.*" contentType="text/html;charset=utf-8"`

`pageEncoding="utf-8"%> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv=Content-Type content="text/html;charset=utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"> <meta content="yes" name="apple-mobile-web-app-capable" /> <meta content="black" name="apple-mobile-web-app-status-bar-style"> <meta content="telephone=no" name="format-detection" /> <meta content="yes" name="apple-touch-fullscreen" /> <meta name="viewport" content="width=device-width,initial-scale=1.0,maximum-scale=1.0,minimum-scale=1.0,user-scalable=no" />  <link rel="stylesheet" type="text/css" href="js/jquery-easyui-1.4.1/themes/default/easyui.css"> <link rel="stylesheet" type="text/css" href="js/jquery-easyui-1.4.1/themes/icon.css"> <link rel="stylesheet" type="text/css" href="js/jquery-easyui-1.4.1/themes/color.css"> <script type="text/javascript" src="js/jquery-easyui-1.4.1/jquery.min.js"></script> <script type="text/javascript" src="js/jquery-easyui-1.4.1/jquery.easyui.min.js"></script> <title>外出审批报表</title> </head> <% request.setAttribute("checkin", "/vooleding/checkin"); request.setAttribute("goout", "/vooleding/goout"); request.setAttribute("supplementrecord", "/vooleding/supplementrecord"); %> <script type="text/javascript"> var path = "<%=request.getContextPath()%>"; var checkin = "<%=request.getAttribute("checkin")%>"; var goout = "<%=request.getAttribute("goout")%>"; var supplementrecord = "<%=request.getAttribute("supplementrecord")%>"; </script> <body> <div style="margin-left: 5%; margin-right: 5%;"> <div> <table> <tr> <td><a href="/vooleding"><font size="5">首页</font></a></td> </tr> <tr> <td><a href="${checkin}"><font size="5">签到考勤报表</font></a></td> </tr> <tr> <td><a href="${supplementrecord}"><font size="5">补打卡审批报表</font></a></td> </tr> </table> </div> <br> <div> <table> <tr> <td>审批状态</td> <td><input id="processStatus"></td> <td>审批结果</td> <td><input id="processResult"></td> </tr> <tr> <td>部门</td> <td><input id="deptId"></td> <td>姓名</td> <td><input id="userId"></td> </tr> <tr> <td>查询开始时间</td> <td> <input id="startDate" class="easyui-datebox" data-options="formatter:myformatter,parser:myparser" style="width:100%;"> </td> <td>查询结束时间</td> <td> <input id="endDate" class="easyui-datebox" data-options="formatter:myformatter,parser:myparser" style="width:100%;"> </td> </tr> <tr> <td colspan="4"></td> </tr> <tr> <td colspan="2"> <input type="button" value="查询" id="query" class="easyui-linkbutton c8" style="width:120px"> </td> <td colspan="2"> <input type="button" value="重置" id="reset" class="easyui-linkbutton c2" style="width:120px"> </td> </tr> <tr> <td colspan="4"> <a id="exportGooutData" href="javascript:exportGooutData();"><button style="width:120px;background: #3366FF;">导出</button></a> </td> </tr> </table> </div> <br> <div> <table id="dataTable"></table> </div> </div> <script type="text/javascript"> var path = "<%=request.getContextPath()%>"; </script> <script type="application/javascript" src="js/micriDingtalk/goout.js"></script> <script type="text/javascript"> function myformatter(date){ var y = date.getFullYear(); var m = date.getMonth()+1; var d = date.getDate(); return y+'-'+(m<10?('0'+m):m)+'-'+(d<10?('0'+d):d); } function myparser(s){ if (!s) return new Date(); var ss = (s.split('-')); var y = parseInt(ss[0],10); var m = parseInt(ss[1],10); var d = parseInt(ss[2],10); if (!isNaN(y) && !isNaN(m) && !isNaN(d)){ return new Date(y,m-1,d); } else { return new Date(); } } </script> </body> </html>`

goout.js

export_goout.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
    <%@ page import="java.util.HashMap"%>
    <%@ page import="java.text.DecimalFormat"%>
    <%@ page import="java.util.*"%>
    <%@ page isELIgnored="false"%>
    <%
    	String path = request.getContextPath();
    	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
    			+ path + "/";
    %>
    
    <%
    	response.reset();
    	//response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    	response.setContentType(" application/vnd.ms-excel");
    	//独立打开excel软件   
    	String s = "外出审批统计报表"+"-"+new Date().toLocaleString()+".xls";
    	String fileName  = new String(s.getBytes("GB2312"), "ISO_8859_1");
    	response.setHeader("Content-Disposition", "attachment;filename=\""+fileName);
    	//response.setHeader("Content_Length","4");
    	response.setCharacterEncoding("UTF-8");
    %>
    <!DOCTYPE HTML>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <html>
    <head>
    </head>
    <body>
    	<table border="1">
    		<tr>
    		    <th>姓名</th>
    			<th>员工ID</th>
    			<th>部门名称</th>
    			<th>外出时间段</th>
    			<th>目的地</th>
    			<th>外出原因</th>
    			<th>审批状态</th>
    			<th>审批结果</th>	
    		</tr>
    		<c:forEach items="${data}" var="p" varStatus="s">
    			<tr>
    				<td>${p.userName}</td>
    				<td>${p.userId}</td>
    				<td>${p.deptName}</td>
    				<td>${p.timeSnippet}</td>
    				<td>${p.destination}</td>
    				<td>${p.reason}</td>
    				<td>${p.processStatus}</td>
    				<td>${p.processResult}</td>						
    			</tr>
    		</c:forEach>
    	</table>
    </body>
    
    </html>

总结: