此文章要干嘛?

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

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

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

文章目录

• 此文章要干嘛?
• 展示
• 一、效果展示
• 二、使用步骤
  • 1.引包
  • 2.代码
    • 2.1实体类：
    • 2.2.String类型多图片导出到单元格转换工具类
    • 2.3.String类型多图片导出到单元格转换工具类 
    •  2.4.图片信息拦截器: 修改图片图片大小位置等等
• 三、测试
• 四、总结

一、效果展示?

二、使用步骤

1.引包

该案例使用easyexcel 2.2.7

        com.alibaba    easyexcel    2.2.7    

该案例引入的包

        com.alibabaeasyexcel2.2.7org.projectlomboklombok1.18.20junitjunit4.13.2cn.hutoolhutool-all5.4.0javax.servletjavax.servlet-api4.0.1

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 imageUrls;    @ExcelProperty(value = "String图片", converter = MyStringImageConverterUtil.class)    private List imageStrings;}

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

public class MyUrlConverterUtil implements Converter> {    @Override    public Class supportJavaTypeKey() {        return List.class;    }    @Override    public CellDataTypeEnum supportExcelTypeKey() {                return CellDataTypeEnum.IMAGE;    }    @Override    public List convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {        return null;    }    @Override    public CellData convertToExcelData(List value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {        // 这里进行对数据实体类URL集合处理        List 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> {    @Override    public Class supportJavaTypeKey() {        return List.class;    }    @Override    public CellDataTypeEnum supportExcelTypeKey() {                return CellDataTypeEnum.IMAGE;    }    @Override    public List convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {        //从excel中读数据时被EasyExcel调用        String stringValue = cellData.getStringValue();        //用json转换工具将excel单元格中数据转换为java List对象        List list = Convert.toList(String.class,stringValue);        return list;    }    @Override    public CellData convertToExcelData(List stringUrl, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {        // 这里进行对数据实体类URL集合处理        List 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 repeats = new ArrayList<>();    // 单元格的图片最大张数（每列的单元格图片张数不确定，单元格宽度需按照张数最多的长度来设置）    private Integer maxDataSize = 0;    @Override    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {    }    @Override    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {    }    @Override    public 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);        }    }    @Override    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List 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 {    @Test    public 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 list = new ArrayList<>();        ArrayList urls = new ArrayList<>();        ArrayList 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的使用,如有疑问欢迎留言讨论!!!

来源地址：https://blog.csdn.net/m0_47520320/article/details/129406746