快捷導(dǎo)航

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

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

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

Python讀取電子表格

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

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

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

對(duì)于文件格式XLS、XLSX

簡(jiǎn)單來(lái)說(shuō)：

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

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

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

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

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

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

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

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

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

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

實(shí)踐代碼示例

代碼中關(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(‘日?qǐng)?bào)表202110-202210')，按sheet名稱

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

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

解析XLS代碼示例：

import xlrd
# filename是文件的路徑名稱，如果路徑或者文件名有中文給前面加一個(gè)r拜師原生字符。
workbook = xlrd.open_workbook(filename=r'日?qǐng)?bào)表202110-202210.XLS')
#workbook = xlrd.open_workbook(filename=r'2日?qǐng)?bào)表202110-202210.XLS',encoding_override='utf-8')
# 獲取第一個(gè)sheet表格
table = workbook.sheets()[0]
# 初始化，按行定義list
dat_row = []
# 數(shù)據(jù)單元格，列的索引位置
cols_index = [1,7,10,13,16,20,23,30,...,83,87]
# 批發(fā)油的情況，價(jià)格不一致（猜測(cè)）
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)   # 日?qǐng)?bào)時(shí)間索引位置39 
    cell_v = table.cell_value(rowx=irow, colx=icol)
    if cell_v == '名稱':
        #irow = irow + irow_bank
        # 跨過(guò)空行
        irow = irow + 1
        while len(table.cell_value(rowx=irow, colx=icol)) == 0:
            irow = irow + 1
        while table.cell_value(rowx=irow, colx=icol) != '合計(jì)':
            dat_col = []
            for j in range(cols_len):
                dat_col.append(table.cell_value(rowx=irow, colx=cols_index[j]))
            # 批發(fā)油的情況，價(jià)格不一致
            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按單元格讀取復(fù)雜電子表格（Excel）數(shù)據(jù)技術(shù)上比較成熟，易操作。

需要注意事項(xiàng)：

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

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

您可能感興趣的文章: