批量从excel指定单元格获取并整理数据

获取excel内容函数

def arrange_yangbenguanli(path):
    # 打开Excel文件
    workbook = openpyxl.load_workbook(path)

    sheet = workbook['病虫害']

    yangdibianhao = sheet['B2'].value
    yangbenbianhao = sheet['H2'].value

    yuwuzhongguanxi = sheet['B3'].value

    caijishijian = sheet['B4'].value
    caijididian = sheet['B5'].value
    jigndu = sheet['C6'].value
    weidu = sheet['G6'].value
    haiba = sheet['I6'].value

    zhongwenming = sheet['C7'].value
    yingwenchangyongming = sheet['H7'].value
    xueming = sheet['C8'].value
    tuming = sheet['H8'].value

    ruqinwuzhongfenlei = sheet['B9'].value
    ruqinwuzhongtezheng = sheet['B10'].value

    jizhuzhiwu = sheet['B11'].value
    pinzhong = sheet['F11'].value

    jiandingren = sheet['B12'].value
    jiandingriqi = sheet['H12'].value
    caijiren = sheet['B13'].value
    jiluren = sheet['H13'].value

    cunfangqingkuang = sheet['B14'].value


    col_names = ['样地编号', '样本编号', '与物种关系', '采集时间',  '采集地点', '经度', '纬度', '海拔', 
                 '中文名称', '英文常用名', '学名', '土名', '入侵物种分类', '入侵物种特征', '寄主植物', '品种', '鉴定人',
                 '鉴定日期', '采集人', '记录人', '存放情况']

    temp_data = [yangdibianhao, yangbenbianhao, yuwuzhongguanxi, caijishijian, caijididian, jigndu, 
                 weidu, haiba, zhongwenming, yingwenchangyongming, xueming, tuming, ruqinwuzhongfenlei, 
                 ruqinwuzhongtezheng, jizhuzhiwu, pinzhong, jiandingren, jiandingriqi, caijiren, jiluren, 
                 cunfangqingkuang]

    df = pd.DataFrame(list(zip(col_names, temp_data)))
    df = df.transpose()
    df.columns = df.iloc[0,]
    df = df.drop(0, axis=0)

    return df

利用循环遍历某个目录

# 指定目标目录的路径
target_directory = '/Users/XXX/Desktop/侵物种数据/'

df = pd.DataFrame(columns=['样地编号', '样本编号', '与物种关系', '采集时间',  '采集地点', '经度', '纬度', '海拔', 
             '中文名称', '英文常用名', '学名', '土名', '入侵物种分类', '入侵物种特征', '寄主植物', '品种', '鉴定人',
             '鉴定日期', '采集人', '记录人', '存放情况'])

# 遍历目录中的文件和子目录
for root, dirs, files in os.walk(target_directory):
    print(f'当前目录: {root}')

    # 遍历子目录
    for dir_name in dirs:
        print(f'子目录: {os.path.join(root, dir_name)}')

    # 遍历文件
    for file_name in files:
        print(f'文件: {os.path.join(root, file_name)}')
        
        data = arrange_yangbenguanli(os.path.join(root, file_name))
        
        df = df.append(data)

        
df.to_csv("样本管理汇总.csv", encoding='GBK', index=False)