java使用excel做数据的导入导出

5 篇文章 0 订阅
4 篇文章 0 订阅

业务逻辑简单,提供数据模板,实现数据的导入导出

话不多说,直接上代码

1.controller层提供模板下载,导入导出接口

package com.zdxf.server.component.govern.controller.cam.community.affairManagement;

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zdxf.server.common.base.model.Result;
import com.zdxf.server.component.govern.model.cam.community.affairManagement.CamCommunityworkerManagementT;
import com.zdxf.server.component.govern.service.cam.community.affairManagement.CamCommunityworkerManagementTService;
import com.zdxf.server.component.govern.vo.request.cam.community.affairManagement.CamCommunityworkerManagementVOImport;
import com.zdxf.server.component.govern.vo.request.cam.community.affairManagement.ReqCamCommunityworkerManagementVO;
import com.zdxf.server.component.govern.vo.response.cam.community.affairManagement.ResCamCommunityworkerManagement;
import com.zdxf.server.component.govern.vo.response.cam.community.affairManagement.ResCamCommunityworkerManagementVO;
import com.zdxf.server.component.security.oplog.annotation.AddOperationLog;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.Objects;

@Api(tags = "社区事务管理-社工管理")
@Slf4j
@RestController
@RequestMapping("/CamCommunityworkerManagementT")
public class CamCommunityworkerManagementTController {

    @Autowired
    private CamCommunityworkerManagementTService camCommunityworkerManagementTService;

    
    @GetMapping("/download/CommunityworkerTemplate")
    @ApiOperation(value = "下载社工导入模版")
    public void downloadTemplate(HttpServletResponse response) {
        try {
            ClassPathResource classPathResource = new ClassPathResource("template/社工数据摸板.xlsx");
            // File file = new File("classpath:template/经济组织导入模版.xlsx");
            File file = classPathResource.getFile();
            ServletOutputStream out = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("社工数据摸板", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            InputStream in = new FileInputStream(file);
            int len;
            while ((len = in.read()) != -1) {
                out.write(len);
            }
        } catch (Exception e) {
            e.printStackTrace();
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            JSONObject r = new JSONObject();
            r.put("code", "500");
            r.put("msg", "下载模版失败,请重试或联系管理员");
            try (PrintWriter writer = response.getWriter()) {
                writer.println(JSON.toJSONString(r));
                response.getOutputStream().close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }

    @PostMapping("/batchImportCommunityData")
    @ApiOperation("社工数据批量导入")
    public Result<Map<String,String>> batchImportCommunityData(@RequestBody MultipartFile file) throws IOException {
        if(!Objects.isNull(file) && !file.isEmpty()){
            List<CamCommunityworkerManagementVOImport> list =EasyExcel.read(file.getInputStream(),CamCommunityworkerManagementVOImport.class,null).sheet(0).doReadSync();
            if (null!=list){
              return  Result.succeed(camCommunityworkerManagementTService.batchImport(list));
            }
        }
        return Result.failed(null, "import failed");
    }

    @PostMapping("/batchExportCommunityData")
    @ApiOperation("社工批量导出")
    public void batchExportCommunityData(@RequestBody @ApiParam(value = "查询条件",required =false)
                                                     ReqCamCommunityworkerManagementVO req,HttpServletResponse response){
        List<ResCamCommunityworkerManagement> records=camCommunityworkerManagementTService.batchExport(req);
        String name="社工数据导出文档";

        try {
            ServletOutputStream outputStream= response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode(name, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            EasyExcel
                    .write(outputStream, ResCamCommunityworkerManagement.class)
                    .autoCloseStream(Boolean.FALSE)
                    .sheet(name)
                    .doWrite(records);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            JSONObject r = new JSONObject();
            r.put("code", "500");
            r.put("msg", "导出失败,请重试或联系管理员");
            try (PrintWriter writer = response.getWriter()) {
                writer.println(JSON.toJSONString(r));
                response.getOutputStream().close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }


    }
}

模板位置就在resource下面,如图所示

2.数据导入的时候所需要接受数据的实体

package com.zdxf.server.component.govern.vo.request.cam.community.affairManagement;

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

@ApiModel(value="CamCommunityworkerManagementVOImport",description="社工数据导入")
@Data
public class CamCommunityworkerManagementVOImport implements Serializable {

   private static final long serialVersionUID = 3723217067752208710L;

    /**
     * 名字
     */
  @ExcelProperty(value = "姓名", index = 0)
  private String column1;
  @ExcelProperty(value = "性别", index = 1)
  private String column2;
  @ExcelProperty(value = "身份证号", index = 2)
  private String column3;
  @ExcelProperty(value = "民族", index = 3)
  private String column4;
  @ExcelProperty(value = "职务", index = 4)
  private String column5;
  @ExcelProperty(value = "手机号", index = 5)
  private String column6;

}

3. 导出数据的时候所需要的的实体,@ColumnWidth(15) 表示导出excel中列的宽度,@ExcelProperty(value = "名字" 表示列        在excel中展示的名称

package com.zdxf.server.component.govern.vo.response.cam.community.affairManagement;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.baomidou.mybatisplus.annotation.TableId;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;

import java.io.Serializable;

@ApiModel(value="ResCamCommunityworkerManagement",description="社工数据导出")
public class ResCamCommunityworkerManagement implements Serializable {

   private static final long serialVersionUID = 3723217067752208710L;

    /**
     * 名字
     */
  @ApiModelProperty(value = "名字")
  @ExcelProperty(value = "名字")
  @ColumnWidth(15)
   private String name;

    /**
     * 性别 1:男, 2:女
     */
  @ApiModelProperty(value = "性别 1:男, 2:女")
  @ExcelProperty(value = "性别")
  @ColumnWidth(15)
   private String sex;

    /**
     * 身份证号
     */
  @ApiModelProperty(value = "身份证号")
  @ExcelProperty(value = "身份证号")
  @ColumnWidth(15)
   private String cardNo;

    /**
     * 民族
     */
  @ApiModelProperty(value = "民族")
  @ExcelProperty(value = "民族")
  @ColumnWidth(15)
   private String nation;

    /**
     * 职务
     */
  @ApiModelProperty(value = "职务")
  @ExcelProperty(value = "职务")
  @ColumnWidth(15)
   private String duty;

    /**
     * 手机号
     */
  @ApiModelProperty(value = "手机号")
  @ExcelProperty(value = "手机号")
  @ColumnWidth(15)
   private String phone;


  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }


  public String getSex() {
    return sex;
  }

  public void setSex(String sex) {
    this.sex = sex;
  }


  public String getCardNo() {
    return cardNo;
  }

  public void setCardNo(String cardNo) {
    this.cardNo = cardNo;
  }

    public String getNation() {
        return nation;
    }

    public void setNation(String nation) {
        this.nation = nation;
    }

    public String getDuty() {
    return duty;
  }

  public void setDuty(String duty) {
    this.duty = duty;
  }

  public String getPhone() {
    return phone;
  }

  public void setPhone(String phone) {
    this.phone = phone;
  }


}

4. 批量到出的接口service

package com.zdxf.server.component.govern.service.cam.community.affairManagement;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.IService;
import com.zdxf.server.common.base.model.Result;
import com.zdxf.server.component.govern.model.cam.community.affairManagement.CamCommunityworkerManagementT;
import com.zdxf.server.component.govern.vo.request.cam.community.affairManagement.CamCommunityworkerManagementVOImport;
import com.zdxf.server.component.govern.vo.request.cam.community.affairManagement.ReqCamCommunityworkerManagementVO;
import com.zdxf.server.component.govern.vo.response.cam.community.affairManagement.ResCamCommunityworkerManagement;
import com.zdxf.server.component.govern.vo.response.cam.community.affairManagement.ResCamCommunityworkerManagementVO;

import java.util.List;
import java.util.Map;

public interface CamCommunityworkerManagementTService extends IService<CamCommunityworkerManagementT> {
    Map<String, String> batchImport(List<CamCommunityworkerManagementVOImport> list);
    List<ResCamCommunityworkerManagement> batchExport(ReqCamCommunityworkerManagementVO req);

}

5.接口的具体实现,导出的时候是按条件查询之后的结果,导入的时候for循环从1开始,否则会把第一行标题也会读取

package com.zdxf.server.component.govern.service.impl.cam.community.affairManagement;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zdxf.server.common.base.model.Result;
import com.zdxf.server.common.base.utils.DateUtils;
import com.zdxf.server.component.common.util.SecurityUserUtil;
import com.zdxf.server.component.govern.mapper.cam.community.affairManagement.CamCommunityworkerManagementTMapper;
import com.zdxf.server.component.govern.model.TplUserT;
import com.zdxf.server.component.govern.model.cam.community.affairManagement.CamCommunityworkerManagementT;
import com.zdxf.server.component.govern.service.cam.community.affairManagement.CamCommunityworkerManagementTService;
import com.zdxf.server.component.govern.vo.TplRoleAccountT;
import com.zdxf.server.component.govern.vo.request.cam.community.affairManagement.CamCommunityworkerManagementVOImport;
import com.zdxf.server.component.govern.vo.request.cam.community.affairManagement.ReqCamCommunityworkerManagementVO;
import com.zdxf.server.component.govern.vo.response.cam.community.affairManagement.ResCamCommunityworkerManagement;
import com.zdxf.server.component.govern.vo.response.cam.community.affairManagement.ResCamCommunityworkerManagementVO;
import com.zdxf.server.component.security.agency.service.impl.AgencyServiceImpl;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;

@Service
public class CamCommunityworkerManagementTServiceImpl extends ServiceImpl<CamCommunityworkerManagementTMapper, CamCommunityworkerManagementT> implements CamCommunityworkerManagementTService {

    @Autowired
    private CamCommunityworkerManagementTMapper mapper;

    @Autowired
    AgencyServiceImpl agencyService;
   

    @Override
    public Map<String, String> batchImport(List<CamCommunityworkerManagementVOImport> list) {

        // 导入信息返回容器
        Map<String, String> importInfo = new HashMap<>();
        // 批处理容器
        List<CamCommunityworkerManagementT> endList = new ArrayList<>();

        for (int i=1;i<list.size();i++){
            CamCommunityworkerManagementVOImport ccm=list.get(i);
            CamCommunityworkerManagementT cwm=new CamCommunityworkerManagementT();

            cwm.setName(ccm.getColumn1());
            if(StringUtils.isNotBlank(ccm.getColumn2())){
                if("女".equals(ccm.getColumn2())){
                    cwm.setSex(2L);
                }else{
                    cwm.setSex(1L);
                }
            }else {
                //性别:默认给男
                cwm.setSex(1L);
            }

            if(StringUtils.isNotBlank(ccm.getColumn3())){
                QueryWrapper<CamCommunityworkerManagementT> qw=new QueryWrapper();
                qw.eq("card_no",ccm.getColumn3());
                List<CamCommunityworkerManagementT> list1=mapper.selectList(qw);
                if (null!=list1 && list1.size()>0){
                    importInfo.put("第"+i+"条数据处理失败,身份证号已存在",ccm.getColumn3());
                    continue;
                }else{
                    cwm.setCardNo(ccm.getColumn3());
                }

            }else{
                importInfo.put("第"+i+"条数据处理失败,身份证号不能为空",ccm.getColumn3());
                continue;
            }
            // 名族  为空的时候默认给汉族
            if(StringUtils.isNotBlank(ccm.getColumn4())){
                cwm.setNation(ccm.getColumn4());
            }else{
                cwm.setNation("汉族");
            }
            cwm.setDuty(ccm.getColumn5());
            cwm.setPhone(ccm.getColumn6());
            cwm.setCreateBy(SecurityUserUtil.obtainUser().getId());
            cwm.setDefault1(SecurityUserUtil.obtainAccount().getTplAgencyT().getId().toString());
            endList.add(cwm);
        }
        if(null!=endList && endList.size()>0){
            if(this.saveBatch(endList)){
                importInfo.put("社工数据导入成功",endList.toString());
            }
        }else{
            importInfo.put("社工数据导入失败",endList.toString());
        }

        return importInfo;
    }

    @Override
    public List<ResCamCommunityworkerManagement> batchExport(ReqCamCommunityworkerManagementVO req) {
        List<Long> agencyIds = agencyService.getCurrentAgencyIds();
        QueryWrapper<CamCommunityworkerManagementT> qw =new QueryWrapper<>();

        List<ResCamCommunityworkerManagement> resList=new ArrayList<>();

        if(!org.springframework.util.StringUtils.isEmpty(req)){
            if(StringUtils.isNotBlank(req.getDuty())){
                qw.like("duty",req.getDuty());
            }
            if(StringUtils.isNotBlank(req.getName())){
                qw.like("name",req.getName());
            }
            if(StringUtils.isNotBlank(req.getPhone())){
                qw.like("phone",req.getPhone());
            }
            qw.in("default1",agencyIds);
        }
        List<CamCommunityworkerManagementT> list=mapper.selectList(qw);
        if(null!=list && list.size()>0){
            for(CamCommunityworkerManagementT cm:list){
                ResCamCommunityworkerManagement res=new ResCamCommunityworkerManagement();
                res.setName(cm.getName());
                res.setSex(cm.getSex().toString());
                res.setCardNo(cm.getCardNo());
                res.setDuty(cm.getDuty());
                res.setNation(cm.getNation());
                res.setPhone(cm.getPhone());
                resList.add(res);
            }
        }
        return resList;
    }

}

 

  • 3
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 6
    评论
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值