通过Excel批量导入数据-Java代码

1. 依赖

        <!-- excel导出 start-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        <!-- excel导出 end-->

2. 导入工具类

/**
     * 将 Excel内容转为List
     *
     * @param file 上传的Excel文件
     * @param name Excel表格中第一个sheet的名字
     * @return 对应的map集合
     **/
    public static List<Map<String, Object>> excelToList(MultipartFile file, String name) throws Exception {
　　　　try(InputStream inputStream = file.getInputStream())
        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheet(name);
        //行数
        int num = sheet.getLastRowNum();
        //列数
        int col = sheet.getRow(0).getLastCellNum();
        List<Map<String, Object>> list = new ArrayList<>();
        String[] colName = new String[col];
        //获取列名
        Row row = sheet.getRow(0);
        for (int i = 0; i < col; i++) {
            String[] s = row.getCell(i).getStringCellValue().split("-");
            colName[i] = s[0];
        }

        //将一行中每列数据放入一个map中,然后把map放入list
        for (int i = 1; i <= num; i++) {
            Map<String, Object> map = new HashMap<>();
            Row row1 = sheet.getRow(i);
            if (row1 != null) {
                for (int j = 0; j < col; j++) {
                    Cell cell = row1.getCell(j);
                    if (cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        map.put(colName[j], cell.getStringCellValue());
                    } else {
　　　　　　　　　　　　　　 map.put(colName[j], null);
　　　　　　　　　　　　}
                }
            }
            list.add(map);
        }
        return list.stream().filter(e -> !checkMap(e)).collect(Collectors.toList());
　　　　}catch(Exception e){
　　　　　　log.error("导入失败：{}",e);
　　　　　　throw e;
　　　　}
    }
/*判断map是否为空，true-所有元素为空*/

private static boolean checkMap(Map<String, Object> map) {
    List<Object> list = map.values().stream().filter(e->StringUtils.isNotBlank(String.valueOf(e))).collect(Collectors.toList());
    return list.size() == 0 ? true : false;
}

3. 导入业务代码

public void importExcel(MultipartFile file) {
        try {
            List<Map<String, Object>> importList = ExcelUtil.excelToList(file, "Sheet1");
            List<User> list = getUserList(importList);
            int flag = 0;
            List<User> userList = new ArrayList<>(16);
            for (User u : list) {
                userList.add(h);
                flag++;
                if (0 == flag % 500 || flag == list.size()) {
                    UserDao.addBatchUser(userList);
                    userList.clear();
                    log.info("执行前 {} 条成功", flag);
                }
            }
        } catch (Exception e) {
            log.info("导入Excel失败");
            e.printStackTrace();
        }
    }

    private List<User> getUserList(List<Map<String, Object>> importList) {
        List<User> list = new ArrayList<>(16);
        importList.forEach(e -> {
            User user = new User();
            if (Objects.nonNull(e.get("姓名"))) {
                user.setUsername(e.get("姓名").toString());
            }
            if (Objects.nonNull(e.get("年龄"))) {
                user.setAge(e.get("年龄").toString());
            }
            if (Objects.nonNull(e.get("手机号"))) {
                user.setPhone(e.get("手机号").toString());
            }
            list.add(user);
        });
        return list;
    }

 ==========================================================================================

1. 导出Excel工具类

/**
     * 导出 excel
     *
     * @param response 响应下载
     * @param name 文件名字
     * @param list 导出的数据list
     * @return
     **/
    public static void exportToExcel(HttpServletResponse response, String name, List<LinkedHashMap<String, Object>> list) {
        try {
            //文件名称
            String fileName = name + ".xls";
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            HSSFSheet hssfSheet = hssfWorkbook.createSheet(name);
            int rowNum = 0;
            //新建行
            HSSFRow hssfRow = hssfSheet.createRow(rowNum++);
            //列
            int j = 0;
            if (list.size() > 0) {
                for (String i : list.get(0).keySet()) {
                    //新建第一行
                    hssfRow.createCell(j++).setCellValue(i);
                }
                //将数据放入表中
                for (int i = 0; i < list.size(); i++) {
                    //新建一行
                    HSSFRow row = hssfSheet.createRow(rowNum++);
                    Map map = list.get(i);
                    System.out.println(map);
                    j = 0;
                    for (Object obj : map.values()) {
                        if (obj != null) {
                            row.createCell(j++).setCellValue(obj.toString());
                        } else {
                            row.createCell(j++);
                        }
                    }
                }
            }
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            hssfWorkbook.write(response.getOutputStream());

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

2. 导出业务代码

    public void exportExcel(HttpServletResponse response, String name) {
        List<LinkedHashMap<String, Object>> list = new ArrayList<>(16);
        UserDto userDto = new UserDto(18);//设置导出哪些数据
        List<User> userList = userDao.selectByCon(userDto);
        userList.forEach(e->{
　　　　　　　LinkedHashMap<String, Object> map = new LinkedHashMap<>(16);
            map.put("姓名",e.getUsername());
            map.put("年龄",e.getAge());
            map.put("手机号",e.getPhone());
            list.add(map);
        });
        ExcelUtil.exportToExcel(response,name,list);
    }

3. 可以在浏览器访问接口，直接下载，也可以调用postman->send and download请求下载