最近发现了easyExcel工具封装得很好用,为了融入自己的项目进行简单的封装,
如果有更好的方式,欢迎大家点评更正
创建excel写的工具类
public class EasyExcelUtil {
private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);
/**
* 导出excel简单通用的方法
* @param easyExcelBuilder 基础参数
* @param obj 对应的实体类
* @param data 实体对应的数据集
* @return
*/
public static boolean writeExcel(EasyExcelBuilder easyExcelBuilder, Class<?> obj, List<List<?>> data){
try{
String filePath = easyExcelBuilder.getFilePath() + easyExcelBuilder.getFileName();
ExcelWriter excelWriter = EasyExcel.write(filePath, obj).build();
int flag = 0;
List<Set<String>> includeColumnFiledNames = easyExcelBuilder.getIncludeColumnFiledNames();
if(includeColumnFiledNames != null){
}
List<Set<String>> excludeColumnFiledNames = easyExcelBuilder.getExcludeColumnFiledNames();
for (String sheetName : easyExcelBuilder.getSheetNames()) {
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.writerSheet(flag, sheetName).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy());
List<?> objects = data.get(flag);
//导入特定的字段
if(includeColumnFiledNames != null){
if(flag < includeColumnFiledNames.size()){
Set<String> includeColumnsName = includeColumnFiledNames.get(flag);
excelWriterSheetBuilder.includeColumnFiledNames(includeColumnsName);
}
}else if(excludeColumnFiledNames != null){
if(flag < excludeColumnFiledNames.size()){
Set<String> excludeColumnName = excludeColumnFiledNames.get(flag);
excelWriterSheetBuilder.excludeColumnFiledNames(excludeColumnName);
}
}
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置
headWriteCellStyle.setFillForegroundColor(easyExcelBuilder.getHeadColor().index);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
if(easyExcelBuilder.getContentColor() != null){
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setFillForegroundColor(easyExcelBuilder.getContentColor().index);
}
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy);
excelWriter.write(objects, excelWriterSheetBuilder.build());
flag++;
}
/// 千万别忘记finish 会帮忙关闭流
excelWriter.finish();
//需要同步到oss
String ossPath = easyExcelBuilder.getOssPath();
if(!StringUtils.isEmpty(ossPath)){
if (ossPath.startsWith("/")) {
ossPath = ossPath.substring(1);
}
///######## 该处为上传文件的逻辑 上传完文件会进行删除原文件
// AliOssClientUtil.upload(filePath, ossPath + easyExcelBuilder.getFileName());
//删除本地文件
try{
new File(filePath).delete();
}catch (Exception ex){
logger.error("EasyExcelUtil, deleted file error", ex);
}
}
return true;
}catch (Exception e){
logger.error("EasyExcelUtil,writeExcel, error : {}", JSONObject.toJSONString(easyExcelBuilder), e);
}
return false;
}
}
EasyExcelBuilder 导入基础数据的创建者
public class EasyExcelBuilder {
/**
* 文件名称 (最好加上后缀)
*/
private String fileName = "模板.xlsx";
/**
* sheet
*/
private List<String> sheetNames = Collections.singletonList("模板");
/**
* 数据类型对应的数据
*/
private List<List<String>> dataModels;
/**
* 特定字段导出支持多个sheet 如果有这个数据即会忽略excludeColumnFiledNames
*/
private List<Set<String>> includeColumnFiledNames;
/**
* 忽略特定字段导出 支持多个sheet、
*/
private List<Set<String>> excludeColumnFiledNames;
/**
* 阿里oos存储路径(不包含文件名)无该路径时默认不上传到oos
*/
private String ossPath;
/** 服务器文件路径 */
private String filePath = "/data/export/excel/";
/** 表头颜色 目前只支持统一颜色 */
private IndexedColors headColor = IndexedColors.GREY_25_PERCENT;
/** 内容颜色 目前也只支持统一颜色 */
private IndexedColors contentColor;
public EasyExcelBuilder buildFileName(String fileName) {
String newFileName = fileName.replaceAll("/", "");
if (!newFileName.contains(".")) {
newFileName += ".xlsx";
}
this.fileName = newFileName;
return this;
}
public EasyExcelBuilder buildSheetName(List<String> sheetNames) {
this.sheetNames = sheetNames;
return this;
}
public EasyExcelBuilder buildDataModels(List<List<String>> dataModels) {
this.dataModels = dataModels;
return this;
}
public EasyExcelBuilder buildExcludeColumnFiledNames(List<Set<String>> excludeColumnFiledNames) {
this.excludeColumnFiledNames = excludeColumnFiledNames;
return this;
}
public EasyExcelBuilder buildIncludeColumnFiledNames(List<Set<String>> includeColumnFiledNames) {
this.includeColumnFiledNames = includeColumnFiledNames;
return this;
}
public EasyExcelBuilder buildOssPath(String ossPath) {
this.ossPath = ossPath;
return this;
}
public EasyExcelBuilder buildFilePath(String filePath) {
this.filePath = filePath;
return this;
}
public EasyExcelBuilder buildHeadColor(IndexedColors headColor) {
this.headColor = headColor;
return this;
}
public EasyExcelBuilder buildContentColor(IndexedColors contentColor) {
this.contentColor = contentColor;
return this;
}
public EasyExcelBuilder build() {
return this;
}
public String getFileName() {
return fileName;
}
public List<String> getSheetNames() {
return sheetNames;
}
public List<List<String>> getDataModels() {
return dataModels;
}
public List<Set<String>> getIncludeColumnFiledNames() {
return includeColumnFiledNames;
}
public List<Set<String>> getExcludeColumnFiledNames() {
return excludeColumnFiledNames;
}
public String getOssPath() {
return ossPath;
}
public String getFilePath() {
return filePath;
}
public IndexedColors getHeadColor() {
return headColor;
}
public IndexedColors getContentColor() {
return contentColor;
}
}
接下来的test类 可以根据需要进行build, 如果需要上传到服务器或者oss等地方 只需要 添加.buildOssPath(“oss的路径”) (oss需要自行配置)
@Test
public void writeExcel(){
//设置需要导出的字段名
List<Set<String>> includeColumnFiledNamesList = new ArrayList<>();
Set<String> includeColumnFiledNames = new HashSet<String>(){{add("name");add("phone");}};
includeColumnFiledNamesList.add(includeColumnFiledNames);
Set<String> includeColumnFiledNames1 = new HashSet<String>(){{add("name");add("phone");add("email");add("address");}};
includeColumnFiledNamesList.add(includeColumnFiledNames1);
//根据需要设置模式
EasyExcelBuilder write = new EasyExcelBuilder()
.buildFileName("文件名称")
.buildSheetName(Arrays.asList("第一个sheet","第二个sheet"))
// .buildOssPath("excel/default/201906/")
.buildIncludeColumnFiledNames(includeColumnFiledNamesList)
// .buildExcludeColumnFiledNames(includeColumnFiledNamesList)
.buildFilePath("/Users/xuxiong/Downloads/")
.buildHeadColor(IndexedColors.LIGHT_YELLOW)
// .buildContentColor(IndexedColors.RED)
.build();
List<List<?>> data = new ArrayList<>();
data.add(data("a列"));
data.add(data("b列"));
EasyExcelUtil.writeExcel(write, Demo.class, data);
}
private static List<Demo> data(String text){
List<Demo> demos = new ArrayList<>();
Demo demo;
for(int i = 0; i < 10; i++){
demo = new Demo();
demo.setName("name" + i + "_"+text);
demo.setAge(i);
demo.setAddress("address" + i+ "_"+text);
demo.setEmail("email" + i+ "_"+text);
demo.setPhone("phone" + i+ "_"+text);
demos.add(demo);
}
return demos;
}
导出的截图