Java 读取excel文件，转为List＜Map＞格式数据，同时处理excel的日期格式

实现功能：使用java将Excel转换为List

注：可能读取日期格式的会出现科学计数法之类的情况，所有这里传入特定的参数指定哪些是日期格式的数据，进行转换格式。

1、相关的依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>

Java代码:传入文件地址获取数据

 /**
     * @param fileUrl Excel文件地址
     * @param dateFormatField 日期字段
     * @param dateFormatStr "yyyy-MM-dd HH:mm:ss"
     * @return 获取Excel数据（获取有一个标题行的格式）,同时将数字格式字段转为时间格式
     *
     */
    public static List<LinkedHashMap<String,String>> getExcelData(String fileUrl,List<String> dateFormatField, String dateFormatStr){
        //获取文件
        String fileType = fileUrl.substring(fileUrl.lastIndexOf("."));
        File excelFile = getExcelFile(fileUrl, fileType);
        //处理文件
        List<LinkedHashMap<String,String>> dataList = new ArrayList<>();
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(excelFile);
            Workbook workbook =null;
            if(".xls".equals(fileType)){
                workbook= new HSSFWorkbook(inputStream);
            }else if(".xlsx".equals(fileType)){
                workbook = new XSSFWorkbook(inputStream);
            }
            Sheet sheet = workbook.getSheetAt(0);
            //获取标题字段
            List<String> titleList = new ArrayList<>();
            Row titleRow = sheet.getRow(sheet.getFirstRowNum());
            for(int i = titleRow.getFirstCellNum(); i < titleRow.getLastCellNum(); i++){
                Cell cell = titleRow.getCell(i);
                titleList.add(cell.toString());
            }
            //获取数据
            for(int j = sheet.getFirstRowNum() +1; j <= sheet.getLastRowNum(); j++){
                Row row = sheet.getRow(j);
                LinkedHashMap<String, String> hashMap = new LinkedHashMap<>();
                for(int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++){
                    Cell cell = row.getCell(i);
                    if (cell == null){
                        hashMap.put(titleList.get(i),"");
                    }else if (dateFormatField.contains(titleList.get(i)) && CellType.NUMERIC.equals(cell.getCellType())){
                        //将指定的日期字段进行转换格式
                        Date dateCellValue = cell.getDateCellValue();
                        SimpleDateFormat format = new SimpleDateFormat(dateFormatStr);
                        String dateValue = format.format(dateCellValue);
                        hashMap.put(titleList.get(i),dateValue);
                    }else if (CellType.NUMERIC.equals(cell.getCellType())){
                        //数字类型 科学计数法转为字符串
                        double numericCellValue = cell.getNumericCellValue();
                        BigDecimal bigDecimal = new BigDecimal(numericCellValue);
                        hashMap.put(titleList.get(i),bigDecimal.toPlainString());
                    }else {
                        hashMap.put(titleList.get(i),cell.getStringCellValue());
                    }
                }
                dataList.add(hashMap);
            }
        }catch (Exception e){
            log.error("获取Excel数据异常：{}",e);
        }finally {
            RemotePDFReaderUtil.delFile(excelFile);
            if (inputStream != null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        log.info("读取excel文件地址：{}：",fileUrl);
        log.info("读取excel文件数据：{}：",dataList);
        return dataList;
    }

 /**
     * 获取远程Excel文件
     * @param remoteUrl
     * @param excelType .文件类型
     * @return
     */
    public static File getExcelFile(String remoteUrl,String excelType){
        try {
            URL url = new URL(remoteUrl);
            String filePath = DateUtil.currentTime() + excelType;
            InputStream inputStream = url.openStream();
            // 将远程PDF文件保存为本地文件
            File localFile = new File(filePath);  // 替换为保存到本地的文件路径和名称
            OutputStream outputStream = new FileOutputStream(localFile);

            byte[] buffer = new byte[1024];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
            }
            System.out.println(localFile.getPath());
            // 关闭流
            inputStream.close();
            outputStream.close();
            return localFile;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }