JAVA POI 解析Excel文件2003 2007为List<Map<String,Object>>

Maven配置

                <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>3.17</version>
		</dependency>

主方法做测试数据

    public static void main(String[] args)  throws IOException {
        File file = new File("F:\\asd.xlsx");
        List<Map<String,Object>> dataList = importExcel(file);
        for (int i = 1; i < dataList.size(); i++) {
            for (String key : dataList.get(i).keySet()) {
                System.out.print("["+key+":"+dataList.get(i).get(key)+"]");
            }
            System.out.println("-");
        }
    }

根据文件们后缀区分excel版本

 /**
     * 判断文件名
     * @param file
     * @return
     * @throws IOException
     */
    public static List<Map<String,Object>> importExcel(File file) throws IOException {
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".")==-1?"":fileName.substring(fileName.lastIndexOf(".")+1);
        //根据文件名做不同处理
        if("xls".equals(extension)){
            return read2003Excel(file);
        }else if("xlsx".equals(extension)){
            return read2007Excel(file);
        }else{
            System.out.println("类型不支持");
            return null;
        }
    }

2003Excel

 /**
     *  Excel 2003
     * @param file
     * @return
     * @throws IOException
     */
    public static List<Map<String,Object>> read2003Excel(File file) throws IOException {
        List<Map<String,Object>> listMap = new ArrayList<Map<String,Object>>();
        // 构造 XSSFWorkbook 对象
        HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
        // 在给定索引处获取HSSFSheet对象。
        HSSFSheet sheet = hwb.getSheetAt(0);//表
        Object value = null;//值
        HSSFRow row = null;//行
        HSSFCell cell = null;//单元格
        //循环（行）
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            //获取第一行数据
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }

            ArrayList<Object> arraylist = new ArrayList<Object>();
            //循环当前行的数据
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                //获取当前单元格
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING://String
                        value = cell.getStringCellValue();
                        //System.out.println(i+"行"+j+" 列 is String type："+value);
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC://Date number
                        if("@".equals(cell.getCellStyle().getDataFormatString())){
                            value = df.format(cell.getNumericCellValue());
                        } else if("General".equals(cell.getCellStyle().getDataFormatString())){
                            value = nf.format(cell.getNumericCellValue());
                        }else{
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                       // System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+value);
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN://boolean
                        value = cell.getBooleanCellValue();
                       // System.out.println(i+"行"+j+" 列 is Boolean type:"+value);
                        break;
                    case XSSFCell.CELL_TYPE_BLANK://null
                        value = "";
                       // System.out.println(i+"行"+j+" 列 is Blank type");
                        break;
                    default://default
                        value = cell.toString();
                        //System.out.println(i+"行"+j+" 列 is default type:"+value);
                }
                //判断value是否为null
                arraylist.add(value);//添加到list中
            }

            Map<String,Object> map = new HashMap<String,Object>();
            //循环arrayList，将数据封装成Map
            for(int k = 0; k < arraylist.size(); k++){
                System.out.println("arrayList Value:"+arraylist.get(k));
                switch (k){
                    case 0:
                        map.put("列1",arraylist.get(k));
                        break;
                    case 1:
                        map.put("列2",arraylist.get(k));
                        break;
                    case 2:
                        map.put("列3",arraylist.get(k));
                        break;
                    case 3:
                        map.put("列4",arraylist.get(k));
                        break;
                    case 4:
                        map.put("列5",arraylist.get(k));
                        break;
                }
            }
            //添加到ListMap中
            listMap.add(map);
        }
        return listMap;
    }

2007Excel

 /**
     *  Excel 2007
     * @param file
     * @return
     * @throws FileNotFoundException
     */
    private static List<Map<String,Object>> read2007Excel(File file) throws IOException {
        List<Map<String,Object>> listMap = new ArrayList<Map<String,Object>>();
        // 构造 XSSFWorkbook 对象
        XSSFWorkbook hwb = new XSSFWorkbook (new FileInputStream(file));
        // 在给定索引处获取HSSFSheet对象。
        XSSFSheet sheet = hwb.getSheetAt(0);//表
        Object value = null;//值
        XSSFRow row = null;//行
        XSSFCell  cell = null;//单元格
        //循环（行）
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            //获取第一行数据
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }

            ArrayList<Object> arraylist = new ArrayList<Object>();
            //循环当前行的数据
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                //获取当前单元格
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING://String
                        value = cell.getStringCellValue();
                        //System.out.println(i+"行"+j+" 列 is String type："+value);
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC://Date number
                        if("@".equals(cell.getCellStyle().getDataFormatString())){
                            value = df.format(cell.getNumericCellValue());
                        } else if("General".equals(cell.getCellStyle().getDataFormatString())){
                            value = nf.format(cell.getNumericCellValue());
                        }else{
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        // System.out.println(i+"行"+j+" 列 is Number type ; DateFormt:"+value);
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN://boolean
                        value = cell.getBooleanCellValue();
                        // System.out.println(i+"行"+j+" 列 is Boolean type:"+value);
                        break;
                    case XSSFCell.CELL_TYPE_BLANK://null
                        value = "";
                        // System.out.println(i+"行"+j+" 列 is Blank type");
                        break;
                    default://default
                        value = cell.toString();
                        //System.out.println(i+"行"+j+" 列 is default type:"+value);
                }
                //判断value是否为null
                arraylist.add(value);//添加到list中
            }

            Map<String,Object> map = new HashMap<String,Object>();
            //循环arrayList，将数据封装成Map
            for(int k = 0; k < arraylist.size(); k++){
                System.out.println("arrayList Value:"+arraylist.get(k));
                switch (k){
                    case 0:
                        map.put("列1",arraylist.get(k));
                        break;
                    case 1:
                        map.put("列2",arraylist.get(k));
                        break;
                    case 2:
                        map.put("列3",arraylist.get(k));
                        break;
                    case 3:
                        map.put("列4",arraylist.get(k));
                        break;
                    case 4:
                        map.put("列5",arraylist.get(k));
                        break;
                }
            }
            //添加到ListMap中
            listMap.add(map);
        }
        return listMap;
    }