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

一、使用的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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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

//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==上传校验文件名后缀、大小限制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

三、对是否使用模板进行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;
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

四、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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

五、获取单个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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

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

解决方法： NumberToTextConverter.toText();

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

参考链接：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);//将日期转化为字符串格式
1
2
3
4
5
6
7
8

参考链接：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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

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