前言
实际开发经常遇到文件的导入导出,传统的导入导出都是在明确的表、类、方法下进行,如果在不限制你查哪张表,用什么类,调什么方法的前提下,如何实现动态导入导出,本文将给你提供一个可以扩展的思路。生成Excel比较有名的框架有Apache poi,但存在一个严重的问题就是非常的耗内存,系统并发量不大的话可能还行,否则并发上来后一定会OOM或者JVM频繁的full gc。
本文将采用EasyExcel,以使用简单,节省内存著称。
正文
引入依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.14</version>
</dependency>
实体类:
@Getter
@Setter
//@Accessors(chain = true)
//堆代码 duidaima.com
//EasyExcel是使用net.sf.cglib.beans.BeanMap 工具类拷贝的, 该工具类的set方法为void
//而@Accessors(chain = true)的set方法返回的是当前对象,这会导致EasyExcel解析不到数据
@TableName("demo1")
public class Demo1 implements Serializable {
private static final long serialVersionUID = 1L;
@ExcelProperty("ID")
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
@ExcelProperty("用户名")
@TableField("name")
private String name;
@ExcelProperty("年龄")
@TableField("age")
private String age;
}
源码ModelBuildEventListener.buildUserModel() -> BeanMap dataMap = BeanMapUtils.create(resultModel);:
public class BeanMapUtils {
public BeanMapUtils() {
}
public static BeanMap create(Object bean) {
BeanMap.Generator gen = new BeanMap.Generator();
gen.setBean(bean);
gen.setNamingPolicy(BeanMapUtils.EasyExcelNamingPolicy.INSTANCE);
return gen.create();
}
public static class EasyExcelNamingPolicy extends DefaultNamingPolicy {
public static final EasyExcelNamingPolicy INSTANCE = new EasyExcelNamingPolicy();
public EasyExcelNamingPolicy() {
}
protected String getTag() {
return "ByEasyExcelCGLIB";
}
}
}
导出
工具类:
@Slf4j
public class EasyExcelUtil <T>{
private static final String downloadPath = "/data/";
//每1w分一个sheet
private static final int batch = 10000;
public Class<T> clazz;
public EasyExcelUtil(Class<T> clazz) {
this.clazz = clazz;
}
/**
* 对list数据源将其里面的数据导入到excel表单
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @param excludeColumnFiledNames 需要移除的标题
*/
public void exportExcel(List<T> list,String sheetName,
Set<String> excludeColumnFiledNames, HttpServletResponse response) {
ExcelWriter build = null;
try{
String filename = UUID.randomUUID() + "_" + sheetName + ".xlsx";
File file = new File(downloadPath+File.separator+ filename);
response.setContentType("aapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(file.getName(), "UTF-8"));
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
List<List<T>> lists = ListUtil.partition(list,batch);
build = EasyExcel.write(response.getOutputStream(), clazz).registerConverter(new EasyTimestampConverter())
.excludeColumnFiledNames(excludeColumnFiledNames).build();
AtomicInteger count = new AtomicInteger(0);
for (List<T> item : lists) {
//获取sheet对象
WriteSheet mainSheet = EasyExcel.writerSheet(count.get(), sheetName + "_" + count.get()).build();
build.write(item, mainSheet);
count.getAndIncrement();
}
}catch (Exception e){
log.error("导出Excel异常{}", e.getMessage());
}finally {
if(build!=null) build.finish();
}
}
}
时间戳转换:
public class EasyTimestampConverter implements Converter<Timestamp> {
//Java数据类型
@Override
public Class<?> supportJavaTypeKey() {
return Timestamp.class;
}
//Excel文件中单元格的数据类型
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
/**
* 这里读的时候会调用
*/
@Override
public Timestamp convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) throws Exception {
Date date;
if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
date = DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
globalConfiguration.getUse1904windowing(), null);
} else {
date = DateUtil.getJavaDate(cellData.getNumberValue().doubleValue(),
contentProperty.getDateTimeFormatProperty().getUse1904windowing(), null);
}
if (date != null){
return new Timestamp(date.getTime());
}else {
return null;
}
}
/**
* 这里写的时候会调用
*
*/
@Override
public WriteCellData<?> convertToExcelData(Timestamp value, ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) throws Exception {
WriteCellData<?> cellData = new WriteCellData<>(value);
String format = null;
if (contentProperty != null && contentProperty.getDateTimeFormatProperty() != null) {
format = contentProperty.getDateTimeFormatProperty().getFormat();
}
WorkBookUtil.fillDataFormat(cellData, format, DateUtils.defaultDateFormat);
return cellData;
}
}
样式与列合并:
public class CustomStyleStrategy {
public static HorizontalCellStyleStrategy getStyleStrategy(){
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为灰色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short)12);
// 字体样式
headWriteFont.setFontName("Frozen");
// 字体颜色
headWriteFont.setColor(IndexedColors.BLACK1.getIndex());
headWriteCellStyle.setWriteFont(headWriteFont);
// 自动换行
headWriteCellStyle.setWrapped(false);
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了FillPatternType所以可以不指定
//contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
// 背景白色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 水平居中方式
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直居中方式
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontHeightInPoints((short)12);
// 字体样式
contentWriteFont.setFontName("Calibri");
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
public class MergeStrategy extends AbstractMergeStrategy {
/**
* 合并的列编号,从0开始
* 指定的index或自己按字段顺序数
*/
private Set<Integer> mergeCellIndex = new HashSet<>();
/**
* 数据集大小,用于区别结束行位置
*/
private Integer maxRow = 0;
// 禁止无参声明
private MergeStrategy() {
}
public MergeStrategy(Integer maxRow, int... mergeCellIndex) {
Arrays.stream(mergeCellIndex).forEach(item -> {
this.mergeCellIndex.add(item);
});
this.maxRow = maxRow;
}
private Map<Integer, MergeRange> lastRow = new HashedMap();
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
int currentCellIndex = cell.getColumnIndex();
// 判断该行是否需要合并
if (mergeCellIndex.contains(currentCellIndex)) {
DataFormatter dataFormatter = new DataFormatter();
String currentCellValue = dataFormatter.formatCellValue(cell);
int currentRowIndex = cell.getRowIndex();
if (!lastRow.containsKey(currentCellIndex)) {
// 记录首行起始位置
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
return;
}
//有上行这列的值了,拿来对比
MergeRange mergeRange = lastRow.get(currentCellIndex);
if (!(mergeRange.lastValue != null && mergeRange.lastValue.equals(currentCellValue))) {
// 结束的位置触发下合并,同行同列不能合并,会抛异常
if (mergeRange.startRow != mergeRange.endRow || mergeRange.startCell != mergeRange.endCell) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(mergeRange.startRow, mergeRange.endRow, mergeRange.startCell, mergeRange.endCell));
}
// 更新当前列起始位置
lastRow.put(currentCellIndex, new MergeRange(currentCellValue, currentRowIndex, currentRowIndex, currentCellIndex, currentCellIndex));
}
// 合并行 + 1
mergeRange.endRow += 1;
// 结束的位置触发下最后一次没完成的合并
if (relativeRowIndex.equals(maxRow - 1)) {
MergeRange lastMergeRange = lastRow.get(currentCellIndex);
// 同行同列不能合并,会抛异常
if (lastMergeRange.startRow != lastMergeRange.endRow || lastMergeRange.startCell != lastMergeRange.endCell) {
sheet.addMergedRegionUnsafe(new CellRangeAddress(lastMergeRange.startRow, lastMergeRange.endRow, lastMergeRange.startCell, lastMergeRange.endCell));
}
}
}
}
}
class MergeRange {
public int startRow;
public int endRow;
public int startCell;
public int endCell;
public String lastValue;
public MergeRange(String lastValue, int startRow, int endRow, int startCell, int endCell) {
this.startRow = startRow;
this.endRow = endRow;
this.startCell = startCell;
this.endCell = endCell;
this.lastValue = lastValue;
}
}
如果需要加其他自定义样式的话,可以参考官网:https://easyexcel.opensource.alibaba.com/
测试:
@GetMapping("export")
public void export2(HttpServletResponse response){
List<Demo1> list = demo1Service.list();
EasyExcelUtil easyExcelUtil = new EasyExcelUtil(Demo1.class);
Set<String> excludeColumnFiledNames = new HashSet<>();
easyExcelUtil.exportExcel(list,"用户信息", excludeColumnFiledNames,response);
}
导入
实现接口ApplicationContextAware:
@Component
public class SpringUtils implements ApplicationContextAware {
private static ApplicationContext applicationContext;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
SpringUtils.applicationContext = applicationContext;
}
public static <T> T getBean(String beanName) {
if(applicationContext.containsBean(beanName)){
return (T) applicationContext.getBean(beanName);
}else{
return null;
}
}
public static <T> Map<String, T> getBeansOfType(Class<T> baseType){
return applicationContext.getBeansOfType(baseType);
}
//通过反射调用其方法
public static void executeServiceMethod(String serviceName, String methodName, Class<?>[] parameterTypes, Object... parameters) throws Exception {
Object serviceInstance = getBean(serviceName);
Class<?> serviceClass = serviceInstance.getClass();
Method method = serviceClass.getDeclaredMethod(methodName, parameterTypes);
method.setAccessible(true);
//1.要调用方法的对象。如果方法是静态方法,则可以传递 null,否则需要传递该方法所属的对象实例
//2.要传递给方法的参数。这是一个可变参数,可以接受任意数量的参数,包括零个参数。参数的类型和顺序需要与方法的声明一致
method.invoke(serviceInstance, parameters);
}
}
继承AnalysisEventListener:
@Slf4j
public class ReadExcelListener<T> extends AnalysisEventListener<T> {
/**
* 堆代码 duidaima.com
* 每隔1w条存储数据库,然后清理list,方便内存回收
*/
private static final int BATCH_COUNT = 10000;
/**
* 缓存的数据
*/
private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
public String serviceName;
public String method;
public Class<?>[] parameterTypes;
public Object[] parameters;
/**
* @param serviceName 业务类名称
* @param method 业务类方法名称
* @param parameterTypes 业务类方法参数类型
* @param parameters 业务类方法参数(不包含据解析集合cachedDataList)
*/
public ReadExcelListener(String serviceName,String method,Class<?>[] parameterTypes,Object... parameters) {
this.serviceName = serviceName;
this.method = method;
this.parameterTypes = parameterTypes;
this.parameters = parameters;
}
/**
* 这个每一条数据解析都会来调用
*
**/
@Override
public void invoke(T t, AnalysisContext analysisContext) {
cachedDataList.add(t);
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存储完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
/**
* 所有数据解析完成了都会来调用
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData(){
log.info("{}条数据,开始存储数据库!", cachedDataList.size());
try {
// 转换为 ArrayList
List<Object> arrayList = new ArrayList<>(Arrays.asList(parameters));
// 在索引位置插入新元素,即解析的数据集合,插入位置与调用方法保持一致
arrayList.add(0, cachedDataList);
// 将 ArrayList 转换回数组
parameters = arrayList.toArray(new Object[0]);
SpringUtils.executeServiceMethod(serviceName,method,parameterTypes,parameters);
} catch (Exception e) {
throw new RuntimeException(e);
}
log.info("存储数据库成功!");
}
}
追加导入方法EasyExcelUtil:
/**
* 对file数据源将其里面的数据导入到数据库中
*
* @param file 导入文件
* @param serviceName 业务类名称
* @param method 业务类方法名称
* @param parameterTypes 业务类方法参数类型
*/
public void importExcel(MultipartFile file,String serviceName,
String method,Class<?>... parameterTypes) throws IOException {
EasyExcel.read(file.getInputStream(), clazz, new ReadExcelListener(serviceName,method,parameterTypes,1)).sheet().doRead();
}
测试:
@PostMapping("import")
public Object importFile(MultipartFile file) throws IOException {
EasyExcelUtil easyExcelUtil = new EasyExcelUtil(Demo1.class);
//这里稍微注意一下,小编代码是通过beanName获取servcie
//所以在相应的service使用了@Service(value = "demo1Service")
//不然会报找不到该bean对象,你也可以使用类型获取bean对象
easyExcelUtil.importExcel(file,"demo1Service","insertBatch",List.class,Integer.class);
return "success";
}
说明:小编通过重载在service写了两个方法,实际调用的是insertBatch(List<Demo1> list, Integer type),这里通过控制台打印也可以看出来
@Override
public void insertBatch(List<Demo1> list) {
this.saveBatch(list);
System.out.println("********************************");
}
@Override
public void insertBatch(List<Demo1> list, Integer type) {
this.saveBatch(list);
System.out.println("================================");
}
至此导入导出已经完成,没错就是这么简单,当然这也是抛转引玉,希望大家看了这篇文章,可以借鉴泛型和反射玩法,做更多更好玩的封装,比如通过反射获取注解信息:
/**
* 堆代码 duidaima.com
* 获取字段注解信息
*/
public List<Object[]> getFields() {
List<Object[]> fields = new ArrayList<Object[]>();
List<Field> tempFields = new ArrayList<>();
tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
for (Field field : tempFields) {
// 单注解
if (field.isAnnotationPresent(Excel.class)) {
Excel attr = field.getAnnotation(Excel.class);
if (attr != null && (attr.type() == Type.ALL || attr.type() == type)) {
field.setAccessible(true);
// ReflectionUtils.makeAccessible(field);
fields.add(new Object[]{field, attr});
}
}
// 多注解
if (field.isAnnotationPresent(Excels.class)) {
Excels attrs = field.getAnnotation(Excels.class);
Excel[] excels = attrs.value();
for (Excel attr : excels) {
if (attr != null && (attr.type() == Type.ALL || attr.type() == type)) {
field.setAccessible(true);
// ReflectionUtils.makeAccessible(field);
fields.add(new Object[]{field, attr});
}
}
}
}
return fields;
}