Java Method to Read Excel Files and Convert Data to List of Maps

The author needed to verify that data in an Excel spreadsheet matches data stored in a database and therefore created a Java method to read the Excel file and return its contents for further comparison.

//读取excel文档，除第一行为标题外内容为数字
public static List
>> readExcel(File filepath) throws Exception{
    /*首先判断文件是否存在
     * 在判断文件类型，xls还是xlsx
     */
    if (!filepath.exists()) {
      output("文件不存在！");
      }
    String filename = filepath.toString();//转化为string类型
    String fileType = filename.substring(filename.lastIndexOf(".") + 1, filename.length());//提取文件名后缀
    InputStream is = null;
    Workbook wb = null;
      try {
        is = new FileInputStream(filepath);
          if (fileType.equals("xls")) {
            wb = new HSSFWorkbook(is);
            } else if (fileType.equals("xlsx")) {
              wb = new XSSFWorkbook(is);
              } else {
                output("文件名错误!");
                }
          //新建集合，考虑到要用value值去查询数据库，所以value设置为string类型
          List
>> result = new ArrayList
>>();
          int sheetSize = wb.getNumberOfSheets();//获取表格的个数
          for (int i = 0; i < sheetSize; i++) {//遍历所有表格
            Sheet sheet = wb.getSheetAt(i);
            List
> sheetList = new ArrayList
>();
            List
titles = new ArrayList
();//放置所有的标题
              int rowSize = sheet.getLastRowNum() + 1;//此处getLastRowNum()方法获取的行数从0开始，故要+1
              for (int j = 0; j < rowSize; j++) {//遍历所有行
                Row row = sheet.getRow(j);
                if (row == null) {//略过空行
                  continue;
                  }
                int cellSize = row.getLastCellNum();//获取列数
                if (j == 0) {//第一行是标题行
                    for (int k = 0; k < cellSize; k++) {//添加到标题集合中
                      Cell cell = row.getCell(k);
                      titles.add(cell.toString());
                      }
                    } else {//其他行是数据行，为数字
                      Map
rowMap = new HashMap
();//保存一行的数据
                      for (int k = 0; k < titles.size(); k++) {//遍历保存此行数据
                        Cell cell = row.getCell(k);
                        String key = titles.get(k);
                        String value = null;
                        if (cell != null) {
                          /*这里因为读取excel数据默认值是double类型的，但我的数据都是整数，为了方便先进行一次转换
                           * 先判断数据类型，然后先转换然后在复制给value
                           * 数值类型是0，字符串类型是1，公式型是2，空值是3，布尔值4，错误5
                           */
                          if (row.getCell(k).getCellType() == 0) {
                            value =(int) row.getCell(k).getNumericCellValue()+"";
                            }else {
                              value = cell.toString();//转换成string赋值给value
                              }
                          }
                        rowMap.put(key, value);//把数据存入map集合
                        }
                      sheetList.add(rowMap);//把存好行的数据存入表格的集合中
                      }
                  }
                result.add(sheetList);//把表格的数据存到excel的集合中
                }
          return result;
          } catch (FileNotFoundException e) {
            throw e;
            } finally {
              if (is != null) {
                is.close();
                }
              }
      }

The implementation checks for file existence, determines the file type (.xls or .xlsx), uses the appropriate POI workbook class, iterates over each sheet, row, and cell, converts numeric cells to integers, stores each row as a Map keyed by the header, and collects all rows into a List of Lists.

The author notes that the original logic was adapted from online sources, added improvements such as using a File parameter, additional file‑type validation, and custom Chinese comments, and hopes the method will be helpful to others.

For further discussion, readers are invited to view the original article or join the author's QQ group.