Java处理Excel从POI到SPL。txt

　　目录POI具有全面而细致的xls读写能力SPL提供足够的查询计算能力SPL支持更优的应用架构
　　对Excel进行解析生成查询计算等处理是Java下较常见的任务，但Excel的文件格式很复杂，自行编码读写太困难，有了POIEasyExcelJExcel等类库就方便多了，其中POI最为出色。POI具有全面而细致的xls读写能力
　　POI可读写多种Excel文件格式，既支持古老的二进制格式（xls），也支持现代的OOXML格式（xlsx），既支持全内存一次性读写，也支持小内存流式读写。POI为大量Excel元素设计了相应的JAVA类，包括workbook、printer、sheet、row、cell，其中，与cell相关的类包括单元格样式、字体、颜色、日期、对齐、边框等。仅单元格样式类，方法就超过了四十个，可进行最全面最细致的读写操作。POI的读写功能很底层
　　POI的读写功能全面而细致，但细致也意味着过于底层，开发者必须从头写起，自己处理每一处细节，即使简单的操作也要编写大量代码。比如，读入首行为列名的行式xls：FileInputStreamfileInputStreamnewFileInputStream（d：Orders。xls）；gettheexcelbookWorkbookworkbooknewHSSFWorkbook（fileInputStream）；if（workbook！null）｛getthefirstsheetSheetsheetworkbook。getSheetAt（0）；if（sheet！null）｛getthecolnamefirstlineRowrowTitlesheet。getRow（0）；firstlineif（rowTitle！null）｛intcellTitlesrowTitle。getPhysicalNumberOfCells（）；getcolumnnumberfor（inti0；icellTi）｛CellcellrowTitle。getCell（i）；thecell！if（cell！null）｛System。out。print（cell。getStringCellValue（））；｝｝｝getthevalueotherlinesintrowssheet。getPhysicalNumberOfRows（）；getlinenumberfor（inti1；i）｛Rowrowsheet。getRow（i）；getrowiif（row！null）｛intcellsrow。getPhysicalNumberOfCells（）；getcolumnnumberfor（intj0；j）｛linenumberandrownumberSystem。out。print（〔ij〕）；Cellcellrow。getCell（j）；thecell！if（cell！null）｛intcellTypecell。getCellType（）；Oswitch（cellType）｛caseHSSFCell。CELLTYPESTRING：stringvaluecell。getStringCellValue（）；caseHSSFCell。CELLTYPEBLANK：空caseHSSFCell。CELLTYPEBOOLEAN：booleanvaluecell。getBooleanCellValue（）；caseHSSFCell。CELLTYPENUMERIC：numberif（HSSFDateUtil。isCellDateFormatted（cell））｛datenumberDatedatecell。getDateCellValue（）；valuenewDateTime（date）。toString（yyyyMMddHH：mm：ss）；｝else｛normalnumberchangetostringtoavoidbeingtoolongcell。setCellType（HSSFCell。CELLTYPESTRING）；｝caseHSSFCell。CELLTYPEERROR：thrownewRuntimeException（datatypemistaken）；｝System。out。println（value）；｝｝｝System。out。println（endoftheiline）；｝System。out。println（endofthevaluelines）；｝｝
　　行式xls是最常见的格式，但POI并没有为此提供方便的处理方法，只能按照workbooksheetlinecell的顺序进行循环解析，造成了如此繁琐的代码。
　　这还只是将数据简单读出来，如果下一步想再处理数据，还要事先转为结构化数据对象，比如ArrayList实体类或HashMap，代码就更繁琐了。POI查询计算困难
　　解析Excel并不是目标，我们通常还要对这些文件进查询计算，但POI作为Excel的解析类，没有也不合适再提供相关的方法，只能用JAVA手工硬写。比如基础的分组汇总运算，JAVA代码大概这样：ComparatorsalesRecordcomparatornewComparatorsalesRecord（）｛publicintcompare（salesRecords1，salesRecords2）｛if（！s1。salesman。equals（s2。salesman））｛returns1。salesman。compareTo（s2。salesman）；｝else｛returns1。ID。compareTo（s2。ID）；｝｝｝；Collections。sort（sales，comparator）；ArrayListresultRecordresultnewArrayListresultRecord（）；salesRecordstandardsales。get（0）；floatsumValuestandard。for（inti1；isales。size（）；i）｛salesRecordrdsales。get（i）；if（rd。salesman。equals（standard。salesman））｛sumValuesumValuerd。｝else｛result。add（newresultRecord（standard。salesman，sumValue））；sumValuestandard。｝｝result。add（newresultRecord（standard。salesman，sumValue））；
　　Java编码实现计算不仅繁琐，而且存在架构性缺陷。代码很难复用，数据结构和计算代码通常会耦合在一起，如果数据结构发生变化，代码就要重写。查询计算的要求灵活多变，而Java作为编译型语言，每次修改代码都要重启应用，维护工作量大，系统稳定性差。
　　POI成熟稳定，但读写能力过于底层，且未提供查询计算能力，直接基于POI完成Excel文件的处理（特别是查询计算）的开发效率很低。如果针对POI进行封装，形成简单易用的高级读写函数，并额外提供查询计算能力，就能大幅度提高开发效率了。
　　esProcSPL就是其中的佼佼者。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）。Amount0。05
　　修改记录的字段值：A1（3）。AmountA1（3）。Amount1。05
　　取一列，返回集合：A1。（Amount）
　　取几列，返回集合的集合：A1。（〔CLIENT，AMOUNT〕）
　　追加记录：A1。insert（200，APPL，10，2400。4，date（20101010））
　　先按字段取再按记录序号取：A1。（AMOUNT）（2）；等价于先按记录序号取再按字段取：A1（2）。AMOUNT
　　解析格式较不规则的行式xls，SPL提供了xlsimport函数，内置丰富而简洁的读取功能：
　　没有列名，首行直接是数据：file（D：Orders。xlsx）。xlsimport（）
　　跳过前2行的标题区：file（D：Orders。xlsx）。xlsimportt（；，3）
　　从第3行读到第10行：file（D：Orders。xlsx）。xlsimportt（；，3：10）
　　只读取其中3个列：file（D：Orders。xlsx）。xlsimportt（OrderID，Amount，OrderDate）
　　读取名为sales的特定sheet：file（D：Orders。xlsx）。xlsimportt（；sales）
　　函数xlsimport还具有读取倒数N行、密码打开文件、读大文件等功能，这里不再详述。
　　解析格式很不规则的xls，SPL提供了xlscell函数，可以读写指定sheet里指定片区的数据，比如读取第1个sheet里的A2格：
　　file（d：Orders。xlsx）。xlsopen（）。xlscell（C2）
　　配合SPL灵活的语法，就可以解析自由格式的xls，比如将下面的文件读为规范的二维表（序表）：
　　这个文件格式很不规则，直接基于POI写Java代码是个浩大的工程，而SPL代码就简短得多：
　　A
　　B
　　C
　　1hrcreate（ID，Name，Sex，Position，Birthday，Phone，Address，PostCode）
　　2hrfile（e：excelemploye。xlsx）。xlsopen（）
　　3hr〔C，C，F，C，C，D，C，C〕
　　〔1，2，2，3，4，5，7，8〕
　　4hrfor
　　A3。（B3（））。（A2。xlscell（））
　　5hriflen（B4（1））0
　　break
　　6hrA1。record（B4）
　　7hrB3B3。（9）
　　生成规则的行式xls，SPL提供了xlsexport函数，用法也很简单。比如，上面例子的解析结果是个序表，存在SPL的A1格中，下面将A1写入新xls的第一个sheet，首行为列名，只要一句代码：file（e：result。xlsx）。xlsexportt（A1）
　　xlsexport函数的功能丰富多样，可以将序表写入指定sheet，或只写入序表的部分行，或只写入指定的列：file（e：scores。xlsx）。xlsexportt（A1，No，Name，Class，Maths）
　　xlsexport函数还可以方便地追加数据，比如对于已经存在且有数据的xls，将序表A1追加到该文件末尾，外观风格与原文件末行保持一致：file（e：scores。xlsx）。xlsexporta（A1）
　　不规则片区写入数据，可以使用前面的xlscell函数。比如，xls中蓝色单元格是不规则的表头，需要在相应的白色单元格中填入数据，如下图：
　　直接用POI要大段冗长的代码，而SPL代码就简短许多：
　　A
　　B
　　C
　　D
　　E
　　F
　　1hrMengniuFunds
　　2017hr3hr58。2
　　364hr300hr2hr8。5
　　50hr200hr100hr400hr200hr3hr182。6
　　76。3
　　43。7
　　28。5
　　16。4
　　4hr120hr1。07
　　30hr0。27
　　90hr0。8
　　5hr154hr6hr4hr6hrfile（e：result。xlsx）
　　A6。xlsopen（）
　　7hrC6。xlscell（B2，1；A1）
　　C6。xlscell（J2，1；B1）
　　C6。xlscell（L2，1；C1）
　　8hrC6。xlscell（B3，1；D1）
　　C6。xlscell（G3，1；E1）
　　C6。xlscell（K3，1；F1）
　　9hrC6。xlscell（B6，1；〔A2：F2〕。concat（））
　　C6。xlscell（H6，1；〔A3：E3〕。concat（））
　　10hrC6。xlscell（B9，1；〔A4：F4〕。concat（））
　　C6。xlscell（B11，1；〔A5：C5〕。concat（））
　　11hrA6。xlswrite（B6）
　　注意，第6、9、11行有连续单元格，SPL可以简化代码一起填入，POI只能依次填入。SPL提供足够的查询计算能力
　　查询计算是Excel处理任务的重点，SPL提供了丰富的计算函数、字符串函数、日期函数，以及标准SQL语法，不仅支持日常的xls计算，也能计算内容不规则的xls和逻辑复杂的xls。
　　SPL提供了丰富的计算函数，可直接完成基础计算。比如前面的分组汇总，只要一句：
　　A1。groups（SellerId；sum（Amount））
　　更多计算：
　　条件查询：A1。select（Amount1000Amount3000like（Client，S））
　　排序：A1。sort（Client，Amount）
　　去重：A1。id（Client）
　　关联两个xlsx：join（T（D：Orders。xlsx），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（Ctop（3，Amount））
　　SPL支持大量日期函数和字符串函数，代码量更短，开发效率更高。比如：
　　时间类函数，日期增减：elapse（20200227，5）返回20200303
　　星期几：dayw（20200227）返回5，即星期4
　　N个工作日之后的日期：workday（date（20220101），25）返回20220204
　　字符串类函数，判断是否全为数字：isdigit（12345）返回true
　　取子串前面的字符串：substrl（abCDcdef，cd）返回abCD
　　按竖线拆成字符串数组：aabbcc。split（）返回〔aa，bb，cc〕
　　SPL还支持年份增减、求年中第几天、求季度、按正则表达式拆分字符串、拆出SQL的where或select部分、拆出单词、按标记拆HTML等功能。
　　SPL提供了标准SQL语法，可以像对数据库表一样直接对xls文件进行查询，极大地降低了数据库程序员的学习门槛：
　　filter：selectfromd：sOrder。xlsxwhereClientlikeselectfromd：sOrder。xlsxwhereClientlikeSor（Amount1000andAmount2000）sort：selectfromd：sOrder。xlsxwhereClientlikeselectfromsales。xlsorderbyClient，Amontdescdistinct：selectdistinct（sellerid）fromsales。xlsgroupbyhaving：KaTeXparseerror：ExpectedEOF，gotatposition98：ingsum（amount）2000000joinselecte。name，s。orderdate，s。amountfromsales。xlssleftjoinemployee。xlsxeons。selleride。eid
　　SPL支持SQL92标准中大部分语法，包括集合计算、casewhen、with、嵌套子查询等，详见《没有RDB也敢揽SQL活的开源金刚钻SPL》
　　内容不规则的xls，一般的类库都无能为力，SPL语法灵活函数丰富，可轻松解决处理。比如Excel单元格里有很多keyvalue形式的字符串，需要整理成规范的二维表，以进行后续计算：
　　A
　　1hrfile（D：datakeyvalue。xlsx）。xlsimportw（）
　　2hrA1。conj（）。（。split（））
　　3hrA2。new（（1），（2））
　　逻辑复杂的计算，SQL和存储过程都难以实现，SPL的计算能力更强，可轻松解决此类问题。比如，计算某支股票最长的连续上涨天数：
　　A
　　1hrT（d：AAPL。xlsx）
　　2hra0，A1。max（aif（priceprice〔1〕，a1，0））SPL支持更优的应用架构
　　SPL是解释型语言，提供JDBC接口，可以用SQL或存储过程的形式被JAVA集成，不仅降低了架构的耦合性，还能支持热切换。SPL还支持多种数据源，并支持跨数据源计算。
　　SPL提供了JDBC接口，可被JAVA轻松调用。简单的SPL代码可以像SQL一样，直接嵌入JAVA，比如条件查询：Class。forName（com。esproc。jdbc。InternalDriver）；ConnectionconnectionDriverManager。getConnection（jdbc：esproc：local：）；Statementstatementconnection。createStatement（）；StringstrT（D：Orders。xls）。select（Amount1000Amount3000like（Client，S））；ResultSetresultstatement。executeQuery（str）；
　　SPL支持计算外置，可降低计算代码和前端应用的耦合性。复杂的SPL代码可以先存为脚本文件，再以存储过程的形式被JAVA调用：Class。forName（com。esproc。jdbc。InternalDriver）；ConnectionconnDriverManager。getConnection（jdbc：esproc：local：）；CallableStatementstatementconn。prepareCall（｛callscriptFileName（？，？）｝）；statement。setObject（1，20200101）；statement。setObject（2，20200131）；statement。execute（）；
　　SPL是解释型语言，通过外置代码可实现热切换。解释型语言无须编译，修改后可立即执行，无须重启JAVA应用，可降低维护工作量，提高系统稳定性。
　　SPL支持多种文件数据源，除了xls外，SPL还能读写csvxtXMLJson等文件，比如对txt进行条件查询：
　　T（sOrders。txt）。groups（SellerId；sum（Amount））
　　selectfromd：sOrders。txtwhereClientlike‘S’or（Amount1000andAmount2000）
　　SPL支持跨数据源计算，比如xls和txt的关联计算：
　　join（T（D：Orders。xlsx），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支持更优的应用架构，可实现代码低耦合和热切换，支持多种数据源和跨数据源计算。