我要评论1、背景介绍
简单的说，就是在excel中有一列h=会有重复的编号，我们相对这个重复的编号，再次进行编号，使其如有重复就加上-1，-2，-3。。。。。以此类推，将重新生成【新编号】放在其旁边列
2、库的安装
| 库 | 用途 | 安装 |
|---|---|---|
| openpyxl | excel读取 | pip install openpyxl -i https://pypi.tuna.tsinghua.edu.cn/simple/ |
| os | 获取路径 | 内置库无需安装 |
3、核心代码
①：编号列的值进行计数
- 遍历excel文件的每一行，对编号列的值进行计数。
- 根据计数值生成新的格式化字符串编号-计数值，并更新到结果编号列。
- 支持单个文件处理和批量文件处理。
for row_idx in range(2, ws.max_row + 1):
# get the 序号 value
序号_value = ws.cell(row=row_idx, column=序号_index).value
# skip if 序号 is none or empty
if 序号_value is none or str(序号_value).strip() == '':
continue
# convert to string to handle any numeric values
序号_str = str(序号_value)
# initialize count if this is the first occurrence
if 序号_str not in 序号_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
def process_subaccount_numbers(file_path, output_path=none):
# load the workbook
print(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 columns
if '编号' not in headers or '结果编号' not in 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 in range(2, ws.max_row + 1):
# get the 序号 value
序号_value = ws.cell(row=row_idx, column=序号_index).value
# skip if 序号 is none or empty
if 序号_value is none or str(序号_value).strip() == '':
continue
# convert to string to handle any numeric values
序号_str = str(序号_value)
# initialize count if this is the first occurrence
if 序号_str not in 序号_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 path
if output_path is none:
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 file
print(f"saving processed file to: {output_path}")
wb.save(output_path)
print("processing completed successfully!")
return output_path
def main():
# 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')]
if not excel_files:
print("no excel files found in the data source directory.")
return
# process each excel file
for file_name in excel_files:
file_path = os.path.join(data_dir, file_name)
process_subaccount_numbers(file_path)
if __name__ == "__main__":
main()效果如下
到此这篇关于python excel实现自动添加编号的文章就介绍到这了,更多相关python excel添加编号内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论