1.Service接口
public interface ExcelExportService {
ResponseEntity<byte[]> exportExcel() throws IOException;
}
2.Service接口实现类
package com.test.service.test.impl;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
/**
* 导出
* @author xxx
*/
@Slf4j
@Validated
@Service(value = ExcelExportServiceImpl.SERVICE_QUALIFIER)
public class ExcelExportServiceImpl
extends AbstractExampleService<Mapper, Model, ModelExample>
implements ExcelExportService
{
public static final String SERVICE_QUALIFIER = "com.test.service.test.impl.ExcelExportServiceImpl";
@Override
public ResponseEntity<byte[]> exportExcel() throws IOException{
//获取导出数据
List<ExportVO> list = getExportList();
String fileName = "列表-" + System.currentTimeMillis() + ".xlsx";
//xlsx格式Excel表格,那么在创建类型为07版的XSSF,并且设置ContentType为07版的格式
ExportParams exportParams = new ExportParams(null, null, ExcelType.XSSF);
//生成workbook
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, ExportVO.class, list); HttpHeaders httpHeaders = new HttpHeaders();
//03版 ContentType
//httpHeaders.add("content-Type", "application/vnd.ms-excel");
//07版 ContentType
httpHeaders.add("content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
httpHeaders.add("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
ByteArrayOutputStream output = null;
InputStream inputStream = null;
try {
//获得它的输入流
output = new ByteArrayOutputStream();
workbook.write(output);
inputStream = new ByteArrayInputStream(output.toByteArray());
output.flush();
//使用输入流创建指定大小的byte[]数组,并将字节码存入byte[]数组中
byte[] body = new byte[inputStream.available()];
inputStream.read(body);
inputStream.close();
return new ResponseEntity<>(body, httpHeaders, HttpStatus.OK);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}
ExportVO模型
package com.test.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
/**
* @author xxx
*/
public class ExportVO {
/**
* name指定导出excel时生成的列名
* orderNum可指定导出的该属性对应的所在列的位置
* width设置单元格宽度
* type设置导出类型 1是文本, 2是图片, 3是函数,10 数字 默认是文本
*/
/**
* 姓名
*/
@Excel(name = "姓名", orderNum = "1", width = 20)
private String name;
/**
* 当gender为1时,导出的结果为 男, 当gender为0时,导出的结果为 女
* mergeVertical设置是否纵向合并列
*/
@Excel(name = "性别",mergeVertical = true, replace = {"男_1", "女_0"},orderNum = "2", width = 5)
private Integer gender;
/**
* type设置导出类型 1是文本, 2是图片, 3是函数,10 数字 默认是文本
*/
@Excel(name = "年龄",orderNum = "3", type = 10, width = 5)
private int age;
/**
* 无参构造
*/
public ExportVO() {
}
/**
* 全参构造
*/
public ExportVO(String name, Integer gender, int age){
this.name = name;
this.gender = gender;
this.age = age;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "ExportVO{name='" + name +
"', gender='" + gender +
"', age='" + age +
"', area='" + area +"'}";
}
}