SpringBoot读取excel表格的示例代码

共同探讨，向各位大佬学习走向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表格内容请搜索好吧啦网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好吧啦网！