从工作簿名称Lis中提取工作簿名称

2024-03-28 13:02:10 发布

您现在位置:Python中文网/ 问答频道 /正文

一条Python。目前,我有一个正在通过load\u workbook函数传递的工作簿名称列表。但是,我有一堆依赖于工作簿的if语句。所以我需要解析它们的名字或者找到另一种方法来检查工作簿。这是我的代码:

for x in range(0, len(allbooks)):

    wb = openpyxl.load_workbook(allbooks[x], keep_vba = True)
    print (wb)

    if wb == "Subportfolio 1.xlsm":
        ws = wb.worksheet("Positions")
        if datetime.datetime.today().weekday() == 6: #check if its sunday
            if ws.cells('D1') != "Price on %s" % last_friday: #check to see if date is last friday
                print ("Need to Update Subportfolio")
        elif ws.cells('D1') != "Price on %s" % d: #check to see if date is today
            print ("Need to Update Subportfolio")

    elif wb == "Mock Portfolio - He Yibo 2 (TMT).xlsm":
        ws = wb.worksheet("Positions")
        if datetime.datetime.today().weekday() == 6:
            if ws.cells('E1') != "Price on %s" % last_friday:
                print ("Need to Update Mock Portfolio - He Yibo 2 (TMT)")
        elif ws.cells('E1') != "Price on %s" % d:
            print ("Need to Update Mock Portfolio - He Yibo 2 (TMT)")

    elif wb == "Mock Portfolio - He Yibo 2 (Utilities).xlsm":
        ws = wb.worksheet("Positions")
        if datetime.datetime.today().weekday() == 6:
            if ws.cells('E1') != "Price on %s" % last_friday:
                print ("Need to Update Mock Portfolio - He Yibo 2 (Utilities)")
        elif ws.cells('E1') != "Price on %s" % d:
            print ("Need to Update Mock Portfolio - He Yibo 2 (Utilities)")

Tags: todatetimeifwsonupdateneedmock
2条回答

第一部分并不是很像Python。在Python中,不需要索引来遍历列表。Python中的for在大多数其他语言中充当foreach,因此

for x in range(0, len(allbooks)):

    wb = openpyxl.load_workbook(allbooks[x], keep_vba = True)

可以缩短成

^{pr2}$

另一种改进方法是用dict或namedtuples替换所有elif语句。如果只是细胞发生了变化,你可以用dict很容易地做到这一点

books = {'Subportfolio 1.xlsm': 'D1', 'Mock Portfolio - He Yibo 2 (TMT).xlsm', 'E1'} #etcetera
for book, important_cell in books.items():
    wb = openpyxl.load_workbook(book, keep_vba = True)
    ws = wb.worksheet("Positions")
    message = 'Need to Update %s' % book
    if datetime.datetime.today().weekday() == 6: #check if its sunday
        if ws.cells(important_cell) != "Price on %s" % last_friday: #check to see if date is last friday
            print (message)
    elif ws.cells(important_cell) != "Price on %s" % d: #check to see if date is today
        print (message)

每个工作簿的更多参数

当每个工作簿有更多参数时,例如工作表名称,可以用几种方法来实现

命名成对

如果参数的数目是固定不变的,则namedtuple是一种非常方便的结构:

myworkbook = namedtuple('myworkbook', ['filename', 'sheetname', 'cell'])
allbooks = [myworkbook('filename0', 'sheetname0', 'cell0'),
            myworkbook('filename1', 'sheetname1', 'cell1'),...]
for book in allbooks:
    wb = openpyxl.load_workbook(book.filename, keep_vba = True)
    ws = wb.worksheet(book.sheetname)
    message = 'Need to Update %s' % book.filename
    if datetime.datetime.today().weekday() == 6: #check if its sunday
        if ws.cells(book.cell) != "Price on %s" % last_friday: #check to see if date is last friday
            print (message)
    elif ws.cells(book.cell) != "Price on %s" % d: #check to see if date is today
        print (message)

口是心非

这只不过是大致相同的。它使用dict.get方法,当dict中缺少键时,该方法接受默认参数

default_cell = 'D1'
default_sheet = 'Positions'

books = {'Subportfolio 1.xlsm': {'sheet' = 'other_sheet'}, 'Mock Portfolio - He Yibo 2 (TMT).xlsm': {'cell': 'E1'}} #etcetera
for book, book_info in books.items():
    wb = openpyxl.load_workbook(book, keep_vba = True)
    ws = wb.worksheet(book_info.get('sheet', default_sheet))
    message = 'Need to Update %s' % book
    important_cell = book_info.get('cell', default_cell)
    if datetime.datetime.today().weekday() == 6: #check if its sunday
        if ws.cells(important_cell) != "Price on %s" % last_friday: #check to see if date is last friday
            print (message)
    elif ws.cells(important_cell) != "Price on %s" % d: #check to see if date is today
        print (message)

班级

您可以创建一个MyWorkbookClass来保存每个工作簿的信息,但这样做可能有点过头了。namedtuple是一种具有固定成员的小类

我没有使用过该模块,但我知道的是,这里有一种替代方法:

假设您的工作簿在同一个文件夹中,您可以使用os模块获取文件名列表。如下所示:

import os
import xlrd
os.chdir("c:/mypath/myfolder")
my_filenames = os.listdir()
for filename in my_filenames:
    if filename == 'desired file.xls'
        my_workbook = xlrd.open_workbook(my_filenames[i])

然后,您可以解析my_filenames,并使用xlrd模块按其名称打开所需的工作簿。当然,在这个模块中,索引的给出是不同的。在

相关问题 更多 >