Java导出导入Excel

 导入

    @PostMapping("/upload")
    public Result upload(MultipartFile file, HttpServletRequest request) {
        logger.info("导入 upload");
        Result result = uploadService.upload(file, request);
        logger.info("导入 结果:{}", JSON.toJSONString(result));
        return result;
    }

 public Result upload(MultipartFile file, HttpServletRequest request) {
        Result result = new Result(true);
        try {
            if (file != null) {
                EasyExcel.read(file.getInputStream(), SortLineExcel.class, new AbstractAnalysisEventListener<SortLineExcel>() {
                    @Override
                    public Result<String> readData(List<SortLineExcel> list) {
                        logger.info("导入 readData");
                        if (CollectionUtils.isEmpty(list)) {
                            return result;
                        }
                        List<SortLineDTO> sortLineDTOList = new ArrayList<>();
                        list = list.stream().filter(s -> StringUtils.isNumeric((s.getChannelId()))).collect(Collectors.toList());
                        for (SortLineExcel sortLineExcel : list) {
                            SortLineDTO sortLineDTO = new SortLineDTO();
                            BeanUtils.copyProperties(sortLineExcel, sortLineDTO);
                            sortLineDTO.setChannelId(Integer.valueOf(sortLineExcel.getChannelId()));
                            sortLineDTO.setUnique();
                            sortLineDTOList.add(sortLineDTO);
                        }
                        sortLineDTOList = sortLineDTOList.stream().collect(Collectors.collectingAndThen(Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(SortLineDTO::getNoIdType))), ArrayList::new));
                        List<List<SortLineDTO>> partition = Lists.partition(sortLineDTOList, 500);
                        for (List<SortLineDTO> dtoList : partition) {
                            List<String> noIdTypeList = dtoList.stream().map(SortLineDTO::getNoIdType).collect(Collectors.toList());
                            SortLineDTO query = new SortLineDTO();
                            query.setNoIdTypeList(noIdTypeList);
                            List<SortLineInfo> sortLineList = sortLineDao.findSortLineList(query);
                            if (CollectionUtils.isNotEmpty(sortLineList)) {
                                List<String> repeat = sortLineList.stream().map(SortLineInfo::getNoIdType).collect(Collectors.toList());
                                dtoList = dtoList.stream().filter(s -> !repeat.contains(s.getNoIdType())).collect(Collectors.toList());
                            }
                            if (CollectionUtils.isNotEmpty(dtoList)) {
                                sortLineDao.batchSaveSortLine(dtoList);
                            }
                        }
                        return result;
                    }
                }).sheet().doRead();
            }
        } catch (Exception e) {
            result.setStatus(false);
            result.setMsg("导入失败");
            logger.error("导入失败", e);
        }
        return result;
    }

 导出带图片

 /**
     * 设备导出(带图片)
     */
    @RequestMapping(value = "/deviceInfo/exportImage")
    public void exportImage(@RequestBody SortLineDTO sortLineDTO, HttpServletResponse response) throws Exception {
        logger.info("设备信息导出 导出功能(带图片)入参:{}", JSON.toJSONString(sortLineDTO));
        List<SortLineDeviceInfoExcel> list = sortLineService.exportImageDeviceInfo(sortLineDTO);
        logger.info("查出记录数:{}", list.size());
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("设备信息", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), SortLineDeviceInfoExcel.class).registerWriteHandler(new CustomImageModifyHandler()).sheet("数据").doWrite(list);
    }

package com.jd.logistics.vision.bean.excel.handle;


import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * @Description: 图片信息修改拦截器
 * @Author GreenArrow
 * @Date: 2021/7/7 16:05
 * @Version 1.0
 */
public class CustomImageModifyHandler implements CellWriteHandler {
    private List<String> repeats = new ArrayList<String>();

    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    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 (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);
        // 默认要导出的图片大小为60*60px,60px的行高大约是900,60px列宽大概是248*8
        sheet.getRow(cell.getRowIndex()).setHeight((short) 900);
        sheet.setColumnWidth(cell.getColumnIndex(), listFlag ? 240 * 8 * data.size() : 240 * 8);
        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(), 10);
        }

    }


    private void insertImage(Sheet sheet, Cell cell, byte[] pictureData, int i) {
        int picWidth = Units.pixelToEMU(60);
        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);
    }

}

 // 前端导出关键代码
    channelExport() {
      const loading = this.$loading({
          lock: true,
          text: 'Loading',
          spinner: 'el-icon-loading',
          background: 'rgba(0, 0, 0, 0.7)'
      });
      setChannelExport(this.channelExportParam)
        .then(res => {
          const blob = new Blob([res], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
          const url = window.URL.createObjectURL(blob);
          const link = document.createElement("a");
          link.style.display = "none";
          link.href = url;
          link.setAttribute("download", "设备信息详情.xlsx");
          document.body.appendChild(link);
          link.click();
          document.body.removeChild(link);
          window.URL.revokeObjectURL(url);
          loading.close()
        }).catch(()=>{
          loading.close()
        })
    }