前言
最近做的一个需求要求归档系统的运行日志,由于公司系统是用于对接三方系统与金蝶系统,所以日志的记录十分重要并且由于接口访问量比较大所以考虑用excel归档所有日志。在做的过程中发现某些字段的值对特别多超过excel的单元格的最大存储量,因此设计了用txt存储,单元格存对应的相对地址的超链接的设计方案。
//zos是写入压缩包的文件流
private FileOutputStream fos = new FileOutputStream("ysb");
private UncloseableZipOutputStream zos = new UncloseableZipOutputStream(fos);
//最大的excel的存储值,我给缩小了7
static Integer MAXLENGTH = 32760;
//数据分页的最大值,根据个人的服务器能力可改
static Integer MAXPAGESIZE = 5000;
主要函数
public void downloadLogExcel(HttpServletResponse response, String localData) throws IOException {
log.info("文件导出开始" + LocalDateTime.now());
//创建文件流
ServletOutputStream outputStream = response.getOutputStream();
response.setContentType("application/zip");
response.setCharacterEncoding("utf-8");
//设置头
response.setHeader("Pragma", "No-cache");
//设置头
response.setHeader("Cache-Control", "no-cache");
//设置日期头
response.setDateHeader("Expires", 0);
zos = new UncloseableZipOutputStream(outputStream);
String nowDate = "";
//调用接口查询场景配置数据
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
if (localData == null || "".equals(localData)) {
nowDate = LocalDateTime.now().format(formatter);
localData = LocalDateTime.now().minusDays(1).format(formatter);
} else {
nowDate = LocalDateTime.now().format(formatter);
}
String timeFile = localData + "-" + nowDate;
response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + timeFile + ".zip");
Integer pageCount = productLogMapper.seleCount(localData, nowDate);
log.info("总行数" + pageCount);
List<ProductLogListOutVoRecords> records = new ArrayList<>();
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ExcelWriter excelWriter = EasyExcel.write(baos).build();
if (pageCount > MAXPAGESIZE) {
for (int i = 0; i < (pageCount % MAXPAGESIZE == 0 ? (pageCount / MAXPAGESIZE) : (pageCount / MAXPAGESIZE + 1)); i++) {
if (i == 0) {
records = productLogMapper.productLogListToExcel(localData, nowDate, "", MAXPAGESIZE);
} else {
String newLastId = records.isEmpty() ? "" : records.get(records.size() - 1).getId();
records = productLogMapper.productLogListToExcel(localData, nowDate, newLastId, MAXPAGESIZE);
}
for (ProductLogListOutVoRecords temp : records) {
//检查特别的字段即可能会超过最大长度的
checkFieldLengths(temp, timeFile);
}
//我采用的是5000为一个子表,会形成多子表的形式
WriteSheet writeSheet = EasyExcel.writerSheet(i, "日志归档" + (i + 1)).head(ProductLogListOutVoRecords.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
.registerWriteHandler(new HyperlinkCellWriteHandler())
.build();
excelWriter.write(records, writeSheet);
}
} else {
records = productLogMapper.productLogListToExcel(localData, nowDate, "", pageCount);
for (ProductLogListOutVoRecords temp : records) {
checkFieldLengths(temp, timeFile);
}
// 第一页写入场景配置
WriteSheet writeSheet = EasyExcel.writerSheet("日志归档")
.registerWriteHandler(new HyperlinkCellWriteHandler())
.head(ProductLogListOutVoRecords.class)
.build();
excelWriter.write(records, writeSheet);
}
**//写完数据后一定要关闭excel的文件流否则会导致数据写入不了**
excelWriter.finish();
createZipWithExcelAndTxt(baos);
//将缓存区的数据输出
outputStream.flush();
//关流
response.getOutputStream().close();
}
我采用的是mybatis去实现取数据,这里分页取数据我采用的是基于主键的分页策略。
假设你有一个 tb_product_log 表,包含字段 id(主键)和 create_time(时间戳)。实现基于主键的分页查询。
首次查询(第一页):
SELECT id, create_time, other_necessary_fields
FROM tb_product_log
WHERE create_time >= #{createTime}
AND create_time <= #{endTime}
ORDER BY id
LIMIT #{pageSize};
#{pageSize}: 每页记录数
ORDER BY id: 按主键排序
后续分页查询(第二页及以后):翻到下一页时,保存当前页最后一条记录的主键 lastId,并用它来查询下一页的数据。
SELECT id, create_time, other_necessary_fields
FROM tb_product_log
WHERE create_time >= #{createTime}
AND create_time <= #{endTime}
AND id > #{lastId} -- 上一页最后一条记录的主键
ORDER BY id
LIMIT #{pageSize};
这样的查询效率比limit高很多。不过要注意一点主键id的设计是递增的而不是没有规律的。
<select id="productLogListToExcel"
resultType="com.lingqingkeji.integration.system.transfer.product.ProductLogListOutVoRecords">
SELECT * FROM tb_product_log
WHERE create_time >= #{createTime}
AND create_time <= #{endTime}
<if test="lastId != null and lastId != ''">
AND id > #{lastId} -- `lastId` 是上一页的最后一个记录的主键值
</if>
ORDER BY id
LIMIT #{pageSize}
</select>
检查字段并且将对应单元格的值设置为超链接的地址。这个fileLink是绝对地址的写法,如果是程序不是用docker去部署的话可以用这个写法,如果是用则用的相对地址我这里是在拦截器里面进行了调整。
public void checkFieldLengths(ProductLogListOutVoRecords productLogListOutVoRecords, String timeFile) throws IOException {
if (productLogListOutVoRecords.getException() != null && productLogListOutVoRecords.getException().length() >= MAXLENGTH) {
String fileLink = saveLongTextToFile(productLogListOutVoRecords.getException());
fileLink = "file:///" + fileLink.replace("\\", "/");
productLogListOutVoRecords.setException(fileLink);
}
if (productLogListOutVoRecords.getResponse() != null && productLogListOutVoRecords.getResponse().length() >= MAXLENGTH) {
String fileLink = saveLongTextToFile(productLogListOutVoRecords.getResponse());
fileLink = "file:///" + fileLink.replace("\\", "/");
productLogListOutVoRecords.setResponse(fileLink);
}
if (productLogListOutVoRecords.getAdaptRequest() != null && productLogListOutVoRecords.getAdaptRequest().length() >= MAXLENGTH) {
String fileLink = saveLongTextToFile(productLogListOutVoRecords.getAdaptRequest());
fileLink = "file:///" + fileLink.replace("\\", "/");
productLogListOutVoRecords.setAdaptRequest(fileLink);
}
if (productLogListOutVoRecords.getAdaptResponse() != null && productLogListOutVoRecords.getAdaptResponse().length() >= MAXLENGTH) {
String fileLink = saveLongTextToFile(productLogListOutVoRecords.getAdaptResponse());
fileLink = "file:///" + fileLink.replace("\\", "/");
productLogListOutVoRecords.setAdaptResponse(fileLink);
}
}
由于excel的最大长度我设计了用超链接的形式所以需要在注册一个拦截器去将对应的单元格转变为超链接的格式。相对地址直接用对应的文件名即可,easyExcel会直接转换的。
public class HyperlinkCellWriteHandler implements CellWriteHandler {
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
Cell cell = context.getCell();
if (cell.getCellType() == CellType.STRING) {
String cellValue = cell.getStringCellValue();
if (cellValue.startsWith("file:")) {
cellValue = cellValue.replace("file:///","");
CreationHelper createHelper = context.getWriteSheetHolder().getSheet().getWorkbook().getCreationHelper();
Hyperlink hyperlink = createHelper.createHyperlink(HyperlinkType.URL);
hyperlink.setAddress(cellValue);
cell.setHyperlink(hyperlink);
cell.setCellValue("超长字段");
}
}
}
}
文本的保存方式,这里有一个坑,如果我们用ZipOutputStream的话会有存在java.io.IOException: Stream closed,bufferwriter流使用了outputStreamWriter流,所以关闭bufferwriter流也会关闭outputStreamWriter流,但是我这个是会多次在请求写入的所以会报错。故是设计了继承了ZipOutputStream重写他的关闭方法。
public String saveLongTextToFile(String longText) throws IOException {
String fileNamePage = UUID.randomUUID().toString() + ".txt";
ZipEntry zipEntry = new ZipEntry(fileNamePage);
zos.putNextEntry(zipEntry);
try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(zos))) {
writer.write(longText);
} catch (IOException e) {
zos.closeEntry(); // Ensure entry is closed in case of exception
throw e; // Re-throw the exception after closing the entry
}
//zos.closeEntry(); // Close the current entry
return fileNamePage;
}
public class UncloseableZipOutputStream extends ZipOutputStream {
OutputStream os;
public UncloseableZipOutputStream( OutputStream os ) {
super(os);
}
@Override
/** just flush but do not close */
public void close() throws IOException {
flush();
}
public void reallyClose() throws IOException {
super.close();
}
}
这样的话就不会在写入的过程中关闭了流,只需要在结束了文件写入后再将其关闭即可。
最后就是写入到压缩包里面
private void addByteArrayToZip(String fileName, byte[] fileData, ZipOutputStream zos) throws IOException {
ZipEntry zipEntry = new ZipEntry(fileName);
zos.putNextEntry(zipEntry);
zos.write(fileData);
zos.closeEntry();
}
public void createZipWithExcelAndTxt(ByteArrayOutputStream excelBaos) throws IOException {
addByteArrayToZip("logs.xlsx", excelBaos.toByteArray(), zos);
zos.finish();
}