Excel导出客户信息

前端

<el-button type="primary"  @click="exprotCustom">导出客户</el-button>


    // 导出客户
    exprotCustom() {
      if(this.queryTime && this.queryTime !== '') {
        this.queryForm.searchTimeBegin = this.queryTime[0]
        this.queryForm.searchTimeEnd = this.queryTime[1]
      } else {
        this.queryForm.searchTimeBegin = ''
        this.queryForm.searchTimeEnd = ''
      }
      let param = '?searchTimeBegin='+this.queryForm.searchTimeBegin+'&searchTimeEnd='+this.queryForm.searchTimeEnd+'&customerName='+this.queryForm.customerName
      //调用导出
      exportCustomerExcel(param)
      // exportCustomerExcel(this.queryForm,true).then(res => {
      //   const blob = new Blob([res.data])
      //   const blobUrl = window.URL.createObjectURL(blob)
      //   const a = document.createElement('a')
      //   a.style.display = 'none'
      //   a.download = '客户信息表格' + '.xlsx'
      //   a.href = blobUrl
      //   a.click()
      //   this.$message.success('客户信息导出成功')
      //   }).catch(e => {console.log(e)})
    },

export function exportCustomerExcel(param) {
  window.location.href = window.atob(window.SITE_CONFIG.baseUrl) + '/customer/exportCustomer' + param
}

控制器

    @GetMapping("/exportCustomer")
    @ApiOperation(value = "导出", notes = "导出用户信息", produces = MediaType.APPLICATION_JSON_VALUE)
    public void exportCustomer(HttpServletResponse response, CustomerInfoDTO customerInfoDTO){
        customerInfoService.exportCustomer(response,customerInfoDTO);
    }

服务实现

    @Override
    public void exportCustomer(HttpServletResponse response,CustomerInfoDTO customerInfoDTO) {
        List<CustomerInfoVO> customerInfoVOList = this.baseMapper.getCustomerExportInfo(customerInfoDTO);
        ExcelWriter excelWriter = null;
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("客户信息表", "UTF-8").replaceAll("\\+", "%20"); // 防止中文乱码
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //初始化对象
            excelWriter = EasyExcel.write(response.getOutputStream(),CustomerInfoVO.class).build();
            WriteSheet sheet1 = EasyExcel.writerSheet("客户列表").build();
            WriteTable table = new WriteTable(); //创建表头对象
            table.setHead(Collections.emptyList()); // 将表头设置为空列表,只保留字段名,不保留字段注解
            excelWriter.write(customerInfoVOList, sheet1, table);
        } catch (Exception e) {
            logger.error("服务实现类发生了异常:", e);
            e.printStackTrace();
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            R.error("导出失败");
        }finally {
            // 关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

DAO层

    List<CustomerInfoVO> getCustomerExportInfo(@Param("dto") CustomerInfoDTO customerInfoDTO);

SQL

    <select id="getCustomerExportInfo" resultType="com...vo.CustomerInfoVO">
        select a.CUSTOMER_PHONE,a.CUSTOMER_NAME,group_concat(c.customer_tag_name) as customerTagNames , d.real_name from p_customer a
        left join p_customer_tag_rel b on a.CUSTOMER_ID = b.CUSTOMER_ID
        left join p_customer_tag c on b.customer_tag_id = c.customer_tag_id
        left join c_user d on a.customer_opt_staff = d.id
        where a.is_del = 0
        <if test="dto.searchTimeBegin !=null and dto.searchTimeBegin !=''">
            and a.customer_opt_time &gt; #{dto.searchTimeBegin}
        </if>
        <if test="dto.searchTimeEnd !=null and dto.searchTimeEnd !=''">
            and a.customer_opt_time &lt; #{dto.searchTimeEnd}
        </if>
        <if test="dto.customerName !=null and dto.customerName !=''">
            and a.customer_name like CONCAT ('%',#{dto.customerName},'%')
        </if>
        <if test="dto.customerPhone !=null and dto.customerPhone !=''">
            and a.customer_phone like CONCAT ('%',#{dto.customerPhone},'%')
        </if>
        group by a.CUSTOMER_PHONE
    </select>

VO

@Data
public class CustomerInfoVO extends BaseRowModel {
    @ExcelIgnore
    private Integer customerId;
    @ExcelIgnore
    private String customerCode;
    @ExcelProperty(value = "客户号码")
    private String customerPhone;
    @ExcelProperty(value = "客户姓名")
    private String customerName;
    @ExcelIgnore
    private Integer createStaff;
    @ExcelIgnore
    private String createTime;
    @ExcelIgnore
    private Integer customerOptStaff;
    @ExcelIgnore
    private String customerOptTime;
    @ExcelIgnore
    private Integer isDel;
    @ExcelProperty(value = "最近操作人")
    private String realName;
    @ExcelIgnore
    private List<CustomerFieldVO> customerField;
    @ExcelIgnore
    private String cityName;
    @ExcelIgnore
    private String customerTagIds;
    @ExcelProperty(value = "客户标签")
    private String customerTagNames;
    @ExcelIgnore
    private String customerNo;
    @ExcelIgnore
    private Integer taskInstId;
 }