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

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

话不多说，直接上代码

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; } }