如何使用Apache POI动态跳过或删除Excel文件末尾指定行

Hey Jeet, sounds like you're stuck on dynamically skipping those 3 trailing rows when converting Excel to text—let's figure this out together!

The core issue here is that we can't rely on fixed row numbers, so we need to identify those 3 rows by their unique content or format characteristics instead. Below are practical solutions based on common scenarios:

1. Filter by Row Content Keywords #

If those 3 rows have obvious text markers (like "Total", "Summary", or "End"—adjust to match your actual data), we can use string matching to exclude them.

Assuming you're using pandas for Excel processing, here's a sample code snippet:

import pandas as pd

# Load your Excel file
df = pd.read_excel("your_source_file.xlsx")

# Define the keywords that mark the rows to skip
skip_keywords = ["Total", "Summary", "Final Notes"]

# Keep only rows that don't contain any of these keywords in any cell
filtered_df = df[~df.apply(
    lambda row: any(keyword in str(cell) for keyword in skip_keywords for cell in row),
    axis=1
)]

# Save to text file (tab-separated here, adjust delimiter as needed)
filtered_df.to_csv("output_text_file.txt", sep="\t", index=False)

2. Filter by Format/Style #

If those rows have distinct formatting (like bold text, colored backgrounds), we can use a library like openpyxl to check cell styles:

from openpyxl import load_workbook
import pandas as pd

wb = load_workbook("your_source_file.xlsx", data_only=True)
ws = wb.active

valid_rows = []
for row_num, row in enumerate(ws.iter_rows(values_only=True), start=1):
    # Check if the first cell of the row is NOT bold (adjust to your format marker)
    first_cell = ws.cell(row=row_num, column=1)
    if not first_cell.font.bold:
        valid_rows.append(row)

# Convert to DataFrame (assuming first row is header)
df = pd.DataFrame(valid_rows[1:], columns=valid_rows[0])
df.to_csv("output_text_file.txt", sep="\t", index=False)

3. Filter by Relative Position to Valid Data #

If those 3 rows always come right after the last valid data row (e.g., valid rows have non-empty values in a key column), we can find the last valid row index and truncate the DataFrame there:

import pandas as pd

df = pd.read_excel("your_source_file.xlsx")

# Assume valid rows have non-null values in the "Record ID" column (adjust to your key column)
last_valid_index = df[df["Record ID"].notna()].index[-1]

# Keep all rows up to and including the last valid one
filtered_df = df.loc[:last_valid_index]

filtered_df.to_csv("output_text_file.txt", sep="\t", index=False)

Quick Tip #

If you can share the specific content or format details from your marked Excel screenshot, we can tweak these solutions to be even more precise for your case!

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