excel_0">导出大文件到excel
- 实体类
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("sys_log")
public class Log implements Serializable {
private static final long serialVersionUID = 1L;
@TableId
private Long id;
/**
* 日志分类（1-系统日志）
*/
@Excel(name = "日志分类", replace = {"系统_1", "业务_2", "审计_3", "安全_4", "告警_5", "登录_6"},
suffix = "日志", isImportField = "true_st")
@ApiModelProperty(value = "日志分类")
private Integer logCategory;
/**
* 日志类别(1-信息 2-警告)
*/
@Excel(name = "日志类别", replace = {"信息_1", "警告_2"}, isImportField = "true_st")
@ApiModelProperty(value = "日志类别")
private Integer logType;
/**
* 事件类型（1-系统事件 2-业务事件）
*/
@Excel(name = "事件类型", replace = {"系统事件_1", "业务事件_2"}, isImportField = "true_st")
@ApiModelProperty(value = "事件类型")
private Integer eventType;
/**
* 日志级别（1-高 2-中 3-低）
*/
@Excel(name = "日志级别", replace = {"高_1", "中_2", "低_3"}, isImportField = "true_st")
@ApiModelProperty(value = "日志级别")
private Integer logLevel;
/**
* 操作类型
*/
@Excel(name = "操作类型描述",isImportField = "true_st")
@ApiModelProperty(value = "操作类型描述")
private String operation;
/**
* 操作用户名
*/
@Excel(name = "用户名", isImportField = "true_st")
@ApiModelProperty(value = "用户名")
private String userName;
/**
* 客户端请求IP地址
*/
@Excel(name = "客户端IP", width = 25, isImportField = "true_st")
@ApiModelProperty(value = "客户端IP")
private String clientIp;
/**
* 日志请求地址
*/
@Excel(name = "请求路径", width = 50, isImportField = "true_st")
@ApiModelProperty(value = "日志请求地址")
private String uri;
/**
* 请求方式method,post,get等
*/
@Excel(name = "请求方式", isImportField = "true_st")
@ApiModelProperty(value = "请求方式")
private String method;
/**
* 请求参数内容,json
*/
@ApiModelProperty(value = "请求参数内容")
private String paramData;
/**
* 请求接口唯一session标识'
*/
@ApiModelProperty(value = "请求接口唯一session标识")
private String sessionId;
/**
* 接口返回时间
*/
@ApiModelProperty(value = "接口返回时间")
private String returmTime;
/**
* 接口返回数据json
*/
@ApiModelProperty(value = "接口返回数据json")
private String returnData;
/**
* 请求时httpStatusCode代码，如：200,400,404等
*/
@ApiModelProperty(value = "请求时httpStatusCode代码")
private String httpStatusCode;
/**
* 请求耗时（毫秒单位）
*/
@Excel(name = "响应时间（毫秒）", suffix = "ms", isImportField = "true_st")
@ApiModelProperty(value = "响应时间（毫秒）")
private Integer timeConsuming;
/**
* 请求结果(1-成功 2-失败)
*/
@Excel(name = "请求结果", replace = {"成功_1", "失败_2"}, isImportField = "true_st")
@ApiModelProperty(value = "请求结果")
private Integer requestResult;
/**
* 创建时间
*/
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
@ApiModelProperty(value = "创建时间")
private Date createTime;
/**
* 浏览器
*/
private String browser;
/**
* 操作系统
*/
private String os;
/**
* 用户代理
*/
private String userAgent;
/**
* 备注
*/
@ApiModelProperty(value = "备注")
private String remark;
/** 修改时间 */
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
private Date updateTime;
- 查询条件
@Data
public class SearchLogDto {
private String userName;
private String clientIp;
private String eventType;
private Integer logLevel;
private Integer operation;
private Integer requestResult;
private Integer logCategory;
/**
* 日志ids
*/
private List<Long> ids;
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
private Date startTime;
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
private Date endTime;
- 控制器
private static final int LIST_SIZE = 10000;
/**
* 导出日志
*
* @param dto 查询日志Dto
* @param response {@link HttpServletResponse}
* @throws
*/
@PostMapping("/export")
public void export(@RequestBody SearchLogDto dto, HttpServletResponse response) {
ExportParams exportParams = new ExportParams();
exportParams.setSheetName(Log.category(logCategory));
QueryWrapper<Log> logQueryWrapper = new QueryWrapper<Log>().like(StringUtils.isNotBlank(dto.getUserName()), "user_name", dto.getUserName()).
like(StringUtils.isNotBlank(dto.getClientIp()), "client_ip", dto.getClientIp()).eq(dto.getRequestResult() != null, "request_result", dto.getRequestResult()).eq(dto.getLogLevel() != null, "log_level", dto.getLogLevel())
.in(dto.getIds().size()>0,"id",dto.getIds());
if (dto.getStartTime() != null){
logQueryWrapper .ge(dto.getStartTime() != null, "create_time",dto.getStartTime());
}
if (dto.getEndTime() != null){
logQueryWrapper.le(dto.getEndTime() != null, "create_time", dto.getEndTime());
}
List<Log> list = logService.list(logQueryWrapper);
GetModelNameAndType.disposeLogList(list);
Workbook workbook = null;
int listSize = list.size();
//子集合的长度
int toIndex = 10000;
for (int i = 0; i < listSize; i += LIST_SIZE) {
if (i + LIST_SIZE > listSize) {
toIndex = listSize - i;
}
List<Log> newList = list.subList(i, i + toIndex);
workbook = ExcelExportUtil.exportBigExcel(exportParams, Log.class, newList);
}
if (workbook == null) {
workbook = ExcelExportUtil.exportBigExcel(exportParams, Log.class, new ArrayList<Log>(0));
}
ExcelExportUtil.closeExportBigExcel();
response.setContentType("application/x-download;charset=UTF-8");
String category = Log.category(logCategory);
try {
RequestUtils.setDownloadHeader(response, new String(category.getBytes(), "ISO8859-1") + ".xls");
} catch (UnsupportedEncodingException e) {
System.err.println(e.getMessage());
}
FileOutputStream fos = null;
InputStream input = null;
OutputStream output = null;
File file = null;
try {
fos = new FileOutputStream("/" + category + ".xls");
workbook.write(fos);
file = new File("/" + category + ".xls");
input = new FileInputStream(file);
output = response.getOutputStream();
byte[] buff = new byte[1024];
int len = 0;
while ((len = input.read(buff)) > -1) {
output.write(buff, 0, len);
}
} catch (IOException e) {
System.err.println(e.getMessage());
} finally {
try {
if (fos != null) {
fos.close();
}
} catch (IOException e) {
System.err.println(e.getMessage());
}
try {
if (output != null) {
output.close();
}
} catch (IOException e) {
System.err.println(e.getMessage());
}
try {
if (input != null) {
input.close();
}
} catch (IOException e) {
System.err.println(e.getMessage());
}
}
if (file != null) {
file.delete();
}
}
RequestUtils
public class RequestUtils {
/**
* 设置让浏览器弹出下载对话框的Header.
*
* @param filename
* 下载后的文件名.
*/
public static void setDownloadHeader(HttpServletResponse response, String filename) {
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + filename + "\"");
}
}
