业务需求，需要读取excel数据，数据中有带图片的形式。网上的教程多且杂，很少整合在一起。所以就整理了一份详细的文档。
一、Maven依赖，使用的是apache的poi依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.9</version>
</dependency>
二、读取excell数据
这是我要读取的excell数据。
代码实现
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Created by GRW on 2019/2/13.
*/
@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@SpringBootTest(classes = {App.class})
public class ExcelCSDNTest {
private static Map<PicturePosition, String> pictureMap;
@Test
public void test() {
//初始化图片容器
pictureMap = new HashMap<>();
String filePath = "C:\\Users\\GRW\\Desktop\\excel.xlsx";
String fileFormat = "xlsx";
Workbook workbook;
try {
if (ExcelFormatEnum.XLS.getValue().equals(fileFormat)) {
workbook = new HSSFWorkbook(new FileInputStream(filePath));
} else if (ExcelFormatEnum.XLSX.getValue().equals(fileFormat)) {
workbook = new XSSFWorkbook(new FileInputStream(filePath));
} else {
return;
}
//读取excel所有图片
if (ExcelFormatEnum.XLS.getValue().equals(fileFormat)) {
getPicturesXLS(workbook);
} else {
getPicturesXLSX(workbook);
}
List<Shop> shopList = new ArrayList<>();
Sheet sheet = workbook.getSheetAt(0);
int rows = sheet.getLastRowNum(); //读取行数
//行遍历 跳过表头直接从数据开始读取
for (int i = 1; i < sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
Shop shop = new Shop();
shop.setPosition(getCellValue(row.getCell(1)));
shop.setTitle(getCellValue(row.getCell(2)));
shop.setImageUrl(pictureMap.get(PicturePosition.newInstance(i, 3)));
shop.setCount(Integer.parseInt(getCellValue(row.getCell(4))));
shop.setUnit(getCellValue(row.getCell(5)));
shop.setSize(getCellValue(row.getCell(6)));
shop.setMaterial(getCellValue(row.getCell(7)));
shop.setRemark(getCellValue(row.getCell(8)));
shopList.add(shop);
}
System.out.println(shopList.toString());
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* cell数据格式转换
* @param cell
* @return
*/
private static String getCellValue(Cell cell){
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
//如果为时间格式的内容
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
return sdf.format(HSSFDateUtil.getJavaDate(cell.
getNumericCellValue())).toString();
} else {
return new DecimalFormat("0").format(cell.getNumericCellValue());
}
case HSSFCell.CELL_TYPE_STRING: // 字符串
return cell.getStringCellValue();
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
return cell.getBooleanCellValue() + "";
case HSSFCell.CELL_TYPE_FORMULA: // 公式
return cell.getCellFormula() + "";
case HSSFCell.CELL_TYPE_BLANK: // 空值
return "";
case HSSFCell.CELL_TYPE_ERROR: // 故障
return null;
default:
return null;
}
}
/**
* 获取Excel2003的图片
*
* @param workbook
*/
private static void getPicturesXLS(Workbook workbook) {
List<HSSFPictureData> pictures = (List<HSSFPictureData>) workbook.getAllPictures();
HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
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);
PicturePosition picturePosition = PicturePosition.newInstance(anchor.getRow1(), anchor.getCol1());
pictureMap.put(picturePosition, printImg(picData));
}
}
}
}
/**
* 获取Excel2007的图片
*
* @param workbook
*/
private static void getPicturesXLSX(Workbook workbook) {
XSSFSheet xssfSheet = (XSSFSheet) workbook.getSheetAt(0);
for (POIXMLDocumentPart dr : xssfSheet.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();
PicturePosition picturePosition = PicturePosition.newInstance(ctMarker.getRow(), ctMarker.getCol());
pictureMap.put(picturePosition, printImg(pic.getPictureData()));
}
}
}
}
/**
* 保存图片并返回存储地址
*
* @param pic
* @return
*/
public static String printImg(PictureData pic) {
try {
String ext = pic.suggestFileExtension(); //图片格式
String filePath = "D:\\pic\\pic" + UUID.randomUUID().toString() + "." + ext;
byte[] data = pic.getData();
FileOutputStream out = new FileOutputStream(filePath);
out.write(data);
out.close();
return filePath;
} catch (Exception e) {
return "";
}
}
/**
* 图片位置
* 行row 列 col
*/
@Data
public static class PicturePosition {
private int row;
private int col;
public static PicturePosition newInstance(int row, int col) {
PicturePosition picturePosition = new PicturePosition();
picturePosition.setRow(row);
picturePosition.setCol(col);
return picturePosition;
}
}
/**
* 枚举excel格式
*/
public enum ExcelFormatEnum {
XLS(0, "xls"),
XLSX(1, "xlsx");
private Integer key;
private String value;
ExcelFormatEnum(Integer key, String value) {
this.key = key;
this.value = value;
}
public Integer getKey() {
return key;
}
public String getValue() {
return value;
}
}
/**
* 商品对象
*/
@Data
public class Shop {
private String position;
private String title;
private String imageUrl;
private Integer count;
private String unit;
private String size;
private String material;
private String remark;
}
}
🐞标题：Spring boot 读取Excel并且带图片
👽作者：ruige
🐾地址：https://jjdhhc.com/articles/2020/11/07/1604721856885.html
🙏感恩：谢谢您的打赏与支持!中间图片是我的微信公众号,扫码关注哦!
