Excel檔案是常見的資料檔案，資料分析過程中經常會用到。有時我們需要用程式程式碼對 Excel 檔案進行一些自動化處理，這需要程式語言能夠方便地解析 Excel 資料。
可以用於解析和處理 Excel 檔案的程式語言一般有以下幾種：
1、 常規高階程式語言，比如 Java
2、 Excel VBA
3、 Python
4、 esProc SPL
本文依次介紹以上幾種程式語言解析Excel檔案的特點，重點放在如何將Excel檔案讀出為結構化資料，之後是用來再計算或是入庫或是其它用途，就只作簡單介紹。
高階語言幾乎都可以讀取Excel資料檔案，但要看有沒有第三方提供專業的API來讀取，若是沒有，就需要程式設計師自己去了解Excel資料檔案的結構，編寫程式來讀取資料，工作量非常大。幸運的是， Apache為Java提供了開源包poi用以讀寫Excel檔案，它能讀取每個單元格的資料和屬性。讓我們來看看用poi如何將Excel檔案讀成結構化的資料。
先看一個很簡單的檔案：第一行是列標題，第二行開始直到最後一行都是資料行。檔案內容如下圖：
用java呼叫poi讀取資料，寫出來的程式是這樣：
DataSet ds = null; //此類用來儲存從Excel中讀取的資料，需要自己編寫
HSSFWorkbook wb = new HSSFWorkbook( new FileInputStream( “simple.xls” ) );
HSSFSheet sheet = wb.getSheetAt( 0 ); //假定要讀取的資料在第一個sheet中
int rows = sheet.getLastRowNum();
int cols = sheet.getRow(0).getLastCellNum();
ds = new DataSet( rows, cols );
for( int row = 0; row <= rows; row++ ) {
HSSFRow r = sheet.getRow( row );
for( int col = 0; col <= cols; col++ ) {
HSSFCell cell = r.getCell( col );
int type = cell.getCellType();
Object cellValue; //單元格資料值物件
switch( type ) { //根據單元格資料型別，將格值處理成對應的Java物件
case HSSFCell.CELL_TYPE_STRING:
……
case HSSFCell.CELL_TYPE_NUMERIC:
……
……
//格值處理程式碼比較長，此處省略
}
if( row == 0 ) ds.setColTitle( col, (String)cellValue );
else ds.setCellValue( row, col, cellValue );
//如果是第一行，則將格值設成列標題，否則設成資料集單元格資料
}
}
這段程式碼只能讀取最簡單格式的Excel檔案，中間還省略了很多格值處理的程式碼，但程式碼已經不短了。如果檔案格式更復雜，比如有合併格、複雜的多行表頭表尾、資料記錄分散於多行、交叉表等，讀取資料的程式程式碼就會變得更長更復雜。
可以看出，即使有了poi這樣強大的開源包，使用Java來解析Excel仍然是非常麻煩的。
而且，高階語言只提供比較基礎的底層函式，缺乏專業的結構化資料計算函式，比如資料集的過濾、排序、分組統計、連線等，都需要程式設計師自己去編寫，因此即使資料讀出來了，但要進行後續的計算，仍然有大量的工作要做。
VBA(Visual Basic for Applications)是Visual Basic的一種宏語言，主要能用來擴充套件Windows的應用程式功能，特別是Microsoft Office軟體如Word、Excel、Access等。VBA用於Excel的目的是為了增強Excel的靈活性和資料處理能力。VBA可以直接獲取單元格的資料，相當於天然有了解析能力，這一點比Java等高階語言方便了很多。但除此之外，它與高階語言一樣，仍然缺乏專業的結構化計算函式，讀取資料以後的後續計算，還是需要編寫大量的程式程式碼。
比如寫一段分組彙總(對sheet1的A列分組，對B列求和)的程式碼是這樣的：
Public Sub test()
Dim Arr
Dim MyRng As Range
Dim i As Long
Dim Dic As Object
Set MyRng = Range(“A1”).CurrentRegion
Set MyRng = MyRng.Offset(1).Resize(MyRng.Rows.Count - 1, 2)
Set Dic = CreateObject(“Scripting.dictionary”)
Arr = MyRng
For i = 1 To UBound(Arr)
If Not Dic.exists(Arr(i, 1)) Then
Dic.Add Arr(i, 1), Arr(i, 2)
Else
Dic.Item(Arr(i, 1)) = Dic.Item(Arr(i, 1)) + Arr(i, 2)
End If
Next i
Sheet2.Range(“A1”) = “subject”
Sheet2.Range(“A2”).Resize(Dic.Count) = Application.WorksheetFunction.Transpose(Dic.keys)
Sheet2.Range(“B1”) = “subtotal”
Sheet2.Range(“B2”).Resize(Dic.Count) = Application.WorksheetFunction.Transpose(Dic.items)
Set Dic = Nothing
End Sub
畢竟我們解析Excel檔案是為了後續計算和處理，僅僅解析本身是沒有用處的。VBA能天然解析Excel檔案，但後續處理能力並不方便。
Python pandas提供了讀取Excel檔案的介面，對於前述用Java讀取的那個簡單格式的Excel檔案，用Python讀取的程式碼如下：
import pandas as pd
file = ‘simple.xls’
data = pd.read_excel(file,sheet_name=’Sheet1’,header=0)
引數header=0表明第一行是列標題，data就是讀出來的結構化資料集。
對於表頭結構比較複雜的Excel，比如下圖：
用Python讀取這個檔案的程式如下：
import pandas as pd
file = ‘complex.xls’
data = pd.read_excel(file,sheet_name=’Sheet1’,header=None,skiprows=[0,1,2,3])
data.columns=[‘No’, ‘ItemCode’, ‘ItemName’, ‘Unit’, ‘Quantity’, ‘Price’, ‘Sum’]
在讀取時用引數指定沒有表頭且讀數時跳過前面4行，從第5行資料區開始讀(如果有表尾，還可以指定忽略最後幾行)，程式最後一行設定資料集data的列名。
Excel檔案中還常有交叉表資料，例如下圖：
讀取這個交叉表的程式如下：
import pandas as pd
file = ‘cross.xls’
data = pd.read_excel(file,sheet_name=’Sheet1’,header=1)
data = data.melt(id_vars=[‘Unnamed: 0’],
value_vars=[‘West’, ‘East’,’Center’, ‘North’,’South’, ‘Northwest’,’Southwest’],
var_name=’Area’,
value_name=’Amount’)
data.rename(columns={‘Unnamed: 0’: ‘Type’})
讀出來的data資料如下圖：
可以看出來，Python讀取Excel檔案的程式碼比較簡單，比Java前進了一大步。而且pandas封裝了不少結構化資料的處理函式，對於後續計算也比Java和VBA提供了較好的支援。如果是可讀入記憶體的小檔案，它可以很簡單地處理。
可惜的是，pandas沒有針對大檔案提供直接分批處理的方法，無論讀取還是運算仍然要自己寫，非常麻煩。可參考Python 如何處理大檔案。
esProc是專業的資料處理工具，提供了各種讀取Excel檔案的方法，其指令碼語言SPL中封裝了豐富的結構化資料計算函式，可以完美地支援各種後續計算、資料匯出及入庫等工作。
esProc讀取Excel檔案的程式非常簡單，只要寫一行程式碼就可以：
1、 簡單格式
=file(“simple.xls”).xlsimport@t()
選項@t表示第一行是列標題
2、 複雜表頭
=file(“complex.xls”). xlsimport(;1,5).rename(#1:No,#2:ItemCode,#3:ItemName,
#4:Unit,#5:Quantity,#6:Price,#7:Sum)
引數1,5表示讀第1個sheet，從第5行開始讀(也可以指定結束行)，讀數以後再用rename修改列名
3、 交叉表
=file(“cross.xls”).xlsimport@t(;1,2).rename(#1:Type).pivot@r(Type;Area,Amount)
pivot函式中以Type分組對錶資料進行行列轉置，選項@r表示將列資料轉換為行資料，轉換後新的列名分別為“Area”、“Amount”。
從程式碼上可以看出來，對於解析Excel檔案，esProc SPL比Python pandas更為簡潔。事實上，SPL做後續處理計算比pandas有更大優勢，具體可參考桌面輕量級資料處理指令碼。
而且，esProc還可以很方便地進行大檔案資料的讀取和計算，它提供遊標機制，允許資料分析師用類似處理小資料量的語法，直觀地處理較大的資料量，程式程式碼和處理小檔案一樣簡單，比如簡單格式的大資料量檔案，用遊標讀數的程式程式碼是：
=file(“big.xlsx”).xlsimport@tc()
透過比較，我們可以看到，Python pandas和esProc SPL用於解析Excel檔案的程式碼都很簡練，而且也都具備豐富的結構化計算函式，可以實現日常工作中的資料處理。兩者相比，esProc SPL更為簡潔，並且還能方便地處理大檔案。
本作品採用《CC 協議》，轉載必須註明作者和本文連結