python pandas操作excel

创建空的excel

?

1 2 3 4

import pandas as pd # 表示excel的sheet页 df = pd.DataFrame() df.to_excel("D:/pycode/output/output.xlsx")

　　

?

1 2 3 4

df = pd.DataFrame({"ID":[1,2,3],"Name":["tom","bobo","jack"]}) # 设置索引重新赋值给df df = df.set_index("ID") df.to_excel("D:/pycode/output/output.xlsx")

　　读取已存在的excel文件：

?

1 2 3 4 5

import pandas   file = r'D:\pycode\output\student.xlsx' data = pandas.read_excel(file,sheet_name=0,keep_default_na=False) print(data)

　　操作excel的常见操作

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

import pandas   file = r'D:\pycode\output\student.xlsx' data = pandas.read_excel(file,sheet_name=0,keep_default_na=False)   # 行号， row_index = data.index.values #print(row_index) row_num = len(row_index) # 行数 #print(row_num) # 列名 col = data.columns.values #print("列名:",col) col_num = len(col) #print(col_num) col_dict = {} for i in range(col_num):     col_dict[col[i]] = i   #print("列字典",col_dict)   for i in range(row_num):         index = col_dict.get('姓名')         cell_data = data.iloc[i,index]         #print(cell_data)   # 将列名中空串替换为null for i in col:     data[i].replace("","null",inplace=True)       data['爱好'].replace("无","rap",inplace=True) # 新增列 data['成绩']=None # 列的第一行赋值 data['成绩'][0]=50 print(data) # 保存excel data.to_excel(file,sheet_name='Sheet1',index=False,header=True)