工具类之十四 excel加水印工具类
程序员文章站
2022-05-01 11:34:27
...
package com.taylor.query.utils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.Color;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.Map;
/**
* 2020/11/20修改 by Taylor
*/
public class ExcelExport {
/**未定义的字段*/
public static String NO_DEFINE = "no_define";
/**默认日期格式*/
public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";
public static int DEFAULT_COLOUMN_WIDTH = 17;
/**
* 导出Excel (.xls)格式
* @param waterContent 水印
* @param headMap 属性-列头
* @param jsonArray 数据集
* @param datePattern 日期格式，传null值则默认 年月日
* @param colWidth 列宽 默认 至少17个字节
* @param out 输出流
*/
public static void exportExcelX(String waterContent, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
if(datePattern==null) {datePattern = DEFAULT_DATE_PATTERN;}
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();//缓存
//workbook.setCompressTempFiles(true);
//表头样式
/*CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);*/
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setFillForegroundColor((short) 10);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
HSSFSheet sheet = workbook.createSheet();
//设置列宽
int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii,arrColWidth[ii]*256);
ii++;
}
// 遍历集合数据，产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if(rowIndex == 65535 || rowIndex == 0){
if ( rowIndex != 0 ){ sheet = workbook.createSheet();}//如果数据超过了，则在第二页显示
/*SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));*/
HSSFRow headerRow = sheet.createRow(0); //列头 rowIndex =1
for(int i=0;i<headers.length;i++)
{
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 1;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
HSSFRow dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++)
{
HSSFCell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if(o==null) {
cellValue = "";
} else if(o instanceof Date) {
cellValue = new SimpleDateFormat(datePattern).format(o);
} else if(o instanceof Float || o instanceof Double) {
cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
} else {
cellValue = o.toString();
}
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
painWaterMark(workbook,waterContent);
workbook.write(out);
// System.out.println("end!!!");
// workbook.dispose();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 大数据量 excel 导出。
* @param waterContent 水印
* @param headMap
* @param jsonArray
* @param datePattern
* @param colWidth
* @param out
*/
public static void SXSSFexportExcel(String waterContent, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
if(datePattern==null) {datePattern = DEFAULT_DATE_PATTERN;}
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
/*CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 700);
titleStyle.setFont(titleFont);*/
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setFillForegroundColor((short) 10);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
// 生成一个(带标题)表格
SXSSFSheet sheet = workbook.createSheet();
//设置列宽
int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext();) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = fieldName.getBytes().length;
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii,arrColWidth[ii]*256);
ii++;
}
// 遍历集合数据，产生数据行
int rowIndex = 0;
for (Object obj : jsonArray) {
if(rowIndex == 65535 || rowIndex == 0){
if ( rowIndex != 0 ) {sheet = (SXSSFSheet) workbook.createSheet();}//如果数据超过了，则在第二页显示
/*SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));*/
SXSSFRow headerRow = (SXSSFRow) sheet.createRow(0); //列头 rowIndex =1
for(int i=0;i<headers.length;i++)
{
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 1;//数据内容从 rowIndex=2开始
}
JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++)
{
SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);
Object o = jo.get(properties[i]);
String cellValue = "";
if(o==null) {
cellValue = "";
} else if(o instanceof Date) {
cellValue = new SimpleDateFormat(datePattern).format(o);
} else if(o instanceof Float || o instanceof Double) {
cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString();
} else {
cellValue = o.toString();
}
newCell.setCellValue(cellValue);
newCell.setCellStyle(cellStyle);
}
rowIndex++;
}
// 自动调整宽度
/*for (int i = 0; i < headers.length; i++) {
sheet.autoSizeColumn(i);
}*/
try {
painWaterMark(workbook,waterContent);
workbook.write(out);
// System.out.println("end!!!");
workbook.dispose();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 加水印
* @param wb
* @param content
* @throws IOException
*/
public static void painWaterMark(Workbook wb, String content) throws IOException {
///String imgFileName = "waterMark_photo_"+content+".png";
///createWaterMark(content,imgFileName);
ByteArrayOutputStream waterMark = createWaterMark(content);
int sheetSize = wb.getNumberOfSheets();
for(int i=0;i<sheetSize;i++){
Sheet sheet = wb.getSheetAt(i);
//获取excel实际所占行
int row = sheet.getFirstRowNum() + sheet.getLastRowNum();
//获取excel实际所占列
int cell = sheet.getRow(sheet.getFirstRowNum()).getLastCellNum() + 1;
//根据行与列计算实际所需多少水印
putWaterRemarkToExcel(wb, sheet, waterMark, 0, 0, 3, 4, cell / 3 + 1, row / 4 + 1, 0, 0);
}
}
/**
* 创建水印图片
* @param content
* @param fileName
* @return 图片路径
* @throws IOException
*/
private static String createWaterMark(String content, String fileName) throws IOException {
BufferedImage image = getBufferedImage(content);
String targetImagePath = Thread.currentThread().getContextClassLoader().getResource("").getPath()+fileName;
ImageIO.write(image, "png", new File(targetImagePath));
return targetImagePath;
}
/**
* 创建水印输出流
* @param content
* @return 图片输出
* @throws IOException
*/
private static ByteArrayOutputStream createWaterMark(String content) throws IOException {
BufferedImage image = getBufferedImage(content);
// 加载图片
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(image, "png", byteArrayOut);
return byteArrayOut;
}
private static BufferedImage getBufferedImage(String content) {
Integer width = 300;
Integer height = 200;
// 获取bufferedImage对象
BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
String fontType = "宋体";
Integer fontStyle = java.awt.Font.PLAIN;
Integer fontSize = 50;
java.awt.Font font = new java.awt.Font(fontType, fontStyle, fontSize);
// 获取Graphics2d对象
Graphics2D g2d = image.createGraphics();
image = g2d.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
g2d.dispose();
g2d = image.createGraphics();
g2d.setColor(new Color(0, 0, 0, 80)); //设置字体颜色和透明度
// 设置字体
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();
Rectangle2D bounds = font.getStringBounds(content, context);
double x = (width - bounds.getWidth()) / 2;
double y = (height - bounds.getHeight()) / 2;
double ascent = -bounds.getY();
double baseY = y + ascent;
// 写入水印文字原定高度过小，所以累计写水印，增加高度
g2d.drawString(content, (int) x, (int) baseY);
// 设置透明度
g2d.setComposite(AlphaComposite.getInstance(AlphaComposite.SRC_OVER));
// 释放对象
g2d.dispose();
return image;
}
/**
* 为Excel打上水印工具函数 请自行确保参数值，以保证水印图片之间不会覆盖。 在计算水印的位置的时候，并没有考虑到单元格合并的情况，请注意
*
* @param wb Excel Workbook
* @param sheet 需要打水印的Excel
* @param waterRemarkPath 水印地址，classPath，目前只支持png格式的图片，
* 因为非png格式的图片打到Excel上后可能会有图片变红的问题，且不容易做出透明效果。
* 同时请注意传入的地址格式，应该为类似："\\excelTemplate\\test.png"
* @param startXCol 水印起始列
* @param startYRow 水印起始行
* @param betweenXCol 水印横向之间间隔多少列
* @param betweenYRow 水印纵向之间间隔多少行
* @param XCount 横向共有水印多少个
* @param YCount 纵向共有水印多少个
* @param waterRemarkWidth 水印图片宽度为多少列
* @param waterRemarkHeight 水印图片高度为多少行
* @throws IOException
*/
private static void putWaterRemarkToExcel(Workbook wb, Sheet sheet, String waterRemarkPath, int startXCol,
int startYRow, int betweenXCol, int betweenYRow, int XCount, int YCount, int waterRemarkWidth,
int waterRemarkHeight) throws IOException {
// 校验传入的水印图片格式
if (!waterRemarkPath.endsWith("png") && !waterRemarkPath.endsWith("PNG")) {
throw new RuntimeException("向Excel上面打印水印，目前支持png格式的图片。");
}
// 加载图片
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
// InputStream imageIn = new FileInputStream(waterRemarkPath);
InputStream imageIn = Thread.currentThread().getContextClassLoader().getResourceAsStream(waterRemarkPath);
if (null == imageIn || imageIn.available() < 1) {
throw new RuntimeException("向Excel上面打印水印，读取水印图片失败(1)。");
}
BufferedImage bufferImg = ImageIO.read(imageIn);
if (null == bufferImg) {
throw new RuntimeException("向Excel上面打印水印，读取水印图片失败(2)。");
}
ImageIO.write(bufferImg, "png", byteArrayOut);
putWaterRemarkToExcel(wb, sheet, byteArrayOut, startXCol, startYRow, betweenXCol, betweenYRow, XCount, YCount, waterRemarkWidth, waterRemarkHeight);
}
/**
* 为Excel打上水印工具函数 请自行确保参数值，以保证水印图片之间不会覆盖。 在计算水印的位置的时候，并没有考虑到单元格合并的情况，请注意
*
* @param wb Excel Workbook
* @param sheet 需要打水印的Excel
* @param waterRemarkByteArrayOut 水印数据，目前只支持png格式的图片，
* 因为非png格式的图片打到Excel上后可能会有图片变红的问题，且不容易做出透明效果。
* @param startXCol 水印起始列
* @param startYRow 水印起始行
* @param betweenXCol 水印横向之间间隔多少列
* @param betweenYRow 水印纵向之间间隔多少行
* @param XCount 横向共有水印多少个
* @param YCount 纵向共有水印多少个
* @param waterRemarkWidth 水印图片宽度为多少列
* @param waterRemarkHeight 水印图片高度为多少行
* @throws IOException
*/
private static void putWaterRemarkToExcel(Workbook wb, Sheet sheet,ByteArrayOutputStream waterRemarkByteArrayOut, int startXCol, int startYRow, int betweenXCol, int betweenYRow, int XCount, int YCount, int waterRemarkWidth, int waterRemarkHeight) {
// 开始打水印
Drawing drawing = sheet.createDrawingPatriarch();
// 按照共需打印多少行水印进行循环
for (int yCount = 0; yCount < YCount; yCount++) {
// 按照每行需要打印多少个水印进行循环
for (int xCount = 0; xCount < XCount; xCount++) {
// 创建水印图片位置
int xIndexInteger = startXCol + (xCount * waterRemarkWidth) + (xCount * betweenXCol);
int yIndexInteger = startYRow + (yCount * waterRemarkHeight) + (yCount * betweenYRow);
/*
* 参数定义： 第一个参数是（x轴的开始节点）； 第二个参数是（是y轴的开始节点）； 第三个参数是（是x轴的结束节点）；
* 第四个参数是（是y轴的结束节点）； 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
* 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）； 第七个参数是（图片宽度，共多少列）；
* 第8个参数是（图片高度，共多少行）；
*/
ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, xIndexInteger,
yIndexInteger, xIndexInteger + waterRemarkWidth, yIndexInteger + waterRemarkHeight);
Picture pic = drawing.createPicture(anchor,
wb.addPicture(waterRemarkByteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
pic.resize();
}
}
}
}
上一篇： CSS图片高度自适应
推荐阅读
-
Springboot基础之RedisUtils工具类
-
自定义MVC框架之工具类-分页类的封装
-
php工具类之【视频变换类】
-
Java JDBC入门之八 : DAO设计模式重构查询方法 AND 使用BeanUtils工具类操作JavaBean
-
php工具类之【String处置类】
-
Android 数据存储之 FileInputStream 工具类及FileInputStream类的使用
-
Java多线程同步工具类之Semaphore
-
Android开发之拼音转换工具类PinyinUtils示例
-
Android开发之超强图片工具类BitmapUtil完整实例
-
Android 数据存储之 FileInputStream 工具类及FileInputStream类的使用