def main(): window1 = Tk() firstW = first(window1) window1.mainloop() if firstW.start: ### It's xml file window2 = Tk() startN= browse(window2, True) window2.mainloop() if startN.clickedBack: main() elif startN.isOk: refNameList, refGap, typeList, depList, wireList = xmlTool.readXML(startN.filePath) folderPath, fileName = splitFileFolderAndName(startN.filePath) if not refNameList or not wireList: startN.fileFormatIncorrectWarning(fileName) else: refNameRepeats = xmlTool.checkRepeats(refNameList) if refNameRepeats: ### creat info files when there is a repeat info = xmlTool.XMLInfo(startN.filePath, refNameRepeats, refNameList, refGap, wireList) errorFilePath = folderPath + '/' + fileName + '_info.txt' infoWindow = Tk() warning = errorMessage(infoWindow, info, errorFilePath, True) infoWindow.mainloop() else: ### write excel sheet excelWrite = excelSheet() excelWrite.startNewExcelSheet(startN.filePath, refNameList, refGap, typeList, depList, wireList) elif firstW.importSheet: ### It's xlsx file window2 = Tk() importS= browse(window2, False) window2.mainloop() if importS.clickedBack: main() elif importS.isOk: excelRead = excelSheet() xmlPath, refExcelDict, error = excelRead.readExcelSheet(importS.filePath) folderPath, fileName = splitFileFolderAndName(importS.filePath) if xmlPath == 0 and refExcelDict == 0 and error == 0: importS.fileFormatIncorrectWarning(fileName) elif xmlPath and refExcelDict: if error: #### call error messager errorFilePath = folderPath + '/' + fileName + '_error.txt' errorWindow = Tk() warning = errorMessage(errorWindow, error, errorFilePath, True) errorWindow.mainloop() else: ### call xml modifier newXmlFilePath = xmlTool.modifier(xmlPath, refExcelDict) if not newXmlFilePath: importS.fileFormatIncorrectWarning(fileName) else: startfile(newXmlFilePath)
def readXMLAndStartSheet(filePath, withFocus): """Get data from XML and present them in a Excel spreadsheet. Parameters ---------- filePath: string A xml file path. withFocus: int 1: with focus. 0: without focus Returns ------- file path: string An error message for incorrect file format. or (errorFilePath, info): tuple When there is any repeating referenece names. errorFilePath: string Info text file path. info: string Reference systems information. """ # Split file path into folder path and file name without extension folderPath, fileName = splitFileFolderAndName(filePath) # Read xml and get all references' names, missing references' names, types, dependon, and wire count information refInfo, wireInfo = xmlTool.readXML(filePath) # If reference name list is None or wireInfo dictionary is None, file format is incorrect if not refInfo and not wireInfo: return ("", "File: " + fileName + " - format incorrect!") if not refInfo and 0 in wireInfo: return ("", wireInfo[0]) # If there is repeating reference name if refInfo['repeats']: # Generate a text of information of xml data info = xmlTool.XMLInfo(filePath, refInfo['repeats'], refInfo['name'], refInfo['gap'], wireInfo['total']) # Create a error text file path to save to errorFilePath = folderPath + '/' + fileName + '_info.txt' return (errorFilePath, info) else: # Create an excelSheet object if withFocus: excelWrite = excelSheet(True) else: excelWrite = excelSheet(False) # Write data into an Excel spreadsheet error = excelWrite.startNewExcelSheet(filePath, refInfo, wireInfo) return ("", error)
def readSheetAndModifyXML(filePath): """ Function that reads a Excel sheet and modify a XML file. Parameters ---------- filePath: string xlsm or xlsx file path. Returns ------- (errorFilePath, error): tuple Errors occur when reading Excel sheets or writing data into xml files. error: string Error message. errorFilePath: string File path of an error text file. """ # Split file path into folder path and file name without extension folderPath, fileName = splitFileFolderAndName(filePath) # Create an excelRead object excelRead = excelSheet() # Read excel spreadsheet xmlPath, refExcelDict, error = excelRead.readExcelSheet(filePath) # If xmlPath and refExcelDict are not None if xmlPath and refExcelDict: # If error is not None, meaning there is an error if error: # Create an error text file path errorFilePath = folderPath + '/' + fileName + '_error.txt' return (errorFilePath, error) else: # Create a new xml file with modified data newXmlFilePathOrError = xmlTool.modifier(xmlPath, refExcelDict) # If newXmlFilePathOrError path is a file if isfile(newXmlFilePathOrError): # Open the new xml file in windows startfile(newXmlFilePathOrError) # No errors return ("", error) # There is an error--newXmlFilePathOrError is an error message return ("", newXmlFilePathOrError) # Errors occur when reading Excel sheet return ("", error)
def modifier(xmlFilePath, referenceDictDFromExc): xmlFolderPath, xmlFileName = splitFileFolderAndName(xmlFilePath) ### make a ElementTree object and find its root (highest node) try: tree = ET.parse(xmlFilePath) except ET.ParseError: return None root = tree.getroot() ### make two lists of all reference elements(objects) and wire elements(objects) referenceE = root.findall('ReferenceSystem') wireE = root.findall('Wire') numOfRef = len(referenceE) numOfWire = len(wireE) referenceEDict = {} for r in referenceE: ### Modify existing ref's type and dep if necessary ref = r.find('Name').text typ, dep = referenceDictDFromExc['og'][ref[1:]] if r.find('Type').text != typ: r.find('Type').text = typ if dep: if r.find('Dependon') == None: newDepEle = Element('Dependon') newDepEle.text = 'R' + dep r.insert(2, newDepEle) indent(newDepEle, 2) elif r.find('Dependon').text != 'R'+ dep: r.find('Dependon').text = 'R' + dep else: if r.find('Dependon') != None: r.remove(r.find('Dependon')) ### make reference element dictionary referenceEDict[ref] = r addRefDict = referenceDictDFromExc['add'] for nName in referenceDictDFromExc['newRefName']: # sorted(map(int, addRefDict.keys())) copy = writeARefCopy(referenceEDict['R'+addRefDict[nName][0]], addRefDict[nName][0], nName, addRefDict[nName][1]) root.insert(int(nName)-1, copy) ### Change wire des modifyWireDesRef(addRefDict[nName][0], nName, wireE) ### write to a new xml file newXmlFilePath = xmlFolderPath + "/" + xmlFileName + "_new.xml" tree.write(newXmlFilePath) return newXmlFilePath
def writeInfoText(xmlFilePath, repRef, refName, refGap, wireList): xmlFolderPath, xmlFileName = splitFileFolderAndName(xmlFilePath) infoName = xmlFileName + "_info.txt" infoFilePath = xmlFolderPath + "/" + infoName numR = len(refName) numW = len(wireList) if exists(xmlFolderPath): info = open(infoFilePath, "w") ### write file path info.write("#Input XML File: " + xmlFilePath + '\n\n') ### write repeating ref name if there is any info.write("#Repeating Reference:\n") if repRef: for r in repRef: info.write("There are " + str(r[1]) + " R" + r[0] + '\n') else: info.write("None\n") ### write first and last ref name info.write("\n#First Reference: R" + refName[0] + '\n') info.write("#Last Reference: R" + refName[numR-1] + '\n') ### write refernce gaps info.write("\n#Range of Gaps (included):\n") if refGap: for g in refGap: if len(g) == 1: info.write("R" + str(g[0]) + '\n') else: info.write("R" + str(g[0]) + ' - R' + str(g[1]) + '\n') else: info.write("None\n") info.write("\n#Number of Wires: " + str(numW) + "\n") ### Close file info.close() ### open text file in windows startfile(infoFilePath)
def readExcelSheet(self, xlsxFilePath): try: workbook = op.load_workbook(filename=xlsxFilePath, read_only=True, data_only=True) worksheet = workbook.get_sheet_by_name(self.workSheetName) except op.utils.exceptions.InvalidFileException: _, fileName = splitFileFolderAndName(xlsxFilePath) message = "File: " + fileName + " - format incorrect!" return None, None, message except KeyError: message = "Cannot find excel sheet - " + self.workSheetName + "!" return None, None, message xmlFilePath = worksheet[self.xmlFilePathCell].value[5:] lastRow = worksheet[self.appendRowCountCell].value # int ### excelReference data structure --> {'og': {'refNum':[type, dependon]}, 'add': {'refNum': [copyNum, type]}, 'newRefName': [str(refNum)]} excelReference = {'og': {}, 'add': {}, 'newRefName': []} missingRef = [] missingCopy = [] missingType = [] missingDep = [] wrongSeqRow = [] newRefName = [] checkRepeatRef = set() allCopy = {} repeat = {} row = self.firstInputRow prevAllExist = True error = False while int(row) <= lastRow: status = worksheet[self.statusC + row].value ref = str(worksheet[self.refC + row].value) copy = str(worksheet[self.copyC + row].value) typ = str(worksheet[self.typeC + row].value) dep = str(worksheet[self.depC + row].value) refExists = ref and ref != 'None' copyExists = copy and copy != 'None' typeExists = typ and typ != 'None' depExists = dep and dep != 'None' and dep != '0' ### with formula depCellEmpty = dep == None or dep == 'None' ### if gets modified by users and left empty if dep == 'None': dep = None if status == self.eTag: if refExists and copyExists and typeExists and not error: excelReference['og'][ref] = [typ, dep] else: if not refExists: missingRef.append(row) if not typeExists: missingType.append(row) error = True elif status == self.mTag: if refExists and copyExists and typeExists and depExists and not error: excelReference['add'][ref] = [copy, typ] excelReference['newRefName'].append(ref) else: if not refExists: missingRef.append(row) if not copyExists: missingCopy.append(row) if not typeExists: missingType.append(row) if depCellEmpty: missingDep.append(row) error = True else: ### append if prevAllExist: if refExists and copyExists and typeExists and depExists and not error: excelReference['add'][ref] = [copy, typ] excelReference['newRefName'].append(ref) elif refExists and not copyExists and not typeExists and not depExists: prevAllExist = False else: if not refExists: missingRef.append(row) if not copyExists: missingCopy.append(row) if not typeExists: missingType.append(row) if depCellEmpty: missingDep.append(row) error = True elif copyExists or typeExists or depExists: wrongSeqRow.append(row) if not refExists: missingRef.append(row) if not copyExists: missingCopy.append(row) if not typeExists: missingType.append(row) if depCellEmpty: missingDep.append(row) prevAllExist = True error = True ### chcek repeats if copy == self.copyBlockedText: copy = None if copy != 'None' and copy: if copy in allCopy and not copy in repeat: repeat[copy] = allCopy[copy] repeat[copy].append(row) error = True elif copy in allCopy and copy in repeat: repeat[copy].append(row) else: allCopy[copy] = [row] row = str(int(row) + 1) missingRealRefNum = [] if worksheet[self.pseudoRefC + self.pseudoTitleRow].value: pseudo2Real = {} exist = True pseudoRefRow = str(int(self.pseudoTitleRow) + 1) while exist: try: pseudoRef = worksheet[self.pseudoRefC + pseudoRefRow].value realRef = str(worksheet[self.realRefC + pseudoRefRow].value) if not pseudoRef or not realRef: exist = False except IndexError: exist = False if exist: if not realRef or realRef == 'None': missingRealRefNum.append(self.realRefC + pseudoRefRow) else: pseudo2Real[pseudoRef] = realRef pseudoRefRow = str(int(pseudoRefRow) + 1) excelReference['pseudo2Real'] = pseudo2Real errorText = "" if missingRef or missingCopy or missingType or missingDep or repeat or wrongSeqRow or missingRealRefNum: errorText = writeErrorMessage(missingRef, missingCopy, missingType, missingDep, repeat, wrongSeqRow, missingRealRefNum) return xmlFilePath, excelReference, errorText
def startNewExcelSheet(self, xmlFilePath, refInfo, wireSDInfo): """ parameters ---------- refGap: list """ refNumList = refInfo['name'] refGap = refInfo['gap'] numOfGap = len(refGap) ### if the number of gaps > the number of existing references, it's an error if numOfGap > len(refNumList): return "The number of missing refs: " + str( numOfGap) + " > the number of existing refs: " + str( len(refNumList)) ### get folder name and xml file name without extension/ name xlsm file path xmlFolderPath, xmlFileName = splitFileFolderAndName(xmlFilePath) xlsxFileName = xmlFileName + '_instruction.xlsm' xlsxFilePath = xmlFolderPath + '/' + xlsxFileName ### creat workbook and worksheet workbook = xlsxwriter.Workbook(xlsxFilePath) worksheet = workbook.add_worksheet(self.workSheetName) ### add cell format unlocked = workbook.add_format({ 'locked': 0, 'valign': 'vcenter', 'align': 'center' }) centerF = workbook.add_format({'valign': 'vcenter', 'align': 'center'}) centerHiddenF = workbook.add_format({ 'valign': 'vcenter', 'hidden': 1, 'align': 'center' }) titleF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#b8cce0', 'font_color': '#1f497d', 'bold': True, 'bottom': 2, 'bottom_color': '#82a5d0' }) topBorderF = workbook.add_format({'top': 2, 'top_color': '#82a5d0'}) copyBlockedF = workbook.add_format({ 'bg_color': '#a6a6a6', 'font_color': '#a6a6a6' }) missingTagAndRefF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#FFC7CE', 'font_color': '#9C0006', 'border': 1, 'border_color': '#b2b2b2' }) missingUnblockedF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#FFC7CE', 'locked': 0, 'border': 1, 'border_color': '#b2b2b2' }) missingDepBlockedF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#FFC7CE', 'locked': 1, 'hidden': 1, 'border': 1, 'border_color': '#b2b2b2' }) missingDepBlockedBlankF = workbook.add_format({ 'bg_color': '#FFC7CE', 'font_color': '#FFC7CE', 'locked': 1, 'hidden': 1, 'border': 1, 'border_color': '#b2b2b2' }) missingWireCountF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#FFC7CE', 'border': 1, 'border_color': '#b2b2b2' }) missingWireCountHiddenF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#FFC7CE', 'hidden': 1, 'border': 1, 'border_color': '#b2b2b2' }) existingWhiteBlockedF = workbook.add_format({ 'font_color': 'white', 'locked': 1, 'hidden': 1 }) appendTagAndRefF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'font_color': 'white', 'bg_color': '#92cddc', 'locked': 1, 'border': 1, 'border_color': '#b2b2b2' }) appendUnblockedF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#92cddc', 'locked': 0, 'border': 1, 'border_color': '#b2b2b2' }) appendBlockedF = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#92cddc', 'locked': 1, 'hidden': 1, 'border': 1, 'border_color': '#b2b2b2' }) appendHiddenZeroBlockedF = workbook.add_format({ 'bg_color': '#92cddc', 'font_color': '#92cddc', 'locked': 1, 'hidden': 1, 'border': 1, 'border_color': '#b2b2b2' }) pseudoRefLetter = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#c6efce', 'font_color': '#006100' }) pseudoCounts = workbook.add_format({ 'valign': 'vcenter', 'align': 'center', 'bg_color': '#c6efce' }) ### activate protection with password "elton" worksheet.protect('elton') ### set column width and protection wireTagC = re.findall("[a-zA-Z]+", self.wireTagCell)[0] worksheet.set_column(self.statusC + ':' + self.statusC, 9) worksheet.set_column(self.refC + ':' + self.refC, 20) worksheet.set_column(self.typeC + ':' + self.typeC, 14) worksheet.set_column(self.depC + ':' + self.depC, 17) worksheet.set_column(self.wireSCountC + ':' + self.wireSCountC, 12) worksheet.set_column(self.wireDCountC + ':' + self.wireDCountC, 12) worksheet.set_column(self.wireNewDcountC + ':' + self.wireNewDcountC, 16) worksheet.set_column(self.hiddenRowsC + ':' + self.hiddenRowsC, 5) worksheet.set_column(wireTagC + ':' + wireTagC, 10) worksheet.set_column(wireTagC + ':' + wireTagC, 10) ### write title worksheet.write(self.statusC + self.titleRow, 'Status', titleF) worksheet.write(self.refC + self.titleRow, 'Reference Number (R)', titleF) worksheet.write(self.copyC + self.titleRow, 'Copy (R)', titleF) worksheet.write(self.typeC + self.titleRow, 'Reference Type', titleF) worksheet.write(self.depC + self.titleRow, 'Dependent On (R)', titleF) worksheet.write(self.wireSCountC + self.titleRow, 'Wire Count S', titleF) worksheet.write(self.wireDCountC + self.titleRow, 'Wire Count D', titleF) worksheet.write(self.wireNewDcountC + self.titleRow, 'Wire New Count D', titleF) worksheet.write(self.wireTagCell, "Wire Count", centerF) worksheet.write(self.wireCountCell, wireSDInfo['total'], centerF) worksheet.write(self.xmlFilePathCell, 'XML: ' + xmlFilePath) ### write rows lastRefRow = int(self.titleRow) + int(refNumList[-1]) fstAppendRow = str(int(lastRefRow) + 1) lastAppendRow = str(int(lastRefRow) + len(refNumList) - numOfGap) lastHiddenRefRow = str(len(refNumList)) # Get pseudo Reference pseudo = refInfo['pseudo'] # A dictionary if pseudo: worksheet.set_column(self.pseudoRefC + ':' + self.pseudoRefC, 18) worksheet.set_column(self.realRefC + ':' + self.realRefC, 19) worksheet.set_column(self.pseudoCountC + ':' + self.pseudoCountC, 6) worksheet.set_column( self.wirePseudoCountSC + ':' + self.wirePseudoCountSC, 12) worksheet.set_column( self.wirePseudoCountDC + ':' + self.wirePseudoCountSC, 12) worksheet.write(self.pseudoRefC + self.pseudoTitleRow, 'Pseudo Reference (R)', titleF) worksheet.write(self.realRefC + self.pseudoTitleRow, 'Reference Number (R)', titleF) worksheet.write(self.pseudoCountC + self.pseudoTitleRow, 'Count', titleF) worksheet.write(self.wirePseudoCountSC + self.pseudoTitleRow, 'Wire Count S', titleF) worksheet.write(self.wirePseudoCountDC + self.pseudoTitleRow, 'Wire Count D', titleF) pseudoRefRowS = str(int(self.pseudoTitleRow) + 1) sortedPseudo = sorted(pseudo.keys()) numPseudo = len(sortedPseudo) for pseudoRef in sortedPseudo: worksheet.write(self.pseudoRefC + pseudoRefRowS, pseudoRef, pseudoRefLetter) worksheet.write(self.realRefC + pseudoRefRowS, None, unlocked) worksheet.write(self.pseudoCountC + pseudoRefRowS, pseudo[pseudoRef], pseudoCounts) worksheet.write(self.wirePseudoCountSC + pseudoRefRowS, len(wireSDInfo[pseudoRef]['s']), pseudoCounts) worksheet.write(self.wirePseudoCountDC + pseudoRefRowS, len(wireSDInfo[pseudoRef]['d']), pseudoCounts) f1 = 'COUNTIF($' + self.realRefC + '$' + str( int(self.pseudoTitleRow) + 1) + ':$' + self.realRefC + '$' + str( int(self.pseudoTitleRow) + numPseudo ) + ',' + self.realRefC + pseudoRefRowS + ')=1' f2 = 'COUNTIF($' + self.hiddenRefC + '$1' + ':$' + self.hiddenRefC + '$' + lastHiddenRefRow + ',' + self.realRefC + pseudoRefRowS + ')=1' pseudoRefFormula = '=AND(' + f1 + ', ' + f2 + ')' worksheet.data_validation( self.realRefC + pseudoRefRowS, { 'validate': 'custom', 'value': pseudoRefFormula, 'error_title': 'Warning', 'error_message': 'Reference does not exist or Duplicates!', 'error_type': 'stop' }) pseudoRefRowS = str(int(pseudoRefRowS) + 1) typeList = [ 'Default', 'NEWBENCH', 'AVX_S', 'AVX_D', 'PRSID_S', 'PRSID_D', 'TECDIA_S', 'TECDIA_D', 'PKGFLOOR', 'IC_S', 'IC_D', 'SIGE', 'RESIST_S', 'RESIST_D', 'TF_S', 'TF_D', 'COIN_S', 'COIN_D' ] refGapSet = set(refGap) refNumber = 1 refListIndex = 0 for rowN in range(int(self.firstInputRow), int(lastAppendRow) + 1): rowS = str(rowN) if rowN < int(fstAppendRow): if str(refNumber) in refGapSet: ### missing ref row worksheet.write(self.statusC + rowS, self.mTag, missingTagAndRefF) worksheet.write(self.refC + rowS, refNumber, missingTagAndRefF) worksheet.write(self.copyC + rowS, None, missingUnblockedF) worksheet.write(self.typeC + rowS, None, missingUnblockedF) worksheet.write(self.wireSCountC + rowS, 0, missingWireCountF) worksheet.write(self.wireDCountC + rowS, 0, missingWireCountF) ### formulas for dependon cells worksheet.write_formula(self.depC + rowS, '=' + self.copyC + rowS, missingDepBlockedF) worksheet.conditional_format( self.depC + rowS, { 'type': 'cell', 'criteria': 'equal to', 'value': 0, 'format': missingDepBlockedBlankF }) ### formulas for wire new D Count cell wireNewDFormula = '=IF(ISBLANK(' + self.copyC + rowS + '), 0, INDIRECT("' + self.wireDCountC + '"& ' + self.copyC + rowS + '+1))' worksheet.write_formula(self.wireNewDcountC + rowS, wireNewDFormula, missingWireCountHiddenF) ### wire formula for data validation. it prevents duplicates and anything outside the list f1 = 'COUNTIF($' + self.copyC + '$' + self.firstInputRow + ':$' + self.copyC + '$' + lastAppendRow + ',' + self.copyC + rowS + ')=1' f2 = 'COUNTIF($' + self.hiddenRefC + '$1' + ':$' + self.hiddenRefC + '$' + lastHiddenRefRow + ',' + self.copyC + rowS + ')=1' countFormula = '=AND(' + f1 + ', ' + f2 + ')' worksheet.data_validation( self.copyC + rowS, { 'validate': 'custom', 'value': countFormula, 'error_title': 'Warning', 'error_message': 'Reference number does not exist or Duplicates!', 'error_type': 'stop' }) # Data validation for types worksheet.data_validation( self.typeC + rowS, { 'validate': 'list', 'source': typeList, 'error_title': 'Warning', 'error_message': 'Type does not exist in the library!', 'error_type': 'stop' }) else: ### existing ref row worksheet.write(self.statusC + rowS, self.eTag, existingWhiteBlockedF) worksheet.write(self.refC + rowS, refNumber, centerF) worksheet.write(self.hiddenRefC + str(refListIndex + 1), int(refNumList[refListIndex]), existingWhiteBlockedF) worksheet.write(self.copyC + rowS, self.copyBlockedText, copyBlockedF) worksheet.write(self.typeC + rowS, refInfo['type'][refListIndex], unlocked) worksheet.write(self.depC + rowS, refInfo['dependon'][refListIndex], centerF) ### data validation for dep listF = 'COUNTIF($' + self.hiddenRefC + '$1' + ':$' + self.hiddenRefC + '$' + lastHiddenRefRow + ',' + self.depC + rowS + ')=1' worksheet.data_validation( self.depC + rowS, { 'validate': 'custom', 'value': listF, 'error_title': 'Warning', 'error_message': 'Reference does not exist!', 'error_type': 'stop' }) # Data validation for types worksheet.data_validation( self.typeC + rowS, { 'validate': 'list', 'source': typeList, 'error_title': 'Warning', 'error_message': 'Type does not exist in the library!', 'error_type': 'stop' }) ### formulas for Wire new D Count cell wireRefSCount = len(wireSDInfo[str(refNumber)]['s']) wireRefDCount = len(wireSDInfo[str(refNumber)]['d']) if pseudo: wireCountSFormula = '=IF(COUNTIF(' + self.realRefC + str(int(self.pseudoTitleRow) + 1) + ':' + self.realRefC + str(int(self.pseudoTitleRow) + numPseudo) + ', ' + str(refNumber) + ') > 0, '\ + str(wireRefSCount) + ' + INDIRECT("' + self.wirePseudoCountSC + '" & MATCH(' + str(refNumber) + ', ' + self.realRefC + str(int(self.pseudoTitleRow) + 1) + ':' + self.realRefC + str(int(self.pseudoTitleRow) + numPseudo)\ + ', 0) + ' + self.pseudoTitleRow +'), ' + str(wireRefSCount) + ')' wireCountDFormula = '=IF(COUNTIF(' + self.realRefC + str(int(self.pseudoTitleRow) + 1) + ':' + self.realRefC + str(int(self.pseudoTitleRow) + numPseudo) + ', ' + str(refNumber) + ') > 0, '\ + str(wireRefDCount) + ' + INDIRECT("' + self.wirePseudoCountDC + '" & MATCH(' + str(refNumber) + ', ' + self.realRefC + str(int(self.pseudoTitleRow) + 1) + ':' + self.realRefC + str(int(self.pseudoTitleRow) + numPseudo)\ + ', 0) + ' + self.pseudoTitleRow +'), ' + str(wireRefDCount) + ')' wireNewDFormula = '=IF(COUNTIF(' + self.copyC + self.firstInputRow + ':' + self.copyC + lastAppendRow + ', ' + str(refNumber) + ') > 0, 0, '\ + 'IF(COUNTIF(' + self.realRefC + str(int(self.pseudoTitleRow) + 1) + ':' + self.realRefC + str(int(self.pseudoTitleRow) + numPseudo) + ', ' + str(refNumber) + ') > 0, '\ + str(wireRefDCount) + ' + INDIRECT("' + self.wirePseudoCountDC + '" & MATCH(' + str(refNumber) + ', ' + self.realRefC + str(int(self.pseudoTitleRow) + 1) + ':' + self.realRefC + str(int(self.pseudoTitleRow) + numPseudo)\ + ', 0) + ' + self.pseudoTitleRow +'), ' + str(wireRefDCount) + '))' worksheet.write_formula(self.wireSCountC + rowS, wireCountSFormula, centerHiddenF) worksheet.write_formula(self.wireDCountC + rowS, wireCountDFormula, centerHiddenF) worksheet.write_formula(self.wireNewDcountC + rowS, wireNewDFormula, centerHiddenF) else: worksheet.write(self.wireSCountC + rowS, wireRefSCount, centerF) worksheet.write(self.wireDCountC + rowS, wireRefDCount, centerF) wireNewDFormula = '=IF(COUNTIF(' + self.copyC + self.firstInputRow + ':' + self.copyC + lastAppendRow + ', ' + str( refNumber) + ') > 0, 0, ' + str( wireRefDCount) + ')' worksheet.write_formula(self.wireNewDcountC + rowS, wireNewDFormula, centerHiddenF) refListIndex += 1 else: ### append section if not refGap or rowS == fstAppendRow: worksheet.write(self.statusC + rowS, self.aTag, appendTagAndRefF) worksheet.write(self.refC + rowS, refNumber, appendTagAndRefF) worksheet.write(self.copyC + rowS, None, appendUnblockedF) worksheet.write(self.typeC + rowS, None, appendUnblockedF) worksheet.write(self.wireSCountC + rowS, 0, appendBlockedF) worksheet.write(self.wireDCountC + rowS, 0, appendBlockedF) ### formulas for dep and Wire new D Count cell worksheet.write_formula(self.depC + rowS, '=' + self.copyC + rowS, appendBlockedF) wireNewDFormula = '=IF(ISBLANK(' + self.copyC + rowS + '), 0, INDIRECT("' + self.wireDCountC + '"& ' + self.copyC + rowS + '+1))' worksheet.write_formula(self.wireNewDcountC + rowS, wireNewDFormula, appendBlockedF) ### conditional formats for wire counts --> dont show values if there is no input in copy column wireConditionalFormula = '=AND(ISBLANK(' + self.copyC + rowS + '), NOT(ISBLANK(' + self.statusC + rowS + ')))' worksheet.conditional_format( self.depC + rowS, { 'type': 'formula', 'criteria': wireConditionalFormula, 'format': appendHiddenZeroBlockedF }) worksheet.conditional_format( self.wireSCountC + rowS, { 'type': 'formula', 'criteria': wireConditionalFormula, 'format': appendHiddenZeroBlockedF }) worksheet.conditional_format( self.wireDCountC + rowS, { 'type': 'formula', 'criteria': wireConditionalFormula, 'format': appendHiddenZeroBlockedF }) worksheet.conditional_format( self.wireNewDcountC + rowS, { 'type': 'formula', 'criteria': wireConditionalFormula, 'format': appendHiddenZeroBlockedF }) ### wire formula for datavalidation. it prevents duplicates and anything outside the list(it writes every row til the last appendable row becuase i dont want to data validation in VBA) f1 = 'COUNTIF($' + self.copyC + '$' + self.firstInputRow + ':$' + self.copyC + '$' + lastAppendRow + ',' + self.copyC + rowS + ')=1' f2 = 'COUNTIF($' + self.hiddenRefC + '$1' + ':$' + self.hiddenRefC + '$' + lastHiddenRefRow + ',' + self.copyC + rowS + ')=1' countFormula = '=AND(' + f1 + ', ' + f2 + ')' worksheet.data_validation( self.copyC + rowS, { 'validate': 'custom', 'value': countFormula, 'error_title': 'Warning', 'error_message': 'Reference number does not exist or Duplicates!', 'error_type': 'stop' }) # Data validation for types worksheet.data_validation( self.typeC + rowS, { 'validate': 'list', 'source': typeList, 'error_title': 'Warning', 'error_message': 'Type does not exist in the library!', 'error_type': 'stop' }) refNumber = refNumber + 1 ### hidden info in excel sheet if not refGap or int(fstAppendRow) > int( lastAppendRow): ### meaning no gaps or no appending section: worksheet.write(self.lastRefRowBeforeMacroCell, rowN, existingWhiteBlockedF) worksheet.write(self.appendRowCountCell, rowN, existingWhiteBlockedF) if refGap: ### add a topborder color if there is no appending section worksheet.write(self.statusC + fstAppendRow, None, topBorderF) worksheet.write(self.refC + fstAppendRow, None, topBorderF) worksheet.write(self.copyC + fstAppendRow, None, topBorderF) worksheet.write(self.typeC + fstAppendRow, None, topBorderF) worksheet.write(self.depC + fstAppendRow, None, topBorderF) worksheet.write(self.wireSCountC + fstAppendRow, None, topBorderF) worksheet.write(self.wireDCountC + fstAppendRow, None, topBorderF) worksheet.write(self.wireNewDcountC + fstAppendRow, None, topBorderF) else: worksheet.write(self.lastRefRowBeforeMacroCell, int(fstAppendRow), existingWhiteBlockedF) worksheet.write(self.appendRowCountCell, int(fstAppendRow), existingWhiteBlockedF) worksheet.write(self.lastAppendRowCell, int(lastAppendRow), existingWhiteBlockedF) worksheet.write(self.hiddenLastExistingRefRowCell, int(refNumList[-1]) + int(self.titleRow), existingWhiteBlockedF) ### import VBA workbook.add_vba_project('vbaProject.bin') workbook.set_vba_name("ThisWorkbook") worksheet.set_vba_name("Sheet1") ### add VBA buttons worksheet.insert_button(self.vbaButtonC + str(lastRefRow - 1), { 'macro': 'appendARow', 'caption': 'Append', 'width': 128, 'height': 40 }) worksheet.insert_button(self.vbaButtonC + str(int(fstAppendRow) + 1), { 'macro': 'undoRow', 'caption': 'UnAppend', 'width': 128, 'height': 40 }) ### merger two cells beteen the two buttons worksheet.merge_range( self.vbaButtonC + str(lastRefRow + 1) + ':' + chr(ord(self.vbaButtonC) + 1) + str(lastRefRow + 1), None) ### add comment copyTitleComment = 'Input a name of any existing refernces from the XML file (number only).\nAll gaps need to be filled out' worksheet.write_comment(self.copyC + self.titleRow, copyTitleComment, { 'author': 'Elton', 'width': 250, 'height': 50 }) worksheet.write_comment(self.depC + self.titleRow, "Double click to unlock or lock cells", { 'author': 'Elton', 'width': 173, 'height': 16 }) if int(fstAppendRow) <= int(lastAppendRow): worksheet.write_comment(self.statusC + fstAppendRow, 'Optional Section', { 'author': 'Elton', 'width': 100, 'height': 15 }) if refGap: worksheet.write_comment( self.statusC + str(int(refGap[0]) + int(self.titleRow)), 'Reference gaps in xml file', { 'author': 'Elton', 'width': 130, 'height': 15 }) ### close workbook. error if there is same workbook open try: workbook.close() except PermissionError: message = "Please close the existing Excel Workbook!" return message startfile(xlsxFilePath) return ""
def modifier(cls, xmlFilePath, referenceDictDFromExc): """Create a new XML file with modified information. Parameters ---------- xmlFilePath: string XML file path. referenceDictDFromExc: dictionary A collection of data from an Excel spreadsheet. referenceDictDFromExc data structure --> {'og': {'refNum':[type, dependon]}, 'add': {'refNum': [copyNum, type]}, 'newRefName': ['refNum'], 'pseudo2Real': {'A': '1', 'B': '2'}} Returns ------- message: string An error message. or newXmlFilePath: string A new XML file path. """ # Split a XML file path into folder path and file name without the extension xmlFolderPath, xmlFileName = splitFileFolderAndName(xmlFilePath) # Try to parse XML file. If there is an error, file format is incorrect. try: tree = ET.parse(xmlFilePath) except ET.ParseError: message = "File: " + xmlFileName + " - format incorrect!" return message except FileNotFoundError: message = "File Not Found! File Path: " + xmlFilePath return message # Create ElementTree object and find its root (highest node) root = tree.getroot() # Create lists of reference elements and wire elements referenceE = root.findall('ReferenceSystem') wireE = root.findall('Wire') # Get numbers of references and wires numOfRef = len(referenceE) numOfWire = len(wireE) referenceEDict = {} for r in referenceE: # Get name of the reference ref = r.find('Name').text if ref.isalpha(): # Remove pseudo reference elements when the name contains only letters root.remove(r) else: # Get number in string from name refNumber = ref[cls.prefixLen:] # Get type and dependency from Excel inputs typ, dep = referenceDictDFromExc['og'][refNumber] # If types dont match, modify type if r.find('Type').text != typ: r.find('Type').text = typ if dep: # If there is no dependon originally, create and dependon element in the reference if r.find('Dependon') == None: newDepEle = Element('Dependon') newDepEle.text = cls.prefix + dep r.insert(2, newDepEle) indent(newDepEle, 2) # If dependon values dont match, modify the original value elif r.find('Dependon').text != cls.prefix + dep: r.find('Dependon').text = cls.prefix + dep else: # If there is depenon originally, but not in Excel sheet, remove dependon element in XML if r.find('Dependon') != None: r.remove(r.find('Dependon')) referenceEDict[refNumber] = r # Read wire source and destination information, see function readWieSDInfo wireSDInfo = xmlTool.readWireSDInfo(wireE) # If there is pseudo references if 'pseudo2Real' in referenceDictDFromExc: # Get converion information dictionary --> {'A': '1', 'B': '2'} pseudoTrans = referenceDictDFromExc['pseudo2Real'] # Get a list of pseudo letters letters = pseudoTrans.keys() # Translate pseudo reference name to real reference name in wire's source or destination for letter in letters: if letter in wireSDInfo: translation = pseudoTrans[letter] # Translate reference name wirePseudoSrcIndex = wireSDInfo[letter]['s'] wirePseudoDesIndex = wireSDInfo[letter]['d'] if wirePseudoSrcIndex: modifyWireRef(letter, translation, wireE, wirePseudoSrcIndex, cls.prefix, 0) if wirePseudoDesIndex: modifyWireRef(letter, translation, wireE, wirePseudoDesIndex, cls.prefix, 1) # Modify wire source and destination index if translation in wireSDInfo: wireSDInfo[translation]['s'] = wireSDInfo[translation][ 's'] + wirePseudoSrcIndex wireSDInfo[translation]['d'] = wireSDInfo[translation][ 'd'] + wirePseudoDesIndex else: wireSDInfo[translation] = { 's': wirePseudoSrcIndex, 'd': wirePseudoDesIndex } # Get a dictionary references to add, addRefDict --> {'refNum': ['copyNum', type]} addRefDict = referenceDictDFromExc['add'] # referenceDictDFromExc['newRefName'] --> ['ref num in str'] for nName in referenceDictDFromExc['newRefName']: # Get reference name to be copied refNameToCopy = addRefDict[nName][0] # Get a new reference element copy = writeARefCopy(referenceEDict[refNameToCopy], refNameToCopy, nName, addRefDict[nName][1], cls.prefix) # Insert into reference tree root.insert(int(nName) - 1, copy) # Change wire destination if refNameToCopy in wireSDInfo: modifyWireRef(refNameToCopy, nName, wireE, wireSDInfo[refNameToCopy]['d'], cls.prefix, 1) # Create new XML file path and write data into it newXmlFilePath = xmlFolderPath + "/" + xmlFileName + "_new.xml" tree.write(newXmlFilePath) return newXmlFilePath