【Excel单元格类型的解析校验】Java使用POI解析excel数据

本是古典何须时尚 2024-03-26 23:46 82阅读 0赞

## 一、使用的maven依赖： ##

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.7</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</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>

## 二、对上传的文件类型，文件大小进行校验控制 ##

//TODO==上传校验文件名后缀、大小限制
    if (org.springframework.util.StringUtils.isEmpty(file)) {
        
        return new ResultVO("请上传文件！");
    }
    
    if (file.getSize() > 20971520 || file.getSize() == 0) //大于20MB
    {
        
        return new ResultVO("文件大小大于0MB，小于20MB");
    }
    System.out.println(file.getContentType());
    System.out.println(file.getOriginalFilename());
    
    List<String> typeList = Arrays.asList
            (".xls", ".XLS", ".xlsx", ".XLSX", ".doc", ".DOC", ".docx", ".DOCX", ".pdf", ".PDF", ".jpg", ".JPG", ".png", ".PNG", ".jepg", ".JPEG", ".bpm", ".BPM", ".zip", ".ZIP", ".rar", ".RAR");
    String fileType = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
    if (!typeList.contains(fileType)) {
        
        return new ResultVO("文件类型有误！");
    }
    //TODO==上传校验文件名后缀、大小限制

## 三、对是否使用模板进行Excel文件的上传进行校验 | 判断Excel固定单元格是否有值 ##

public static String ExecelExport(MultipartFile file) {
        
        Sheet sheet;
        InputStream inputStream = null;
        try {
        
            //获取前端传递过来的文件对象，存储在“inputStream”中
            inputStream = file.getInputStream();
            //获取文件名
            String fileName = file.getOriginalFilename();
            //用于存储解析后的Excel文件
            Workbook workbook = null;
            //判断文件扩展名为“.xls还是xlsx的Excel文件”,因为不同扩展名的Excel所用到的解析方法不同
            String fileType = fileName.substring(fileName.lastIndexOf("."));
            if (".xls".equals(fileType)) {
        
                //HSSFWorkbook专门解析.xls文件
                workbook = new HSSFWorkbook(inputStream);
            } else if (".xlsx".equals(fileType)) {
        
                //XSSFWorkbook专门解析.xlsx文件
                workbook = new XSSFWorkbook(inputStream);
            } else {
        
                throw new BusinessException("导入文件类型非法");
            }
            //获取第一个sheet页
            Sheet sheet1 = workbook.getSheetAt(0);
            //获取固定行固定列，(获取想要进行校验的单元格的值)
            Cell cell = sheet1.getRow(0).getCell(11);
            String stringCellValue = cell.getStringCellValue();
            return stringCellValue;
        } catch (Exception e) {
        
            System.out.println(e.getMessage());
            e.printStackTrace();
            return null;
        } finally {
        
            try {
        
                inputStream.close();
            } catch (IOException e) {
        
                e.printStackTrace();
                return null;
            }
        }
    }

## 四、Java解析Excel，获取单元格，并对单元格中的内容进行校验 ##

//获得文本操作对象
    XSSFWorkbook wb = new XSSFWorkbook(file.getInputStream());
    // 得到第一个SHEET页面
    XSSFSheet sheet = wb.getSheetAt(0);
    // 开始进行SHEET页面解析
    if(sheet != null){
        
        Set sfzhmSet = new HashSet();
        //校验，并将有问题的校验信息全部返回到前端
        String errResultMsg = "";
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
        
            //排除表头，从第四行开始解析
            if (rowNum > 2) {
        
                //获取表格有数据的第一行
                XSSFRow hssfRow = sheet.getRow(rowNum);
                //获取第一行第一个单元格
                Cell cell = hssfRow.getCell(0);
                //判断单元格里内容是否为空
                if (hssfRow == null || cell == null || cell.getCellType() == CellType.BLANK) {
        
                    continue;
                }
                //进行基本数据校验
                String errMsg = ValidateUtil.validatePersonnelInfo(hssfRow).toString();
                if (StringUtils.isNotBlank(errMsg)) {
        
                    errResultMsg += errMsg;
                }
            }
        }
        //将全部校验结果返回至前端
        if(!"".equals(errResultMsg)){
        
            return new ResultVO(errResultMsg);
        }
    }

## 五、获取单个Excel单元格的类型，进行相关的校验 ##

> CellType.NUMERIC：数字类型  
> CellType.STRING：字符串类型  
> CellType.FORMULA：公式类型  
> CellType.BLANK：空值类型  
> CellType.BOOLEAN：Boolean类型  
> CellType.ERROR：错误故障类型

//获取单个单元格的数据类型
    public static String getCellValue(Cell cell) {
        
        String cellValue = "";
        if (cell == null) {
        
            return cellValue;
        }
    
        // 判断数据的类型
        switch (cell.getCellType()) {
        
            case NUMERIC:
                // 数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case STRING:
                // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN:
                // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA:
                // 公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case BLANK:
                // 空值
                cellValue = "";
                break;
            case ERROR:
                // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
    
        return cellValue;
    }

### 1、若Excel中导入的字符串数字类型变成了NUMERIC数字类型： ###

**解决方法：** NumberToTextConverter.toText();

if(c.getCellType()==CellType.NUMERIC){
        
        returnStr = NumberToTextConverter.toText(c.getNumericCellValue());
    }

参考链接：[https://blog.csdn.net/FxxYSHOOO/article/details/126297887][https_blog.csdn.net_FxxYSHOOO_article_details_126297887]

### 2、excel中数据明明是yyyy/mm/dd ,java程序解析为dd-M月-yyyy | Excel中，单元格类型为NUMERIC的日期数据的处理： ###

**处理方式：需要先将数据转化为天数，在转成日期格式的字符串。**

//1、将数据转化为天数
    double value = cell.getNumericCellValue(); 
    //2、将天数转成日期格式的字符串
    Calendar calendar = new GregorianCalendar(1900,0,-1);
    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    Date d = calendar.getTime();
    Date dd = DateUtils.addDays(d,Integer.valueOf((int) value));
    String format1 = formatter.format(dd);//将日期转化为字符串格式

参考链接：[https://blog.csdn.net/qq\_43354492/article/details/128124813][https_blog.csdn.net_qq_43354492_article_details_128124813]

### 3、更全面的解析Excel单元格类型，并对数据进行处理的方法： ###

主要是对 数据类型 和 公式类型 两种有细分的区别处理；

public static final String REGEX = "\n";
    //getCellValue出来的数据就是去格式化之后的正确数据
    private String getCellValue(Cell cell) {
        
            Object cellValue;
            if (cell != null) {
        
                cellValue = cellValue(cell);
            } else {
        
                cellValue = "";
            }
            return String.valueOf(cellValue);
        }
    
        private Object cellValue(Cell cell) {
        
            //判断cell类型
            Object cellValue;
            switch (cell.getCellType()) {
        
                case NUMERIC: {
        
                    cellValue = date(cell);
                    break;
                }
                case FORMULA: {
        
                    cellValue = formula(cell);
                    break;
                }
                case STRING: {
        
                    cellValue = cell.getRichStringCellValue().getString().replaceAll(REGEX, " ") ;
                    break;
                }
                default:
                    cellValue = cell.getStringCellValue();
            }
            return cellValue;
        }
    
        private Object formula(Cell cell) {
        
            //判断cell是否为日期格式
            Object cellValue = null;
            try {
        
                cellValue = date(cell);
            } catch (Exception e) {
        
                if (e.getMessage().contains(STRING)) {
        
                    cellValue = cell.getRichStringCellValue().getString().replaceAll(REGEX, " ");
                }
            }
            return cellValue;
        }
    
        private Object date(Cell cell) {
        
            Object cellValue;
            if (DateUtil.isCellDateFormatted(cell)) {
        
                cellValue = cell.getDateCellValue();
                cellValue = cellDate(cellValue);
            } else {
        
                //数字
                cell.setCellType(CellType.STRING);
                cellValue = cell.getRichStringCellValue();
    
            }
            return String.valueOf(cellValue).replaceAll(REGEX, " ");
        }
    
        private Object cellDate(Object cellValue) {
        
            DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
            cellValue = formater.format(cellValue);
            return cellValue;
        }

参考链接1：[https://blog.csdn.net/icemeco/article/details/130872752][https_blog.csdn.net_icemeco_article_details_130872752]  
参考链接2：[https://blog.csdn.net/FxxYSHOOO/article/details/126297887][https_blog.csdn.net_FxxYSHOOO_article_details_126297887]

[https_blog.csdn.net_FxxYSHOOO_article_details_126297887]: https://blog.csdn.net/FxxYSHOOO/article/details/126297887
[https_blog.csdn.net_qq_43354492_article_details_128124813]: https://blog.csdn.net/qq_43354492/article/details/128124813
[https_blog.csdn.net_icemeco_article_details_130872752]: https://blog.csdn.net/icemeco/article/details/130872752