java利用poi为excel添加图片水印,还没研究文字水印的。。
所需jar包:poi-3.17.jar, poi-ooxml-3.17.jar, ooxml-schemas-1.3.jar,commons-collections4-4.1.jar,xmlbeans-2.6.0.jar,
Spire.Office.jar或Spire.Xls.jar(这两得在这下载 https://www.e-iceblue.cn/)
其他jar包可在这里下载:https://mvnrepository.com/
或者要送我分也行,嘿嘿:excel水印jar包.rar
代码
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import javax.imageio.ImageIO;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelWaterMarkUtil {
/**
* excel文件添加图片水印
* @param srcPath 输入的文件路径
* @param destPath 输出的文件路径
* @param imagePath 水印图片的路径
* @throws Exception
*/
public static void addWaterMark(String srcPath, String destPath, String imagePath)
throws Exception {
//若excel文件扩展名是xls(Excel2003及以前的版本),转换为xlsx(Excel2007及以后的版本)
//HSSFWorkbook用于xls,XSSFWorkbook用于xlsx
if (srcPath.endsWith("xls")) {
//workbook = new HSSFWorkbook(new FileInputStream(srcPath));
//存放转换后的文件的路径
String tempPath = srcPath.substring(0, srcPath.lastIndexOf(File.separator)) + File.separator +
"tempFile" + File.separator + srcPath.substring(srcPath.lastIndexOf(File.separator)+1);
tempPath += "x";//xls改为xlsx
destPath += "x";
//xls转换为xlsx
ExcelUtil.xlsToXlsx(srcPath, tempPath);
srcPath = tempPath;
}
Workbook workbook = new XSSFWorkbook(new FileInputStream(srcPath));
//加载水印图片
BufferedImage image = ImageIO.read(new FileInputStream(imagePath));
//导出到字节流
ByteArrayOutputStream 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);
int sheetsNum = workbook.getNumberOfSheets();
for (int i=0; i<sheetsNum; i++) {
XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(i);
if (sheet == null) {
continue;
}
//poi-3.17
String rID = sheet.addRelation(null, XSSFRelation.IMAGES, poixmlDocumentPart)
.getRelationship().getId();
//poi-3.9
/*String rID = sheet.getPackagePart().addRelationship(
poixmlDocumentPart.getPackagePart().getPartName(), TargetMode.INTERNAL,
XSSFRelation.IMAGES.getRelation(), null).getId();*/
sheet.getCTWorksheet().addNewPicture().setId(rID);
}
workbook.write(new FileOutputStream(destPath));
//poi-3.17
if (workbook != null)
workbook.close();
if (os != null)
os.close();
}
public static void main(String[] args) throws Exception {
addWaterMark("E:\\测试.xls", "E:\\测试水印.xls", "E:\\waterMark.png");
addWaterMark("E:\\测试.xlsx", "E:\\测试水印2.xlsx", "E:\\waterMark.png");
}
}
xls转换为xlsx得工具类
import com.spire.xls.FileFormat;
import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;
public class ExcelUtil {
/**
* 将xls的表复制到新建的xlsx文档,实现xls转换为xlsx
* @param srcPath
* @param destPath
* @throws Exception
*/
public static void xlsToXlsx (String srcPath, String destPath) throws Exception {
//新建空文档,用于复制
Workbook destWorkbook = new Workbook();
//加载原文档
Workbook srcWorkbook = new Workbook();
srcWorkbook.loadFromFile(srcPath);
//原文档表数
int count = srcWorkbook.getWorksheets().size();
for (int i=0; i<count; i++) {
//获取原文档第 i 张表
Worksheet srcSheet = srcWorkbook.getWorksheets().get(i);
//获取空文档第 i 张表,若没有则自动添加,
Worksheet destSheet = destWorkbook.getWorksheets().get(i);
//表名与原文档的表名保持一致
destSheet.setName(srcSheet.getName());
/*if (i > 0) {
//添加新的工作表
destSheet = destWorkbook.getWorksheets().add(srcSheet.getName());
}*/
//复制
destSheet.copyFrom(srcSheet);
}
//保存文档
destWorkbook.saveToFile(destPath, FileFormat.Version2010);
}
public static void main(String[] args) throws Exception {
xlsToXlsx("E:\\测试.xls", "E:\\测试.xlsx");
}
}