获取Excel文件数据添加到数据库

编写代码——————

excel->数据库

实体类（注解内容与Excel表头字段相同）

导入jar包

   com.alibaba
   easyexcel
   1.0.1

package com.xueyi.tenant.domain.excel;

import com.xueyi.common.core.annotation.Excel;
import lombok.Data;

import java.util.Date;

@Data
public class TenantExcel {

    @Excel(name = "企业名称*")
    private String enterpriseName;

    @Excel(name = "登记状态*")
    private String registrationStatus;

    @Excel(name = "法定代表人*")
    private String legalRepresentative;

    @Excel(name = "注册资本*")
    private String registeredCapital;


    @Excel(name = "成立日期*")
    private Date establishDate;


    @Excel(name = "核准日期*")
    private Date approvalDate;


    @Excel(name = "所属省份*")
    private String province;


    @Excel(name = "所属城市*")
    private String city;



    @Excel(name = "所属区县*")
    private String area;


    @Excel(name = "电话*")
    private String phone;


    @Excel(name = "更多电话*")
    private String morePhone;


    @Excel(name = "邮箱*")
    private String mail;


    @Excel(name = "更多邮箱*")
    private String moreMail;

    @Excel(name = "统一社会信用代码*")
    private String unifiedSocialCreditCode;


    @Excel(name = "纳税人识别号*")
    private String taxpayerIdentificationCode;


    @Excel(name = "注册号*")
    private String registrationCode;


    @Excel(name = "组织机构代码*")
    private String organizationCode;


    @Excel(name = "参保人数*")
    private String insuredPeopleNumber;

    @Excel(name = "企业类型*")
    private String enterpriseType;

    @Excel(name = "所属行业*")
    private String industry;

    @Excel(name = "曾用名*")
    private String nameUsedBefore;

    @Excel(name = "英文名*")
    private String englishName;

    @Excel(name = "网址*")
    private String website;

    @Excel(name = "企业地址*")
    private String businessAddress;

    @Excel(name = "最新年报地址*")
    private String latestAnnualReportAddress;

    @Excel(name = "经营范围*")
    private String businessArea;

}

接收前端传递的Excel文件
使用@RequestParam注解来获取文件
注意@RequestParam注解类型必须为datafrom不是body
使用swagger测试有可能是body类型（坑）

   

    @Log(title = "Excel信息", businessType = BusinessType.EXPORT)
    @PostMapping("/upload")
    public boolean upload(@RequestParam("file") MultipartFile file) {
        System.out.println("导入的表："+file.getOriginalFilename());
        //调用业务接口
        //file获取到的文件
        //TenantExcel.class对应的实体类
        int tenants = tenantService.readExcelFile( file , TenantExcel.class);
        System.out.println(tenants);
        return false;
    }

编写实现类
service

  
    int addExcel(List tenantExcels);

    
    int readExcelFile(MultipartFile file , Class head);

serviceimpl

 
    @Override
    public int readExcelFile(MultipartFile file ,Class head) {
    	//创建集合保存数据TenantExcel类型
        List list = new ArrayList<>();
        //使用AnalysisEventListener添加数据到list
        //AnalysisEventListener会生成两个方法invoke、doAfterAllAnalysed 
        AnalysisEventListener analysisEventListener = new AnalysisEventListener() {
        	//此方法用于获取数据添加到list集合中
            @Override
            public void invoke(TenantExcel tenantExcel, AnalysisContext analysisContext) {
                list.add(tenantExcel);
            }
			//此方法提示Excel表数据已经获取完成
            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                System.out.println("所有数据解析完成！");
            }
        };
		//创建文件流
        InputStream inputStream;

        try {
            inputStream = file.getInputStream();
        }catch (IOException e){
            System.out.println("upload-InputStream-Exception："+e);
            return 0;
        }
        //相当于开始按钮开始获取Excel表
        EasyExcel.read(inputStream,head,analysisEventListener).sheet().doRead();
			//吧数据传到添加数据库方法中
            return addExcel(list);
    }

 
    @Override
    public int addExcel(List tenantExcels) {
			
        int num = 0;
        System.out.println("总条数"+tenantExcels.size());
        //遍历集合
        for (TenantExcel tenantExcel : tenantExcels){
       		//吧数据一条一条的添加进数据库
            System.out.println("数据"+tenantExcel );
           num = tenantMapper.addExcel(tenantExcel );
        }
        return num;
    }


   

编写mapper
excelmapper

	//添加数据库方法 链接mapper.xml
	//编写相应的sql语句即可
    int addExcel(Tenant Tenant);

根据实际字段编写sql语句

收工

查询数据库数据导入excel

实体类

package com.xueyi.tenant.domain;

import com.xueyi.common.core.annotation.Excel;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.time.LocalDate;
import java.util.Date;



@Data
public class TenantExcelOut {


    @Excel(name = "企业名称*")
    private String tenantNick;

    @Excel(name = "同意识别码*")
    private String unifiedSocialCreditCode;

    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @Excel(name = "开业日期*")
    private String establishDate;

    @Excel(name = "法人代表*")
    private String legalRepresentative;
	//date类型保存到excel会变成数字所以使用String类型（如有别的方法还可以使用别的方法）
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    @Excel(name = "核准日期*")
    private String approvalDate;

    @Excel(name = "归属地区*")
    private String aggregationArea;

    @Excel(name = "注册资本*")
    private String registeredCapital;

    @Excel(name = "注册详细地址*")
    private String businessAddress;

    @Excel(name = "企业状态*")
    private Integer auditStatus;


}

	@ApiOperation("导出企业管理列表")
    @GetMapping("/tenantExcelOut")
    public R tenantExcelOut(@RequestParam Date startDate, @RequestParam Date endDate, @RequestParam String mail) {
    	//使用rabbitmq
        iTenantService.tenantExcel(startDate,endDate,mail);
        return R.ok();
    }

service接口

    void tenantExcel(Date startDate,Date endDate,String mail);

serviceimpl实现

@Override
    public void tenantExcel(Date startDate, Date endDate, String mail) {
        Map map=new HashMap();
        map.put("startDate",startDate);
        map.put("endDate",endDate);
        map.put("mail",mail);
        String jsonMap = JSONObject.toJSONString(map);
		//使用rabbitmq        
        rabbitTemplate.convertAndSend(DirectRabbitConfig.TENANT_EXPORT_EXCHANGE,DirectRabbitConfig.TENANT_EXPORT_ROUTING,jsonMap);
    }

 @Resource
    TenantMapper tenantMapper;

    @Resource
    EmailCommonService emailCommonService;
//消费者接收消息执行
    @RabbitHandler
    @RabbitListener(queues = DirectRabbitConfig.TENANT_EXPORT_QUEUE ,exclusive = true)
    public void ProductOnShelfHandler(Message message, Channel channel) throws Exception {
        //手动应答消息
        channel.basicAck(message.getMessageProperties().getDeliveryTag(), true);
        System.out.println("企业消费者消费");
        Map map = JSONObject.parseObject(new String(message.getBody()), Map.class);
        System.out.println(map);
        List tenantExcelOut = tenantMapper.tenantExcelOut(new Date(Long.parseLong(map.get("startDate").toString())),
                new Date(Long.parseLong(map.get("endDate").toString())));
        //ExcelUtil
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立sheet对象
        HSSFSheet sheet=wb.createSheet("企业表");
        HSSFRow row1=sheet.createRow(0);
        row1.createCell(0).setCellValue("企业名称");
        row1.createCell(1).setCellValue("统一识别码");
        row1.createCell(2).setCellValue("开业日期");
        row1.createCell(3).setCellValue("法人代表");
        row1.createCell(4).setCellValue("核准日期");
        row1.createCell(5).setCellValue("归属地区");
        row1.createCell(6).setCellValue("注册资本");
        row1.createCell(7).setCellValue("注册详细地址");
        row1.createCell(8).setCellValue("企业状态");

        int cell = 1;
        for (TenantExcelOut excelOut :tenantExcelOut){
            HSSFRow row=sheet.createRow(cell);
            row.createCell(0).setCellValue(excelOut.getTenantNick());
            row.createCell(1).setCellValue(excelOut.getUnifiedSocialCreditCode());
            //date类型保存到excel会变成数字所以要用String类型
            row.createCell(2).setCellValue(excelOut.getEstablishDate());
            row.createCell(2).setCellValue(excelOut.getEstablishDate());
            row.createCell(3).setCellValue(excelOut.getLegalRepresentative());
            row.createCell(4).setCellValue(excelOut.getApprovalDate());
            row.createCell(5).setCellValue(excelOut.getAggregationArea());
            row.createCell(6).setCellValue(excelOut.getRegisteredCapital());
            row.createCell(7).setCellValue(excelOut.getBusinessAddress());
            if (excelOut.getAuditStatus()==null){
                row.createCell(8).setCellValue("未审核");
            }else if(excelOut.getAuditStatus()==1){
                row.createCell(8).setCellValue("已通过");
            }else if(excelOut.getAuditStatus()==2){
                row.createCell(8).setCellValue("已拒绝");
            }
            cell++;
        }
		//把excel放入文件流中
        FileOutputStream output=new FileOutputStream("workbook.xls");
        wb.write(output);
        output.flush();
        //调用发送emal方法
        emailCommonService.sendExecl(map.get("mail").toString(),wb,"导出邮件");
    }

service

 //发送execl文档
      boolean sendExecl(String email, Workbook workbook,String subject);

impl

	//将配置保存到yml文件中读取
 	@Value("${spring.mail.username}")
    private  String userName;

    @Resource
    private JavaMailSender mailSender;

    @Override
    public boolean sendExecl(String email, Workbook workbook, String subject) {
    
        System.out.println("发送邮箱-企业excel");
        
        try {
            System.out.println(email);
            MimeMessage mimeMessage = mailSender.createMimeMessage();
            MimeMessageHelper helper=new MimeMessageHelper(mimeMessage,true);
            // 发件人
            helper.setFrom(userName);
            // 收件人
            helper.setTo(email);
            // 邮件标题
            helper.setSubject(subject);
            // 邮件内容
            File file=new File("企业列表.xlsx");
            FileOutputStream fos = new FileOutputStream(file);
            if(Objects.nonNull(workbook)) {
                workbook.write(fos);
                helper.setText("这是一个全部企业导出的邮件");
                helper.addAttachment("企业列表.xlsx",file);
                mailSender.send(mimeMessage);
            }
        }catch (Exception e){

        }


        return false;
    }