在进行集合数据生成到指定excell中,有多条数据集合(多个不同对象)和单对象集合区分,下面是关于不同类型集合的生成代码:
1.(ArrayList<List<?>> dataList 形式,多sheet
public static void simpleWriteSheets(ArrayList<List<?>> dataList){
//路径
String filename=ConstantInfo.PATH+"EasyTest-"+System.currentTimeMillis() +".xlsx";
ExcelWriter excelWriter=excelWriter = EasyExcel.write(filename).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
for (int i = 0; i < dataList.size(); i++) {
List<?> data = dataList.get(i);
Class<?> aClass = data.get(0).getClass();
//获取类中指定注解对象
TableName declaredAnnotation = aClass.getDeclaredAnnotation(TableName.class);
//获取对象中的值,得到表名
String value = declaredAnnotation.value();
//sheet对象
WriteSheet writeSheet = EasyExcel.writerSheet(i,value+i).head(aClass).build();
//写入数据和sheet对象
excelWriter.write(data,writeSheet);
}
//全部写入后再关流
excelWriter.finish();
}
}
2.<List<?> dataList形式 单sheet
public static void simpleWriteSheet(List<?> dataList){
String filename=ConstantInfo.PATH+"EasyTest-"+System.currentTimeMillis() +".xlsx";
Class<?> aClass = dataList.get(0).getClass();
//获取类中指定注解对象
TableName declaredAnnotation = aClass.getDeclaredAnnotation(TableName.class);
String value = declaredAnnotation.value();
EasyExcel.write(filename,aClass).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet(value).doWrite(dataList);
}
实体类样式
我这个需要用到Myabtis-plus的注解 不同注解可以使得表格样式发生变化
@Data
@AllArgsConstructor
@NoArgsConstructor
@ContentRowHeight(15)//文本的高度
@HeadRowHeight(25)//标题的行高
@ColumnWidth(27)//每个标题的列宽
// 头背景设置成红色 IndexedColors.RED.getIndex()
//@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 10)
// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
//@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
// 内容字体设置成20
@ContentFontStyle(fontHeightInPoints = 10)
@TableName(value = "user")
public class User{
// 这一列 每隔2行 合并单元格
//@ContentLoopMerge(eachRow = 2)
@ColumnWidth(10)
@ExcelProperty(value = {"User","id"},index = 0)
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
@ColumnWidth(10)
@ExcelProperty(value = {"User","username"},index = 1)
@TableField(value = "username")
private String userName;
@ColumnWidth(10)//设置独特的列宽
@ExcelProperty(value = {"Ps","password"},index = 2)
// 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
//@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
// 字符串的头字体设置成20
//@HeadFontStyle(fontHeightInPoints = 10)
// 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
// 字符串的内容字体设置成20
//@ContentFontStyle(fontHeightInPoints = 30)
@TableField(value = "password")
private String passWord;
private Long Number;
}
如果遇到Mybatis-plus和Mybatis的冲突问题,我的版本是这样的
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>`
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1</version>
<exclusions>
<exclusion>
<artifactId>mybatis</artifactId>
<groupId>org.myabtis</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.3.1</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.3</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
</exclusion>
<exclusion>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
</exclusion>
</exclusions>
</dependency>