利用POI解析excel文件小例子

秒速五厘米 2024-03-30 09:07 79阅读 0赞

#### 1、导入pom依赖 ####

pom.xml

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.0</version>
            </dependency>
            
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.12</version>
                <scope>provided</scope>
            </dependency>

#### 2、相关实体类 ####

Account.java

package com.zwt.pojo;
    
    import java.io.Serializable;
    import java.math.BigDecimal;
    import java.util.Date;
    import lombok.Data;
    
    /**
     * account
     * @author 
     */
    @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;
    
      /**
       * 上传（导入）
       * @param file
       * @return
       */
      @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<Account> updateList = new ArrayList<>();
        List<Account> 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<Account> 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文件表头 ####

![在这里插入图片描述][51a7e4d78a4142b69ba488a53136651e.png]

[51a7e4d78a4142b69ba488a53136651e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/3410322371f74715ad62b0539517ffde.png