python - 加快 openpyxl 读取速度

Question

我正在开发一个相当大的程序，它再次从一个相当大的 Excel 电子表格中获取数据。由于一些奇怪的原因，加载这个大的 Excel 文件需要很长时间，我希望能以某种方式加快速度。我做了自己的研究并尝试了 read_only，但这不允许我读取任何单元格(至少如下所示的方式)。非常欢迎任何建议。

def LoadSheet(Filename,worksheet_nr):
    import time as t
    st = t.clock()
    wb = xl.load_workbook(str(Filename))#workbook, loading workbook
    Sheets = wb.get_sheet_names()#Worksheets, shows all sheets in the workbook loaded
    ActiveSheet = wb.get_sheet_by_name(Sheets[worksheet_nr])#Selects the first sheet
    print round(t.clock() - st,3), "seconds of loading time."
    print "Sheet is loaded and reading..."
    return ActiveSheet



def CountRC_nr(ActiveSheet, StartR,StartC):
    R = StartR#Selected starting Row number
    C = StartC#Selected starting Column number
    Val = None #Value in cell (Right now, none is selected, thus zero value)
    while (Val != "None"):
        Val = str(ActiveSheet.cell(row=R,column=C).value)
        R += 1

    NumberRows = R - StartR - 1
    Val = None
    R=StartR

    while (Val != "None"):
        Val = str(ActiveSheet.cell(row=R,column=C).value)
        C += 1
    NumberColumns = C - StartC - 1

    return NumberRows, NumberColumns

由于有些人要求查看我如何使用 read_only，这里是修改后的代码:

def LoadSheet(Filename,worksheet_nr):
    import time as t
    st = t.clock()
    wb = xl.load_workbook(str(Filename), read_only = True)#workbook, loading workbook
    Sheets = wb.get_sheet_names()#Worksheets, shows all sheets in the workbook loaded
    ActiveSheet = wb.get_sheet_by_name(Sheets[worksheet_nr])#Selects the first sheet
    print round(t.clock() - st,3), "seconds of loading time."
    print "Sheet is loaded and reading..."
    return ActiveSheet



def CountRC_nr(ActiveSheet, StartR,StartC):
    R = StartR#Selected starting Row number
    C = StartC#Selected starting Column number
    Val = None #Value in cell (Right now, none is selected, thus zero value)
    while (Val != "None"):
        Val = str(ActiveSheet.cell(row=R,column=C).value)
        R += 1

    NumberRows = R - StartR - 1
    Val = None
    R=StartR

    while (Val != "None"):
        Val = str(ActiveSheet.cell(row=R,column=C).value)
        C += 1
    NumberColumns = C - StartC - 1

    return NumberRows, NumberColumns

Answer 1

正如我已经说过的，您正在针对 API 进行工作。如果工作表的尺寸已知，那么您可以访问它们，否则您可以构建自己的计数器，但没有什么意义。

只读模式对于单个查找来说非常快，但是由于每个查找都涉及将行中的所有单元格解析到感兴趣的单元格，因此对于许多单元格来说效率非常低，例如您的代码正在使用。

wb = load_workbook(filename, read_only=True)
for ws in wb:
    print(ws.dimensions)
    for row in ws.rows:
         for cell in row:
             print(cell.value)  # do whatever you want with the cell

您可以使用(行中单元格的cell.value)轻松将行转换为NumPy数组，但Pandas库也可以将工作表直接读入数据帧。