Python pandas 读取和保存DataFrame到Excel中多个sheet的方法及示例代码

1、读取Excel中sheet中数据

import pandas as pd
xl = pd.ExcelFile('foo.xls')
xl.sheet_names  # 查看所有工作表名称
xl.parse(sheet_name)  # 读取指定的sheet到 DataFrame

或者

xl = pd.ExcelFile(path)
for name in xl.sheet_names:
    df = pd.read_excel(xl, name)

 2、保存DataFrame到Excel文件中多个sheet

from pandas import ExcelWriter
def save_xls(list_dfs, xls_path):
    with ExcelWriter(xls_path) as writer:
        for n, df in enumerate(list_dfs):
            df.to_excel(writer,'sheet%s' % n)
        writer.save()

 或者

import pandas as pd 
# dictionary of data 
dct1 = {'ID': {0: 23, 1: 43, 2: 12, 
              3: 13, 4: 67, 5: 89, 
              6: 90, 7: 56, 8: 34}, 
      'Name': {0: 'Ram', 1: 'Deep', 
               2: 'Yash', 3: 'Aman', 
               4: 'Arjun', 5: 'Aditya', 
               6: 'Divya', 7: 'Chalsea', 
               8: 'Akash' }, 
      'Marks': {0: 89, 1: 97, 2: 45, 3: 78, 
                4: 56, 5: 76, 6: 100, 7: 87, 
                8: 81}, 
      'Grade': {0: 'B', 1: 'A', 2: 'F', 3: 'C', 
                4: 'E', 5: 'C', 6: 'A', 7: 'B', 
                8: 'B'} 
    } 
 dct2 = {'ID': {0: 23, 1: 43, 2: 12, 
              3: 13, 4: 67, 5: 89, 
              6: 90, 7: 56, 8: 34}, 
      'Name': {0: 'C', 1: 'Java', 
               2: 'Python', 3: 'Cjavapy', 
               4: 'Js', 5: 'linux', 
               6: 'Docker', 7: 'C#', 
               8: 'C++' }, 
      'Marks': {0: 89, 1: 97, 2: 45, 3: 78, 
                4: 56, 5: 76, 6: 100, 7: 87, 
                8: 81}, 
      'Grade': {0: 'B', 1: 'A', 2: 'F', 3: 'C', 
                4: 'E', 5: 'C', 6: 'A', 7: 'B', 
                8: 'B'} 
    }  
df1 = pd.DataFrame(dct1) 
df2 = pd.DataFrame(dct2) 
with pd.ExcelWriter(r'D:\cjavapy\test2.xls') as writer:
    df1.to_excel(writer, sheet_name='df1')
    df2.to_excel(writer, sheet_name='df2')