前言

初始化數據是，需要將excel中的數據解析并插入數據庫。
但是某幾列存放的是圖片，這時候怎么辦呢。

主要解決的是：獲取excel中指定行列的圖片字節。

---

一、pom依賴

解析文件的依賴

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId></dependency><dependency><groupId>commons-io</groupId><artifactId>commons-io</artifactId><version>2.11.0</version></dependency>

如果執行接口報錯的話，可能是poi和log4j有沖突，log4j換成下面的依賴

<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-api</artifactId><version>2.17.1</version>
</dependency>
<dependency><groupId>org.apache.logging.log4j</groupId><artifactId>log4j-core</artifactId><version>2.17.1</version>
</dependency>

二、使用步驟

1.示例數據

excel的M、N列是圖片，對應index為：12、13

2.代碼如下（示例）：

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
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.web.bind.annotation.GetMapping;
import org.springframework.web.multipart.MultipartFile;@GetMapping(value = "/uploadPointFile")public List<String> uploadPointFile(@RequestParam(value = "file") MultipartFile file) throws Exception {LinkedList<String> insertSql = new LinkedList<>();String OG_POINT = "INSERT INTO `table_one`(`id`, `city`, `district`, `street`, `community`, `name`, `address`, `deleted`, `gaoDe_log`, `gaoDe_lat`, `label`, `contact_user`, `contact_mobile`,  `remark`,`link_path`, `create_community`, `created_time`, `updated_time`) VALUES ('%s',  '%s', '%s', '%s', '%s', '%s','%s', 0, '%s', '%s', '%s', '%s', '%s', '%s','%s', '510121',now(),now());";String OGP_PICTURE = "INSERT INTO `table_two`(`id`, `point_id`, `picture_url`, `deleted`, `created_time`, `updated_time`) VALUES ('%s', '%s', 'https://xxxxx/x/%s', 0,now(),now());";InputStream inputStream = file.getInputStream();Workbook workbook = new XSSFWorkbook(inputStream);// 獲取第一個工作表Sheet sheet = workbook.getSheetAt(0);for (Row row : sheet) {if (row.getRowNum() == 0) {continue; // 跳過表頭}String uuid = UUID.randomUUID().toString().replaceAll("-", "");// 獲取前兩列內容作為圖片名String ogSql = String.format(OG_POINT, uuid, row.getCell(0).toString().substring(0, 4), row.getCell(0).toString(), row.getCell(1).toString(), row.getCell(2).toString(), row.getCell(3).toString(), row.getCell(4).toString(), row.getCell(5).toString(), row.getCell(6).toString(), row.getCell(7).toString(), row.getCell(8).toString(), row.getCell(9).toString(), row.getCell(10).toString(), row.getCell(11).toString());//打印point 的sqlinsertSql.add(ogSql);// 獲取工作表中的繪圖對象XSSFDrawing drawing = (XSSFDrawing) sheet.getDrawingPatriarch();if (drawing == null) {continue; // 如果沒有繪圖對象則跳過}// 獲取所有形狀List<XSSFShape> shapes = drawing.getShapes();for (XSSFShape shape : shapes) {if (shape instanceof XSSFPicture) {XSSFPicture picture = (XSSFPicture) shape;XSSFClientAnchor anchor = (XSSFClientAnchor) picture.getAnchor();// 檢查圖片是否在目標單元格中if (row.getRowNum() == anchor.getRow1() && (anchor.getCol1() == 12 || anchor.getCol1() == 13)) {// 獲取圖片數據XSSFPictureData pictureData = picture.getPictureData();byte[] pictureBytes = pictureData.getData();String fileExtension = pictureData.suggestFileExtension();//調用文件上傳接口String pictureUrl = "xxxxxxx";String pictureUuid = UUID.randomUUID().toString().replaceAll("-", "");String pictureSql = String.format(OGP_PICTURE, pictureUuid, uuid, pictureUrl);insertSql.add(pictureSql);}}}}return insertSql;}

總結

沒有總結，歪比巴卜