# -*- coding: utf-8 -*- import sys import FreeCAD from PySide import QtGui import re """ EasyAreaAlias.FCMacro.py This macro can be used to easily create aliases for an rectangular area of cells based on column header cells and row header cells in the selected spreadsheet. The aliases are built as the combination of the row header followed by a "_" followed by the column header. The header cells must be the cells left resp. above the selected cell range. As an example, suppose you want to build something from wooden board and lath In a spreadsheet you simply list each of the pieces by name and enter their dimensions eg. as Length, Width and Bread in a table row. In the top row Enter names (Length, Width, Bread). To assign the cells the alias just mark the data (excluding the headers) and run the macro. The macro cleans (to contain only valid characters) the headers (and rewrites it, so that the field is in synch with the alias, and finaly assignes the alias to each cell in the area. This macro is an extension of the macro EasyAlias from TheMarkster (https://wiki.freecadweb.org/Macro_EasyAlias) """ __title__ = "EasyAreaAlias" __author__ = "kludikovsky" __url__ = "https://wiki.freecadweb.org/Macro_EasyAlias" __Wiki__ = "https://wiki.freecadweb.org/Macro_EasyAlias" __date__ = "2020.12.22" #year.month.date __version__ = __date__ def getSpreadsheet(): """return first selected spreadsheet object in document or None if none selected.""" selObj = FreeCADGui.Selection.getSelectionEx() if not selObj: return None for obj in selObj: if 'Spreadsheet.Sheet' in str(type(obj.Object)): return obj.Object def getSelected(selected_sheet): """returns a QModelIndex object or None if none are selected use [0] to get at first cell in the selection use [0].row() to get first cell's row use [0].column() to get first cell's column use [-1] to get last cell in the selection """ mw=FreeCADGui.getMainWindow() mdiarea=mw.findChild(QtGui.QMdiArea) subw=mdiarea.subWindowList() widgets = [] for i in subw: if i.widget().metaObject().className() == "SpreadsheetGui::SheetView": widgets.append(i.widget()) if len(widgets) > 1: FreeCAD.Console.PrintError("Having more than one spreadsheet view open at a time can confuse the macro. Close the other sheet views and try again\n") return None elif len(widgets) == 1: return widgets[0].findChild(QtGui.QTableView).selectedIndexes() return None def getSelectedCellIndices(selected_sheet): """returns selected cell indices in the form of a list of tuples (row,col) or None if no cells are selected. Raises exception if more than one column selected. In addition it returns the col and row header indices """ sel = getSelected(selected_sheet) if not sel: FreeCAD.Console.PrintWarning('Select the spreadsheet in the tree view in addition to selecting the cells in the active view.\n') return [] """ >>>> KL >>>>> Here check for single column if there is something special to do Need to check if there is are titles (a column left and a row on top) """ elif sel[0].column() == 0: raise Exception('Selection cannot include Column A.') elif sel[0].row() == 0: raise Exception('Selection cannot include Row 1.') col = sel[0].column() cellIndices=[] #will be list of tuples in form of (row,col) for c in sel: cellIndices.append((c.row(),c.column())) upperLeft = min(cellIndices) lowerRight = max(cellIndices) headerColIndices=[] headerRowIndices=[] for i in range(upperLeft[1],lowerRight[1]+1): headerColIndices.append((upperLeft[0]-1,i)) for i in range(upperLeft[0],lowerRight[0]+1): headerRowIndices.append((i,upperLeft[1]-1)) return cellIndices, headerColIndices, headerRowIndices def getCellIndexNextColumn(ci): # --- not used here return (ci[0],ci[1]+1) #(ci[0],ci[1]+1) gets cellIndex of the next cell to the right (next column) def setCellContent(sheet, cellIndex, val): """sheet is the spreadsheet object, cellIndex is a tuple in the form of (row,col), e.g. (3,2) to set cell contents of cell(3,2) of sheet to val, in other words C2. Return value is return code of sheet.set.""" address = cellIndexToAddress(cellIndex) return sheet.set(address, val) def getCellContent(sheet, cellIndex): """sheet is the spreadsheet object, cellIndex is a tuple in the form of (row,col), e.g. (3,2) to get cell contents of cell(3,2) of sheet, in other words C2. Return value is content of cell.""" address = cellIndexToAddress(cellIndex) return sheet.get(address) def cellIndexToAddress(cellIndex): """ convert cell indices to spreadsheet adresses (3,2) => C2 """ chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' r,c = cellIndex if c > 26: raise StandardError('Columns beyond Z are not supported at this time.') address = chars[c]+str(r+1) return address def setAlias(sheet, cellIndex, alias): """ set the cell-alias """ address = cellIndexToAddress(cellIndex) sheet.setAlias(address,alias) def getHeaderText(sheet, cellIndices): """ retrieve the header text from a list of cell indices in the sheet """ headerText = [] for ci in cellIndices: # get content / value text = getCellContent(sheet,ci) # clean value text = re.sub(r"\W", "_", text, flags=re.I) text = re.sub(r"^[\d_]+", "", text, flags=re.I) # remove leading numbers text = re.sub(r" ", "_", text, flags=re.I) text = re.sub(r"ä", "ae", text, flags=re.I) text = re.sub(r"ö", "oe", text, flags=re.I) text = re.sub(r"ü", "ue", text, flags=re.I) text = re.sub(r"ß", "sz", text, flags=re.I) text = re.sub(r"Ä", "Ae", text, flags=re.I) text = re.sub(r"Ö", "Oe", text, flags=re.I) text = re.sub(r"Ü", "Ue", text, flags=re.I) text = re.sub(r"ä", "", text, flags=re.I) text = re.sub(r"ä", "", text, flags=re.I) text = re.sub(r"ä", "", text, flags=re.I) text = re.sub(r"\W", "", text, flags=re.I) # append to header_list headerText.append(text) # update the cell value try: setCellContent(sheet, ci, text) except: FreeCAD.Console.PrintError("Problem setting header value.\n") return headerText """ Main Task """ s = getSpreadsheet() if not s: raise Exception('No spreadsheet selected. Please select a spreadsheet in the tree view.') cellIndices, headerColIndices, headerRowIndices = getSelectedCellIndices(s) # get the indecies of the selected area as well as for the headers if len(cellIndices) == 0: FreeCAD.Console.PrintWarning("Unable to get selected cell indices.\n"); colHeader = getHeaderText(s,headerColIndices) # get the cleaned header texts from col rowHeader = getHeaderText(s,headerRowIndices) # and rows upperLeft = min(cellIndices) # needed for index entry computation for ci in cellIndices: rh = ci[0] - upperLeft[0] # compute entry in list ch = ci[1] - upperLeft[1] try: setAlias(s, ci, rowHeader[rh] + "_" + colHeader[ch]) except: FreeCAD.Console.PrintError("Error. Unable to set alias: "+rowHeader[rh] + "_" + colHeader[ch]+" for spreadsheet: "+str(s)+" cell "+cellIndexToAddress(ci)+"\n") FreeCAD.Console.PrintError("Remember, aliases cannot begin with a numeral or an underscore or contain any invalid characters.\n") App.ActiveDocument.recompute()