如何使用openpyxl获取Excel文件中指定行的最大列数？

To find the highest column number containing data in a specific row (like row 3) of an Excel file, the openpyxl library (a go-to tool for .xlsx files in Python) makes this straightforward. Here’s a practical, step-by-step breakdown:

Step 1: Install Openpyxl (if you haven’t already) #

First, get the library set up:

pip install openpyxl

Step 2: Implement the Solution #

Here’s a complete code example to fetch the maximum column number for row 3:

from openpyxl import load_workbook

# Load your Excel file
workbook = load_workbook('your_file.xlsx')

# Pick your target sheet (use the active sheet or specify by name)
sheet = workbook.active  # Or use sheet = workbook['Sheet1'] if you know the sheet name

# Access the 3rd row (note: openpyxl uses 1-based indexing for rows/columns)
target_row = sheet[3]

# Iterate through cells to find the last non-empty column
max_col = 0
for cell in target_row:
    if cell.value is not None:
        max_col = cell.column
    # Optional: If you know there are no gaps after the last non-empty cell, uncomment below
    # elif max_col > 0:
    #     break

print(f"Maximum column number in row 3: {max_col}")

Quick Notes: #

• 1-based indexing: Unlike some other tools, openpyxl counts rows and columns starting at 1, so row 3 is directly sheet[3].
• Concise alternative: If you prefer shorter code, use a generator expression to get the max column in one line:

  max_col = max(cell.column for cell in target_row if cell.value is not None) if any(cell.value for cell in target_row) else 0
  

Using Pandas (Alternative Approach) #

If you’re already working with Pandas, here’s how to do it:

import pandas as pd

# Load the Excel file into a DataFrame
df = pd.read_excel('your_file.xlsx')

# Get the 3rd row (Pandas uses 0-based indexing, so row 3 is index 2)
target_row = df.iloc[2]

# Find the last non-empty column and convert to 1-based number
last_col_label = target_row.last_valid_index()
if last_col_label is not None:
    max_col_num = df.columns.get_loc(last_col_label) + 1
    print(f"Maximum column number in row 3: {max_col_num}")
else:
    print("Row 3 has no non-empty cells.")

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