利用POI解析excel文件小例子

1、导入pom依赖

pom.xml

        
        
            org.apache.poi
            poi
            4.0.0
        
        
            org.apache.poi
            poi-ooxml
            4.0.0
        
        
        
            org.projectlombok
            lombok
            true
        

2、相关实体类

Account.java

package com.zwt.pojo;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
import lombok.Data;


@Data
public class Account implements Serializable {
    private Integer id;

    private String name;

    private Double money;

    private BigDecimal bomQty;  //数量

    private Date createTime;// 创建时间

    private Date updateTime;// 更新时间

    private static final long serialVersionUID = 1L;
}

ReturnObject.java

package com.zwt.pojo;


public class ReturnObject {

	public static final int SECCESS = 0;
	public static final int FAIL = -1;
	public static final int BUSINESS_ERROR = 100;

	private int code;// 状态代码

	private String msg;// 错误信息

	private int count;// 总记录数

	private Object data;// 数据对象

	public ReturnObject() {
		this.code = SECCESS;
		this.msg = "success";
	}

	public ReturnObject(Object data) {
		this.code = SECCESS;
		this.msg = "success";
		this.data = data;
	}

	public ReturnObject(int code, String msg) {
		this.code = code;
		this.msg = msg;
	}

	public ReturnObject(int count, Object data) {
		this.code = SECCESS;
		this.msg = "success";
		this.count = count;
		this.data = data;
	}
	
	public ReturnObject(int code, String msg, Object data) {
		this.code = code;
		this.msg = msg;
		this.data = data;
	}

	public int getCode() {
		return code;
	}

	public void setCode(int code) {
		this.code = code;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

	public int getCount() {
		return count;
	}

	public void setCount(int count) {
		this.count = count;
	}

	public Object getData() {
		return data;
	}

	public void setData(Object data) {
		this.data = data;
	}

	@Override
	public String toString() {
		return "ReturnObject{" +
			"code=" + code +
			", msg='" + msg + ''' +
			", count=" + count +
			", data=" + data +
			'}';
	}
}

3、controller类

package com.zwt.controller;

import com.zwt.pojo.ReturnObject;
import com.zwt.service.HelloService;
import org.springframework.beans.factory.annotation.Autowired;
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;

@RestController
public class TestController {

  @Autowired
  private HelloService helloService;

  
  @PostMapping(value = "/upload")
  public ReturnObject upload(@RequestParam("file") MultipartFile file) {
    return helloService.upload(file);
  }

4、service类

package com.zwt.service.impl;

import com.zwt.pojo.Account;
import com.zwt.pojo.ReturnObject;
import com.zwt.service.HelloService;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Pattern;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

@Service
@Slf4j
public class HelloServiceImpl implements HelloService {

  @Autowired
  private com.zwt.mapper.AccountMapper AccountMapper;


  @Override
  public ReturnObject upload(MultipartFile file) {
    FileInputStream fis;
    Workbook wookbook;
    try {
      fis = (FileInputStream) file.getInputStream();
      wookbook = new HSSFWorkbook(fis);// 得到2003工作簿
    } catch (Exception ex) {
      try {
        fis = (FileInputStream) file.getInputStream();
        wookbook = new XSSFWorkbook(fis);// 得到2007工作簿
      } catch (Exception e) {
        return new ReturnObject(ReturnObject.BUSINESS_ERROR, "请检查上传文件格式！");
      }
    }

    // 得到一个工作表
    Sheet sheet = wookbook.getSheetAt(0);
    // 获得表头
    Row rowHead = sheet.getRow(0);
    if (rowHead == null) {
      return new ReturnObject(ReturnObject.BUSINESS_ERROR, "上传文件表头为空！");
    }

    // 判断表头是否正确
    if (rowHead.getPhysicalNumberOfCells() != 10) {
      return new ReturnObject(ReturnObject.BUSINESS_ERROR, "上传文件表头数量有误！");
    }

    // 获得数据的总行数
    int totalRowNum = sheet.getLastRowNum();
    if (totalRowNum <= 0) {
      return new ReturnObject(ReturnObject.BUSINESS_ERROR, "上传文件中没有数据！");
    }

    // 创建list，用于发送更新通知邮件
    List updateList = new ArrayList<>();
    List insertList = new ArrayList<>();

    // 获得所有数据
    for (int i = 1; i <= totalRowNum; i++) {
      Account account = new Account();
      try {
        // 获得第i行对象
        Row row = sheet.getRow(i);
        if (row == null) {
          continue;
        }
        String id = row.getCell(0).getStringCellValue();
        if (id.length() < 0 ){
          return new ReturnObject(ReturnObject.BUSINESS_ERROR, "上传文件中第" + (i + 1) + "行ID值不正确！");
        }
        String name = row.getCell(1).getStringCellValue();
        String money = row.getCell(2).getStringCellValue();
        String bomQty = row.getCell(3).getStringCellValue();
        if (!Pattern.matches("^(([1-9][0-9])|([0-9]))(\.[0-9]{1})?$", bomQty)){
          return new ReturnObject(ReturnObject.BUSINESS_ERROR, "上传文件中第" + (i + 1) + "行用量格式不正确！");
        }
        account.setId(Integer.valueOf(id));
        account.setName(name);
        account.setMoney(Double.valueOf(money));
        account.setBomQty(new BigDecimal(bomQty));
        log.info("upload account:{}", account);

        if (account.getId() == null || StringUtils.isEmpty(account.getName())
            || account.getMoney() == null
            || account.getBomQty() == null
        ) {
          return new ReturnObject(ReturnObject.BUSINESS_ERROR, "上传文件中第" + (i + 1) + "行数据必填项不能为空！");
        }
        
        //根据业务主键查询
        List list = AccountMapper.selectBySelective(account);
        if (!list.isEmpty()){
          account.setCreateTime(list.get(0).getCreateTime());
          account.setUpdateTime(new Date());
          int updateFlag = AccountMapper.updateByPrimaryKey(account);
          updateList.add(account);
        } else {
          account.setCreateTime(new Date());
          int insertFlag = AccountMapper.insert(account);
          insertList.add(account);
        }
      } catch (Exception e) {
        log.error("解析上传文件异常，异常信息：",e);
        return new ReturnObject(ReturnObject.BUSINESS_ERROR, "上传文件中第" + (i + 1) + "行数据格式不为文本或值长度超标！");
      }
    }

    log.info("updateList:{},insertList:{}",updateList,insertList);

    return new ReturnObject();
  }
}

5、excel文件表头