pandas中excel表的处理

pd.read_excel('path_to_file.xls', sheet_name='Sheet1')

处理excel中的多个工作表

xlsx = pd.ExcelFile('path_to_file.xls')

df = pd.read_excel(xlsx, 'Sheet1')

with pd.ExcelFile('path_to_file.xls') as xls:
    df1 = pd.read_excel(xls, 'Sheet1')
    df2 = pd.read_excel(xls, 'Sheet2')
with pd.ExcelFile('path_to_file.xls') as xls:
    data['Sheet1'] = pd.read_excel(xls, 'Sheet1', index_col=None,na_values=['NA'])
    data['Sheet2'] = pd.read_excel(xls, 'Sheet2', index_col=1)



with pd.ExcelFile('path_to_file.xls') as xls:
    data['Sheet1'] = pd.read_excel(xls, 'Sheet1', index_col=None,na_values=['NA'])
    data['Sheet2'] = pd.read_excel(xls, 'Sheet2', index_col=None,na_values=['NA'])


等价于
data = pd.read_excel('path_to_file.xls', ['Sheet1', 'Sheet2'],index_col=None, na_values=['NA'])
df = pd.DataFrame({'a': [1, 2, 3, 4], 'b': [5, 6, 7, 8]},index=pd.MultiIndex.from_product([['a', 'b'], ['c', 'd']]))
df.to_excel('path_to_file.xlsx')
df = pd.read_excel('path_to_file.xlsx', index_col=[0, 1])
df.index = df.index.set_names(['lvl1', 'lvl2'])
df.to_excel('path_to_file.xlsx')

df.index.set_names(['lvl1', 'lvl2'])

pd.read_excel('path_to_file.xls', 'Sheet1', usecols='A,C:E')
pd.read_excel('path_to_file.xls', 'Sheet1', usecols=[0, 2, 3])
pd.read_excel('path_to_file.xls', 'Sheet1', usecols=lambda x: x.isalpha())
# 转换日期
pd.read_excel('path_to_file.xls', 'Sheet1', parse_dates=['date_strings'])
# 单元格转换
pd.read_excel('path_to_file.xls', 'Sheet1', converters={'MyBools': bool})
def cfun(x):
    return int(x) if x else -1
pd.read_excel('path_to_file.xls', 'Sheet1', converters={'MyInts': cfun})
pd.read_excel('path_to_file.xls', dtype={'MyInts': 'int64', 'MyText': str})
df.to_excel('path_to_file.xlsx', sheet_name='Sheet1')
# 合并单元格
df.to_excel('path_to_file.xlsx', index_label='label', merge_cells=False)
# 一个excel文件中写入多个write
with pd.ExcelWriter('path_to_file.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1')
    df2.to_excel(writer, sheet_name='Sheet2')
将excel文件写入缓存
try:
    from io import BytesIO
except ImportError:
    from cStringIO import StringIO as BytesIO
bio = BytesIO()
writer = pd.ExcelWriter(bio, engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()
bio.seek(0)
workbook = bio.read()