Newer
Older
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
import pandas as pd
from datetime import date
import sys,os
from optparse import OptionParser
import openpyxl.utils.exceptions as ecc
# Read the ICD excel file
class excelIcd:
def __init__(self, excel_file=None):
self.bookfile =excel_file
self.book = None
self.nsheets=0
self.sheets={}
self.MainSheet={}
self.GetSheet={}
self.SetSheet={}
self.CmdSheet={}
self.ModeSheet={}
self.debug=False
self._getExcelWorkbook()
def setDebugModeOn(self):
debug=True
def setDebugModeOff(self):
debug=False
def _getExcelWorkbook(self):
try:
self.book = pd.ExcelFile(self.bookfile,engine='openpyxl')
except FileNotFoundError:
print("File does not exist")
except ecc.InvalidFileException:
print(" openpyxl does not support the old .xls file format\n","Trying with xlrd")
self.book = pd.ExcelFile(self.bookfile)
self.nsheets = len(self.book.sheet_names)
if(self.debug):
print(self.nsheets,self.book.sheet_names)
for sheet_name in (self.book.sheet_names):
#sheet=self.book.sheet_names[sheet_index]
self.sheets[sheet_name]=self.book.parse(sheet_name)
siz=self.sheets[sheet_name].shape
if(self.debug):
print(self.sheets[sheet_name].columns,siz[0],siz[1])
self.getMain()
self.GetSheet=self.getSheet("GET")
self.SetSheet=self.getSheet("SET")
self.CmdSheet=self.getSheet("CMD")
self.ModeSheet=self.getSheet("MODE")
def getMain(self):
self.MainSheet["NCols"]=(self.sheets["Main"].shape)[1]
self.MainSheet["NRows"]=(self.sheets["Main"].shape)[0] -2
self.MainSheet["Colnames"]= self.sheets["Main"].columns[1:]
for colind,coname in enumerate (self.sheets["Main"].loc[0]):
#print "CCC",coname
colval=[]
if(self.debug):
print("****",coname,"*****",(str(coname)).replace(' ','_'))
#colname=(str(coname)).replace(' ','_')
for idx in range(0,self.MainSheet["NRows"]):
#print "BBBBB", self.sheets["Main"].cell(idx,colind).value
colval.append(self.sheets["Main"].iat[idx+1,colind])
self.MainSheet[coname]=colval
if(self.debug):
print(coname,self.MainSheet[coname])
print("Read Sheet: Main"," from:", self.bookfile )
return self.MainSheet
def getSheet(self, name):
Sheet={}
Sheet["NCols"]=(self.sheets[name].shape)[1]
Sheet["NRows"]=(self.sheets[name].shape)[0]
Sheet["Colnames"]= self.sheets[name].columns
#print(Sheet["Colnames"])
for colind,coname in enumerate (self.sheets[name]):
#coname=self.sheets["Main"].cell(1,colind).value
#print "CCC",coname
colval=[]
#colname=(str(coname)).replace(' ','_')
for idx in range(0,Sheet["NRows"]):
#print "BBBBB", self.sheets["Main"].cell(idx,colind).value
colval.append(self.sheets[name].iat[idx,colind])
Sheet[coname]=colval
#print(coname,Sheet[coname])
print("Read Sheet:",name," from:", self.bookfile )
return Sheet