[移动开发]Java解析Excel 获取文本和图片信息

1.引入jar

package org.apache.poi.hssf.usermodel;
package org.apache.poi.ooxml;
package org.apache.poi.ss.usermodel;

2.主要方法

 public static void getExcelText(String fileName, InputStream inputStreaml) throws Exception {
        FileTextVo vo = new FileTextVo();
        StringBuffer sb = new StringBuffer();
        Workbook wb = WorkbookFactory.create(inputStream);
        List<PictureData> pictureDataList = new ArrayList<>();
        for (int index = 0; index < wb.getNumberOfSheets(); index++) {
            //3.得到Excel工作表对象
            Sheet sheet = wb.getSheetAt(index);
            Map<String, PictureData> sheetIndexPicMap;
            if (fileName.toLowerCase().endsWith(".xls")) {
                sheetIndexPicMap = getSheetPictrues03(index, (HSSFSheet) sheet, (HSSFWorkbook) wb);
            } else {
                sheetIndexPicMap = getSheetPictrues07(index, (XSSFSheet) sheet, (XSSFWorkbook) wb);
            }
            if (!ObjectUtils.isEmpty(sheetIndexPicMap)) {
                for (PictureData photo : sheetIndexPicMap.values()) {
                    pictureDataList.add(photo);
                }
            }
            //总行数
            int trLength = sheet.getLastRowNum();
            //4.得到Excel工作表的行
            Row row = sheet.getRow(0);
            if (ObjectUtils.isEmpty(row)) {
                break;
            }
            //总列数
            int tdLength = row.getLastCellNum();
            Row rowtmp1 = sheet.getRow(1);
            if (rowtmp1 != null) {
                if (rowtmp1.getLastCellNum() > tdLength) {
                    tdLength = rowtmp1.getLastCellNum();
                }
            }
            Row rowtmp2 = sheet.getRow(1);
            if (rowtmp2 != null) {
                if (rowtmp2.getLastCellNum() > tdLength) {
                    tdLength = rowtmp2.getLastCellNum();
                }
            }
            //5.得到Excel工作表指定行的单元格
            for (int i = 0; i <= trLength; i++) {
                //得到Excel工作表的行
                Row row1 = sheet.getRow(i);
                if (row1 != null) {
                    for (int j = 0; j <= tdLength; j++) {
                        //得到Excel工作表指定行的单元格
                        Cell cell1 = row1.getCell(j);
                        //获得每一列中的值
                        if (cell1 != null) {
                            sb.append(cell1);
                        }
                    }
                }
            }
        }
        //图片识别
        if (!ObjectUtils.isEmpty(pictureDataList)) {
            for (PictureData photo : pictureDataList) {
                InputStream sbs = new ByteArrayInputStream(photo.getData());
            }
        }
        vo.setText(sb.toString());
        vo.setTotalPage(wb.getNumberOfSheets()); //将标签数作为总页数
    }


    //从03格式excel中获取图片
    public static Map<String, PictureData> getSheetPictrues03(int sheetNum,
                                                              HSSFSheet sheet, HSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + String.valueOf(anchor.getRow1()) + "_"
                            + String.valueOf(anchor.getCol1());
                    sheetIndexPicMap.put(picIndex, picData);
                }
            }
            return sheetIndexPicMap;
        } else {
            return null;
        }
    }

    //07格式excel获取图片。
    public static Map<String, PictureData> getSheetPictrues07(int sheetNum,
                                                              XSSFSheet sheet, XSSFWorkbook workbook) {
        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;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    String picIndex = String.valueOf(sheetNum) + "_"
                            + ctMarker.getRow() + "_" + ctMarker.getCol();
//                    pic.getPictureData().getData();
                    sheetIndexPicMap.put(picIndex, pic.getPictureData());
                }
            }
        }
        return sheetIndexPicMap;
    }

《新程序员》：云原生和全面数字化实践

50位技术专家共同创作，文字、视频、音频交互阅读