Excel模版表格导入、导出、模版下载

//引入依赖包
       <!--easypoi-base 导入 导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能-->
        <dependency>
            <groupId>org.jeecg</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>2.3.1</version>
        </dependency>
        <!--easypoi-web  耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能-->
        <dependency>
            <groupId>org.jeecg</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>2.3.1</version>
        </dependency>
        <!--easypoi-annotation 基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理-->
        <dependency>
            <groupId>org.jeecg</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>2.3.1</version>
        </dependency>

//实体类
@Data
public class FamilyExcel implements Serializable{
    private static final long serialVersionUID = 4025701117092763393L;

    @ApiModelProperty(value = "商户身份证号")
    @Excel(name = "商户身份证号",width = 20)
    private String identityCard;

    @ApiModelProperty(value = "家庭关系")
    @Excel(name = "家庭关系")
    private String relationship;

    @ApiModelProperty(value = "家庭成员姓名")
    @Excel(name = "家庭成员姓名",width = 20)
    private String familyMemberName;

    @ApiModelProperty(value = "家庭成员年龄")
    @Excel(name = "家庭成员年龄",width = 20)
    private Integer familyMemberAge;

    @ApiModelProperty(value = "家庭成员工作单位")
    @Excel(name = "家庭成员工作单位",width = 20)
    private String familyMemberJob;

    @ApiModelProperty(value = "家庭成员电话")
    @Excel(name = "家庭成员电话",width = 20)
    private String familyMemberPhone;

    @ApiModelProperty(value = "备注")
    @Excel(name = "备注")
    private String remark;
}


// 工具类

@Component
public class ExcelUtils {

    /**
     * excel导入
     *
     * @param file       excel文件
     * @param titleRows  头部属于第几行文件
     * @param headerRows 头部有几
     * @param pojoClass  导入实体类型
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        // 文件是否为空
        if (file != null) {
            // 参数设置
            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            List<T> list;
            try {
                // excel导入
                list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
            } catch (NoSuchElementException e) {
                throw new ExcelExportException(ReturnMsg.CONTENT_CANNOT_BE_EMPTY);
            } catch (Exception e) {
                throw new ExcelExportException(e.getMessage());
            }
            return list;
        }
        return null;
    }

    /**
     * excel模板下载
     *
     * @param fileName  文件名称(测试.xls/.xlsx)
     * @param response  HttpServletResponse 请求返回
     * @param pojoClass 类名.class 泛型
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response, Class<?> pojoClass, String title, String sheet) {
        try {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            // excel做成
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(title, sheet), pojoClass, new ArrayList<>());
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            // 异常处理
            throw new ExcelExportException(e.getMessage());
        }
    }
}


// 使用
    @GetMapping(value = "/v1/download/template/family")
    @ApiOperation(value = "下载人员excel模板表")
    public void downloadFamilyTemplate(HttpServletResponse response) {
        ExcelUtils.downLoadExcel("人员信息.xls", response, FamilyExcel.class, "商户家庭成员信息", "家庭成员");
    }




    @PostMapping(value = "/v1/import/merchant")
    @ApiOperation(value = "excel上传")
    public Response importExcel(@RequestParam("file") @NotNull MultipartFile file, Integer person) {
        List<MerchantExcel> merchantExcels =ExcelUtils.importExcel(file, 1, 1, MerchantExcel.class);
        merchantService.importMerchant(merchantExcels, person);
        return ResponseUtil.success();
    }

    @GetMapping(value = "v1/download/getPriceTag")
    @ApiOperation(value = "价签下载")
    public void downloadPriceTag(HttpServletResponse response,Integer state,Integer type,String contractNumber) {
        try {
            List<PriceTag> priceTags = priceTagService.downloadPriceTag(state,type,contractNumber);
            if(null != priceTags && priceTags.size()>0){
                String excelName = "价签.xls";
                // 设置响应输出的头类型
                response.setHeader("content-Type", "application/vnd.ms-excel");
                // 下载文件的默认名称
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8"));
                // 导出到excel
                Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("价签", "价签"), PriceTag.class, priceTags);
                // 输出
                workbook.write(response.getOutputStream());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }