共同探討，向各位大佬學(xué)習(xí)走向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工作薄對(duì)象 Workbook workbook = getWorkBook(file); //創(chuàng)建返回對(duì)象，把每行中的值作為一個(gè)數(shù)組，所有行作為一個(gè)集合返回// List<String[]> list = new ArrayList<String[]>(); List<String> list = new ArrayList<>(); if(workbook != null){ for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){//獲得當(dāng)前sheet工作表Sheet sheet = workbook.getSheetAt(sheetNum);if(sheet == null){ continue;}//獲得當(dāng)前sheet的開(kāi)始行int firstRowNum = sheet.getFirstRowNum();//獲得當(dāng)前sheet的結(jié)束行int lastRowNum = sheet.getLastRowNum();//循環(huán)所有行for(int rowNum = firstRowNum;rowNum <= lastRowNum;rowNum++){ //獲得當(dāng)前行 Row row = sheet.getRow(rowNum); if(row == null){ continue; } //獲得當(dāng)前行的開(kāi)始列 int firstCellNum = row.getFirstCellNum(); //獲得當(dāng)前行的列數(shù) int lastCellNum = row.getPhysicalNumberOfCells();// String[] cells = new String[row.getPhysicalNumberOfCells()]; //循環(huán)當(dāng)前行 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; } //校驗(yàn)文件是否合法 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(); //創(chuàng)建Workbook工作薄對(duì)象，表示整個(gè)excel Workbook workbook = null; try { //獲取excel文件的io流 InputStream is = file.getInputStream(); //根據(jù)文件后綴名不同(xls和xlsx)獲得不同的Workbook實(shí)現(xiàn)類對(duì)象 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; } //如果當(dāng)前單元格內(nèi)容為日期類型，需要特殊處理 String dataFormatString = cell.getCellStyle().getDataFormatString(); if(dataFormatString.equals('m/d/yy')){ cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue()); return cellValue; } //把數(shù)字當(dāng)成String來(lái)讀，避免出現(xiàn)1讀成1.0的情況 if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ cell.setCellType(Cell.CELL_TYPE_STRING); } //判斷數(shù)據(jù)的類型 switch (cell.getCellType()){ case Cell.CELL_TYPE_NUMERIC: //數(shù)字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測(cè)試

@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,查看數(shù)據(jù) for (String s : list) {System.out.println(s); } //創(chuàng)建map對(duì)象或者pojo類存入所需的數(shù)據(jù)， 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(); } }}

訪問(wèn)測(cè)試

控制臺(tái)輸出

2020年度審核計(jì)劃編制人：張三時(shí)間：2020/10/10審核依據(jù)：1233211234567審核目的：12345555556984552368內(nèi)審組長(zhǎng)：張器內(nèi)審副組長(zhǎng)：漲吧審核分組：第一組張四李有里爾三點(diǎn)第二組張五王柳王琦士大夫{date=2020/10/10, plan=2020年度審核計(jì)劃, er=張三}

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

到此這篇關(guān)于SpringBoot讀取excel表格的示例代碼的文章就介紹到這了,更多相關(guān)SpringBoot讀取excel表格內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！