SpringBoot整合Fastexcel/EasyExcel導出Excel導出多個圖片

整個工具的代碼都在Gitee或者Github地址內

gitee：solomon-parent: 這個項目主要是總結了工作上遇到的問題以及學習一些框架用于整合例如:rabbitMq、reids、Mqtt、S3協議的文件服務器、mongodb、xxl-job、powerjob還有用Docker compose部署各類中間組件。如果大家有什么想要弄成通用組件的，可以給我留言，我可以研究下

github：https://github.com/ZeroNing/solomon-parent

需要引入的JAR包(版本根據自身要求使用，本教程用的版本均為最新)

    <dependency><groupId>cn.idev.excel</groupId><artifactId>fastexcel</artifactId></dependency>

1.新增對List數組的Converter轉換器

public class ListExcelConverter implements Converter<List<?>> {@Overridepublic Class<?> supportJavaTypeKey() {return List.class;}@Overridepublic WriteCellData<?> convertToExcelData(List<?> list, ExcelContentProperty contentProperty,GlobalConfiguration globalConfiguration) throws IOException {if (ValidateUtils.isEmpty(list)){return new WriteCellData<>("");}Object value = list.getFirst();boolean isInputStream = value instanceof InputStream;try {if(isInputStream){List<ImageData> imageDataList = new ArrayList<>();WriteCellData<?> writeCellData = new WriteCellData<>();for(Object val : list){InputStream inputStream = (InputStream) val;ImageData imageData = new ImageData();imageData.setImage(IoUtils.toByteArray(inputStream));imageDataList.add(imageData);}writeCellData.setType(CellDataTypeEnum.EMPTY);writeCellData.setImageDataList(imageDataList);return writeCellData;} else {List<String> stringList = new ArrayList<>();for(Object val : list){stringList.add(val.toString());}return new WriteCellData<>(stringList.toString());}}catch (Exception e){return new WriteCellData<>("InputStream異常");} finally {if (ValidateUtils.isNotEmpty(list) && isInputStream){for(Object val : list){InputStream inputStream = (InputStream) val;inputStream.close();}}}}
}

2.新增對圖片的excel處理類

public class ImageCellWriteHandler implements CellWriteHandler {private final HashMap<String, List<ImageData>> imageDataMap = new HashMap<>(16);/*** 單元格的圖片最大張數（每列的單元格圖片張數不確定，單元格寬度需按照張數最多的長度來設置）*/private final AtomicReference<Integer> MAX_IMAGE_SIZE = new AtomicReference<>(0);/*** 默認圖片寬度（單位像素）：60*/private final static int DEFAULT_IMAGE_WIDTH = 60;/*** 默認像素轉換因子：32*/private final static int DEFAULT_PIXEL_CONVERSION_FACTOR = 32;/*** 圖片寬度，單位像素*/private final int imageWidth;/*** 像素轉換因子*/private final int pixelConversionFactor;public ImageCellWriteHandler() {this.imageWidth = DEFAULT_IMAGE_WIDTH;this.pixelConversionFactor = DEFAULT_PIXEL_CONVERSION_FACTOR;}public ImageCellWriteHandler(int imageWidth, int pixelConversionFactor) {this.imageWidth = imageWidth;this.pixelConversionFactor = pixelConversionFactor;}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, WriteCellData<?> cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {//  在數據轉換成功后 不是頭就把類型設置成空if (isHead) {return;}//將要插入圖片的單元格的type設置為空,下面再填充圖片if (ValidateUtils.isNotEmpty(cellData.getImageDataList())) {imageDataMap.put(cell.getRowIndex() + "_" + cell.getColumnIndex(), cellData.getImageDataList());cellData.setType(CellDataTypeEnum.EMPTY);cellData.setImageDataList(new ArrayList<>());}}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {//  在單元格寫入完畢后 ，自己填充圖片if (isHead || ValidateUtils.isEmpty(cellDataList)) {return;}Sheet sheet = cell.getSheet();WriteCellData<?> writeCellData = cellDataList.getFirst();CellDataTypeEnum type = writeCellData.getType();if (type != CellDataTypeEnum.EMPTY) {return;}List<ImageData> imageDataList = imageDataMap.get(cell.getRowIndex() + "_" + cell.getColumnIndex());int widthValue =  imageWidth * pixelConversionFactor;sheet.setColumnWidth(cell.getColumnIndex(), widthValue * MAX_IMAGE_SIZE.get() + pixelConversionFactor);int i = 0;for (ImageData imageData : imageDataList) {// 讀取文件this.insertImage(sheet, cell, imageData.getImage(), i);i = i + 1;}}/*** 重新插入一個圖片** @param sheet       Excel頁面* @param cell        表格元素* @param pictureData 圖片數據* @param i           圖片順序*/public int insertImage(Sheet sheet, Cell cell, byte[] pictureData, int i) {int picWidth = Units.pixelToEMU(imageWidth);int index = sheet.getWorkbook().addPicture(pictureData, HSSFWorkbook.PICTURE_TYPE_PNG);Drawing<?> drawing = sheet.getDrawingPatriarch();if (drawing == null) {drawing = sheet.createDrawingPatriarch();}CreationHelper helper = sheet.getWorkbook().getCreationHelper();ClientAnchor anchor = helper.createClientAnchor();/** 設置圖片坐標* 為了不讓圖片遮擋單元格的上邊框和右邊框，故 x1、x2、y1 這幾個坐標點均向后移動了一個像素點*/anchor.setDx1(Units.pixelToEMU(1) + picWidth * i);anchor.setDx2(Units.pixelToEMU(1) + picWidth + picWidth * i);anchor.setDy1(Units.pixelToEMU(1));anchor.setDy2(0);//設置圖片位置int columnIndex = cell.getColumnIndex();anchor.setCol1(columnIndex);anchor.setCol2(columnIndex);int rowIndex = cell.getRowIndex();anchor.setRow1(rowIndex);anchor.setRow2(rowIndex + 1);anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);drawing.createPicture(anchor, index);return index;}
}

最后需要在將轉換器和excel處理器set入FastEasy/EasyExcel內,代碼如下

ExcelWriterBuilder excelWriterBuilder = FastExcel.write(os, clazz).registerConverter(new ListExcelConverter()).registerWriteHandler(new ImageCellWriteHandler())

3.測試結果

3.1單行多照片測試

新增測試類

public class Test {@ExcelProperty(value = "abc")private List<InputStream> inputStream;@ExcelProperty(value = "123456")private List<String> abc = List.of("1","2","3","4","5","6","7","8","9");public List<InputStream> getInputStream() {return inputStream;}public void setInputStream(List<InputStream> inputStream) {this.inputStream = inputStream;}public List<String> getAbc() {return abc;}public void setAbc(List<String> abc) {this.abc = abc;}
}

@RestController
public class TestFileController {private final FileServiceInterface fileService;private final Logger logger = LoggerUtils.logger(TestFileController.class);public TestFileController(FileServiceInterface fileService) {this.fileService = fileService;}@PostMapping("/test")public void test(@RequestPart(name = "file") List<MultipartFile> file) throws Exception {String bucketName = "default";//判斷桶是否存在
//        boolean bucketExists = fileService.bucketExists(bucketName);
//        logger.info("桶:{}{}",bucketExists,bucketExists ? "已存在" : "不存在");
//        上傳文件
//        FileUpload fileUpload = fileService.upload(file,bucketName);
//        分享URL
//        String shareUrl = fileService.share(fileUpload.getFileName(),bucketName,3600L);
//        刪除文件
//        fileService.deleteFile(fileUpload.getFileName(),bucketName);
//        刪除桶
//        fileService.deleteBucket(bucketName);Test test = new Test();List<InputStream> inputStreams = new ArrayList<>();for(MultipartFile multipartFile: file){inputStreams.add(multipartFile.getInputStream());}test.setInputStream(inputStreams);List<Object> a = new ArrayList<>();a.add(test);fileService.upload(ExcelUtils. export("123.xls","123",Test.class,a),bucketName);
//        return new ResultVO<>("123");
//        return new ResultVO<String>(shareUrl);}
}

測試結果

3.2測試多列單照片

新增測試類

public class Test {@ExcelProperty(value = "abc")private List<InputStream> inputStream;@ExcelProperty(value = "123456")private List<String> abc = List.of("1","2","3","4","5","6","7","8","9");@ExcelProperty(value = "abc2")private List<InputStream> inputStream2;public List<InputStream> getInputStream2() {return inputStream2;}public void setInputStream2(List<InputStream> inputStream2) {this.inputStream2 = inputStream2;}public List<InputStream> getInputStream() {return inputStream;}public void setInputStream(List<InputStream> inputStream) {this.inputStream = inputStream;}public List<String> getAbc() {return abc;}public void setAbc(List<String> abc) {this.abc = abc;}
}

@RestController
public class TestFileController {private final FileServiceInterface fileService;private final Logger logger = LoggerUtils.logger(TestFileController.class);public TestFileController(FileServiceInterface fileService) {this.fileService = fileService;}@PostMapping("/test")public void test(@RequestPart(name = "file") List<MultipartFile> file) throws Exception {String bucketName = "default";//判斷桶是否存在
//        boolean bucketExists = fileService.bucketExists(bucketName);
//        logger.info("桶:{}{}",bucketExists,bucketExists ? "已存在" : "不存在");
//        上傳文件
//        FileUpload fileUpload = fileService.upload(file,bucketName);
//        分享URL
//        String shareUrl = fileService.share(fileUpload.getFileName(),bucketName,3600L);
//        刪除文件
//        fileService.deleteFile(fileUpload.getFileName(),bucketName);
//        刪除桶
//        fileService.deleteBucket(bucketName);Test test = new Test();for(MultipartFile multipartFile: file){List<InputStream> inputStream1 = test.getInputStream();if(ValidateUtils.isEmpty(inputStream1)){inputStream1 = new ArrayList<>();inputStream1.add(multipartFile.getInputStream());test.setInputStream(inputStream1);} else {List<InputStream> inputStream2 = test.getInputStream2();if(ValidateUtils.isEmpty(inputStream2)){inputStream2 = new ArrayList<>();inputStream2.add(multipartFile.getInputStream());test.setInputStream2(inputStream2);}}}List<Object> a = new ArrayList<>();a.add(test);fileService.upload(ExcelUtils. export("123.xls","123",Test.class,a),bucketName);
//        return new ResultVO<>("123");
//        return new ResultVO<String>(shareUrl);}
}

測試結果