java读取excel表格(含有多个sheet)
//方式1,用本地的文件
public List<List<List<String>>> uploadExcel(String filePath) throws FileNotFoundException { File file2 = new File(filePath); InputStream inputStream = new FileInputStream(file2); List<List<List<String>>> list = null; try { XSSFWorkbook wb = new XSSFWorkbook(inputStream); list = getData(wb, 0, null);//3个参数。 } catch (Exception e) { log.info("文件读取失败"); } finally { if (inputStream != null) { try { inputStream.close(); } catch (Exception e) { log.info("文件流关闭失败"); } inputStream = null; } } return list; // return new Result(list);//读取到的list(表(行(列))),直接返回,也可以按需求做别的操作。 }
//方式2,上传的文件MultipartFile
public List<List<List<String>>> uploadExcel(MultipartFile myFile) { String fileName = myFile.getOriginalFilename(); assert fileName != null; if (!fileName.endsWith(".xlsx")) { return new Result(ResultCode.FILE_WRONG); } List<List<List<String>>> list = null; InputStream inputStream = null; try { inputStream = myFile.getInputStream(); XSSFWorkbook wb = new XSSFWorkbook(inputStream); list = getData(wb, 0, null);//3个参数。 } catch (Exception e) { log.info("文件读取失败"); } finally { if (inputStream != null) { try { inputStream.close(); } catch (Exception e) { log.info("文件流关闭失败"); } inputStream = null; } } return list;//读取到的list(表(行(列))),直接返回,也可以按需求做别的操作。 }
public static List<List<List<String>>> getData(XSSFWorkbook wb, Integer ignoreRows, Integer sheetNum) { if (sheetNum == null) { sheetNum = wb.getNumberOfSheets(); } List<List<List<String>>> lists = new ArrayList<>(); //for循环:取前N个表,下标从0开始 for (int i = 0; i < sheetNum; i++) { XSSFSheet sheetI = wb.getSheetAt(i); List<List<String>> list = new ArrayList<>(); int cellSize = sheetI.getRow(0).getLastCellNum();//列数 //第N+1行开始,可以通过传参,从第N+1行开始取 for (int rowIndex = ignoreRows; rowIndex <= sheetI.getLastRowNum(); rowIndex++) { XSSFRow row = sheetI.getRow(rowIndex); if (row == null) { continue; } List<String> rowList = new ArrayList<>(); //在每行中的每一列,从下标0开始,一直取到所有 for (int a = 0; a < cellSize; a++) { String cellValue = getCellValue(row.getCell(a)); rowList.add(cellValue); } list.add(rowList); } lists.add(list); } return lists; }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步