一、前期准备
①所需jar包
②ssm框架以及tomcat环境
二、实例
1、pojo层
@ExcelProperty(value = "日志编号", index = 0)
标题,以及标题位置第几个
@ColumnWidth(20)
行宽
package com.ruanyuan.zy.utils;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
/**
* 导出工具类
*
* @author 赵皮球
*
* 2023年5月10日
*/
@TableName("excel_demo_data")
public class Export {
@ExcelProperty(value = "日志编号", index = 0)
@ColumnWidth(20)
private Integer id;
@ExcelProperty(value = "日志类型,1登录,2操作", index = 1)
@ColumnWidth(20)
private String type;
@ExcelProperty(value = "日志内容", index = 2)
@ColumnWidth(20)
private String content;
@ExcelProperty(value = "操作用户", index = 3)
@ColumnWidth(20)
@TableField("empno")
private String empno;
@ExcelProperty(value = "操作时间", index = 4)
@ColumnWidth(20)
private String time;
/**
* 无参构造
*
*/
public Export() {
super();
// TODO Auto-generated constructor stub
}
/**
* 有参构造
*
* @param id 日志编号
* @param type 日志类型,1登录,2操作
* @param content 操作内容
* @param empno 操作用户,外键用户表
* @param time 操作时间
*/
public Export(Integer id, String type, String content, String empno, String time) {
super();
this.id = id;
this.type = type;
this.content = content;
this.empno = empno;
this.time = time;
}
/**
* 各个属性的getter/setter
*
* @return
*/
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getEmpno() {
return empno;
}
public void setEmpno(String empno) {
this.empno = empno;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
/**
* 重写toString
*
*/
@Override
public String toString() {
return "Logs [id=" + id + ", type=" + type + ", content=" + content + ", empno=" + empno + ", time=" + time
+ "]";
}
}
2、mapper层
①mapper.java,我这里是logsMapper.java,根据需要更改
package com.ruanyuan.zy.mapper;
import java.util.List;
import com.ruanyuan.zy.pojo.Logs;
/**
* 系统日志 Mapper 接口层
*
* @author 赵皮球
*
* 2023年5月1日
*/
public interface LogsMapper {
/**
* 查询所有的日志
*
* @return
*/
public List<Logs> getAllLogs();
}
②mapper.xml,我这里是logsMapper.xml,根据需要更改
<?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.ruanyuan.zy.mapper.LogsMapper">
<!-- 查询所有日志数据 -->
<select id="getAllLogs" parameterType="Logs" resultType="Logs">
SELECT *
FROM `logs` ORDER BY `time` DESC
</select>
</mapper>
3、service层
①service.java
package com.ruanyuan.zy.service;
import java.util.List;
import com.ruanyuan.zy.pojo.Logs;
/**
* 系统日志 服务类
*
* @author 赵皮球
*
* 2023年5月1日
*/
public interface LogsService {
/**
* 查询所有的日志
*
* @return
*/
public List<Logs> getAllLogs();
}
②serviceImpl.java
package com.ruanyuan.zy.service.impl;
import com.ruanyuan.zy.mapper.LogsMapper;
import com.ruanyuan.zy.pojo.Logs;
import com.ruanyuan.zy.service.LogsService;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
* 系统日志 服务实现类
*
* @author 赵皮球
*
* 2023年5月1日
*/
@Service
public class LogsServiceImpl implements LogsService {
@Autowired
private LogsMapper logsMapper;
/**
* 重写查询所有的日志
*
* @return
*/
@Override
public List<Logs> getAllLogs() {
// TODO Auto-generated method stub
return logsMapper.getAllLogs();
}
}
4、controller层(关键代码,最后一步)
List<Logs> logsList = logsMapper.getAllLogs();
后续步骤有
/**
* 日志导出
*
* @return
*/
@RequestMapping("/exportExcel")
public String exportExcel(HttpServletResponse response, Model model) {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 获取所有的日志信息
List<Logs> logsList = logsMapper.getAllLogs();
// 文件名
String fileName;
fileName = "登录日志";
try {
// 文件名,编码格式
fileName = URLEncoder.encode(fileName, "UTF-8");
// 文件格式
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 使用EasyExcel工具写excel
EasyExcel.write(response.getOutputStream(), Export.class).sheet("logs").doWrite(logsList);
} catch (IOException e) {
e.printStackTrace();
}
// 跳转列表页面,这里有配置文件统一写了后缀名,无需写后缀名.jsp
return "logsList";
}
5、logsList.jsp页面
关键代码超链接,调用controller中的导出接口方法
<a href="${pageContext.request.contextPath}/exportExcel">全部导出excel </a>
(页面样式以及数据仅供参考)
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<meta name="renderer" content="webkit|ie-comp|ie-stand">
<title>日志管理</title>
</head>
<body>
<nav class="breadcrumb">
<i class="Hui-iconfont"></i>
首页
<span class="c-gray en">></span>
系统管理
<span class="c-gray en">></span>
登录日志管理
<a class="btn btn-success radius r"
style="line-height: 1.6em;
margin-top: 3px"
href="javascript:location.replace(location.href);"
title="刷新">
<i class="Hui-iconfont"></i>
</a>
</nav>
<div class="page-container">
<div class="page-container">
<div class="cl pd-5 bg-1 bk-gray">
<span class="l">
<a href="javascript:;"
id="delmore"
class="btn btn-danger radius">
<i class="Hui-iconfont"></i>
批量删除
</a>
<a class="btn btn-primary radius "
href="${pageContext.request.contextPath}/exportExcel?type=${type}&empno=${employee.empno}"
target="dataOperate">
<i class="Hui-iconfont"></i>
全部导出excel
</a>
<a class="" target="dataOperate"></a>
</span>
<span class="r">
共有数据:<strong>${sum }</strong> 条
</span>
</div>
<table class="table table-border table-bordered table-hover table-bg">
<thead>
<tr>
<th scope="col" colspan="6">登录日志管理</th>
</tr>
<tr class="text-c">
<th width="25"><input type="checkbox" value="" name=""></th>
<th width="200">ID</th>
<th width="200">登录员工</th>
<th width="200">登录IP</th>
<th width="200">操作日期</th>
<th width="70">操作</th>
</tr>
</thead>
<tbody>
<c:forEach items="${logsList }" var="logsList">
<tr class="text-c">
<input type="hidden" value="${type }" id="type">
<td><input type="checkbox" value="${logsList.id }" name="id"></td>
<td>${logsList.id }</td>
<td>${logsList.ename}</td>
<td>${logsList.content }</td>
<td>${logsList.time }</td>
<td class="f-14">
<a title="删除" href="javascript:;" class="ml-5 delone" style="text-decoration: none">
<i class="Hui-iconfont"></i>
</a>
</td>
</tr>
</c:forEach>
</tbody>
</table>
</div>
</body>
</html>
三、效果