python pandas操作excel

创建空的excel

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

　　

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文件：

import pandas

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

　　操作excel的常见操作

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)