@RequestMapping("/addempExcel")
public ETJSONResponse addEmps(MultipartFile pic, HttpServletRequest request, HttpServletResponse respon) throws IOException, InterruptedException, ParseException {
ETJSONResponse response = new ETJSONResponse();
ServletContext application = request.getSession().getServletContext();
String name = pic.getOriginalFilename();
String path = application.getRealPath("/wd/" + name);
String fEXT= FilenameUtils.getExtension(pic.getOriginalFilename());
FileInputStream fis =null;
Workbook workbook = null;
Sheet sheet =null;
List<String>paths=null;
try
{
fis = (FileInputStream) pic.getInputStream();
}
catch(Exception e)
{
e.printStackTrace();
}
workbook=new XSSFWorkbook(fis);
Map<String, PictureData> maplist=null;
sheet = workbook.getSheetAt(0);
maplist=getPictures2((XSSFSheet) sheet);
try {
paths=printImg(maplist,pic,request,respon);
} catch (Exception e) {
e.printStackTrace();
}
Row rowHead = sheet.getRow(0);
int totalRowNum = sheet.getLastRowNum();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
List<Emp> emps=new ArrayList<>();
for(int i = 1 ; i <= totalRowNum ; i++) {
Row row = sheet.getRow(i);
Emp emp=new Emp();
emp.setEname(row.getCell(1).getStringCellValue());
emp.setJob(row.getCell(2).getStringCellValue());
emp.setHiredate(sdf.parse(row.getCell(3).getStringCellValue()));
emp.setSal(Double.parseDouble(row.getCell(4).getNumericCellValue()+""));
emp.setDeptno((int)row.getCell(5).getNumericCellValue());
emps.add(emp);
}
for (Map.Entry<String, PictureData> entry : maplist.entrySet()) {
System.out.println("Key = " + entry.getKey() + ", Value = " + entry.getValue());
}
workbook.close();
if (fis != null){
fis.close();
}
Map<String,Object>map=new HashMap<>();
map.put("emps",emps);
map.put("paths",paths);
response.setMsg(map);
return response;
}
private List<String>printImg(Map<String, PictureData> maplist,MultipartFile multipartFile,HttpServletRequest request, HttpServletResponse respon) throws IOException {
Object key[] = maplist.keySet().toArray();
List<String>paths=new ArrayList<>();
String filePath = "";
ServletContext application = request.getSession().getServletContext();
for (int i = 0; i < maplist.size(); i++) {
PictureData pic = maplist.get(key[i]);
String picName = key[i].toString();
String ext = pic.suggestFileExtension();
byte[] data = pic.getData();
filePath=application.getRealPath("/files/" + picName + ".jpg");
System.out.println(filePath);
paths.add(filePath);
multipartFile.transferTo(new File(filePath));
}
return paths;
}
private Map<String, PictureData> getPictures2(XSSFSheet sheet) {
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();
map.put(key, picture.getPictureData());
}
}
}
return map;
}
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>