核心代码
# 导入pandas库
import pandas as pd
# 导入正则表达式包
import re# 指定Excel文件的路径，这个data.xlsx表为原始表，表内有40个sheet子表
file_path = 'data.xlsx'
# 读取各个子表
allDf = pd.read_excel(file_path, sheet_name=None)
# 获取各个子工作表表名
workSheet = allDf.keys()
# 定义workSheetList ，存放所有子工作表表名
workSheetList = list(workSheet)
# 直接打印
# print(workSheetList)# 创建一个空list，组织
organizationList = []
# 总人数
countList = []
# 未活跃人数
inactiveCountList = []
# 平均周活跃度
userActivityList = []
# 总采纳行数
acceptanceLineNumberList = []
# 总推荐行数
recommendationsLineNumberList = []
# 平均采纳率
acceptanceRateList = []
# 总采纳次数
numberOfAcceptancesList = []
# 总推荐次数
numberOfRecommendationsList = []
# 循环workSheetList 列表，去掉表名以sheet开头的子表名，即无用的sheet表
for j in workSheetList:if j.startswith('Sheet'):workSheetList.remove(j)else:# 使用正则表达式，去掉空stringj = re.sub(r"\s+", "", j)organizationList.append(j)# 读取Excel文件中的子工作表sheetDateFrom = pd.read_excel(file_path, j)# 获取每个工作表的行数rows = len(sheetDateFrom.index)countList.append(rows)# 获取特定列的内容column_8 = sheetDateFrom.iloc[:, 7]# 统计是否获取列，包含“否”的个数count_no = (column_8 == '否').sum()inactiveCountList.append(count_no)activityNum = rows - count_nouserActivity = activityNum / rows# 去掉小数点后面的数，例如50%userActivity = '{:.0%}'.format(userActivity)userActivityList.append(userActivity)# 获取采纳行数的内容,子表第5列的数据acceptanceLineSum = sheetDateFrom.iloc[:, 4].sum()acceptanceLineNumberList.append(acceptanceLineSum)# 获取推荐行数的内容,子表第6列的数据recommendationsLineSum = sheetDateFrom.iloc[:, 5].sum()recommendationsLineNumberList.append(recommendationsLineSum)# 获取采纳次数的内容,子表第11列的数据numberOfAcceptances = sheetDateFrom.iloc[:, 10].sum()numberOfAcceptancesList.append(numberOfAcceptances)# 获取推荐次数的内容,子表第12列的数据numberOfRecommend = sheetDateFrom.iloc[:, 11].sum()numberOfRecommendationsList.append(numberOfRecommend)# 获取采纳率/行的内容，解决除数为0的异常情况if numberOfRecommend == 0:acceptanceRateSum = 0else:acceptanceRateSum = numberOfAcceptances/numberOfRecommendacceptanceRateSum = '{:.0%}'.format(acceptanceRateSum)acceptanceRateList.append(acceptanceRateSum)# 创建一个DataFrame对象，并将组织名称、人数和未活跃人数分别赋值给每一列
data = {'组织': organizationList,'人数': countList,'未活跃人数': inactiveCountList,'周活跃度': userActivityList,'采纳率/行': acceptanceRateList,'总采纳行数': acceptanceLineNumberList,'总推荐行数': recommendationsLineNumberList,'总采纳次数': numberOfAcceptancesList,'总推荐次数': numberOfRecommendationsList}
resultDf = pd.DataFrame(data)
# 显示DataFrame
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.max_colwidth', 1000)
# 解决在控制台输出的列不完整，有...
pd.set_option('expand_frame_repr', False)
print(resultDf)
# 输出表
resultDf.to_excel('output.xlsx', index=False)
输出的结果
组织 人数 未活跃人数 周活跃度 采纳率/行 总采纳行数 总推荐行数 总采纳次数 总推荐次数
0 A部 1 1 0% 0% 0 0 0 0
1 B部 17 9 47% 44% 660 1385 341 777
2 C部 8 4 50% 38% 218 551 137 361
3 D部 8 2 75% 26% 114 444 70 266E
4 E部 8 5 38% 21% 69 468 58 282
5 F部 2 2 0% 0% 0 0 0 0
6 G部 51 27 47% 23% 1362 5820 676 2955
7 H部 13 4 69% 38% 460 1425 261 685
8 I部 25 9 64% 20% 297 1844 192 963
9 J部 2 2 0% 0% 0 0 0 0
10 K部 3 3 0% 0% 0 0 0 0
11 L部 2 2 0% 0% 0 0 0 0
12 M部 63 36 43% 19% 534 3447 219 1150
13 N部 63 26 59% 34% 1659 5354 913 2705
14 总计 267 133 50% 28% 5373 20738 2867 10144进程已结束，退出代码为 0
推荐的插件
Chinese(Simplified)Language·Pack / 中文语言包
特别说明
参考了pycharm 专业版激活码(2024.5亲测可用)最新https://blog.csdn.net/qq_41717536/article/details/136171678
UX394X3HLT-eyJsaWNlbnNlSWQiOiJVWDM5NFgzSExUIiwibGljZW5zZWVOYW1lIjoiSG9uZ2lrIFVuaXZlcnNpdHntmY3snbXrjIDtlZnqtZAiLCJsaWNlbnNlZVR5cGUiOiJDTEFTU1JPT00iLCJhc3NpZ25lZU5hbWUiOiLkvJfliJvkupEg5bel5L2c5a6kIiwiYXNzaWduZWVFbWFpbCI6ImhhbmF6YXdhbWl0b0BnbWFpbC5jb20iLCJsaWNlbnNlUmVzdHJpY3Rpb24iOiJGb3IgZWR1Y2F0aW9uYWwgdXNlIG9ubHkiLCJjaGVja0NvbmN1cnJlbnRVc2UiOmZhbHNlLCJwcm9kdWN0cyI6W3siY29kZSI6IkdPIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJSUzAiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IkRNIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJDTCIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiUlNVIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJSU0MiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiUEMiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IkRTIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJSRCIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiUkMiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IlJTRiIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjp0cnVlfSx7ImNvZGUiOiJSTSIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiSUkiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IkRQTiIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiREIiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6ZmFsc2V9LHsiY29kZSI6IkRDIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJQUyIsInBhaWRVcFRvIjoiMjAyNC0xMi0xMyIsImV4dGVuZGVkIjpmYWxzZX0seyJjb2RlIjoiUlNWIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOnRydWV9LHsiY29kZSI6IldTIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOmZhbHNlfSx7ImNvZGUiOiJQU0kiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiUENXTVAiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiUlMiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiRFAiLCJwYWlkVXBUbyI6IjIwMjQtMTItMTMiLCJleHRlbmRlZCI6dHJ1ZX0seyJjb2RlIjoiUERCIiwicGFpZFVwVG8iOiIyMDI0LTEyLTEzIiwiZXh0ZW5kZWQiOnRydWV9XSwibWV0YWRhdGEiOiIwMTIwMjMxMjI4TFBBQTAwNDAwOCIsImhhc2giOiI1MjY4NzM4Ny8yNTMxODE0MTotMTUwNDg0MzkwMSIsImdyYWNlUGVyaW9kRGF5cyI6NywiYXV0b1Byb2xvbmdhdGVkIjpmYWxzZSwiaXNBdXRvUHJvbG9uZ2F0ZWQiOmZhbHNlLCJ0cmlhbCI6ZmFsc2UsImFpQWxsb3dlZCI6dHJ1ZX0=-YqDHrEIEaf/x1JqIdTI64AYA1IpRoYiqoZ/1YDnfpEqSFNJIC4er7K1hjUm9tFslnY2XoNRs04JSUG8CgNkTgIKA4xLyxGBufJYyHv26UKQmyf1nb1pM9XATb3pWSQ3h6o8/4x3jacVk3zbAuXt3uV6eEj2bCZvhGpATFmK1JVsSor+XgPr5aYePCtyymiyPOq33ghW5onzSI5LsQR5motHvLgWmjf0Mkutys3SmWt13YVcIe5yCzhlTNCZw++CAuRh2Fx/JXZhRt+kUqW2yLbkIo3kNEg2I31H8qya+RDJ09Qz7DsDkrIgODqX4Wbd2fy1C7Q1CcjjksvjhswnpNA==-MIIETDCCAjSgAwIBAgIBDzANBgkqhkiG9w0BAQsFADAYMRYwFAYDVQQDDA1KZXRQcm9maWxlIENBMB4XDTIyMTAxMDE2MDU0NFoXDTI0MTAxMTE2MDU0NFowHzEdMBsGA1UEAwwUcHJvZDJ5LWZyb20tMjAyMjEwMTAwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQC/W3uCpU5M2y48rUR/3fFR6y4xj1nOm3rIuGp2brELVGzdgK2BezjnDXpAxVDw5657hBkAUMoyByiDs2MgmVi9IcqdAwpk988/Daaajq9xuU1of59jH9eQ9c3BmsEtdA4boN3VpenYKATwmpKYkJKVc07ZKoXL6kSyZuF7Jq7HoQZcclChbF75QJPGbri3cw9vDk/e46kuzfwpGftvl6+vKibpInO6Dv0ocwImDbOutyZC7E+BwpEm1TJZW4XovMBegHhWC04cJvpH1u98xoR94ichw0jKhdppywARe43rGU96163RckIuFmFDQKZV9SMUrwpQFu4Z2D5yTNqnlLRfAgMBAAGjgZkwgZYwCQYDVR0TBAIwADAdBgNVHQ4EFgQU5FZqQ4gnVc+inIeZF+o3ID+VhcEwSAYDVR0jBEEwP4AUo562SGdCEjZBvW3gubSgUouX8bOhHKQaMBgxFjAUBgNVBAMMDUpldFByb2ZpbGUgQ0GCCQDSbLGDsoN54TATBgNVHSUEDDAKBggrBgEFBQcDATALBgNVHQ8EBAMCBaAwDQYJKoZIhvcNAQELBQADggIBANLG1anEKid4W87vQkqWaQTkRtFKJ2GFtBeMhvLhIyM6Cg3FdQnMZr0qr9mlV0w289pf/+M14J7S7SgsfwxMJvFbw9gZlwHvhBl24N349GuthshGO9P9eKmNPgyTJzTtw6FedXrrHV99nC7spaY84e+DqfHGYOzMJDrg8xHDYLLHk5Q2z5TlrztXMbtLhjPKrc2+ZajFFshgE5eowfkutSYxeX8uA5czFNT1ZxmDwX1KIelbqhh6XkMQFJui8v8Eo396/sN3RAQSfvBd7Syhch2vlaMP4FAB11AlMKO2x/1hoKiHBU3oU3OKRTfoUTfy1uH3T+t03k1Qkr0dqgHLxiv6QU5WrarR9tx/dapqbsSmrYapmJ7S5+ghc4FTWxXJB1cjJRh3X+gwJIHjOVW+5ZVqXTG2s2Jwi2daDt6XYeigxgL2SlQpeL5kvXNCcuSJurJVcRZFYUkzVv85XfDauqGxYqaehPcK2TzmcXOUWPfxQxLJd2TrqSiO+mseqqkNTb3ZDiYS/ZqdQoGYIUwJqXo+EDgqlmuWUhkWwCkyo4rtTZeAj+nP00v3n8JmXtO30Fip+lxpfsVR3tO1hk4Vi2kmVjXyRkW2G7D7WAVt+91ahFoSeRWlKyb4KcvGvwUaa43fWLem2hyI4di2pZdr3fcYJ3xvL5ejL3m14bKsfoOv
如果提示报错，可以配置一下代理，比如
（注意：这样会影响插件、库的下载导入；那么可以在安装插件和导入库之后再配置）
手动代理配置
http
主机名：localhost
端口号：80
