此文章要干嘛?

使用easyExcel针对于普通数据, url类型和String类型单张,多张,,无异常图片导出Excel文件

官网针对图片导出提供五种方式, 如有其他需求参考官方文档

官网: EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel

---

一、效果展示?

二、使用步骤

1.引包

该案例使用easyexcel 2.2.7

    <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.7</version></dependency>

该案例引入的包

        <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.7</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.20</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.4.0</version></dependency><dependency><groupId>javax.servlet</groupId><artifactId>javax.servlet-api</artifactId><version>4.0.1</version></dependency>

2.代码

2.1实体类：

MyUrlConverterUtil : Url类型图片转换工具

MyStringImageConverterUtil: String类型图片转换工具

@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER)
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
public class MyExcel {@ExcelProperty(value = "姓名")private String name;@ExcelProperty(value = "年龄")private String age;@ExcelProperty(value = "URL图片", converter = MyUrlConverterUtil.class)private List<URL> imageUrls;@ExcelProperty(value = "String图片", converter = MyStringImageConverterUtil.class)private List<String> imageStrings;
}

2.2. String类型多图片导出到单元格转换工具类

public class MyUrlConverterUtil implements Converter<List<URL>> {@Overridepublic Class supportJavaTypeKey() {return List.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {/***这里记得枚举类型为IMAGE*/return CellDataTypeEnum.IMAGE;}@Overridepublic List convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return null;}@Overridepublic CellData convertToExcelData(List<URL> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {// 这里进行对数据实体类URL集合处理List<CellData> data = new ArrayList<>();// for 循环一次读取for (URL url : value) {InputStream inputStream = null;try {inputStream = url.openStream();byte[] bytes = IoUtils.toByteArray(inputStream);data.add(new CellData(bytes));} catch (Exception e) {//图片异常展示的图片data.add(new CellData(IoUtils.toByteArray(new FileInputStream("D:\\easyexcel\\err.png"))));continue;} finally {if (inputStream != null){inputStream.close();}}}// 这种方式并不能返回一个List,所以只好通过CellData cellData = new CellData(data);将这个list对象塞到返回值CellData对象的data属性中；CellData cellData = new CellData(data);cellData.setType(CellDataTypeEnum.IMAGE);return cellData;}
}

2.3.String类型多图片导出到单元格转换工具类 

public class MyStringImageConverterUtil implements Converter<List<String>> {@Overridepublic Class supportJavaTypeKey() {return List.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {/***这里记得枚举类型为IMAGE*/return CellDataTypeEnum.IMAGE;}@Overridepublic List convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {//从excel中读数据时被EasyExcel调用String stringValue = cellData.getStringValue();//用json转换工具将excel单元格中数据转换为java List<String>对象List<String> list = Convert.toList(String.class,stringValue);return list;}@Overridepublic CellData convertToExcelData(List<String> stringUrl, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {// 这里进行对数据实体类URL集合处理List<CellData> data = new ArrayList<>();// for 循环一次读取for (String url : stringUrl) {FileInputStream inputStream = null;try {inputStream = new FileInputStream(url);byte[] bytes = IoUtils.toByteArray(inputStream);data.add(new CellData(bytes));} catch (Exception e) {//图片异常展示的图片data.add(new CellData(IoUtils.toByteArray(new FileInputStream("D:\\easyexcel\\err.png"))));continue;} finally {if (inputStream != null){inputStream.close();}}}// 这种方式并不能返回一个List,所以只好通过CellData cellData = new CellData(data);将这个list对象塞到返回值CellData对象的data属性中；CellData cellData = new CellData(data);cellData.setType(CellDataTypeEnum.IMAGE);return cellData;}
}

 2.4.图片信息拦截器: 修改图片图片大小位置等等

public class CustomImageModifyHandler implements CellWriteHandler {private List<String> repeats = new ArrayList<>();// 单元格的图片最大张数（每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置）private Integer maxDataSize = 0;@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {//  在 数据转换成功后 不是头就把类型设置成空if (isHead) {return;}//将要插入图片的单元格的type设置为空,下面再填充图片if(cellData.getImageValue()!=null||cellData.getData() instanceof ArrayList){cellData.setType(CellDataTypeEnum.EMPTY);}}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {//  在 单元格写入完毕后 ，自己填充图片if (isHead || CollectionUtils.isEmpty(cellDataList)) {return;}Boolean listFlag = false;ArrayList data = null;Sheet sheet = cell.getSheet();// 此处为ListUrlConverterUtil的返回值if (cellDataList.get(0).getData() instanceof ArrayList){data = (ArrayList) cellDataList.get(0).getData();if (CollectionUtils.isEmpty(data)) {return;}if (data.get(0) instanceof CellData){CellData cellData = (CellData) data.get(0);if (cellData.getImageValue() == null){return;}else {listFlag = true;}}}if (!listFlag && cellDataList.get(0).getImageValue() == null){return;}String key = cell.getRowIndex() + "_" + cell.getColumnIndex();if (repeats.contains(key)){return;}repeats.add(key);if (data.size() > maxDataSize) {maxDataSize = data.size();}//60px的行高大约是900,60px列宽大概是248*8,根据需要调整sheet.getRow(cell.getRowIndex()).setHeight((short)900);sheet.setColumnWidth(cell.getColumnIndex(), (int) (listFlag?21.8*256*maxDataSize:22.8*256));if (listFlag){for (int i = 0; i < data.size(); i++) {CellData cellData= (CellData) data.get(i);if(cellData.getImageValue()==null){continue;}this.insertImage(sheet,cell,cellData.getImageValue(),i);}}else {// cellDataList 是list的原因是 填充的情况下 可能会多个写到一个单元格 但是如果普通写入 一定只有一个this.insertImage(sheet,cell,cellDataList.get(0).getImageValue(),0);}}private void insertImage(Sheet sheet,Cell cell,byte[] pictureData,int i){int picWidth = Units.pixelToEMU(175);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();// 设置图片坐标anchor.setDx1(picWidth*i);anchor.setDx2(picWidth+picWidth*i);anchor.setDy1(0);anchor.setDy2(0);//设置图片位置anchor.setCol1(cell.getColumnIndex());anchor.setCol2(cell.getColumnIndex());anchor.setRow1(cell.getRowIndex());anchor.setRow2(cell.getRowIndex() + 1);// 设置图片可以随着单元格移动anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);drawing.createPicture(anchor, index);}
}

三.测试

public class ImagesExportTest {@Testpublic void test() throws Exception {String filename = "D:\\easyexcel\\easyExcelImages.xlsx";// 图片位置String imagePath = "D:\\easyexcel\\1_1.jpg";// 网络图片URL url = new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN3246625733_1920x1080.jpg&rf=LaDigue_1920x1080.jpg&pid=hp");ArrayList<String> list = new ArrayList<>();ArrayList<URL> urls = new ArrayList<>();ArrayList<MyExcel> excelList = new ArrayList<>();for (int i = 0; i < 3; i++) {list.add(imagePath);if (i==2){//异常String类型图片地址list.add("D:\\easyexcel\\1.jpg");//异常url类型图片地址urls.add(new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN324665733_1920x1080.jpg"));}urls.add(url);excelList.add(new MyExcel().setImageStrings(list).setImageUrls(urls).setName("张三").setAge("12"));}excelList.add(new MyExcel().setImageStrings(list.subList(0, 2)).setImageUrls(urls.subList(0, 1)).setName("赵六").setAge("12"));excelList.add(new MyExcel().setImageStrings(list.subList(0, 1)).setImageUrls(urls.subList(0, 2)).setName("纳兹").setAge("12"));//异常string和url类型图片地址excelList.add(new MyExcel().setImageStrings(Arrays.asList("D:\\easyexcel\\1.jpg")).setImageUrls(Arrays.asList(new URL("https://cn.bing.com/th?id=OHR.TanzaniaBeeEater_ZH-CN324665733_1920x1080.jpg"))).setName("纳兹").setAge("12"));//无图片excelList.add(new MyExcel().setImageStrings(list.subList(0, 0)).setImageUrls(urls.subList(0, 0)).setName("纳兹").setAge("12"));//该文件导出到本地EasyExcel.write(filename, MyExcel.class).registerWriteHandler(new CustomImageModifyHandler()).sheet("多图片").doWrite(excelList);}
}

注意:从浏览器导出改变参数

EasyExcel.write(outputStream, Excel数据实体类.class).registerWriteHandler(new CustomImageModifyHandler()).sheet().doWrite(查询出来并且完成数据转换的Excel数据集合);

---

四.总结

以上就是今天要讲的内容，本文介绍了简单easyExcel的使用,如有疑问欢迎留言讨论!!!