目录
pom中导入ooxml-schemas依赖
新增水印类ExcelWaterMark.java
ExcelUtil.java中重写setResponseHeader方法
pom中导入ooxml-schemas依赖
<!-- excel操作 --><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>ooxml-schemas</artifactId><version>1.0</version></dependency>
新增水印类ExcelWaterMark.java
public class ExcelWaterMark{private static final Logger log = LoggerFactory.getLogger(ExcelWaterMark.class);/*** 给 Excel 添加水印** @param workbook XSSFWorkbook* @param waterMarkText 水印文字内容*/public static void insertWaterMarkText(XSSFWorkbook workbook, String waterMarkText){BufferedImage image = createWatermarkImage(waterMarkText);// 导出到字节流BByteArrayOutputStream os = new ByteArrayOutputStream();try{ImageIO.write(image, "png", os);}catch (IOException e){log.error("添加水印失败");}int pictureIdx = workbook.addPicture(os.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG);XSSFPictureData pictureData = workbook.getAllPictures().get(pictureIdx);for (int i = 0; i < workbook.getNumberOfSheets(); i++){// 获取每个Sheet表XSSFSheet sheet = workbook.getSheetAt(i);PackagePartName ppn = pictureData.getPackagePart().getPartName();String relType = XSSFRelation.IMAGES.getRelation();PackageRelationship pr = sheet.getPackagePart().addRelationship(ppn, TargetMode.INTERNAL, relType, null);sheet.getCTWorksheet().addNewPicture().setId(pr.getId());}}/*** 创建水印图片 excel** @param waterMark 水印内容*/public static BufferedImage createWatermarkImage(String waterMark){String[] textArray = waterMark.split("\n");java.awt.Font font = new java.awt.Font("microsoft-yahei", java.awt.Font.PLAIN, 20);Integer width = 500;Integer height = 200;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(Integer.parseInt("#C5CBCF".substring(1), 16))); // 设定画笔颜色g.setFont(font); // 设置画笔字体g.shear(0.1, -0.26); // 设定倾斜度// 设置字体平滑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;}}
ExcelUtil.java中重写setResponseHeader方法
// 发送响应流方法public void setResponseHeader(XSSFWorkbook workbook, HttpServletResponse response, String fileName, String fileType) {try {try {ExcelWaterMark.insertWaterMarkText(workbook, "内部资料 请勿外传");// fileName = new String(fileName.getBytes(),"UTF-8");//fileName = new String(fileName.getBytes("gbk"), "iso8859-1") + ".xls";fileName = new String(fileName.getBytes("gbk"), "iso8859-1") + "." + fileType;} catch (UnsupportedEncodingException e) {// TODO Auto-generated catch blocke.printStackTrace();}response.setContentType("application/x-download");// 下面三行是关键代码，处理乱码问题response.setCharacterEncoding("utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + fileName);/** response.setContentType("application/octet-stream;charset=UTF-8");* response.setHeader("Content-Disposition", "attachment;filename="+ fileName);*/response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");} catch (Exception ex) {ex.printStackTrace();}}
水印添加只支持XSSF，不支持HSSF。ooxml-schemas和poi版本是有可能冲突的，调试一番poi3.9和ooxml-schemas1.0版本不存在冲突jar
