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