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
