JAVA POI导出Excel文件并添加水印

本文将介绍导出的excel文件增加水印的两种方式

本文参考https://www.cnblogs.com/liuyu666/p/13282864.html；https://gitee.com/guo492273770/POIexcel/blob/master/src/ExcelWaterRemarkUtils.java

1 准备好水印模板文件，然后将数据输出到模板文件中实现导出

优点：实现逻辑简单

缺点：水印内容固定

水印文件模板的制作可以搜索下，我找的是在excel中插入艺术字形成的

上代码，

import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.net.framework.util.StringUtil; //自定义的工具

/**
 * 导出excel文件增加水印
 * 
 * @author Arien
 *
 */
public class TT {
	/**
	 * 读取模板文件,当前例子文件放在WEB-INF/classes/下
	 * 
	 * @return
	 * @throws Exception
	 */
	public static FileInputStream createWorkbook() throws Exception {
		TT downLoadUtil = new TT();
		String filePath = downLoadUtil.getClass().getClassLoader()
				.getResource("water.xlsx").getPath();

		File finalXlsxFile = new File(filePath);
		FileInputStream inputStream = new FileInputStream(finalXlsxFile);
		return inputStream;
	}

	/**
	 * @param list
	 *            导出数据内容
	 * @param fileTitile
	 *            导出文件名
	 * @param custCols
	 *            导出标题列
	 * @param str
	 *            导出信息key
	 * @param response
	 */
	public static void export(List<Map<String, Object>> list,
			String fileTitile, String[] custCols, String[] str,
			HttpServletResponse response) {
		OutputStream os = null;
		XSSFWorkbook wbook = null;
		try {
			wbook = new XSSFWorkbook(createWorkbook());
			String title = "";

			for (int i = 0; i < custCols.length; ++i) {
				title = title + custCols[i] + ",";
			}

			title = title.substring(0, title.length() - 1);
			title = new String(title.getBytes("gb2312"), "gbk");

			String[] titles = title.split(",");
			String fileName = new String(fileTitile.getBytes("gb2312"),
					"iso-8859-1");
			os = response.getOutputStream();
			response.reset();
			response.setHeader("Content-disposition", "attachment; filename="
					+ fileName + ".xls");
			response.setContentType("application/msexcel;charset=utf-8");
			response.setCharacterEncoding("utf-8");

			XSSFSheet wsheet = wbook.getSheetAt(0);
			// 标题列
			XSSFRow row0 = wsheet.createRow(0);
			for (int i = 0; i < titles.length; ++i) {
				wsheet.setDefaultColumnWidth(23);
				XSSFCell cell = row0.createCell(i);
				cell.setCellValue(titles[i]);
			}

			if ((list != null) && (!list.isEmpty())) {
				for (int j = 0; j < list.size(); ++j) {
					Map<String, Object> obj = list.get(j);
					XSSFRow row = wsheet.createRow(j + 1);
					for (int i = 0; i < str.length; ++i) {
						XSSFCell cell = row.createCell(i);
						String name = str[i];
						String attr = String.valueOf(obj.get(name));
						if (("null".equals(attr)) || (StringUtil.isEmpty(attr)))
							attr = "";
						cell.setCellValue(attr);
					}

				}

			}

			wbook.write(os);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (os != null)
				try {
					os.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
		}
	}
}

2 动态生成水印图片加到导出的excel文件中

优点：可以根据不同需求自定义水印内容

缺点：相对第一种代码复杂

用到的jar包

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.9</version>
        </dependency>

import java.awt.AlphaComposite;
import java.awt.BasicStroke;
import java.awt.Color;
import java.awt.Font;
import java.awt.Graphics2D;
import java.awt.Transparency;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.imageio.ImageIO;
/**
 * 形成水印图片
 * 
 * @author Arien
 *
 */
public class ImageUtil {
	 public static void main(String[] args) throws IOException {
		 //createWaterMark("内部资料","C:\\水印.png");
		 createWaterMark(new String[]{"内部资料",new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())});
	 }
	 
	 /**
		 * 
		 * @param content  水印内容
		 * @throws IOException
		 * createby lyt
		 */
		public static BufferedImage createWaterMark(String[] content ) throws IOException{
			Integer width = 320;
			Integer height = 230;
			BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象
			String fontType = "宋体";
			Integer fontStyle = Font.PLAIN;
			Integer fontSize = 28;
			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, 50)); //设置字体颜色和透明度
			g2d.setStroke(new BasicStroke(1)); // 设置字体
			g2d.setFont(font); // 设置字体类型  加粗 大小
			g2d.rotate(Math.toRadians(-10),(double) image.getWidth() / 2, (double) image.getHeight() / 2);//设置旋转角度
			
			FontRenderContext context = g2d.getFontRenderContext();
			//找到水印信息中最长的
			int contentLindex = 0;
			int contentLength = content[0].length();
			
			for (int i = 0; i < content.length; i++) {
				if (content[i].length()>contentLength) {
					contentLindex = i;
				}
			}
			Rectangle2D bounds = font.getStringBounds(content[contentLindex], context);
			double x = (width - bounds.getWidth()) / 2;
			double y = (height - bounds.getHeight()) / 2;
			double ascent = -bounds.getY();
			double baseY = y + ascent;
			// 写入水印文字原定高度过小，所以累计写水印，增加高度
			for (int i = 0; i < content.length; i++) {
		            g2d.drawString(content[i], (int)x, (int)baseY);// 画出字符串
		            baseY = baseY + font.getSize();
			}
			 
	        
			// 设置透明度
			g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
			// 释放对象
			g2d.dispose();
			return image;
			//ImageIO.write(image, "png", new File(path));
		}
		  
		 
}

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import javax.imageio.ImageIO;

import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
/**
 * 
 * @author Arien
 */

public class WaterMarkUtil {

	/**
	 * 为Excel打上水印工具函数 请自行确保参数值，以保证水印图片之间不会覆盖。
	 * @param wb Excel Workbook
	 * @param sheet 需要打水印的Excel
	 * @param image  水印图片
	 * @param startXCol 水印起始列
	 * @param startYRow 水印起始行
	 * @param betweenXCol 水印横向之间间隔多少列
	 * @param betweenYRow 水印纵向之间间隔多少行
	 * @param XCount 横向共有水印多少个
	 * @param YCount 纵向共有水印多少个
	 * @param waterMarkWidth 水印图片宽度为多少列
	 * @param waterMarkHeight 水印图片高度为多少行
	 * @throws IOException
	 */
	public static void putWaterMarkToExcel(Workbook wb, Sheet sheet, BufferedImage image, int startXCol,
			int startYRow, int betweenXCol, int betweenYRow, int XCount, int YCount, int waterMarkWidth,
			int waterMarkHeight) throws IOException {

		 
		// 加载图片
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
		 
		if (null == image) {
			throw new RuntimeException("向Excel上面打印水印，读取水印图片失败(2)。");
		}
		ImageIO.write(image, "png", byteArrayOut);

		// 开始打水印
		Drawing drawing = sheet.createDrawingPatriarch();
		
		// 按照共需打印多少行水印进行循环
		for (int yCount = 0; yCount < YCount; yCount++) {
			// 按照每行需要打印多少个水印进行循环
			for (int xCount = 0; xCount < XCount; xCount++) {
				// 创建水印图片位置
				int xIndexInteger = startXCol + (xCount * waterMarkWidth) + (xCount * betweenXCol);
				int yIndexInteger = startYRow + (yCount * waterMarkHeight) + (yCount * betweenYRow);
				/*
				 * 参数定义： 第一个参数是（x轴的开始节点）； 第二个参数是（是y轴的开始节点）； 第三个参数是（是x轴的结束节点）；
				 * 第四个参数是（是y轴的结束节点）； 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
				 * 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）； 第七个参数是（图片宽度，共多少列）；
				 * 第8个参数是（图片高度，共多少行）；
				 */
				ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, xIndexInteger,
						yIndexInteger, xIndexInteger+waterMarkWidth, yIndexInteger+waterMarkHeight);
				
				Picture pic = drawing.createPicture(anchor,
						wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
				pic.resize();
			}
		}
	}
	
	

}

import java.awt.Font;
import java.awt.image.BufferedImage;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import net.sf.json.JSONObject;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
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.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.ServletActionContext;


import com.net.framework.util.StringUtil;//自定义工具类


/**
 * 
* @作者 Arien 
 */
public class TT{
	
	/**
	 * @param list
	 *            导出数据内容
	 * @param fileTitile
	 *            导出文件名
	 * @param custCols
	 *            导出标题列
	 * @param str
	 *            导出信息key
	 * @param response
	 */
	public static void export(List<Map<String, Object>> list,String fileTitile,String [] custCols,String [] str,HttpServletResponse response) {
 
		  OutputStream os =null;
		  
		  HSSFWorkbook wbook = null;
 		  HSSFSheet sheet = null;
 		
		  try {
			wbook = new HSSFWorkbook(); 
		    String title = "";
		    
		    for(int i = 0; i < custCols.length; i++ ){
		        title += custCols[i] + ",";
		    }
		      
		    title = title.substring(0, title.length() -1);      
		    title = new String(title.getBytes("gb2312"), "gbk");
		    
		    String titles[] = title.split(",");
		    String fileName = new String(fileTitile.getBytes("gb2312"), "iso-8859-1"); // 乱码解决
		    os = response.getOutputStream();// 取得输出流
		    response.reset();// 清空输出流
		    response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xls");// 设定输出文件头
		    response.setContentType("application/msexcel;charset=utf-8");// 定义输出类型
		    response.setCharacterEncoding("utf-8");
		    //response.setContentType("application/vnd.ms-excel;charset=GB18030");// 定义输出类型
		
		     
		    sheet = wbook.createSheet(fileTitile);// sheet名称
		    //为文件添加密码，设置文件只读
 			sheet.protectSheet(UUID.randomUUID().toString());
		   
		    // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
			HSSFRow row0 = sheet.createRow(0);
			//设置一个标题样式
			HSSFCellStyle titleStyle = wbook.createCellStyle();
			
			HSSFFont titleFont = wbook.createFont();
			titleFont.setFontHeightInPoints((short)12); //字体大小
			titleFont.setFontName("Arial"); //什么字体
			titleFont.setItalic(false); //是不倾斜
			titleFont.setStrikeout(false); //是不是划掉
			titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //字体加粗 
			titleFont.setColor((short) 255);
			titleStyle.setFont(titleFont);
			titleStyle.setFillForegroundColor(HSSFColor.RED.index);//添加前景色,内容看的清楚 
			//生成标题列
		    for (int i = 0; i < titles.length; i++) {
		    	// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
		    	HSSFCell titleCell = row0.createCell(i);
		    	// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
				// 设置单元格内容
		    	titleCell.setCellValue(titles[i]);
		    	titleCell.setCellStyle(titleStyle);
				sheet.setDefaultColumnWidth(23);
		    }
		    
		    //生成内容
		    if(list != null && !list.isEmpty()){
			    for(int j = 0;j < list.size();j++) {
			    	 Map<String, Object> obj = list.get(j);
			    	 HSSFRow row = sheet.createRow(j+1);
			        for(int i = 0;i<str.length;i++) {
			        	HSSFCell cell = row.createCell(i);
					    String name = str[i];
				        String attr =obj.get(name);  
				        if("null".equals(attr) || StringUtil.isEmpty(attr))
				        	attr ="";
					    cell.setCellValue(attr);
			        }
			   }
		    }
		    //主体内容生成结束
		    //添加水印开始  计算水印位置 个数，可以自行更改
		      System.out.println("jinru =================================");
		      String[] waterMarkInfo = new String[] {"内部资料", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())};
			  BufferedImage image = ImageUtil.createWaterMark(waterMarkInfo);
			  int startXCol = 0;//水印起始行
			  int betweenXCol = 0; //水印横向之间间隔多少列
			  int betweenYRow = 0; //水印纵向之间间隔多少行
			  int XCount = 0; //横向共有水印多少个
			  int YCount = 0; //纵向共有水印多少个
			  int waterRemarkWidth = 0; //水印图片宽度为多少列
			  int waterRemarkHeight = 0;// 水印图片高度为多少行
			  
			  
			  
			  
			  int Ymultiple = 5;    //纵向间隔
			  int Xmultiple = 2;    //横向间隔
			  int totalData = 0;   
			  int totalColumn = 0;
			  if(list != null && !list.isEmpty()){
				  totalData = list.size();
				  totalColumn = custCols.length;
				  startXCol = Ymultiple;
				  betweenXCol = Xmultiple;
				  betweenYRow = Ymultiple;
				  XCount = totalColumn/betweenYRow;
				  YCount = totalData/betweenYRow + 1;
				  waterRemarkWidth = Xmultiple;
				  waterRemarkHeight = Ymultiple;  
			  }
			  
			  //WaterMarkUtil.putWaterMarkToExcel(wbook, sheet, image, 0, 0, 5, 5, custCols.length / 10 + 1, list.size() / 10 + 1, 0, 0);
			  WaterMarkUtil.putWaterMarkToExcel(wbook, sheet, image, 0, 0, 10, 10, custCols.length / 5 + 1, list.size() / 10 + 1, 0, 0);

			 //WaterMarkUtil.putWaterMarkToExcel(wbook, sheet, image, 1, 1, betweenXCol, betweenYRow, XCount, YCount, waterRemarkWidth, waterRemarkHeight);
			 //WaterRemarkUtil.putWaterRemarkToExcel(wbook, sheet, image, 0, 5, 6, 12, 5, 20, 0, 0);
		    //添加水印结束
			//ByteArrayOutputStream os = new ByteArrayOutputStream();
		    wbook.write(os); // 写入文件
		  } catch (Exception ex) {
		    ex.printStackTrace();
		  }finally {
 
		    
		    if(os != null) {
		      try {
				os.close();  // 关闭流
		      }catch (Exception e) {
		        e.printStackTrace();
		      }
		    }
		  }
		
	}
}

 

欢迎纠错。