import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletRequest; import java.io.IOException; import java.io.InputStream; import java.io.FileNotFoundException; import java.util.ArrayList; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Map.Entry; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; @PostMapping("/import") public String importXXXXXXWords( @RequestParam("file") MultipartFile uploadFile, HttpServletRequest request){ String columns[] = {"order_no","escrow_trade_no","for_ali_num"};//对应excel表头 List<Map<String,String>> list = readExcel(uploadFile,columns); //遍历解析出来的list (这里按自己需要转实体类) for (Map<String,String> map : list) { for (Entry<String,String> entry : map.entrySet()) { System.out.print(entry.getKey()+":"+entry.getValue()+","); } System.out.println(); } return "更新有误"; } //读取excel public static List<Map<String,String>> readExcel(MultipartFile uploadFile,String columns[]){ Workbook wb = null; List<Map<String,String>> list = new ArrayList<>(); Sheet sheet = null; Row row = null; String cellData = null; /**读取excel文件*/ String filePath = uploadFile.getOriginalFilename(); if(filePath==null){ return null; } String extString = filePath.substring(filePath.lastIndexOf(".")); try { InputStream is = uploadFile.getInputStream(); //两种表格 if(".xls".equals(extString)){ wb = new HSSFWorkbook(is); }else if(".xlsx".equals(extString)){ wb = new XSSFWorkbook(is); }else{ wb = null; } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } /**处理文件数据*/ if(wb != null){ //用来存放表中数据 list = new ArrayList<Map<String,String>>(); //获取第一个sheet sheet = wb.getSheetAt(0); //获取最大行数 int rownum = sheet.getPhysicalNumberOfRows(); //获取第一行 row = sheet.getRow(0); //获取最大列数 int colnum = row.getPhysicalNumberOfCells(); for (int i = 1; i<rownum; i++) { Map<String,String> map = new LinkedHashMap<String,String>(); row = sheet.getRow(i);//第几行 if(row !=null){ for (int j=0;j<colnum;j++){ //第几列 cellData = (String) getCellFormatValue(row.getCell(j)); map.put(columns[j], cellData); } }else{ break; } list.add(map); } } return list; } public static Object getCellFormatValue(Cell cell){ Object cellValue = null; if(cell!=null){ //判断cell类型 switch(cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC:{ cellValue = String.valueOf(cell.getNumericCellValue()); break; } case Cell.CELL_TYPE_FORMULA:{ //判断cell是否为日期格式 if(DateUtil.isCellDateFormatted(cell)){ //转换为日期格式YYYY-mm-dd cellValue = cell.getDateCellValue(); }else{ //数字 cellValue = String.valueOf(cell.getNumericCellValue()); } break; } case Cell.CELL_TYPE_STRING:{ cellValue = cell.getRichStringCellValue().getString(); break; } default: cellValue = ""; } }else{ cellValue = ""; } return cellValue; }
参考了网友一些代码 做出部分调整 适合公共调用
