import pandas as pd

import os
os.system('cls')

#----------------------------------------------------------#
# make pandas DataFrame
#----------------------------------------------------------#

sheet1 = pd.DataFrame({'c1': ['A0', 'A1', 'A2', 'A3'],
                       'c2': ['','','',''],
                       'c3': ['C0', 'C1', 'C2', 'C3']},
                       index=['r1', 'r2', 'r3', 'r4'])

row = ['r1','r2','r3']
col = ['c1','c2','c3','c4']
sheet2 = pd.DataFrame([],row,col)

df = pd.Series({'sheet1':sheet1,'sheet2':sheet2})

i,j=10,11
for name,sheet in df.items():
    for r in sheet.index:
        for c in sheet.columns:
            sheet.at[r,c]=j
            j+=1
        i,j=i+10,i+11

#----------------------------------------------------------#
# write excel
#----------------------------------------------------------#
xls_file='pandas_excel.xlsx'
writer = pd.ExcelWriter(xls_file)
df['sheet1'].to_excel(writer,sheet_name='Sheet 1')
df['sheet2'].to_excel(writer,sheet_name='Sheet 2')
writer.close()

#----------------------------------------------------------#
# read excel
#----------------------------------------------------------#
#df= pd.read_excel("cell1.xls") #default header=0,index_col=None,sheet_name=0)
df = pd.read_excel(xls_file,index_col=0,sheet_name=['Sheet 1','Sheet 2'])

sheet1 = df.get('Sheet 1')
sheet2 = df.get('Sheet 2')

for sheet in df.keys():
    print("#-----------------------------------------#")
    print("# sheet:",sheet)
    print("#-----------------------------------------#")
    print("  index:",df[sheet].index.tolist())
    print("columns:",df[sheet].columns.ravel())
    print("columns[2: ]=",df[sheet].columns[1:])
    print("columns[ :2]=",list(df[sheet].columns[:2]))
    print("at['r1','c2']=",df[sheet].at['r1','c2'])
    #----------------------------------------------------------#
    # write csv
    #----------------------------------------------------------#
    print(df[sheet].to_csv(index=True))