Python讀寫試算表檔案

在某些情況，需要從試算表檔案中讀取資料，或是把資料存入試算表中，Python有一些package包可以很方便的把這類工作做好。這類試算表檔案，較流行的有MS Excel的.xls或.xlsx以及Open/Libre Office Calc的.ods格式。因為Python包可以良好的處理它們，所以這裡就不花時間去探討它們的檔案格式。

網路上的一些公開資料，若是試算表檔案，通常以.xls居多。而Libre Office這免費的自由軟體取得容易，Calc的使用也類似Excel，是最佳的辦公室軟體套件。這裡就以讀取.xls與寫入.ods為例。

會用到二個package，記得要先用pip安裝xlrd與relatorio。閱讀本文者除了要稍有Python基礎，也要知道Libre Office Calc的簡單使用方法，以下例子中會用到插入超連結的操作。

範例說明

這例子只是個簡單範例，儘量避免涉及其它專業領域的術語，重點在說明如何從.xls檔案讀取資料，與把資料寫入.ods中，對其它則不做過多說明，以免主題失焦。

這個例子中，從權民最大網自行下載個權證達人寶典的.xls檔案，例子中用的檔案，大小約11 MB，第一頁工作表的資料有1萬5千多筆。假定要從中取得連結標的為2454聯發科的所有權證資料，其中剩餘天數大於60天的要寫入.ods檔案中。

.ods中除了寫入公司代碼與名稱，還有權證資料，但只要以下欄位：

• 權證代碼
• 權證名稱
• 剩餘天數
• 標的價格
• 最新履約
• 價認購/售類別

這例子的工作就只是從.xls檔案讀入資料，把其中符合所需條件的資料寫入.ods檔案。以下開始說明其中工作的代碼。

讀取

import xlrd

XLS = '權證達人寶典_NEWVOL_2017-08-25.xls'
TICKER = ('2454', '聯發科')

def read_xls(xls, tk):
    """ 從Excel檔案讀取 """
    ret = []
    book = xlrd.open_workbook(xls)
    sheet = book.sheet_by_index(0)    # 第一個工作表
    for row_index in range(5, sheet.nrows - 1):        # 略過前面5列的標頭
        ticker = sheet.cell(row_index, 17).value    # 第18欄，標的代碼
        if ticker != tk:
            continue
        remain = int(sheet.cell(row_index, 15).value)    # 剩餘天數
        if remain <= 60:
            continue
        code = sheet.cell(row_index, 0).value    # 權證代碼
        name = sheet.cell(row_index, 1).value    # 權證名稱
        price = sheet.cell(row_index, 19).value  # 標的價格
        sp = sheet.cell(row_index, 22).value     # 最新履約價
        w_type = sheet.cell(row_index, sheet.ncols - 1).value    # 最後一欄，認購/售類別

        ret.append([code, name, remain, price, sp, w_type])
        # print(ret[-1])
    return ret

wlist = read_xls(XLS, TICKER[0])

讀取的工作較容易辦到，再來說明寫入.ods的作法，首先需要個範本檔，在其中決定如何擺放資料，然後把資料打包後傳入其中。

寫入

範本檔製作

做個.ods範本檔，其中使用Genshi範本語言置入placeholder佔位符，用來決定資料的擺放位置，供資料寫入。別擔心Genshi，它的語法相當簡單，這裡也只用到簡單的表示方式，與Python語法極類似。

要寫入的資料則包裝成資料物件，以方便寫入時，在範本中可依該物件的屬性找到位置把值填入，像例子中的公司代碼ticker與名稱name。多筆的資料，如例子中的權證資料，逐條打包成字典的串列，指定給資料物件中的某個屬性即可，如本例中用lines屬性，在範本中用for each將lines中的資料逐條帶入。

範本檔製作佔位符的方式是以“插入”>>“超連結”進行，URL開頭必須是relatorio，如圖：

整個做完插入超連結的範本檔：

在這裡，範本檔中使用的物件(object)名稱為o，Python代碼中要把打包好的資料物件對應過來。注意到其中的o.ticker, o.name與o.lines，待會在Python代碼會見到它。接下來看資料如何打包成物件並傳進來。

資料打包成物件並傳入範本物件

這裡簡單的做個基於字典的資料類別，依這類別建立一物件，叫warrant。把權證資料逐條包裝成字典後指定到該物件的lines屬性，Class定義中並無此屬性，但別忘了Python可動態地增加屬性。也把公司代碼、名稱指定給warrant的ticker, name屬性。

之後在範本物件的generate()把資料物件warrant指定給範本中的o，render()傳回StringIO物件供存到檔案。

from relatorio.templates.opendocument import Template

ODS_TEMPLATE = 'template.ods'
ODS = 'output.ods'


class Warrant(dict):
    pass


def wrap_data(lst):
    """ 權證資料打包成字典串列 """
    ret = []
    for x in lst:
        ret.append({'code': x[0], 'name': x[1], 'remain': x[2], 'price': x[3], 'sp': x[4], 'w_type': x[-1]})
    return ret


def write_ods(data, template, ods):
    """ 資料套入範本，另存.ods檔 """
    warrant = Warrant(lines=data, ticker=TICKER[0], name=TICKER[1])
    basic = Template(source=None, filepath=template)
    with open(ods, 'wb') as f:
        f.write(basic.generate(o=warrant).render().getvalue())

data = wrap_data(wlist)
write_ods(data, ODS_TEMPLATE, ODS)

這個寫入.ods的範例較為簡單，所以代碼不多。relatorio不僅可用在.ods試算表，也可用在LibreOffice Writer與Impress，詳情請見它的完整說明。

上述範例代碼純粹為展示與說明而寫，並非最有效率的作法，較好的作法可在讀取.xls時就順便把資料打包一下。

xlrd與relatorio只是許多這類用途的其中兩種，在PyPI中還可以找到不少相關的，〈Working with Excel Files in Python〉也提到一些。

完整的範例代碼重新整理如下，全部範例代碼、資料、範本與輸出的檔案打包在此。

import xlrd
from relatorio.templates.opendocument import Template


XLS = '權證達人寶典_NEWVOL_2017-08-25.xls'
TICKER = ('2454', '聯發科')
ODS_TEMPLATE = 'template.ods'
ODS = 'output.ods'


class Warrant(dict):
    pass


def read_xls(xls, tk):
    """ 從Excel檔案讀取 """
    ret = []
    book = xlrd.open_workbook(xls)
    sheet = book.sheet_by_index(0)    # 第一個工作表
    for row_index in range(5, sheet.nrows - 1):        # 略過前面5列的標頭
        ticker = sheet.cell(row_index, 17).value    # 第18欄，標的代碼
        if ticker != tk:
            continue
        remain = int(sheet.cell(row_index, 15).value)    # 剩餘天數
        if remain <= 60:
            continue
        code = sheet.cell(row_index, 0).value    # 權證代碼
        name = sheet.cell(row_index, 1).value    # 權證名稱
        price = sheet.cell(row_index, 19).value  # 標的價格
        sp = sheet.cell(row_index, 22).value     # 最新履約價
        w_type = sheet.cell(row_index, sheet.ncols - 1).value    # 最後一欄，認購/售類別

        ret.append([code, name, remain, price, sp, w_type])
        # print(ret[-1])
    return ret


def wrap_data(lst):
    """ 權證資料打包成字典串列 """
    ret = []
    for x in lst:
        ret.append({'code': x[0], 'name': x[1], 'remain': x[2], 'price': x[3], 'sp': x[4], 'w_type': x[-1]})
    return ret


def write_ods(data, template, ods):
    """ 資料套入範本，另存.ods檔 """
    warrant = Warrant(lines=data, ticker=TICKER[0], name=TICKER[1])
    basic = Template(source=None, filepath=template)
    with open(ods, 'wb') as f:
        f.write(basic.generate(o=warrant).render().getvalue())


if __name__ == '__main__':
    wlist = read_xls(XLS, TICKER[0])
    data = wrap_data(wlist)
    write_ods(data, ODS_TEMPLATE, ODS)