如何使用Openpyxl创建包含工作表名与特定单元格值的DataFrame

代码优化与补充 #

首先修正现有初始化代码的冗余重复赋值问题，同时优化工作簿加载参数：

# 导入依赖
import pandas as pd
from openpyxl import load_workbook
from pathlib import Path

# 配置文件路径（原代码重复赋值冗余，已简化）
src_file = Path.cwd() / 'lm_balance.xlsx'
# 加载工作簿时开启data_only=True，直接读取单元格的实际计算值，而非公式本身
wb = load_workbook(filename=src_file, data_only=True)

注意：加载时必须开启data_only=True参数，否则会读入Excel单元格内的公式字符串，而非公式计算出的最终余额数值。

核心提取逻辑 #

按照需求，每个工作表的目标值固定为「最后一行的G列」，无需逐行扫描文本匹配，直接定位坐标即可，整体效率远高于全表遍历：

# 初始化结果存储容器
name_res = []
balance_res = []

# 逐个遍历所有工作表
for sheet_name in wb.sheetnames:
    # 获取当前工作表对象
    current_ws = wb[sheet_name]
    # 获取当前工作表的最后一行行号（openpyxl行号从1开始计数）
    last_row_num = current_ws.max_row
    # G列对应第7列，直接定位最后一行G列的单元格值
    target_balance = current_ws.cell(row=last_row_num, column=7).value
    
    # 跳过无有效值的工作表
    if target_balance is None:
        continue
    
    # 兼容带千分位分隔符的字符串格式（如"45,408.83"）
    target_balance = float(str(target_balance).replace(',', ''))
    
    # 存入结果列表
    name_res.append(sheet_name)
    balance_res.append(target_balance)

# 组装为要求的两列DataFrame
balance_df = pd.DataFrame({
    'name': name_res,
    'balance': balance_res
})

常见异常场景处理 #

• 不需要重复调用pandas的Excel读取接口打开文件，仅通过当前已加载的openpyxl工作簿对象即可完成所有值提取，避免重复IO开销。
• 如果工作表末尾存在空白行导致max_row定位偏差，可通过倒序查找从最后一行往上扫描，取第一个非空的G列单元格作为目标值，对应代码片段可替换为：

  # 倒序查找跳过末尾空白行
  for row_idx in range(current_ws.max_row, 1, -1):
      cell_val = current_ws.cell(row=row_idx, column=7).value
      if cell_val is not None:
          target_balance = cell_val
          break
  

内容的提问来源于stack exchange，提问作者mahai217