excel导出大文件到Excel(2)

excel导出大文件到Excel(2)

1. 实体类

/**
 * 日志实体类
 *
 * @author xiaohui
 * @version 1.0
 * @date 2019-05-29 16:45:54
 */
@Entity
@Table(name = "jc_sys_log")
public class SysLog extends AbstractDomain<Integer> implements Serializable {

	private static final long serialVersionUID = 1L;

	private Integer id;
	/**
	 * 日志分类（1-系统日志）
	 */
	@Excel(name = "日志分类", replace = {"系统_1", "业务_2", "审计_3", "安全_4", "告警_5"},
			suffix = "日志", isImportField = "true_st")
	private Integer logCategory;
	/**
	 * 日志类别(1-信息   2-警告)
	 */
	@Excel(name = "日志类别", replace = {"信息_1", "警告_2"}, isImportField = "true_st")
	private Integer logType;
	/**
	 * 事件类型（1-系统事件  2-业务事件）
	 */
	@Excel(name = "事件类型", replace = {"系统事件_1", "业务事件_2"}, isImportField = "true_st")
	private Integer eventType;
	/**
	 * 日志级别（1-高   2-中  3-低）
	 */
	@Excel(name = "日志级别", replace = {"高_1", "中_2", "低_3"}, isImportField = "true_st")
	private Integer logLevel;
	/**
	 * 操作类型（1-查询  2-新增  3-修改   4-删除  5-导出  6-导入 7-上传 8-下载）
	 */
	@Excel(name = "操作类型", replace = {"查询_1", "新增_2", "修改_3", "删除_4", "导出_5", "导入_6", "上传_7", "下载_8", "登录_9", "退出_10"},
			isImportField = "true_st")
	private Integer operateType;
	/**
	 * 事件子类型
	 */
	@Excel(name = "事件子类型", width = 25, isImportField = "true_st")
	private String subEventType;
	/**
	 * 操作用户名
	 */
	@Excel(name = "用户名", isImportField = "true_st")
	private String username;
	/**
	 * 客户端请求IP地址
	 */
	@Excel(name = "客户端IP", width = 25, isImportField = "true_st")
	private String clientIp;
	/**
	 * 日志请求地址
	 */
	@Excel(name = "请求路径", width = 50, isImportField = "true_st")
	private String uri;
	/**
	 * 请求方式method,post,get等
	 */
	@Excel(name = "请求方式", isImportField = "true_st")
	private String method;
	/**
	 * 请求参数内容,json
	 */
	private String paramData;
	/**
	 * 请求接口唯一session标识'
	 */
	private String sessionId;
	/**
	 * 接口返回时间
	 */
	private String returmTime;
	/**
	 * 接口返回数据json
	 */
	private String returnData;
	/**
	 * 请求时httpStatusCode代码，如：200,400,404等
	 */
	private String httpStatusCode;
	/**
	 * 请求耗时（毫秒单位）
	 */
	@Excel(name = "响应时间（毫秒）", suffix = "ms", isImportField = "true_st")
	private Integer timeConsuming;
	/**
	 * 请求结果(1-成功   2-失败)
	 */
	@Excel(name = "请求结果", replace = {"成功_1", "失败_2"}, isImportField = "true_st")
	private Integer requestResult;
	/**
	 * 创建时间
	 */
	@Excel(name = "操作时间", width = 35, databaseFormat = "yyyy-MM-dd HH:mm:ss", isImportField = "true_st")
	private Date createTime;
	/**
	 * 浏览器
	 */
	private String browser;
	/**
	 * 操作系统
	 */
	private String os;
	/**
	 * 用户代理
	 */
	private String userAgent;
	/**
	 * 备注
	 */
	private String remark;
	/**
	 * 响应时间（毫秒）
	 */
	private String responseTime;

2. 查询条件

@Data
public class SearchLogDto {

	private String username;
	private String clientIp;
	private String subEventType;
	private Integer logLevel;
	private Integer operateType;
	private Integer requestResult;
	private Integer logCategory;
	private Date beginDate;
	private Date endDate;
}

3. 控制器

/**
	 * 导出日志
	 *
	 * @param dto      查询日志Dto
	 * @param response {@link HttpServletResponse}
	 * @throws GlobalException 异常
	 */
	@PostMapping("/export")
	public void export(@RequestBody SearchLogDto dto, HttpServletResponse response) {
		ExportParams exportParams = new ExportParams();
		Integer logCategory = dto.getLogCategory() == null ? 1 : dto.getLogCategory();
		exportParams.setSheetName(LogConstants.category(logCategory));
		Map<String, String[]> params = new HashMap<>(9);
		params.put("LIKE_subEventType_String", new String[]{dto.getSubEventType()});
		if (dto.getBeginDate() != null) {
			params.put("GTE_createTime_Timestamp", new String[]{MyDateUtils.formatDate(dto.getBeginDate(),
					MyDateUtils.COM_Y_M_D_H_M_S_PATTERN)});
		}
		if (dto.getEndDate() != null) {
			params.put("LTE_createTime_Timestamp", new String[]{MyDateUtils.formatDate(dto.getEndDate(),
					MyDateUtils.COM_Y_M_D_H_M_S_PATTERN)});
		}
		params.put("LIKE_clientIp_String", new String[]{dto.getClientIp()});
		params.put("LIKE_username_String", new String[]{dto.getUsername()});
		params.put("EQ_logLevel_Integer", new String[]{dto.getLogLevel() != null ? String.valueOf(dto.getLogLevel()) : ""});
		params.put("EQ_operateType_Integer", new String[]{dto.getOperateType() != null
				? String.valueOf(dto.getOperateType()) : ""});
		params.put("EQ_requestResult_Integer", new String[]{dto.getRequestResult() != null
				? String.valueOf(dto.getRequestResult()) : ""});
		params.put("EQ_logCategory_Integer", new String[]{String.valueOf(logCategory)});
		List<SysLog> list = service.getList(params, null, false);
		Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams, SysLog.class, list);

		ExcelExportUtil.closeExportBigExcel();
		response.setContentType("application/x-download;charset=UTF-8");
		String category = LogConstants.category(logCategory);
		RequestUtils.setDownloadHeader(response, new String(category.getBytes(), StandardCharsets.ISO_8859_1) + ".xlsx");
		FileOutputStream fos = null;
		InputStream input = null;
		OutputStream output = null;
		File file = null;
		try {
			fos = new FileOutputStream(WebConstants.SPT + category + ".xlsx");
			workbook.write(fos);
			file = new File(WebConstants.SPT + category + ".xlsx");
			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) {
			log.error(e.getMessage());
		} finally {
			try {
				if (fos != null) {
					fos.close();
				}
			} catch (IOException e) {
				log.error(e.getMessage());
			}
			try {
				if (output != null) {
					output.close();
				}
			} catch (IOException e) {
				log.error(e.getMessage());
			}
			try {
				if (input != null) {
					input.close();
				}
			} catch (IOException e) {
				log.error(e.getMessage());

			}
		}
		if (file != null) {
			file.delete();
		}
	}