def openWorkBook(self):
self.workbook = xlrd.open_workbook(self.planningSheetName)
timestamp = time.strftime('%d-%m-%y_%H.%M.%S')
self.write_logger.info(
self.planningSheetName + "--" +
self.planningSheetName[self.planningSheetName.
rfind("\\"):len(self.planningSheetName)])
excelName = self.planningSheetName[self.planningSheetName.rfind(
"\\"):len(self.planningSheetName) - 4] + timestamp
self.outputWorkBookName = config.project_path + '/outputFiles/' + excelName + ".xlsm"
self.wb = load_workbook(filename=self.planningSheetName, keep_vba=True)
self.wb.save(self.outputWorkBookName)
self.wb = load_workbook(filename=self.outputWorkBookName,
keep_vba=True)
for objName in self.objectList:
print("Writing Values for Object %s" % objName)
#Open the corresponding worksheet and read header information
self.openWorkSheet(objName)
#FIll up the cols with respect to col number
self.getCols()
#Get values from the validation row
self.getValidation()
#Start writing value in the worksheet.
self.writeObjectValues(objName)
self.wb.save(self.outputWorkBookName)
os.startfile(self.outputWorkBookName)
def __init__(self, worksheetName, outputDir, timestamp):
print("Inside init")
print(worksheetName)
self.nsr_workbook_name = worksheetName
print(" %s" % self.nsr_workbook_name)
self.workbook = xlrd.open_workbook(self.nsr_workbook_name)
self.outputDir = outputDir
self.timestamp = timestamp
##############
worksheet = self.workbook.sheet_by_name("Contents")
num_rows = worksheet.nrows
num_cols = worksheet.ncols
initial_header_row = 2
start_column_num = 1
last_col_num = 2
_inputFile_Address_Col = 10
wrap = wrapper()
### Getting list of Object to be processed
demoList = wrap.fetchingObect(worksheet, num_rows, initial_header_row,
start_column_num, last_col_num)
#logger.info("Mains-start---"+str(demoList))
print(demoList)
_obj_name_ = ""
for _obj_name_ in demoList[:]:
if ((_obj_name_ in skipWorksheet)
or (self.isMapSheet(_obj_name_) == True)):
print("Skip %s" % _obj_name_)
else:
print("Creating SPML Query for %s" % _obj_name_)
self.createSPMLGetQuery(_obj_name_, self.timestamp)
def toXML(filename, node_name, cellsAs):
xlsFile = xlrd.open_workbook(filename.encode(sys.getfilesystemencoding() ))
firstSheet = xlsFile.sheet_by_index(0)
attributes = firstSheet.row_values(0)
for rownum in range(1, firstSheet.nrows):
firstSheet.row_values(rownum)
cells = [ convertFloatsToIntStrings(i) for i in firstSheet.row_values(rownum) ]
if (cellsAs == "attributes"):
s = xmlRowAsAttributes(node_name, attributes, cells, rownum)
elif (cellsAs == "nodes"):
s = xmlRowAsNodes(node_name, attributes, cells, rownum)
print s.encode('utf8')
def toXML(filename, node_name, cellsAs, output_file):
xlsFile = xlrd.open_workbook(filename)
firstSheet = xlsFile.sheet_by_index(0)
attributes = firstSheet.row_values(0)
for rownum in range(1, firstSheet.nrows):
firstSheet.row_values(rownum)
cells = [ convertFloatsToIntStrings(i) for i in firstSheet.row_values(rownum) ]
if (cellsAs == "attributes"):
s = xmlRowAsAttributes(node_name, attributes, cells, rownum)
elif (cellsAs == "nodes"):
s = xmlRowAsNodes(node_name, attributes, cells, rownum)
output_file.write(s)
def toXML(filename, node_name, cellsAs):
xlsFile = xlrd.open_workbook(filename.encode(sys.getfilesystemencoding()))
firstSheet = xlsFile.sheet_by_index(0)
attributes = firstSheet.row_values(0)
for rownum in range(1, firstSheet.nrows):
firstSheet.row_values(rownum)
cells = [
convertFloatsToIntStrings(i) for i in firstSheet.row_values(rownum)
]
if (cellsAs == "attributes"):
s = xmlRowAsAttributes(node_name, attributes, cells, rownum)
elif (cellsAs == "nodes"):
s = xmlRowAsNodes(node_name, attributes, cells, rownum)
print s.encode('utf8')
def getFlagValuesFromTemplateSheet(self):
workbook = xlrd.open_workbook(config.project_path + "\\templates" +
"\\NSRdata_HLR_fALU.xlsm")
worksheet = workbook.sheet_by_name(config.nokiaObjectName)
self.f_gprs_100 = worksheet.cell_value(1, 10)
self.f_gprs_102 = worksheet.cell_value(1, 12)
#customer = "ericsson"
#inputExcel = "D:/userdata/djyoti/Desktop/2018/SDM/Code/svn/0405/templates/NSRdata_HLR_ericsson.xlsm"
#===========================================================================
#===========================================================================
# _input_dumpfile_ = sys.argv[1]
# logger.info("processing input dump " + _input_dumpfile_)
#===========================================================================
#===========================================================================
customer = sys.argv[2]
inputExcel = sys.argv[1]
#===========================================================================
logger.info("input data found for customer " + customer +
" excel path is " + inputExcel)
wb = xlrd.open_workbook(inputExcel)
config.customer = customer
worksheet = wb.sheet_by_name("Contents")
num_rows = worksheet.nrows
num_cols = worksheet.ncols
initial_header_row = 2
start_column_num = 1
last_col_num = 2
_inputFile_Address_Col = 10
wrap = wrapper()
### Getting list of Object to be processed
demoList = wrap.fetchingObect(worksheet, num_rows, initial_header_row,
start_column_num, last_col_num)
def construct_MappingInfo(self):
# open the mapping workbook and the worksheet
self.workbook = xlrd.open_workbook(self.mappingWorkbookPath)
# check for errors
self.worksheet = self.workbook.sheet_by_name(self.mappingSheetName)
#self.logger.debug("customer--"+ self.customer)
# check for errors
if (self.worksheet.nrows > 0 and self.worksheet.ncols > 1):
self.objectname = self.worksheet.cell_value(0, 1)
config.nokiaObjectName = self.worksheet.cell_value(0, 1)
self.logger.debug("nokia object name = %s" %
config.nokiaObjectName)
self.logger.debug("object name = %s " % self.objectname)
self.logger.debug(" number of rows = %d " % self.worksheet.nrows)
self.logger.debug("number of cols1 = %d " % self.worksheet.ncols)
print("\n %s :: Parsing Mapping Sheet" % (config.nokiaObjectName))
mappingAtribute = {}
#for row in range(2,self.worksheet.nrows):
row = 2
while row < self.worksheet.nrows:
#print("row number %d\n",row)
# it this is a non merged cell or first cell of a merged cell then store new mappingAttribute
#if ((isMergedCell(row,0) == True and isMergedCell(row -1 , 0) == False) or
# isMergedCell(row,0) == False):
if (self.isMergedCell(row, 0) == False):
# create a new mappintAttribute
mappingAttribute = {}
mappingAttribute['row'] = row
# print ( "nokiaAttrName = %s faluAttrName = %s characterset = %s" %(self.worksheet.cell_value(row,0),self.worksheet.cell_value(row,2),self.worksheet.cell_value(row,3) ))
mappingAttribute['nokiaAttrName'] = self.CellValue(
self.worksheet.cell_type(row, 0),
self.worksheet.cell_value(row, 0))
mappingAttribute['isMapped'] = self.CellValue(
self.worksheet.cell_type(row, 1),
self.worksheet.cell_value(row, 1))
mappingAttribute['faluAttrName'] = self.CellValue(
self.worksheet.cell_type(row, 2),
self.worksheet.cell_value(row, 2))
mappingAttribute['characterSet'] = self.CellValue(
self.worksheet.cell_type(row, 3),
self.worksheet.cell_value(row, 3))
attrValMap = []
# print("non merged cell")
valMap = {}
valMap['faluAttrVal'] = self.CellValue(
self.worksheet.cell_type(row, 4),
self.worksheet.cell_value(row, 4))
valMap['nokiaAttrVal'] = self.CellValue(
self.worksheet.cell_type(row, 5),
self.worksheet.cell_value(row, 5))
attrValMap.append(valMap)
mappingAttribute['valueSet'] = attrValMap
self.mappingInfo.append(mappingAttribute)
else:
# in case of merged cell, attacheh teh valMap to the last mappintAttribute
mappingAttribute = self.mappingInfo[-1]
attrValMap = mappingAttribute['valueSet']
nextrow = row
while (self.isMergedCell(nextrow, 0)):
valMap = {}
# print("processing merged cell row %d falu val %s nokia val %s" %(nextrow,self.worksheet.cell_value(nextrow,4),self.worksheet.cell_value(nextrow,5)))
valMap['faluAttrVal'] = self.CellValue(
self.worksheet.cell_type(nextrow, 4),
self.worksheet.cell_value(nextrow, 4))
valMap['nokiaAttrVal'] = self.CellValue(
self.worksheet.cell_type(nextrow, 5),
self.worksheet.cell_value(nextrow, 5))
attrValMap.append(valMap)
nextrow = nextrow + 1
# print(" setting row to %d"%nextrow)
row = nextrow - 1
row = row + 1
return self.mappingInfo