pandas读取Excel核心源码剖析，面向过程仿openpyxl源码实现Excel数据加载

今天我们将研究pandas如何使用openpyxl引擎读取xlsx格式的Excel的数据，并考虑以面向过程的形式简单的自己实现一下。

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

• pandas：1.5.2
• openpyxl：3.0.10

今天所有的测试全部基于以下文件：

这里我使用pycharm工具对以下代码进行debug跟踪：

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

核心就是两行代码：

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

我们研究一下这两行代码所做的事：

内容有很多，我们挑一些有价值的内容进行解析。默认传递的参数下，会调用inspect_excel_format函数获取文件的扩展名。

直接通过文件名获取的扩展名有可能不真实，我们可以查看pandas.io.excel._base.inspect_excel_format的源码，研究pandas判断Excel真实扩展名的实现。

个人在阅读源码后，整理出如下可以直接使用的方法：

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"

获取到扩展名之后，get_default_engine将获取默认的处理引擎，定义如下：

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

self._engines[engine]会找到对应的处理类来处理当前文件。

而ExcelFile有个类定义：

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

于是就可以使用OpenpyxlReader来读取对应的Excel文件：

self._reader = OpenpyxlReader(self._io)

首先判断是否安装openpyxl，然后调用父类BaseExcelReader的构造方法，其中核心代码为：

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

而OpenpyxlReader的load_workbook实现为：

from openpyxl import load_workbook

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

可以确定pandas再调用openpyxl时，固定了这些参数。

跟踪可以看到内部调用了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引擎读取出指定表格的数据，我们后面再细究。

TextParser用于解析结果，构造函数调用TextFileReader的_make_engine处理结果数据，内部使用python引擎对应的PythonParser进行解析处理，PythonParser的构造方法中，核心代码为：

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

该代码根据header参数读取data的前N行作为列，每次调用self._next_line()读取，会改变self.pos的值即当前位置，并当前读取到的行存入self.buf。但是最终该函数会清空self.buf的值。

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

这行代码的实现会两次调用self._next_line()读取数据，这两行的数据会存入self.buf中。

parser.read的核心代码为：

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

self._engine.read调用_get_lines函数将剩余的数据都读入self.buf中并返回，最终得到处理表头以外的所有行数据content。

然后调用self._rows_to_cols(content)将所有的行数据转换为列数据：

alldata = self._rows_to_cols(content)

这行代码内部的核心实现为：

import pandas._libs.lib as lib

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

不过lib.to_object_array的底层采用其他语言实现，只能直接查看。

然后_exclude_implicit_index将列数据转换为字典，核心代码为：

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

最终经过一些转换后得到最终结果。

前面在OpenpyxlReader的构造函数中，通过openpyxl的load_workbook函数加载了Excel文件得到self.book。

self.get_sheet_by_name(asheetname)的实现是：

return self.book[asheetname]

self.get_sheet_by_index(asheetname)的实现是：

return self.book.worksheets[index]

可以翻译为：

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

get_sheet_data的源码：

内部核心获取数据的代码为sheet.rows，该属性是调用了openpyxl.worksheet.worksheet.Worksheet的iter_rows方法获取数据。

pandas会使用_convert_cell方法对openpyxl获取的单元格提取数值并转换，convert_float参数默认为True，作用是当一个数值可以转为整数时就是整数，并不是所有数值都转为浮点数。

然后while循环实现剔除空行。

pandas读取Excel的核心代码，我们可以总结为如下形式：

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)

当然pandas多余的处理代码比这些更复杂。

我们也可以进一步简化为：

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源码读取部分的源码相比pandas处理部分更加复杂，下面我主要对核心代码进行翻译。

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压缩包中读取需要的数据。

在处理之前，我们定义一些需要用到的常量：

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

该函数用于读取各类xml在压缩包中的路径，openpyxl使用特殊的自定义类来解析xml，我们则使用基本语法读取需要的数据：

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'}]}

该方法用于读取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的源码在这个部分使用defusedxml解析xml，如果我们使用etree解析全部加载到内存的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("//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可以简化代码。

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

['商品', '单价', '数量', '订单号', '订单时间', '总金额', '名称管理', '苹果', 'A', '哈密瓜', 'B', '芒果', 'C']

这部分的核心代码有：

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解析的数据有点多，下面我尽量只提取需要的数据：

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)

获取活跃表格角标：

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'}

用于读取文档的一些属性信息：

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格式的时间字符串有很多种形式，上述代码直接使用openpyxl现成的实现，将2023-03-08T09:07:00Z等形式的时间字符串解析为日期时间对象。

核心代码是使用如下正则进行匹配：

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)

这个函数除了读取字体、对齐、边框、填充等各种样式数据以外，还会读取出哪些列是日期格式的列，用于后续将数值类型的列解析为时间。

对于纯数据读取而言，样式数据并不是我们需要的数据，这里只演示字体列表的读取：

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})

读取出哪些列是日期格式的列，体现在openpyxl.styles.stylesheet.Stylesheet类构造函数的self._normalise_numbers()函数上。

根据custom_formats函数的实现，解析出所有的自定义格式：

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;@'}

获取日期列的实现：

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

这里我们已经设置了只读形式，而且我们不考虑透视图类型的工作表，那么核心代码为：

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)

所需要的数据都封装到ReadOnlyWorksheet对象中。

其实所需要的数据只有表名和对应的路径，解析代码如下：

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的构造函数中，self._get_size()函数会解析整个表的大小，面向过程的实现为：

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)

该值分别代表行列的最小和最大数量：

min_col, min_row, max_col, max_row = dimensions

最后我们终于到了解析数据的环节，当调用ReadOnlyWorksheet对象的iter_rows方法时，到底发生了什么呢？

iter_rows实际上调用的是ReadOnlyWorksheet对象的_cells_by_row函数，核心代码为：

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()

最终翻译过来的实现代码为：

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

结果：

[['商品', '单价', '数量', '订单号', '订单时间', '总金额', '名称管理'],
 ['苹果', 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, '芒果']]

可以看到已经顺利的读取所需要的各种类型的数据。

注意：get_text_content在前面的read_strings一节已经实现。

最终我们终于顺利的实践了解析Excel的全过程，可以基于以上过程封装几个简易的类解决该问题。