导入导出表格数据

后端代码：

导出数据

  @Override
    public void export(HttpServletRequest request, HttpServletResponse response,Integer id)  {
        //获取时间随机数
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmssSSS");
        String fileName = LocalDateTime.now(ZoneOffset.of("+8")).format(formatter);
        //获取某条件下的对象集合
        Example example=new Example(TrainActivityPer.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andEqualTo("activityId",id);
        List<TrainActivityPer> trainActivityPers = dao.selectByExample(example);
        //列名
        Map<String, String> headMap =new LinkedHashMap<>();
        headMap.put( "no", "序号" );
        headMap.put( "companyName", "公司全称" );
        headMap.put( "name", "参会人员" );
        headMap.put( "phone", "联系方式" );
        headMap.put( "post", "职务" );
        headMap.put( "signStatus", "报名状态" );
        headMap.put( "registerStatus", "签到状态" );
        headMap.put( "evaluateStatus", "评价状态" );
        //表格数据
        List<Map<String,Object>> list=new ArrayList<>();
        for(int i=0;i<trainActivityPers.size();i++){
            TrainActivityPer activityPer=trainActivityPers.get(i);
            Map<String,Object>map1=new HashMap<>();
            map1.put("no",i+1);
            //TrainActivity trainActivity = activityDao.selectByPrimaryKey(id);
            //map1.put("activity",trainActivity.getActivityName());
            map1.put("companyName", coCompanyInfoDAO.selectByPrimaryKey(activityPer.getCompanyId()).getCompanyName());
            map1.put("name",activityPer.getName());
            map1.put("phone",activityPer.getPhone());
            map1.put("post",activityPer.getPost());
            map1.put("signStatus",activityPer.getSignStatus());
            map1.put("registerStatus",activityPer.getRegisterStatus());
            map1.put("evaluateStatus",activityPer.getEvaluateStatus());
            list.add(map1);
        }
        try {
            ExcelUtils.exportXlsx(response,fileName,headMap,list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

导入数据：

 @Override
    public void perImport(MultipartFile file, Integer id) {
        //strings为设置对应的表列名有几列就取几个相对应
        String[] strings=new String[]{"id","company","name","phone","post","signStatus","registerStatus","evaluateStatus"};
        try {
            List<Map<String, Object>> list = ExcelUtils.importExcel(file, strings, true);
            System.out.println(list.size());
            for (int i = 0; i <list.size() ; i++) {
                Map<String, Object> map = list.get(i);
                TrainActivityPer tp =new TrainActivityPer();
                tp.setActivityId(id);
                tp.setName(String.valueOf(map.get("name")));
                tp.setPost(String.valueOf(map.get("post")));
                tp.setPhone(String.valueOf(map.get("phone")));
                tp.setSignStatus(String.valueOf(map.get("signStatus")));
                tp.setRegisterStatus(String.valueOf(map.get("registerStatus")));
                tp.setEvaluateStatus(String.valueOf(map.get("evaluateStatus")));
                Example example=new Example(CoCompanyInfo.class);
                Example.Criteria criteria = example.createCriteria();
                criteria.andEqualTo("companyName",String.valueOf(map.get("company")));
                CoCompanyInfo coCompanyInfo = coCompanyInfoDAO.selectOneByExample(example);
                tp.setCompanyId(coCompanyInfo.getId());
                dao.insert(tp);
            }

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

    }

工具类:

public class ExcelUtils {

    private static final Logger logger = LoggerFactory.getLogger(com.nbpi.company.modules.tag.utils.ExcelUtils.class);

    private static List<List<Object>> lineList = new ArrayList<>();


    /**
     * 检验文件是否有效
     *
     * @param file
     * @throws Exception
     */
    public static void checkFile(MultipartFile file) {
        String fileName = file.getOriginalFilename();
        // 上传文件为空
        if (StringUtils.isEmpty(fileName)) {
            throw CustomizeException.build(ErrorCode.NO_IMPORT_EXCEL_ERROR);
        }
        //上传文件大小为1000条数据
        if (file.getSize() > 1024 * 1024 * 10) {
            logger.error("upload | 上传失败: 文件大小超过10M，文件大小为：{}", file.getSize());
            throw CustomizeException.build(ErrorCode.EXCEL_TOO_LARGE_ERROR);
        }
        // 上传文件名格式不正确
        if (
                fileName.lastIndexOf(".") != -1
                        && !".xlsx".equals(fileName.substring(fileName.lastIndexOf(".")))
                        && !".xls".equals(fileName.substring(fileName.lastIndexOf(".")))
        ) {
            throw CustomizeException.build(ErrorCode.EXCEL_MISMATCH_ERROR);
        }
    }

    /**
     * @param response
     * @param fileName excel文件名
     * @param headMap  表头map
     * @param dataList 表格数据
     */
    public static void exportXlsx(HttpServletResponse response, String fileName,
                                    Map<String, String> headMap, List<Map<String, Object>> dataList) throws IOException {

        ArrayList<Map<String, Object>> maps = CollUtil.newArrayList(dataList);
        // 通过工具类创建writer，默认创建xls格式
        ExcelWriter writer = ExcelUtil.getWriter();
        Iterator<String> iterator = headMap.keySet().iterator();
        while (iterator.hasNext()){
            String next = iterator.next();
            writer.addHeaderAlias(next,headMap.get(next));
        }
        writer.write(maps,true);
        //out为OutputStream，需要写出到的目标流

        //response为HttpServletResponse对象
        response.setContentType("application/octet-stream");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
        ServletOutputStream out = response.getOutputStream();
        writer.flush(out, true);
        // 关闭writer，释放内存
        writer.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }

    /**
     * excel导入工具类
     *
     * @param file       文件
     * @param columNames 列对应的字段名
     * @return 返回数据集合
     * @throws IOException
     */
    public static List<Map<String, Object>> importExcel(MultipartFile file, String[] columNames, boolean isRemoveFirst) throws Exception {
        checkFile(file);
        //读取数据
        ExcelUtil.readBySax(file.getInputStream(), 0, createRowHandler());
        //去除excel中的第一行数据
        if (isRemoveFirst) {
            lineList.remove(0);
        }

        //将数据封装到list<Map>中
        List<Map<String, Object>> dataList = new ArrayList<>();
        for (int i = 0; i < lineList.size(); i++) {
            if (null != lineList.get(i)) {
                Map<String, Object> hashMap = new HashMap<>();
                for (int j = 0; j < columNames.length; j++) {
                    Object property = lineList.get(i).get(j);
                    hashMap.put(columNames[j], property);
                }
                dataList.add(hashMap);
            } else {
                break;
            }
        }
        return dataList;
    }

    /**
     * 通过实现handle方法编写我们要对每行数据的操作方式
     */
    private static RowHandler createRowHandler() {
        //清空一下集合中的数据
        lineList.removeAll(lineList);
        return new RowHandler() {
            @Override
            public void handle(int i, long l, List<Object> list) {
                lineList.add(list);
            }
        };
    }

}