目录
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);
// 导出到字节流B
ByteArrayOutputStream 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 block
e.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