python_批量处理excel文件

情况

 pip install openpyxl
 pip install xlrd

数据

 Excel文件的格式为xls和xlsx，pandas读取excel文件需要安装依赖库xlrd和openpyxl。
 !注意：当xlrd>=2.0时，只支持xls格式，不再支持xlsx。

数据代码

import pandas as pd
import os
import json

if __name__ =="__main__":
    excel_dir_path = r"D:\tic_info"
    excel_file_nm = r"test.xlsx"
	##读取excel
    excel_file_path = os.path.join(excel_dir_path,excel_file_nm)
    excel_data = pd.read_excel(io = excel_file_path,sheet_name=0,header=None)
    row_len = excel_data.shape[0]
    col_len = excel_data.shape[1]
    ##列名设置
    excel_data.columns = excel_data.iloc[row_len-2,:].tolist()
    #移除不需要的数据
    excel_data = excel_data.drop(labels=[row_len-1,row_len-2], axis=0)
    #
    print(excel_data.iloc[:,15:])
    ##数据类型转换
    excel_data["班级人数"] = excel_data["班级人数"].astype(int)
    # 获取批次的数据
    batch_nm = excel_data["班级地址"][0].split("/")[1]
    ## 对各个行进行求和
    data = excel_data.iloc[:, 15:].sum(axis=0)
    f_obj= {batch_nm:data.to_dict()}
    #print({batch_nm:data.to_dict()})
    json_file =r"D:\tic_info\static_js.json"
    with open(file=json_file, mode="a", encoding="utf8") as f_write:
        json.dump(f_obj, f_write, indent=4,ensure_ascii=False)

参考

 Python--pandas读取excel https://zhuanlan.zhihu.com/p/362709226