1、HSSFWorkbook等添加水印工具类
ExcelWaterRemarkUtils.java
package com.qingliang.util;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.IOException;
public class ExcelWaterRemarkUtils {
/*
* 为Excel打上水印工具函数 请自行确保参数值,以保证水印图片之间不会覆盖。 在计算水印的位置的时候,并没有考虑到单元格合并的情况,请注意
*
* @param wb
* Excel Workbook
* @param sheet
* 需要打水印的Excel
* @param waterRemarkPath
* 水印地址,classPath,目前只支持png格式的图片,
* 因为非png格式的图片打到Excel上后可能会有图片变红的问题,且不容易做出透明效果。
* 同时请注意传入的地址格式,应该为类似:"\\excelTemplate\\test.png"
* @param startXCol
* 水印起始列
* @param startYRow
* 水印起始行
* @param betweenXCol
* 水印横向之间间隔多少列
* @param betweenYRow
* 水印纵向之间间隔多少行
* @param XCount
* 横向共有水印多少个
* @param YCount
* 纵向共有水印多少个
* @param waterRemarkWidth
* 水印图片宽度为多少列
* @param waterRemarkHeight
* 水印图片高度为多少行
* @throws IOException
*/
public static void putWaterRemarkToExcel(Workbook wb, Sheet sheet, String waterRemarkPath, int startXCol,
int startYRow, int betweenXCol, int betweenYRow, Integer XCount, Integer YCount, int waterRemarkWidth,
int waterRemarkHeight) throws IOException {
if (XCount == null || XCount == 0) {
int cell221 = sheet.getRow(sheet.getFirstRowNum()).getLastCellNum() + 1;
XCount = cell221 / 4 + 1;
}
if (YCount == null || YCount == 0) {
int rowNum1 = sheet.getFirstRowNum() + sheet.getLastRowNum();
YCount = rowNum1 / 15 + 1;
}
// 加载图片
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
String waterMaker = getCurrentUserName() + "\n";
if (request != null) {
waterMaker += (request.getRemoteAddr() + "\n");
}
waterMaker += DateUtil.formatDateTime(new Date());
BufferedImage bufferImg = createWatermarkImage(waterMaker);
ImageIO.write(bufferImg, "png", byteArrayOut);
// 开始打水印
Drawing drawing = sheet.createDrawingPatriarch();
Drawing<?> drawingPatriarch = sheet.getDrawingPatriarch();
// 按照共需打印多少行水印进行循环
for (int yCount = 0; yCount < YCount; yCount++) {
// 按照每行需要打印多少个水印进行循环
for (int xCount = 0; xCount < XCount; xCount++) {
// 创建水印图片位置
int xIndexInteger = startXCol + (xCount * waterRemarkWidth) + (xCount * betweenXCol);
int yIndexInteger = startYRow + (yCount * waterRemarkHeight) + (yCount * betweenYRow);
/*
* 参数定义: 第一个参数是(x轴的开始节点); 第二个参数是(是y轴的开始节点); 第三个参数是(是x轴的结束节点);
* 第四个参数是(是y轴的结束节点); 第五个参数是(是从Excel的第几列开始插入图片,从0开始计数);
* 第六个参数是(是从excel的第几行开始插入图片,从0开始计数); 第七个参数是(图片宽度,共多少列);
* 第8个参数是(图片高度,共多少行);
*/
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, xIndexInteger,
yIndexInteger, xIndexInteger + waterRemarkWidth, yIndexInteger + waterRemarkHeight);
Picture pic = drawing.createPicture(anchor,
wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
pic.resize();
}
}
lockedSheets(wb);
}
/**
* 创建水印图片 excel
*
* @param waterMark 水印内容
*/
public static BufferedImage createWatermarkImage(String waterMark)
{
String[] textArray = waterMark.split("\n");
java.awt.Font font = new java.awt.Font("微软雅黑", java.awt.Font.BOLD, 20);
Integer width = 500;
Integer height = 500;
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(0, 0, 0, 20)); // 设定画笔颜色
g.setFont(font); // 设置画笔字体
// g.shear(0.1, 0.26); // 设定倾斜度
g.rotate(0.5, (double) image.getWidth() / 2, (double) image.getHeight() / 2);//设置倾斜度
// 设置字体平滑
g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
int y = 150;
for (int i = 0; i < textArray.length; i++)
{
g.drawString(textArray[i], 0, y); // 画出字符串
y = y + font.getSize();
}
g.dispose(); // 释放画笔
return image;
}
public static void lockedSheets(Workbook wb) {
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
wb.getSheetAt(i).protectSheet(getPassword());
}
}
public static String getPassword() {
return "****";
}
}
ExcelSerive.java
package com.qingliang.util;
import com.gccloud.idc.security.shiro.UserUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
/**
* @Author
* @since
*/
@Service
@Slf4j
public class ExcelService {
/* XSSFWorkbook加密方式 */
public String encryptXSSFWorkbookAndWriteOut(String functionModelName, String reportName, String fileName, XSSFWorkbook workbook, HttpServletResponse response) {
String reportPath = functionModelName + "->" + reportName;
OutputStream outputStream = null;
OutputStream encryptOutPutStream = null;
try {
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(fileName + ".xls","UTF-8"));
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
outputStream = response.getOutputStream();
//加密
EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
Encryptor enc = info.getEncryptor();
// enc.confirmPassword("123");
POIFSFileSystem poifsFileSystem = new POIFSFileSystem();
encryptOutPutStream = enc.getDataStream(poifsFileSystem);
workbook.write(encryptOutPutStream);
poifsFileSystem.writeFilesystem(outputStream);
outputStream.flush();
} catch (Exception e) {
log.error("Excel导出输出流输出异常:{}", e.getStackTrace());
} finally {
try {
if (outputStream != null) {
outputStream.close();
}
if (encryptOutPutStream != null) {
encryptOutPutStream.close();
}
} catch (Exception e) {
log.error("Excel导出输出流关闭异常:{}", e.getStackTrace());
}
}
return "导出成功";
}
/* HSSFWorkbook加密方式 */
public String setPasswordBeforeCreateHSSFWorkbook() {
String password = "";
// Biff8EncryptionKey.setCurrentUserPassword(authCode) ; //打开workbook前调用
return password;
}
}
2、EasyExcel
EasyExcel.write(response.getOutputStream())
.registerWriteHandler(new WaterMarkHandler(""))
.password("****")
// ...
WaterMarkHandler.java
package com.qingliang.handler;
import cn.hutool.core.util.ReflectUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.gccloud.handle.service.RiskRect.impl.DateUtils;
import com.gccloud.idc.security.shiro.UserUtils;
import lombok.RequiredArgsConstructor;
import lombok.SneakyThrows;
import org.apache.commons.lang3.StringUtils;
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.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import java.awt.*;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Date;
/**
* @Author
* @since
*/
@RequiredArgsConstructor
public class WaterMarkHandler implements SheetWriteHandler {
private final String WATER_MARK;
public static ByteArrayOutputStream createWaterMark(String content) throws IOException {
int width = 500;
int height = 500;
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象
String fontType = "微软雅黑";
int fontStyle = Font.BOLD;
int fontSize = 30;
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.split("\n")[2], context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double baseY = y + ascent;
// 写入水印文字原定高度过小,所以累计写水印,增加高度
String[] textArray = content.split("\n");
for (int i = 0; i < textArray.length; i++)
{
g2d.drawString(textArray[i], (int) x, (int) baseY);
baseY = baseY + font.getSize();
}
// 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 data
SXSSFWorkbook 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 sheet
// .setId(pr.getId())
shReflect.getCTWorksheet().addNewPicture().setId(pr.getId());
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@SneakyThrows
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
String waterMark2 = UserUtils.getCurrentUserName() + "\n" + request.getRemoteAddr() + "\n" + DateUtils.formatDateTime(new Date());
try (ByteArrayOutputStream waterMark = createWaterMark(StringUtils.isNotEmpty(WATER_MARK) ? WATER_MARK : waterMark2)){
SXSSFSheet sheet = (SXSSFSheet) writeSheetHolder.getSheet();
// 锁定表格
// sheet.protectSheet(UserUtils.getCurrentUserName());
// sheet.lockSelectLockedCells(true);
// 加水印
// putWaterRemarkToExcel(sheet, waterMark.toByteArray());
}
}
}