在數(shù)據(jù)分析業(yè)務(wù)中，經(jīng)常要把Excel文件數(shù)據(jù)結(jié)構(gòu)化解析以后再進行計算或?qū)腙P(guān)系數(shù)據(jù)庫歹啼，但許多Excel文件的格式并不規(guī)整严嗜，而且文件結(jié)構(gòu)也多種多樣碉咆，導致編程進行結(jié)構(gòu)化的工作量會比較大，而且很難通用败玉，每次都要針對文件格式進行分析后再進行開發(fā)敌土。
本文將介紹如何進行各種格式的Excel文件結(jié)構(gòu)化解析，如普通行式运翼、復雜表頭返干、自由格式、交叉表血淌、主子表矩欠、大文件等，并提供用 esProc SPL 編寫的代碼示例悠夯。esProc 是專業(yè)的數(shù)據(jù)計算引擎癌淮，其采用的 SPL 中有完善的 Excel 文件處理函數(shù)，進行結(jié)構(gòu)化解析及后續(xù)的計算沦补、入庫等操作非常方便该默。
1.??普通行式
這是一種最簡單的文件格式，文件中每行都是一條數(shù)據(jù)記錄策彤，更常見的是第一行是列標題栓袖。
示例：在學生成績文件scores.xlsx中匣摘，查詢各班語文平均成績。部分數(shù)據(jù)如下圖：
esProc SPL腳本如下：
2.??復雜表頭
多數(shù)時候裹刮，Excel文件的表頭格式并不簡單音榜，往往是由多行構(gòu)成的，比如有表標題捧弃、項目名稱赠叼、填表人、填寫日期违霞、頁碼等信息嘴办。解析這種格式的文件時，需要跳過復雜的表頭买鸽，指定從數(shù)據(jù)行的位置開始讀取涧郊，然后指定結(jié)構(gòu)化后的數(shù)據(jù)各列的列名。
示例：在項目造價文件itemPrices.xlsx中眼五，計算項目總造價妆艘，部分數(shù)據(jù)如下圖：
esProc SPL腳本如下：
3.??自由格式
一條數(shù)據(jù)記錄分散在多行，字段列值單元格緊跟在列名單元格后面或下面看幼，可能有跨列或跨行的合并單元格批旺。但每條記錄所占的行數(shù)以及對應(yīng)行結(jié)構(gòu)是相同的。循環(huán)讀取時要以每條記錄所占行數(shù)為單位組成一條記錄诵姜。
示例：把自由格式的員工信息文件employee.xlsx存入到數(shù)據(jù)庫表employee中汽煮，部分數(shù)據(jù)如下圖：
esProc SPL腳本如下：
A1創(chuàng)建列名為“ID、Name棚唆、Sex逗物、Position、Birthday瑟俭、Phone翎卓、Address、PostCode”的空序表
A2打開Excel數(shù)據(jù)文件
A3定義雇員信息所在單元格列號序列
B3定義雇員信息所在單元格行號序列
A4用for循環(huán)讀取每個雇員信息
B4?? A3.(~/B3(#))先算出當前雇員單元格編號序列,再讀出這些單元格值組成雇員信息序列摆寄。第一次循環(huán)時為[C1,C2,F2,C3,C4,D5,C7,C8]失暴，第二次循環(huán)時為[C10,C11,F11,C12,C13,D14,C16,C17]……每次行號加9。$[A2.xlscell(]與"A2.xlscell("相同微饥，都是表示一個字符串逗扒，它的好處是在IDE中編寫程序時，如果A2單元格的編號發(fā)生了變化欠橘，$[A2.xlscell(]中的A2會自動變化矩肩，比如在A2前插入了一行，這個表達式就會變成$[A3.xlscell(]肃续，而用引號的話黍檩，就不會自動變了叉袍。
B5判斷雇員ID值是否為空，為空則退出循環(huán)刽酱，結(jié)束讀數(shù)
B6將一條雇員信息存入A1序表尾
B7讓雇員信息的行號序列都加上9喳逛，讀取下一條雇員信息
A8-A10連接數(shù)據(jù)庫，將雇員信息存入數(shù)據(jù)庫表employee棵里，關(guān)閉數(shù)據(jù)庫
讀取出來的A1單元格數(shù)據(jù)如下圖所示：
4.??交叉表
交叉表是統(tǒng)計學中常見的一種矩陣式表格润文，可以清晰地表達兩個變量間的數(shù)量關(guān)系。交叉表數(shù)據(jù)逐行讀入后殿怜，需要以某個列變量為基準典蝌，另一個變量及交叉值進行行轉(zhuǎn)置；或者以某個行變量為基準头谜，另一個變量及交叉值進行列轉(zhuǎn)置骏掀。
示例：將訂單地區(qū)與貨運方式交叉表cross.xlsx文件解析成結(jié)構(gòu)化數(shù)據(jù)，文件數(shù)據(jù)如下圖所示乔夯。
esProc SPL腳本如下：
A3格的部分數(shù)據(jù)如下圖所示：
5.??主子表
每個sheet是一條主表記錄，同時sheet中也包含N條子表記錄款侵。文件中有多少主表記錄末荐，就有多少個sheet。對這種主子表結(jié)構(gòu)的數(shù)據(jù)新锈，需要創(chuàng)建兩個數(shù)據(jù)表分別保存主表和子表的記錄甲脏。
示例：在員工信息登記表文件staff.xlsx中，每個sheet有員工信息及他的家庭成員信息妹笆，請將員工信息及家庭成員信息分別解析成兩個結(jié)構(gòu)化數(shù)據(jù)表块请。其中一個sheet如下圖：
esProc SPL腳本如下：
A1創(chuàng)建列名分別為IDCard、Name拳缠、Sex墩新、Birthday、Nation窟坐、Phone海渊、Depart、Home哲鸳、Marital臣疑、Entry的空序表，用于保存主表員工信息
A2創(chuàng)建列名分別為IDCard徙菠、Name讯沈、Relation、Workplace婿奔、Phone的空序表缺狠，用于保存子表員工家庭成員信息
A3定義主表員工信息所在單元格序列
A4打開Excel數(shù)據(jù)文件
A5循環(huán)讀取Excel文件各sheet數(shù)據(jù)
B5讀取員工信息序列
C5將B5讀取的員工信息保存到序表A1
B6從第6行開始讀取員工家庭成員信息问慎，只讀指定的5列Family、Name儒老、Relation蝴乔、Workplace、Phone
B7將B6序表的Family列改名為IDCard
C7為B7序表的IDCard列賦值為員工信息中的IDCard
B8將B7中的員工家庭成員信息追加到序表A2
A1讀到的部分數(shù)據(jù)如下：
A2讀到的部分數(shù)據(jù)如下：
6.??大文件
大文件結(jié)構(gòu)化解析及計算的相關(guān)原理可參看《大文件上的結(jié)構(gòu)化數(shù)據(jù)計算示例》驮樊，那篇文章是以文本文件為例薇正，本文在此以Excel文件為例再作示范。
示例：在訂單信息大數(shù)據(jù)文件orders.xlsx中囚衔，統(tǒng)計各地區(qū)的訂單金額總和挖腰。部分數(shù)據(jù)如下圖：
esProc SPL腳本如下：
《SPL CookBook》中有更多敏捷計算示例。
