1、前端
//导入
export function excelImport(data){
var param = new FormData()
Object.keys(data).forEach(key => {
param.append(key, data[key])
})
return request({
url: 'XX/XXXX/excelImport',
method: 'post',
data: param,
headers: {
'Content-Type': 'multipart/form-data'
},
})
}
2、pom依赖
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>RELEASE</version>
</dependency>
3、controller
@PostMapping("/excelImport")
@ApiOperation("excel导入包含图片")
public Result<JSONObject> excelImport(@RequestParam("file") MultipartFile file) {
JSONObject object = service.excelImport(file);
return R.ok(object);
}
4、serviceImp
@Override
@Transactional
public JSONObject excelImport(MultipartFile file) {
List<实体类A> listAdd = new ArrayList<>();
List<Map<String, Object>> listImg = new ArrayList<>();
String msg = "异常需要处理,导入数据失败";
String filePath = getFilePath(file);
try {
ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
//读图片--开始
Map<String, PictureData> maplist=null;
BufferedInputStream inputStream = new BufferedInputStream(file.getInputStream());
Workbook wb = null;
// 判断用07还是03的方法获取图片
if (filePath.endsWith("xls")) {
wb = new HSSFWorkbook(inputStream);
Sheet sheet = wb.getSheetAt(0);
maplist = getPictures03((HSSFSheet) sheet);
} else if (filePath.endsWith("xlsx")) {
wb = new XSSFWorkbook(inputStream);
Sheet sheet = wb.getSheetAt(0);
maplist = getPictures07((XSSFSheet) sheet);
}else{
msg = "Excel文件格式不支持";
}
//读图片--结束
List<Map<String, Object>> list = reader.read(0, 1, Integer.MAX_VALUE);
for(int i= 1; i <= list.size(); i++){
MultipartFile file2 = null;
if(maplist.get(i+"_7")!=null&&!"".equals(maplist.get(i+"_7"))){
PictureData pictureData=maplist.get(i+"_7");
String replace = i+"."+pictureData.getMimeType().replace("image/", "");
byte[] data = pictureData.getData();
InputStream inputStream2 = new ByteArrayInputStream(data);
//给MultipartFile的getOriginalFilename赋值replace
file2 = new MockMultipartFile("File",replace,"text/plain",inputStream2);
}
Map<String, Object> map = list.get(i - 1);
实体类A a = new 实体类A();
String code = String.valueOf(map.get("编号(*)"));
if (code == null || code.equals("")) {
msg = "号不能为空";
throw new CrmException(1001, "编号不能为空");
}
String time = "";
Object time1 = map.get("时间");
//判断数据类型是Date还是String
if(time1 instanceof String){
alarmTime = (String)time1;
}else{
Date date = (Date)time1;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
time = simpleDateFormat.format(date);
}
boolean legalDate = isLegalDate(time);
if(!legalDate){
msg = "时间格式不正确,应为:yyyy-MM-dd HH:mm:ss";
throw new CrmException(1001, "时间格式不正确,应为:yyyy-MM-dd HH:mm:ss");
}
Object alarmType1 = map.get("类型");
String alarmType = "";
if (alarmType1 == null || alarmType1.equals("")) {
throw new CrmException(1001, "类型不能为空");
}else{
//判断数据类型是否Long
if(alarmType1 instanceof Long){
Long a = (Long)alarmType1;
alarmType = String.valueOf(a);
}else{
alarmType = (String)map.get("类型");
}
}
String batchId = UUID.randomUUID().toString();
if(file2 != null){
Map mapimg = new HashMap();
mapimg.put("file",file2);
mapimg.put("batchId",batchId);
listImg.add(mapimg);
}
SimpleDateFormat formatter = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");
Date date = formatter.parse(time);
safetyBlockAlarm.time(date);
listAdd.add(a);
}
}catch (Exception e){
throw new CrmException(1001, msg);
}
// AtomicReference<Integer> num = new AtomicReference<>(0);
// ExcelUtil.readBySax(filePath, 0, (int sheetIndex, int rowIndex, List<Object> rowList) -> {
// System.out.println(rowIndex);
// System.out.println(rowList);
//
// if (rowIndex >= 1) {
// //1、编号
// String code = rowList.get(0).toString().trim();
// if (code != null && !code .equals("")) {
//
// } else {
// // 编号为null 或者空字符串抛出异常
// throw new CrmException(1001, "编号不能为空");
// }
// }
// });
for(Map map : listImg){
MultipartFile fileImg = (MultipartFile)map.get("file");
String batchId = (String)map.get("batchId");
//上传图片
}
saveBatch(listAdd);
//Excel删除
FileUtil.del(filePath);
JSONObject result = new JSONObject().fluentPut("totalSize", listAdd.size()).fluentPut("errSize", 0);
return result;
}
/**
* 获取03图片和位置 (xls) clerk
* @param sheet
* @return
* @throws IOException
*/
public static Map<String, PictureData> getPictures03(HSSFSheet sheet) throws IOException {
Map<String, PictureData> map = new HashMap<String, PictureData>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor();
HSSFPictureData pdata = picture.getPictureData();
String key = cAnchor.getRow1() + "_" + cAnchor.getCol1(); // 行号-列号
map.put(key, pdata);
}
}
return map;
}
/**
* 获取Excel2007图片
* @param sheet 当前sheet对象
* @return Map key:图片单元格索引(0_1)String,value:图片流PictureData
*/
public static Map<String, PictureData> getPictures07(XSSFSheet sheet) {
int sheetNum = 7;
Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
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 = pic.getPreferredSize();
CTMarker ctMarker = anchor.getFrom();
String picIndex = ctMarker.getRow() + "_" + ctMarker.getCol();
if(sheetNum==ctMarker.getCol() ){
sheetIndexPicMap.put(picIndex, pic.getPictureData());
}
}
}
}
return sheetIndexPicMap;
}
//Excel下载
private String getFilePath(MultipartFile file) {
String dirPath = FileUtil.getTmpDirPath();
try {
InputStream inputStream = file.getInputStream();
File fromStream = FileUtil.writeFromStream(inputStream, dirPath + "/" + IdUtil.simpleUUID() + file.getOriginalFilename());
return fromStream.getAbsolutePath();
} catch (IOException e) {
throw new CrmException(SystemCodeEnum.SYSTEM_UPLOAD_FILE_ERROR);
}
}
//判断 时间格式
private static boolean isLegalDate(String sDate) {
DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
try {
Date date = formatter.parse(sDate);
return sDate.equals(formatter.format(date));
} catch (Exception e) {
return false;
}
}