excel导入

需要相关包

import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;

 @PostMapping(value = "import/excel")
    @Authority
    public Result importExcel(@RequestParam(value = "file") MultipartFile file, @ApiIgnore @User CurrentUser currentUser) {
        String fileName = DateUtil.format(DateUtil.date(), DatePattern.PURE_DATETIME_MS_PATTERN) + ".xls";
        ExcelWriter write = null;
        try {
            InputStream inputStream = file.getInputStream();
            List<Map<String, Object>> mapList = ExcelUtil.getReader(inputStream).readAll();
            if (mapList.isEmpty()) {
                return ResultGenerator.genFailResult(CommonCode.SERVICE_ERROR, "请检查上传文件，无数据", null);
            }
            List<Map<String, Object>> errorList = new ArrayList<>(10);
            int count = shippingBillService.importExcel(currentUser, mapList, errorList);
            String responsePath = null;
            if (!errorList.isEmpty()) {
                //写出错误提示文件
                write = new ExcelWriter(importPath + fileName);
                StyleSet style = write.getStyleSet();
                style.setBackgroundColor(IndexedColors.ORANGE, false);
                write.write(errorList, true);
                responsePath = excelErrorUrl + fileName;
            }
            return ResultGenerator.genFailResult(CommonCode.SUCCESS, "成功：" + count + "条", responsePath);
        } catch (IOException e) {
            StaticLog.error("导入运输单失败： " + e.getMessage());
            return ResultGenerator.genFailResult(CommonCode.SERVICE_ERROR, "读取excel文件出错", null);
        } finally {
            IoUtil.close(write);
        }
    }

主要方法：

InputStream inputStream = file.getInputStream(); 获取上传得文件流

List<Map<String, Object>> mapList = ExcelUtil.getReader(inputStream).readAll(); 获取excel文件得头(标题)

int count = shippingBillService.importExcel(currentUser, mapList, errorList);  导入excel方法

@Override
    public Integer importExcel(CurrentUser currentUser, List<Map<String, Object>> mapList, List<Map<String, Object>> errorList) {
        ShippingBill shippingBill;
        List<ShippingBill> shippingBillList = new ArrayList<>(50);
        for (Map<String, Object> map : mapList) {
            shippingBill = new ShippingBill();
            shippingBill.setZipCode(Convert.toStr(map.get("邮编")));
            shippingBill.setZip4Code(Convert.toStr(map.get("zip4邮编")));
            shippingBill.setPhone(Convert.toStr(map.get("收件人电话")));
            shippingBill.setEmail(Convert.toStr(map.get("收件人邮箱")));
            shippingBillList.add(shippingBill);
        }
        if (shippingBillList.isEmpty()) {
            return 0;
        }
        return shippingBillMapper.insertForeach(shippingBillList);
    }

主要方法：遍历excel中传过来得mapList每一项。并封装到对象集合中，保存到数据库