使用Kettle导出excel

1.开发背景

在web项目中，经常会需要查询数据导出excel，以前比较常见的就是用poi。使用poi的时候也有两种方式，一种就是直接将集合一次性导出为excel，还有一种是分批次追加的方式适合数据量较大的情况。poi支持xls和xlsx，使用2003版本的只支持6万多行以下的数据量，使用2007版本的支持百万行。但是呢，当数据量大了之后这种方式却非常耗内存和时间。

接触了etl之后就想着用kettle来做导数据，经过测试是完全可行的。几十万行，一百万行都能快速导出来，代码也非常简单。

2.kettle相关maven依赖如下

 1 <dependency>

 2     <groupId>org.apache.commons</groupId>

 3     <artifactId>commons-vfs2</artifactId>

 4     <version>2.0</version>

 5 </dependency>

 6 <dependency>

 7     <groupId>org.scannotation</groupId>

 8     <artifactId>scannotation</artifactId>

 9     <version>1.0.3</version>

10 </dependency>

11 <dependency>

12     <groupId>dom4j</groupId>

13     <artifactId>dom4j</artifactId>

14     <version>1.6.1</version>

15 </dependency>

16 <dependency>

17     <groupId>pentaho-kettle</groupId>

18     <artifactId>kettle-vfs</artifactId>

19     <version>5.2.0.0</version>

20     <classifier>pentaho</classifier>

21 </dependency>

22 <dependency>

23     <groupId>pentaho-kettle</groupId>

24     <artifactId>kettle-engine</artifactId>

25     <version>5.2.0.0</version>

26 </dependency>

27 <dependency>

28     <groupId>pentaho-kettle</groupId>

29     <artifactId>kettle-core</artifactId>

30     <version>5.2.0.0</version>

31 </dependency>

仓库如果没有kettle的jar包，可以先现在下来再上传到maven仓库

3.ktr文件：如以下附件下载链接

由于博客园不支持ktr路径的文件上传，所以我将它保存为xml文件，使用时将xml后缀去掉用ktr后缀就可以了，该转换就是查询，导出为excel两个组件，如图所示：

这里用到一个输入和excel输出，里面配置的参数：

查询语句： ${exec_select_sql}、

文件名称：${filepath}、

sheet名称：${sheetname}

4.调用ktr

java调用kettle转换

5.测试导出方法

web项目中的测试

@RequestMapping("/kettle")

public Object kettle(int rows, String sql) {

String sqlLimit = sql + "LIMIT "+rows;

String fullName = "/home/admin/DataPlatform/temp"+ "/kettle"+uuid;

this.kettleExportExcel(sqlLimit, fullName, "kettle");

return null;

}

也可以用main函数或junit测试

6.打印执行信息,也可以直接在程序里面加

@Component

@Aspect

public class ControllerAspect {

    private static Logger logger_info = Logger.getLogger("api-info");

    private static Logger logger_error = Logger.getLogger("api-error");

    /**

     * 切面

     */

    private final String POINT_CUT = "execution(* com.demo.controller.*.*(..))";

    @Pointcut(POINT_CUT)

    private void pointcut() {

    }

    @AfterThrowing(value = POINT_CUT, throwing = "e")

    public void afterThrowing(Throwable e) {

        logger_error.error("afterThrowing: " + e.getMessage(), e);

    }

    /**

     * @功能描述: 打印Controller方法的执行时间

     * @创建日期: 2016年11月2日 上午11:44:11

     * @param proceedingJoinPoint

     * @return

     * @throws Throwable

     */

    @Around(value = POINT_CUT)

    public Object around(ProceedingJoinPoint proceedingJoinPoint)

            throws Throwable {

        String className = proceedingJoinPoint.getTarget().getClass().getName();

        String methodName = proceedingJoinPoint.getSignature().getName();

        Long begin = System.currentTimeMillis();

        Long beginMemory = Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory();

        StringBuilder log = new StringBuilder(className+"@"+methodName);

        Object result = null;

        try {

            result = proceedingJoinPoint.proceed();

        } catch (Exception e) {

            logger_error.error(log + e.getMessage(), e);

        }

        Long end = System.currentTimeMillis();

        Long endMemory = Runtime.getRuntime().totalMemory()-Runtime.getRuntime().freeMemory();

        log.append(" 执行时间: ").append(end - begin).append("ms");

        log.append(" 消耗内存: ").append(endMemory - beginMemory).append("Byte");

        logger_info.info(log);

        return result;

    }

}

7.执行结果

* 导出10w行记录

执行时间: 1133ms

执行时间: 1082ms

执行时间: 1096ms

* 导出100w行记录

执行时间: 39784ms

执行时间: 8566ms

执行时间: 8622ms
* Excel 2007行数极限 1048575 执行时间: 9686ms

第一次导数据要加载kettle组件运行稍慢，后面几次再导数据速度就飞快了，更多结果有兴趣的可以去试试。

巴特西

使用Kettle导出excel

最新文章

热门文章