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

柔光的暖阳◎ 2022-05-24 01:14 923阅读 0赞

背景：考勤周期为本月19号至上月20号，则每个月考勤周期表的列是动态变化的。所有数据显示和excel导出功能都需要动态的进行处理。

显示格式如下图

![70][]

前端

checkin.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">
    <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>
    
    
    <style type="text/css">  
        .ui-datepicker-calendar {  
            display: none;// 不显示日期面板  
        }  
    
        .datagrid-view2 {
        	overflow-x : auto;
        }
    </style>  
    <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%">
    	<div>
    		<table>
    		<tr>
    			<td><a href="/vooleding"><font size="5">首页</font></a></td>
    		</tr>
    		<tr>
    			<td><a href="${goout}"><font size="5">外出审批报表</font></a></td>
    		</tr>
    		<tr>
    			<td><a href="${supplementrecord}"><font size="5">补打卡审批报表</font></a></td>
    		</tr>
    	</table>
    	</div>
    	<div>
    		<input type="hidden" id="userName" /> <input type="hidden"
    			id="userId" />
    	</div>
    	<br>
    	<div>
    		<div>
    			<div>部门<span><input id="deptCombotree"></span></div>
    		</div>
    		<div>请选择考勤月份</div>
    
    		
    		<table id="querytable" style="border:1px;">
    			<tr>
    				<td style="width: 20%;">
    					<span class="leftspan">
                	<span style="color:#FF0000;">
                		<img src="img/left.jpg" style="height:35px; width:35px;" οnclick="reduceMonth()"/>
                	</span>
    			</span>
    				</td>
    				<td align="center">
    					<label id="beginTime" class="kbtn" 
    				style="height:100px; width:200px;font-size: 35px;">
    				
    			</label>
    				</td>
    				<td style="width: 20%;" align="right">
    					<span class="rightspan">
                	<span style="color:#FF0000;">
                		<img src="img/right.jpg" style="height:35px; width:35px;" οnclick="addMonth()" />
                	</span>
    			</span>
    				</td>
    			</tr>
    			<tr>
    				<td colspan="3">
    					<a href="javascript:exportCheckinData();"><button style="width: 120px;background: #3366FF;">导出</button></a> 
    				</td>
    			</tr>
    		</table>
    	</div>
    	<div id="dataTable" style="width: 90%;" >
    		<table id="RecordList2" ></table>
    	</div>
    </div>
    </body>
    
    <script type="text/javascript">
    var path = "<%=request.getContextPath()%>";
    console.log(path);
    </script>
    
    <script type="text/javascript" src="js/micriDingtalk/checkin.js"></script>
    </html>

checkin.js
    
    //初始考勤月份为当前月份
    var today = new Date();
    var nowDate = today.getFullYear() + '-' + (today.getMonth() + 1);
    //	$("#date1").val(new Date());
    var startDay = 20;
    var endDay = 19;
    var month0 = "";
    var year0 = "";
    var deptObj = {"field":"deptName","title":"部门名称"};
    var nameObj = {"field":"name","title":"姓名"};
    var userIdObj = {"field":"userId","title":"员工ID"};
    var exportParams = [];
    //cols.push(deptObj);
    //cols.push(nameObj);
    //cols.push(userIdObj);
    
    $(document).ready(function() {
    	//初始月份
    	document.getElementById("beginTime").innerHTML = nowDate;
    	//初始部门列表
    	$("#deptCombotree").combotree ({
    		url:path+"/record/deptlist?deptid=0",  
    		onBeforeExpand:function(node) {
    		      $("#deptCombotree").combotree("tree").tree("options").url = path+"/record/deptlist?deptid=" + node.id;
    		},
    		onSelect:function(node){
    			$("#RecordList2").datagrid('reload',{deptId:node.id,recodeMonth:document.getElementById("beginTime").innerHTML});
    		}
    	 }); 
    	
    	scrollShow($("#RecordList2"));
    		initTable(today.getFullYear(), today.getMonth());
    		
    		
    
    });
    
    function exportCheckinData() { exportParams = [{"name":"deptId","value":$("#deptCombotree").combotree('getValue')}, {"name":"recodeMonth","value":document.getElementById("beginTime").innerHTML}]; var c1 = JSON.stringify(exportParams); c1 = encodeURI(encodeURI(c1)); // location.href = 'exportOANoteRateData?conditionParams=' + c +'&'+ 'menuParam=' + c1 ; location.href = 'exportcheckindata?exportParams=' + c1; } //动态创建column对象的数组，注意table的列式column对象的数组 function initColumnsStr(queryYear, queryMonth){ var cols = []; var preMonth = Number(queryMonth)-1; var startDate = new Date(queryYear, preMonth, startDay); var endDate = new Date(queryYear, queryMonth, endDay); var nextDate = new Date(queryYear, preMonth, startDay); console.log("initColumnsStr startDate " + startDate); console.log("initColumnsStr endDate " + endDate); console.log("initColumnsStr nextDate " + nextDate); var columns = "["; while(Date.parse(nextDate) <= Date.parse(endDate)){ // console.log((nextDate.getMonth()+1) + '-' + nextDate.getDate()); var dateObj = {}; dateName = (nextDate.getMonth()+1) + '-' + nextDate.getDate(); dateObj["field"] = dateName; dateObj["title"] = dateName; cols.push(dateObj); nextDate.setDate(nextDate.getDate() + 1); } console.log(cols); return cols; }
    
    //有月份和column对象数组 初始生成table -- init dataGrid
    function initTable(queryYear, queryMonth){
    	console.log("initTable--" + queryYear+ "-" +queryMonth);
    	//初始化表
    	var colStr = initColumnsStr(queryYear, queryMonth);
    	var queryDate = document.getElementById("beginTime").innerHTML;
    	console.log("----------------");
    	console.log(colStr);
    	$('#RecordList2').datagrid({
          url: path+'/record/recordlist',
          method: 'get',
          pageSize:10,
          pageList : [10], 
          pagination:true,                                   
    		queryParams:{recodeMonth:queryDate,deptId:$("#deptCombotree").combotree("getValue")},
          title: '签到考勤表',
          columns:[colStr],
          frozenColumns:[[ 
          	{field:'deptName',title:'部门名称',align:'left'}, 
          	{field:'name',title:'姓名',align:'left'},
          	{field:'userId',title:'员工ID',align:'left'}
          	]] 
      });
    
    }
    
    //月份加减处理
    function addMonth() {
    	var s = document.getElementById("beginTime").innerHTML;
    	var arr = s.split("-");
    	var year = parseInt(arr[0]);
    	var month = parseInt(arr[1]);
    	//判断month
    	if (month == 12) {
    		year = year + 1;
    		month = 1;
    	} else {
    		month = month + 1;
    	}
    	document.getElementById("beginTime").innerHTML = year + "-" + month;
    	initTable(year, month-1);
    }
    
    function reduceMonth() {
    	var s = document.getElementById("beginTime").innerHTML;
    	var arr = s.split("-");
    	var year = parseInt(arr[0]);
    	var month = parseInt(arr[1]);
    	console.log(year + "/" + month);
    	//判断month
    	if (month == 1) {
    		year = year - 1;
    		month = 12;
    	} else {
    		month = month - 1;
    	}
    	document.getElementById("beginTime").innerHTML = year + "-" + month;
    	initTable(year, month-1);
    }
    
    //easyUI默认出现滚动条  
    function defaultHaveScroll(gridid){  
    var opts=$('#'+gridid).datagrid('options');  
    // alert(Ext.util.JSON.encode(opts.columns));  
    var text='{';  
    for(var i=0;i<opts.columns.length;i++){  
       var inner_len=opts.columns[i].length;  
       for(var j=0;j<inner_len;j++){  
           if((typeof opts.columns[i][j].field)=='undefined')break;  
            text+="'"+opts.columns[i][j].field+"':''";  
            if(j!=inner_len-1){  
                text+=",";  
            }  
       }  
    }  
    text+="}";  
    text=eval("("+text+")");  
    var data={"total":1,"rows":[text]};  
    $('#'+gridid).datagrid('loadData',data);  
    // $('#grid').datagrid('appendRow',text);  
    $("tr[datagrid-row-index='0']").css({"visibility":"hidden"});  
    }
    
    function scrollShow(datagrid) {  
      datagrid.prev(".datagrid-view2").children(".datagrid-body").html("<div style='width:" + datagrid.prev(".datagrid-view2").find(".datagrid-header-row").width() + "px;border:solid 0px;height:1px;'></div>");  
    }

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.CheckinRecordMapper">
    	<resultMap type="java.util.Map" id="map"></resultMap>
    	<resultMap type="java.lang.String" id="String"></resultMap>
    
    	<select id="selectCheckinListByMonth" resultType="map"
    		parameterType="map">
    		SELECT  tmp.deptName deptName, tmp.userId userId, tmp.NAME name, tmp.records, tmp.checkin_date, ld.type, ld.localday
    		FROM 
    		(
    			SELECT any_value(b.deptName) as deptName, a.userId userId, a.name name,
    			GROUP_CONCAT(CONCAT(DATE_FORMAT(checkin_time, '%H:%i:%s'),' ',place)) records,
    			checkin_date
    		
    			FROM ding_checkinrecord a
    			RIGHT JOIN (SELECT u.userId userId, d.name deptName, u.department deptId 
    							FROM ding_user u
    							LEFT JOIN ding_dept d
    							ON u.department = d.id
    						
    							) b
    			ON a.userId = b.userId
    			GROUP BY a.userId, a.checkin_date
    			HAVING  a.userId = #{userId} 
    			) tmp
    		RIGHT JOIN localdays ld
    		ON tmp.checkin_date = ld.localday
    		WHERE ld.localday <![CDATA[ >= ]]> #{startDate} AND ld.localday <![CDATA[ <= ]]> #{endDate}
    		ORDER BY NAME DESC
    	</select>
    	<select id="selectCheckinUserIdsByMonth" resultType="String" parameterType="map">
    		SELECT DISTINCT a.userId userId
    		FROM ding_checkinrecord a
    		RIGHT JOIN (SELECT u.userId userId, d.name deptName, u.department deptId 
    						FROM ding_user u
    						LEFT JOIN ding_dept d
    						ON u.department = d.id
    						WHERE FIND_IN_SET(u.department, getChildDepts(#{deptId}))  ) b
    		ON a.userId = b.userId
    		GROUP BY a.userId, a.checkin_date
    		HAVING (a.checkin_date <![CDATA[ >= ]]> #{startDate} AND a.checkin_date <![CDATA[ <= ]]> #{endDate} )
    		limit 
                #{firstPage}, 
                #{rows}
    	</select>
    	
    	<select id="selectAllCheckinUserIdsByMonth" resultType="String" parameterType="map">
    		SELECT DISTINCT a.userId userId
    		FROM ding_checkinrecord a
    		RIGHT JOIN (SELECT u.userId userId, d.name deptName, u.department deptId 
    						FROM ding_user u
    						LEFT JOIN ding_dept d
    						ON u.department = d.id
    						WHERE FIND_IN_SET(u.department, getChildDepts(#{deptId}))  ) b
    		ON a.userId = b.userId
    		GROUP BY a.userId, a.checkin_date
    		HAVING (a.checkin_date <![CDATA[ >= ]]> #{startDate} AND a.checkin_date <![CDATA[ <= ]]> #{endDate} )
    	</select>
    	<select id="countCheckinUserIdsByMonth" resultType="int" parameterType="map">
    	select count(*) from (
    		SELECT DISTINCT a.userId userId
    		FROM ding_checkinrecord a
    		RIGHT JOIN (SELECT u.userId userId, d.name deptName, u.department deptId 
    						FROM ding_user u
    						LEFT JOIN ding_dept d
    						ON u.department = d.id
    						WHERE FIND_IN_SET(u.department, getChildDepts(#{deptId}))  ) b
    		ON a.userId = b.userId
    		GROUP BY a.userId, a.checkin_date
    		HAVING (a.checkin_date <![CDATA[ >= ]]> #{startDate} AND a.checkin_date <![CDATA[ <= ]]> #{endDate} )) a
    	</select>
    	<select id="countCheckinListByMonth" resultType="int" parameterType="map">
    		SELECT count(*)
    		FROM 
    		(
    			SELECT b.deptName deptName, a.userId userId, a.name name,
    			GROUP_CONCAT(CONCAT(DATE_FORMAT(checkin_time, '%H:%i:%s'),' ',place)) records,
    			checkin_date,
    			(SELECT TYPE FROM localdays WHERE localday = a.checkin_date) dateType
    			FROM ding_checkinrecord a
    			RIGHT JOIN (SELECT u.userId userId, d.name deptName, u.department deptId 
    							FROM ding_user u
    							LEFT JOIN ding_dept d
    							ON u.department = d.id
    							WHERE u.department = #{deptId}) b
    			ON a.userId = b.userId
    			GROUP BY a.userId, a.checkin_date
    			HAVING (a.checkin_date <![CDATA[ >= ]]> #{startDate} AND a.checkin_date <![CDATA[ <= ]]> #{endDate} )
    			and a.userId = #{userId} ) tmp
    		RIGHT JOIN localdays ld
    		ON tmp.checkin_date = ld.localday
    		WHERE ld.localday <![CDATA[ >= ]]> #{startDate} AND ld.localday <![CDATA[ <= ]]> #{endDate}
    	</select>
    	
    	<select id="getAllUsersByDeptId" parameterType="map" resultType="map">
    		SELECT u.userId userId,u.name name, d.name deptName, u.department deptId 
    			FROM ding_user u
    			LEFT JOIN ding_dept d
    			ON u.department = d.id
    			WHERE FIND_IN_SET(u.department, getChildDepts(#{deptId}))
    			ORDER BY u.userId
    			limit 
                #{firstPage}, 
                #{rows}
    	</select>
    	<select id="countAllUsersByDeptId" parameterType="map" resultType="int">
    	select count(*)
    	from(
    		SELECT u.userId userId,u.name name, d.name deptName
    			FROM ding_user u
    			LEFT JOIN ding_dept d
    			ON u.department = d.id
    			WHERE FIND_IN_SET(u.department, getChildDepts(#{deptId}))
    			ORDER BY u.userId
    			limit 
                #{firstPage}, 
                #{rows}) a
    	</select>
    
    </mapper>

package com.vooledingtalk.dao;
    
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    public interface CheckinRecordMapper {
    	/**
    	 * 获取签到考勤数据
    	 * @param map
    	 * @return
    	 */
    	
    	ArrayList<String> selectCheckinUserIdsByMonth(Map map);
    	int countCheckinUserIdsByMonth(Map map);
    	List<Map> selectCheckinListByMonth(Map map);
    	int  countCheckinListByMonth(Map map);
    	
    	/**
    	 * 用于导出功能
    	 * @param map
    	 * @return
    	 */
    	ArrayList<String> selectAllCheckinUserIdsByMonth(Map map);
    	/**
    	 * 获取指定部门下的所有员工的： 员工Id， 员工姓名，部门名称，部门Id
    	 * @param map
    	 * @return
    	 */
    	List<Map> getAllUsersByDeptId(Map map);
    	int countAllUsersByDeptId(Map map);
    	
    	
    }

Service层处理

package com.vooledingtalk.service;
    
    import java.util.List;
    import java.util.Map;
    
    
    
    public interface CheckinRecordService {
    
    	List<Map> selectCheckinListByMonth(Map params,boolean isExport);
    	
    	int countUserIdsByMonth(Map params);
    	
    	List<Map> getAllUsersByDeptId(Map params);
    	int countAllUsersByDeptId(Map params);
    
    }

package com.vooledingtalk.service.impl;
    
    import java.sql.Date;
    import java.time.LocalDate;
    import java.time.format.DateTimeFormatter;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.stereotype.Service;
    
    import com.vooledingtalk.common.utils.DateUtil;
    import com.vooledingtalk.dao.CheckinRecordMapper;
    import com.vooledingtalk.service.CheckinRecordService;
    
    import cucumber.deps.com.thoughtworks.xstream.mapper.Mapper.Null;
    
    
    @Service
    public class CheckinRecordServiceImpl implements CheckinRecordService {
    	
    	@Autowired
    	private CheckinRecordMapper checkinRecordMapper;
    
    	@Value("${STARTDATE}")
    	private String STARTDATE;
    	@Value("${ENDDATE}")
    	private String ENDDATE;
    	
    	public List<Map> selectCheckinListByMonth(Map params,boolean isExport) {
    		String recodeMonth = (String) params.get("recodeMonth");
    		long defaultDeptId = (Long) params.get("deptId");
    		HashMap<String, Object> map = new HashMap<String, Object>();
    
    		DateTimeFormatter ymd = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    
    		String startDate = DateUtil.getStartDate(recodeMonth);
    		String endDate = DateUtil.getEndDate(recodeMonth);
    
    		map.put("startDate", startDate);
    		map.put("endDate", endDate);
    		map.put("deptId", defaultDeptId);
    		map.put("firstPage", params.get("firstPage"));
    		map.put("rows",  params.get("rows"));
    		System.out.println("----params.get(firstPage)-------------------" +params.get("firstPage") + params.get("rows"));
    		//获取签到考勤人员列表
    		ArrayList<String> userIds = null;
    		if (isExport) {
    			userIds = checkinRecordMapper.selectAllCheckinUserIdsByMonth(map);
    		}else {
    			userIds = checkinRecordMapper.selectCheckinUserIdsByMonth(map);
    		}
    				
    		List<Map> tMaps = new ArrayList<Map>();
    		for (String userId : userIds) {
    			HashMap<String, Object> paramGet = new HashMap<String, Object>();
    			paramGet.put("startDate", startDate);
    			paramGet.put("endDate", endDate);
    			paramGet.put("deptId", defaultDeptId);
    			paramGet.put("userId", userId);
    			//获取指定人员的本月签到考勤数据
    			List<Map> list = checkinRecordMapper.selectCheckinListByMonth(paramGet);
    			HashMap<String, Object> resultMap = new HashMap<String, Object>();
    			System.out.println("--------size: " + list.size() + "-------" + list.get(0).get("deptName"));
    			resultMap.put("deptName", list.get(0).get("deptName"));
    			resultMap.put("userId", list.get(0).get("userId"));
    			resultMap.put("name", list.get(0).get("name"));
    			System.out.println("------list.get(0).get(name)----" + list.get(0).get("name"));
    			for (Map record : list) {
    				Date date = (Date) record.get("localday");
    				LocalDate date2 = LocalDate.parse(date.toString(), ymd);	
    				String recordsInfo =  (String) record.get("records");
    				if(recordsInfo!=null){
    					recordsInfo = (-1!=recordsInfo.indexOf(","))?recordsInfo.replaceAll(",", "</br>"):recordsInfo;
    				}
    				System.out.println("-------------" + recordsInfo);
    				resultMap.put(date2.getMonthValue() + "-" + date2.getDayOfMonth(),recordsInfo);
    			}
    			
    			tMaps.add(resultMap);
    		}
    		
    		return tMaps;
    	}
    
    	public int countUserIdsByMonth(Map params) {
    		String recodeMonth = (String) params.get("recodeMonth");
    		long defaultDeptId = (Long) params.get("deptId");
    		HashMap<String, Object> map = new HashMap<String, Object>();
    
    		String startDate = DateUtil.getStartDate(recodeMonth);
    		String endDate = DateUtil.getEndDate(recodeMonth);
    		map.put("startDate", startDate);
    		map.put("endDate", endDate);
    		map.put("deptId", defaultDeptId);
    		map.put("firstPage", params.get("firstPage"));
    		map.put("rows",  params.get("rows"));
    		return checkinRecordMapper.countCheckinUserIdsByMonth(map);
    	}
    
    	//以下为优化方案
    	
    	public List<Map> getAllUsersByDeptId(Map params) {
    		return checkinRecordMapper.getAllUsersByDeptId(params);
    	}
    
    	public int countAllUsersByDeptId(Map params) {	
    		return checkinRecordMapper.countAllUsersByDeptId(params);
    	}
    
    }

package com.vooledingtalk.service; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFWorkbook; public interface ExportCheckinDataService { HSSFWorkbook exportData(String month, List<Map> list); }

package com.vooledingtalk.service.impl; import java.time.LocalDate; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.stereotype.Service; import com.vooledingtalk.common.utils.DateUtil; import com.vooledingtalk.service.ExportCheckinDataService; import cucumber.deps.com.thoughtworks.xstream.mapper.Mapper.Null; @Service public class ExportCheckinDataServiceImpl implements ExportCheckinDataService { public HSSFWorkbook exportData( String month, List<Map> list) { // 创建工作空间 HSSFWorkbook wb = new HSSFWorkbook(); // 创建表 HSSFSheet sheet = wb.createSheet("签到考勤统计报表"); sheet.setDefaultColumnWidth(25); // sheet.setDefaultRowHeightInPoints(60); // 生成一个样式 HSSFCellStyle style = wb.createCellStyle(); style.setWrapText(true); // 创建行 HSSFRow row = sheet.createRow((int) 0); //表头标示串 用于和查询数据map中的key进行匹配 List<String> head = new ArrayList<String>(); head.add("name"); head.add("userId"); head.add("deptName"); LocalDate startDate = DateUtil.getStartDateDate(month); //开始时间上月20号 LocalDate endDate = DateUtil.getEndDateDate(month); //结束时间本月19号 while(!startDate.equals(endDate.minusDays(-1))){     //循环将如期加入表头行标示list中 head.add(startDate.getMonthValue()+"-"+startDate.getDayOfMonth()); startDate = startDate.minusDays(-1); } //添加表头数据 HSSFCell cell0 = row.createCell(0); cell0.setCellValue("姓名"); HSSFCell cell1 = row.createCell(1); cell1.setCellValue("员工ID"); HSSFCell cell2 = row.createCell(2); cell2.setCellValue("部门姓名"); for(int i = 3 ; i < head.size(); i++){ HSSFCell cell = row.createCell(i); cell.setCellValue(head.get(i)); cell.setCellStyle(style); } // 添加单元格数据 for(int i=0; i<list.size(); i++){ row = sheet.createRow(i + 1); Map map = list.get(i); for(int j=0; j<head.size();j++){ HSSFCell newCell = row.createCell(j); if (map.get(head.get(j))==null) { newCell.setCellValue(""); }else{ String tmp = map.get(head.get(j)).toString(); if (-1 == tmp.indexOf("</br>")) { newCell.setCellValue(tmp); }else { tmp = tmp.replace("</br>", "\r\n"); newCell.setCellValue(tmp); } } newCell.setCellStyle(style); // row.createCell(j).setCellValue(map.get(head.get(j))==null ? "" : map.get(head.get(j)).toString()); } row.setHeight((short)800); } return wb; } }

Controller层处理

package com.vooledingtalk.controller;
    
    
    import java.io.OutputStream;
    import java.io.UnsupportedEncodingException;
    import java.util.Date;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    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.common.TableResult;
    import com.vooledingtalk.dao.DingDeptMapper;
    import com.vooledingtalk.pojo.DingDept;
    import com.vooledingtalk.service.CheckinRecordService;
    import com.vooledingtalk.service.ExportCheckinDataService;
    
    import net.sf.json.JSONArray;
    import net.sf.json.JSONObject;
    
    
    @Controller
    public class CheckinRecordController {
    	
    	@Autowired
    	private DingDeptService dingDeptService;
    
    	@Autowired
    	private CheckinRecordService checkinRecordService;
    	
    	@Autowired
    	private ExportCheckinDataService exportCheckinDataService;
    	
    	@RequestMapping("/")
    	public String showIndex(){
    		return "index";
    	}
    	@RequestMapping("/checkin")
    	public String toCheckin(){
    		return "checkin";
    	}
    	
    	@SuppressWarnings("unchecked")
    	@RequestMapping("/record/recordlist")
    	@ResponseBody
    	public String getContentListByCatId(@RequestParam(value="deptId", required=false)long deptId, @RequestParam(value="recodeMonth", required=false)String recodeMonth,
    			@RequestParam(value="page", required=false)int page, @RequestParam(value="rows", required=false)int rows){
    		System.out.println("-------pageparam-----" + page);
    		Page page2 = new Page(page, rows);
    		@SuppressWarnings("rawtypes")
    		HashMap params = new HashMap();
    		
    		params.put("deptId", deptId);
    		params.put("recodeMonth", recodeMonth);
    		params.put("firstPage", page2.getFirstPage());
    		params.put("rows", rows);
    		
    		List<Map> list = checkinRecordService.selectCheckinListByMonth(params, false);
    		int total = checkinRecordService.countUserIdsByMonth(params);
    		System.out.println("------------total----" + total);
    		System.out.println("------------list.size()----" + list.size());
    		HashMap<String, Object> resultMap = new HashMap<String, Object>();
    		resultMap.put("rows", list);
    		resultMap.put("total", total);
    		ObjectMapper MAPPER = new ObjectMapper();
    		try {
    			String string = MAPPER.writeValueAsString(resultMap);
    			return string;
    		} catch (JsonProcessingException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    
    		return null;
    	}
    	
    	@RequestMapping("/record/recorddeptuser")
    	@ResponseBody
    	public Map getRecordDeptUser(@RequestParam(value="deptId", defaultValue="1")long deptId,
    			@RequestParam(value="page", required=false)int page, @RequestParam(value="rows", required=false)int rows){
    		Page page2 = new Page(page, rows);
    		HashMap<String,Object> params = new HashMap<String, Object>();
    		
    		params.put("deptId", deptId);
    		params.put("firstPage", page2.getFirstPage());
    		params.put("rows", rows);
    //		List<Map> list = ding;
    		int total = checkinRecordService.countAllUsersByDeptId(params);
    		return null;
    	}
    	
    	@RequestMapping("/exportcheckindata") public void export(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException { 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")); System.out.println("name" + jsonObject.get("name").toString() + " value" + jsonObject.get("value")); } catch (Exception e) { break; } } //以上为处理串过来的查询参数 Long deptId = Long.parseLong(params.get("deptId").toString()); DingDept dept = dingDeptService.selectDeptById(deptId);//获取部门信息，用于后面的excel文件名的拼接 params.put("deptId",deptId); List<Map> list = checkinRecordService.selectCheckinListByMonth(params, true);//根据查询参数进行查询数据 //以下Excel处理 HSSFWorkbook wb = exportCheckinDataService.exportData( params.get("recodeMonth").toString(), list); response.reset(); response.setContentType(" application/vnd.ms-excel"); //独立打开excel软件 String s = dept.getName()+params.get("recodeMonth").toString()+"月份签到考勤统计报表"+"-"+new Date().toLocaleString()+".xls"; String fileName = new String(s.getBytes("GB2312"), "ISO_8859_1"); response.setHeader("Content-Disposition", "attachment;filename=\""+fileName); response.setCharacterEncoding("UTF-8"); OutputStream ouputStream = null; try { ouputStream = response.getOutputStream(); wb.write(ouputStream); } catch (Exception e) { throw new RuntimeException("系统异常"); } finally { try { ouputStream.flush(); ouputStream.close(); } catch (Exception e) { throw new RuntimeException("系统异常"); } } } 
    }

总结：

[70]: /images/20220524/49a2196d3d6d449d8c4b5fb555ec9d4e.png