Java实现读取excel中的数据及图片

一、背景

需要完成excel数据的读取与落库

二、实现

Java读取excel数据：指定某一行，读取整列的数据

   /*
 如果是xls格式，使用HSSFWorkbook，HSSFSheet，HSSFRow来进行相关操作
 如果是xlsx格式，使用XSSFWorkbook，XSSFSheet，XSSFRow来进行相关操作,目前只支持xlsx
 */
    public static HashMap readExcelData(String filename, Integer row, Integer column,Integer sheet) throws IOException {
        //用于存储Exce读取数据
        HashMap<Integer,String> hashMap=new HashMap();
        log.info("开始读取excel数据");
        //读取excel数据
        File file = ResourceUtils.getFile(filename);
        InputStream inputStream = new FileInputStream(file);
        XSSFWorkbook xssfWorkbook=new XSSFWorkbook(inputStream);

        //获取sheet表格，及读取单元格内容
        XSSFSheet xssfSheet=xssfWorkbook.getSheetAt(sheet);

        //先将获取的单元格设置为String类型，下面使用getStringCellValue获取单元格内容
        Integer cellIndex = 0;
        while(cellIndex<=column){

            //第一列为空时直接,赋值为空
            if (xssfSheet.getRow(row)==null || xssfSheet.getRow(row).getCell(cellIndex)==null){
                hashMap.put(cellIndex,"");
                cellIndex++;
                continue;
            }

            xssfSheet.getRow(row).getCell(cellIndex).setCellType(CellType.STRING);
            String stringValue=xssfSheet.getRow(row).getCell(cellIndex).getStringCellValue();

            hashMap.put(cellIndex,stringValue);
            cellIndex++;
        }
        log.info("readExcelData:{}",hashMap.toString());
        return hashMap;
    }

Java读取excel图片：获取整个excel的图片，可以按照指定的行和列来定位读取图片

    /**
     * 获取Excel中的图片
     * @param xssfSheet
     * @return
     */
    public static Map<String, XSSFPictureData> getPictures(XSSFSheet xssfSheet){

        Map<String,XSSFPictureData> map=new HashMap<>();
        List<XSSFShape> list=xssfSheet.getDrawingPatriarch().getShapes();

        for (XSSFShape shape:list){

            XSSFPicture picture = (XSSFPicture) shape;
            XSSFClientAnchor xssfClientAnchor=(XSSFClientAnchor) picture.getAnchor();
            XSSFPictureData pdata = picture.getPictureData();
            // 行号-列号
            String key = xssfClientAnchor.getRow1() + "-" + xssfClientAnchor.getCol1();
            log.info("key数据:{}",key);
            map.put(key, pdata);

        }

        return map;
    }

实际调用测试

   @Test
    public void test() throws IOException, UnirestException {
        String filename="classpath:file/org.xlsx";

        File file = ResourceUtils.getFile(filename);
        InputStream inputStream = new FileInputStream(file);
        XSSFWorkbook xssfWorkbook=new XSSFWorkbook(inputStream);
        Map<String, XSSFPictureData> map=getPictures(xssfWorkbook.getSheetAt(0));
        String mapKey="3-15";//指定行和列
        XSSFPictureData xssfPictureData= map.get(mapKey);
        byte[] data =xssfPictureData.getData();
        FileOutputStream out = new FileOutputStream("/Users/test12.png");
        out.write(data);
        out.close();
   }
}

 jar包版本

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>