情况
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