EasyPoi word导出 简易使用指南

0、整理思路

（1）前端按钮调用后端控制类
（2）前端：传递参数，接受返回的文件并下载到本地
（3）后端：接受传递过来的参数，查找出对应的实体类，通过easypoi提供的模板和控制类，写入文件
（4）模板：根据要写入的值确定word模板，并将其放入启动类所在包的resource下
（5）实体：实体类添加@Escel相关注释

1、安装依赖

 <!-- 集成 easypoi -->
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-base</artifactId>
	<version>3.3.0</version>
</dependency>
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-web</artifactId>
	<version>3.3.0</version>
</dependency>
<dependency>
	<groupId>cn.afterturn</groupId>
	<artifactId>easypoi-annotation</artifactId>
	<version>3.3.0</version>
</dependency>

2、确定word模板

{{area}}   双括号内放变量或常量
{{fd:(Time;yyyy-MM-dd)}}   fd:(obj;yyyy-MM-dd)格式化时间格式
{{fe:List t}}   fe:obj  t  t为别名

放一个自己的例子


这里因为放的是实体类，所以是t.name，同一行的数据只需要两对{}

3、修改实体类

//牵扯到了三个实体类，所以都要注释

@Data
@Accessors(chain = true)
@TableName(value = "report_dingtalk_data" , autoResultMap = true)
public class DingTalkDateEntity {

    @TableId
    private Long id;
    /**
     * 大区
     */
    @Excel(name = "大区", width = 15)   //需要导出的数据需添加@Ecxcel注解
    private String area;
    /**
     * 大区负责人名称
     */
    @Excel(name = "大区负责人名称", width = 15)
    private String marketingPersonnel;
    /**
     * 市/县 （一级经销商信息）
     */
    @Excel(name = "市、县", width = 15)
    private String city;
    /**
     * 姓名 （一级经销商信息）
     */
    @Excel(name = "姓名", width = 15)
    private String userName;
    /**
     * 最后执行时间
     */
    @Excel(name =" 最后执行时间", width = 15, format = "yyyy-MM-dd")//确定表示时间的格式
    @JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date applicationTime;
    /**
     * 数据
     */
    @ExcelCollection(name = "数据", orderNum = "12")
    @TableField(typeHandler = DecorationFormJsonTypeHandler.class)
    private DecorationForm body;    
}  
@Data
@Accessors(chain = true)
@ApiModel("形象店装修申请表")
public class DecorationForm {

    @Excel(name = "大区", width = 15)
    @ApiModelProperty("大区")
    private String area;

    @Excel(name =" 申请时间", width = 15, format = "yyyy-MM-dd")
    @ApiModelProperty("申请时间")
    @JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    private Date applicationTime;

    @Excel(name = "消费人员", width = 15)
    @ApiModelProperty("营销人员")
    private String marketingPersonnel;

    @Excel(name = "市/县 （一级经销商信息）", width = 15)
    @ApiModelProperty("市/县 （一级经销商信息）")
    private String city;

    @Excel(name = "姓名 （一级经销商信息）", width = 15)
    @ApiModelProperty("姓名 （一级经销商信息）")
    private String userName;

    @ExcelCollection(name = "二级渠道经销商基础信息", orderNum = "7")
    @ApiModelProperty("二级渠道经销商基础信息")
    private List<DealerBaseInfo> dealerBaseInfos;


}
@Data
@Accessors(chain = true)
@ApiModel("二级渠道经销商基础信息")
public class DealerBaseInfo {
    @Excel(name = "乡镇名称", width = 15)
    @ApiModelProperty("乡镇名称")
    private String townshipName;

    @Excel(name = "姓名", width = 15)
    @ApiModelProperty("姓名")
    private String name;

    @Excel(name = "联系方式", width = 15)
    @ApiModelProperty("联系方式")
    private String contactDetails;

    @Excel(name = "经营品牌", width = 15)
    @ApiModelProperty("经营品牌")
    private String businessContent;

    @Excel(name = "年销量", width = 15)
    @ApiModelProperty("年销量")
    private String annualSales;

    @Excel(name = "店面间数", width = 15)
    @ApiModelProperty("店面间数")
    private String storeCount;

    @Excel(name = "装修尺寸", width = 15)
    @ApiModelProperty("装修尺寸")
    private String decorationSize;

    @Excel(name="图片附件",width=25,type=2)
    @ApiModelProperty("图片附件")
    private List<String> annexPic;

}

4、编写控制类

@RequestMapping(value = "/exportXls")
    public void exportXls(String id, HttpServletResponse response) throws Exception {
        Map<String, Object> map = new HashMap<String, Object>();  //map映射,来映射模板中的名与参数
        DingTalkDateEntity dingTalkDate = dingTalkDateService.getById(id);  //根据id查询出实体类
        map.put("area", dingTalkDate.getArea());                    //将第一个实体类的参数打印
        map.put("marketingPersonnel", dingTalkDate.getMarketingPersonnel());
        map.put("ApplicationTime", dingTalkDate.getApplicationTime());
        DecorationForm body = dingTalkDate.getBody();
        map.put("city", body.getCity());   //获得第二个实体类
        map.put("name",body.getUserName());

        List<DealerBaseInfo>  dealerBaseInfos = body.getDealerBaseInfos();  //第三个实体类的List信息
        List<Map<String,Object>>  infoList = new ArrayList<>();
        for(int i=0; i<dealerBaseInfos.size(); i++){ //遍历List
            DealerBaseInfo info =  new DealerBaseInfo();
            info = dealerBaseInfos.get(i);
            Map<String, Object> stringObjectMap = BeanUtil.beanToMap(info);
            stringObjectMap.put("index",i+1);   //添加序号标识
            infoList.add(stringObjectMap);
        }
        map.put("infoList",infoList);             

      

        ResourceLoader resourceLoader = new DefaultResourceLoader();  //Spring对物理资源的访问方式抽象成Resource，我们可以通过Spring提供的接口来访问磁盘文件等数据。
        InputStream inputStream = resourceLoader.getResource("classpath:word/test123.docx").getInputStream();//字节输入流，用来将文件中的数据读取到java程序中
        XWPFDocument document = new MyXWPFDocument(inputStream);  //直接new一个空的XWPFDocument，之后再往这个XWPFDocument里面填充内容，然后再把它写入到对应的输出流中。
        selfCreateAnnexPic(document,dealerBaseInfos);    // 添加附件函数
        WordExportUtil.exportWord07(document, map);   //word导出工具类，将输入流和映射关系写入
        document.write(response.getOutputStream());  //写入文件
    }

	private void selfCreateAnnexPic(XWPFDocument document, List<DealerBaseInfo> imgList) {
        XWPFParagraph paragraph = document.createParagraph();
        paragraph.setPageBreak(true);
        for (DealerBaseInfo imgInfo: imgList) {
            XWPFRun run = paragraph.createRun();
            run.setBold(true);
            run.setText("附件：" + imgInfo.getTownshipName());
            run.setFontSize(15);
            run.addBreak();
            for (String s : imgInfo.getAnnexPic()) {
                ImageEntity image = new ImageEntity();
                image.setHeight(250);
                image.setWidth(200);
                image.setUrl(s);
                image.setType(ImageEntity.URL);
                ExcelMapParse.addAnImage(image,run);
                run.addTab();
            }
            run.addBreak();
        }
    }

5、前端代码

 import { getAction, downloadFile } from '@/api/manage'
 <a href="javascript:;" @click="exportToWord(record)">导出</a>
 exportToWord(record) {
        downloadFile("/ding/exportXls","下载文件.docx",{
          id: record.id
        }).then(res => {
        })
      },

6、效果展示

7、多sheet导出

 /**
     * 导出多sheet表方法
     * @param excelClassList,放名字，数据，类class
     * @param response
     */
    public static void downloadExcel(String excelName,List<ExcelClass> excelClassList, HttpServletResponse response){
        try {
            //构建map集合
            List<Map<String,Object>> mapList = new ArrayList<Map<String,Object>>();
            for (ExcelClass excelClass:excelClassList){
                Map<String,Object> map = new HashMap<String,Object>();
                map.put("title",new ExportParams(excelClass.getName(),excelClass.getName(), ExcelType.XSSF));
                map.put("entity",excelClass.getAClass());
                map.put("data",excelClass.getData());
                mapList.add(map);
            }
            //调用ExcelExportUtil.exportExcel方法生成workbook
            Workbook wb = ExcelExportUtil.exportExcel(mapList, ExcelType.XSSF);
            String fileId = String.valueOf(System.currentTimeMillis());
            String fileName = excelName+ File.separator + fileId + ".xls";
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            wb.write(response.getOutputStream());
        }catch (Exception e){
            e.printStackTrace();
        }
    }

用法：

  /**
     * 导出出租单Excel
     * @param response
     * @param sqlLeaseBills
     * @param commonQuery
     */
    @Override
    public void downloadBills(HttpServletResponse response, LeaseBills sqlLeaseBills, CommonQuery commonQuery){
        //构造材料设备表查询器
        QueryWrapper<LeaseBills> autoQueryWrapper = QueryGenerator.initQueryWrapper(sqlLeaseBills, commonQuery);
        autoQueryWrapper.orderByDesc("create_time");
        List<LeaseBills> leaseBills = leaseBillsMapper.getBills(autoQueryWrapper);
        List<LeaseBillsDetails> leaseBillsDetails = leaseBillsDetailsService.list(Wrappers.<LeaseBillsDetails>lambdaQuery().in(LeaseBillsDetails::getLeaseNo,leaseBills.stream().map(LeaseBills::getLeaseNo).collect(Collectors.toList())));
        List<LeaseBillsOtherCost> leaseBillsOtherCosts = leaseBillsOtherCostService.list(Wrappers.<LeaseBillsOtherCost>lambdaQuery().in(LeaseBillsOtherCost::getLeaseNo,leaseBills.stream().map(LeaseBills::getLeaseNo).collect(Collectors.toList())));
        List<ExcelClass> excelClassList = new LinkedList<>();
        excelClassList.add(new ExcelClass("出租单主表",leaseBills,LeaseBills.class));
        excelClassList.add(new ExcelClass("出租单明细",leaseBillsDetails, LeaseBillsDetails.class));
        excelClassList.add(new ExcelClass("其他费用",leaseBillsOtherCosts, LeaseBillsOtherCost.class));
        //输出表格
        ExcelUtils.downloadExcel("出租单",excelClassList,response);
    }