【EasyPoi】SpringBoot使用EasyPoi自定义模版导出Excel

EasyPoi

官方文档：http://doc.wupaas.com/docs/easypoi

Excel模版导出

引入依赖

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.4.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.4.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.4.0</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version></dependency></dependencies>

创建模版

在这里插入图片描述

测试

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.core.io.ClassPathResource;
import org.springframework.test.context.junit4.SpringRunner;
import pers.kw.esaypoi.EasyPoiApp;import java.io.File;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;@SpringBootTest(classes = EasyPoiApp.class)
@RunWith(SpringRunner.class)
public class PoiTest {@Testpublic void test() throws Exception {ClassPathResource classPathResource = new ClassPathResource("templates/student.xlsx");File file = classPathResource.getFile();TemplateExportParams params = new TemplateExportParams(file.getPath());Map<String, Object> map = new HashMap<>();map.put("date", "2014年09月01日");map.put("sex", "男");map.put("stuNo", "12138");map.put("addr", "五道口");map.put("tel", "123456");//excel如何遍历列表Workbook workbook = ExcelExportUtil.exportExcel(params, map);FileOutputStream fos = new FileOutputStream("/Users/kw/Downloads/student.xlsx");workbook.write(fos);fos.close();}
}

导出效果
在这里插入图片描述

问题记录

运行时，类找不到

Caused by: java.lang.ClassNotFoundException: org.apache.poi.ss.usermodel.WorkbookFactory$CreateWorkbook0

这种运行类找不到问题，一般就是依赖冲突了。
使用maven helper查看到poi-ooxml等相关依赖，版本冲突了。
解决：
引入依赖

 <dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version>
</dependency>
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version>
</dependency>

运行报错No valid entries or contents found, this is not a valid OOXML (Office Open XML) file

原因是maven插件打包配置了过滤文件类型
排除xlsx xls类型即可

<build><resources><resource><directory>src/main/resources/</directory><includes><include>**/*.yml</include><include>**/*.xml</include><include>**/*.properties</include><include>**/*.html</include><include>**/*.xlsx</include><include>**/*.xls</include></includes><filtering>true</filtering></resource></resources><plugins><plugin><artifactId>maven-resources-plugin</artifactId><version>3.1.0</version><configuration><encoding>utf-8</encoding><resources><resource><directory>src/main/resources/</directory><filtering>true</filtering></resource></resources><nonFilteredFileExtensions><nonFilteredFileExtension>xls</nonFilteredFileExtension><nonFilteredFileExtension>xlsx</nonFilteredFileExtension></nonFilteredFileExtensions></configuration></plugin><plugin><groupId>org.apache.maven.plugins</groupId><artifactId>maven-compiler-plugin</artifactId><version>3.8.1</version><configuration><target>1.8</target><source>1.8</source><encoding>utf-8</encoding></configuration></plugin></plugins></build>