我导入一个excel文件，有52万+的数据，先打水印，再写上数据，导出新的excel，结果数据有水印不存在，但是不导数据，只打水印，水印又有了
ublic 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;
}
}
public class ExcelWaterMarkUtil {
private final static Logger logger = LoggerFactory.getLogger(ExcelWaterMarkUtil.class);
/**
* 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);
}
@Override
public StandardRecord copy() {
return null;
}
}
}
public class Test {
public static void main(String[] args) {
Test.writeFile();
}
public static File markPic(){
File flies = Test.createExcel("F:\\");
String filePath = ExcelWaterMarkUtil.excelWaterMarkForXlsx(flies.getPath(), "zlf");
flies.delete();
return new File(filePath);
}
public static File createExcel(String path) {
SXSSFWorkbook wb = new SXSSFWorkbook();
SXSSFSheet sheet = wb.createSheet("sheet1");
String filePath = "F:\\" + System.currentTimeMillis() + ".xlsx";
try {
FileOutputStream fileOutputStream = new FileOutputStream(filePath);
wb.write(fileOutputStream);
wb.close();
fileOutputStream.close();
System.out.println(filePath);
} catch (Exception e) {
throw new RuntimeException(e);
}
return new File(filePath);
}
public static void writeFile() {
List<DemoData> list = parseData();
File flies = Test.markPic();
EasyExcel.write(flies)
.head(DemoData.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("sheet1")
.doWrite(list);
}
public static List<DemoData> parseData() {
List<DemoData> list = new LinkedList<>();
EasyExcel.read("F:\\1739944118538342400.xlsx")
.head(DemoData.class)
.sheet()
.registerReadListener(new AnalysisEventListener<DemoData>() {
@Override
public void invoke(DemoData demoData, AnalysisContext analysisContext) {
list.add(demoData);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
System.out.println("所有数据读取完毕！");
}
}).doRead();
return list;
}
}
