基于PIO把带有数据和图片的excel表导入到数据库中（图片存储到服务器中，可访问图片路径存入在数据库中）
文章目录
前言
创作来之不易可点一颗小心心
一、工具类getExcelMap说明？
以下工具类都是基于PIO原生态实现的，其中包含了.xls和.xlsx的导入，也涉及到了在excel解析图片。
二、使用步骤
1.引入库
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
2.方法实现：importFile
public void importFile(MultipartFile file) {
Map<String, String> fieldMap=new HashMap<>();
fieldMap.put("id", "id");
fieldMap.put("姓名", "name");
fieldMap.put("年龄", "age");
fieldMap.put("性别", "sex");
fieldMap.put("电话", "phone");
fieldMap.put("人物肖像", "photo");
fieldMap.put("身份证信息", "message");
//图片存储路径
String filePath="D://cs/picture/web/";
//图片访问地址
String webPath="http://localhost:8080/cccc/pic/";
List<Map<String,Object>> list= getExcelMap(file,fieldMap, filePath,webPath);
}
3.读excel中的数据和图片
excel截图：
代码如下：
/**
* fieldMap：字段信息
* filePath 为图片的存储路径:/aaa/bbb/。
* webPath 为图片的访问路径：http://localhost:8080/aaa/bbb/
*
*/
public List<Map<String,Object>> getExcelMap(MultipartFile multipartFile，Map<String, String> fieldMap,String filePath,String webPath ) {
List<Map<String,Object>> list=new ArrayList<>();
//如果excel表中有日期的话，则把日期规范化
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try (InputStream inputStream = multipartFile.getInputStream()) {
//读取excel
Workbook workbook;
Map<String, PictureData> sheetPictures=null;
Sheet sheet=null;
if (multipartFile.getOriginalFilename().endsWith(".xls")) {
workbook = new HSSFWorkbook(inputStream);
sheet= workbook.getSheetAt(0);
//获取图片
sheetPictures = ExcelUtil.getPictures((HSSFSheet)sheet);
} else {
workbook = new XSSFWorkbook(inputStream);
sheet= workbook.getSheetAt(0);
//获取图片
sheetPictures = ExcelUtil.getPictures((XSSFSheet) sheet);
}
Map<String,String> printImgMap=null;
//把图片存入服务器中，返回图片存储路径 printImgMap
if (sheetPictures.size()!=0){
printImgMap= ExcelUtil.printImg( sheetPictures,filePath,webPath);
}
//获取的firstRowNum其实是=0的，firstCellNum 也是=0
int firstRowNum = sheet.getFirstRowNum();
Row firstRow = sheet.getRow(firstRowNum);
int lastRowNum = sheet.getLastRowNum();
int firstCellNum = firstRow.getFirstCellNum();
int lastCellNum = firstRow.getLastCellNum();
//firstRowNum + 1是因为单元格都是坐标（0，0）开始算的。excel表中的第一行是表头，所以不需要获取，就直接从第二行数据行开始
for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
Row row = sheet.getRow(i);
//空表格就不需要进循环了，根据第一列名称的有无来判断是否是空白行
/**
* formatter.formatCellValue(row.getCell(0))这个方法就相当于:
* Cell cell = row.getCell(0);
* cell.setCellType(CellType.STRING);
* value = cell.getStringCellValue();
*/
DataFormatter formatter = new DataFormatter();
if (StringUtil.isBlank(formatter.formatCellValue(row.getCell(0)))){
break;
}
String value = "";
Map<String,Object> map=new LinkedHashMap<>();
log.info("lastCellNum------>{}",lastCellNum);
for (int j = firstCellNum; j < lastCellNum; j++) {
value = "";
Cell cell = row.getCell(j);
//null != cell避免遇到空指针。使用StringUtil.isNotBlank(cell)的话会报错，原因是如果单元格没有内容的话会报空指针异常。
if(null != cell) {
log.info("第【{}】行，第【{}】列数据:{}", i, j, cell);
//单元格为整数、小数、日期
if (cell.getCellType().equals(CellType.NUMERIC)) {
if (DateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
value = formater.format(d);
} else {
cell.setCellType(CellType.STRING);
value = cell.getStringCellValue();
}
//单元格中有图片存在
}else if(StringUtil.isNotBlank(printImgMap.get(i + "_" +j))){
value=printImgMap.get(i+"_"+j);
} else {
cell.setCellType(CellType.STRING);
value = cell.getStringCellValue();
}
}
//遍历列，获得第一行对应的表头所在列的单元格中的字段中文名称。然后根据字段中文名获取到字段英文名。为key值。
Cell headCell = firstRow.getCell(j);
log.info("headCell[{}]:{}", j, headCell);
String fieldNameCn = headCell.getStringCellValue();
log.info("fieldNameCn:{}", fieldNameCn);
String fieldNameEn = fieldMap.get(fieldNameCn);
log.info("fieldNameEn:{}", fieldNameEn);
map.put(fieldNameEn,value);
log.info("map:{}",map);
}
list.add(map);
}
} catch (IOException e) {
log.error("拼接sql发生异常 [{}]", e);
}
return list;
}
4.ExcelUtil：获取图片和存图片
public class ExcelUtil{
/** 支持单个图片
* 获取Excel2003图片
* @param sheet 当前sheet对象
* @return Map key:图片单元格索引（0_11）String，value:图片流PictureData
* @throws IOException
*/
public static Map<String, PictureData> getPictures(HSSFSheet sheet) {
Map<String, PictureData> map = new HashMap<>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
PictureData pData = picture.getPictureData();
String key = cAnchor.getRow1() + "_" + cAnchor.getCol1();
map.put(key, pData);
}
}
return map;
}
/**
* 获取图片和位置 (xlsx)
* @param sheet
* @return Map key:图片单元格索引（0_1）String，value:图片流PictureData
* @throws IOException
*/
public static Map<String, PictureData> getPictures (XSSFSheet sheet) throws IOException {
Map<String, PictureData> map = new HashMap<>();
for (POIXMLDocumentPart dr : sheet.getRelations()) {
if (dr instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) dr;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture pic = (XSSFPicture) shape;
XSSFClientAnchor anchor = (XSSFClientAnchor) shape.getAnchor();
CTMarker ctMarker = anchor.getFrom();
String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
map.put(picIndex, pic.getPictureData());
}
}
}
return map;
}
/**
* 图片写出
* @param mapList为图片对象
* @param webPath 为图片的访问路径：http://localhost:8080/aaa/bbb/
* @param filePath 为图片的存储路径:/aaa/bbb/。
* @throws IOException
*/
//图片写出
public static Map printImg(Map<String, PictureData> mapList,String filePath,String webPath) throws IOException {
Map<String, String> map = new HashMap<>();
mapList.forEach((key, pic) -> {
// 文件扩展名
String ext = pic.suggestFileExtension();
// 图片数据
byte[] data = pic.getData();
//图片保存路径 医院名称+微信公众号/医院照片+图片后缀
String ImgName= UUID.randomUUID().toString()+"." + ext;
//写入到对应的文件夹路径下面去
//filePath+imgName=/aaa/bbb/xxxxx.jpg
try (FileOutputStream out = new FileOutputStream(filePath+imgName)) {
out.write(bytes);
} catch (Exception e) {
e.printStackTrace();
}
//map中的value为：http://localhost:8080/aaa/bbb/xxxxx.jpg
map.put(key, webPath+ImgPath);
});
return map;
}
}
}
5.导入报错
有一天，导入未带图片的.xlsx表单，报以下错误：
XSSFSimpleShape cannot be cast to org.apache.poi.xssf.usermodel.XSSFPicture
1.原因是：低版本改成了高版本Excel，解析有问题
解决办法：将【xlsx】另存为【xls】
2.报错显示的是类型转换错误，我明明是用XSSFShape类型转换的，XSSFShapeGroup是那来的？在网上查到XSSFShapeGroup是获取组合形状的。
解决办法：在excel表里面找到组合形状的图形，取消组合
3.原因是：默认导入包含矩形形状表格时出错，代码在获取所有图片的时候把所有Shape强转成XSSFPicture，实际上是XSSFSimpleShape，强制失败
在ExcelUtil：获取图片和存图片的代码14行出现问题。
解决办法：
List<XSSFShape> shapes = drawing.getShapes();
替换成
List<XSSFShape> shapes = drawing.getShapes()
.stream().filter(s->s instanceof XSSFPicture).collect(Collectors.toList());
总结
由于List<Map<String,Object>> getExcelMap最后的返回list存入数据库去的方式是使用jdbc原生态insert语句拼接而成的，过程很复杂，但其核心取excel中的数据和图片已实现
扫一扫
3417
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
