1. 读取前n行所有数据
import pandas as pd
df = pd.read_excel('school.xlsx')
data1 = df.head(7)
data2 = df.values
print("获取到所有的值:\n{0}".format(data1))
print("获取到所有的值:\n{0}".format(data2))
2. 读取特定行,特定列
import pandas as pd
df = pd.read_excel('school.xlsx')
data1 = df.ix[0].values
data2 = df.ix[1,1]
data3 = df.ix[[1,2]].values
data4 = df.ix[:,[0]].values
data5 = df.ix[:,[u'class',u'name']].values
print("数据:\n{0}".format(data1))
print("数据:\n{0}".format(data2))
print("数据:\n{0}".format(data3))
print("数据:\n{0}".format(data4))
print("数据:\n{0}".format(data5))
3. 获取xlsx文件行号,所有列名称
import pandas as pd
df = pd.read_excel('school.xlsx')
print("输出行号列表{}".format(df.index.values))
print("输出列标题{}".format(df.columns.values))
4. 读取xlsx数据转换为字典
import pandas as pd
df = pd.read_excel('school.xlsx')
test_data=[]
for i in df.index.values:
row_data=df.ix[i,['id','name','class','data','stature']].to_dict()
test_data.append(row_data)
print("最终获取到的数据是:{0}".format(test_data))
5. 写xlsx文件
import xlsxwriter
file_name = "first_book.xlsx"
workbook = xlsxwriter.Workbook(file_name)
worksheet = workbook.add_worksheet('sheet1')
worksheet.write(0, 0, 'id')
worksheet.write(0,1, 'name')
worksheet.write(0,2, 'class')
worksheet.write(0,3, 'data')
worksheet.write_row(1, 0, [1, 2, 3])
worksheet.write_column('D2', ['a', 'b', 'c'])
workbook.close()