【springboot】上传并解析excel表，使用postman测试上传文件，解决excel版本报错问题

声明：博客代码只是实际项目的一部分，项目是前后端分离的，这篇博客中将记录如何使用工具类中提供的解析excel表格数据，并使用postman测试上传excel表的接口。

在项目pom.xml文件中添加依赖（版本自选，注意不同的版本有可能会抱错，需根据自己的项目导入合适的版本）：

<!-- 解析excel -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

解析excel表的工具类

我使用该工具类来获取表格数据，并将表格数据创建实体对象（根据实际项目需要改动代码）

public class ParseExcelUtil {

    public static Workbook getWorkbook(InputStream inputStream,String fileName) throws Exception{
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        // 推荐使用poi-ooxml中的WorkbookFactory.create(is)来创建Workbook,
        // 因为HSSFWorkbook和XSSFWorkbook都实现了Workbook接口（可以解决以下报错问题）
        workbook = WorkbookFactory.create(inputStream);
       /*我使用以下方法，postman测试接口时会报错（excel版本问题）
        if(".xls".equals(fileType)){
            workbook = new HSSFWorkbook(inputStream);
        }else if(".xlsx".equals(fileType)){
            workbook = new XSSFWorkbook(inputStream);
        }else {
            throw new Exception("请上传文件！");
        }*/
        return workbook;
    }

    public static List<Birth> getListByParseExcel(Workbook workbook){
        List<Birth> birthList = new ArrayList<>();
        try {
            //获取第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            //第0行是表明，忽略，从第二行开始读取
            for(int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++){
                //JSONObject object = new JSONObject();
                Birth birth = new Birth();
                Row row = sheet.getRow(rowNum);
                Cell cell1 = row.getCell(0);
                Cell cell2 = row.getCell(1);
                //先将数字类型的值转化为文本类型，然后再进行读取
                DataFormatter dataFormatter = new DataFormatter();
                String phoneString = dataFormatter.formatCellValue(cell1);
                String idCardString = dataFormatter.formatCellValue(cell2);
                birth.setPhone(phoneString);
                birth.setIdcard(idCardString);
                birthList.add(birth);
            }
        }finally {
            IOUtils.closeQuietly(workbook);
        }
        return birthList;
    }
}

entity文件夹下的实体类Birth.java:

public class Birth implements Serializable {

    private String phone;

    private String idcard;

    private static final long serialVersionUID = 1L;

    public Birth(String phone, String idcard) {
        this.phone = phone;
        this.idcard = idcard;
    }

    public Birth() {
        super();
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone == null ? null : phone.trim();
    }

    public String getIdcard() {
        return idcard;
    }

    public void setIdcard(String idcard) {
        this.idcard = idcard == null ? null : idcard.trim();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", phone=").append(phone);
        sb.append(", idcard=").append(idcard);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

controller文件夹下UpExcelFileController.java:

@RestController
public class UpExcelFileController {

    @Autowired
    private UpExcelFileService upExcelFileService;
    //导入

    @RequestMapping(value = "/excel/batchImport",method = RequestMethod.POST)
    public String saveBirthdayInfoWithExcel(@RequestParam(value = "excel") MultipartFile excel) throws Exception {
        JSONObject response = new JSONObject();

        //判断文件是否为空
        if (excel == null) {
            response.put("10004", "文件为空");
            return response.toString();
        }

        //获取文件名
        String fileName = excel.getOriginalFilename();

        //验证文件是否合格
        if (!ExcelImportUtils.validateExcel(fileName)) {
            response.put("10003", "文件必须是excel格式");
            return response.toString();
        }

        //进一步判断文件内容是否为空（即判断其大小是否为0或其名称是否为null）
        long fileSize = excel.getSize();
        if (StringUtils.isEmptyOrWhitespaceOnly(fileName) || fileSize == 0) {
            response.put("10002", "文件不能为空");
            return response.toString();
        }

        //读入input流
        InputStream inputStream = excel.getInputStream();
        String res = upExcelFileService.saveBirthList(inputStream,fileName);
        return res;
    }
 }

service文件夹下UpExcelServiceImp.java:

@Service
public class UpExcelFileServiceImpl implements UpExcelFileService{

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    @Override
    public String saveBirthList(InputStream inputStream,String fileName) throws Exception{
        JSONObject res = new JSONObject();
        List<Birth> array = null;
        //创建excel工作簿
        Workbook workbook = ParseExcelUtil.getWorkbook(inputStream,fileName);
        array = ParseExcelUtil.getListByParseExcel(workbook);
        System.out.println(array.toString());
        return array.toString();
    }
}

postman工具测试接口结果如图：