|
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)) |
|