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包(版本根据自身要求使用，本教程用的版本均为最新)

  cn.idev.excel fastexcel 

1.新增对List数组的Converter转换器

public class ListExcelConverter implements Converter<List> { @Override public Class supportJavaTypeKey() { return List.class; } @Override public 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 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 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> imageDataMap = new HashMap(16); /** * 单元格的图片最大张数（每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置） */ private final AtomicReference 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; } @Override public 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()); } } @Override public 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 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; @ExcelProperty(value = \"123456\") private List abc = List.of(\"1\",\"2\",\"3\",\"4\",\"5\",\"6\",\"7\",\"8\",\"9\"); public List getInputStream() { return inputStream; } public void setInputStream(List inputStream) { this.inputStream = inputStream; } public List getAbc() { return abc; } public void setAbc(List abc) { this.abc = abc; }}

@RestControllerpublic 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 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 inputStreams = new ArrayList(); for(MultipartFile multipartFile: file){ inputStreams.add(multipartFile.getInputStream()); } test.setInputStream(inputStreams); List a = new ArrayList(); a.add(test); fileService.upload(ExcelUtils. export(\"123.xls\",\"123\",Test.class,a),bucketName);// return new ResultVO(\"123\");// return new ResultVO(shareUrl); }}

测试结果

3.2测试多列单照片

新增测试类

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

@RestControllerpublic 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 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 inputStream1 = test.getInputStream(); if(ValidateUtils.isEmpty(inputStream1)){ inputStream1 = new ArrayList(); inputStream1.add(multipartFile.getInputStream()); test.setInputStream(inputStream1); } else { List inputStream2 = test.getInputStream2(); if(ValidateUtils.isEmpty(inputStream2)){  inputStream2 = new ArrayList();  inputStream2.add(multipartFile.getInputStream());  test.setInputStream2(inputStream2); } } } List a = new ArrayList(); a.add(test); fileService.upload(ExcelUtils. export(\"123.xls\",\"123\",Test.class,a),bucketName);// return new ResultVO(\"123\");// return new ResultVO(shareUrl); }}

测试结果