1、模板下载

 public void downLoad(HttpServletRequest request, HttpServletResponse response) {
    

        try(XSSFWorkbook xssfWorkbook = new XSSFWorkbook();) {
    
            Sheet hssfSheet =xssfWorkbook.createSheet();
        hssfSheet.setDefaultColumnWidth(20);
        Row defaulhssfRow = hssfSheet.createRow(0);
        Cell hssfCell = defaulhssfRow.createCell(0);
            hssfCell.setCellValue("组织机构名称");
            hssfCell = defaulhssfRow.createCell(1);
            hssfCell.setCellValue("排序");
        response.setContentType("application/octet-stream;charset=UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode("组织结构导入","utf-8") +".xlsx");//Excel文件名
            response.flushBuffer();
            xssfWorkbook.write(response.getOutputStream());
        } catch (IOException e) {
    
            log.error(e.getMessage(),e);
        }
    }

2、数据下载

  public XSSFWorkbook downLoadShopMember(LoginUser loginUser) {
    
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        Sheet sheet =xssfWorkbook.createSheet();
        sheet.setDefaultColumnWidth(20);
        Row row = sheet.createRow(0);
        for(int i=0;i<DOWN_EXCEL_TITLE.length;i++){
    
            Cell cell = row.createCell(i);
            cell.setCellValue(DOWN_EXCEL_TITLE[i]);
        }
        ShopMemberDTO shopMemberDTO = new ShopMemberDTO();
        ShopMemberEntity shopMemberEntity =shopMemberService.getByUserId(loginUser.getUserId());
        shopMemberDTO.setOrgId(shopMemberEntity.getOrgId());
        List<ShopMemberVO> shopMemberVOList= shopMemberMapper.findShopMemberList(shopMemberDTO);
        int i=1;
        for(ShopMemberVO shopMemberVO:shopMemberVOList){
    
            Row row1 = sheet.createRow(i);
            Cell cell = row1.createCell(0);
            cell.setCellValue(shopMemberVO.getNickName());
            Cell cell1 = row1.createCell(1);
            cell1.setCellValue(shopMemberVO.getMobile());
            Cell cell2 = row1.createCell(2);
            cell2.setCellValue(shopMemberVO.getTitle());
            Cell cell3 = row1.createCell(3);
            cell3.setCellValue(shopMemberVO.getStatus()==1?"正常":"停用");
            Cell cell4 = row1.createCell(4);
            cell4.setCellValue(shopMemberVO.getOrgName());
            Cell cell5 = row1.createCell(5);
            cell5.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(shopMemberVO.getCreateDate()));
            i++;
        }
        return xssfWorkbook;
    }

3、数据导入

public Response importOrganizationExcel(MultipartFile multipartFile, LoginUser loginUser) {
    
        if(loginUser==null || loginUser.getUserId()==null){
    
            return ResponseUtils.returnFailMsg("当前请求非法，请先登录系统在操作");
        }
        String fileName =multipartFile.getOriginalFilename();
        if(!fileName.endsWith(".xlsx")){
    
            return ResponseUtils.returnFailMsg("文件格式不正确");
        }
     try(XSSFWorkbook workbook =new XSSFWorkbook(multipartFile.getInputStream());){
    
        ShopMemberEntity shopMemberEntity =shopMemberService.getByUserId(loginUser.getUserId());
        Sheet sheet = workbook.getSheetAt(0);
        List<SysOrganizationEntity> organizationEntityList =new ArrayList();
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
    
                SysOrganizationEntity newOrg = new SysOrganizationEntity();
                Row row = sheet.getRow(i);
                String name = row.getCell(0).getStringCellValue();
                Cell cell = row.getCell(1);
                if (cell != null ) {
    
                    Double orderSort=cell.getNumericCellValue();
                    newOrg.setOrderSort(orderSort.intValue());
                }
                newOrg.setName(name);
                newOrg.setLevel(3);
                newOrg.setCreateDate(new Date());
                newOrg.setPid(shopMemberEntity.getOrgId());
                newOrg.setCreator(loginUser.getLongName());
                organizationEntityList.add(newOrg);
            }

         organizationService.insertOriginBatch(organizationEntityList);
         return ResponseUtils.returnSuccess();
        }catch (IOException |NumberFormatException e){
    
            log.error(e.getMessage(),e);
            return ResponseUtils.returnFailMsg("排序字段必须为数字类型");
        }
    }