一.poi导出word

1.在pom.xml中加入依赖

<!-- POI工具 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.1</version>
</dependency>
<!--导出word poi-tl-->
<dependency>
  <groupId>com.deepoove</groupId>
  <artifactId>poi-tl</artifactId>
  <version>1.8.2</version>
</dependency>
<!--Sping表达式  为了在模板标签中使用SpEL表达式-->
<dependency>
    <groupId>org.springframework</groupId>
        <artifactId>spring-expression</artifactId>
    <version>5.1.3.RELEASE</version>
</dependency>

2.准备word模板

image.png

3.代码

        //模板路径
        private final static String WORD_PATH = "static/docs/assistPolice.docx";

    private final String defaultStr = "-";
    private final BigDecimal defaultDecimal = new BigDecimal(0);

    /**
     * 导出个人档案Word
     * @param id
     * @return
     */
    public AjaxResult exportAssisPoliceWordById(String id){
              //获取导出对象
        AssistPolice assistPolice = (AssistPolice) setEmpty(selectExportWordAssistPoliceById(id));
        XWPFTemplate xwpfTemplate = null;
        FileOutputStream out = null;
        try {
            ClassPathResource template = new ClassPathResource(WORD_PATH);
            // Spring EL 无法容忍变量不存在，直接抛出异常，表达式计算引擎为Spring Expression Language
            Configure config = Configure.newBuilder().setElMode(ELMode.SPEL_MODE).build();
            xwpfTemplate = XWPFTemplate.compile(template.getInputStream(),config).render(assistPolice);
            String filename = encodingWordFilename(assistPolice.getName()+"档案");
            out = new FileOutputStream(getAbsoluteFile(filename));
                        // 输出流
            xwpfTemplate.write(out);
            out.flush();
            out.close();
            xwpfTemplate.close();
            return AjaxResult.success(filename);
        }catch (Exception e){
            e.printStackTrace();
            log.info(e.toString());
            throw new BusinessException("导出个人档案失败，请联系网站管理员！");
        }finally{
            if (xwpfTemplate != null){
                try{
                    xwpfTemplate.close();
                }catch (IOException e1){
                    e1.printStackTrace();
                }
            }
            if (out != null){
                try{
                    out.close();
                }catch (IOException e1){
                    e1.printStackTrace();
                }
            }
        }
    }

    /**
     * 获取下载路径
     *
     * @param filename 文件名称
     */
    public String getAbsoluteFile(String filename){
        //KcConfig.getDownloadPath()取配置文件中的下载路径  profile: C:/profile/
        //String downloadPath = KcConfig.getDownloadPath() + filename;
       String downloadPath = " profile: C:/profile/" + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists()){
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }

    /**
     * 编码word文件名
     */
    public String encodingWordFilename(String filename){
        filename = UUID.randomUUID().toString().replaceAll("-","") + "_" + replaceBlank(filename.trim()) + ".docx";
        return filename;
    }
    
   /**
     * 去掉字符串中的制表符
     * @param str
     * @return
     */
    public static String replaceBlank(String str){
        String dest = "";
        if (str != null) {
            Pattern p = Pattern.compile("\\s*|\t|\r|\n");
            Matcher m = p.matcher(str);
            dest = m.replaceAll("");
        }
        return dest;
    }

    /**
     * 设置AssistPolice中为空的属性为''
     * @param object
     * @return
     */
    @SuppressWarnings(value={"rawtypes"})
    public Object setEmpty(Object object){
        try {
            Class clazz = object.getClass();
            Field[] fields = clazz.getDeclaredFields();
            for(int i=0;i<fields.length;i++){
                Field field = fields[i];
                String fieldName = field.getName();
                Class fieldClass=field.getType();
                field.setAccessible(true); //设置访问权限
                if(isFieldValueNull(fieldName,object)){
                    if (fieldClass == Integer.class ) {
                        field.set(object, defaultDecimal.intValue());
                    }else if (fieldClass == Long.class) {
                        field.set(object, defaultDecimal.longValue());
                    }else if (fieldClass == Float.class) {
                        field.set(object, defaultDecimal.doubleValue());
                    }else if (fieldClass == BigDecimal.class) {
                        field.set(object, defaultDecimal);
                    } else if (fieldClass == String.class){
                        field.set(object, defaultStr); // 设置值
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e.getMessage());
        }
        return object;
    }

4.参考文档
http://deepoove.com/poi-tl/

二.easypoi导出Excel

1.在pom.xml中加入依赖
官方提供 我这里有jar包冲突

       <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>

角落找到的新包

        <!---excel操作-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.1.2</version>
        </dependency>

!!!坑：有commons-lang3包，导入版本3.9以上

2.实体 （两种字典替换方式）
a、replace，该方式支持直接写入注解参数中

    @ApiModelProperty(value="是否是退役军人",example="0")
    @Excel(name = "是否是退役军人",replace ={"是_1", "否_0"}, width = 10,orderNum = "20")
    private Integer veterans;

b、dict，字典方式，传入字典参数中

    @ApiModelProperty("人员籍贯(字典)")
    @Excel(name = "籍贯",dict = "nativeplace",  width = 40,orderNum = "4")
    private String nativeplace;

3.实现IExcelDictHandler接口翻译字典

package com.hnkc.fjbm.service.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.hnkc.fjbm.model.DictData;
import com.hnkc.fjbm.service.service.DictService;

import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;

@Component
public class ExcelDictHandlerImpl  implements IExcelDictHandler{
    
    private static Logger logger = LoggerFactory.getLogger(ExcelDictHandlerImplService.class);
    
    @Autowired
    private DictService tempDictService;
    private static DictService dictService;
    private static Map<String, Object> mapData;
    
//PostConstruct注解启动时加载一次
    @PostConstruct
    public void init() {
        DictService dictService = this.tempDictService;
        logger.info("--------加载字典数据-----------");
        mapData = (Map<String, Object>)dictService.getAllDicts().get("data");
        logger.info("------------加载完成---------");
    }
    
    @Override
    public String toName(String dict, Object obj, String name, Object value) {
        try {
            List<DictData> dictDataList = new ArrayList<>();
            if ("nativeplace".equals(dict) && mapData.containsKey("籍贯")) {
                dictDataList = (List<DictData>)mapData.get("籍贯");
                for(DictData dictData : dictDataList) {
                    if(!"".equals(value) && value != null) {
                        if(value.toString().equals(dictData.getValue())) {
                            return dictData.getLabel();
                        }
                    }
                }
                    }
            }
        }catch (Exception e){
                e.printStackTrace();
        }
        return "未知";
    }

    @Override
    public String toValue(String dict, Object obj, String name, Object value) {
        // TODO Auto-generated method stub
        return null;
    }

}

可以参考下https://blog.csdn.net/changzhou0519/article/details/87858969
4.导出表格

          List<Signups> list = service.getSignUpList(signUps_cs);
          try {
              ExportParams params = new ExportParams("惠州市公安局招聘警务辅助人员报名表", "sheetName1", ExcelType.XSSF);
              // 指定单元格转换字典
              params.setDictHandler(new ExcelDictHandlerImplService());
              Workbook workbook = ExcelExportUtil.exportExcel(params, Signups.class, list);
              File saveFile = new File("excel");
              if (!saveFile.exists()) {
                  saveFile.mkdirs();
              }
              String filename = encodingWordFilename("惠州市公安局招聘警务辅助人员报名表");
              FileOutputStream fos = new FileOutputStream(getAbsoluteFile(filename));
              workbook.write(fos);
              fos.close();
          } catch (IOException e) {
              e.printStackTrace();
          }

参考文档：
http://easypoi.mydoc.io/