之前写过一个文章使用easyPoi导入excel文件;这次随着业务发展,又要做导出,那我正好用easyPoi的便捷性又做了一个导出,这里记录一下
先看成品
官方介绍
最新官方文档:http://doc.wupaas.com/docs/easypoi/easypoi-1c0u6ksp2r091
加入依赖
<!-- EasyPoi导入导出-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
我用的时候比较早了,官方后来支持了springboot,可以自己试一下,导入下面的boot依赖就可以了
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
自定义注解导出的格式和内容
我这里没有用模板导出,使用模板导出需要拟定义一个excel模板,然后往里面加数据,这样可以限制固定格式,并且可以解决比较复杂的表格,但是不易变通。我的需求比较简单,所以使用了自定义注解导出。模板导出可以查看官方介绍
定义一个实体:试卷,他包含名称,用途,以及若干试题:
@Data
@ExcelTarget("TestPaperInfoEntity")
public class TestPaperInfoEntity implements Serializable {
/**
* 主键
*/
private String id;
@Excel(name = "名称", orderNum = "12", width = 25)
private String name;
@Excel(name = "用途", replace = { "测试_0", "作业_1" },orderNum = "13", width = 25)
private String useType;
@ExcelCollection(name = "", orderNum = "1")
private List<TestPaperExcelOutModel> questions;
}
- @ExcelTarget(“TestPaperInfoEntity”):表示使用TestPaperInfoEntity这个对象是可以针对不同字段做不同处理
- @Excel(name = “用途”, replace = { “测试_0”, “作业_1” },orderNum = “13”, width = 25):这个是必须使用的注解,各属性介绍:
- @ExcelCollection:表示这是一个集合,主要针对一对多的导出,比如我这一个试卷对应很多道试题,那么试题就需要使用这个注解
然后定义试题:
@Data
public class TestPaperExcelOutModel implements Serializable {
private static final long serialVersionUID = 1L;
@Excel(name = "序号")
private String order;
@Excel(name = "题型")
private String questionType;
@Excel(name = "子题型")
private String childQuestionType;
@Excel(name = "试题序列")
private String questionSec;
@Excel(name = "题干", width = 30)
private String questionBody;
@Excel(name = "选项", width = 30)
private String questionOpt;
@Excel(name = "答案", width = 30)
private String questionAnswer;
@Excel(name = "解析", width = 30)
private String questionResolve;
@Excel(name = "难度")
private String difficultyLevel;
@Excel(name = "分数")
private Integer score;
}
测试生成文件步骤
@SpringBootTest(classes = DemoApplication.class)
@RunWith(SpringRunner.class)
public class TestPaperServiceImplTest {
@Autowired
private TestPaperServiceImpl service;
@Before
public void setUp() throws Exception {
}
@After
public void tearDown() throws Exception {
}
@Test
public void exporTestPaperExcel() throws IOException {
List<TestPaperInfoEntity> list = new ArrayList<>();
TestPaperInfoEntity infoEntity = new TestPaperInfoEntity();
List<TestPaperExcelOutModel> questions = new ArrayList<>();
for (int i = 0; i < 10; i++) {
TestPaperExcelOutModel model = new TestPaperExcelOutModel();
model.setQuestionAnswer("答案"+i);
model.setDifficultyLevel("1");
model.setOrder(i+"");
model.setQuestionBody("试题的题干"+i);
model.setQuestionOpt("试题的选项"+i);
model.setQuestionResolve("试题解析");
model.setQuestionSec(i+"");
model.setScore(i);
model.setQuestionType("单选题");
questions.add(model);
}
infoEntity.setId("1");
infoEntity.setName("测试名称");
infoEntity.setUseType("1");
infoEntity.setQuestions(questions);
list.add(infoEntity);
// 组装好数据后,直接调用这个方法就可以了,当然他有很多重载方法,可以自己看一下源码选择合适的
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), TestPaperInfoEntity.class, list);
// 得到Workbook就可以写入到文件中了
String path = "E:/testPaper/" + "导出的excel名称" + ".xls";
File file = new File(path);
if(file.exists() && file.isFile()){
file.delete();
}
FileOutputStream outputStream = new FileOutputStream(path);
workbook.write(outputStream);
outputStream.close();
// todo 提供浏览器下载
}
}
查看结果
去本地文件夹找到文件看一下: