Java导出Excel 带图片

可自行优化

相关依赖自定义注解参考：

java导入Excel(带图片)-暂支持浮动式图片-CSDN博客

import com.amazonaws.services.s3.model.S3ObjectInputStream;
import com.sgcloud.clp.common.file.core.FileTemplate;
import com.sgcloud.clp.industrySupervision.util.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.awt.*;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Objects;

/**
 * @Author zbz
 * @Date 2024/2/19
 * @Description
 */
@Slf4j
public class ExcelExporter {

	public static <T> void export(List<T> entities, HttpServletResponse response, FileTemplate fileTemplate, String title) throws IOException {
		try (Workbook workbook = new XSSFWorkbook()) {
			Sheet sheet = workbook.createSheet("Data");

			// 设置标题样式
			CellStyle titleStyle = createTitleStyle(workbook);

			// 设置表头样式
			CellStyle headerStyle = createHeaderStyle(workbook);

			// 设置内容样式
			CellStyle contentStyle = createContentStyle(workbook);

			// 写入标题
			Row titleRow = sheet.createRow(0);
			Cell titleCell = titleRow.createCell(0);
			titleCell.setCellValue(title);
			titleCell.setCellStyle(titleStyle);
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, getColumnCount(entities.get(0).getClass()) - 1));

			// 写入表头
			writeHeaderRow(sheet, entities.get(0).getClass(), headerStyle);

			// 写入数据
			int rowIndex = 2; // 从第三行开始写入数据（标题占一行，表头占一行）
			for (T entity : entities) {
				Row row = sheet.createRow(rowIndex++);
				writeEntityData(row, workbook, sheet, entity, contentStyle, fileTemplate);
			}

			// 调整列宽
			for (int i = 0; i < getColumnCount(entities.get(0).getClass()); i++) {
				sheet.autoSizeColumn(i);
			}

			// 导出文件
			ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
			workbook.write(outputStream);
			byte[] bytes = outputStream.toByteArray();
			//  响应客户端浏览器区分数据
			//  清空首部
			response.reset();
			response.setContentType("application/vnd.ms-excel; charset=UTF-8");
			//  没有缓存
			response.setHeader("Cache-Control", "No-cache");
			//  没有缓存
			response.setHeader("Pragma", "No-cache");
			//  设置文件名
			response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(title + ".xlsx", "UTF-8"));
			response.addHeader("Content-Length", String.valueOf(bytes.length));

			// 将工作簿写入输出流
			workbook.write(response.getOutputStream());
			response.getOutputStream().flush();
			response.getOutputStream().close();
		}
	}

	/**
	 * 获取列数
	 * @param entityType
	 * @return
	 */
	private static int getColumnCount(Class<?> entityType) {
		int count = 0;
		Field[] fields = entityType.getDeclaredFields();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelProperty.class)) {
				count++;
			}
		}
		return count;
	}

	/**
	 * 写入表头行
	 * @param sheet
	 * @param entityType
	 * @param headerStyle
	 */
	private static void writeHeaderRow(Sheet sheet, Class<?> entityType, CellStyle headerStyle) {
		Row headerRow = sheet.createRow(1);
		Field[] fields = entityType.getDeclaredFields();
		int columnIndex = 0;
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelProperty.class)) {
				ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
				Cell cell = headerRow.createCell(columnIndex);
				cell.setCellValue(excelProperty.value());
				cell.setCellStyle(headerStyle);

				double width = getColumnWidthByValue(entityType, excelProperty.value());

				// 设置列宽
				sheet.setColumnWidth(columnIndex, (int) width * 256); // 乘以256是因为单位是1/256个字符宽度
				columnIndex++;
			}
		}
	}

	/**
	 * 根据内容获取宽度
	 * @param entityType
	 * @param value
	 * @return
	 */
	private static double getColumnWidthByValue(Class<?> entityType, String value) {
		Field[] fields = entityType.getDeclaredFields();
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelProperty.class)) {
				ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
				if (excelProperty.value().equals(value)) {
					return excelProperty.width();
				}
			}
		}
		return -1; // 如果找不到对应的列宽度，返回-1
	}

	/**
	 * 写入实体内容
	 * @param row
	 * @param workbook
	 * @param sheet
	 * @param entity
	 * @param contentStyle
	 * @param fileTemplate
	 * @param <T>
	 */
	private static <T> void writeEntityData(Row row, Workbook workbook, Sheet sheet, T entity, CellStyle contentStyle, FileTemplate fileTemplate) {
		Class<?> entityType = entity.getClass();
		Field[] fields = entityType.getDeclaredFields();
		int columnIndex = 0;
		int maxHeight = 0; // 记录最大图片高度
		for (Field field : fields) {
			if (field.isAnnotationPresent(ExcelProperty.class)) {
				field.setAccessible(true); // 设置字段可访问
				ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
				Cell cell = row.createCell(columnIndex++);
				try {
					Object value = field.get(entity);
					if (excelProperty.type() == 2 && value instanceof String) {
						writeImage(cell, workbook, sheet, (String) value, excelProperty.width(), excelProperty.height(), fileTemplate);
					} else {
						setCellValue(cell, value);
						cell.setCellStyle(contentStyle);
					}
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}
		}

	}

	/**
	 * 设置单元格内容
	 * @param cell
	 * @param value
	 */
	private static void setCellValue(Cell cell, Object value) {
		if (value != null) {
			if (value instanceof String) {
				cell.setCellValue((String) value);
			} else if (value instanceof Number) {
				cell.setCellValue(((Number) value).doubleValue());
			} else if (value instanceof Boolean) {
				cell.setCellValue((Boolean) value);
			} else if (value instanceof LocalDateTime) {
				cell.setCellValue(DateUtil.localDateTimeFormat((LocalDateTime) value, "yyyy-MM-dd HH:mm:ss"));
			}
		}
	}

	/**
	 * 创建标题样式
	 * @param workbook
	 * @return
	 */
	private static CellStyle createTitleStyle(Workbook workbook) {
		Font titleFont = workbook.createFont();
		titleFont.setFontName("仿宋");
		titleFont.setFontHeightInPoints((short) 24); // 标题字体大小为24
		titleFont.setBold(true);

		CellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setFont(titleFont);
		titleStyle.setAlignment(HorizontalAlignment.CENTER); // 居中对齐

		return titleStyle;
	}

	/**
	 * 创建表头样式
	 * @param workbook
	 * @return
	 */
	private static CellStyle createHeaderStyle(Workbook workbook) {
		Font headerFont = workbook.createFont();
		headerFont.setFontName("仿宋");
		headerFont.setFontHeightInPoints((short) 18); // 表头字体大小为18
		headerFont.setBold(true);

		CellStyle headerStyle = workbook.createCellStyle();
		headerStyle.setFont(headerFont);
		headerStyle.setAlignment(HorizontalAlignment.CENTER); // 居中对齐
		headerStyle.setBorderTop(BorderStyle.THIN);
		headerStyle.setBorderBottom(BorderStyle.THIN);
		headerStyle.setBorderLeft(BorderStyle.THIN);
		headerStyle.setBorderRight(BorderStyle.THIN);

		return headerStyle;
	}

	/**
	 * 创建内容样式
	 * @param workbook
	 * @return
	 */
	private static CellStyle createContentStyle(Workbook workbook) {
		Font contentFont = workbook.createFont();
		contentFont.setFontName("仿宋");
		contentFont.setFontHeightInPoints((short) 14); // 正文字体大小为14

		CellStyle contentStyle = workbook.createCellStyle();
		contentStyle.setFont(contentFont);
		contentStyle.setAlignment(HorizontalAlignment.CENTER); // 居中对齐
		contentStyle.setBorderTop(BorderStyle.THIN);
		contentStyle.setBorderBottom(BorderStyle.THIN);
		contentStyle.setBorderLeft(BorderStyle.THIN);
		contentStyle.setBorderRight(BorderStyle.THIN);

		// 设置垂直居中
		contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		// 设置自动换行
		contentStyle.setWrapText(true);

		return contentStyle;
	}

	/**
	 * 写入图片
	 * @param cell 单元格
	 * @param workbook 表格
	 * @param sheet 页
	 * @param imagePath 图片地址
	 * @param width 图片宽度
	 * @param height 图片高度
	 * @param fileTemplate 文件处理实例
	 */
	private static void writeImage(Cell cell, Workbook workbook, Sheet sheet, String imagePath, double width, double height, FileTemplate fileTemplate) {
		if (Objects.isNull(imagePath)) return;
		byte[] imageData = getImageData(imagePath, fileTemplate);

		Drawing<?> drawing = sheet.createDrawingPatriarch();

		// 计算单元格的行号和列号
		int rowIndex = cell.getRowIndex();
		int columnIndex = cell.getColumnIndex();

		// 计算图片的起始和结束列号
		int col1 = columnIndex;
		int col2 = columnIndex + 1; // 假设图片占用一个单元格的宽度

		// 计算图片的起始和结束行号
		int row1 = rowIndex;
		int row2 = rowIndex + 1; // 假设图片占用一个单元格的高度

		// 计算图片的宽度和高度
		double imageWidth = width * Units.EMU_PER_PIXEL;
		double imageHeight = height * Units.EMU_PER_PIXEL;

		// 计算单元格的宽度和高度
		double cellWidth = sheet.getColumnWidthInPixels(columnIndex) * 0.9;
		double cellHeight = cell.getRow().getHeightInPoints() * Units.EMU_PER_POINT * 0.9;

		// 计算图片在单元格中的位置
		int dx1 = (int) ((cellWidth - imageWidth) / 2); // 水平居中
		int dy1 = (int) ((cellHeight - imageHeight) / 2); // 垂直居中

		// 创建锚点
		ClientAnchor anchor = drawing.createAnchor(dx1, dy1, 0, 0, col1, row1, col2, row2);

		// 添加图片到锚点
		int pictureIndex = workbook.addPicture(imageData, Workbook.PICTURE_TYPE_JPEG);
		Picture picture = drawing.createPicture(anchor, pictureIndex);

		// 设置单元格样式
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setWrapText(true);
		cell.setCellStyle(cellStyle);

		// 清空单元格内容
		cell.setCellValue("");

		// 设置单元格列宽为图片的宽度
		sheet.setColumnWidth(col1, (int) (width / 2.54 * 72 * 256)); // 256为单位，表示1/256个字符宽度

		// 设置单元格行高为图片的高度
		cell.getRow().setHeight((short) (height / 2.54 * 72 * 20)); // 20为单位，表示20个字符的高度，可以根据实际情况调整
	}

	/**
	 * 根据连接下载图片并转换为 byte[]
	 *
	 * @param orgSourcePath
	 * @return
	 */
	private static byte[] getImageData(String orgSourcePath, FileTemplate fileTemplate) {
		String[] split = orgSourcePath.split("\\?")[1].split("&");
		String bucket = split[0].split("=")[1];
		String fileName = split[1].split("=")[1];
		S3ObjectInputStream s3ObjectInputStream = fileTemplate.getObject(bucket, fileName).getObjectContent();

		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		try {
			byte[] buffer = new byte[4096];
			int bytesRead;

			while ((bytesRead = s3ObjectInputStream.read(buffer)) != -1) {
				outputStream.write(buffer, 0, bytesRead);
			}
		} catch (IOException e) {
			log.error("图片输入流转换byte[]失败，Exception:{}", e);
		}
		return outputStream.toByteArray();
	}
}