功能描述
将查询结果生成带分表和水印的excel压缩文件
功能点
1、将查询结果导出为excel文件
2、每个表格存放50万条数据，超过50万条数据，生成新的分表
3、生成的表格需要添加水印
4、将生成的全部分表，打包成zip压缩文件
引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
<exclusions>
<exclusion>
<artifactId>commons-compress</artifactId>
<groupId>org.apache.commons</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
<!-- zip处理工具包 -->
<dependency>
<groupId>net.lingala.zip4j</groupId>
<artifactId>zip4j</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.8.19</version>
<scope>compile</scope>
</dependency>package com.cusc.product.common.util.excel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.cusc.product.common.util.WaterMarkCommonUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.File;
import java.io.IOException;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Objects;
import java.util.Set;
import java.util.UUID;
import java.util.function.Supplier;
/**
* Excel文件生成
*
* @author liubin
*/
@Slf4j
public class EasyExcelWriteCommonUtil {
/**
* 文件行数限制
*/
private static final int EXCEL_LIMIT_NUM = 500000;
/**
* 数据写入Excel文件，多文件 自定义表头
*
* @param suppliers 自定义函数
* @param listFiled 表头
* @param dirName 期待的压缩文件的名称
* @return zip压缩后的文件
*/
public static File createMultiFileExcelZipFiles(List<Supplier<List>> suppliers, List<String> listFiled,
Class clzss, String dirName, String waterMarkUserName, String waterMarkUsePhone) {
File zipFile;
// 创建临时目录
if (Objects.isNull(dirName)) {
dirName = UUID.randomUUID().toString();
}
File localDir = createDir(FileBaseCommonUtil.TEMPORARY_DIRECTORY_PATH + "/" + dirName);
log.info("临时目录: " + localDir.getPath());
String sheet = "Sheet1";
// 文件批次编号
int fileId = 1;
// 单个文件累积写入数据行数
int number = 0;
ExcelWriter excelWriter = null;
WriteSheet writeSheet = null;
//只导出这几列
Set<String> includeColumnFiledNames = new LinkedHashSet<>();
for (int j = 0; j < listFiled.size(); j++) {
includeColumnFiledNames.add(listFiled.get(j));
}
try {
for (int i = 0; i < suppliers.size(); i++) {
List dataList = suppliers.get(i).get();
if (dataList.size() > EXCEL_LIMIT_NUM) {
log.error("Supplier返回数据量最大不可超过{}", EXCEL_LIMIT_NUM);
throw new RuntimeException(String.format("Supplier返回数据量最大不可超过%s", EXCEL_LIMIT_NUM));
}
number += dataList.size();
if (number > EXCEL_LIMIT_NUM) {
// 数据量累积超出了， 需要新建文件记录
int comNum = number - EXCEL_LIMIT_NUM;
List compensate = dataList.subList(dataList.size() - comNum, dataList.size());
List oldDataList = dataList.subList(0, dataList.size() - comNum);
// 旧文件追加数据
File oldFile = new File(localDir, dirName + "_" + fileId + ".xlsx");
if (oldFile.exists()) {
excelWriter.write(oldDataList, writeSheet);
// 添加水印
Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
excelWriter.finish();
}
if (CollectionUtils.isNotEmpty(compensate)) {
// 写入新的文件数据
File file = new File(localDir, dirName + "_" + (++fileId) + ".xlsx");
excelWriter = EasyExcelFactory.write(file.getAbsolutePath()).build();
writeSheet = EasyExcel.writerSheet(i, sheet).head(clzss).includeColumnFieldNames(includeColumnFiledNames).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
// writeSheet = EasyExcelFactory.writerSheet(sheet).build();
excelWriter.write(dataList, writeSheet);
// 添加水印
Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
// 重置记录数
number = compensate.size();
}
if (CollectionUtils.isNotEmpty(oldDataList)) {
oldDataList.clear();
}
if (CollectionUtils.isNotEmpty(compensate)) {
compensate.clear();
}
if (CollectionUtils.isNotEmpty(dataList)) {
dataList.clear();
}
continue;
}
File file = new File(localDir, dirName + "_" + fileId + ".xlsx");
if (file.exists()) {
excelWriter.write(dataList, writeSheet);
// 添加水印
Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
} else {
excelWriter = EasyExcelFactory.write(file.getAbsolutePath()).build();
writeSheet = EasyExcel.writerSheet(i, sheet).head(clzss).includeColumnFieldNames(includeColumnFiledNames).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
excelWriter.write(dataList, writeSheet);
// 添加水印
Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
WaterMarkCommonUtil.insertWaterMarkNamePhoneToXlsx(workbook, waterMarkUserName, waterMarkUsePhone);
}
if (CollectionUtils.isNotEmpty(dataList)) {
dataList.clear();
}
}
} catch (RuntimeException e) {
log.error("数据导出失败", e);
throw new RuntimeException(e);
} catch (IOException e) {
log.error("数据导出失败", e);
throw new RuntimeException(e);
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
String zipPath = Zip4jCommonUtil.zip(localDir.getPath());
deleteFile(localDir);
if (StringUtils.isEmpty(zipPath)) {
log.error("文件压缩失败");
throw new RuntimeException("文件压缩失败");
}
zipFile = new File(zipPath);
log.info("临时目录压缩后路径: " + zipPath);
return zipFile;
}
/**
* 删除文件目录
*
* @param file
* @return
*/
private static Boolean deleteFile(File file) {
// 判断文件不为null或文件目录存在
if (file == null || !file.exists()) {
log.error("文件删除失败,请检查文件是否存在以及文件路径是否正确");
return false;
}
// 获取目录下子文件
File[] files = file.listFiles();
// 遍历该目录下的文件对象
for (File f : files) {
// 判断子目录是否存在子目录,如果是文件则删除
if (f.isDirectory()) {
// 递归删除目录下的文件
deleteFile(f);
} else {
// 文件删除
f.delete();
}
}
// 文件夹删除
file.delete();
return true;
}
/**
* 创建目录
*
* @param localDirPath 目录路径
* @return File对象
*/
private static File createDir(String localDirPath) {
File localDir = new File(localDirPath);
if (!localDir.exists()) {
localDir.mkdirs();
}
return localDir;
}
}
package com.cusc.product.common.util;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.ReflectUtil;
import org.apache.poi.openxml4j.opc.PackagePartName;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
public class WaterMarkCommonUtil {
/**
* Excel 导出添加水印
*
* @param workbook ExcelWorkbook
* @param userName 导出人姓名
*/
public static void insertWaterMarkNamePhoneToXlsx(Workbook workbook, String userName, String phone) throws IOException {
String date = DateUtil.now();
String waterMarkText = userName + "\n" + phone;
if (workbook instanceof SXSSFWorkbook) {
insertWaterMarkTextToXlsx((SXSSFWorkbook) workbook, waterMarkText);
} else if (workbook instanceof XSSFWorkbook) {
insertWaterMarkTextToXlsx((XSSFWorkbook) workbook, waterMarkText);
}
//throw new RemoteException("HSSFWorkbook 模式不支持 Excel 水印");
}
/**
* Excel 导出添加水印
*
* @param workbook ExcelWorkbook
* @param userName 导出人姓名
*/
public static void insertWaterMarkTextToXlsx(Workbook workbook, String userName) throws IOException {
String date = DateUtil.now();
String waterMarkText = userName + "\n" + date;
if (workbook instanceof SXSSFWorkbook) {
insertWaterMarkTextToXlsx((SXSSFWorkbook) workbook, waterMarkText);
} else if (workbook instanceof XSSFWorkbook) {
insertWaterMarkTextToXlsx((XSSFWorkbook) workbook, waterMarkText);
}
//throw new RemoteException("HSSFWorkbook 模式不支持 Excel 水印");
}
/**
* 给 Excel 添加水印
*
* @param workbook SXSSFWorkbook
* @param waterMarkText 水印文字内容
*/
private static void insertWaterMarkTextToXlsx(SXSSFWorkbook workbook, String waterMarkText) throws IOException {
BufferedImage image = createWatermarkImage(waterMarkText);
ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
ImageIO.write(image, "png", imageOs);
int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFPictureData pictureData = (XSSFPictureData) workbook.getAllPictures().get(pictureIdx);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
SXSSFSheet sheet = workbook.getSheetAt(i);
//这里由于 SXSSFSheet 没有 getCTWorksheet() 方法，通过反射取出 _sh 属性
XSSFSheet shReflect = (XSSFSheet) ReflectUtil.getFieldValue(sheet, "_sh");
PackagePartName ppn = pictureData.getPackagePart().getPartName();
String relType = XSSFRelation.IMAGES.getRelation();
PackageRelationship pr = shReflect.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
shReflect.getCTWorksheet().addNewPicture().setId(pr.getId());
}
imageOs.close();
}
/**
* 给 Excel 添加水印
*
* @param workbook XSSFWorkbook
* @param waterMarkText 水印文字内容
*/
private static void insertWaterMarkTextToXlsx(XSSFWorkbook workbook, String waterMarkText) throws IOException {
BufferedImage image = createWatermarkImage(waterMarkText);
ByteArrayOutputStream imageOs = new ByteArrayOutputStream();
ImageIO.write(image, "png", imageOs);
int pictureIdx = workbook.addPicture(imageOs.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFPictureData pictureData = workbook.getAllPictures().get(pictureIdx);
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
XSSFSheet sheet = workbook.getSheetAt(i);
PackagePartName ppn = pictureData.getPackagePart().getPartName();
String relType = XSSFRelation.IMAGES.getRelation();
PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
sheet.getCTWorksheet().addNewPicture().setId(pr.getId());
}
imageOs.close();
}
/**
* 创建水印图片
*
* @param waterMark 水印文字
*/
private static BufferedImage createWatermarkImage(String waterMark) {
String[] textArray = waterMark.split("\n");
Font font = new Font("microsoft-yahei", Font.PLAIN, 24);
// int width = 500;
// int height = 400;
int width = 300;
int height = 200;
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
// 背景透明 开始
Graphics2D g = image.createGraphics();
image = g.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g.dispose();
// 背景透明 结束
g = image.createGraphics();
g.setColor(new Color(237, 235, 233));// 设定画笔颜色
g.setFont(font);// 设置画笔字体
// g.shear(0.1, -0.26);// 设定倾斜度
// 设置字体平滑
g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
//文字从中心开始输入，算出文字宽度，左移动一半的宽度，即居中
FontMetrics fontMetrics = g.getFontMetrics(font);
// 水印位置
int x = width / 2;
int y = height / 2;
// 设置水印旋转
g.rotate(Math.toRadians(-40), x, y);
for (String s : textArray) {
// 文字宽度
int textWidth = fontMetrics.stringWidth(s);
g.drawString(s, x - (textWidth / 2), y);// 画出字符串
y = y + font.getSize();
}
g.dispose();// 释放画笔
return image;
}
}
