#!/usr/bin/env python3 # -*- coding: utf-8 -*- # *************************************************************************** # * Copyright (c) 2016 Ulrich Brammer * # * * # * This program is free software; you can redistribute it and/or modify * # * it under the terms of the GNU General Public License (GPL) * # * as published by the Free Software Foundation; either version 2 of * # * the License, or (at your option) any later version. * # * for detail see the LICENCE text file. * # * * # * FreeCAD is distributed in the hope that it will be useful, * # * but WITHOUT ANY WARRANTY; without even the implied warranty of * # * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * # * GNU Library General Public License for more details. * # * * # * You should have received a copy of the GNU Library General Public * # * License along with FreeCAD; if not, write to the Free Software * # * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 * # * USA * # ***************************************************************************/ __title__ = "FreeCAD Spreadsheet Workbench - XLSX importer" __author__ = "Ulrich Brammer " __url__ = ["https://www.freecad.org"] """ This library imports an Excel-XLSX-file into FreeCAD. Version 1.1, Nov. 2016: Changed parser, adds rad-unit to trigonometric functions in order to give the same result in FreeCAD. Added factor to arcus-function in order to give the same result in FreeCAD Added support for celltype "inlineStr" Version 1.0: It uses a minimal parser, in order to translate the IF-function into the different FreeCAD version. The other function-names are translated by search and replace. Features: - Imports tables defined inside Excel-document - Set alias definitions - Translate formulas known by FreeCAD. (see tokenDic as by version 1.1) - set cross table references - strings are imported - references to cells with strings are working known issues: - units are not imported - string support is minimal, the same as in FreeCAD """ import zipfile import xml.dom.minidom import FreeCAD as App try: import FreeCADGui except ValueError: gui = False else: gui = True # The sepToken structure is used in the tokenizer functions isKey and # getNextToken. # sepToken defines a search tree for separator tokens with length of 1 to 3 characters # it is also used as a list of separators between other tokens. sepToken = { "(": None, "=": None, "<": "branchLower", ">": "branchHigher", ")": None, # '"':None, # ';':None, " ": None, ",": None, # Separator on lists "!": None, # Connector to cells on other Sheets "+": None, "-": None, "*": None, "/": None, "^": None, } branchLower = {">": None, "=": None} branchHigher = {"=": None} # Needed to get a reference from a string to a dict treeDict = {"branchLower": branchLower, "branchHigher": branchHigher} # The tokenDic is used in parseExpr. # The tokenDic contains the following information: # levelchange: -1: tree down, 0, +1: tree up # replacement token # function-state: needed to do something special in the parser # 0 = normal, 1 = the pi-case, 2 = angle-function, # 3 = IF-function, 4 = IF-truecase, 5 IF-falsecase tokenDic = { "(": (1, "(", 0), "=": (0, "==", 0), "<>": (0, "!=", 0), ">=": (0, ">=", 0), "<=": (0, "<=", 0), "<": (0, "<", 0), ">": (0, ">", 0), ",": (0, ",", 0), ")": (-1, ")", 0), "!": (0, ".", 0), # Connector to cells on other Sheets # '"' :( 2 ,'', 0), "+": (0, "+", 0), "-": (0, "-", 0), "*": (0, "*", 0), "/": (0, "/", 0), "^": (0, "^", 0), "IF": (0, "", 3), "ABS": (0, "abs", 0), "ACOS": (0, "pi/180deg*acos", 0), "ASIN": (0, "pi/180deg*asin", 0), "ATAN": (0, "pi/180deg*atan", 0), "ATAN2": (0, "pi/180deg*atan2", 0), "COS": (0, "cos", 2), "COSH": (0, "cosh", 2), "EXP": (0, "exp", 0), "LOG": (0, "log", 0), "LOG10": (0, "log10", 0), "MOD": (0, "mod", 0), "POWER": (0, "pow", 0), "SIN": (0, "sin", 2), "SINH": (0, "sinh", 2), "SQRT": (0, "sqrt", 0), "TAN": (0, "tan", 2), "TANH": (0, "tanh", 2), "AVERAGE": (0, "average", 0), "COUNT": (0, "count", 0), "MAX": (0, "max", 0), "MIN": (0, "min", 0), "STDEVA": (0, "stddev", 0), "SUM": (0, "sum", 0), "PI": (0, "pi", 1), "_xlfn.CEILING.MATH": (0, "ceil", 0), "_xlfn.FLOOR.MATH": (0, "floor", 0), } class exprNode(object): """This defines a tree class for expression parsing. A tree is built, to step down into the levels of the expression.""" def __init__(self, parent, state, actIndex): self.state = state # see comment: State used for Angle-functions and IF-function self.parent = parent # Parent tree node self.lIndex = actIndex # Index to the list of tokens self.result = "" class FormulaTranslator(object): """This class translates a cell-formula from Excel to FreeCAD.""" def __init__(self): self.tokenList = ["="] def translateForm(self, actExpr): self.getNextToken(actExpr) # print("tokenList: ", self.tokenList) self.resultTree = exprNode(None, 0, 1) self.resultTree.result = self.tokenList[0] self.parseExpr(self.resultTree) # print('parseResult: ', self.resultTree.result) return self.resultTree.result def getNextToken(self, theExpr): """This is the recursive tokenizer for an excel formula. It appends all identified tokens to self.tokenList.""" # print('next Token theExpr: ', theExpr) # print('actTList: ', self.tokenList) tokenComplete = False keyToken = False if len(theExpr) > 0: theTok = theExpr[0] theExpr = theExpr[1:] if theTok in sepToken: keyToken = True branch = sepToken[theTok] while branch: # print(branch, ' theExpr[0]: ',theExpr[0]) if theExpr[0] in treeDict[branch]: branch = treeDict[branch][theExpr[0]] theTok = theTok + theExpr[0] theExpr = theExpr[1:] else: branch = None tokenComplete = True self.tokenList.append(theTok) self.getNextToken(theExpr) else: if len(theExpr) > 0: while not tokenComplete: if not self.isKey(theExpr): theTok = theTok + theExpr[0] theExpr = theExpr[1:] if len(theExpr) == 0: tokenComplete = True else: tokenComplete = True self.tokenList.append(theTok) self.getNextToken(theExpr) def isKey(self, theExpr): # print('look up: ', theExpr) keyToken = False lenExpr = len(theExpr) if theExpr[0] in sepToken: branch = sepToken[theExpr[0]] if branch is None: keyToken = True else: # print('There is a branch. look up: ', theExpr[1]) if (lenExpr > 1) and (theExpr[1] in treeDict[branch]): branch = treeDict[branch][theExpr[1]] if branch is None: keyToken = True else: if (lenExpr > 2) and (theExpr[2] in treeDict[branch]): keyToken = True else: keyToken = True return keyToken def parseExpr(self, treeNode): token = self.tokenList[treeNode.lIndex] treeNode.lIndex += 1 if token in tokenDic: lChange, newToken, funcState = tokenDic[token] else: lChange = 0 newToken = token funcState = 0 # print('treeNode.state: ', treeNode.state, ' my.index: ', treeNode.lIndex-1, ' ', token, ' fState: ', funcState) if token == ",": if treeNode.state == 4: newToken = ":" treeNode.state = 6 if treeNode.state == 3: newToken = "?" treeNode.state = 4 if funcState == 3: funcState = 0 newNode = exprNode(treeNode, 3, treeNode.lIndex) self.parseIF(newNode) else: treeNode.result = treeNode.result + newToken if funcState == 2: funcState = 0 newNode = exprNode(treeNode, 2, treeNode.lIndex) self.parseAngle(newNode) treeNode.result = treeNode.result + ")" elif funcState == 1: treeNode.lIndex += 2 # do skip the 2 parentheses of the PI() if lChange == -1: # print 'state: ', treeNode.state, 'parent.result: ', treeNode.parent.result, ' mine: ', treeNode.result treeNode.parent.result = treeNode.parent.result + treeNode.result treeNode.parent.lIndex = treeNode.lIndex # print('Go one level up, state: ', treeNode.state) if treeNode.state < 2: # print(' Look up more token above') if treeNode.lIndex < len(self.tokenList): self.parseExpr(treeNode.parent) elif lChange == 1: # print('Go one level down') newNode = exprNode(treeNode, 1, treeNode.lIndex) self.parseExpr(newNode) treeNode.lIndex = newNode.lIndex else: if treeNode.lIndex < len(self.tokenList): # print('parse to the end') self.parseExpr(treeNode) def parseIF(self, treeNode): # print('IF state: ', treeNode.state) treeNode.result = treeNode.result + "(" treeNode.lIndex += 1 self.parseExpr(treeNode) # print('IF result: ', treeNode.result) return def parseAngle(self, treeNode): # print('Angle state: ', treeNode.state) treeNode.result = treeNode.result + "(1rad*(" treeNode.lIndex += 1 self.parseExpr(treeNode) # print('angle result: ', treeNode.result) def getText(nodelist): rc = [] for node in nodelist: if node.nodeType == node.TEXT_NODE: rc.append(node.data) return "".join(rc) def handleWorkSheet(theDom, actSheet, strList): rows = theDom.getElementsByTagName("row") for row in rows: handleCells(row.getElementsByTagName("c"), actSheet, strList) def handleCells(cellList, actCellSheet, sList): for cell in cellList: cellAtts = cell.attributes refRef = cellAtts.getNamedItem("r") ref = getText(refRef.childNodes) refType = cellAtts.getNamedItem("t") if refType: cellType = getText(refType.childNodes) else: cellType = "n" # FIXME: some cells don't have t and s attributes # print("reference: ", ref, ' Cell type: ', cellType) if cellType == "inlineStr": iStringList = cell.getElementsByTagName("is") # print('iString: ', iStringList) for stringEle in iStringList: tElement = stringEle.getElementsByTagName("t")[0] theString = getText(tElement.childNodes) # print('theString: ', theString) actCellSheet.set(ref, theString) formulaRef = cell.getElementsByTagName("f") if len(formulaRef) == 1: theFormula = getText(formulaRef[0].childNodes) # print("theFormula: ", theFormula) fTrans = FormulaTranslator() actCellSheet.set(ref, fTrans.translateForm(theFormula)) else: valueRef = cell.getElementsByTagName("v") # print('valueRef: ', valueRef) if len(valueRef) == 1: valueRef = cell.getElementsByTagName("v")[0] if valueRef: theValue = getText(valueRef.childNodes) # print("theValue: ", theValue) if cellType == "n": actCellSheet.set(ref, theValue) if cellType == "s": actCellSheet.set(ref, (sList[int(theValue)])) def handleWorkBook(theBook, sheetDict, Doc): theSheets = theBook.getElementsByTagName("sheet") # print("theSheets: ", theSheets) for sheet in theSheets: sheetAtts = sheet.attributes nameRef = sheetAtts.getNamedItem("name") sheetName = getText(nameRef.childNodes) # print("table name: ", sheetName) idRef = sheetAtts.getNamedItem("sheetId") sheetFile = "sheet" + getText(idRef.childNodes) + ".xml" # print("sheetFile: ", sheetFile) # add FreeCAD-spreadsheet sheetDict[sheetName] = (Doc.addObject("Spreadsheet::Sheet", sheetName), sheetFile) theAliases = theBook.getElementsByTagName("definedName") for theAlias in theAliases: aliAtts = theAlias.attributes nameRef = aliAtts.getNamedItem("name") aliasName = getText(nameRef.childNodes) # print("aliasName: ", aliasName) aliasRef = getText(theAlias.childNodes) # aliasRef can be None if aliasRef and "$" in aliasRef: refList = aliasRef.split("!$") addressList = refList[1].split("$") # print("aliasRef: ", aliasRef) # print('Sheet Name: ', refList[0]) # print('Address: ', addressList[0] + addressList[1]) actSheet, sheetFile = sheetDict[refList[0]] actSheet.setAlias(addressList[0] + addressList[1], aliasName) def handleStrings(theStr, sList): print("process Strings: ") stringElements = theStr.getElementsByTagName("t") for sElem in stringElements: print("string: ", getText(sElem.childNodes)) sList.append(getText(sElem.childNodes)) def open(nameXLSX): if len(nameXLSX) > 0: z = zipfile.ZipFile(nameXLSX) theDoc = App.newDocument() sheetDict = dict() stringList = [] theBookFile = z.open("xl/workbook.xml") theBook = xml.dom.minidom.parse(theBookFile) handleWorkBook(theBook, sheetDict, theDoc) theBook.unlink() if "xl/sharedStrings.xml" in z.namelist(): theStringFile = z.open("xl/sharedStrings.xml") theStrings = xml.dom.minidom.parse(theStringFile) handleStrings(theStrings, stringList) theStrings.unlink() for sheetSpec in sheetDict: # print("sheetSpec: ", sheetSpec) theSheet, sheetFile = sheetDict[sheetSpec] f = z.open("xl/worksheets/" + sheetFile) myDom = xml.dom.minidom.parse(f) handleWorkSheet(myDom, theSheet, stringList) myDom.unlink() z.close() # This is needed more than once, otherwise some references are not calculated! theDoc.recompute() theDoc.recompute() theDoc.recompute() return theDoc def insert(nameXLSX, docname): try: theDoc = App.getDocument(docname) except NameError: theDoc = App.newDocument(docname) App.ActiveDocument = theDoc sheetDict = dict() stringList = [] z = zipfile.ZipFile(nameXLSX) theBookFile = z.open("xl/workbook.xml") theBook = xml.dom.minidom.parse(theBookFile) handleWorkBook(theBook, sheetDict, theDoc) theBook.unlink() if "xl/sharedStrings.xml" in z.namelist(): theStringFile = z.open("xl/sharedStrings.xml") theStrings = xml.dom.minidom.parse(theStringFile) handleStrings(theStrings, stringList) theStrings.unlink() for sheetSpec in sheetDict: # print("sheetSpec: ", sheetSpec) theSheet, sheetFile = sheetDict[sheetSpec] f = z.open("xl/worksheets/" + sheetFile) myDom = xml.dom.minidom.parse(f) handleWorkSheet(myDom, theSheet, stringList) myDom.unlink() z.close() # This is needed more than once, otherwise some references are not calculated! theDoc.recompute() theDoc.recompute() theDoc.recompute()