python 按照excel表头字符串读取单元格数据

原理

先获取表格第一行表头字符串所在的列下标，循环行数时，可以得到行，进而定位到指定的单元格。

# -*- ecoding: utf-8 -*-
# @ModuleName: test001
# @Function:
# @Author: darling
# @Time: 2022-04-12 15:52
import datetime

import openpyxl


def get_data_batch(columnHeaders):
    file_name = './excel.xlsx'
    workbook = openpyxl.load_workbook(file_name)
    # 获取当前活动的sheet页对象
    sheet = workbook.active
    # 获取指定的sheet页对象
    sheet2 = workbook["Sheet1"]
    # df=sheet2.
    # 按行保存单元格值((1,2,3),(4,5,6),(7,8,9))
    valuesByRow = tuple(sheet2.values)
    # 第一行，也就是表头
    firstRow = valuesByRow[0]
    # 空字典，收集指定表头对应的下标
    cellValueList = {}
    for columnHeader in columnHeaders:
        for i, v in enumerate(firstRow):
            if v == columnHeader:
                cellValueList[columnHeader] = i
                break
    # 循环表格进行业务处理
    for row in valuesByRow[1:]:
        inventoryId = row[cellValueList.get('库存ID')]
        toLot = row[cellValueList.get('到批号')]
        toManufactureDate = row[cellValueList.get('到生产日期')]
        if inventoryId is None or inventoryId == '':
            continue
        # 处理时间类型，如果是datetime类型，则进行转类型处理
        if isinstance(toManufactureDate, datetime.datetime):
            toManufactureDate = str(toManufactureDate.date())


if __name__ == "__main__":
    # 获取数据
    get_data_batch(['库存ID','到批号', '到生产日期'])

以上是基于下面的方法进行改造，共勉之

# -*- coding: utf-8 -*-
"""
第一种方法
"""
from openpyxl import load_workbook

wb = load_workbook(filename='./excel.xlsx', data_only=True)
sheet = wb[wb.sheetnames[0]]
minRow = sheet.min_row  # 最小行
maxRow = sheet.max_row  # 最大行
minCol = sheet.min_column  # 最小列
maxCol = sheet.max_column  # 最大列


def read_value_by_column_header(sheet, columnHeader):
    # 读取第一行数据
    firstRow = tuple(sheet.rows)[0]
    # 创建空列表储存数据
    cellValueList = []
    # 判断第几列的表头是输入的表头
    for i, cell in enumerate(firstRow):
        if cell.value == columnHeader:
            # tuple(sheet.columns)返回一个元组包含按列排列的Cell对象，
            # 我们只要指定表头的那一列
            columns = tuple(sheet.columns)[i]
            # 取这一列第二个到最后一个cell对象，
            # 将其value属性（也就是单元格值）append到空列表中
            for cell in columns[1:]:
                cellValueList.append(cell.value)
            break
    return cellValueList

# -*- coding: utf-8 -*-
"""
第二种方法
"""
from openpyxl import load_workbook

wb = load_workbook(filename='./excel.xlsx', data_only=True)
sheet = wb[wb.sheetnames[0]]


def read_value_by_column_header(sheet, columnHeader):
    # 按行保存单元格值((1,2,3),(4,5,6),(7,8,9))
    valuesByRow = tuple(sheet.values)
    # 第一行，也就是表头
    firstRow = valuesByRow[0]
    # 空列表
    cellValueList = []
    for i, v in enumerate(firstRow):
        if v == columnHeader:
            for row in valuesByRow[1:]:
                cellValueList.append(row[i])
            break
    return cellValueList