??作者： 小小明-代碼實(shí)體

??博客主頁：https://blog.csdn.net/as604049322

??歡迎點(diǎn)贊 ?? 收藏 ?留言 ?? 歡迎討論！

今天我們將研究pandas如何使用openpyxl引擎讀取xlsx格式的Excel的數(shù)據(jù)，并考慮以面向過程的形式簡單的自己實(shí)現(xiàn)一下。

截止目前本人所使用的pandas和openpyxl版本為：

• pandas：1.5.2
• openpyxl：3.0.10

今天所有的測試全部基于以下文件：

pandas的read_excel核心代碼

這里我使用pycharm工具對以下代碼進(jìn)行debug跟蹤：

import pandas as pd
df = pd.read_excel("張三.xlsx")

核心就是兩行代碼：

io = ExcelFile(io)
return io.parse(...)

我們研究一下這兩行代碼所做的事：

ExcelFile構(gòu)造函數(shù)

內(nèi)容有很多，我們挑一些有價(jià)值的內(nèi)容進(jìn)行解析。默認(rèn)傳遞的參數(shù)下，會調(diào)用inspect_excel_format函數(shù)獲取文件的擴(kuò)展名。

直接通過文件名獲取的擴(kuò)展名有可能不真實(shí)，我們可以查看pandas.io.excel._base.inspect_excel_format的源碼，研究pandas判斷Excel真實(shí)擴(kuò)展名的實(shí)現(xiàn)。

個(gè)人在閱讀源碼后，整理出如下可以直接使用的方法：

from zipfile import ZipFile

def inspect_excel_format(filename):
    XLS_SIGNATURES = (
        b"\x09\x00\x04\x00\x07\x00\x10\x00",  # BIFF2
        b"\x09\x02\x06\x00\x00\x00\x10\x00",  # BIFF3
        b"\x09\x04\x06\x00\x00\x00\x10\x00",  # BIFF4
        b"\xD0\xCF\x11\xE0\xA1\xB1\x1A\xE1",  # Compound File Binary
    )
    ZIP_SIGNATURE = b"PK\x03\x04"
    PEEK_SIZE = max(map(len, XLS_SIGNATURES + (ZIP_SIGNATURE,)))
    with open(filename, "rb") as stream:
        peek = stream.read(PEEK_SIZE)
        if peek is None:
            raise ValueError("stream is empty")
        if any(peek.startswith(sig) for sig in XLS_SIGNATURES):
            return "xls"
        elif not peek.startswith(ZIP_SIGNATURE):
            return None
        with ZipFile(stream) as zf:
            component_names = [
                name.replace("\\", "/").lower() for name in zf.namelist()
            ]
            if "xl/workbook.xml" in component_names:
                return "xlsx"
            if "xl/workbook.bin" in component_names:
                return "xlsb"
            if "content.xml" in component_names:
                return "ods"
            return "zip"

獲取到擴(kuò)展名之后，get_default_engine將獲取默認(rèn)的處理引擎，定義如下：

_default_readers = {
    "xlsx": "openpyxl",
    "xlsm": "openpyxl",
    "xlsb": "pyxlsb",
    "xls": "xlrd",
    "ods": "odf",
}

self._engines[engine]會找到對應(yīng)的處理類來處理當(dāng)前文件。

而ExcelFile有個(gè)類定義：

_engines: Mapping[str, Any] = {
	"xlrd": XlrdReader,
	"openpyxl": OpenpyxlReader,
	"odf": ODFReader,
	"pyxlsb": PyxlsbReader,
}

于是就可以使用OpenpyxlReader來讀取對應(yīng)的Excel文件：

self._reader = OpenpyxlReader(self._io)

OpenpyxlReader的構(gòu)造函數(shù)

首先判斷是否安裝openpyxl，然后調(diào)用父類BaseExcelReader的構(gòu)造方法，其中核心代碼為：

self.book = self.load_workbook(self.handles.handle)

而OpenpyxlReader的load_workbook實(shí)現(xiàn)為：

from openpyxl import load_workbook

return load_workbook(
	filepath_or_buffer, read_only=True, data_only=True, keep_links=False
)

可以確定pandas再調(diào)用openpyxl時(shí)，固定了這些參數(shù)。

ExcelFile.parse

跟蹤可以看到內(nèi)部調(diào)用了self._reader.parse，這里的核心代碼為：

ret_dict = False
sheets = [sheet_name]
output = {}
for asheetname in sheets:
    if isinstance(asheetname, str):
        sheet = self.get_sheet_by_name(asheetname)
    else:
        sheet = self.get_sheet_by_index(asheetname)
    data = self.get_sheet_data(sheet, convert_float, file_rows_needed)
    parser = TextParser(data,header=header)
    output[asheetname] = parser.read(nrows=nrows)
if ret_dict:
	return output
else:
	return output[asheetname]

self.get_sheet_data使用openpyxl引擎讀取出指定表格的數(shù)據(jù)，我們后面再細(xì)究。

TextParser用于解析結(jié)果，構(gòu)造函數(shù)調(diào)用TextFileReader的_make_engine處理結(jié)果數(shù)據(jù)，內(nèi)部使用python引擎對應(yīng)的PythonParser進(jìn)行解析處理，PythonParser的構(gòu)造方法中，核心代碼為：

columns,self.num_original_columns,self.unnamed_cols = self._infer_columns()

該代碼根據(jù)header參數(shù)讀取data的前N行作為列，每次調(diào)用self._next_line()讀取，會改變self.pos的值即當(dāng)前位置，并當(dāng)前讀取到的行存入self.buf。但是最終該函數(shù)會清空self.buf的值。

(index_names, self.orig_names, self.columns) = self._get_index_name(
	self.columns
)

這行代碼的實(shí)現(xiàn)會兩次調(diào)用self._next_line()讀取數(shù)據(jù)，這兩行的數(shù)據(jù)會存入self.buf中。

parser.read的核心代碼為：

index, columns, col_dict = self._engine.read(nrows)
return DataFrame(col_dict, columns=columns, index=index)

self._engine.read調(diào)用_get_lines函數(shù)將剩余的數(shù)據(jù)都讀入self.buf中并返回，最終得到處理表頭以外的所有行數(shù)據(jù)content。

然后調(diào)用self._rows_to_cols(content)將所有的行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)：

alldata = self._rows_to_cols(content)

這行代碼內(nèi)部的核心實(shí)現(xiàn)為：

import pandas._libs.lib as lib

zipped_content = list(lib.to_object_array(content, min_width=col_len).T)

不過lib.to_object_array的底層采用其他語言實(shí)現(xiàn)，只能直接查看。

然后_exclude_implicit_index將列數(shù)據(jù)轉(zhuǎn)換為字典，核心代碼為：

{
	name: alldata[i + offset] for i, name in enumerate(names) if i < len_alldata
}

最終經(jīng)過一些轉(zhuǎn)換后得到最終結(jié)果。

ExcelFile.get_sheet_data

前面在OpenpyxlReader的構(gòu)造函數(shù)中，通過openpyxl的load_workbook函數(shù)加載了Excel文件得到self.book。

self.get_sheet_by_name(asheetname)的實(shí)現(xiàn)是：

return self.book[asheetname]

self.get_sheet_by_index(asheetname)的實(shí)現(xiàn)是：

return self.book.worksheets[index]

可以翻譯為：

sheet = self.book.worksheets[0]
data = self.get_sheet_data(sheet, convert_float, file_rows_needed)

get_sheet_data的源碼：

內(nèi)部核心獲取數(shù)據(jù)的代碼為sheet.rows，該屬性是調(diào)用了openpyxl.worksheet.worksheet.Worksheet的iter_rows方法獲取數(shù)據(jù)。

pandas會使用_convert_cell方法對openpyxl獲取的單元格提取數(shù)值并轉(zhuǎn)換，convert_float參數(shù)默認(rèn)為True，作用是當(dāng)一個(gè)數(shù)值可以轉(zhuǎn)為整數(shù)時(shí)就是整數(shù)，并不是所有數(shù)值都轉(zhuǎn)為浮點(diǎn)數(shù)。

然后while循環(huán)實(shí)現(xiàn)剔除空行。

總結(jié)

pandas讀取Excel的核心代碼，我們可以總結(jié)為如下形式：

from openpyxl import load_workbook
import pandas as pd
import numpy as np
import pandas._libs.lib as lib
from openpyxl.cell.cell import (
    TYPE_ERROR,
    TYPE_NUMERIC,
)


def convert_cell(cell, convert_float=True):
    if cell.value is None:
        return ""  # compat with xlrd
    elif cell.data_type == TYPE_ERROR:
        return np.nan
    elif cell.data_type == TYPE_NUMERIC:
        if convert_float:
            val = int(cell.value)
            if val == cell.value:
                return val
        else:
            return float(cell.value)
    return cell.value


workbook = load_workbook(filename="張三.xlsx", read_only=True, data_only=True, keep_links=False)
sheet = workbook.worksheets[0]
data = [[convert_cell(cell) for cell in row] for row in sheet.rows]
names = data[0]
alldata = lib.to_object_array(data[1:], min_width=len(names)).T
zipped_content = {name: alldata[i] for i, name in enumerate(names)}
df = pd.DataFrame(zipped_content)

當(dāng)然pandas多余的處理代碼比這些更復(fù)雜。

我們也可以進(jìn)一步簡化為：

from openpyxl import load_workbook
import pandas as pd

workbook = load_workbook(filename="張三.xlsx", read_only=True, data_only=True, keep_links=False)
sheet = workbook.worksheets[0]
data = [row for row in sheet.iter_rows(values_only=True)]
df = pd.DataFrame(data[1:], columns=data[0])

仿openpyxl源碼讀取Excel

openpyxl源碼讀取部分的源碼相比pandas處理部分更加復(fù)雜，下面我主要對核心代碼進(jìn)行翻譯。

load_workbook的代碼為：

def load_workbook(filename, read_only=False, keep_vba=KEEP_VBA,
                  data_only=False, keep_links=True):
    reader = ExcelReader(filename, read_only, keep_vba, data_only, keep_links)
    reader.read()
    return reader.wb

ExcelReader核心：

from zipfile import ZipFile, ZIP_DEFLATED, BadZipfile

filename = r"D:\PycharmProjects\demo1\test\張三.xlsx"
archive = ZipFile(filename, 'r')
valid_files = archive.namelist()
print(valid_files)

['[Content_Types].xml', '_rels/', '_rels/.rels', 'docProps/', 'docProps/app.xml', 'docProps/core.xml', 'docProps/custom.xml', 'xl/', 'xl/_rels/', 'xl/_rels/workbook.xml.rels', 'xl/sharedStrings.xml', 'xl/styles.xml', 'xl/theme/', 'xl/theme/theme1.xml', 'xl/workbook.xml', 'xl/worksheets/', 'xl/worksheets/sheet1.xml', 'xl/worksheets/sheet2.xml', 'xl/worksheets/sheet3.xml']

read的代碼為：

def read(self):
    self.read_manifest()
    self.read_strings()
    self.read_workbook()
    self.read_properties()
    self.read_theme()
    apply_stylesheet(self.archive, self.wb)
    self.read_worksheets()
    self.parser.assign_names()
    if not self.read_only:
        self.archive.close()

這里將一步步從Excel壓縮包中讀取需要的數(shù)據(jù)。

在處理之前，我們定義一些需要用到的常量：

ARC_CORE = 'docProps/core.xml'
PACKAGE_RELS = '_rels'
ARC_THEME = f'xl/theme/theme1.xml'
ARC_STYLE = f'xl/styles.xml'
ARC_CONTENT_TYPES = '[Content_Types].xml'
SHEET_MAIN_NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'
INLINE_STRING = "{%s}is" % SHEET_MAIN_NS
ROW_TAG = '{%s}row' % SHEET_MAIN_NS
VALUE_TAG = '{%s}v' % SHEET_MAIN_NS
SECS_PER_DAY = 24*60*60

read_manifest

該函數(shù)用于讀取各類xml在壓縮包中的路徑，openpyxl使用特殊的自定義類來解析xml，我們則使用基本語法讀取需要的數(shù)據(jù)：

from lxml import etree
import re

def localname(name):
    NS_REGEX = "({(?P<namespace>.*)})?(?P<localname>.*)"
    return re.match(NS_REGEX, name).group('localname')


def read_manifest(archive):
    src = archive.read(ARC_CONTENT_TYPES)
    manifest = {}
    for el in etree.fromstring(src):
        manifest.setdefault(localname(el.tag), []).append(el.attrib)
    return manifest


manifest = read_manifest(archive)
manifest

{'Default': [{'Extension': 'rels', 'ContentType': 'application/vnd.openxmlformats-package.relationships+xml'},
  {'Extension': 'xml', 'ContentType': 'application/xml'}],
 'Override': [{'PartName': '/docProps/app.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.extended-properties+xml'},
  {'PartName': '/docProps/core.xml', 'ContentType': 'application/vnd.openxmlformats-package.core-properties+xml'},
  {'PartName': '/docProps/custom.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.custom-properties+xml'},
  {'PartName': '/xl/sharedStrings.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml'},
  {'PartName': '/xl/styles.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml'},
  {'PartName': '/xl/theme/theme1.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.theme+xml'},
  {'PartName': '/xl/workbook.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml'},
  {'PartName': '/xl/worksheets/sheet1.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'},
  {'PartName': '/xl/worksheets/sheet2.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'},
  {'PartName': '/xl/worksheets/sheet3.xml', 'ContentType': 'application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml'}]}

read_strings

該方法用于讀取Excel中的所有常量字符串：

from defusedxml.ElementTree import iterparse

def get_text_content(node):
    snippets = []
    plain = node.find("./x:t", namespaces={"x": SHEET_MAIN_NS})
    if plain is not None:
        snippets.append(plain.text)
    for t in node.findall("./x:r/x:t", namespaces={"x": SHEET_MAIN_NS}):
        snippets.append(t.text)
    return "".join(snippets)

def read_strings(manifest, archive):
    ct = None
    SHARED_STRINGS = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"
    for t in manifest["Override"]:
        if t["ContentType"] == SHARED_STRINGS:
            ct = t
            break
    shared_strings = []
    STRING_TAG = '{%s}si' % SHEET_MAIN_NS

    if ct is not None:
        strings_path = ct["PartName"][1:]
        with archive.open(strings_path) as xml_source:
            for _, node in iterparse(xml_source):
                if node.tag != STRING_TAG:
                    continue
                text = get_text_content(node).replace('x005F_', '')
                node.clear()
                shared_strings.append(text)
    return shared_strings


shared_strings = read_strings(manifest, archive)
print(shared_strings)

openpyxl的源碼在這個(gè)部分使用defusedxml解析xml，如果我們使用etree解析全部加載到內(nèi)存的xml，則可以使用如下代碼：

def read_strings(manifest, archive):
    ct = None
    SHARED_STRINGS = "application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"
    for t in manifest["Override"]:
        if t["ContentType"] == SHARED_STRINGS:
            ct = t
            break
    shared_strings = []
    STRING_TAG = '{%s}si' % SHEET_MAIN_NS

    if ct is not None:
        strings_path = ct["PartName"][1:]
        root = etree.fromstring(archive.read(strings_path))
        for node in root.xpath("http://x:si", namespaces={"x": SHEET_MAIN_NS}):
            snippets = node.xpath(".//x:t/text()", namespaces={"x": SHEET_MAIN_NS})
            shared_strings.append("".join(snippets).replace('x005F_', ''))
    return shared_strings


shared_strings = read_strings(manifest, archive)
print(shared_strings)

使用xpath解析xml可以簡化代碼。

最終得到的常量字符串有：

['商品', '單價(jià)', '數(shù)量', '訂單號', '訂單時(shí)間', '總金額', '名稱管理', '蘋果', 'A', '哈密瓜', 'B', '芒果', 'C']

read_workbook

這部分的核心代碼有：

wb_part = _find_workbook_part(self.package)
self.parser = WorkbookParser(self.archive, wb_part.PartName[1:], keep_links=self.keep_links)
self.parser.parse()

我們先翻譯_find_workbook_part(self.package)：

def find_workbook_part_name(manifest):
    part = None
    WORKBOOK_MACRO = "application/vnd.ms-excel.{}.macroEnabled.main+xml"
    WORKBOOK = "application/vnd.openxmlformats-officedocument.spreadsheetml.{}.main+xml"
    XLTM = WORKBOOK_MACRO.format('template')
    XLSM = WORKBOOK_MACRO.format('sheet')
    XLTX = WORKBOOK.format('template')
    XLSX = WORKBOOK.format('sheet')
    for ct in (XLTM, XLTX, XLSM, XLSX):
        for t in manifest["Override"]:
            if t["ContentType"] == ct:
                return t["PartName"][1:]


workbook_part_name = find_workbook_part_name(manifest)
workbook_part_name

'xl/workbook.xml'

WorkbookParser.parser解析的數(shù)據(jù)有點(diǎn)多，下面我盡量只提取需要的數(shù)據(jù)：

src = archive.read(workbook_part_name)
node = etree.fromstring(src)
workbookPr = node.xpath("./x:workbookPr", namespaces={"x": SHEET_MAIN_NS})[0].attrib
print(workbookPr)

{'codeName': 'ThisWorkbook'}

import datetime

def get_epoch(workbookPr):
    MAC_EPOCH = datetime.datetime(1904, 1, 1)
    WINDOWS_EPOCH = datetime.datetime(1899, 12, 30)

    epoch = WINDOWS_EPOCH
    if "date1904" in workbookPr and workbookPr["date1904"]:
        epoch = MAC_EPOCH
    return epoch


epoch = get_epoch(workbookPr)
epoch

datetime.datetime(1899, 12, 30, 0, 0)

獲取活躍表格角標(biāo)：

bookViews = [el.attrib for el in node.xpath(
    "x:bookViews/x:workbookView", namespaces={"x": SHEET_MAIN_NS})]
bookViews

[{'windowWidth': '28800', 'windowHeight': '12690'}]

def get_active(bookViews):
    for view in bookViews:
        if "activeTab" in view:
            return int(view["activeTab"])
    return 0

active = get_active(bookViews)
active

0

獲取所有工作表的名稱和ID：

sheets = [{localname(k): v for k, v in el.attrib.items()} for el in node.xpath(
    "x:sheets/x:sheet", namespaces={"x": SHEET_MAIN_NS})]
sheets

[{'name': 'Sheet1', 'sheetId': '1', 'id': 'rId1'},
 {'name': 'Sheet2', 'sheetId': '2', 'id': 'rId2'},
 {'name': 'Sheet3', 'sheetId': '3', 'id': 'rId3'}]

讀取命名空間的定義：

def getDefinedNames(node):
    valid_names = {}
    for el in node.xpath(
            "x:definedNames/x:definedName", namespaces={"x": SHEET_MAIN_NS}):
        name, value = el.get("name"), el.text
        if name in ("_xlnm.Print_Titles", "_xlnm.Print_Area") and "localSheetId" not in el:
            continue
        elif name == "_xlnm._FilterDatabase":
            continue
        valid_names[name] = value
    return valid_names

definedNames = getDefinedNames(node)
definedNames

{'aaa': 'Sheet1!$A$3', 'bbb': 'Sheet1!$A$2', 'ccc': 'Sheet1!$A$4'}

read_properties

用于讀取文檔的一些屬性信息：

from openpyxl.utils.datetime import from_ISO8601

properties = {}
if ARC_CORE in valid_files:
    for el in etree.fromstring(archive.read(ARC_CORE)):
        key = localname(el.tag)
        value = el.text
        if key in ("lastPrinted", "created", "modified"):
            value = from_ISO8601(value)
        properties[key] = value
properties

{'creator': 'openpyxl',
 'lastModifiedBy': '那年&那天',
 'created': datetime.datetime(2023, 3, 8, 9, 7),
 'modified': datetime.datetime(2023, 3, 26, 15, 40, 30)}

符合ISO8601格式的時(shí)間字符串有很多種形式，上述代碼直接使用openpyxl現(xiàn)成的實(shí)現(xiàn)，將2023-03-08T09:07:00Z等形式的時(shí)間字符串解析為日期時(shí)間對象。

核心代碼是使用如下正則進(jìn)行匹配：

ISO_REGEX = re.compile(r'''
(?P<date>(?P<year>\d{4})-(?P<month>\d{2})-(?P<day>\d{2}))?T?
(?P<time>(?P<hour>\d{2}):(?P<minute>\d{2})(:(?P<second>\d{2})(?P<microsecond>\.\d{1,3})?)?)?Z?''', re.VERBOSE)

apply_stylesheet

這個(gè)函數(shù)除了讀取字體、對齊、邊框、填充等各種樣式數(shù)據(jù)以外，還會讀取出哪些列是日期格式的列，用于后續(xù)將數(shù)值類型的列解析為時(shí)間。

對于純數(shù)據(jù)讀取而言，樣式數(shù)據(jù)并不是我們需要的數(shù)據(jù)，這里只演示字體列表的讀?。?/p>

fonts = []
node = etree.fromstring(archive.read(ARC_STYLE))
for el in node.xpath(
        "x:fonts/x:font", namespaces={"x": SHEET_MAIN_NS}):
    fonts.append({localname(e.tag): e.attrib for e in el})

讀取出哪些列是日期格式的列，體現(xiàn)在openpyxl.styles.stylesheet.Stylesheet類構(gòu)造函數(shù)的self._normalise_numbers()函數(shù)上。

根據(jù)custom_formats函數(shù)的實(shí)現(xiàn)，解析出所有的自定義格式：

node = etree.fromstring(archive.read(ARC_STYLE))
numFmts = {
    int(el.get("numFmtId")): el.get("formatCode") for el in node.xpath(
        "x:numFmts/x:numFmt", namespaces={"x": SHEET_MAIN_NS})
}
numFmts

{41: '_ * #,##0_ ;_ * \\-#,##0_ ;_ * "-"_ ;_ @_ ',
 42: '_ "￥"* #,##0_ ;_ "￥"* \\-#,##0_ ;_ "￥"* "-"_ ;_ @_ ',
 43: '_ * #,##0.00_ ;_ * \\-#,##0.00_ ;_ * "-"??_ ;_ @_ ',
 44: '_ "￥"* #,##0.00_ ;_ "￥"* \\-#,##0.00_ ;_ "￥"* "-"??_ ;_ @_ ',
 176: 'yyyy\\-m\\-d\\ h:mm:ss',
 177: '[h]:mm:ss;@'}

獲取日期列的實(shí)現(xiàn)：

from openpyxl.styles.numbers import BUILTIN_FORMATS, STRIP_RE

date_formats = set()
cell_styles = node.xpath("x:cellXfs/x:xf", namespaces={"x": SHEET_MAIN_NS})
for idx, el in enumerate(cell_styles):
    style = el.attrib
    numFmtId = int(style["numFmtId"])
    if numFmtId in numFmts:
        fmt = numFmts[numFmtId]
    else:
        fmt = BUILTIN_FORMATS[numFmtId]
    fmt = fmt.split(";")[0]
    if re.search(r"[^\\][dmhysDMHYS]", STRIP_RE.sub("", fmt)) is not None:
        date_formats.add(idx)
date_formats

read_worksheets

這里我們已經(jīng)設(shè)置了只讀形式，而且我們不考慮透視圖類型的工作表，那么核心代碼為：

def read_worksheets(self):
    for sheet, rel in self.parser.find_sheets():
        ws = ReadOnlyWorksheet(self.wb, sheet.name, rel.target, self.shared_strings)
        ws.sheet_state = sheet.state
        self.wb._sheets.append(ws)

所需要的數(shù)據(jù)都封裝到ReadOnlyWorksheet對象中。

其實(shí)所需要的數(shù)據(jù)只有表名和對應(yīng)的路徑，解析代碼如下：

import posixpath

def get_rels_path(path):
    folder, obj = posixpath.split(path)
    filename = posixpath.join(folder, '_rels', '{0}.rels'.format(obj))
    return filename


def get_dependents(archive, filename):
    filename = get_rels_path(filename)
    folder = posixpath.dirname(filename)
    parent = posixpath.split(folder)[0]
    rels = {}
    for el in etree.fromstring(archive.read(filename)):
        r = el.attrib
        if r.get("TargetMode") == "External":
            continue
        elif r["Target"].startswith("/"):
            r["Target"] = r["Target"][1:]
        else:
            pth = posixpath.join(parent, r["Target"])
            r["Target"] = posixpath.normpath(pth)
        rels[r.get("Id")] = r
    return rels


rels = get_dependents(archive, workbook_part_name)
name2file = {}
for sheet in sheets:
    name2file[sheet["name"]] = rels[sheet["id"]]["Target"]
name2file

{'Sheet1': 'xl/worksheets/sheet1.xml',
 'Sheet2': 'xl/worksheets/sheet2.xml',
 'Sheet3': 'xl/worksheets/sheet3.xml'}

而ReadOnlyWorksheet的構(gòu)造函數(shù)中，self._get_size()函數(shù)會解析整個(gè)表的大小，面向過程的實(shí)現(xiàn)為：

from openpyxl.utils.cell import column_index_from_string

def parse_dimensions(worksheet_path):
    source = archive.open(worksheet_path)
    for _event, element in iterparse(source):
        tag_name = localname(element.tag)
        if tag_name == "dimension":
            ref = element.get("ref")
            min_col, min_row, sep, max_col, max_row = re.match(
                "\$?([A-Za-z]{1,3})\$?(\d+)(:\$?([A-Za-z]{1,3})\$?(\d+))?", ref).groups()
            min_col, max_col = map(
                column_index_from_string, (min_col, max_col))
            min_row, max_row = map(int, (min_row, max_row))
            return min_col, min_row, max_col, max_row
        elif tag_name == "sheetData":
            break
        element.clear()
    source.close()


worksheet_path = name2file['Sheet1']
dimensions = parse_dimensions(worksheet_path)
dimensions

(1, 1, 7, 4)

該值分別代表行列的最小和最大數(shù)量：

min_col, min_row, max_col, max_row = dimensions

sheet.iter_rows是如何解析數(shù)據(jù)的

最后我們終于到了解析數(shù)據(jù)的環(huán)節(jié)，當(dāng)調(diào)用ReadOnlyWorksheet對象的iter_rows方法時(shí)，到底發(fā)生了什么呢？

iter_rows實(shí)際上調(diào)用的是ReadOnlyWorksheet對象的_cells_by_row函數(shù)，核心代碼為：

def _cells_by_row(self, min_col, min_row, max_col, max_row, values_only=False):
    src = self._get_source()
    parser = WorkSheetParser(src, self._shared_strings,
                             data_only=self.parent.data_only, epoch=self.parent.epoch,
                             date_formats=self.parent._date_formats)
    for idx, row in parser.parse():
        row = self._get_row(row, min_col, max_col, values_only)
        yield row
    src.close()

最終翻譯過來的實(shí)現(xiàn)代碼為：

def from_excel_time(value, epoch):
    SECS_PER_DAY = 24*60*60
    day, fraction = divmod(value, 1)
    diff = datetime.timedelta(
        milliseconds=round(fraction * SECS_PER_DAY * 1000))
    if 0 <= value < 1 and diff.days == 0:
        mins, seconds = divmod(diff.seconds, 60)
        hours, mins = divmod(mins, 60)
        dt = datetime.time(hours, mins, seconds, diff.microseconds)
    else:
        if 0 < value < 60 and epoch == WINDOWS_EPOCH:
            day += 1
        dt = epoch + datetime.timedelta(days=day) + diff
    return dt


def load_data(archive, file):
    src = archive.open(file)
    data = []
    for _, element in iterparse(src):
        tag_name = element.tag
        if tag_name != ROW_TAG:
            continue
        cells = []
        for el in element:
            data_type = el.get('t', 'n')
            coordinate = el.get('r')
            style_id = int(el.get('s', 0))
            if data_type == "inlineStr":
                child = el.find(INLINE_STRING)
                value = None
                if child is not None:
                    data_type = 's'
                    value = get_text_content(child)
            else:
                value = el.findtext(VALUE_TAG, None) or None
                if data_type == 'n':
                    if re.search("[.e]", value, flags=re.I):
                        value = float(value)
                    else:
                        value = int(value)
                    if style_id in date_formats:
                        data_type = 'd'
                        try:
                            value = from_excel_time(value, epoch)
                        except (OverflowError, ValueError):
                            data_type = "e"
                            value = "#VALUE!"
                elif data_type == 's':
                    value = shared_strings[int(value)]
                elif data_type == 'b':
                    value = bool(int(value))
                elif data_type == "str":
                    data_type = "s"
                elif data_type == 'd':
                    value = from_ISO8601(value)
            cells.append(value)
        element.clear()
        data.append(cells)
    src.close()
    return data


data = load_data(archive, name2file['Sheet1'])
data

結(jié)果：

[['商品', '單價(jià)', '數(shù)量', '訂單號', '訂單時(shí)間', '總金額', '名稱管理'],
 ['蘋果', 5.5, 1, 'A', datetime.datetime(2020, 1, 5, 12, 20), 5.5, '哈密瓜'],
 ['哈密瓜', 8, 3, 'B', datetime.time(12, 35), 24, '蘋果'],
 ['芒果', 10, 2, 'C', datetime.datetime(2020, 1, 7, 9, 10), 20, '芒果']]

可以看到已經(jīng)順利的讀取所需要的各種類型的數(shù)據(jù)。

注意：get_text_content在前面的read_strings一節(jié)已經(jīng)實(shí)現(xiàn)。

最終我們終于順利的實(shí)踐了解析Excel的全過程，可以基于以上過程封裝幾個(gè)簡易的類解決該問題。文章來源地址http://www.zghlxwxcb.cn/news/detail-418433.html

到了這里，關(guān)于pandas讀取Excel核心源碼剖析，面向過程仿openpyxl源碼實(shí)現(xiàn)Excel數(shù)據(jù)加載的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！