EasyExcell导出excel添加水印

1、添加easyExcel相关依赖

		<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.2</version></dependency><dependency><groupId>org.apache.poi</groupId><version>5.2.2</version><artifactId>poi-ooxml-lite</artifactId></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.2.1</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.22</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.12</version></dependency>

2、准备基础工具类

DemoData .class

@Getter
@Setter
@EqualsAndHashCode
public class DemoData {@ExcelProperty("字符串标题")private String string;@ExcelProperty("日期标题")private Date date;@ExcelProperty("数字标题")private Double doubleData;/*** 忽略这个字段*/@ExcelIgnoreprivate String ignore;
}

TestFileUtil.class

public class TestFileUtil {public static InputStream getResourcesFileInputStream(String fileName) {return Thread.currentThread().getContextClassLoader().getResourceAsStream("" + fileName);}public static String getPath() {return TestFileUtil.class.getResource("/").getPath();}public static File createNewFile(String pathName) {File file = new File(getPath() + pathName);if (file.exists()) {file.delete();} else {if (!file.getParentFile().exists()) {file.getParentFile().mkdirs();}}return file;}public static File readFile(String pathName) {return new File(getPath() + pathName);}public static File readUserHomeFile(String pathName) {return new File(System.getProperty("user.home") + File.separator + pathName);}
}

3、创建水印handler类

WaterMarkHandler.class水印生成类
EasyExcel提供了一个水印接口类，我们实现SheetWriteHandler自定义我们的水印

@RequiredArgsConstructor
public class WaterMarkHandler implements SheetWriteHandler {private final String WATER_MARK;public static ByteArrayOutputStream createWaterMark(String content) throws IOException {int width = 200;int height = 150;BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象String fontType = "微软雅黑";int fontStyle = Font.BOLD;int fontSize = 20;Font font = new Font(fontType, fontStyle, fontSize);Graphics2D g2d = image.createGraphics(); // 获取Graphics2d对象image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);g2d.dispose();g2d = image.createGraphics();g2d.setColor(new Color(0, 0, 0, 20)); //设置字体颜色和透明度，最后一个参数为透明度g2d.setStroke(new BasicStroke(1)); // 设置字体g2d.setFont(font); // 设置字体类型 加粗 大小g2d.rotate(-0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);//设置倾斜度FontRenderContext context = g2d.getFontRenderContext();Rectangle2D bounds = font.getStringBounds(content, context);double x = (width - bounds.getWidth()) / 2;double y = (height - bounds.getHeight()) / 2;double ascent = -bounds.getY();double baseY = y + ascent;// 写入水印文字原定高度过小，所以累计写水印，增加高度g2d.drawString(content, (int) x, (int) baseY);// 设置透明度g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));// 释放对象g2d.dispose();ByteArrayOutputStream os = new ByteArrayOutputStream();ImageIO.write(image, "png", os);return os;}/**为Excel打上水印工具函数@param sheet excel sheet@param bytes 水印图片字节数组*/public static void putWaterRemarkToExcel(SXSSFSheet sheet, byte[] bytes) {//add relation from sheet to the picture dataSXSSFWorkbook workbook = sheet.getWorkbook();XSSFSheet shReflect = (XSSFSheet) ReflectUtil.getFieldValue(sheet, "_sh");int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);XSSFPictureData xssfPictureData = (XSSFPictureData) workbook.getAllPictures().get(pictureIdx);PackagePartName ppn = xssfPictureData.getPackagePart().getPartName();PackageRelationship pr = shReflect.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, XSSFRelation.IMAGES.getRelation(), null);//set background picture to sheetshReflect.getCTWorksheet().addNewPicture().setId(pr.getId());}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@SneakyThrows@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {try (ByteArrayOutputStream waterMark = createWaterMark(WATER_MARK)){SXSSFSheet sheet = (SXSSFSheet) writeSheetHolder.getSheet();putWaterRemarkToExcel(sheet, waterMark.toByteArray());}}
}

4、创建单元测试类WriteTest.class

public class WriteTest {@Testpublic void writer() {String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭// 如果这里想使用03 则 传入excelType参数即可EasyExcel.write(fileName, DemoData.class).registerWriteHandler(new WaterMarkHandler("zhangsan")).sheet("模板").doWrite(() -> {// 分页查询数据return data();});}private List<DemoData> data() {List<DemoData> list = ListUtils.newArrayList();for (int i = 0; i < 10; i++) {DemoData data = new DemoData();data.setString("字符串" + i);data.setDate(new Date());data.setDoubleData(0.56);list.add(data);}return list;}
}

5、测试结果

导出的excel成功添加水印