java poi方式读取Excel的图片

爱打篮球的秃头程序员

已于 2023-02-17 18:01:36 修改

阅读量3.8k

点赞数

文章标签： java excel 开发语言

于 2022-11-09 11:07:36 首次发布

本文链接：https://blog.csdn.net/h1403635700/article/details/127764442

版权

POM

<!-- poi -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.0</version>
</dependency>
<dependency>
    <groupId>javax.servlet.jsp</groupId>
    <artifactId>javax.servlet.jsp-api</artifactId>
    <version>2.3.3</version>
    <scope>provided</scope>
</dependency>

读取每一行每一列的信息

Map<String, Map<Integer, Map<Integer, String>>> sheetsMap = this.readExcelImageToMap(file);
Set<String> keys = sheetsMap.keySet();
List<String> sheetsNames = new ArrayList<>();
// 获取所有的key == 页名称
for (String sheetsName : keys) {
    sheetsNames.add(sheetsName);
}
// 遍历所有的列
for (int i = 0; i < sheetsMap.size(); i++) {
    // 根据页名称获取页
    String sheetsName = sheetsNames.get(i);
    Map<Integer, Map<Integer, String>> rows = sheetsMap.get(sheetsName);
    if (rows.size() > 0) {
        // 根据所有的行遍历
        for (int rowIdx = 1; rowIdx < rows.size(); rowIdx++) {
            // 遍历当前行的所有列
            Map<Integer, String> columnsTitel = rows.get(0); // 第一行的标题
            Map<Integer, String> columns = rows.get(rowIdx);
            OrderImportVO orderImportVO = new OrderImportVO();
            String offProperty = "";
            String offProductId = "";
            for (int colIdx = 0; colIdx < columnsTitel.size(); colIdx++) {
                System.out.println(columns.get(colIdx));
                String colValue = columns.get(colIdx);
                if ("图片".equals(columnsTitel.get(colIdx))) {
                    String imageUrl = StringUtils.isEmpty(columns.get(colIdx)) ? "" : colValue;
                    orderImportVO.setImage(imageUrl);
                    if (StringUtils.isEmpty(imageUrl)) {
                        throw new ServiceException("图片不能为空！");
                    }
                }
            }
        }
    }
}

    /**
     * 读取上传带图片文件转成Map
     *
     * @param file
     * @return
     * @throws IOException
     */
    private Map<String, Map<Integer, Map<Integer, String>>> readExcelImageToMap(MultipartFile file) {
        // 声明所有页的集合
        Workbook wb = readExcel(file); // 读取上传excel
        if (wb == null) {
            return Collections.emptyMap();
        }
        // 获取总页数
        int pageSize = wb.getNumberOfSheets();

        // excel 里边的sheet 有多个时 限制只取第一个。
        if (pageSize > 1) {
            pageSize = 1;
        }
        Map<String, Map<Integer, Map<Integer, String>>> mapSheet = new HashMap<>();

        for (int i = 0; i < pageSize; i++) {
            // 声明当前页的行和列
            Map<Integer, Map<Integer, String>> map = new HashMap<>();
            // 获取当前页
            Sheet sheet = wb.getSheetAt(i);

            String sheetName = sheet.getSheetName();
            // log.info("当前sheet名称:" + sheetName);
            int rowSize = sheet.getPhysicalNumberOfRows(); // 获取不为空的总行数
            // 遍历每一行
            for (int rowNum = 0; rowNum < rowSize; rowNum++) {
                Row row = sheet.getRow(rowNum);
                //int columnSize = row.getPhysicalNumberOfCells(); // 获取不为空的列个数
                int columnSize = row.getLastCellNum(); // 获取最后一个不为空的列是第几个
                // 声明当前列
                Map<Integer, String> columnMap = new HashMap<>();
                // 遍历一行中每列值
                for (int cellNum = 0; cellNum < columnSize; cellNum++) {
                    System.out.println(row.getCell(cellNum));
                    Cell cell=row.getCell(cellNum);
                    if(cell!=null){
                        cell.setCellType(CellType.STRING);
                        String value=cell.toString();
                        columnMap.put(cellNum, value);
                    }
//                    String value = (String) getCellValue(row.getCell(cellNum));
                    // 添加当前列的内容 cellNum代表第几列 value是内容

                }
                // 添加当前行的内容 rowNum 代表第几行 value是列的内容 意思是第几行第几列的内容
                map.put(rowNum, columnMap);
            }

            // 声明当前页图片的集合
            Map<String, PictureData> sheetImageMap = null;
            // 获取图片
            try {
                //2003版本的excel，用.xls结尾
                sheetImageMap = getPicturesHSS((HSSFSheet) sheet);
            } catch (Exception ex) {
                log.error(ex.getMessage());
                try {
                    //2007版本的excel，用.xlsx结尾
                    sheetImageMap = getPicturesXSS((XSSFSheet) sheet);
                } catch (Exception e) {
                    log.error(ex.getMessage());
                }
            }
            //解析图片并上传到服务器 并设置该字段的值为字符串类型添加到map中 进行数据库上传
            Object key[] = sheetImageMap.keySet().toArray();
            for (int p = 0; p < sheetImageMap.size(); p++) {
                PictureData pic = sheetImageMap.get(key[p]);
                String picName = key[p].toString();
                String ext = pic.suggestFileExtension();
                byte[] picData = pic.getData();
                try {
                    // 上传图片到服务器并返回访问路径
                    String imageUrl = uploadPicture(picData, ext);
                    if (org.apache.commons.lang3.StringUtils.isNotEmpty(imageUrl)) {
                        //按（行-列）规则解析key 并根据key 设置 某一行的某一列的 图片链接
                        String[] split = picName.split("-");
                        Integer rowIndex = Integer.parseInt(split[0].toString()), columnIndex = Integer.parseInt(split[1].toString());
                        //根据行下标 获取所有的列
                        Map<Integer, String> columns = map.get(rowIndex);
                        //根据列下标 设置图片链接值
                        columns.put(columnIndex, imageUrl);
                    }
                } catch (Exception e) {
                    log.error(e.getMessage());
                }
            }
            // 添加当前页的所有内容
            mapSheet.put(sheetName, map);
        }
        return mapSheet;
    }

/**
 * 获取图片和位置 (xls)
 *
 * @param sheet
 * @return
 * @throws IOException
 */
public static Map<String, PictureData> getPicturesHSS(HSSFSheet sheet) {
    Map<String, PictureData> map = new HashMap<String, PictureData>();
    List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
    for (HSSFShape shape : list) {
        if (shape instanceof HSSFPicture) {
            HSSFPicture picture = (HSSFPicture) shape;
            HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
            PictureData pdata = picture.getPictureData();
            String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); // 行号-列号
            map.put(key, pdata);
        }
    }
    return map;
}

/**
 * 获取图片和位置 (xlsx)
 *
 * @param sheet
 * @return
 * @throws IOException
 */
private static Map<String, PictureData> getPicturesXSS(XSSFSheet sheet) {
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
    for (POIXMLDocumentPart dr : sheet.getRelations()) {
        if (dr instanceof XSSFDrawing) {
            XSSFDrawing drawing = (XSSFDrawing) dr;
            List<XSSFShape> shapes = drawing.getShapes();
            for (XSSFShape shape : shapes) {
                XSSFPicture pic = (XSSFPicture) shape;
                //解决图片空指针报错问题   2021-12-27
                XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
                //XSSFClientAnchor anchor = pic.getPreferredSize();
                CTMarker marker = anchor.getFrom();
                String key = marker.getRow() + "-" + marker.getCol(); // 行号-列号
                sheetIndexPicMap.put(key, pic.getPictureData());
            }
        }
    }
    return sheetIndexPicMap;
}

/**
 * 读取excel
 *
 * @param file
 * @return
 */
private static Workbook readExcel(MultipartFile file) {
    Workbook wb = null;
    ZipSecureFile.setMinInflateRatio(0);
    if (file == null) {
        return null;
    }
    String filename = file.getOriginalFilename();
    InputStream is = null;

    try {
        is = file.getInputStream();
        //2003版本的excel，用.xls结尾
        wb = new HSSFWorkbook(is);//得到工作簿
    } catch (Exception ex) {
        log.error(ex.getMessage());
        try {
            //2007版本的excel，用.xlsx结尾
            is = file.getInputStream();
            wb = new XSSFWorkbook(is);//得到工作簿
        } catch (IOException e) {
            log.error(ex.getMessage());
        }
    }

    return wb;
}

/**
 * 将图片上传，返回图片存储路径
 *
 * @param pictureData
 * @param ext
 * @return
 * @throws IOException
 */
public String uploadPicture(byte[] pictureData, String ext) throws IOException {
    //SimpleDateFormat ymd = new SimpleDateFormat("yyyyMMdd");
    //SimpleDateFormat ymdHm = new SimpleDateFormat("yyyyMMddHHmm");
    //Date nowDate = new Date();
    //String dateDir = ymd.format(nowDate);// /sysFiles/offlineOrderPicture/ 目录下的日期文件夹名称
    String fileDir = "uploadxlspic/"; // 线下图片上传所在文件夹路径
    InputStream inputStream = new ByteArrayInputStream(pictureData);
    // 获取图片哈希值
    String imageHash = null;
    try {
        imageHash = getMD5Checksum(inputStream);
    } catch (Exception e) {
        e.printStackTrace();
    }
    //String fileName = picIndexName + "_" + imageHash; // 新图片文件名是 excel图片索引+图片哈希
    String newFileName = imageHash + "." + ext; // 文件名（加后缀）
    // 将图片按路径和文件名上传到服务器
    inputStream = new ByteArrayInputStream(pictureData);
    File file = File.createTempFile(fileDir, newFileName);
    OutputStream outputStream = new FileOutputStream(file);
    IOUtils.copy(inputStream, outputStream);
    inputStream.close();
    outputStream.close();
    String savePath = upload(file, newFileName, fileDir);
    file.delete();
    return savePath;
}

public static String getMD5Checksum(InputStream is) throws NoSuchAlgorithmException, IOException {
    byte[] buffer = new byte[1024];
    MessageDigest complete = MessageDigest.getInstance("MD5");

    int numRead;
    do {
        numRead = is.read(buffer);
        if (numRead > 0) {
            complete.update(buffer, 0, numRead);
        }
    } while (numRead != -1);

    if (is != null) {
        is.close();
    }

    byte[] digest = complete.digest();
    String result = "";

    for (int i = 0; i < digest.length; ++i) {
        result = result + Integer.toString((digest[i] & 255) + 256, 16).substring(1);
    }

    return result;
}

@Value("${erp.oss.accessKeyId:}")
private String accessKeyId;
@Value("${erp.oss.accessKeySecret:}")
private String accessKeySecret;
@Value("${erp.oss.endpoint:}")
private String endpoint;
@Value("${erp.oss.bucket:}")
private String bucket;

//OSS存储路径

public String upload(File file, String fileName, String rootPath) {
    LocalDate now = LocalDate.now();
    // 存储路径
    String path = rootPath + now.getYear() + "-" + now.getMonthValue() + "-" + now.getDayOfMonth() + "/" + fileName.replace("+", "_");
    // oss
    OSS client = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
    // 存储文件
    client.putObject(bucket, path, file);

    return "https://" + bucket + "." + endpoint + "/" + path;
}

/**
 * 利用正则表达式判断字符串是否是数字
 * @param str
 * @return
 */
public boolean isNumeric(String str){
    Pattern pattern = Pattern.compile("[0-9]*");
    Matcher isNum = pattern.matcher(str);
    if(!isNum.matches() ){
        return false;
    }
    return true;
}

//处理尺码
public String dealWithSize(String str) {
    int count = 0;
    int lCount=0;
    int sCount=0;
    //for循环遍历数组
    for (int i = 0; i < str.length(); i++) {
        //charAt方法获取数组i的值，和a比较，相等的话count+1
        if (str.charAt(i) == 'X') {
            count++;
        }
        if(count==0){
            break;
        }
        if (str.charAt(i) == 'L') {
            lCount++;
        }
        if (str.charAt(i) == 'S') {
            sCount++;
        }

    }
    String size=str;
    if(count==1&&lCount>=1){
        size="XL";
    }else if(count>1&&lCount>=1){
        size=count+"XL";
    }

    if(count==1&&sCount>=1){
        size="XS";
    }else if(count>1&&sCount>=1){
        size=count+"XS";
    }

    return size;
}