for row_idx inrange(2, ws.max_row +1):# Get the 序号 value
序号_value = ws.cell(row=row_idx, column=序号_index).value
# Skip if 序号 is None or emptyif 序号_value isNoneorstr(序号_value).strip()=='':continue# Convert to string to handle any numeric values
序号_str =str(序号_value)# Initialize count if this is the first occurrenceif 序号_str notin 序号_counts:
序号_counts[序号_str]=0# Increment the count
序号_counts[序号_str]+=1# Create the new format: "序号-count"
new_子账号编号 =f"{序号_str}-{序号_counts[序号_str]}"# Update the 子账号编号 cell
ws.cell(row=row_idx, column=子账号编号_index).value = new_子账号编号
4、完整代码
import os
import openpyxl
defprocess_subaccount_numbers(file_path, output_path=None):# Load the workbookprint(f"Reading file: {file_path}")
wb = openpyxl.load_workbook(file_path)
ws = wb.active
# Get the headers
headers =[cell.value for cell in ws[1]]# Find the indices of the required columnsif'编号'notin headers or'结果编号'notin headers:print("Error: Required columns '序号' or '结果编号' not found in the Excel file.")return
序号_index = headers.index('编号')+1# +1 because openpyxl is 1-indexed
子账号编号_index = headers.index('结果编号')+1# Create a dictionary to track occurrences of each 序号
序号_counts ={}# Process each row (starting from row 2, skipping the header)for row_idx inrange(2, ws.max_row +1):# Get the 序号 value
序号_value = ws.cell(row=row_idx, column=序号_index).value
# Skip if 序号 is None or emptyif 序号_value isNoneorstr(序号_value).strip()=='':continue# Convert to string to handle any numeric values
序号_str =str(序号_value)# Initialize count if this is the first occurrenceif 序号_str notin 序号_counts:
序号_counts[序号_str]=0# Increment the count
序号_counts[序号_str]+=1# Create the new format: "序号-count"
new_子账号编号 =f"{序号_str}-{序号_counts[序号_str]}"# Update the 子账号编号 cell
ws.cell(row=row_idx, column=子账号编号_index).value = new_子账号编号
# Determine the output pathif output_path isNone:
file_name, file_ext = os.path.splitext(file_path)
output_path =f"{file_name}_processed{file_ext}"# Save the modified workbook to a new Excel fileprint(f"Saving processed file to: {output_path}")
wb.save(output_path)print("Processing completed successfully!")return output_path
defmain():# Get the data source directory
data_dir ='./数据源/'# Find all Excel files in the directory
excel_files =[f for f in os.listdir(data_dir)if f.endswith('.xlsx')or f.endswith('.xls')]ifnot excel_files:print("No Excel files found in the data source directory.")return# Process each Excel filefor file_name in excel_files:
file_path = os.path.join(data_dir, file_name)
process_subaccount_numbers(file_path)if __name__ =="__main__":
main()