Java使用easyExcel導(dǎo)出數(shù)據(jù)及單元格多張圖片

更新時間：2023年05月23日 11:18:02 作者：小菜雞℡

除了平時簡單的數(shù)據(jù)導(dǎo)出需求外,我們也經(jīng)常會遇到一些有固定格式或者模板要求的數(shù)據(jù)導(dǎo)出,下面這篇文章主要給大家介紹了關(guān)于Java使用easyExcel導(dǎo)出數(shù)據(jù)及單元格多張圖片的相關(guān)資料,需要的朋友可以參考下

此文章要干嘛?

使用easyExcel針對于普通數(shù)據(jù), url類型和String類型單張,多張,,無異常圖片導(dǎo)出Excel文件

官網(wǎng)針對圖片導(dǎo)出提供五種方式, 如有其他需求參考官方文檔

官網(wǎng): 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實(shí)體類：

MyUrlConverterUtil : Url類型圖片轉(zhuǎn)換工具

MyStringImageConverterUtil: String類型圖片轉(zhuǎn)換工具

@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類型多圖片導(dǎo)出到單元格轉(zhuǎn)換工具類

public class MyUrlConverterUtil implements Converter<List<URL>> {
    @Override
    public Class supportJavaTypeKey() {
        return List.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        /**
         *這里記得枚舉類型為IMAGE
         */
        return CellDataTypeEnum.IMAGE;
    }
    @Override
    public List convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }
    @Override
    public CellData convertToExcelData(List<URL> value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 這里進(jìn)行對數(shù)據(jù)實(shí)體類URL集合處理
        List<CellData> data = new ArrayList<>();
        // for 循環(huán)一次讀取
        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類型多圖片導(dǎo)出到單元格轉(zhuǎn)換工具類

public class MyStringImageConverterUtil implements Converter<List<String>> {
    @Override
    public Class supportJavaTypeKey() {
        return List.class;
    }
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        /**
         *這里記得枚舉類型為IMAGE
         */
        return CellDataTypeEnum.IMAGE;
    }
    @Override
    public List convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        //從excel中讀數(shù)據(jù)時被EasyExcel調(diào)用
        String stringValue = cellData.getStringValue();
        //用json轉(zhuǎn)換工具將excel單元格中數(shù)據(jù)轉(zhuǎn)換為java List<String>對象
        List<String> list = Convert.toList(String.class,stringValue);
        return list;
    }
    @Override
    public CellData convertToExcelData(List<String> stringUrl, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 這里進(jìn)行對數(shù)據(jù)實(shí)體類URL集合處理
        List<CellData> data = new ArrayList<>();
        // for 循環(huán)一次讀取
        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<>();
    // 單元格的圖片最大張數(shù)（每列的單元格圖片張數(shù)不確定，單元格寬度需按照張數(shù)最多的長度來設(shè)置）
    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) {
        //  在 數(shù)據(jù)轉(zhuǎn)換成功后 不是頭就把類型設(shè)置成空
        if (isHead) {
            return;
        }
        //將要插入圖片的單元格的type設(shè)置為空,下面再填充圖片
        if(cellData.getImageValue()!=null||cellData.getData() instanceof ArrayList){
            cellData.setType(CellDataTypeEnum.EMPTY);
        }
    }
    @Override
    public 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,根據(jù)需要調(diào)整
        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();
        // 設(shè)置圖片坐標(biāo)
        anchor.setDx1(picWidth*i);
        anchor.setDx2(picWidth+picWidth*i);
        anchor.setDy1(0);
        anchor.setDy2(0);
        //設(shè)置圖片位置
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex());
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex() + 1);
        // 設(shè)置圖片可以隨著單元格移動
        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";
        // 網(wǎng)絡(luò)圖片
        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"));
        //該文件導(dǎo)出到本地
        EasyExcel.write(filename, MyExcel.class)
                .registerWriteHandler(new CustomImageModifyHandler())
                .sheet("多圖片")
                .doWrite(excelList);
    }
}

注意:從瀏覽器導(dǎo)出改變參數(shù)

EasyExcel.write(outputStream, Excel數(shù)據(jù)實(shí)體類.class)
    .registerWriteHandler(new CustomImageModifyHandler()).sheet().doWrite(查詢出來并且完成數(shù)據(jù)轉(zhuǎn)換的Excel數(shù)據(jù)集合);

四.總結(jié)

到此這篇關(guān)于Java使用easyExcel導(dǎo)出數(shù)據(jù)及單元格多張圖片的文章就介紹到這了,更多相關(guān)easyExcel導(dǎo)出單元格多張圖片內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: