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.MainSheet=self.getSheet("Main")
		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]
		self.MainSheet["Colnames"]= self.sheets["Main"].columns
		for colind,coname in enumerate (self.sheets["Main"]):
			
			#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,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={}
		try:
			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
		except:
			print("sheet:",name,"does not exist")
			return None