Excel文件是常見的數(shù)據(jù)文件，數(shù)據(jù)分析過程中經(jīng)常會用到。有時我們需要用程序代碼對 Excel 文件進行一些自動化處理，這需要程序語言能夠方便地解析 Excel 數(shù)據(jù)。
可以用于解析和處理 Excel 文件的程序語言一般有以下幾種：
1、 常規(guī)高級編程語言，比如 Java
2、? Excel VBA
3、? Python
4、? esProc SPL
本文依次介紹以上幾種程序語言解析Excel文件的特點，重點放在如何將Excel文件讀出為結(jié)構(gòu)化數(shù)據(jù)，之后是用來再計算或是入庫或是其它用途，就只作簡單介紹。
一、??高級語言（以Java為例）
高級語言幾乎都可以讀取Excel數(shù)據(jù)文件，但要看有沒有第三方提供專業(yè)的API來讀取，若是沒有，就需要程序員自己去了解Excel數(shù)據(jù)文件的結(jié)構(gòu)，編寫程序來讀取數(shù)據(jù)，工作量非常大。幸運的是，Apache為Java提供了開源包poi用以讀寫Excel文件，它能讀取每個單元格的數(shù)據(jù)和屬性。讓我們來看看用poi如何將Excel文件讀成結(jié)構(gòu)化的數(shù)據(jù)。
先看一個很簡單的文件：第一行是列標題，第二行開始直到最后一行都是數(shù)據(jù)行。文件內(nèi)容如下圖：
用java調(diào)用poi讀取數(shù)據(jù)，寫出來的程序是這樣：
DataSet ds = null;? //此類用來保存從Excel中讀取的數(shù)據(jù)，需要自己編寫
HSSFWorkbook wb = new HSSFWorkbook( new FileInputStream( "simple.xls" ) );
HSSFSheet sheet = wb.getSheetAt( 0 );? //假定要讀取的數(shù)據(jù)在第一個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;? //單元格數(shù)據(jù)值對象
switch( type ) {? //根據(jù)單元格數(shù)據(jù)類型，將格值處理成對應(yīng)的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 );
//如果是第一行，則將格值設(shè)成列標題，否則設(shè)成數(shù)據(jù)集單元格數(shù)據(jù)
}
}
這段代碼只能讀取最簡單格式的Excel文件，中間還省略了很多格值處理的代碼，但代碼已經(jīng)不短了。如果文件格式更復雜，比如有合并格、復雜的多行表頭表尾、數(shù)據(jù)記錄分散于多行、交叉表等，讀取數(shù)據(jù)的程序代碼就會變得更長更復雜。
可以看出，即使有了poi這樣強大的開源包，使用Java來解析Excel仍然是非常麻煩的。
而且，高級語言只提供比較基礎(chǔ)的底層函數(shù)，缺乏專業(yè)的結(jié)構(gòu)化數(shù)據(jù)計算函數(shù)，比如數(shù)據(jù)集的過濾、排序、分組統(tǒng)計、連接等，都需要程序員自己去編寫，因此即使數(shù)據(jù)讀出來了，但要進行后續(xù)的計算，仍然有大量的工作要做。
二、??Excel VBA
VBA(Visual Basic for Applications)是Visual Basic的一種宏語言，主要能用來擴展Windows的應(yīng)用程序功能，特別是Microsoft Office軟件如Word、Excel、Access等。VBA用于Excel的目的是為了增強Excel的靈活性和數(shù)據(jù)處理能力。VBA可以直接獲取單元格的數(shù)據(jù)，相當于天然有了解析能力，這一點比Java等高級語言方便了很多。但除此之外，它與高級語言一樣，仍然缺乏專業(yè)的結(jié)構(gòu)化計算函數(shù)，讀取數(shù)據(jù)以后的后續(xù)計算，還是需要編寫大量的程序代碼。
比如寫一段分組匯總(對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文件是為了后續(xù)計算和處理，僅僅解析本身是沒有用處的。VBA能天然解析Excel文件，但后續(xù)處理能力并不方便。
三、??Python
Python pandas提供了讀取Excel文件的接口，對于前述用Java讀取的那個簡單格式的Excel文件，用Python讀取的代碼如下：
import pandas as pd
file = 'simple.xls'
data = pd.read_excel(file,sheet_name='Sheet1',header=0)
參數(shù)header=0表明第一行是列標題，data就是讀出來的結(jié)構(gòu)化數(shù)據(jù)集。
對于表頭結(jié)構(gòu)比較復雜的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']
在讀取時用參數(shù)指定沒有表頭且讀數(shù)時跳過前面4行，從第5行數(shù)據(jù)區(qū)開始讀(如果有表尾，還可以指定忽略最后幾行)，程序最后一行設(shè)置數(shù)據(jù)集data的列名。
Excel文件中還常有交叉表數(shù)據(jù)，例如下圖：
讀取這個交叉表的程序如下：
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數(shù)據(jù)如下圖：
可以看出來，Python讀取Excel文件的代碼比較簡單，比Java前進了一大步。而且pandas封裝了不少結(jié)構(gòu)化數(shù)據(jù)的處理函數(shù)，對于后續(xù)計算也比Java和VBA提供了較好的支持。如果是可讀入內(nèi)存的小文件，它可以很簡單地處理。
可惜的是，pandas沒有針對大文件提供直接分批處理的方法，無論讀取還是運算仍然要自己寫，非常麻煩?？蓞⒖?a target="_blank">Python 如何處理大文件。
四、??esProc ?SPL
esProc是專業(yè)的數(shù)據(jù)處理工具，提供了各種讀取Excel文件的方法，其腳本語言SPL中封裝了豐富的結(jié)構(gòu)化數(shù)據(jù)計算函數(shù)，可以完美地支持各種后續(xù)計算、數(shù)據(jù)導出及入庫等工作。
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)
參數(shù)1,5表示讀第1個sheet，從第5行開始讀(也可以指定結(jié)束行)，讀數(shù)以后再用rename修改列名
3、? 交叉表
=file("cross.xls").xlsimport@t(;1,2).rename(#1:Type).pivot@r(Type;Area,Amount)
pivot函數(shù)中以Type分組對表數(shù)據(jù)進行行列轉(zhuǎn)置，選項@r表示將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)，轉(zhuǎn)換后新的列名分別為“Area”、“Amount”。
從代碼上可以看出來，對于解析Excel文件，esProc SPL比Python pandas更為簡潔。事實上，SPL做后續(xù)處理計算比pandas有更大優(yōu)勢，具體可參考桌面輕量級數(shù)據(jù)處理腳本。
而且，esProc還可以很方便地進行大文件數(shù)據(jù)的讀取和計算，它提供游標機制，允許數(shù)據(jù)分析師用類似處理小數(shù)據(jù)量的語法，直觀地處理較大的數(shù)據(jù)量，程序代碼和處理小文件一樣簡單，比如簡單格式的大數(shù)據(jù)量文件，用游標讀數(shù)的程序代碼是：
=file("big.xlsx").xlsimport@tc()
通過比較，我們可以看到，Python pandas和esProc SPL用于解析Excel文件的代碼都很簡練，而且也都具備豐富的結(jié)構(gòu)化計算函數(shù)，可以實現(xiàn)日常工作中的數(shù)據(jù)處理。兩者相比，esProc SPL更為簡潔，并且還能方便地處理大文件。
