一、添加增强日志AOP
引入依赖 pom.xml(base)
<!--AOP-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>IpUtil.java(base)
package com.lyj.common.base.util;
import javax.servlet.http.HttpServletRequest;
import java.net.InetAddress;
import java.net.UnknownHostException;
/**
* IP工具类
*/
public class IpUtil {
public static String getIpAddr(HttpServletRequest request) {
if (null == request) {
return "null";
}
String ipAddress = null;
try {
ipAddress = request.getHeader("x-forwarded-for");
if (ipAddress == null || ipAddress.length() == 0 || "unknown".equalsIgnoreCase(ipAddress)) {
ipAddress = request.getHeader("Proxy-Client-IP");
}
if (ipAddress == null || ipAddress.length() == 0 || "unknown".equalsIgnoreCase(ipAddress)) {
ipAddress = request.getHeader("WL-Proxy-Client-IP");
}
if (ipAddress == null || ipAddress.length() == 0 || "unknown".equalsIgnoreCase(ipAddress)) {
ipAddress = request.getRemoteAddr();
if (ipAddress.equals("127.0.0.1") || ipAddress.equals("0:0:0:0:0:0:0:1")) {
// 根据网卡取本机配置的IP
InetAddress inet = null;
try {
inet = InetAddress.getLocalHost();
} catch (UnknownHostException e) {
e.printStackTrace();
}
ipAddress = inet.getHostAddress();
}
}
// 对于通过多个代理的情况，第一个IP为客户端真实IP,多个IP按照','分割
if (ipAddress != null && ipAddress.length() > 15) { // "***.***.***.***".length()
// = 15
if (ipAddress.indexOf(",") > 0) {
ipAddress = ipAddress.substring(0, ipAddress.indexOf(","));
}
}
} catch (Exception e) {
ipAddress = "";
}
// ipAddress = this.getRequest().getRemoteAddr();
return ipAddress;
}
}
Log.java(base)
package com.lyj.common.base.aspect;
import java.lang.annotation.*;
/**
* 日志AOP注解
*/
@Target({ElementType.METHOD, ElementType.PARAMETER})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Log {
}
IpLogAspect.java(base)
package com.lyj.common.base.aspect;
import com.lyj.common.base.util.IpUtil;
import lombok.AllArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.Signature;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletRequest;
/**
* aop切面
* 增强@log注解
* 在controller前后记录请求信息
*/
@Component
@Aspect
@Slf4j
@AllArgsConstructor
public class IpLogAspect {
private final HttpServletRequest request;
//定义切点
@Pointcut("@annotation(com.lyj.common.base.aspect.Log)")
public void pointCut() {
}
@Pointcut("execution(public * com.lyj.service..api..*.*(..))")
public void controller() {
}
/**
* api 日志
*
* @param joinPoint
* @return
*/
@Around("controller()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
Signature signature = joinPoint.getSignature();
Object[] args = joinPoint.getArgs();
String ipAddr = IpUtil.getIpAddr(request);
String requestURL = request == null ? "" : request.getRequestURL().toString();
long start = System.currentTimeMillis();
log.info("start:{}|url:{}|method:{}|args:{}|customer_ip:{}", start, requestURL, signature, args, ipAddr);
Object proceed = null;
try {
proceed = joinPoint.proceed();
} finally {
log.info("end:{}|consume:{}|url:{}|method:{}|customer_ip:{}", System.currentTimeMillis(), System.currentTimeMillis() - start, requestURL, signature, ipAddr);
}
return proceed;
}
/**
* 内部方法
*
* @param joinPoint
* @return
*/
@Around("pointCut()")
public Object around1(ProceedingJoinPoint joinPoint) throws Throwable {
Signature signature = joinPoint.getSignature();
Object[] args = joinPoint.getArgs();
log.info("start:{}|method:{}|args:{}", System.currentTimeMillis(), signature, args);
Object proceed = null;
try {
proceed = joinPoint.proceed();
} finally {
log.info("end:{}|method:{}|args:{}", System.currentTimeMillis(), signature, args);
}
return proceed;
}
}
启动测试
增强前
增强后
二、集成Excel处理工具(easyexcel)
引入依赖
pom.xml(initMode)
<!--Excel处理工具-->
<easyexcel.version>3.0.5</easyexcel.version>
<dependency>
<groupId>io.jsonwebtoken</groupId>
<artifactId>jjwt-impl</artifactId>
<version>${jjwt.version}</version>
</dependency>pom.xml(base)
<!--Excel处理工具-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
</dependency>工具类ExcelUtil.java(base)
package com.lyj.service.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.CellData;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.property.ExcelWriteHeadProperty;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @description: Excel 文件导出工具类
*/
public class ExcelUtil {
private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
private static final int MAX_ROWS = 1000;
private static final int DEFAULT_VALUE_ONE = 1;
public static final String SUFFIX_1 = ".xls";
public static final String SUFFIX_2 = ".xlsx";
/**
* 导出
*
* @param fileName 导出文件名称
* @param exportData 导出的数据集合
* @param clazz 导出映射的实体类
* @param needSn 是否需要序号
*/
public static void export(String fileName, List<?> exportData, Class<?> clazz, HttpServletResponse response, Boolean needSn) {
OutputStream outputStream = null;
try {
outputStream = getOutputStream(fileName, response);
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream, clazz)
.registerWriteHandler(new CustomCellWriteHandler());
if (needSn) {
excelWriterBuilder.registerWriteHandler(new OrderNumberRowWriteHandler());
}
excelWriterBuilder.sheet(fileName).doWrite(exportData);
outputStream.flush();
} catch (Exception e) {
log.error("excel导出异常-{},导出数据：{} 异常原因：{}", fileName, exportData.toString(), e.getMessage());
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
} catch (Exception e) {
log.error("excel导出异常：{}", e.getMessage());
}
}
}
/**
* 导出 列6,7 列宽减少一定比例长度
*
* @param fileName 导出文件名称
* @param exportData 导出的数据集合
* @param clazz 导出映射的实体类
* @param needSn 是否需要序号
*/
public static void exportRewriteColumnWidth(String fileName, List<?> exportData, Class<?> clazz, HttpServletResponse response, Boolean needSn) {
OutputStream outputStream = null;
try {
outputStream = getOutputStream(fileName, response);
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream, clazz)
.registerWriteHandler(new CustomCellWriteHandlerRewrite());
if (needSn) {
excelWriterBuilder.registerWriteHandler(new OrderNumberRowWriteHandler());
}
excelWriterBuilder.sheet(fileName).doWrite(exportData);
outputStream.flush();
} catch (Exception e) {
log.error("excel导出异常-{},导出数据：{} 异常原因：{}", fileName, exportData.toString(), e.getMessage());
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
} catch (Exception e) {
log.error("excel导出异常：{}", e.getMessage());
}
}
}
/**
* 使用模板导出，未适配【第一列的序号】
*
* @param fileName 导出文件名称
* @param exportData 导出的数据集合
* @param classPathResource 导出映射的模板
*/
public static void export(String fileName, List<?> exportData, HttpServletResponse response,
ClassPathResource classPathResource) {
OutputStream outputStream = null;
ExcelWriter excelWriter = null;
try {
//设置响应流的格式
outputStream = getOutputStream(fileName, response);
//构造需要返回的模板
ExcelWriterBuilder writerBuilder = EasyExcel
.write(outputStream)
.withTemplate(classPathResource.getInputStream())
.excelType(ExcelTypeEnum.XLSX);
//生成excel写入构造器
excelWriter = writerBuilder.build();
//写入数据到模板中
excelWriter.fill(exportData,EasyExcel.writerSheet(0).build());
//刷新响应流
outputStream.flush();
//完成写入
excelWriter.finish();
} catch (Exception e) {
log.error("excel导出异常-{},导出数据：{} 异常原因：{}", fileName, exportData.toString(), e.getMessage());
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
if (excelWriter != null) {
excelWriter.finish();
}
} catch (Exception e) {
log.error("excel导出异常：{}", e.getMessage());
}
}
}
/**
* 导出
*
* @param exportData 导出的数据集合
* @param clazz 导出映射的实体类
* @param needSn 是否需要序号
*/
public static void export(File file, List<?> exportData, Class<?> clazz, Boolean needSn) {
export(file, exportData, null, clazz, needSn, null);
}
public static void export(File file, List<?> exportData, Class<?> clazz, Boolean needSn, List<List<String>> head) {
export(file, exportData, null, clazz, needSn, head);
}
public static void export(File file, List<?> exportData, List<String> exclude, Class<?> clazz, Boolean needSn) {
export(file, exportData, exclude, clazz, needSn, null);
}
public static void export(File file, List<?> exportData, List<String> excludeFields, Class<?> clazz, Boolean needSn, List<List<String>> head) {
String name = file.getName();
try {
ExcelWriterBuilder excelWriterBuilder = EasyExcelFactory
.write(file, clazz)
.registerWriteHandler(new CustomCellWriteHandler());
if (needSn) {
excelWriterBuilder.registerWriteHandler(new OrderNumberRowWriteHandler());
}
if (CollectionUtils.isNotEmpty(excludeFields)) {
excelWriterBuilder.excludeColumnFiledNames(excludeFields);
}
if (CollectionUtils.isNotEmpty(head)) {
excelWriterBuilder.head(head);
}
excelWriterBuilder.sheet("导出数据").doWrite(exportData);
} catch (Exception e) {
log.error("excel导出异常-{},导出数据：{} 异常原因：{}", name, exportData.toString(), e.getMessage(), e);
}
}
public static void export(String fileName, List<?> exportData, List<String> excludeFields, Class<?> clazz, HttpServletResponse response, Boolean needSn) {
OutputStream outputStream = null;
try {
outputStream = getOutputStream(fileName, response);
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream, clazz)
.registerWriteHandler(new CustomCellWriteHandler());
if (needSn) {
excelWriterBuilder.registerWriteHandler(new OrderNumberRowWriteHandler());
}
if (CollectionUtils.isNotEmpty(excludeFields)) {
excelWriterBuilder.excludeColumnFiledNames(excludeFields);
}
excelWriterBuilder.sheet(fileName).doWrite(exportData);
outputStream.flush();
} catch (Exception e) {
log.error("excel导出异常-{},导出数据：{} 异常原因：{}", fileName, exportData.toString(), e.getMessage());
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
} catch (Exception e) {
log.error("excel导出异常：{}", e.getMessage());
}
}
}
public static void export(String fileName, List<?> exportData, Class<?> clazz, HttpServletResponse response, Boolean needSn, List<List<String>> head) {
OutputStream outputStream = null;
try {
outputStream = getOutputStream(fileName, response);
ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(outputStream, clazz)
.registerWriteHandler(new CustomCellWriteHandler());
if (needSn) {
excelWriterBuilder.registerWriteHandler(new OrderNumberRowWriteHandler());
}
if (CollectionUtils.isNotEmpty(head)) {
excelWriterBuilder.head(head);
}
excelWriterBuilder.sheet(fileName).doWrite(exportData);
outputStream.flush();
} catch (Exception e) {
log.error("excel导出异常-{},导出数据：{} 异常原因：{}", fileName, exportData.toString(), e.getMessage());
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
} catch (Exception e) {
log.error("excel导出异常：{}", e.getMessage());
}
}
}
public static void setContentHead() {
//1.设置请求头
ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
RequestContextHolder.setRequestAttributes(servletRequestAttributes, true);
}
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
response.setContentType("application/octet-stream");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" +
URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
response.setCharacterEncoding("UTF-8");
return response.getOutputStream();
}
/**
* 自适应列宽设置
*/
public static class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == CellDataTypeEnum.STRING) {
if (cell.getStringCellValue().contains("汇总时段") || cell.getStringCellValue().contains("总计工时")) {
return;
}
}
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = dataLength(cellDataList, cell, isHead) + 1;
if (columnWidth >= 0) {
if (columnWidth > 50) {
columnWidth = 50;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
}
/**
* 重写自适应列宽设置
*/
public static class CustomCellWriteHandlerRewrite extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == CellDataTypeEnum.STRING) {
if (cell.getStringCellValue().contains("汇总时段") || cell.getStringCellValue().contains("总计工时")) {
return;
}
}
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());
Integer columnWidth = dataLength(cellDataList, cell, isHead) + 1;
if (columnWidth >= 0) {
columnWidth = updateDataLength(isHead, cell.getColumnIndex(), columnWidth);
if (columnWidth > 255) {
columnWidth = 255;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
}
private static Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
}
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
}
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
case DATE:
return ((WriteCellData) cellData).getDateValue().toString().getBytes().length;
default:
return -1;
}
}
public static Integer updateDataLength(Boolean isHead, int columnIndex, Integer column) {
if (Boolean.TRUE.equals(isHead) || column == null || column == 0) {
return column;
}
if (columnIndex == 7) {
return (int) Math.ceil(column * (0.76));
} else if (columnIndex == 8) {
return (int) Math.ceil(column * (0.70));
}
return column;
}
/**
* 导出文件列序号
*/
public static class OrderNumberRowWriteHandler implements RowWriteHandler {
private boolean init = true;
private static final String TITLE = "序号";
@Override
public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
if (init) {
// 修改存储头部及对应字段信息的 map, 将其中的内容均右移一位, 给新增的序列号预留为第一列
ExcelWriteHeadProperty excelWriteHeadProperty = writeSheetHolder.excelWriteHeadProperty();
Map<Integer, Head> headMap = excelWriteHeadProperty.getHeadMap();
int size = headMap.size();
for (int current = size; current > 0; current--) {
int previous = current - 1;
headMap.put(current, headMap.get(previous));
}
// 空出第一列
headMap.remove(0);
// 只需要修改一次 map 即可, 故使用 init 变量进行控制
init = false;
}
}
@Override
public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead) {
// 在行创建完成后添加序号列
Cell cell = row.createCell(0);
int rowNum = row.getRowNum();
if (rowNum == 0) {
cell.setCellValue(OrderNumberRowWriteHandler.TITLE);
} else {
cell.setCellValue(rowNum);
}
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Integer relativeRowIndex, Boolean isHead) {
if (row.getLastCellNum() > 1) {
// 将自定义新增的序号列的样式设置与默认的样式一致
row.getCell(0).setCellStyle(row.getCell(1).getCellStyle());
row.getCell(0).getCellStyle().setAlignment(HorizontalAlignment.LEFT);
}
}
}
}
测试
SysUserService.java(shiro)
void export(String id, HttpServletResponse servletResponse);SysUserServiceImpl.java(shiro)
@Override
public void export(String id, HttpServletResponse servletResponse) {
SysUser sysUser = this.getById(id);
List list = new ArrayList();
list.add(sysUser);
String localDate = LocalDateTime.now().format(DateTimeFormatter.ofPattern(DateUtil.PATTERN_YEAR_MONTH_DAY_HH_MM_SS));
ExcelUtil.export("用户信息-" + localDate, list, SysUserVO.class, servletResponse, Boolean.FALSE);
}SysUserVO.java
package com.lyj.common.domain.sysUser.vo;
import java.time.LocalDateTime;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.experimental.Accessors;
import java.io.Serializable;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
/**
* 管理端用户表(SysUser)表VO层
*
* @author lyj
* @since 2024-07-02 19:14:02
*/
@Data
@Accessors(chain = true) //不写默认为false，当该值为 true 时，对应字段的 getter 方法前面就没有 get，setter 方法就不会有 set。
@ApiModel("管理端用户表VO")
public class SysUserVO implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键Id
*/
@ApiModelProperty(value = "主键Id")
@ExcelIgnore
private String id;
/**
* 账号
*/
@ApiModelProperty(value = "账号")
@ExcelProperty("账号")
private String account;
/**
* 用户姓名
*/
@ApiModelProperty(value = "用户姓名")
@ExcelProperty("用户姓名")
private String name;
/**
* 用户昵称
*/
@ApiModelProperty(value = "用户昵称")
@ExcelProperty("用户昵称")
private String nickname;
/**
* 用户密码 （同步数据时 设置初始密码）
*/
@ApiModelProperty(value = "用户密码")
@ExcelProperty("用户密码")
private String password;
/**
* 盐
*/
@ApiModelProperty(value = "盐")
@ExcelProperty("盐")
private String salt;
/**
* 用户邮箱
*/
@ApiModelProperty(value = "用户邮箱")
@ExcelProperty("用户邮箱")
private String email;
/**
* 手机号码
*/
@ApiModelProperty(value = "手机号码")
@ExcelProperty("手机号码")
private String phone;
/**
* 办公手机号
*/
@ApiModelProperty(value = "办公手机号")
@ExcelProperty("办公手机号")
private String workPhone;
/**
* 角色id
*/
@ApiModelProperty(value = "角色id")
@ExcelProperty("角色id")
private String roleId;
/**
* 部门id
*/
@ApiModelProperty(value = "部门id")
@ExcelProperty("部门id")
private String deptId;
/**
* 账号状态 0：有效 1：失效
*/
@ApiModelProperty(value = "账号状态 0：有效 1：失效")
@ExcelProperty("账号状态 0：有效 1：失效")
private Integer status;
/**
* 创建时间
*/
@ApiModelProperty(value = "创建时间")
@ExcelProperty("创建时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private LocalDateTime createTime;
/**
* 更新时间
*/
@ApiModelProperty(value = "更新时间")
@ExcelProperty("更新时间")
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
private LocalDateTime updateTime;
/**
* 是否删除
*/
@ApiModelProperty(value = "是否删除")
@ExcelProperty("是否删除")
private String deleteFlag;
}
testApi.java(manegement)
@ApiOperation(value = "导出用户列表", produces = "application/octet-stream")
@GetMapping("/export")
@ApiImplicitParams({
@ApiImplicitParam(name = "id", value = "用户id", required = true)
})
public void export(@NotEmpty(message = "用户id不能为空")String id, HttpServletResponse servletResponse) {
sysUserService.export(id,servletResponse);
}启动测试
340
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
