Java后端优雅地校验Excel文件

标签：excelRow Java String Excel 校验 private validatedRow import row

在Java后端如何优雅地校验用户上传的Excel文件呢？
可以采用以下步骤实践

1. 使用Apache POI库读取Excel文件

Apache POI是一个强大的Java库，用于处理Microsoft Office文档，包括Excel文件。首先，确保在项目中引入Apache POI依赖。

Maven依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>5.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.4</version>
</dependency>

2. 创建Excel文件读取工具类

创建一个工具类来读取Excel文件内容，并将其转换为Java对象。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExcelReader {

    public static List<ExcelRow> readExcel(InputStream inputStream) throws Exception {
        List<ExcelRow> rows = new ArrayList<>();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();

        // Skip header row
        if (rowIterator.hasNext()) {
            rowIterator.next();
        }

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            ExcelRow excelRow = new ExcelRow();
            excelRow.setId((int) row.getCell(0).getNumericCellValue());
            excelRow.setUri(row.getCell(1).getStringCellValue());
            excelRow.setLabel(row.getCell(2).getStringCellValue());
            excelRow.setRate(row.getCell(3).getNumericCellValue());
            excelRow.setScene(row.getCell(4).getStringCellValue());
            excelRow.setSuggestion(row.getCell(5).getStringCellValue());
            excelRow.setFrameShot(row.getCell(6).getStringCellValue());
            excelRow.setUpdateTime(row.getCell(7).getDateCellValue());
            excelRow.setCensorType(row.getCell(8).getStringCellValue());
            excelRow.setCreateTime(row.getCell(9).getDateCellValue());
            excelRow.setStatus(row.getCell(10).getStringCellValue());
            excelRow.setDealType(row.getCell(11).getStringCellValue());
            excelRow.setDealTime(row.getCell(12).getDateCellValue());
            excelRow.setFrames(row.getCell(13).getStringCellValue());
            excelRow.setStreamName(row.getCell(14).getStringCellValue());
            excelRow.setPushAppNick(row.getCell(15).getStringCellValue());
            excelRow.setOrganName(row.getCell(16).getStringCellValue());
            excelRow.setRemark(row.getCell(17).getStringCellValue());
            excelRow.setPersonId((int) row.getCell(18).getNumericCellValue());
            excelRow.setUid((int) row.getCell(19).getNumericCellValue());

            rows.add(excelRow);
        }

        workbook.close();
        return rows;
    }
}

class ExcelRow {
    private int id;
    private String uri;
    private String label;
    private double rate;
    private String scene;
    private String suggestion;
    private String frameShot;
    private java.util.Date updateTime;
    private String censorType;
    private java.util.Date createTime;
    private String status;
    private String dealType;
    private java.util.Date dealTime;
    private String frames;
    private String streamName;
    private String pushAppNick;
    private String organName;
    private String remark;
    private int personId;
    private int uid;

    // Getters and Setters
}

3. 创建校验规则

使用Hibernate Validator或其他校验框架来定义校验规则。

添加Hibernate Validator依赖

<dependency>
    <groupId>org.hibernate.validator</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>6.2.0.Final</version>
</dependency>
<dependency>
    <groupId>javax.validation</groupId>
    <artifactId>validation-api</artifactId>
    <version>2.0.1.Final</version>
</dependency>

定义校验注解

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Positive;
import javax.validation.constraints.Size;
import java.util.Date;

public class ValidatedExcelRow {

    @Positive(message = "ID must be positive")
    private int id;

    @NotBlank(message = "URI cannot be blank")
    private String uri;

    @NotBlank(message = "Label cannot be blank")
    private String label;

    @Positive(message = "Rate must be positive")
    private double rate;

    @NotBlank(message = "Scene cannot be blank")
    private String scene;

    @NotBlank(message = "Suggestion cannot be blank")
    private String suggestion;

    @NotBlank(message = "Frame shot cannot be blank")
    private String frameShot;

    @NotNull(message = "Update time cannot be null")
    private Date updateTime;

    @NotBlank(message = "Censor type cannot be blank")
    private String censorType;

    @NotNull(message = "Create time cannot be null")
    private Date createTime;

    @NotBlank(message = "Status cannot be blank")
    private String status;

    @NotBlank(message = "Deal type cannot be blank")
    private String dealType;

    @NotNull(message = "Deal time cannot be null")
    private Date dealTime;

    @NotBlank(message = "Frames cannot be blank")
    private String frames;

    @NotBlank(message = "Stream name cannot be blank")
    private String streamName;

    @NotBlank(message = "Push app nick cannot be blank")
    private String pushAppNick;

    @NotBlank(message = "Organ name cannot be blank")
    private String organName;

    @Size(max = 255, message = "Remark must not exceed 255 characters")
    private String remark;

    @Positive(message = "Person ID must be positive")
    private int personId;

    @Positive(message = "UID must be positive")
    private int uid;

    // Getters and Setters
}

4. 实现校验逻辑

在读取Excel文件后，将每一行数据转换为ValidatedExcelRow对象，并进行校验。

import javax.validation.Validation;
import javax.validation.Validator;
import javax.validation.ValidatorFactory;
import javax.validation.ConstraintViolation;
import java.io.InputStream;
import java.util.Set;

public class ExcelValidator {

    private static final ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
    private static final Validator validator = factory.getValidator();

    public static void validateExcel(InputStream inputStream) throws Exception {
        List<ExcelRow> rows = ExcelReader.readExcel(inputStream);
        for (ExcelRow row : rows) {
            ValidatedExcelRow validatedRow = convertToValidatedRow(row);
            Set<ConstraintViolation<ValidatedExcelRow>> violations = validator.validate(validatedRow);
            if (!violations.isEmpty()) {
                StringBuilder errorMessages = new StringBuilder();
                for (ConstraintViolation<ValidatedExcelRow> violation : violations) {
                    errorMessages.append(violation.getMessage()).append("\n");
                }
                throw new IllegalArgumentException("Validation failed: " + errorMessages.toString());
            }
        }
    }

    private static ValidatedExcelRow convertToValidatedRow(ExcelRow row) {
        ValidatedExcelRow validatedRow = new ValidatedExcelRow();
        validatedRow.setId(row.getId());
        validatedRow.setUri(row.getUri());
        validatedRow.setLabel(row.getLabel());
        validatedRow.setRate(row.getRate());
        validatedRow.setScene(row.getScene());
        validatedRow.setSuggestion(row.getSuggestion());
        validatedRow.setFrameShot(row.getFrameShot());
        validatedRow.setUpdateTime(row.getUpdateTime());
        validatedRow.setCensorType(row.getCensorType());
        validatedRow.setCreateTime(row.getCreateTime());
        validatedRow.setStatus(row.getStatus());
        validatedRow.setDealType(row.getDealType());
        validatedRow.setDealTime(row.getDealTime());
        validatedRow.setFrames(row.getFrames());
        validatedRow.setStreamName(row.getStreamName());
        validatedRow.setPushAppNick(row.getPushAppNick());
        validatedRow.setOrganName(row.getOrganName());
        validatedRow.setRemark(row.getRemark());
        validatedRow.setPersonId(row.getPersonId());
        validatedRow.setUid(row.getUid());
        return validatedRow;
    }
}

5. 集成到Controller

在Spring Boot Controller中集成文件上传和校验逻辑。

import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;

@RestController
public class FileUploadController {

    @PostMapping("/upload")
    public String uploadFile(@RequestParam("file") MultipartFile file) {
        try (InputStream inputStream = file.getInputStream()) {
            ExcelValidator.validateExcel(inputStream);
            return "File uploaded and validated successfully";
        } catch (IOException e) {
            return "Failed to read file: " + e.getMessage();
        } catch (IllegalArgumentException e) {
            return "Validation failed: " + e.getMessage();
        } catch (Exception e) {
            return "An error occurred: " + e.getMessage();
        }
    }
}

6. 处理异常

为了更好地处理异常，可以创建一个全局异常处理器。

import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;

@ControllerAdvice
public class GlobalExceptionHandler {

    @ExceptionHandler(IllegalArgumentException.class)
    public ResponseEntity<String> handleIllegalArgumentException(IllegalArgumentException ex) {
        return new ResponseEntity<>(ex.getMessage(), HttpStatus.BAD_REQUEST);
    }

    @ExceptionHandler(Exception.class)
    public ResponseEntity<String> handleException(Exception ex) {
        return new ResponseEntity<>("An error occurred: " + ex.getMessage(), HttpStatus.INTERNAL_SERVER_ERROR);
    }
}

总结

通过以上步骤，你可以优雅地校验前端上传的Excel文件内容字段。主要步骤包括：

使用Apache POI读取Excel文件。
创建校验规则，使用Hibernate Validator定义校验注解。
实现校验逻辑，将每一行数据转换为校验对象并进行校验。
集成到Controller，处理文件上传请求。
处理异常，创建全局异常处理器以捕获和处理异常。

这种方法不仅提高了代码的可维护性和可读性，还确保了数据的完整性和有效性。

标签：excelRow,Java,String,Excel,校验,private,validatedRow,import,row
From： https://blog.csdn.net/xiongjikai/article/details/144381382