快捷導航

Python如何按單元格讀取復雜電子表格(Excel)的數(shù)據(jù)

更新時間：2023年06月21日 17:25:46 作者：肖永威

這篇文章主要介紹了Python如何按單元格讀取復雜電子表格(Excel)的數(shù)據(jù)問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

Python讀取電子表格

本文所使用電子表格的目標是讀取、解析來自Excel編制的數(shù)據(jù)報表，或者軟件界面導出的數(shù)據(jù)報表，這類電子表格報表顯著特點是有一定的格式，且數(shù)據(jù)位置不連續(xù)，而非標準二維數(shù)據(jù)表。

關(guān)于電子表格，比較常見的有微軟Office Excel、WPS Office、Open Office、LibreOffice、永中Office等等，這些軟件關(guān)于電子表格定義相近，文件格式兼容MS Excel標準。

一個電子表格文檔（Excel）稱為一個工作簿
一個工作簿保存在一個擴展名為XLS（.xlsx）的文件中
一個工作簿可以包含多個表（sheet）
在特定行和列的方格稱為單元格、格子

對于文件格式XLS、XLSX

簡單來說：

XLS是excel2003及以前版本所生成的文件格式
XLSX是excel2007及以后版本所生成的文件格式

Python對excel文件的讀寫功能的模塊有以下三種

xlwt：對 xls 格式的 Excel 文件進行寫入；
xlrd：對 xls 格式的 Excel 文件進行讀?。?/li>
OpenpPyXL 實現(xiàn)了對 xlsm 、xlsx 開放電子表格格式的讀寫。

另外，Pandas也能實現(xiàn)了對Excel讀寫，例如read_excel()和to_excel()，直接讀取數(shù)據(jù)到DataFrame中。

首先，安裝第三方包xlrd和xlwt（如果已經(jīng)安裝，則略過）：

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple xlrd
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple xlwt

原始數(shù)據(jù)

原始數(shù)據(jù)是來自接口方提供XLS格式數(shù)據(jù)，內(nèi)容如下圖所示，由程序生成而導出的數(shù)據(jù)（為什么會這樣，歷史原因不予分析），雖然行、列對應(yīng)不清晰，但是總體上還是有規(guī)律的，日報表的每日內(nèi)容格式基本一致，針對數(shù)據(jù)行數(shù)變化需要特殊解析處理。

沒有好辦法，標記上順序數(shù)字，逐個查數(shù)定位！

定義數(shù)據(jù)字典

data_dict = {
            '油品規(guī)格名稱':{'colname':'oilname','id':1},
            '期初庫存':{'colname':'openinginventory','id':7},
            '本期進貨':{'colname':'currentpurchase','id':10},
            '加油機發(fā)出量':{'colname':'sendout','id':13},
            '數(shù)量':{'colname':'values','id':16},
            ......

數(shù)據(jù)解析過程框圖

實踐代碼示例

代碼中關(guān)鍵API函數(shù)解釋

打開XLS工作?。?/p>

workbook = xlrd.open_workbook(filename)

三種方式獲取工作表

table = workbook.sheet_by_index(0)，按索引順序
table = workbook.sheets()[0]
table = workbook.sheet_by_name(‘日報表202110-202210')，按sheet名稱

讀取單元格數(shù)據(jù)：

value = table.cell_value(rowx=2, colx=0)，rowx，colx分別是行、列索引數(shù)（注意：從0開始）

解析XLS代碼示例：

import xlrd
# filename是文件的路徑名稱，如果路徑或者文件名有中文給前面加一個r拜師原生字符。
workbook = xlrd.open_workbook(filename=r'日報表202110-202210.XLS')
#workbook = xlrd.open_workbook(filename=r'2日報表202110-202210.XLS',encoding_override='utf-8')
# 獲取第一個sheet表格
table = workbook.sheets()[0]
# 初始化，按行定義list
dat_row = []
# 數(shù)據(jù)單元格，列的索引位置
cols_index = [1,7,10,13,16,20,23,30,...,83,87]
# 批發(fā)油的情況，價格不一致（猜測）
cols_index_0 = [16,20,23,30,...]
cols_len = len(cols_index)
cols_len_0 = len(cols_index_0)
# 獲取sheet中有效行數(shù)
rows = table.nrows
# 表頭與首行數(shù)據(jù)間隔函數(shù)
irow_bank = 5 
irow = 0
icol = cols_index[0]
while irow < rows:
    if table.cell_value(rowx=irow, colx=0) == '編制單位:':
        cell_vd = table.cell_value(rowx=irow, colx=39)   # 日報時間索引位置39 
    cell_v = table.cell_value(rowx=irow, colx=icol)
    if cell_v == '名稱':
        #irow = irow + irow_bank
        # 跨過空行
        irow = irow + 1
        while len(table.cell_value(rowx=irow, colx=icol)) == 0:
            irow = irow + 1
        while table.cell_value(rowx=irow, colx=icol) != '合計':
            dat_col = []
            for j in range(cols_len):
                dat_col.append(table.cell_value(rowx=irow, colx=cols_index[j]))
            # 批發(fā)油的情況，價格不一致
            if len(table.cell_value(rowx=irow+1, colx=icol)) == 0:
                irow = irow + 1            
                for j in range(cols_len_0):
                    dat_col.insert(4+j,table.cell_value(rowx=irow, colx=cols_index_0[j]))
            else:
                for j in range(cols_len_0):
                    dat_col.append(0)
            dat_col.append(cell_vd)
            print(dat_col)                
            dat_row.append(dat_col)
            irow = irow + 1
    # 逐行掃描
    irow = irow + 1
irow

保持解析結(jié)果到文件中：

import pandas as pd
df.to_excel('dd.xls',encoding='utf_8_sig',index=False)

結(jié)果如下：

總結(jié)

Python按單元格讀取復雜電子表格（Excel）數(shù)據(jù)技術(shù)上比較成熟，易操作。

需要注意事項：

由于數(shù)據(jù)文件來自第三方，可能存在編碼問題（中文亂碼），最好拿到手后，在文件處理的系統(tǒng)上，再另存新文件（本次工作，就是遇到類似問題，花費變天時間也沒有解決編碼轉(zhuǎn)換或者加密問題，簡單的另存解決。
數(shù)據(jù)處理過程，還是使用pandas更加專業(yè)。

好了，以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: