关于java:使用开源计算引擎提升Excel格式文件处理效率

对Excel进行解析\生成\查问\计算等解决是Java下较常见的工作,但Excel的文件格式很简单,自行编码读写太艰难,有了POI\EasyExcel\JExcel等类库就不便多了,其中POI最为杰出。

POI具备全面而粗疏的xls读写能力

POI可读写多种Excel文件格式,既反对古老的二进制格局(xls),也反对古代的OOXML格局(xlsx),既反对全内存一次性读写,也反对小内存流式读写。POI为大量Excel元素设计了相应的JAVA类,包含workbook、printer、sheet、row、cell,其中,与cell相干的类包含单元格款式、字体、色彩、日期、对齐、边框等。仅单元格款式类,办法就超过了四十个,可进行最全面最粗疏的读写操作。

POI的读写性能很底层

POI的读写性能全面而粗疏,但粗疏也意味着过于底层,开发者必须从头写起,本人解决每一处细节,即便简略的操作也要编写大量代码。比方,读入首行为列名的行式xls:

FileInputStream fileInputStream = new FileInputStream("d:\\Orders.xls");
// get the excel book
Workbook workbook = new HSSFWorkbook(fileInputStream);
if (workbook != null) {
    // get the first sheet
    Sheet sheet = workbook.getSheetAt(0);
    if (sheet != null) {
        //get the col name/first line
        Row rowTitle = sheet.getRow(0); // first line
        if (rowTitle != null) {
            int cellTitles = rowTitle.getPhysicalNumberOfCells(); // get column number
            for (int i = 0; i < cellTitles; i++) {
                Cell cell = rowTitle.getCell(i); //the cell!
                if (cell != null) {
                    System.out.print(cell.getStringCellValue() + " | ");
                }
            }
        }
        //get the value/other lines
        int rows = sheet.getPhysicalNumberOfRows(); // get line number
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i); // get row i
            if (row != null) {
                int cells = row.getPhysicalNumberOfCells(); // get column number
                for (int j = 0; j < cells; j++) {
                    // line number and row number
                    System.out.print("[" + i + "-" + j + "]");
                    Cell cell = row.getCell(j); // the cell!
                    if (cell != null) {
                        int cellType = cell.getCellType();
                        Object value = "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING: // string
                                value = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BLANK: // 空
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN: // boolean
                                value = cell.getBooleanCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC: // number
                                if (HSSFDateUtil.isCellDateFormatted(cell)) { // date number
                                    Date date = cell.getDateCellValue();
                                    value = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
                                }else { // normal number
                                    // change to string to avoid being too long
                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    value = cell;
                                }
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                throw new RuntimeException("data type mistaken");
                        }

                        System.out.println(value);
                    }
                }
            }
            System.out.println("end of the "+i+" line");
        }
        System.out.println("end of the value lines=======================================");
    }
}

行式xls是最常见的格局,但POI并没有为此提供方便的解决办法,只能依照workbook->sheet->line->cell的程序进行循环解析,造成了如此繁琐的代码。

这还只是将数据简略读出来,如果下一步想再解决数据,还要当时转为结构化数据对象,比方ArrayList<实体类>或HashMap,代码就更繁琐了。

POI查问计算艰难

解析Excel并不是指标,咱们通常还要对这些文件进查问计算,但POI作为Excel的解析类,没有也不适合再提供相干的办法,只能用JAVA手工硬写。比方根底的分组汇总运算,JAVA代码大略这样:

Comparator<salesRecord> comparator = new Comparator<salesRecord>() {
    public int compare(salesRecord s1, salesRecord s2) {
        if (!s1.salesman.equals(s2.salesman)) {
            return s1.salesman.compareTo(s2.salesman);
        } else {
            return s1.ID.compareTo(s2.ID);
        }
    }
};
Collections.sort(sales, comparator);
ArrayList<resultRecord> result=new ArrayList<resultRecord>();
salesRecord standard=sales.get(0);
float sumValue=standard.value;
for(int i = 1;i < sales.size(); i ++){
    salesRecord rd=sales.get(i);
    if(rd.salesman.equals(standard.salesman)){
        sumValue=sumValue+rd.value;
    }else{
        result.add(new resultRecord(standard.salesman,sumValue));
        standard=rd;
        sumValue=standard.value;
    }
}
result.add(new resultRecord(standard.salesman,sumValue));

Java编码实现计算不仅繁琐,而且存在架构性缺点。代码很难复用,数据结构和计算代码通常会耦合在一起,如果数据结构发生变化,代码就要重写。查问计算的要求灵便多变,而Java作为编译型语言,每次批改代码都要重启利用,保护工作量大,零碎稳定性差。

POI成熟稳固,但读写能力过于底层,且未提供查问计算能力,间接基于POI实现Excel文件的解决(特地是查问计算)的开发效率很低。如果针对POI进行封装,造成简略易用的高级读写函数,并额定提供查问计算能力,就能大幅度提高开发效率了。

esProc SPL就是其中的佼佼者。

SPL内置高级读写函数

SPL是JVM下开源的计算引擎,它对POI也进行了封装,内置简略易用的高级函数,可解析\生成各类格局规定或不规则的xls,并主动生成结构化数据对象。

解析格局规定的行式Excel,SPL提供了T函数。比方解析后面的xls文件,用封装前的POI要几十行,封装后只有一句:

=T(“d:\Orders.xls”)

解析行式Excel是很常见的工作,SPL用T函数封装了POI的性能,接口简略易用。无论xls还是xlsx,T函数都能够对立解析。可主动进行类型转换,开发者毋庸在细节浪费时间。T函数可主动辨别首行的列名和其余行的数据,并依据列名创立序表(SPL的结构化数据对象)并填入数据:

读入并解析成序表后,就能够应用SPL提供的丰盛的结构化数据处理办法了:

取第3条记录:A1(3)

取后3条记录:A1.m([-1,-2,-3])

取记录的字段值:A1(3).Amount*0.05

批改记录的字段值:A1(3).Amount = A1(3). Amount*1.05

取一列,返回汇合:A1.(Amount)

取几列,返回汇合的汇合:A1.([CLIENT,AMOUNT])

追加记录:A1.insert(200,”APPL”,10,2400.4,date(“2010-10-10”))

先按字段取再按记录序号取:A1.(AMOUNT)(2);等价于先按记录序号取再按字段取:A1(2).AMOUNT

解析格局较不规则的行式xls,SPL提供了xlsimport函数,内置丰盛而简洁的读取性能:

没有列名,首行间接是数据:file(“D:\Orders.xlsx”).xlsimport()

跳过前2行的题目区:file(“D:/Orders.xlsx”)[email protected](;,3)

从第3行读到第10行:file(“D:/Orders.xlsx”)[email protected](;,3:10)

只读取其中3个列:file(“D:/Orders.xlsx”)[email protected](OrderID,Amount,OrderDate)

读取名为”sales”的特定sheet:file(“D:/Orders.xlsx”)[email protected](;”sales”)

函数xlsimport还具备读取倒数N行、明码关上文件、读大文件等性能,这里不再详述。

解析格局很不规则的xls,SPL提供了xlscell函数,能够读写指定sheet里指定片区的数据,比方读取第1个sheet里的A2格:

=file(“d:/Orders.xlsx”).xlsopen().xlscell(“C2”)

配合SPL灵便的语法,就能够解析自在格局的xls,比方将上面的文件读为标准的二维表(序表):

这个文件格式很不规则,间接基于POI写Java代码是个盛大的工程,而SPL代码就简短得多:

生成规定的行式xls,SPL提供了xlsexport函数,用法也很简略。比方,下面例子的解析后果是个序表,存在SPL的A1格中,上面将A1写入新xls的第一个sheet,首行为列名,只有一句代码:=file(“e:/result.xlsx”)[email protected](A1)

xlsexport函数的功能丰富多样,能够将序表写入指定sheet,或只写入序表的局部行,或只写入指定的列:=file(“e:/scores.xlsx”)[email protected](A1,No,Name,Class,Maths)

xlsexport函数还能够不便地追加数据,比方对于曾经存在且有数据的xls,将序表A1追加到该文件开端,外观格调与原文件末行保持一致:=file(“e:/scores.xlsx”)[email protected](A1)

不规则片区写入数据,能够应用后面的xlscell函数。比方,xls中蓝色单元格是不规则的表头,须要在相应的红色单元格中填入数据,如下图:

间接用POI要大段简短的代码,而SPL代码就简短许多:

留神,第6、9、11行有间断单元格,SPL能够简化代码一起填入,POI只能顺次填入。

SPL提供足够的查问计算能力

查问计算是Excel解决工作的重点,SPL提供了丰盛的计算函数、字符串函数、日期函数,以及规范SQL语法,不仅反对日常的xls计算,也能计算内容不规则的xls和逻辑简单的xls。

SPL提供了丰盛的计算函数,可间接实现根底计算。比方后面的分组汇总,只有一句:

A1.groups(SellerId;sum(Amount))

更多计算:

条件查问:A1.select(Amount>1000 && Amount<=3000 && like(Client,”S“))

排序:A1.sort(Client,-Amount)”

去重:A1.id(Client)”

关联两个xlsx:join(T(“D:/Orders.xlsx”):O,SellerId; T(“D:/Employees.xls”):E,EId).new(O.OrderID,O.Client,O.SellerId,O.Amount,O.OrderDate, E.Name,E.Gender,E.Dept)”

TopN:T(“D:/Orders.xls”).top(-3;Amount)

组内TopN (开窗函数):T(“D:/Orders.xls”).groups(Client;top(3,Amount))

SPL反对大量日期函数和字符串函数,代码量更短,开发效率更高。比方:

工夫类函数,日期增减:elapse(“2020-02-27”,5) //返回2020-03-03

星期几:[email protected](“2020-02-27”) //返回5,即星期4

N个工作日之后的日期:workday(date(“2022-01-01”),25) //返回2022-02-04

字符串类函数,判断是否全为数字:isdigit(“12345”) //返回true

取子串后面的字符串:[email protected](“abCDcdef”,”cd”) //返回abCD

按竖线拆成字符串数组:”aa|bb|cc”.split(“|”) //返回[“aa”,”bb”,”cc”]

SPL还反对年份增减、求年中第几天、求季度、按正则表达式拆分字符串、拆出SQL的where或select局部、拆出单词、按标记拆HTML等性能。

SPL提供了规范SQL语法,能够像对数据库表一样间接对xls文件进行查问,极大地升高了数据库程序员的学习门槛:

filter:$select * from d:/sOrder.xlsx where Client like ‘%S%’ or (Amount&gt;1000 and Amount&lt;=2000)sort:$select * from sales.xls order by Client,Amont descdistinct:$ select distinct(sellerid) from sales.xls group by…having:$select year(orderdate) y,sum(amount) s from sales.xls group by year(orderdate) having sum(amount)>=2000000join:$select e.name, s.orderdate, s.amount from sales.xls s left join employee.xlsx e on s.sellerid= e.eid

SPL反对SQL-92规范中大部分语法,包含汇合计算、case when、with、嵌套子查问等,详见《没有 RDB 也敢揽 SQL 活的开源金刚钻 SPL》

内容不规则的xls,个别的类库都无能为力,SPL语法灵便函数丰盛,可轻松解决解决。比方Excel单元格里有很多”key=value”模式的字符串,须要整顿成标准的二维表,以进行后续计算:

逻辑简单的计算,SQL和存储过程都难以实现,SPL的计算能力更强,可轻松解决此类问题。比方,计算某支股票最长的间断上涨天数:

SPL反对更优的利用架构

SPL是解释型语言,提供JDBC接口,能够用SQL或存储过程的模式被JAVA集成,不仅升高了架构的耦合性,还能反对热切换。SPL还反对多种数据源,并反对跨数据源计算。

SPL提供了JDBC接口,可被JAVA轻松调用。简略的SPL代码能够像SQL一样,间接嵌入JAVA,比方条件查问:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="=T(\"D:/Orders.xls\").select(Amount>1000 && Amount<=3000 && like(Client,\"*S*\"))";
ResultSet result = statement.executeQuery(str);

SPL反对计算外置,可升高计算代码和前端利用的耦合性。简单的SPL代码能够先存为脚本文件,再以存储过程的模式被JAVA调用:

Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call scriptFileName(?, ?)}");
statement.setObject(1, "2020-01-01");
statement.setObject(2, "2020-01-31");
statement.execute();

SPL是解释型语言,通过外置代码可实现热切换。解释型语言毋庸编译,批改后可立刻执行,毋庸重启JAVA利用,可升高保护工作量,进步零碎稳定性。

SPL反对多种文件数据源,除了xls外,SPL还能读写csv\txt\XML\Json等文件,比方对txt进行条件查问:

T(“sOrders.txt”).groups(SellerId;sum(Amount))

$select * from d:/sOrders.txt where Client like ‘%S%’ or (Amount>1000 and Amount<=2000)

SPL反对跨数据源计算,比方xls和txt的关联计算:

=join(T(“D:/Orders.xlsx”):O,SellerId; T(“D:/Employees.txt”):E,EId).new(O.OrderID,O.Client,O.SellerId,O.Amount,O.OrderDate, E.Name,E.Gender,E.Dept)”

SPL还能拜访各类关系型数据库,WebService、Restful等网络服务, Hadoop、redis、Kafka、Cassandra等NoSQL。

POI只适宜简略的xls解析\生成工作,且未提供查问计算能力。SPL对POI进行了封装,内置高级读写函数,不仅能够大幅简化代码,还能进行较不规则甚至很不规则的xls解析\生成工作。SPL额定提供了弱小的计算能力,不仅反对日常的Excel查问计算,还可计算内容不规则的xls和逻辑简单的xls。SPL反对更优的利用架构,可实现代码低耦合和热切换,反对多种数据源和跨数据源计算。

## SPL材料

  • SPL官网
  • SPL下载
  • SPL源代码

欢送对SPL有趣味的加小助手(VX号:SPL-helper),进SPL技术交换群
欢送关注我的布告号:字母哥杂谈,回复003赠送作者专栏《docker修炼之道》的PDF版本,30余篇精品docker文章。字母哥博客:zimug.com

【腾讯云】轻量 2核2G4M,首年65元

阿里云限时活动-云数据库 RDS MySQL  1核2G配置 1.88/月 速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据