后台接收前端excel并解析入库

先来看controller

@PostMapping("/savePersonByExcel")
public ApiResult savePersonByExcel(@RequestParam("file") MultipartFile file) throws Exception {
    
    //TODO: 做一些校验

    File tempFile = File.createTempFile("temp", null);
    file.transferTo(tempFile);
    ExcelParser parser = new ExcelParser();
    List<Person> persons = parser.parseExcel(tempFile);
    tempFile.delete();
    personService.saveAll(persons);
}

这里使用MutipartFile来接收文件，创建临时文件tempFile(用完一定要记得删除)，ExcelParser为转换器，以下是详细代码：

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

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExcelContactParser {

    public List<Person> parseExcelToPerson(File file) throws IOException {
        List<Person> persons = new ArrayList<>();

        FileInputStream fis = new FileInputStream(file);
        Workbook workbook = WorkbookFactory.create(fis);
        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();

        if (rowIterator.hasNext()) {
            rowIterator.next();
        }

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();

            if (row == null || isRowEmpty(row)) {
                continue;
            }

            Cell cname = row.getCell(0);
            Cell csex = row.getCell(1);
            Cell cage = row.getCell(2);
            Cell cmobile = row.getCell(3);

            YthSmsContacts c = new Person(cname.getStringCellValue()
                    , cmobile.getStringCellValue()
                    , csex.getStringCellValue()
                    , cage.getStringCellValue()
            );
            persons.add(c);
        }

        return persons;
    }

    
    //判断去除空行，很重要
    public static boolean isRowEmpty(Row row) {
        for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
            Cell cell = row.getCell(cellNum);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    
    //通过判断某个关键列判断数据完整性
    public static boolean isMobileEmpty(Row row) {
        Cell cell = row.getCell(3);
        if (cell == null || cell.getCellType() == CellType.BLANK)
            return true;
        else
            return false;
    }


}

最后按常规方式入库就好了