写入excel文件:
#!/usr/bin/python
import pandas as pd
import os
# 三个字段 name, site, age
nme = ["Google", "Runoob", "Taobao", "Wiki"]
st = ["www.google.com", "www.runoob.com", "www.taobao.com", "www.wikipedia.org"]
ag = [90, 40, 80, 90]
# 字典
dict = {'name': nme, 'site': st, 'age': ag}
df = pd.DataFrame(dict)
print(df)
df.to_excel('aaa.xlsx')
out:
name site age
0 Google www.google.com 90
1 Runoob www.runoob.com 40
2 Taobao www.taobao.com 80
3 Wiki www.wikipedia.org 90
同目录下会生成aaa.xlsx文件。查看文件内容:
去除索引:
df.to_excel('aaa.xlsx', index=False)
文件内容:
指定sheet_name:
df.to_excel('aaa.xlsx', index=False, sheet_name='测试页')
写入到多个sheet页中:
with pd.ExcelWriter('aaa.xlsx') as writer:
df.to_excel(writer, index=False, sheet_name='测试页1')
df.to_excel(writer, sheet_name='测试页2')
excel表内容如下:
读取excel
#!/usr/bin/python
import pandas as pd
import os
df = pd.read_excel('aaa.xlsx', sheet_name='测试页1')
print(df)
df2 = pd.read_excel('aaa.xlsx', sheet_name='测试页2')
print(df2)
out:
name site age
0 Google www.google.com 90
1 Runoob www.runoob.com 40
2 Taobao www.taobao.com 80
3 Wiki www.wikipedia.org 90
Unnamed: 0 name site age
0 0 Google www.google.com 90
1 1 Runoob www.runoob.com 40
2 2 Taobao www.taobao.com 80
3 3 Wiki www.wikipedia.org 90
read_excel 其中sheet_name指定要读取的sheet名称,默认是第一个。
index_col 指定哪一列做索引
#!/usr/bin/python
import pandas as pd
import os
df = pd.read_excel('aaa.xlsx', sheet_name='测试页1',index_col=False)
print(df)
df = pd.read_excel('aaa.xlsx', sheet_name='测试页1',index_col=0)
print(df)
out:
name site age
0 Google www.google.com 90
1 Runoob www.runoob.com 40
2 Taobao www.taobao.com 80
3 Wiki www.wikipedia.org 90
site age
name
Google www.google.com 90
Runoob www.runoob.com 40
Taobao www.taobao.com 80
Wiki www.wikipedia.org 90
默认index_col=False
参数:usecols
指定读取哪几列
df = pd.read_excel('aaa.xlsx',sheet_name='测试页1',index_col=False)
print(df)
df = pd.read_excel('aaa.xlsx',sheet_name='测试页1',index_col=False,usecols=[0,1])
print(df)
out:
name site age
0 Google www.google.com 90
1 Runoob www.runoob.com 40
2 Taobao www.taobao.com 80
3 Wiki www.wikipedia.org 90
name site
0 Google www.google.com
1 Runoob www.runoob.com
2 Taobao www.taobao.com
3 Wiki www.wikipedia.org
参数:nrows
指定读取多少行
df = pd.read_excel('aaa.xlsx',sheet_name='测试页1',index_col=False,usecols=[0,1],nrows=2)
print(df)
out:
name site
0 Google www.google.com
1 Runoob www.runoob.com
参考: