easypoi导出excel 2007添加水印—仅支持 XSSFWorkbook
效果图如下：
1、依赖添加
1. <dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.2.0</version>
</dependency> <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>
<!-- poi 添加水印 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>ooxml-schemas</artifactId>
<version>1.4</version>
</dependency>
2、调用controller
@RequestMapping("/exportsessionrecord")
@Menu(type="apps", subtype="export")
public void exportsessionrecord(ModelMap map , String scope, HttpServletRequest request,
QueryCondition queryCondition, HttpServletResponse response) throws Exception{
String isExcel = queryCondition.getIsExcel();
List<AgentServiceDto> dtoList = statisService.queryAgentServiceList(null, queryCondition,
new PageRequest(0, 9999), super.getOrgiByTenantshare(request));
if("1".equals(isExcel)){
ExportParams params = new ExportParams();
params.setType(ExcelType.XSSF); // HSSF2003版本太老了，不兼容水印
Workbook workbook = ExcelExportUtil.exportExcel(params,
AgentServiceDto.class, dtoList);
final User login = super.getUser(request);
final String userName = login.getUsername();
ExcelWaterMarkUtil.excelWaterMark(workbook,userName,queryCondition,repository);
response.setHeader(
"content-disposition",
"attachment;filename="+ URLEncoder.encode("excel表名称.xlsx","UTF-8"));
workbook.write(response.getOutputStream());
}
}
3、添加文字图片水印工具类 ExcelWaterMarkUtil
import java.awt.*;
import java.awt.image.BufferedImage;
import java.awt.image.Raster;
import java.io.*;
import java.lang.reflect.Field;
import java.nio.ByteBuffer;
import java.nio.ByteOrder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import javax.imageio.ImageIO;
import com.chatopera.cc.persistence.repository.TraceTopLevelInfoRepository;
import com.chatopera.cc.service.dto.QueryCondition;
import org.apache.poi.hssf.model.InternalSheet;
import org.apache.poi.hssf.record.RecordBase;
import org.apache.poi.hssf.record.StandardRecord;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ooxml.POIXMLDocumentPart;
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.util.LittleEndianOutput;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class ExcelWaterMarkUtil {
private static ExecutorService executors= Executors.newFixedThreadPool(2);
private final static Logger logger = LoggerFactory.getLogger(ExcelWaterMarkUtil.class);
public static void main(String[] args) {
String filePath = excelWaterMarkForXlsx("C:\\Users\\admin\\Desktop\\1739944118538342400.xlsx","zlf");
String filePath1 = excelWaterMarkForXls("C:\\1234.xls","牛哄哄");
// System.out.println(filePath);
}
public static void excelWaterMark(Workbook workbook, String userName, QueryCondition queryCondition, TraceTopLevelInfoRepository repository){
ByteArrayOutputStream os = null;
try {
//生成水印图片并导出字节流
BufferedImage image = FontImageUtil.createWatermarkImage(new FontImageUtil.Watermark(true,userName, null,null));
os = new ByteArrayOutputStream();
ImageIO.write(image, "png", os);
int pictureIdx = workbook.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);
POIXMLDocumentPart poixmlDocumentPart = (POIXMLDocumentPart) workbook.getAllPictures().get(pictureIdx);
//获取每个Sheet表并插入水印
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
XSSFSheet sheet1 = (XSSFSheet) workbook.getSheetAt(i);
PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();
String relType = XSSFRelation.IMAGES.getRelation();
//add relation from sheet to the picture data
PackageRelationship pr = sheet1.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
//set background picture to sheet
sheet1.getCTWorksheet().addNewPicture().setId(pr.getId());
}
} catch (Exception e) {
logger.error("excel文件添加水印异常",e);
}finally {
if (os != null){
try {
os.close();
}catch (Exception e){
logger.error("水印图片字节流关闭异常",e);
}
}
//异步保留日志
executors.execute(new LogThread(queryCondition, workbook, userName,repository));
}
}
/**
* xlsx 类型添加水印
* @param inputFilePath
* @param text
* @return
*/
public static String excelWaterMarkForXlsx(String inputFilePath,String text) {
FileInputStream is = null;
FileOutputStream out = null;
XSSFWorkbook workbook = null;
ByteArrayOutputStream os = null;
try {
//生成水印图片并导出字节流
BufferedImage image = FontImageUtil.createWatermarkImage(new FontImageUtil.Watermark(true,text, null,null));
os = new ByteArrayOutputStream();
ImageIO.write(image, "png", os);
//获取excel工作簿
is = new FileInputStream(inputFilePath);
workbook = new XSSFWorkbook(is);
int pictureIdx = workbook.addPicture(os.toByteArray(), Workbook.PICTURE_TYPE_PNG);
POIXMLDocumentPart poixmlDocumentPart = workbook.getAllPictures().get(pictureIdx);
//获取每个Sheet表并插入水印
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
XSSFSheet sheet1 = workbook.getSheetAt(i);
PackagePartName ppn = poixmlDocumentPart.getPackagePart().getPartName();
String relType = XSSFRelation.IMAGES.getRelation();
//add relation from sheet to the picture data
PackageRelationship pr = sheet1.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);
//set background picture to sheet
sheet1.getCTWorksheet().addNewPicture().setId(pr.getId());
}
//生成添加水印的excel文件
File f = new File(inputFilePath);
String outputFilePath = f.getParent() + File.separator+System.currentTimeMillis()+ f.getName();
out = new FileOutputStream(outputFilePath);
workbook.write(out);
return outputFilePath;
} catch (Exception e) {
logger.error("excel文件添加水印异常",e);
}finally {
if (is != null){
try {
is.close();
}catch (Exception e){
logger.error("excel输入文件关闭异常",e);
}
}
if (out != null){
try {
out.close();
}catch (Exception e){
logger.error("excel输出文件关闭异常",e);
}
}
if (workbook != null){
try {
workbook.close();
}catch (Exception e){
logger.error("excel工作簿关闭异常",e);
}
}
if (os != null){
try {
os.close();
}catch (Exception e){
logger.error("水印图片字节流关闭异常",e);
}
}
}
return "";
}
/**
* xls 类型添加水印
* @param inputFilePath
* @param text
* @return
*/
public static String excelWaterMarkForXls(String inputFilePath,String text) {
FileInputStream is = null;
FileOutputStream out = null;
HSSFWorkbook workbook = null;
ByteArrayOutputStream os = null;
//水印图片
String tarImgPath = "";
try {
//生成水印图片并导出字节流
BufferedImage image = FontImageUtil.createWatermarkImage(new FontImageUtil.Watermark(true,text, null,null));
File f1 = new File(inputFilePath);
tarImgPath = f1.getParent() + File.separator+System.nanoTime()+text+ ".png";
FileOutputStream outImgStream = new FileOutputStream(tarImgPath);
ImageIO.write(image, "png", outImgStream);
//获取excel工作簿
is = new FileInputStream(inputFilePath);
workbook = new HSSFWorkbook(is);
//获取每个Sheet表并插入水印
// get bytes of the image file
byte[] data = getBackgroundBitmapData(tarImgPath); //PNG must not have transparency
for (int k = 0; k < workbook.getNumberOfSheets(); k++) {
HSSFSheet sheet1 = workbook.getSheetAt(k);
Field _sheet = HSSFSheet.class.getDeclaredField("_sheet");
_sheet.setAccessible(true);
InternalSheet internalsheet = (InternalSheet)_sheet.get(sheet1);
// get List of RecordBase
Field _records = InternalSheet.class.getDeclaredField("_records");
_records.setAccessible(true);
@SuppressWarnings("unchecked")
List<RecordBase> records = (List<RecordBase>)_records.get(internalsheet);
// do creating BitmapRecord and ContinueRecords from the data in parts of 8220 bytes
BitmapRecord bitmapRecord;
List<ContinueRecord> continueRecords = new ArrayList<>();
int bytes;
if (data.length > 8220) {
bitmapRecord = new BitmapRecord(Arrays.copyOfRange(data, 0, 8220));
bytes = 8220;
while (bytes < data.length) {
if ((bytes + 8220) < data.length) {
continueRecords.add(new ContinueRecord(Arrays.copyOfRange(data, bytes, bytes + 8220)));
bytes += 8220;
} else {
continueRecords.add(new ContinueRecord(Arrays.copyOfRange(data, bytes, data.length)));
break;
}
}
} else {
bitmapRecord = new BitmapRecord(data);
}
// add the records after PageSettingsBlock
int i = 0;
for (RecordBase r : records) {
if (r instanceof org.apache.poi.hssf.record.aggregates.PageSettingsBlock) {
break;
}
i++;
}
records.add(++i, bitmapRecord);
for (ContinueRecord continueRecord : continueRecords) {
records.add(++i, continueRecord);
}
// // debug output
// for (RecordBase r : internalsheet.getRecords()) {
// System.out.println(r);
// }
}
//生成添加水印的excel文件
File f = new File(inputFilePath);
String outputFilePath = f.getParent() + File.separator+System.currentTimeMillis()+ f.getName();
out = new FileOutputStream(outputFilePath);
logger.info(outputFilePath);
outImgStream.close();
boolean res = new File(tarImgPath).delete();
workbook.write(out);
return outputFilePath;
} catch (Exception e) {
logger.error("excel文件添加水印异常",e);
}finally {
if (is != null){
try {
is.close();
}catch (Exception e){
logger.error("excel输入文件关闭异常",e);
}
}
if (out != null){
try {
out.close();
}catch (Exception e){
logger.error("excel输出文件关闭异常",e);
}
}
if (workbook != null){
try {
workbook.close();
}catch (Exception e){
logger.error("excel工作簿关闭异常",e);
}
}
if (os != null){
try {
os.close();
}catch (Exception e){
logger.error("水印图片字节流关闭异常",e);
}
}
new File(tarImgPath).delete();
}
return "";
}
static byte[] getBackgroundBitmapData(String filePath) throws Exception {
//see https://www.openoffice.org/sc/excelfileformat.pdf - BITMAP
// get file byte data in type BufferedImage.TYPE_3BYTE_BGR
FileInputStream fio = new FileInputStream(filePath);
BufferedImage in = ImageIO.read(fio);
BufferedImage image = new BufferedImage(in.getWidth(), in.getHeight(), BufferedImage.TYPE_3BYTE_BGR);
Graphics2D graphics = image.createGraphics();
graphics.drawImage(in, null, 0, 0);
graphics.dispose();
// calculate row size (c)
int rowSize = ((24 * image.getWidth() + 31) / 32) * 4;
ByteArrayOutputStream output = new ByteArrayOutputStream(image.getHeight() * rowSize * 3 + 1024);
// put the record headers into the data
ByteBuffer header = ByteBuffer.allocate(8 + 12);
header.order(ByteOrder.LITTLE_ENDIAN);
// Undocumented XLS stuff
header.putShort((short) 0x09);
header.putShort((short) 0x01);
header.putInt(image.getHeight() * rowSize + 12); // Size of image stream
// BITMAPCOREHEADER (a)
header.putInt(12);
header.putShort((short) image.getWidth());
header.putShort((short) image.getHeight()); // Use -height if writing top-down
header.putShort((short) 1); // planes, always 1
header.putShort((short) 24); // bitcount
output.write(header.array());
// Output rows bottom-up (b)
Raster raster = image.getRaster()
.createChild(0, 0, image.getWidth(), image.getHeight(), 0, 0, new int[]{2, 1, 0}); // Reverse BGR -> RGB (d)
byte[] row = new byte[rowSize]; // padded (c)
for (int i = image.getHeight() - 1; i >= 0; i--) {
row = (byte[]) raster.getDataElements(0, i, image.getWidth(), 1, row);
output.write(row);
}
fio.close();
return output.toByteArray();
}
static class BitmapRecord extends StandardRecord {
//see https://www.openoffice.org/sc/excelfileformat.pdf - BITMAP
byte[] data;
BitmapRecord(byte[] data) {
this.data = data;
}
@Override
public int getDataSize() {
return data.length;
}
@Override
public short getSid() {
return (short)0x00E9;
}
@Override
public void serialize(LittleEndianOutput out) {
out.write(data);
}
@Override
public StandardRecord copy() {
return null;
}
}
static class ContinueRecord extends StandardRecord {
//see https://www.openoffice.org/sc/excelfileformat.pdf - CONTINUE
byte[] data;
ContinueRecord(byte[] data) {
this.data = data;
}
@Override
public int getDataSize() {
return data.length;
}
@Override
public short getSid() {
return (short)0x003C;
}
@Override
public void serialize(LittleEndianOutput out) {
out.write(data);
}
public StandardRecord copy() {
return null;
}
}
}
图片水印工具类 FontImageUtil
import java.awt.*;
import java.awt.image.BufferedImage;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.springframework.util.StringUtils;
public class FontImageUtil {
/**
* 水印内容类
*/
public static class Watermark {
private Boolean enable;
private String text;
private String dateFormat;
private String color;
public Watermark(Boolean enable, String text, String dateFormat, String color) {
this.enable = enable;
this.text = text;
this.dateFormat = dateFormat;
this.color = color;
}
public Watermark() {
}
public Boolean getEnable() {
return enable;
}
public void setEnable(Boolean enable) {
this.enable = enable;
}
public String getText() {
return text;
}
public void setText(String text) {
this.text = text;
}
public String getDateFormat() {
return dateFormat;
}
public void setDateFormat(String dateFormat) {
this.dateFormat = dateFormat;
}
public String getColor() {
return color;
}
public void setColor(String color) {
this.color = color;
}
}
/**
* 生成水印图片
* @param watermark
* @return
*/
public static BufferedImage createWatermarkImage(Watermark watermark) {
if (watermark == null) {
watermark = new Watermark();
watermark.setEnable(true);
watermark.setText("内部资料");
watermark.setColor("#000000");
watermark.setDateFormat("yyyy-MM-dd HH:mm");
} else {
if (StringUtils.isEmpty(watermark.getDateFormat())) {
watermark.setDateFormat("yyyy-MM-dd HH:mm");
} else if (watermark.getDateFormat().length() == 16) {
watermark.setDateFormat("yyyy-MM-dd HH:mm");
} else if (watermark.getDateFormat().length() == 10) {
watermark.setDateFormat("yyyy-MM-dd");
}
if (StringUtils.isEmpty(watermark.getText())) {
watermark.setText("内部资料");
}
if (StringUtils.isEmpty(watermark.getColor())) {
watermark.setColor("#C5CBCF");
}
}
String[] textArray = watermark.getText().split("\n");
Font font = new Font("microsoft-yahei", Font.PLAIN, 20);
Integer width = 400;
Integer height = 200;
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
// 背景透明 开始
Graphics2D g = image.createGraphics();
// image = g.getDeviceConfiguration().createCompatibleImage(width, height);
// g.dispose();
// 背景透明 结束
g.setColor(Color.white);
g.fillRect(0, 0, width , height);
g.setColor(new Color(Integer.parseInt(watermark.getColor().substring(1), 16)));// 设定画笔颜色
g.setFont(font);// 设置画笔字体
g.shear(0.1, -0.26);// 设定倾斜度
// 设置字体平滑
g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON);
int y = 50;
for (int i = 0; i < textArray.length; i++) {
g.drawString(textArray[i], 0, y);// 画出字符串
y = y + font.getSize();
}
SimpleDateFormat sdf = new SimpleDateFormat(watermark.getDateFormat());
g.drawString(sdf.format(new Date()), 0, y);// 画出字符串
g.dispose();// 释放画笔
return image;
}
}
609
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
