引用Maven依赖
<!-- POI相关依赖 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
<!--hutool 工具类 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.5.11</version>
</dependency>
添加水印工具类
import cn.hutool.core.date.DatePattern;
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.*;
import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Date;
/**
* Excel 添加水印。支持 SXSSFWorkbook 和 XSSFWorkbook 模式
*
* @author Pengfei Jia
* @since 2022-09-20
*/
public class WaterMarkUtil {
/**
* Excel 导出添加水印
*
* @param workbook ExcelWorkbook
*/
public static void insertWaterMarkTextToXlsx(Workbook workbook) throws IOException {
//水印文字
String sysName = "测试水印";
//操作人
String userName = "Anhk丶";
String date = DateUtil.format(new Date(), DatePattern.NORM_DATE_PATTERN);
String waterMarkText = sysName + "\n" + userName + " " + 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 水印文字内容
*/
public 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());
}
}
/**
* 给 Excel 添加水印
*
* @param workbook XSSFWorkbook
* @param waterMarkText 水印文字内容
*/
public 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());
}
}
/**
* 创建水印图片
*
* @param waterMark 水印文字
*/
public static BufferedImage createWatermarkImage(String waterMark) {
String[] textArray = waterMark.split("\n");
Font font = new Font("microsoft-yahei", Font.PLAIN, 32);
int width = 500;
int height = 400;
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(Color.lightGray.getRGB()));// 设定画笔颜色
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;
}
/**
* 设置打印的参数
*
* @param wb XSSFWorkbook
*/
public static void setPrintParams(XSSFWorkbook wb) {
XSSFSheet sheet = wb.getSheetAt(0);
XSSFPrintSetup printSetup = sheet.getPrintSetup();
// 打印方向，true：横向，false：纵向(默认
printSetup.setLandscape(true);
//设置A4纸
printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
// 将整个工作表打印在一页（缩放）,如果行数很多的话，可能会出问题
// sheet.setAutobreaks(true);
//将所有的列调整为一页，行数多的话，自动分页
printSetup.setScale((short) 70);//缩放的百分比，自行调整
sheet.setAutobreaks(false);
}
}
使用工具类Demo
@RequestMapping("/waterTest")
public void getWorkBook(HttpServletRequest request, HttpServletResponse response) throws Exception {
Workbook workbook = new XSSFWorkbook(new FileInputStream("C:\\Users\\xxx\\Desktop\\新建 Microsoft Excel 工作表.xlsx"));
//添加水印
WaterMarkUtil.insertWaterMarkTextToXlsx(workbook);
//创建字节输出流
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
//写流
workbook.write(byteArrayOutputStream);
//下载
DownLoadUtil.download(byteArrayOutputStream, "水印测试.xlsx", request, response);
}
下载工具类
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
public class DownLoadUtil {
/**
* 下载文件
*
* @param byteArrayOutputStream 文件的字节输出流
* @param returnName 文件名
@param request 请求
* @param response 响应
* @throws IOException
*/
public static void download(ByteArrayOutputStream byteArrayOutputStream, String returnName, HttpServletRequest request, HttpServletResponse response) throws IOException {
response.setContentType("application/octet-stream;charset=utf-8");
//解决IE下导出中文乱码问题
request.setCharacterEncoding("UTF-8");
String header = request.getHeader("User-Agent").toUpperCase();
if (header.contains("MSIE") || header.contains("TRIDENT") || header.contains("EDGE")) {
returnName = URLEncoder.encode(returnName, "utf-8");
returnName = returnName.replace("+", "%20"); //IE下载文件名空格变+号问题
} else {
returnName = new String(returnName.getBytes(), "ISO8859-1");
}
//保存的文件名必须和页面编码一致，否则乱码
//returnName = response.encodeURL(new String(returnName.getBytes(), "iso8859-1"));
response.addHeader("Content-Disposition", "attachment;filename=\"" + returnName + "\"");
response.setContentLength(byteArrayOutputStream.size());
//获取输出流
ServletOutputStream outputStream = response.getOutputStream();
//写进输出流
byteArrayOutputStream.writeTo(outputStream);
//刷新数据
byteArrayOutputStream.close();
outputStream.flush();
}
}
