Java中excel操作：把业务数据生成excel文件并且存放指定目录

达成效果：如标题

1、直接上代码

            <!-- 阿里excel-->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.0.2</version>
            </dependency>
            <dependency>
                <groupId>com.fasterxml.jackson.core</groupId>
                <artifactId>jackson-annotations</artifactId>
                <version>2.10.3</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.1.2</version>
            </dependency>

import org.apache.commons.lang3.concurrent.BasicThreadFactory;
import org.springframework.stereotype.Service;

import java.util.concurrent.*;

@Service
public class ScheduledPool {
    private static ScheduledExecutorService pools;

    static {
        pools = new ScheduledThreadPoolExecutor(2,
                new BasicThreadFactory.
                        Builder().namingPattern("example-schedule-pool-%d").daemon(true).build());
    }

    public static ScheduledExecutorService getInstance() {
        if (pools == null) {
            synchronized (ScheduledPool.class) {
                if (pools == null) {
                    pools = new ScheduledThreadPoolExecutor(2,
                            new BasicThreadFactory.
                                    Builder().namingPattern("example-schedule-pool-%d").daemon(true).build());
                }
            }
        }
        return pools;
    }
}

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.xxxxxx.aaaaaa.exception.XxxxException;
import com.xxxxxx.aaaaaa.utils.ScheduledPool;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.io.*;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

@Data
class BasicInfo {
    private List<ItemInfo> itemList;
}

@Data
class ItemInfo {
    private int field1;
    private String field2;
}

class CustomImageModifyHandler implements CellWriteHandler {

}

public class DataToExcelUtil {
    public static void main(String[] args) {
        BasicInfo basicInfo = new BasicInfo();
        List<ItemInfo> itemList = new ArrayList<>();
        ItemInfo itemInfo = new ItemInfo();
        itemInfo.setField1(123456);
        itemInfo.setField2("test!!!");
        itemList.add(itemInfo);
        basicInfo.setItemList(itemList);
        String testDataToExcel = generateExcel(basicInfo,
                "C:\\Users\\tuyua\\Desktop\\空白图片空白图片\\DataToExcelTemplate.xlsx",
                "testDataToExcel.xls",
                "C:\\Users\\tuyua\\Desktop\\空白图片空白图片\\",
                true);
        System.out.println(testDataToExcel);
    }

    /**
     * @param data               业务数据，
     * @param templateModulePath 模板路径
     * @param fileName           文件名
     * @param dir                生成的excel文件存放的目录
     * @param justFillList       是否只填充list
     * @return 生成的excel文件所在路径（绝对路径）
     */
    public static String generateExcel(Object data, String templateModulePath, String fileName, String dir,
                                       boolean justFillList) {
        if (data == null) {
            throw new BusinessMallException("模板生成excel异常，数据不能为空");
        }
        FileOutputStream bos = null;
        ExcelWriter excelWriter = null;
        try {
            dir = dir + System.currentTimeMillis() + fileName;
            File file = new File(dir);
            // 校验文件夹目录是否存在，不存在就创建一个目录
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }
            String filePath = file.getPath();
            ScheduledExecutorService newScheduledThreadPool = ScheduledPool.getInstance();
            newScheduledThreadPool.schedule(new TimerTask() {
                @Override
                public void run() {
                    try {
                        File file = new File(filePath);
                        if (file.exists()) {
                            if (file.delete()) {
                                System.out.println("删除文件");
                            } else {
                                System.out.println("删除失败");
                            }

                        }
                    } catch (Exception exception) {
                        exception.printStackTrace();
                    }
                }
            }, 2 * 60, TimeUnit.SECONDS);
            bos = new FileOutputStream(file);
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            //设置 垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            excelWriter = EasyExcel.write(bos)
                    .registerWriteHandler(new CustomImageModifyHandler())
                    .withTemplate(templateModulePath).
                    build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //常规数据填充
            HashMap<String, Object> map = ObjectToMap(data);
            if (!justFillList) {
                excelWriter.fill(map, writeSheet);
            }
            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            // 如果数据量大 list不是最后一行 参照下一个
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            //如果有集合就需要填充集合
            for (Map.Entry<String, Object> entrySet : map.entrySet()) {
                Object entryValue = entrySet.getValue();
                if (entryValue != null && entryValue instanceof Collection) {
                    excelWriter.fill(entryValue, fillConfig, writeSheet);
                }
            }
            return filePath;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (bos != null) {
                try {
                    bos.close();
                } catch (IOException e) {
                }
            }
        }
    }

    public static HashMap<String, Object> ObjectToMap(Object data) {
        HashMap<String, Object> result = new HashMap<>();
        try {
            Field[] filedList = data.getClass().getDeclaredFields();
            for (Field field : filedList) {
                field.setAccessible(true);
                Object targetValue = field.get(data);
                String name = field.getName();
                JsonFormat jsonFormatAnnotation = field.getAnnotation(JsonFormat.class);
                String dateDefaultPattern = "yyyy-MM-dd";
                if (jsonFormatAnnotation != null) {
                    String datePattern = jsonFormatAnnotation.pattern();
                    if (StringUtils.isBlank(datePattern)) {
                        datePattern = dateDefaultPattern;
                    }
                    SimpleDateFormat dateFormat = new SimpleDateFormat(datePattern);
                    if (targetValue != null) {
                        targetValue = dateFormat.format((Date) targetValue);
                    }
                }
                if (targetValue instanceof Collection) {
                    List<Object> targetMapList = new ArrayList<>();
                    List<Object> targetList = (List) targetValue;
                    if (CollUtil.isNotEmpty(targetList)) {
                        for (Object item : targetList) {
                            targetMapList.add(ObjectToMap(item));
                        }
                    }
                    targetValue = targetMapList;
                }
                if (targetValue != null) {
                    result.put(name, targetValue);
                } else {
                    result.put(name, "");
                }

            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return result;
    }
}

success!!!