EasyPoi的简单使用
依赖引入
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>${easypoi.version}</version>
</dependency>
<dependency>
<groupId>org.hibernate.validator</groupId>
<artifactId>hibernate-validator</artifactId>
</dependency>
<dependency>
<groupId>javax.validation</groupId>
<artifactId>validation-api</artifactId>
<version>1.1.0.Final</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>编写导出实体类
Company.java
package cn.com.jcoo.model;
import cn.afterturn.easypoi.excel.annotation.*;
import lombok.Builder;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import javax.validation.constraints.NotNull;
import java.util.Date;
import java.util.List;
@Data
@Builder
@ExcelTarget("company")
public class Company {
Long keyId;
@Excel(name = "机构名称", width = 20, needMerge = true)
@NotNull
String companyName;
@Excel(name = "机构地址", width = 25, needMerge = true)
String companyAddress;
@Excel(name = "成立日期", format = "yyyy/MM/dd", needMerge = true)
Date foundingDate;
@Excel(name = "员工人数", needMerge = true)
// @Min(value = 1, message = "员工人数不能少于1")
// @Max(value = 10000000, message = "员工人数不能大于10000000")
Integer totalEmployees;
@Excel(name = "是否公立", needMerge = true)
String publicMate;
@ExcelIgnore
Boolean isPublic;
public Boolean getPublic() {
return StringUtils.endsWith(publicMate, "是");
}
@Excel(name = "网站地址")
// @Pattern(regexp = "^(http|https)://[a-zA-Z0-9-.]+.[a-zA-Z]{2,}(/\\S*)?$", message =
// "网站地址格式不正确")
String officialWebsite;
@ExcelEntity(id = "leader", name = "领导人")
User leader;
@ExcelCollection(name = "部门")
List<Department> departments;
}
Department.java
package cn.com.jcoo.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Builder;
import lombok.Data;
import java.util.List;
@Data
@Builder
@ExcelTarget("department")
public class Department {
Long keyId;
/**
* @see Company#keyId
*/
Long companyId;
@Excel(name = "部门名称", needMerge = true)
String dptName;
@Excel(name = "部门职责", needMerge = true)
String dptJob;
@ExcelEntity(id = "manager", name = "部门负责人")
User manager;
// 这里加这个导出存在问题
// @ExcelCollection(id = "cym", name = "管理层")
// List<User> managers;
@ExcelCollection(name = "工作小组")
List<Group> groups;
}
Group.java
package cn.com.jcoo.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelCollection;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Builder;
import lombok.Data;
import java.util.List;
@Data
@Builder
@ExcelTarget("group")
public class Group {
Long keyId;
/**
* {@link Department#keyId}
*/
String dptId;
@Excel(name = "工作组名称", needMerge = true)
String groupName;
@ExcelEntity(id = "grouper", name = "组长")
User grouper;
@ExcelCollection(id = "member", name = "成员")
List<User> members;
}
User.java
package cn.com.jcoo.model;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Builder;
import lombok.Data;
import java.math.BigDecimal;
@Data
@Builder
@ExcelTarget("user")
public class User {
Long keyId;
@Excel(name = "法人_leader,成员_cym,部门负责人_manager,组长_grouper,小组成员_member", needMerge = true)
String username;
@Excel(name = "年龄", needMerge = true)
Integer age;
@Excel(name = "收入", needMerge = true)
BigDecimal revenue;
}
测试导出功能
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.com.jcoo.model.Company;
import cn.com.jcoo.model.Department;
import cn.com.jcoo.model.Group;
import cn.com.jcoo.model.User;
import com.google.common.collect.Lists;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Workbook;
import org.junit.Test;
import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@Slf4j
public class ExportTest {
@SneakyThrows
@Test
public void testExport() {
User jcoo = User.builder().username("Jcoo").age(28)
.revenue(new BigDecimal("30000")).build();
User alice = User.builder().username("alice").revenue(new BigDecimal("25000.89"))
.age(25).build();
User ben = User.builder().username("ben").revenue(new BigDecimal("20000")).age(27)
.build();
Group java1DevGroup = Group.builder().groupName("Java研发一组").grouper(ben)
.members(Lists.newArrayList(ben)).build();
Group java2DevGroup = Group.builder().groupName("Java研发二组").grouper(alice)
.members(Lists.newArrayList(alice, jcoo)).build();
Department dev = Department.builder().dptName("研发部")
.groups(Lists.newArrayList(java1DevGroup, java2DevGroup)).manager(alice)
.build();
Department manage = Department.builder().dptName("管理层")
.groups(Lists.newArrayList())
// .managers(Lists.newArrayList(alice, jcoo))
.manager(jcoo).build();
Company company = Company.builder().companyName("xx技术有限责任公司").leader(jcoo)
.companyAddress("四川成都xx").departments(Lists.newArrayList(manage, dev))
.build();
List<Company> companies = Lists.newArrayList(company);
ExportParams exportParams = new ExportParams();
exportParams.setType(ExcelType.XSSF);
exportParams.setSheetName("机构");
String now = new SimpleDateFormat("yyyyMMddmmHHss").format(new Date());
String filename = "机构" + now + ".xlsx";
try (Workbook sheets = ExcelExportUtil.exportExcel(exportParams, Company.class,
companies)) {
File file = new File(filename);
try (FileOutputStream fos = new FileOutputStream(file)) {
sheets.write(fos);
}
}
}
}
测试结果：
其中黑色部分是应该要合并单元格的，但是实际却没有合并。
excel模板填充导出
jszzyhwzb.xlsx（resource路径的template/zzjs/下）
Object cacheObject = redisService.getCacheObject("zzjs:pausetasklist:zbdata");
if (cacheObject != null) {
String time = DateUtil.now();
Object obj = redisService.getCacheObject("zzjs:pausetasklist:cachetime");
if (obj != null) {
time = (String) obj;
}
String date = DateUtil.format(DateUtil.parse(time), "yy年MM月dd日");
List<PauseTaskZbVo> zb = JacksonUtils.parseList((String)cacheObject, PauseTaskZbVo.class);
if (zb == null) {
return;
}
TemplateExportParams params = new TemplateExportParams("template/zzjs/jszzyhwzb.xlsx", true);
List<PauseTaskVo> pauseTaskList = evoaPauseAndRecoverService.getPauseTaskList(false, false);
Map<String, Object> map = evoaPauseAndRecoverService.getZbDataMap(date, zb, pauseTaskList);
try (Workbook workbook = ExcelExportUtil.exportExcel(params, map)) {
String filename = "计时中止与恢复模块-数据排查周报（截止" + date + "）.xlsx";
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
workbook.write(response.getOutputStream());
} catch (Exception e) {
log.error(e.getMessage());
}
}
public Map<String, Object> getZbDataMap(String date, List<PauseTaskZbVo> zb, List<PauseTaskVo> pauseTaskList) {
Map<String, Object> map = new HashMap<>();
map.put("date", date);
List<PauseTaskVo> wttasklist = new ArrayList<>();
for (PauseTaskZbVo pauseTaskZbVo : zb) {
List<PauseTaskVo> pauseTaskVoList = pauseTaskZbVo.getPauseTaskVoList();
int pausecount = ListUtil.isEmpty(pauseTaskVoList) ? 0 : pauseTaskVoList.size();
map.put(pauseTaskZbVo.getSeq()+"pausecount", pausecount);
List<PauseTaskVo> pauseTaskVoWtList = pauseTaskZbVo.getPauseTaskVoWtList();
int wtcount = ListUtil.isEmpty(pauseTaskVoWtList) ? 0 : pauseTaskVoWtList.size();
map.put(pauseTaskZbVo.getSeq()+"wtcount", wtcount);
if (wtcount > 0) {
wttasklist.addAll(pauseTaskVoWtList);
}
}
wttasklist = wttasklist.stream().filter(distinctByKey(PauseTaskVo::getSeq)).collect(Collectors.toList());
map.put("wttasklist", wttasklist);
map.put("allwtcount", wttasklist.size());
map.put("allpausetask", pauseTaskList);
map.put("allpausecount", ListUtil.isEmpty(pauseTaskList) ? 0 : pauseTaskList.size());
return map;
}大批量导出
使用EasyExportUtil中的exportBigExcel方法
@SneakyThrows
@GetMapping("/download")
public void download(HttpServletResponse response) {
ExecutorService executorService = Executors.newFixedThreadPool(10);
List<CompletableFuture<Company>> companyGenFutures = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
int finalI = i;
companyGenFutures.add(CompletableFuture.supplyAsync(() -> genData("xxx" + finalI + "公司", "四川" + finalI + "地址"), executorService));
}
CompletableFuture.allOf(companyGenFutures.toArray(new CompletableFuture[0])).join();
List<Company> companies = new ArrayList<>();
for (CompletableFuture<Company> companyGenFuture : companyGenFutures) {
Company company = companyGenFuture.get();
companies.add(company);
}
ExportParams exportParams = new ExportParams();
exportParams.setType(ExcelType.XSSF);
exportParams.setSheetName("机构");
String now = new SimpleDateFormat("yyyyMMddmmHHss").format(new Date());
String filename = "机构" + now + ".xlsx";
// v4.4.0
ExcelUtils.batchDownload(companies, Company.class, filename, 200, exportParams, response);
// v3.2.0
// Workbook sheets = ExcelExportUtil.exportBigExcel(exportParams, Company.class, companies);
// ExcelUtils.download(sheets, response, filename);
// // 将 Excel 写入文件或输出流
// File file = new File("C:\\alice\\temp\\company.xlsx");
// FileOutputStream out = new FileOutputStream(file);
// sheets.write(out);
// out.close();
}
@SuppressWarnings("unused")
public class ExcelUtils {
public static final MediaType MS_EXCEL = new MediaType("application", "vnd.ms-excel");
public static final String MS_EXCEL_VALUE = "application/vnd.ms-excel;charset=UTF-8";
public static void download(List<?> dataList, Class<?> pojoClass, String filename,
ExportParams exportParams,
HttpServletResponse response) throws IOException {
try (Workbook sheets = ExcelExportUtil.exportExcel(exportParams, pojoClass, dataList)) {
download(sheets, response, filename);
}
}
// v4.4.0
public static void batchDownload(List<?> dataList, Class<?> pojoClass, String filename,
int batchSize,
ExportParams exportParams,
HttpServletResponse response) throws IOException {
int total = dataList.size();
int pageCount = total / batchSize + (total % batchSize == 0 ? 0 : 1);
try (Workbook sheets = ExcelExportUtil.exportBigExcel(exportParams, pojoClass, (o, i) -> {
if ((int) o < i) {
return null;
}
int skip = (i - 1) * batchSize;
return dataList.stream().skip(skip).limit(batchSize).collect(Collectors.toList());
}, pageCount)) {
download(sheets, response, filename);
}
}
public static void download(Workbook sheets, HttpServletResponse response, String filename) throws IOException {
// response.setHeader(HttpHeaders.CONTENT_TYPE, MS_EXCEL_VALUE);
response.addHeader(HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setHeader(HttpHeaders.CONTENT_DISPOSITION, getDisposition(filename));
ServletOutputStream outputStream = response.getOutputStream();
sheets.write(outputStream);
}
@SneakyThrows
private static String getDisposition(String filename) {
return "attachment;filename=" + new String((filename).getBytes("GB2312"), StandardCharsets.ISO_8859_1);
}
}
注意事项：
不同版本对应的用法有所更改
效率与导出类的复杂程度正相关, 一对多关系越多效率越低，建议不使用一对多
评论区