java解析Excel文件，接口导入Excel文件，解析

最新推荐文章于 2024-09-23 11:00:00 发布

南清北风呀

最新推荐文章于 2024-09-23 11:00:00 发布

阅读量664

点赞数

分类专栏： 2020-2021实习

本文链接：https://blog.csdn.net/qq_43533700/article/details/115716862

版权

2020-2021实习专栏收录该内容

10 篇文章 0 订阅

订阅专栏

该博客主要介绍了如何通过POST接口实现批量导入Excel数据。首先，接口接收FilePart类型的文件参数，然后使用FileUtils将其转换为File对象。接着，利用ExcelUtil的readExcelToEntity方法读取Excel内容并映射到ImportPostSaleNoDto实体类，进行数据验证和去重操作，最后将数据注入对象并返回处理结果。

摘要由CSDN通过智能技术生成

接口参数

@PostMapping(value = Hplus.platform.trade.batch_post_sale, consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
    public Mono<HplusResponse<BatchPostSaleByPosSaleNoResultBo>> batchPostSaleByPostSaleNoList(@RequestPart(name = "file") FilePart filePart,
                                                                                               @RequestParam("serviceOrganCode") String serviceOrganCode) {
            File file = FileUtils.transferTo(filePart);
            Assert.isTrue(file.exists(), "文件上传失败");
            //读取Excel中的数据，并将每一行的数据映射为实体类ImportPostSaleNoDto
            List<ImportPostSaleNoDto> importPostSaleNoList = ExcelUtil.readExcelToEntity(ImportPostSaleNoDto.class, file, 2);
            Assert.isTrue(CollectionUtils.isNotEmpty(importPostSaleNoList), "数据不能为空");
            List<String> importPostSaleNos = importPostSaleNoList.stream().map(ImportPostSaleNoDto::getPostSaleNo).collect(Collectors.toList());
            Assert.isTrue(importPostSaleNoList.size() <= 1000, "最多导入1000条");
            HashSet<String> distinctList = new HashSet<>(importPostSaleNos);
            Assert.isTrue(importPostSaleNos.size() == distinctList.size(), "数据有重复,无法导入,请先去重");
      }

ExcelUtil.readExcelToEntity(Class clazz, File file, int beginRow)方法详解


    /**
     * @param clazz 实体
     * @param file  文件
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcelToEntity(Class<T> clazz, File file, int beginRow) {
        try {
            return readExcelToEntity(clazz, new FileInputStream(file), file.getName(), beginRow);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

readExcelToEntity(Class clazz, InputStream in, String fileName, int beginRow)方法


    /**
     * @param clazz    实体靶向
     * @param in       文件流
     * @param fileName 文件名称
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> List<T> readExcelToEntity(Class<T> clazz, InputStream in, String fileName, int beginRow) {
//        checkFile(fileName);
        Workbook workbook = createWorkbook(in, fileName);
        Sheet sheet = workbook.getSheetAt(0);
        return getData(clazz, sheet, beginRow);
    }

createWorkbook(InputStream in, String fileName)方法详解

private static Workbook createWorkbook(InputStream in, String fileName) {
        try {
            if (fileName.endsWith(".xls")) {
                return new HSSFWorkbook(in);
            } else {
                return new XSSFWorkbook(in);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

getData(Class clazz, Sheet sheet, int beginRow)

private static <T> List<T> getData(Class<T> clazz, Sheet sheet, int beginRow) {
        try {
            //创建属性map
            HashMap<String, String> valueMap = new HashMap<>();
            Field[] fields = clazz.getDeclaredFields();
            List<T> beanList = new ArrayList<>(1_000);

            //遍历excel文件
            T t;
            Cell cell;
            Row row;
            for (int x = sheet.getFirstRowNum() + beginRow - 1, y = sheet.getLastRowNum(); x <= y; x++) {
                row = sheet.getRow(x);
                for (int i = 0; i < fields.length; i++) {
                    cell = row.getCell(i);
                    String cellValue = getCellValueAsString(cell);
                    valueMap.put(fields[i].getName(), cellValue == null ? null : cellValue.trim());
                }
                t = clazz.getDeclaredConstructor().newInstance();
                //使用BeanUtils将封装的属性注入对象
                BeanUtils.populate(t, valueMap);
                //将对象添加至容器
                beanList.add(t);
            }
            return beanList;
        }
        catch (Exception e) {
            throw new RuntimeException(e);
        }
    }