导出大文件到excel(1)

导出大文件到excel

1. 实体类

@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;

2. 查询条件

@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;

3. 控制器

    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 + "\"");
    }

}