一 读取excel内容、并放入字典
1 读取excel文件
import pandas as pdfile_path = '/cluster/home3/zjc/Code/COD/BGNet_My/Dy_BGNet_master/openpyxl-light.xlsx' 读取excel raw_data = pd.read_excel(file_path, header=0)# header=0表示第一行是表头,就自动去除了print(raw_data)# 读取到的结果如下'''读取excel(Pdb) raw_data numname wfm00COD10K-CAM-1-Aquatic-1-BatFish-2.png0.89910811COD10K-CAM-1-Aquatic-1-BatFish-4.png0.88533322COD10K-CAM-1-Aquatic-1-BatFish-5.png0.89933433COD10K-CAM-1-Aquatic-1-BatFish-6.png0.50347244COD10K-CAM-1-Aquatic-10-LeafySeaDragon-416.png0.422056...... ... ...20212021COD10K-CAM-5-Other-69-Other-5051.png0.97337120222022COD10K-CAM-5-Other-69-Other-5059.png0.74531020232023COD10K-CAM-5-Other-69-Other-5060.png0.00019820242024COD10K-CAM-5-Other-69-Other-5062.png0.83567220252025COD10K-CAM-5-Other-69-Other-5063.png0.982474'''
2 读取value,舍弃行号
data = raw_data.values # 获取其中内容(num、name、wfm)(舍弃行号)print(data)'''仅展示value,舍弃行号array([[0, 'COD10K-CAM-1-Aquatic-1-BatFish-2.png', 0.8991082232693329], [1, 'COD10K-CAM-1-Aquatic-1-BatFish-4.png', 0.8853328644290668], [2, 'COD10K-CAM-1-Aquatic-1-BatFish-5.png', 0.8993339821090026], ..., [2023, 'COD10K-CAM-5-Other-69-Other-5060.png',0.0001984435470317607], [2024, 'COD10K-CAM-5-Other-69-Other-5062.png', 0.8356721476832216], [2025, 'COD10K-CAM-5-Other-69-Other-5063.png', 0.9824740778028651]],dtype=object) '''
3 读取为字典
dict = {}for i in range(len(data)):dict[data[i][1]] = data[i][2]print(dict)'''将下面数据放入字典data[0][1]-> 'COD10K-CAM-1-Aquatic-1-BatFish-2.png'data[0][2]->0.8991082232693329字典为dict['COD10K-CAM-1-Aquatic-1-BatFish-2.png']-> 0.8991082232693329'''
一 读取excel内容、并放入字典(完整代码)
import pandas as pdfile_path = '/cluster/home3/zjc/Code/COD/BGNet_My/Dy_BGNet_master/openpyxl-light.xlsx'# r对路径进行转义,windows需要raw_data = pd.read_excel(file_path, header=0)# header=0表示第一行是表头,就自动去除了print(raw_data)'''读取excel(Pdb) raw_data numname wfm00COD10K-CAM-1-Aquatic-1-BatFish-2.png0.89910811COD10K-CAM-1-Aquatic-1-BatFish-4.png0.88533322COD10K-CAM-1-Aquatic-1-BatFish-5.png0.89933433COD10K-CAM-1-Aquatic-1-BatFish-6.png0.50347244COD10K-CAM-1-Aquatic-10-LeafySeaDragon-416.png0.422056...... ... ...20212021COD10K-CAM-5-Other-69-Other-5051.png0.97337120222022COD10K-CAM-5-Other-69-Other-5059.png0.74531020232023COD10K-CAM-5-Other-69-Other-5060.png0.00019820242024COD10K-CAM-5-Other-69-Other-5062.png0.83567220252025COD10K-CAM-5-Other-69-Other-5063.png0.982474'''data = raw_data.valuesprint(data)'''仅展示value,舍弃行号array([[0, 'COD10K-CAM-1-Aquatic-1-BatFish-2.png', 0.8991082232693329], [1, 'COD10K-CAM-1-Aquatic-1-BatFish-4.png', 0.8853328644290668], [2, 'COD10K-CAM-1-Aquatic-1-BatFish-5.png', 0.8993339821090026], ..., [2023, 'COD10K-CAM-5-Other-69-Other-5060.png',0.0001984435470317607], [2024, 'COD10K-CAM-5-Other-69-Other-5062.png', 0.8356721476832216], [2025, 'COD10K-CAM-5-Other-69-Other-5063.png', 0.9824740778028651]],dtype=object)'''dict = {}for i in range(len(data)):dict[data[i][1]] = data[i][2]print(dict)'''将下面数据放入字典data[0][1]-> 'COD10K-CAM-1-Aquatic-1-BatFish-2.png'data[0][2]->0.8991082232693329字典为dict['COD10K-CAM-1-Aquatic-1-BatFish-2.png']-> 0.8991082232693329'''import pdbpdb.set_trace()
二、将字典内容写入 excel文件
1
假设已有字典内容为:
即student列表里有4个字典,
第一个字典里面有3对key-value
“num”: 1,
“name”: “cod1”,
“wfm”: 0.1
student = [{"num": 1, "name": "cod1", "wfm": 0.1},{"num": 2, "name": "cod2", "wfm": 0.2},{"num": 3, "name": "cod3", "wfm": 0.3},{"num": 4, "name": "cod4", "wfm": 0.4},]
2 导入Workbook并实力化
from openpyxl import Workbookworkbook = Workbook()
3 激活sheet-设置sheet名-插入标题-
# 默认sheetsheet = workbook.active# 激活sheetsheet.title = "openpyxl"# 设置sheet名字sheet.append(["num", "name", "wfm"])# 插入标题for data in student:# 列表循环读取dictsheet.append(list(data.values()))workbook.save("openpyxl.xlsx")
二、将字典内容写入 excel文件(完整代码)
student = [{"num": 1, "name": "cod1", "wfm": 0.1},{"num": 2, "name": "cod2", "wfm": 0.2},{"num": 3, "name": "cod3", "wfm": 0.3},{"num": 4, "name": "cod4", "wfm": 0.4},]import pdbpdb.set_trace()from openpyxl import Workbookworkbook = Workbook()# 默认sheetsheet = workbook.active# 激活sheetsheet.title = "openpyxl"# 设置sheet名字sheet.append(["num", "name", "wfm"])# 插入标题for data in student:# 列表循环读取dictsheet.append(list(data.values()))workbook.save("openpyxl.xlsx")