开源SPL助力Java处理开放格式文件（txt/csv/json/xml/xls）

在Java应用程序中处理txt、csv、json、xml和xls等开放格式的数据文件是很常见的。在Java中硬编码处理非常复杂，因此我们经常会转向使用现成的开源包。但每个包都有其缺点。

解析库。这类类库可以通过比硬编码数据检索更简单的编码过程，将外部文件作为Java内部对象读取。常见的工具有：用于解析txt和csv文件的OpenCSV；用于解析json文件的SJ.json、Gson和JsonPath；用于解析XML文件的XOM、Xerces-J、Jdom、Dom4J；以及用于解析XLS的POI。JsonPath提供了JsonPath语法，Dom4J提供了XPath语法来处理简单的过滤计算。但这些库通常计算能力较弱，需要借助硬编码或其他类库来完成计算任务。

Spark。Spark作为Scala的一个类库，支持结构化数据文件，并且计算能力相对较强。该库的缺点是缺乏解析能力，需要第三方类库的辅助，例如用于解析xml的spark-xml，以及用于解析xls的spark-excel。这使得计算不如使用原生类库稳定。Scala编程语言也有不足之处。比Java更陡峭的学习曲线意味着高昂的学习成本，而过于频繁的新版本发布给实际应用带来了不便。

嵌入式数据库。将文件解析并写入SQLite、HSQLDB和Derby等嵌入式数据库，可以利用SQL强大的计算能力。然而，嵌入式数据库框架复杂，数据加载过程非常麻烦，会导致严重的延迟。SQL强大的计算能力并非总是如此，因为它只擅长计算二维数据，而不擅长处理json/xml这样的层次化数据。

一些类库，包括simoc csvjdbc、xiao321、Csvjdbc和xlsjdbc等文件JDBC驱动，以及Tablesaw和Joinery等DataFrame库，能够计算结构化数据文件，但它们不成熟，计算能力较弱，实用价值不高。

esProc SPL是更好的选择。

SPL是一种基于JVM的开源编程语言。它提供简单的解析方法来读取规则或不规则的txt、csv、json、xml和xls文件，为统一表达二维数据和层次化数据提供专门的数据对象，并提供丰富的函数来满足各种业务计算需求。

txt\csv

SPL拥有各种内置的解析函数，可以使用简单的代码解析各种文本文件，并提供丰富的函数来以一致的方式计算已解析的文本文件。

规则格式的文本文件。二维文本文件，如同数据库表，第一行包含列名，一行对应一个记录，并使用固定分隔符分隔列。最常见的格式是逗号分隔的CSV和制表符分隔的txt。SPL提供T函数，只需一行代码即可解析任何文本文件。

s=T("D:\\data\\Orders.csv") 

不规则格式的文本文件。SPL使用import函数，并带有丰富的选项来解析它们。例如，解析以双横线分隔的文本文件。

s=file("D:/Orders.txt").import@t(;,"--")

不规则格式的文本文件。SPL使用import函数，并带有丰富的选项来解析它们。例如，解析以双横线分隔的文本文件。

丰富的函数。对于已解析的文本文件，SPL可以轻松完成类SQL的计算。

• 过滤：s.select(Amount>1000 && Amount<=3000 && like(Client,"*s*"))
• 排序：s.sort(Client,-Amount)
• 去重：s.id(Client)
• 分组与聚合：s.groups(year(OrderDate);sum(Amount))
• 连接：join(T ("D:/data/Orders.csv"):O,SellerId; T("D:/data/Employees.txt"):E,EId)
• 获取TopN：s.top(-3;Amount)
• 获取每组的TopN：s.groups(Client;top(3,Amount))

更不规则格式的文本文件。通常，这种文本文件无法直接解析为结构化数据。SPL提供灵活的函数语法，通过简单的处理即可获得所需数据。例如，在一个文本文件中，每三行构成一个记录，每条记录的第二行包含多个字段。我们试图重新排列文件，将其转换为按第3和第4个字段排序的结构化文件。

	A
1	=file("D:\\data.txt").import@si()
2	=A1.group((#-1)\3)
3	=A2.new(~(1):OrderID, (line=~(2).array("\t"))(1):Client,line(2):SellerId,line(3):Amount,~(3):OrderDate )
4	=A3.sort(_3,_4)

SPL还支持SQL92标准的SQL语法，包括集合计算、case when、with和嵌套查询。例如，进行分组与聚合。

$select year(OrderDate),sum(Amount) from D:/data/Orders.txt group by year(OrderDate)

json\xml

SPL方便地处理JSON和XML等层次化数据，可以自由访问任何层次结构，并以一致的方式计算数据。

专门的层次化结构化数据对象。SPL方便地表达json\xml数据的层次结构。例如，读取文件中的层次化json字符串并进行解析。

	A
1	=file("d:\\xml\\emp_orders.json").read()
2	=json(A1)

以下截图显示了层次结构。

读取和解析XML字符串也类似。

	A
1	=file("d:\\xml\\emp_orders.xml").read()
2	=xml(A1,"xml/row")

访问层次化数据。SPL通过点号访问特定层级的数据，通过下标访问特定位置的数据。

• 获取一组Client字段值：A2.(Client)
• 获取第10条记录的Orders字段（二维表值）：A2(10).Orders
• 获取第10条记录的Orders字段中的第5条记录：(A2(10).Orders)(5)

计算层次化数据。SPL以统一的代码计算二维数据和层次化数据。

	A
3	=A2.conj(Orders).groups(year(OrderDate);sum(Amount))
4	=A2(10).Orders.select(Amount>1000 && Amount<=3000 && like(Client,"*s*"))

处理从Web下载的层次化数据。除了本地层次化数据，SPL还可以处理从Web下载的层次化数据，如Web服务和RESTful。例如，从RESTful检索层次化json数据并执行条件查询。

	A
1	=httpfile("http://127.0.0.1:6868/restful/emp_orders").read()
2	=json(A1)
3	=A2.conj(Orders)
4	=A3.select(Amount>1000 && Amount<=2000 && like@c(Client,"*business*"))

许多特殊数据源，如MongoDB、ElasticSearch和SalesForce，也以层次化方式存储数据。SPL可以直接从它们检索数据进行进一步计算。

xls

SPL可以轻松地读写各种规则或不规则格式的xls文件，通过封装的POI进行强大的处理，并通过一致的代码用内置函数和语法进行计算。

SPL仍然使用T函数读取规则格式的逐行xls文件。

=T("d:\\Orders.xls") 

并以类似处理文本文件的方式进行后续计算。
SPL使用xlsexport函数生成规则格式的逐行XLS文件。例如，将数据表A1写入新xls文件的第一个工作表，并使第一行为列名，SPL仅需一行代码。

=file("e:/result.xlsx").xlsexport@t(A1)

xlsexport函数具有许多功能。它可以将数据表写入指定的工作表，将数据表的某些行写入其中，或将数据表的指定列写入其中。

=file("e:/scores.xlsx").xlsexport@t(A1,No,Name,Class,Maths)

xlsexport函数也可以方便地用于追加数据。假设有一个包含数据的xls文件，我们试图将数据表A1中的数据追加到文件末尾，使其外观与现有xls文件的最后一行相同。

=file("e:/scores.xlsx").xlsexport@a(A1)

SPL使用xlsimport函数从逐行xls文件的不规则格式读取数据。该函数功能丰富且简单。

导入没有列标题的xls文件，详细数据从第一行开始：file("D:\\Orders.xlsx").xlsimport()。

• 通过跳过前两行的标题导入xls文件：file("D:/Orders.xlsx").xlsimport@t(;,3)。
• 导入xls文件从第3行到第10行的数据：file("D:/Orders.xlsx").xlsimport@t(;,3:10)。
• 导入xls文件的3列：file("D:/Orders.xlsx").xlsimport@t(OrderID,Amount,OrderDate)。
• 导入名为“sales”的工作表：file("D:/Orders.xlsx").xlsimport@t(;"sales")。

xlsimport函数还具有读取最后N行、使用密码打开xls文件以及读取大型xls文件等其他功能。

格式极其不规则的xls文件。SPL使用xlscell函数读写给定工作表中指定范围的数据。例如，读取sheet1中的单元格A2。

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

SPL能够通过其敏捷的语法解析自由格式的xls文件。例如，将以下文件解析为标准的二维表（表序列）。

该文件的格式非常不规则。直接用POI编写Java代码将是一项繁重且耗时的工作，但SPL代码简短而简洁。

	A	B	C
1	=create(ID,Name,Sex,Position,Birthday,Phone,Address,PostCode)
2	=file("e:/excel/employe.xlsx").xlsopen()
3	[C,C,F,C,C,D,C,C]	[1,2,2,3,4,5,7,8]
4	for	=A3.(~/B3(#)).(A2.xlscell(~))
5		if len(B4(1))==0	break
6		>A1.record(B4)
7		>B3=B3.(~+9)

xlscell函数还可以用于向不规则格式的范围写入数据。例如，下图中蓝色的单元格包含不规则的表格标题，我们正试图在相应的空白单元格中填充数据。

POI代码将显得臃肿且冗长。如下所示的SPL代码则简短而简洁。

	A	B	C	D	E	F
1	蒙牛乳业基金	2017	3	58.2	364	300
2	8.5	50	200	100	400	200
3	182.6	76.3	43.7	28.5	16.4
4	120	1.07	30	0.27	90	0.8
5	154	6	4
6	=file("e:/result.xlsx")		=A6.xlsopen()
7	=C6.xlscell("B2",1;A1)		=C6.xlscell("J2",1;B1)		=C6.xlscell("L2",1;C1)
8	=C6.xlscell("B3",1;D1)		=C6.xlscell("G3",1;E1)		=C6.xlscell("K3",1;F1)
9	=C6.xlscell("B6",1;[A2:F2].concat("\t"))			=C6.xlscell("H6",1;[A3:E3].concat("\t"))
10	=C6.xlscell("B9",1;[A4:F4].concat("\t"))			=C6.xlscell("B11",1;[A5:C5].concat("\t"))
11	=A6.xlswrite(B6)

请注意，row6、row9和row11有连续的单元格，SPL将代码合并以一次性填充它们。而POI只能逐个单元格操作。

卓越的计算能力

SPL提供了大量的string函数和date函数，以及方便的语法，可以有效地简化代码，实现SQL和存储过程难以处理的复杂逻辑。

丰富的日期和字符串函数。除了执行常规计算的函数，如获取指定日期之前的或之后的date以及string截断，SPL还提供了更多的日期和string函数，在数量和功能上都超越了SQL。

获取指定季度之前或之后的日期。

elapse@q("2020-02-27",-3)      // Return 2019-05-27

获取N个工作日后的日期。

workday(date("2022-01-01"),25) // Return 2022-02-04

String函数。

检查一个string是否全部由字母组成。

isdigit("12345")               // Return true 

获取指定子字符串之前的string。

substr@l("abCDcdef","cd")      // Return abCD 

将string按竖线分割成子字符串数组：["aa","bb","cc"]。

"aa|bb|cc".split("|")          // Return 

SPL还提供了获取日期前或后多少年的函数、获取日期属于哪个季度、根据正则表达式分割string、获取SQL语句的where或select部分、从string中获取单词、按特定标记分割HTML等函数。

方便的函数语法。SPL支持函数选项。这允许具有相似功能的函数使用相同的名称，并通过不同的选项加以区分。select函数的基本功能是过滤数据。如果我们需要获取第一个符合条件的记录，我们使用@1选项。

T.select@1(Amount>1000) 

SPL使用@b选项通过二分查找算法对有序数据进行快速过滤。

T.select@b(Amount>1000) 

SPL使用@o选项对按分组字段排序的数据执行基于顺序的分组，将具有相同分组字段值的相邻记录放在一起。

T.groups@o(Client;sum(Amount))

函数选项可以协同工作。

Orders.select@1b(Amount>1000) 

通常，结构化计算函数中的参数很复杂。例如，SQL使用大量关键字将语句的参数分成多个组，导致语句结构不一致。SPL具有层次化参数。它使用分号、逗号和冒号来标识三个级别的参数，并以简单的方式编写复杂的参数。

join(Orders:o,SellerId ; Employees:e,EId) 

简单实现复杂的业务逻辑。SPL具有出色的计算能力。它轻松处理SQL/存储过程难以处理的基于顺序的计算、面向集合的计算、连接和分步计算。例如，计算股票连续上涨的最长天数，SPL有以下代码。

	A
1	// 文件解析
2	=a=0,A1.max(a=if(price>price[-1],a+1,0))

找出占总金额一半以上的大客户，并按金额降序排序。

	A	B
1	//文件解析
2	=A1.sort(amount:-1)	// 按金额降序排序
3	=A2.cumulate(amount)	// 获取累计金额序列
4	=A3.m(-1)/2	// 计算最终累计金额，即总金额
5	=A3.pselect(~>=A4)	// 找出累计金额超过一半总金额的位置
6	=A2(to(A5))	// 通过位置获取目标值

跨数据源计算。SPL支持多种数据源，不仅包括结构化数据文件，还包括Hadoop、Redis、Kafka和Cassandra等各种数据库和NoSQL，因此可以完成涉及不同类型源的计算，例如xls和txt之间的连接。

=join(T("D:/Orders.xlsx"):O,SellerId; T("D:/Employees.txt"):E,EId)

易于集成

SPL提供方便易用的JDBC驱动。简单的代码，如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提供方便易用的JDBC驱动。简单的代码，如SQL，可以直接嵌入Java程序中。

将计算代码与Java程序分开存储，减少耦合。对于复杂的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程序外部，可以实现热插拔。解释型语言实时执行，更改后无需重新编译，无需重启Java应用程序。这使得维护方便，并创建了更稳定的系统。

虽然有许多用于计算txt\csv\json\xml\xls文件的类库，但它们都有各自的缺陷。SPL作为一种基于JVM的开源编程语言，可以解析规则或不规则格式的结构化数据文件，以统一的方式表示二维和层次化数据，并使用一致的代码执行常见的类SQL计算。SPL拥有更丰富的string和date函数集，更方便的语法和更强大的计算能力。它提供易于集成的JDBC驱动，支持将算法放在应用程序内部或外部——这可以有效地降低系统耦合，并实现代码热插拔。

扩展阅读

• 哪种工具最适合多种数据源混合计算？
• 开源SPL，无需RDB即可执行SQL

历史

• 2022年6月21日：初始版本