一.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/
