POI导入excel读取excel中图片_poi读取excel中的图片-优快云博客

本文链接：https://blog.youkuaiyun.com/lianyunpeng/article/details/136864730

本文介绍了如何在SpringMVC项目中，通过ApachePOI库操作Excel文件，包括读取图片并与单元格绑定，以及处理上传的MultipartFile。文章展示了如何解析图片数据并保存到本地，同时提到了扩展保存数据库功能的可能性但未实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.首先excel图片需要跟单元格绑定

2.pom文件

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>${poi.version}</version>
</dependency>

3.编写测试方法进行导入

import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.List;
import java.util.UUID;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Shape;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;


/**
 * 资产清单Controller
 * 
 * @author hhxx
 * @date 2024-03-13
 */
@RestController
@RequestMapping("/expand/property")
public class BuPropertyListController extends BaseController
{
    @PreAuthorize("@ss.hasPermi('expand:property:import')")
    @PostMapping("/importData")
    public AjaxResult importData(MultipartFile file, boolean updateSupport) throws Exception {
    	// 获取文件流
    	InputStream inputStream = file.getInputStream();
    	// 创建一个工作薄对象
    	Workbook workbook = new XSSFWorkbook(inputStream);
    	// 获取第一个工作表对象
    	String sheetName = workbook.getSheetName(0);
    	Sheet sheet = workbook.getSheet(sheetName);
    	// 获取excel所有的图片对象
    	List<XSSFShape> shapes = ((XSSFDrawing) sheet.getDrawingPatriarch()).getShapes();
        for (Shape shape : shapes) {
            if (shape instanceof XSSFPicture) {
                XSSFPicture picture = (XSSFPicture) shape;
                ClientAnchor anchor2 = picture.getClientAnchor();
                int row1 = anchor2.getRow1();
                short col1 = anchor2.getCol1();
                // 获取图片单元格坐标。例R3、A4等
//                String cellRef = new CellReference(row1, col1).formatAsString();
                // 根据图片位置获取对应的第一行标题单元格
                Row row = sheet.getRow(row1-row1);
                Cell cell3 = row.getCell(col1);
                // 获取标题单元格文本内容
                String stringCellValue = cell3.getStringCellValue();
                System.out.println("图片所在的单元格标题: " + stringCellValue);
                // 获取图片数据
                XSSFPictureData pictureData = picture.getPictureData();
                byte[] bytes = pictureData.getData();
                // 图片保存至本地
                FileOutputStream out = new FileOutputStream("D:\\img\\pic\\image_" + UUID.randomUUID().toString() + "." + pictureData.suggestFileExtension());
                out.write(bytes);
                out.close();
            }
        }
        workbook.close();
        return AjaxResult.success();
    }
}

4.展示效果

5.根据此内容可以扩展保存数据库功能，在此暂不实现。