封装EasyExcel导出工具类
相关的依赖自己网上搜索加上，这里不在阐述
@Slf4j
@Service
public class AgentExcelUtils {public String syncDynamicHeadWrite(String fileName,String sheetName,List<List<String>> headList,List<?> data) throws IOException {String downloadPath = "D:\\file\\" + fileName + DateUtils.dateTimeNow() + ".xlsx";String downloadBusinessFileName = "D:\\file\\" + fileName + DateUtils.dateTimeNow() + "1" + ".xlsx";EasyExcel.write(downloadPath).inMemory(true).head(headList).sheet(sheetName).registerWriteHandler(new AgentWaterMarkHandlerUtil("供应链")).registerWriteHandler(registerWriteHandler()).doWrite(data);File exportedFile = new File(downloadPath);Workbook workbook = WorkbookFactory.create(exportedFile);Sheet sheet = workbook.getSheet(sheetName);// 根据列内容动态调整列宽for (int i = 0; i < headList.size(); i++) {int maxLength = headList.get(i).get(0).length(); // 获取标题列的长度for (Object row : data) {String cellValue = row.toString();if (cellValue.length() > maxLength) {maxLength = cellValue.length(); // 更新列宽}}sheet.setColumnWidth(i, maxLength * 256); // 乘以256以适配Excel的宽度单位}// 保存文件try (FileOutputStream fos = new FileOutputStream(downloadBusinessFileName)) {workbook.write(fos);workbook.close();} catch (Exception e) {log.error("仓储、配送导出文件失败=", e);}return downloadBusinessFileName;}private HorizontalCellStyleStrategy registerWriteHandler() {// 头的策略WriteCellStyle headWriteCellStyle = new WriteCellStyle();// 头背景设置headWriteCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short) 10);headWriteCellStyle.setWriteFont(headWriteFont);headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);// 内容的策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();WriteFont contentWriteFont = new WriteFont();// 字体大小contentWriteFont.setFontHeightInPoints((short) 10);contentWriteCellStyle.setWriteFont(contentWriteFont);contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);}
}
excel水印代码
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.RequiredArgsConstructor;
import org.apache.commons.io.output.ByteArrayOutputStream;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.font.FontRenderContext;
import java.awt.geom.Rectangle2D;
import java.awt.image.BufferedImage;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;/*** @author sunpeiyang* @date 2025/5/9 14:33*/
@RequiredArgsConstructor
public class AgentWaterMarkHandlerUtil implements SheetWriteHandler {private final String WATER_MARK;public static ByteArrayOutputStream createWaterMark(String content) throws IOException, FontFormatException {int width = 200;int height = 150;// 添加中文字体 加载下载的中文字体,这样就不会出现中文乱码或中文丢失的情况ClassPathResource resource = new ClassPathResource("font/msyh.ttf");InputStream fi = resource.getInputStream();BufferedInputStream fb = new BufferedInputStream(fi);// String fontType = "微软雅黑";int fontStyle = Font.PLAIN;int fontSize = 20;// Font font = new Font(fontType, fontStyle, fontSize);Font font = Font.createFont(fontStyle, fb);font = font.deriveFont(fontStyle, fontSize);BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);// 获取bufferedImage对象// 获取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, 40));// 设置字体g2d.setStroke(new BasicStroke(1));// 设置字体类型 加粗 大小g2d.setFont(font);g2d.rotate(-0.5, (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();ByteArrayOutputStream os = new ByteArrayOutputStream();ImageIO.write(image, "png", os);return os;}/*** 为Excel打上水印工具函数** @param sheet excel sheet* @param bytes 水印图片字节数组*/public static void putWaterRemarkToExcel(XSSFSheet sheet, byte[] bytes) {//add relation from sheet to the picture dataXSSFWorkbook workbook = sheet.getWorkbook();int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);String rID = sheet.addRelation(null, XSSFRelation.IMAGES, workbook.getAllPictures().get(pictureIdx)).getRelationship().getId();//set background picture to sheetsheet.getCTWorksheet().addNewPicture().setId(rID);}@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {try (ByteArrayOutputStream waterMark = createWaterMark(WATER_MARK)) {XSSFSheet sheet = (XSSFSheet) writeSheetHolder.getSheet();putWaterRemarkToExcel(sheet, waterMark.toByteArray());} catch (IOException | FontFormatException e) {e.printStackTrace();}}
}字体文件私聊问我要
excel执行导出
String excelPath;try {excelPath = agentExcelUtils.syncDynamicHeadWrite(fileName, sheetName, headList, dataLists);} catch (Exception e) {log.error("导出excel失败=", e);}
excel转pdf转图片
String imageName = fileName + DateUtils.dateTimeNow() + ".png";String pdfPath = "D:\\file\\" + fileName + DateUtils.dateTimeNow() + ".pdf";String imagePath = "D:\\file\\" + fileName + DateUtils.dateTimeNow() + ".png";try {exportToImage.convertExcelToImage(excelPath, pdfPath, imagePath);} catch (Exception e) {log.error("导出excel转图片失败=", e);}
AgentExcelToImage工具类
import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.Rectangle;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import com.juepeiscm.common.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.compress.utils.IOUtils;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.rendering.PDFRenderer;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;import javax.imageio.ImageIO;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;/*** Excel 转图片*/
@Slf4j
@Service
public class AgentExcelToImage {/*** Excel->pdf->图片* @param EXCEL_PATH excel文件路径* @param PDF_PATH pdf文件路径* @param IMAGE_PATH 图片文件路径*/public void convertExcelToImage(String EXCEL_PATH, String PDF_PATH, String IMAGE_PATH) {try {// Step 1: Excel to PDFconvertExcelToPdf(EXCEL_PATH, PDF_PATH);// Step 2: PDF to ImageconvertPdfToImage(PDF_PATH, IMAGE_PATH);} catch (Exception e) {log.error("convertExcelToImage异常=", e);}}/*** pdf转图片* @param pdfFilePath pdf文件路径* @param imageFilePath 图片路径* @throws Exception 异常*/private void convertPdfToImage(String pdfFilePath, String imageFilePath) throws Exception {PDDocument document = PDDocument.load(new File(pdfFilePath));PDFRenderer renderer = new PDFRenderer(document);float scale = 2f; // DPI 比例（可适当提高如 3f）List<BufferedImage> croppedImages = new ArrayList<>();int pageWidth = 0;int totalHeight = 0;// Step 1: 渲染并裁剪每一页for (int i = 0; i < document.getNumberOfPages(); i++) {BufferedImage image = renderer.renderImage(i, scale);if (i == document.getNumberOfPages() - 1) {image = cropBottomWhiteSpace(image); // 只裁剪最后一页}croppedImages.add(image);pageWidth = Math.max(pageWidth, image.getWidth());totalHeight += image.getHeight();}// Step 2: 创建最终合并图像BufferedImage combinedImage = new BufferedImage(pageWidth, totalHeight, BufferedImage.TYPE_INT_RGB);Graphics2D g2d = combinedImage.createGraphics();int currentY = 0;for (BufferedImage img : croppedImages) {g2d.drawImage(img, 0, currentY, null);currentY += img.getHeight();}g2d.dispose();// Step 3: 保存图像ImageIO.write(combinedImage, "PNG", new File(imageFilePath));document.close();}/*** 裁剪图像底部的空白区域（假设背景为白色）*/private BufferedImage cropBottomWhiteSpace(BufferedImage img) {int width = img.getWidth();int height = img.getHeight();// 默认从下往上找第一个非白色像素int bottom = height - 1;outer:for (int y = height - 1; y >= 0; y--) {for (int x = 0; x < width; x++) {if ((img.getRGB(x, y) & 0xFFFFFF) != 0xFFFFFF) { // 非白色像素bottom = y;break outer;}}}// 设置新的高度（裁剪到底部有效行）int newHeight = bottom + 1;return img.getSubimage(0, 0, width, newHeight);}/*** excel转pdf* @param excelFilePath excel路径* @param pdfFilePath pdf路径* @throws Exception 异常*/private void convertExcelToPdf(String excelFilePath, String pdfFilePath) throws Exception {Workbook workbook = new XSSFWorkbook(Files.newInputStream(Paths.get(excelFilePath)));Sheet sheet2 = workbook.getSheetAt(0);float rowHeightPt = 60f;int headerRows = 1;int dataRows = sheet2.getLastRowNum();float calculatedHeight = (headerRows + dataRows) * rowHeightPt - (dataRows * 30);// 设置页面高度为自适应高度Rectangle pageSize = new Rectangle(PageSize.A4.getWidth(), calculatedHeight);Document document = new Document(pageSize, 20, 20, 20, 0); //设置上下左右边距PdfWriter writer = PdfWriter.getInstance(document, Files.newOutputStream(Paths.get(pdfFilePath)));writer.setPageEvent(new WaterMarkEvent());document.open();Font font = null;BaseFont baseFont = null;ClassPathResource resource = new ClassPathResource("font/msyh.ttf");try (InputStream inputStream = resource.getInputStream()) {byte[] fontBytes = IOUtils.toByteArray(inputStream);baseFont = BaseFont.createFont("font/msyh.ttf", BaseFont.IDENTITY_H, BaseFont.EMBEDDED, true, fontBytes, null);font = new Font(baseFont, 12, Font.NORMAL);} catch (Exception e) {e.printStackTrace();log.error("convertExcelToPdf异常=", e);}//----开始测试
// String fontPath = "D:\\src\\main\\resources\\font\\msyh.ttf";
// File fontFile = new File(fontPath);
// if (!fontFile.exists()) {
// }
// BaseFont baseFont = BaseFont.createFont(fontPath, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
// Font font = new Font(baseFont, 12, Font.NORMAL);//----结束测试// Iterate over sheets, but only add header onceboolean headerAdded = false;for (int i = 0; i < workbook.getNumberOfSheets(); i++) {Sheet sheet = workbook.getSheetAt(i);if (sheet == null || sheet.getLastRowNum() < 0) continue;int columnCount = sheet.getRow(0).getLastCellNum();PdfPTable table = new PdfPTable(columnCount);table.setWidthPercentage(100);table.setSplitLate(false); // 允许拆分到下一页table.setExtendLastRow(false);// Add header row only onceif (!headerAdded) {Font whiteFont = new Font(baseFont, 12, Font.NORMAL);whiteFont.setColor(BaseColor.WHITE);addTableRows(sheet, table, whiteFont, true); //使用 whiteFontheaderAdded = true;}addTableRows(sheet, table, font, false);document.add(table);if (i < workbook.getNumberOfSheets() - 1) {document.newPage();}}document.close();writer.close();workbook.close();}private void addTableRows(Sheet sheet, PdfPTable table, Font font, boolean isHeader) {// Add header row (only once)if (isHeader) {Row row = sheet.getRow(0);if (row != null) {int columnCount = row.getLastCellNum();for (int k = 0; k < columnCount; k++) {Cell cell = row.getCell(k);String cellValue = cell != null ? cell.toString() : "";PdfPCell pdfCell = new PdfPCell(new Phrase(cellValue, font));applyCellStyle(pdfCell, true);pdfCell.setMinimumHeight(30f);table.addCell(pdfCell);}}}if (!isHeader) {for (int j = 1; j <= sheet.getLastRowNum(); j++) {Row dataRow = sheet.getRow(j);if (dataRow == null) continue;int columnCount = dataRow.getLastCellNum();for (int k = 0; k < columnCount; k++) {Cell cell = dataRow.getCell(k);String cellValue = cell != null ? cell.toString() : "";// 动态计算字体大小float baseFontSize = 12f;float minFontSize = 6f; // 最小字体int maxChars = 10; // 每个单元格最多容纳字符数int charCount = cellValue.length();float adjustedFontSize = baseFontSize;if (charCount > maxChars) {adjustedFontSize = Math.max(minFontSize, baseFontSize * ((float) maxChars / charCount));}Font dynamicFont = new Font(font.getBaseFont(), adjustedFontSize, font.getStyle());PdfPCell pdfCell = new PdfPCell(new Phrase(cellValue, dynamicFont));applyCellStyle(pdfCell, false);pdfCell.setMinimumHeight(30f); // 固定行高pdfCell.setNoWrap(false); // 允许换行（可选）table.addCell(pdfCell);}}}}private void applyCellStyle(PdfPCell cell, boolean isHeader) {cell.setPadding(5);cell.setBorder(Rectangle.BOX);cell.setBorderWidth(1f);cell.setBorderColor(BaseColor.BLACK);if (isHeader) {// 设置表头背景色为 AQUA（浅蓝色）cell.setBackgroundColor(new BaseColor(0, 85, 145));cell.setHorizontalAlignment(Element.ALIGN_CENTER);} else {cell.setBackgroundColor(BaseColor.WHITE);cell.setHorizontalAlignment(Element.ALIGN_CENTER);}}public static void main(String[] args) {AgentExcelToImage agentExcelToImage = new AgentExcelToImage();String EXCEL_PATH = "D:\\file\\出库单信息确认202505131514021.xlsx";String PDF_PATH = "D:\\file\\output" + DateUtils.dateTimeNow() + ".pdf";String IMAGE_PATH = "D:\\file\\output_image" + DateUtils.dateTimeNow() + ".png";agentExcelToImage.convertExcelToImage(EXCEL_PATH, PDF_PATH, IMAGE_PATH);}
}
pdf水印工具类
import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Element;
import com.itextpdf.text.Font;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.Rectangle;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfContentByte;
import com.itextpdf.text.pdf.PdfGState;
import com.itextpdf.text.pdf.PdfPageEventHelper;
import com.itextpdf.text.pdf.PdfWriter;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.ClassPathResource;import java.io.File;
import java.io.InputStream;import static com.google.common.io.ByteStreams.toByteArray;/*** @author sunpeiyang* @date 2025/5/11 19:52*/
@Slf4j
public class WaterMarkEvent extends PdfPageEventHelper {private String waterMarkText = "供应链";@Overridepublic void onEndPage(PdfWriter writer, com.itextpdf.text.Document document) {try {PdfContentByte cb = writer.getDirectContent();Rectangle rect = document.getPageSize();// 加载中文字体（确保路径正确）BaseFont baseFont = null;ClassPathResource resource = new ClassPathResource("font/msyh.ttf");try (InputStream inputStream = resource.getInputStream()) {byte[] fontBytes = toByteArray(inputStream);baseFont = BaseFont.createFont("font/msyh.ttf", BaseFont.IDENTITY_H, BaseFont.EMBEDDED, true, fontBytes, null);} catch (Exception e) {e.printStackTrace();log.error("水印字体转换异常=", e);}//----开始测试
// String fontPath = "D:\\jp_project\\jp-customer-service-agent-admin2\\customer-service-agent-main\\src\\main\\resources\\font\\msyh.ttf";
// File fontFile = new File(fontPath);
// if (!fontFile.exists()) {
// }
// BaseFont baseFont = BaseFont.createFont(fontPath, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);// 设置透明度PdfGState gstate = new PdfGState();gstate.setFillOpacity(0.5f);cb.saveState();cb.setGState(gstate);float fontSize = 12;float spacingX = 80; // 减小横向间距float spacingY = 50; // 减小纵向间距float pageWidth = rect.getWidth();float pageHeight = rect.getHeight();// 遍历页面绘制多个水印for (float x = 0; x < pageWidth; x += spacingX) {for (float y = 0; y < pageHeight; y += spacingY) {cb.beginText();cb.setFontAndSize(baseFont, fontSize);cb.setColorFill(BaseColor.LIGHT_GRAY); // 或者 BaseColor.GRAY 更深一些cb.showTextAligned(Element.ALIGN_CENTER,waterMarkText, // 只传字符串x + spacingX / 2,y + spacingY / 2,45 // 旋转 45°);cb.endText();}}cb.restoreState();} catch (Exception e) {System.err.println("绘制水印失败: " + e.getMessage());e.printStackTrace();}}
}
效果图如下
