read_excel.py

read_excel.py

#[[file:/pip_raid/phil/gcm_e340/read_names.py]]
import xlrd
filename='standard_output.xls'
workbook = xlrd.open_workbook(filename)
worksheet = workbook.sheet_by_name('Amon')

twod_fields=[]
headers=worksheet.row(14)
colnames=[item.value for item in headers]
for rownum in np.arange(16,66):
    row = worksheet.row(rownum)
    twod_fields.append([item.value for item in row])

#[[file:/pip_raid/phil/gcm_e340/read_xls.py]]    
from openpyxl import load_workbook
wb = load_workbook(name,data_only=True)
B1_sh,A2_sh=wb.get_sheet_names()
sheet=wb[A2_sh]
df_a2=sheet_dataframe(sheet)
sheet=wb[B1_sh]
df_b1=sheet_dataframe(sheet)


def sheet_dataframe(sheet):
    region=sheet['A6':'I245']
    num_rows=len(region)
    num_cols=len(region[0])
    the_rows=[]
    for row in range(num_rows):
        col_list=[]
        for col in range(num_cols):
            if col==0:
                value=dt(region[row][col].value,1,1)
            else:
                value=region[row][col].value
            col_list.append(value)
        the_rows.append(col_list)
    line=sheet['A5':'I5']        
    header=[item.value for item in line[0]]
    header=[item.replace(' ','_') for item in header]
    header=[item.lower() for item in header]
    df=pd.DataFrame.from_records(the_rows,columns=header)
    return df