共同探討，向各位大佬學習走向CEO，迎娶白富美

<!--springboot核心依賴--> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.4.RELEASE</version> </parent> <dependencies> <!--springboot--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!--excel--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.11</version> </dependency> <!--mybatis-plus--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.0</version> </dependency> <!--mysql--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> </dependencies>POIUtils工具類

public class POIUtils { private final static String xls = 'xls'; private final static String xlsx = 'xlsx'; private final static String DATE_FORMAT = 'yyyy/MM/dd'; /** * 讀入excel文件，解析后返回 * @param file * @throws IOException */ public static List<String> readExcel(MultipartFile file) throws IOException { //檢查文件 checkFile(file); //獲得Workbook工作薄對象 Workbook workbook = getWorkBook(file); //創建返回對象，把每行中的值作為一個數組，所有行作為一個集合返回// List<String[]> list = new ArrayList<String[]>(); List<String> list = new ArrayList<>(); if(workbook != null){ for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){//獲得當前sheet工作表Sheet sheet = workbook.getSheetAt(sheetNum);if(sheet == null){ continue;}//獲得當前sheet的開始行int firstRowNum = sheet.getFirstRowNum();//獲得當前sheet的結束行int lastRowNum = sheet.getLastRowNum();//循環所有行for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){ //獲得當前行 Row row = sheet.getRow(rowNum); if(row == null){ continue; } //獲得當前行的開始列 int firstCellNum = row.getFirstCellNum(); //獲得當前行的列數 int lastCellNum = row.getPhysicalNumberOfCells();// String[] cells = new String[row.getPhysicalNumberOfCells()]; //循環當前行 for(int cellNum = firstCellNum; cellNum <= lastCellNum;cellNum++){ Cell cell = row.getCell(cellNum);// cells[cellNum] = getCellValue(cell); String cellValue = getCellValue(cell); list.add(cellValue); }} } workbook.close(); } return list; } //校驗文件是否合法 public static void checkFile(MultipartFile file) throws IOException{ //判斷文件是否存在 if(null == file){ throw new FileNotFoundException('文件不存在！'); } //獲得文件名 String fileName = file.getOriginalFilename(); //判斷文件是否是excel文件 if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){ throw new IOException(fileName + '不是excel文件'); } } public static Workbook getWorkBook(MultipartFile file) { //獲得文件名 String fileName = file.getOriginalFilename(); //創建Workbook工作薄對象，表示整個excel Workbook workbook = null; try { //獲取excel文件的io流 InputStream is = file.getInputStream(); //根據文件后綴名不同(xls和xlsx)獲得不同的Workbook實現類對象 if(fileName.endsWith(xls)){//2003workbook = new HSSFWorkbook(is); }else if(fileName.endsWith(xlsx)){//2007workbook = new XSSFWorkbook(is); } } catch (IOException e) { e.printStackTrace(); } return workbook; } public static String getCellValue(Cell cell){ String cellValue = ''; if(cell == null){ return cellValue; } //如果當前單元格內容為日期類型，需要特殊處理 String dataFormatString = cell.getCellStyle().getDataFormatString(); if(dataFormatString.equals('m/d/yy')){ cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue()); return cellValue; } //把數字當成String來讀，避免出現1讀成1.0的情況 if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ cell.setCellType(Cell.CELL_TYPE_STRING); } //判斷數據的類型 switch (cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC: //數字cellValue = String.valueOf(cell.getNumericCellValue());break; case Cell.CELL_TYPE_STRING: //字符串cellValue = String.valueOf(cell.getStringCellValue());break; case Cell.CELL_TYPE_BOOLEAN: //BooleancellValue = String.valueOf(cell.getBooleanCellValue());break; case Cell.CELL_TYPE_FORMULA: //公式cellValue = String.valueOf(cell.getCellFormula());break; case Cell.CELL_TYPE_BLANK: //空值cellValue = '';break; case Cell.CELL_TYPE_ERROR: //故障cellValue = '非法字符';break; default:cellValue = '未知類型';break; } return cellValue; }}controller測試

@RestController@RequestMapping('/excel')public class ExcelController { @PostMapping('/look') public void look(@RequestParam('excelFile') MultipartFile excelFile){ try { List<String> list = POIUtils.readExcel(excelFile);// list.removeIf(Objects::isNull);去掉null值 //去掉空字符串 Iterator<String> iterator = list.iterator(); while (iterator.hasNext()){if (iterator.next() == ''){ iterator.remove();} } //遍歷list,查看數據 for (String s : list) {System.out.println(s); } //創建map對象或者pojo類存入所需的數據， Map<String,Object> map = new HashMap<>(); map.put('plan',list.get(0)); map.put('er',list.get(2)); map.put('date',list.get(4)); System.out.println(map); } catch (IOException e) { e.printStackTrace(); } }}

訪問測試

控制臺輸出

2020年度審核計劃編制人：張三時間：2020/10/10審核依據：1233211234567審核目的：12345555556984552368內審組長：張器內審副組長：漲吧審核分組：第一組張四李有里爾三點第二組張五王柳王琦士大夫{date=2020/10/10, plan=2020年度審核計劃, er=張三}

在excel表格中，日期或者時間在java讀取時會以數值的類型讀取，得到的是10-十月-2020，所以在excel表格日期類型的數據用自定義類型，并設置格式。（親測）

到此這篇關于SpringBoot讀取excel表格的示例代碼的文章就介紹到這了,更多相關SpringBoot讀取excel表格內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！