POI将本地图片插入EXCEL单元格中

矫情吗；* 2023-10-05 16:39 70阅读 0赞

本机这个位置的图片插入到指定的EXCEL中  
![在这里插入图片描述][20201229100223768.png]

public static void main(String[] args) {
        
            try {
        
                export();
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    
        private static void export() throws Exception{
        
            SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMdd hhmmss");
            HSSFWorkbook workbook = new HSSFWorkbook();  // 创建一个excel
            HSSFSheet sheet = workbook.createSheet("测试"); // 为excel创建一个sheet页
            List<Map<String,Object>> list=new ArrayList<>();
            List<Object> fieldList=new ArrayList<>();
            fieldList.add("xh");
            fieldList.add("picurl");
            for (int i = 0; i < 5; i++) {
        
                Map<String,Object> map=new HashMap<>();
                map.put("xh",i);
                map.put("picurl","C:\\Users\\BS\\Pictures\\Camera Roll\\00"+(i+1)+".jpg");
                list.add(map);
            }
            System.out.println(list);
            Integer maxcols=2;
            Integer rowIndex=0;
            for (int i = 0; i < list.size(); i++) {
        
                HSSFRow row = sheet.createRow(rowIndex);
                row.setHeight((short)(70*15));//设置行高
                Map<String,Object> vo = list.get(i); // 得到对象.
                for (int j = 0; j < maxcols; j++) {
        
                    HSSFCell cell = row.createCell(j);
                    // 取得类型,并根据对象类型设置值.
                    if (vo.get(fieldList.get(j)) == null) {
        
                        cell.setCellValue("");// 如果数据是null,就填入空格.
                    }else {
        
                        Class<?> fieldType=vo.get(fieldList.get(j)).getClass();
                        if (String.class == fieldType) {
        
                            if ((String)vo.get(fieldList.get(j))!=null&&("picurl".equals(fieldList.get(j)))){
        
                                HSSFPatriarch patriarch=sheet.createDrawingPatriarch();
                                setpic(patriarch,workbook,i,1,(String)vo.get(fieldList.get(j)));
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cell.setCellValue("");
                            }else {
        
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cell.setCellValue((String) vo.get(fieldList.get(j)));// 如果数据存在就填入,不存在填入空格.
                            }
                        } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
        
                            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                            Integer value=(Integer) vo.get(fieldList.get(j));
                            cell.setCellValue(value);
                        } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
        
                            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue((Long)vo.get(fieldList.get(j)));
                        } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
        
                            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue((Float)vo.get(fieldList.get(j)));
                        } else if (Double.TYPE == fieldType||Double.class == fieldType) {
        
                            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                            Double value=(Double) vo.get(fieldList.get(j));
                            cell.setCellValue(value);
                        } else if ((BigDecimal.class == fieldType)) {
        
                            Double value = ((BigDecimal) vo.get(fieldList.get(j))).doubleValue();
                            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(value);
                        } else if (Character.TYPE == fieldType) {
        
    
                        }
                    }
                }
                rowIndex++;
            }
            String folderPath = "D:\\Excel";
            //创建上传文件目录
            File folder = new File(folderPath);
            //如果文件夹不存在创建对应的文件夹
            if (!folder.exists()) {
        
                folder.mkdirs();
            }
            //设置文件名
            String fileName = sdf1.format(new Date()) + "测试" + ".xlsx";
            String savePath = folderPath + File.separator + fileName;
    
            OutputStream fileOut = new FileOutputStream(savePath);
            workbook.write(fileOut);
            fileOut.close();
            //返回文件保存全路径
        }
        //rowNum excel的第几行 colNum第几列
        private  static void setpic(HSSFPatriarch patriarch,HSSFWorkbook workbook,int rowNum,int colNum,String picurl) throws Exception{
        
            // 先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
    
    
            InputStream file=new FileInputStream(picurl);
    
            //将图片读到BufferedImage
           
            BufferedImage bufferImg = ImageIO.read(file);
            ImageIO.write(bufferImg, "jpg", byteArrayOut);
    
            //图片一导出到单元格B2中
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short)colNum , rowNum, (short)(colNum+1), rowNum+1);
            // 插入图片
            patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        }

运行后的结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MDMwNDY1_size_16_color_FFFFFF_t_70]

[20201229100223768.png]: https://img-blog.csdnimg.cn/20201229100223768.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MDMwNDY1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20201229101240145.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4MDMwNDY1,size_16,color_FFFFFF,t_70