项目中有一需求:需要读取excel中的信息,带图片,需要将每一行的图片,做对应,整理方法如下:
主要逻辑:
1,获取excel对象,
2,读取excel中所有的图片流,将图片所处的位置和图片流存在map中getPictures()方法
4,根据图片所处位置,获取图片流,上传图片,并将图片的所处位置和上传路径存在map中printImg()方法
5,读取excel中的每一行文字,当读取到图片列时,根据key获取第四步的map中的value,将路径以及文字信息存入list中;readData()方法
- controller层
@PostMapping("/uploadFile")
public List<Map<String, String>> uploadMonitorItem(MultipartFile upfile, String providerId) throws Exception {
InputStream in = null;
List<Map<String, String>> listob = null;
in = upfile.getInputStream();
listob = ExcelUtil.readExcelByInputStream(in, providerId);
return listob;
}
2.service层
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.qskj.framework.config.ERPConfig;
public class ExcelUtil {
/**
* 读取 Excel文件内容
*
* @param inputstream 文件输入流
* @return
* @throws Exception
*/
public static List<Map<String, String>> readExcelByInputStream(InputStream inputstream, String providerId)
throws Exception {
// 结果集
List<Map<String, String>> list = new ArrayList<Map<String, String>>();
XSSFWorkbook wb = new XSSFWorkbook(inputstream);
String filePath = ERPConfig.getProfile() + "/" + "pic/" + providerId + "/";//图片保存路径
final XSSFSheet sheet = wb.getSheetAt(0);// 得到Excel工作表对象
Map<String, PictureData> map = ExcelImgUtil.getPictures(sheet);//获取图片和位置
Map<String, String> pathMap = ExcelImgUtil.printImg(map, filePath);//写入图片,并返回图片路径,key:图片坐标,value:图片路径
list = ExcelImgUtil.readData(sheet, pathMap,providerId);
return list;
}
}
3,工具类
package com.qskj.project.util;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
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.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import com.qskj.common.utils.security.Md5Utils;
import com.qskj.framework.config.ERPConfig;
public class ExcelImgUtil {
private static int counter = 0;
/**
* 获取图片和位置 (xlsx)
*
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, PictureData> getPictures(XSSFSheet sheet) throws IOException {
Map<String, PictureData> map = new HashMap<String, PictureData>();
List<POIXMLDocumentPart> list = sheet.getRelations();
for (POIXMLDocumentPart part : list) {
if (part instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) part;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
CTMarker marker = anchor.getFrom();
String key = marker.getRow() + "-" + marker.getCol();
byte[] data = picture.getPictureData().getData();
map.put(key, picture.getPictureData());
}
}
}
return map;
}
public static Map<String, String> printImg(Map<String, PictureData> sheetList, String path) throws IOException {
Map<String, String> pathMap = new HashMap<String, String>();
Object[] key = sheetList.keySet().toArray();
File f = new File(path);
if (!f.exists()) {
f.mkdirs(); // 创建目录
}
for (int i = 0; i < sheetList.size(); i++) {
// 获取图片流
PictureData pic = sheetList.get(key[i]);
// 获取图片索引
String picName = key[i].toString();
// 获取图片格式
String ext = pic.suggestFileExtension();
String fileName = encodingFilename(picName);
byte[] data = pic.getData();
// 图片保存路径
String imgPath = path + fileName + "." + ext;
FileOutputStream out = new FileOutputStream(imgPath);
imgPath = imgPath.substring(ERPConfig.getProfile().length(), imgPath.length());// 截取图片路径
pathMap.put(picName, imgPath);
out.write(data);
out.close();
}
return pathMap;
}
private static final String encodingFilename(String fileName) {
fileName = fileName.replace("_", " ");
fileName = Md5Utils.hash(fileName + System.nanoTime() + counter++);
return fileName;
}
/**
* 读取excel文字
*
* Excel 07版本以上
*
* @param sheet
*/
public static List<Map<String, String>> readData(XSSFSheet sheet, Map<String, String> map,String providerId) {
List<Map<String, String>> newList = new ArrayList<Map<String, String>>();// 单行数据
try {
int rowNum = sheet.getLastRowNum() + 1;
for (int i = 1; i < rowNum; i++) {// 从第三行开始读取数据,第一行是备注,第二行是标头
Row row = sheet.getRow(i);// 得到Excel工作表的行
if (row != null) {
int col = row.getPhysicalNumberOfCells();
// 单行数据
Map<String, String> mapRes = new HashMap<String, String>();// 每格数据
for (int j = 0; j < col; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
// arrayString.add("");
} else if (cell.getCellType() == 0) {// 当时数字时的处理
mapRes.put(getMapKey(j), new Double(cell.getNumericCellValue()).toString());
} else {// 如果EXCEL表格中的数据类型为字符串型
mapRes.put(getMapKey(j), cell.getStringCellValue().trim());
}
}
if (i != 1) {// 不是标头列时,添加图片路径
String path = map.get(i + "-9");
mapRes.put(getMapKey(9), path);
}
mapRes.put("providerId", providerId);
newList.add(mapRes);
}
}
} catch (Exception e) {
}
return newList;
}
public static String getMapKey(int num) {
String res = "";
switch (num) {
case 0:// 分类
res = "secondDictCode";
break;
case 1:// 产品名称
res = "productName";
break;
case 2:// 规格型号
res = "specification";
break;
case 3:// 计量单位
res = "unit";
break;
case 4:// 风格
res = "style";
break;
case 5:// 颜色
res = "color";
break;
case 6:// 采购单价
res = "purchasePrice";
break;
case 7:// 材质
res = "material";
break;
case 8:// 备注
res = "remark";
break;
case 9:// 产品图片
res = "picture";
break;
default:
break;
}
return res;
}
}
- postman请求测试:/poi/uploadFile?providerId=5
- 最终效果
[
{
"unit": "计量单位",
"color": "颜色",
"material": "材质",
"providerId": "5",
"specification": "规格型号",
"style": "风格",
"remark": "备注",
"purchasePrice": "采购单价",
"secondDictCode": "分类",
"productName": "产品名称",
"picture": "产品图片"
},
{
"unit": "平方米",
"color": "白色,绿色,红色",
"material": "木质1",
"providerId": "5",
"specification": "600*900",
"style": "美式,北欧,中式",
"remark": "大萨达十大1",
"purchasePrice": "66.8",
"secondDictCode": "家具",
"productName": "床",
"picture": "/pic/5/b3e42b2d741cc16bff4fefaa6bb61d87.jpeg"
},
{
"providerId": "5",
"picture": null
}
]