前言
项目中遇到一个模板导出excel需要根据值进行字典转码的问题,尝试使用三目运算符无果后,在EasyPoi文档里发现有一项**dict:**指令,虽然下方有相关指令讲解,但是还是无法第一时间明白如何使用,查询前辈的博客后找到了一些现成代码,加以修改后就可以直接使用了。
示例
- 字典处理类
import cn.afterturn.easypoi.handler.inter.IExcelDictHandler;
import com.google.common.collect.ImmutableMap;
import org.springframework.stereotype.Component;
import java.util.Map;
/**
* 导出excel字典转换处理类
*/
@Component
public class DictHandler implements IExcelDictHandler {
public Map<String, Map<String, String>> dictMap = new ImmutableMap.Builder<String, Map<String, String>>()
.put("GENDER",ImmutableMap.of("男","1","女","2"))
.put("JOB",ImmutableMap.of("工人","1","收银员","2","司机","3"))
.build();
/**
* 从值翻译到名称
*
* @param dict 字典Key
* @param obj 对象
* @param name 属性名称
* @param value 属性值
* @return
*/
@Override
public String toName(String dict, Object obj, String name, Object value) {
Map<String, String> map = dictMap.get(dict);
for (String key : map.keySet()) {
if (map.get(key).equals(String.valueOf(value))) {
return key;
}
}
return null;
}
/**
* 从名称翻译到值
*
* @param dict 字典Key
* @param obj 对象
* @param name 属性名称
* @param value 属性值
* @return
*/
@Override
public String toValue(String dict, Object obj, String name, Object value) {
Map<String, String> map = dictMap.get(dict);
for (String key : map.keySet()) {
if (key.equals(name)) {
return map.get(name);
}
}
return null;
}
}
其中用到了ImmutableMap,在声明不变的map时我比较喜欢使用它,这里也算安利一下,下面是它的依赖。如果不想用的话用静态代码块初始化map或者通过调用方法动态赋值也都是可以的。
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>22.0</version>
</dependency>
- 导出excel控制层
import cn.afterturn.easypoi.entity.vo.TemplateExcelConstants;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.view.PoiBaseView;
import cn.com.lixiaochen.handler.DictHandler;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/people")
public class PeopleController {
@Resource
DictHandler dictHandler;
@GetMapping("/export")
public void exportPWorkRpt(ModelMap modelMap, @RequestParam Map<String, Object> params,
HttpServletRequest request, HttpServletResponse response) {
// 导出数据
List<Map<String,Object>> dataList = Arrays.asList(
new HashMap<String, Object>(){{
put("name","张三");
put("sex","1");
put("job","1");
}},
new HashMap<String, Object>(){{
put("name","李四");
put("sex","2");
put("job","2");
}},
new HashMap<String, Object>(){{
put("name","王五");
put("sex","1");
put("job","3");
}}
);
Map<String,Object> exportMap = new HashMap<String, Object>(){{
put("data",dataList);
}};
modelMap.put(TemplateExcelConstants.FILE_NAME, "人员表");
TemplateExportParams templateExportParams = new TemplateExportParams("easypoi/people.xlsx");
// 设置字典处理类
templateExportParams.setDictHandler(dictHandler);
modelMap.put(TemplateExcelConstants.PARAMS, templateExportParams);
modelMap.put(TemplateExcelConstants.MAP_DATA, exportMap);
PoiBaseView.render(modelMap, request, response, TemplateExcelConstants.EASYPOI_TEMPLATE_EXCEL_VIEW);
}
}
- 设置excel模板
4.导出效果
小结
这里主要整理的是excel模板+字典的导出,涉及到实体类的导出导入,还是参考下方链接中文档和博客吧。