解决Excel文件读取遇到值为null单元格会自动跳过的情况

- 日理万妓 2022-10-08 02:26 66阅读 0赞

第一种方法：使用**Excel4J**依赖

<dependency>
        <groupId>com.github.crab2died</groupId>
        <artifactId>Excel4J</artifactId>
        <version>3.0.0</version>
    </dependency>

import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.FileInputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    @Slf4j
    public class demo {
    
        public static void main(String[] args){
            String filePath="F:\\cs.xlsx";
            String sheetName="Sheet1";
            List<List<String>> list=getExcelData(filePath,sheetName);
            System.out.println(list.size());
        }
    
        private static List<List<String>> getExcelData(String filePath,String sheetName){
            List<List<String>> list=new ArrayList<>();
            FileInputStream fileInputStream=null;
            try {
                fileInputStream = new FileInputStream(filePath);
                XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
                //获取当前sheet
                XSSFSheet sheet = sheets.getSheet(sheetName);
                //获取当前sheet的行数
                int rows = sheet.getPhysicalNumberOfRows();
                for(int i=1;i<rows;i++){
                    List<String> stringList=new ArrayList<>();
                    //获取第几行
                    XSSFRow row = sheet.getRow(i);
                    //获取当前行的最后一个单元格的位置，从0开始
                    int columns = row.getLastCellNum();
                    for(int j=0;j<columns;j++){
                        if(row.getCell(j)==null||"".equals(row.getCell(j).toString())){//单元格不存在值
                            stringList.add("");
                        }else {//单元格存在值
                            stringList.add(row.getCell(j).toString());
                        }
                    }
                    list.add(stringList);
                }
            } catch (Exception e) {
                log.error("读取excel文件出现异常：",e);
            }finally {
                try{
                    if(fileInputStream!=null){
                        fileInputStream.close();
                    }
                }catch (Exception e){
                    log.error("关闭文件流出现了异常：",e);
                }
            }
            return list;
        }
    }

第二种：使用**apache poi-ooxml**依赖

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

import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    
    import java.io.FileInputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    public class demo {
    
        public static void main(String[] args){
            String filePath="F:\\cs.xlsx";
            List<List<String>> list=getExcelData(filePath);
            System.out.println(list.toString());
            System.out.println(list.size());
        }
    
        private static List<List<String>> getExcelData(String filePath){
            List<List<String>> list=new ArrayList<>();
            FileInputStream fileInputStream=null;
            try {
                fileInputStream = new FileInputStream(filePath);
    
                //获取excel文件流
                Workbook workbook = WorkbookFactory.create(fileInputStream);
                //获取sheet
                Sheet sheet = workbook.getSheetAt(0);
                //获取当前sheet的行数
                int rows = sheet.getPhysicalNumberOfRows();
                for(int i=1;i<rows;i++){
                    List<String> stringList=new ArrayList<>();
                    //获取第几行
                    Row row = sheet.getRow(i);
                    //获取当前行的最后一个单元格的位置，从0开始
                    int columns = row.getLastCellNum();
                    for(int j=0;j<columns;j++){
                        if(row.getCell(j)==null||"".equals(row.getCell(j).toString())){//单元格不存在值
                            stringList.add("");
                        }else {//单元格存在值
                            stringList.add(row.getCell(j).toString());
                        }
                    }
                    list.add(stringList);
                }
            } catch (Exception e) {
                System.out.println("读取excel文件出现异常："+e);
            }finally {
                try{
                    if(fileInputStream!=null){
                        fileInputStream.close();
                    }
                }catch (Exception e){
                    System.out.println("关闭文件流出现了异常："+e);
                }
            }
            return list;
        }
    }