java导入excel

Java导入excel

Java 导入加校验，注意校验需根据自己公司的要求来，这里只做参考，接收参数根据自己导入的数据来定义实体；请认真阅读，若还不明白，可以咨询

1 、工具类

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Objects;

public class UploadUtil {

    public static String getUniqueId(){
        SnowflakeUtil snowflakeUtil = new SnowflakeUtil(0,0);
        Long id = snowflakeUtil.nextId();
        return id.toString();
    }

    public static Workbook getWorkbookAuto(MultipartFile file) throws IOException {
        /** 判断文件的类型，是2003还是2007 */
        boolean isExcel2003 = true;
        if (isExcel2007(file.getOriginalFilename())) {
            isExcel2003 = false;
        }
        BufferedInputStream is = new BufferedInputStream(
                file.getInputStream());
        Workbook wb;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        return wb;
    }

    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    public static Boolean checkExtension(MultipartFile file){
        String fileName = file.getOriginalFilename();
        String extension = fileName.substring(fileName.lastIndexOf(".")+1);
        return checkExtension(extension);
    }

    public static Boolean checkExtension(String extension){
        return Lists.newArrayList("xls","xlsx","XLS","XLSX").contains(extension);
    }

    public static Boolean isOfficeFile(MultipartFile file) throws IOException {
        BufferedInputStream bufferedInputStream = new BufferedInputStream(file.getInputStream());
        boolean result = false;
        result = isOfficeFile(bufferedInputStream);
        return result;
    }

    public static Boolean isOfficeFile(InputStream inputStream){
        boolean result = false;
        try {
            FileMagic fileMagic = FileMagic.valueOf(inputStream);
            if (Objects.equals(fileMagic,FileMagic.OLE2)||Objects.equals(fileMagic,fileMagic.OOXML)){
                result = true;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static class SnowflakeUtil{
        /**
         * 起始的时间戳
         */
        private final static long twepoch = 1557825652094L;

        /**
         * 每一部分占用的位数
         */
        private final static long workerIdBits = 5L;
        private final static long datacenterIdBits = 5L;
        private final static long sequenceBits = 12L;

        /**
         * 每一部分的最大值
         */
        private final static long maxWorkerId = -1L ^ (-1L << workerIdBits);
        private final static long maxDatacenterId = -1L ^ (-1L << datacenterIdBits);
        private final static long maxSequence = -1L ^ (-1L << sequenceBits);

        /**
         * 每一部分向左的位移
         */
        private final static long workerIdShift = sequenceBits;
        private final static long datacenterIdShift = sequenceBits + workerIdBits;
        private final static long timestampShift = sequenceBits + workerIdBits + datacenterIdBits;

        private long datacenterId; // 数据中心ID
        private long workerId; // 机器ID
        private long sequence = 0L; // 序列号
        private long lastTimestamp = -1L; // 上一次时间戳

        public SnowflakeUtil(long workerId, long datacenterId) {
            if (workerId > maxWorkerId || workerId < 0) {
                throw new IllegalArgumentException(
                        String.format("worker Id can't be greater than %d or less than 0", maxWorkerId));
            }
            if (datacenterId > maxDatacenterId || datacenterId < 0) {
                throw new IllegalArgumentException(
                        String.format("datacenter Id can't be greater than %d or less than 0", maxDatacenterId));
            }
            this.workerId = workerId;
            this.datacenterId = datacenterId;
        }

        public synchronized long nextId() {
            long timestamp = timeGen();
            if (timestamp < lastTimestamp) {
                throw new RuntimeException(String.format(
                        "Clock moved backwards.  Refusing to generate id for %d milliseconds", lastTimestamp - timestamp));
            }
            if (timestamp == lastTimestamp) {
                sequence = (sequence + 1) & maxSequence;
                if (sequence == 0L) {
                    timestamp = tilNextMillis();
                }
            } else {
                sequence = 0L;
            }
            lastTimestamp = timestamp;

            return (timestamp - twepoch) << timestampShift // 时间戳部分
                    | datacenterId << datacenterIdShift // 数据中心部分
                    | workerId << workerIdShift // 机器标识部分
                    | sequence; // 序列号部分
        }

        private long tilNextMillis() {
            long timestamp = timeGen();
            while (timestamp <= lastTimestamp) {
                timestamp = timeGen();
            }
            return timestamp;
        }

        private long timeGen() {
            return System.currentTimeMillis();
        }
    }
}

2、上传接口controller

   /**  注意这里的 Claims 只是我这边获取用户信息的代码，
   	 * 如果你们公司获取用户信息不是这，请修改
     * @description  图书增量导入校验
     * @date 2021/11/19 10:38
     * @param 
     * @return 
     */
    @PostMapping("/vifImpBook")
    public ResponseJson vifImpBook(@RequestParam("file") MultipartFile file){
        Claims claims = (Claims) getSession().getAttribute("userInfo");
        if(null == claims.get("userId")){
            throw new BusinessException(ExceptionEnum.USER_LOGIN_FAILED);
        }
        String userName = claims.get("userName").toString();
        return offlineService.vifImpBook(file, userName);
    }

3、service 实现

/**
     * @author wzw
     * @description  图书增量导入
     * @date 2021/11/19 10:38
     * @param 
     * @return 
     */
    @Override
    public ResponseJson vifImpBook(MultipartFile userExcel, String userName) {
        if (!UploadUtil.checkExtension(userExcel)) {
            return ResponseJson.error("100", "请求文件类型错误:后缀名错误", null);
        }
        int index = 1;
        try {
            if (UploadUtil.isOfficeFile(userExcel)) {
                //正确的文件类型 自动判断2003或者2007
                Workbook workbook = UploadUtil.getWorkbookAuto(userExcel);
                Sheet sheet = workbook.getSheetAt(0);//默认只有一个sheet
                int rows = sheet.getPhysicalNumberOfRows();//获得sheet有多少行
                // 校验必填字段
                Map<String, Object> map = vifMustFiedls(rows, sheet,userName);
                return new ResponseJson("200", "success", map);
            } else {
                return ResponseJson.error("100", "未读取到上传文件数据", null);
            }
        } catch (IOException e) {
            log.info("importBook", e);
            return ResponseJson.error("100", String.format("当前上传文件第%d行有错误，请检查", index), null);
        }
    }

4、校验导入的数据是否符合业务标准

 /**
     * @description 校验excle 字段
     * @date 2021/11/19 16:02
     * @param
     * @return
     */
    private Map<String, Object> vifMustFiedls(int rows, Sheet sheet, String userName) {
    	// 这一步是为了获取表中所有数据，避免重复查库，因为业务需要校验表中存在的不能继续添加，可根据自己公司业务来是否需要这一步。
        List<OfflineBookEntity> offlineBookList = offlineBookDao.getOfflineBookList(null, null, null, null);
        List<String> list = new ArrayList<>();
        Set<String> set = new HashSet<>();
        List<Map<String, String>> successList = new ArrayList<>();
        Map<String, Object> resMap = new HashMap<>();
        boolean f = true;
        int r = 1;
        for (int i = 1; i < rows; i++) {
            r = i + 1;
            StringBuilder sb = new StringBuilder();
            Map<String, String> map = new HashMap<>();
            Row row = sheet.getRow(i);
            String barCode = cellStringValue(row, 0);
            String name = cellStringValue(row, 1);
            String author = cellStringValue(row, 2);
            String press = cellStringValue(row, 3);
            String publishDate = cellStringValue(row, 4);
            String state = cellStringValue(row, 5).equals("是") ? "1" : "2";
            String price = cellStringValue(row, 6);
            String stock = cellStringValue(row, 7);
            // 校验图书条形码是否存在
            if(StringUtils.isEmpty(barCode)){
                sb.append("第"+r+"行图书条形码为空，");
                f = false;
            }
            if(StringUtils.isEmpty(name)){
                sb.append("第"+r+"行图书名称为空，");
                f = false;
            }
            if(StringUtils.isEmpty(author)){
                sb.append("第"+r+"行作者为空，");
                f = false;
            }
            if(StringUtils.isEmpty(press)){
                sb.append("第"+r+"行出版社为空，");
                f = false;
            }
            if(StringUtils.isEmpty(state)){
                sb.append("第"+r+"行是否可借阅为空，");
                f = false;
            }
            if(StringUtils.isEmpty(price)){
                sb.append("第"+r+"行价格为空，");
                f = false;
            }
            if(StringUtils.isEmpty(stock)){
                sb.append("第"+r+"行库存数量为空，");
                f = false;
            }
            if(set.contains(barCode)){
                sb.append("第"+r+"行图书条形码重复，");
                f = false;
            }
            if(null == CommonUtil.parse(publishDate)){
                sb.append("第"+r+"行出版日期格式错误，");
                f = false;
            }
            for (int j = 0; j < offlineBookList.size(); j++) {
                if(offlineBookList.get(j).getBarCode().equals(barCode)){
                    sb.append("第"+r+"行图书条形码已存在；");
                    f = false;
                }
            }
            set.add(barCode);
            if(!f){
                list.add(sb.toString());
                f = true;
               continue;
            }else{
                map.put("barCode",barCode);
                map.put("name",name);
                map.put("author",author);
                map.put("press",press);
                map.put("publishDate",publishDate);
                map.put("state",state);
                map.put("price",price);
                map.put("stock",stock);
                map.put("times","0");
                map.put("typeCode","0");
                map.put("createBy",userName);
                map.put("updateBy",userName);
                successList.add(map);
            }
        }
        resMap.put("successList", successList) ;
        resMap.put("errorList", list);
        return resMap;
    }

    /**
     * 获取单元格字符串
     *
     * @param row
     * @param i
     * @return
     */
    private String cellStringValue(Row row, int i) {
        Cell cell = row.getCell(i);
        if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) {
            return "";
        }
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

5、结果

6、注意我这里是因为前端需要展示正确导入的数据和不符合的错误数据，所以没有直接添加到数据库，如果你们公司不需要前端展示，那么在校验完之后就可以直接调用dao层入库