easypoi大数据导出，感觉真的好用

比眉伴天荒 2022-05-16 13:37 882阅读 0赞

我用的这个poi版本

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.2.0</version>
            </dependency>

先来个导出的基本例子:

public static void main(String[] args) {
    
            try {
                List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
                //构造对象等同于@Excel
                entity.add(new ExcelExportEntity("姓名", "name"));
                entity.add(new ExcelExportEntity("性别", "sex"));
                ExcelExportEntity r = new ExcelExportEntity("入学时间", "date");
                r.setFormat("yyyy-MM-dd");
                entity.add(r);
                ExcelExportEntity r2 = new ExcelExportEntity("入学交钱", "money");
                entity.add(r2);
                r2.setNumFormat("###,###,###.##");
                entity.add(new ExcelExportEntity("sku", "sku"));
    
                Workbook workbook = null;
    
                List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
                for (int i = 0; i < 3000003; i++) {
                    Map<String, Object> map = new HashMap<String, Object>();
                    map.put("name", "tanlei" + i);
                    map.put("sex", i % 2 == 0 ? 1 : 0);
                    map.put("date", new Date());
                    map.put("money", 10000000.122 + i);
                    map.put("sku", "1111111111111111");
                    list.add(map);
                    if (list.size() == 10000) {
                        workbook = ExcelExportUtil.exportBigExcel(new ExportParams(null, "Sheet1"), entity, list);
                        list.clear();
                    }
                }
                ExcelExportUtil.closeExportBigExcel();
                FileOutputStream fos = new FileOutputStream("D:/t.xlsx");
                workbook.write(fos);
                fos.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
    
    
        }

几百万行还是很轻松的。

从数据库导出要依赖于jdbc的resultset，resultset的rs.next操作是不会一次性将数据库数据全部取出来的，不会造成java内存泄漏。这里可以参考我的另一篇文章:[https://blog.csdn.net/u012572955/article/details/88534950][https_blog.csdn.net_u012572955_article_details_88534950]

我这里写了个简单的表头配置，将excel表头信息配置到数据库中。数据库excel\_column\_info类似如下：

[{
    		"columnIndex": 1,
    		"dbFieldEnName": "column1",
    		"excelColumnName": "商户编码",
    		"type": "String"
    	},
    	{
    		"columnIndex": 2,
    		"dbFieldEnName": "column2",
    		"excelColumnName": "订单日期",
    		"format": "yyyy-MM-dd",
    		"type": "Date"
    	},
    	{
    		"columnIndex": 3,
    		"dbFieldEnName": "column3",
    		"excelColumnName": "清算日期",
    		"format": "yyyy-MM-dd",
    		"type": "Date"
    	},
    	{
    		"columnIndex": 4,
    		"dbFieldEnName": "column4",
    		"excelColumnName": "订单编号",
    		"type": "String"
    	},
    	{
    		"columnIndex": 5,
    		"dbFieldEnName": "column5",
    		"excelColumnName": "订单行号",
    		"type": "String"
    	},
    	{
    		"columnIndex": 6,
    		"dbFieldEnName": "column6",
    		"excelColumnName": "交易类型",
    		"type": "String"
    	},
    	{
    		"columnIndex": 7,
    		"dbFieldEnName": "column7",
    		"excelColumnName": "订单金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 8,
    		"dbFieldEnName": "column8",
    		"excelColumnName": "运费",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 9,
    		"dbFieldEnName": "column9",
    		"excelColumnName": "佣金比例",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 10,
    		"dbFieldEnName": "column10",
    		"excelColumnName": "佣金金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 11,
    		"dbFieldEnName": "column11",
    		"excelColumnName": "平台红包金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 12,
    		"dbFieldEnName": "column12",
    		"excelColumnName": "平台券金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 13,
    		"dbFieldEnName": "column13",
    		"excelColumnName": "平台礼品卡金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 14,
    		"dbFieldEnName": "column14",
    		"excelColumnName": "平台积分金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 15,
    		"dbFieldEnName": "column15",
    		"excelColumnName": "平台满减金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 16,
    		"dbFieldEnName": "column16",
    		"excelColumnName": "平台其它优惠金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 17,
    		"dbFieldEnName": "column17",
    		"excelColumnName": "苏宁优惠金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 18,
    		"dbFieldEnName": "column18",
    		"excelColumnName": "清算金额",
    		"format": "###,###,###.##",
    		"type": "Double"
    	},
    	{
    		"columnIndex": 19,
    		"dbFieldEnName": "column19",
    		"excelColumnName": "备注",
    		"type": "String"
    	}
    ]

columnIndex代表数据查询sql是第几个字段，用于排序的，也代表是excel的第几个字段，dbFieldEname代表对应的sql别名，excelColumnName代表excel中文字段名，type代表excel数据类型，format代表格式化操作，像double为了避免科学记数法，date避免英文时间。

对应数据库查询sql如下:

select 
    oit.cmmdy_id AS "column1",
    oit.date_updated AS "column2",
    oit.date_updated AS "column3",
    oi.order_id AS "column4",
    oit.order_item_id AS "column5",
    case when oit.status = '36' then '退货'
    else '交易' end AS "column6",
    round((oit.cmmdy_price * oit.cmmdy_num),2) AS "column7",
    oit.shipping_fee AS "column8",
    COALESCE(cast(gci.rate as numeric),0) as "column9",
    round((oit.cmmdy_price*oit.cmmdy_num)*COALESCE(cast(gci.rate as numeric),0),2) as "column10",
    0 as "column11",
    oit.share_coupon_reduce AS "column12",
    0 as "column13",
    0 as "column14",
    0 as "column15",
    0 as "column16",
    0 as "column17",
    round((oit.cmmdy_price * oit.cmmdy_num) + oit.shipping_fee-0 + (oit.cmmdy_price*oit.cmmdy_num)*COALESCE(cast(gci.rate as numeric),0),2) as "column18",
    '' as "column19"
    from mall_order.order_info oi
    left join mall_order.order_item oit
    on oi.order_id = oit.order_id
    left join ims.G_ITEM_SKU gsku
    on  oit.cmmdy_id = gsku.sku_code
    left join ims.g_commision_info gci
    on gsku.catalog_id = gci.catalog_id
    where oit.status in ('17','18','19','20','21','23','25','27','31','33','35','36','37')

具体封装后的代码:

FileUtil:

public class FileUtil {
    
        private static final Logger logger = LogManager.getLogger(FileUtil.class);
        public static final int ZIP_APPEND = 0;
        public static final int ZIP_REFRESH = 1;
    
        /**
         * 写文件
         *
         * @param paths
         * @param fileName
         * @param content
         * @return
         */
    
        public static boolean writeFile(String paths, String fileName, String content) {
            //默认追加方式
            return writeFile(paths,fileName,content,StandardOpenOption.APPEND);
        }
        public static boolean writeFile(String paths, String fileName, String content,StandardOpenOption openOption) {
            try {
                if (!Files.exists(Paths.get(paths))) {
                    Files.createDirectory(Paths.get(paths));
                }
                if (!Files.exists(Paths.get(paths + File.separator + fileName))) {
                    Files.createFile(Paths.get(paths + File.separator + fileName));
                }
                Files.write(Paths.get(paths + File.separator + fileName), content.getBytes(), openOption);
            } catch (IOException e) {
                logger.error("写文件异常，路径：{}。文件名字：{}。", paths, fileName, e);
                return false;
            }
            return true;
        }
    
    
        /**
         * 删除文件
         *
         * @param dir
         * @param fileName
         * @return
         */
        public static boolean delFile(String dir, String fileName) {
            try {
                Files.deleteIfExists(Paths.get(dir + File.separator + fileName));
            } catch (IOException e) {
                logger.error("删除文件异常，路径：{}，文件名字：{}。", dir, fileName, e);
                return false;
            }
            return true;
        }
    
    
        /**
         * 清空路径(不管是文件还是文件夹)
         *
         * @param path 要删除的path
         * @return 删除成功返回 true，否则返回 false。
         */
        public static boolean deletePath(String path) {
            File file = new File(path);
            // 判断目录或文件是否存在
            if (!file.exists()) {  // 不存在返回 false
                return false;
            } else {
                // 判断是否为文件
                if (file.isFile()) {  // 为文件时调用删除文件方法
                    return deleteFile(path);
                } else {  // 为目录时调用删除目录方法
                    return deleteDirectory(path);
                }
            }
        }
    
        /**
         * 删除单个文件
         *
         * @param path 被删除文件path
         * @return 删除成功返回true，否则返回false
         */
        public static boolean deleteFile(String path) {
            try {
                return Files.deleteIfExists(Paths.get(path));
            } catch (IOException e) {
                logger.error("删除文件异常，路径：{}", path, e);
                return false;
            }
        }
    
        /**
         * 删除目录以及目录下的文件
         *
         * @param path 被删除目录的路径
         * @return 目录删除成功返回true，否则返回false
         */
        public static boolean deleteDirectory(String path) {
            //如果path不以文件分隔符结尾，自动添加文件分隔符
            if (!path.endsWith(File.separator)) {
                path = path + File.separator;
            }
            File dirFile = new File(path);
            //如果dir对应的文件不存在，或者不是一个目录，则退出
            if (!dirFile.exists() || !dirFile.isDirectory()) {
                return false;
            }
            boolean flag = true;
            //删除文件夹下的所有文件(包括子目录)
            File[] files = dirFile.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    //删除子文件
                    flag = deleteFile(files[i].getAbsolutePath());
                    if (!flag) break;
                } else {
                    //删除子目录
                    flag = deleteDirectory(files[i].getAbsolutePath());
                    if (!flag) break;
                }
            }
            if (!flag) return false;
            //删除当前目录
            if (dirFile.delete()) {
                return true;
            } else {
                return false;
            }
        }
    
    
        /**
         * @param srcPath
         * @param password
         * @param dstPath
         * @param type
         * @return
         */
        //可选覆盖或追加文件
        public static boolean encryptZip(String srcPath, String password, String dstPath, int type) {
            if (type == ZIP_APPEND) {
                return encryptZip(srcPath, password, dstPath);
            } else {
                deleteFile(dstPath);
                return encryptZip(srcPath, password, dstPath);
            }
        }
        private static boolean createDir(String dir) throws Exception{
            File dirFile = new File(dir);
            if(!dirFile.exists()){
                createDir(dirFile.getParent());
                dirFile.mkdir();
            }
            return true;
        }
        public static boolean encryptZip(String srcPath, String password, String dstPath) {
            try {
                if (!new File(srcPath).exists()) {
                    return false;
                }
                //创建目标文件目录
                File desFile = new File(dstPath);
                createDir(desFile.getParent());
    
                ZipParameters parameters = new ZipParameters();
                parameters.setEncryptFiles(true);
                parameters.setEncryptionMethod(Zip4jConstants.ENC_METHOD_AES);
                parameters.setAesKeyStrength(Zip4jConstants.AES_STRENGTH_256);
                parameters.setPassword(password.toCharArray());
                parameters.setCompressionLevel(Zip4jConstants.DEFLATE_LEVEL_NORMAL);//压缩级别7
                File srcFile = new File(srcPath);
                ZipFile destFile = new ZipFile(dstPath);
                if (srcFile.isDirectory()) {
                    destFile.addFolder(srcFile, parameters);
                } else {
                    destFile.addFile(srcFile, parameters);
                }
                return true;
            } catch (Exception e) {
                logger.error("压缩文件异常，原路径：{}。目标路径：{}。", srcPath, dstPath, e);
                return false;
            }
        }
    
        /**
         * 根据需求,直接调用静态方法start来执行操作
         * 参数:
         * rows 为多少行一个文件 int 类型
         * sourceFilePath 为源文件路径 String 类型
         * targetDirectoryPath 为文件分割后存放的目标目录 String 类型
         * ---分割后的文件名为索引号(从0开始)加'_'加源文件名,例如源文件名为test.txt,则分割后文件名为0_test.txt,以此类推
         */
        public static Integer split(String sourceFilePath, int rows) throws Exception {
            int idx = sourceFilePath.lastIndexOf(".");
            String targetDirectoryPath = sourceFilePath.substring(0, idx);
            return FileUtil.split(sourceFilePath, rows, targetDirectoryPath);
        }
    
        public static Integer split(String sourceFilePath, int rows, String targetDirectoryPath) throws Exception {
            File sourceFile = new File(sourceFilePath);
            File targetFile = new File(targetDirectoryPath);
    
            String bom = new String(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
    
            if (!sourceFile.exists() || rows <= 0 || sourceFile.isDirectory()) {
                throw new Exception("源文件不存在或者输入了错误的行数");
            }
            if (targetFile.exists()) {
                if (!targetFile.isDirectory()) {
                    throw new Exception("目标文件夹错误,不是一个文件夹");
                }
            } else {
                targetFile.mkdirs();
            }
    
            String fileName = sourceFile.getName();
            String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);//获取文件后缀
    
            InputStreamReader inStream = new InputStreamReader(new FileInputStream(sourceFile), "UTF8");
            BufferedReader br = new BufferedReader(inStream);
            BufferedWriter bw = null;
            StringBuffer sb = new StringBuffer("");
            String tableHead = "";
            if("csv".equals(suffix)){
                tableHead = br.readLine();//默认第一行就是表头，暂存以便给第二张之后的表用
                if(StringUtils.isEmpty(tableHead)){
                    throw new Exception("源文件内容为空,无法切割");
                }
                tableHead += "\r\n";
            }
            String tempData = br.readLine();
            int i = 1, s = 0;
            while (tempData != null) {
                sb.append(tempData + "\r\n");
                if (i % rows == 0) {
                    bw = new BufferedWriter(new FileWriter(new File(targetFile.getAbsolutePath() + "/" + s + "_" + sourceFile.getName())));
                    if ("csv".equals(suffix)) {
                        //csv文件头部加上bom，避免中文乱码问题(第一个文件已经存在bom，因此不需要再加)
                        bw.write(bom + tableHead+sb.toString());
                    } else {
                        bw.write(sb.toString());
                    }
                    bw.close();
                    sb.setLength(0);
                    s += 1;
                }
                i++;
                tempData = br.readLine();
            }
            //读完最后一行且最后一行不在文件尾部，把未写入文件的sb写入最后一个文件
            if ((i - 1) % rows != 0||i==1) {
                bw = new BufferedWriter(new FileWriter(new File(targetFile.getAbsolutePath() + "/" + s + "_" + sourceFile.getName())));
                if ("csv".equals(suffix)) {
                    //csv文件头部加上bom，避免中文乱码问题
                    bw.write(bom + tableHead+sb.toString());
                } else {
                    bw.write(sb.toString());
                }
                bw.close();
                br.close();
                s += 1;
            }
            logger.info("文件分割结束,共分割成了{}个文件",s);
            return s;
        }
    
    
    
    }

入参实体:

public class CfgSqlInput {
    
        private static final long serialVersionUID = 8831167730162732878L;
    
        /**
         * 需要执行的sql
         */
        private String sql;
    
        /**
         * 文件路径
         */
        private String filePath;
    
        /**
         * 文件名字
         */
        private String fileName;
    
        /**
         * 数据中心
         */
        private String dataSource;
    
        /**
         * 是否需要表头
         */
        private String header;
    
        /**
         * 分割线
         */
        private String lineSeparator;
    
        /**
         * 行尾分割线
         */
        private String lineEnd;
    
        /**
         * 文件类型
         */
        private String suffix;
    
        /**
         * excel
         * @return
         */
        private String excelColumnInfo;
    
    
        public String getSql() {
            return sql;
        }
    
        public void setSql(String sql) {
            this.sql = sql;
        }
    
        public String getFilePath() {
            return filePath;
        }
    
        public void setFilePath(String filePath) {
            this.filePath = filePath;
        }
    
        public String getFileName() {
            return fileName;
        }
    
        public void setFileName(String fileName) {
            this.fileName = fileName;
        }
    
        public String getDataSource() {
            return dataSource;
        }
    
        public void setDataSource(String dataSource) {
            this.dataSource = dataSource;
        }
    
        public String getHeader() {
            return header;
        }
    
        public void setHeader(String header) {
            this.header = header;
        }
    
        public String getLineSeparator() {
            return lineSeparator;
        }
    
        public void setLineSeparator(String lineSeparator) {
            this.lineSeparator = lineSeparator;
        }
    
        public String getLineEnd() {
            return lineEnd;
        }
    
        public void setLineEnd(String lineEnd) {
            this.lineEnd = lineEnd;
        }
    
        public String getSuffix() {
            return suffix;
        }
    
        public void setSuffix(String suffix) {
            this.suffix = suffix;
        }
    
        public String getExcelColumnInfo() {
            return excelColumnInfo;
        }
    
        public void setExcelColumnInfo(String excelColumnInfo) {
            this.excelColumnInfo = excelColumnInfo;
        }
    }

jdbc操作我用了spring的jdbctemplate，也可以直接用java。

@Service
    public class ReportRunSqlServiceImpl implements ReportRunSqlService {
    
        private static final Logger logger = LogManager.getLogger(ReportRunSqlServiceImpl.class);
    
        /**
         * excel表最大行数对应一个workbook
         */
        private static final int DEFAULT_MAX_ROW = 30000;
        
    
        @Override
        public CfgSqlOutput runSqlToXlsx(CfgSqlInput input) {
            logger.info("runSql 开始执行报表生成xlsx input：{}", input);
    
            JdbcTemplate jdbcTemplate = null;
            CfgSqlOutput output = new CfgSqlOutput();
            output.setFlag(true);
    
            try {
                jdbcTemplate = (JdbcTemplate) SpringContextHolder.getBean(input.getDataSource());
                //先删除旧文件
                FileUtil.delFile(input.getFilePath(), input.getFileName());
    
                List<ExcelColumnInfo> excelColumnInfos = generateExcelColumnInfoList(input.getExcelColumnInfo());
                if (CollectionUtils.isEmpty(excelColumnInfos)) {
                    output.setFlag(false);
                    logger.error("执行excel报表sql的时候发生了异常, 未正确生成excel表头配置信息:{}", input.getExcelColumnInfo());
                    return output;
                }
                List<ExcelExportEntity> entitys = generateExcelExportEntity(excelColumnInfos);
                if (CollectionUtils.isEmpty(entitys)) {
                    output.setFlag(false);
                    logger.error("执行excel报表sql的时候发生了异常, 未正确生成excel表头配置信息:{}", input.getExcelColumnInfo());
                    return output;
                }
                List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); //rs遍历数据集合
                List<Integer> rsultNumList = new ArrayList<Integer>();
                jdbcTemplate.query(input.getSql(), new RowCallbackHandler() {
                    @Override
                    public void processRow(ResultSet resultSet) throws SQLException {
                        Workbook workbook = null;
                        ResultSetMetaData resultMetaData = resultSet.getMetaData();
                        int cols = resultMetaData.getColumnCount();//获取列数量
                        int workBookNum = 0;
                        int resultNum = 0; //结果集count
                        boolean firstRow = false;
                        if (resultSet.isFirst()) {
                            firstRow = true;
                        }
                        while (firstRow || resultSet.next()) {
                            resultNum++;
                            firstRow = false;
                            //记录结果行数
                            rsultNumList.clear();
                            rsultNumList.add(resultNum);
                            Map<String, Object> map = new HashMap<String, Object>();
                            for (int i = 1; i <= cols; i++) { //数据放入map
                                map.put(excelColumnInfos.get(i - 1).getDbFieldEnName(), resultSet.getObject(i));
                            }
                            list.add(map);
                            if (list.size() == DEFAULT_MAX_ROW) {//每30w条创建一个工作簿
                                workBookNum++;
                                workbook = ExcelExportUtil.exportBigExcel(new ExportParams(null, null), entitys, list);//创建工作簿
                                list.clear();
                            }
                        }
                        if (workBookNum * DEFAULT_MAX_ROW < resultNum) {
                            workbook = ExcelExportUtil.exportBigExcel(new ExportParams(null, null), entitys, list);//创建工作簿
                        }
                        uploadXlsx(workbook, input);
                    }
                });
                if (rsultNumList.size() < 1) {
                    logger.info("执行excel报表sql查询数据为空，sql is:{} ", input.getSql());
                    //生成空文件
                    Workbook workbook = ExcelExportUtil.exportBigExcel(new ExportParams(null, null), entitys, list);//创建工作簿
                    uploadXlsx(workbook, input);
                } else {
                    logger.info("执行excel报表sql查询导出数据行数为:{}， sql is:{}", rsultNumList.get(0), input.getSql());
                }
            } catch (DataAccessException e) {
                output.setFlag(false);
                logger.error("执行报表sql的时候发生了异常, {}", e);
            }
            return output;
        }
    
        /**
         * 生成excel操作
         *
         * @param workbook
         */
        private void uploadXlsx(Workbook workbook, CfgSqlInput input) {
            ExcelExportUtil.closeExportBigExcel();
            try {
                if (!Files.exists(Paths.get(input.getFilePath()))) {
                    Files.createDirectory(Paths.get(input.getFilePath()));
                }
                FileOutputStream fos = new FileOutputStream(input.getFilePath() + File.separator + input.getFileName());
                workbook.write(fos);
                fos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    
        /**
         * excel表头配置信息生成和数据库sql字段对应的有序list
         */
        private List<ExcelColumnInfo> generateExcelColumnInfoList(String excelColumnInfoJsonStr) {
            List<ExcelColumnInfo> list = null;
            if (StringUtils.isEmpty(excelColumnInfoJsonStr)) {
                return null;
            }
            list = JSONObject.parseArray(excelColumnInfoJsonStr, ExcelColumnInfo.class);
            //根据columnIndex排序
            list = list.stream().sorted((a, b) -> a.getColumnIndex() - b.getColumnIndex()).collect(Collectors.toList());
            return list;
        }
    
        /**
         * 生成excel表头和各列数据类型信息
         *
         * @return
         */
        private List<ExcelExportEntity> generateExcelExportEntity(List<ExcelColumnInfo> excelColumnInfoList) {
            if (CollectionUtils.isEmpty(excelColumnInfoList)) {
                return null;
            }
            List<ExcelExportEntity> entitys = new ArrayList<ExcelExportEntity>();
            for (ExcelColumnInfo excelColumnInfo : excelColumnInfoList) {
                ExcelExportEntity entity = new ExcelExportEntity(excelColumnInfo.getExcelColumnName(), excelColumnInfo.getDbFieldEnName()); //new ExcelExportEntity("入学时间", "date");
                if (StringUtils.equalsIgnoreCase("Date", excelColumnInfo.getType())) {//如果是date类型，设置format
                    if (StringUtils.isEmpty(excelColumnInfo.getFormat())) {
                        entity.setFormat("yyyy-MM-dd");//设置默认format
                    } else {
                        entity.setFormat(excelColumnInfo.getFormat());
                    }
                } else if (StringUtils.equalsIgnoreCase("Double", excelColumnInfo.getType())) {
                    if (StringUtils.isEmpty(excelColumnInfo.getFormat())) {
                        entity.setNumFormat("###,###,###.##");//设置默认double类型format
                    } else {
                        entity.setNumFormat(excelColumnInfo.getFormat());
                    }
                }
                //end,其他数据类型无需设置默认数据类型
                entitys.add(entity);
            }
            return entitys;
        }
        
    
    }

调用runSqlToXlsx方法即可。

测试代码如下，自己封装CfgSqlInput参数即可

@Test
        public void test() {
            ReportCfg reportCfg = reportConfigService.getByReportId("0001");
            CfgSqlInput input = new CfgSqlInput();
            input.setFileName("1.xlsx");
            input.setFilePath("D:/");
            input.setSql(reportCfg.getReportSql());
            input.setDataSource(reportCfg.getDataSource());//这个是数据源，用户自己可以定义jdbctemplate
            input.setExcelColumnInfo(reportCfg.getExcelColumnInfo());
            //input.setHeader(reportCfg.getHeader());
            //input.setLineEnd(reportCfg.getLineEnd());
            //input.setLineSeparator(reportCfg.getLineSeparator());
            //input.setSuffix(reportCfg.getSuffix());
    
            reportRunSqlService.runSqlToXlsx(input);
        }

[https_blog.csdn.net_u012572955_article_details_88534950]: https://blog.csdn.net/u012572955/article/details/88534950