Macro EasyAlias: Difference between revisions
TheMarkster (talk | contribs) (version 2022.07.31) |
TheMarkster (talk | contribs) (v2023.04.27) |
||
Line 41: | Line 41: | ||
# -*- coding: utf-8 -*- |
# -*- coding: utf-8 -*- |
||
import FreeCAD |
import FreeCAD |
||
import re |
|||
from PySide import QtGui |
from PySide import QtGui |
||
Line 46: | Line 47: | ||
EasyAlias.FCMacro.py |
EasyAlias.FCMacro.py |
||
This macro can be used to easily create aliases based on the |
This macro can be used to easily create aliases based on the contents of selected spreadsheet |
||
cells in the previous column. |
cells in the previous column. As an example, suppose you wish to have the following: |
||
A1: content = 'radius', B1: content = '5', alias = 'radius' |
A1: content = 'radius', B1: content = '5', alias = 'radius' |
||
Line 88: | Line 89: | ||
__title__ = "EasyAlias" |
__title__ = "EasyAlias" |
||
__author__ = "TheMarkster" |
__author__ = "TheMarkster and rosta" |
||
__url__ = "https://wiki.freecadweb.org/Macro_EasyAlias" |
__url__ = "https://wiki.freecadweb.org/Macro_EasyAlias" |
||
__Wiki__ = "https://wiki.freecadweb.org/Macro_EasyAlias" |
__Wiki__ = "https://wiki.freecadweb.org/Macro_EasyAlias" |
||
__date__ = " |
__date__ = "2023.04.27" #year.month.date |
||
__version__ = __date__ |
__version__ = __date__ |
||
CELL_ADDR_RE = re.compile(r"([A-Za-z]+)([1-9]\d*)") |
|||
CUSTOM_ALIAS_RE = re.compile(r".*\((.*)\)") |
|||
MAGIC_NUMBER = 64 |
|||
REPLACEMENTS = { |
|||
" ": "_", |
|||
".": "_", |
|||
"ä": "ae", |
|||
"ö": "oe", |
|||
"ü": "ue", |
|||
"Ä": "Ae", |
|||
"Ö": "Oe", |
|||
"Ü": "Ue", |
|||
"ß": "ss" |
|||
} |
|||
def getSpreadsheets(): |
|||
def getSelected(selected_sheet): |
|||
""" |
|||
"""returns a QModelIndex object or None if none are selected |
|||
Returns a set of selected spreadsheets in the active document or None if none is selected. |
|||
use [0] to get at first cell in the selection |
|||
:returns: a set of selected spreadsheets in the active document or None if none is selected |
|||
use [0].row() to get first cell's row |
|||
:rtype: set |
|||
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 |
|||
spreadsheets = set() |
|||
def getSelectedCellIndices(selected_sheet): |
|||
for selectedObject in Gui.Selection.getSelection(): |
|||
"""returns selected cell indices in the form of a list of tuples (row,col) |
|||
if selectedObject.TypeId == 'Spreadsheet::Sheet': |
|||
or None if no cells are selected. Raises exception if more than one column selected. |
|||
spreadsheets.add(selectedObject) |
|||
""" |
|||
elif selectedObject.TypeId == "App::Link": |
|||
sel = getSelected(selected_sheet) |
|||
linkedObject = selectedObject.LinkedObject |
|||
if not sel: |
|||
if linkedObject.TypeId == 'Spreadsheet::Sheet': |
|||
FreeCAD.Console.PrintWarning('Select the spreadsheet in the tree view in addition to selecting the cells in the active view.\n') |
|||
spreadsheets.add(linkedObject) |
|||
return [] |
|||
return spreadsheets |
|||
elif sel[0].column() != sel[-1].column(): |
|||
raise Exception('Multiple columns selected. Only cells from a single column are supported.') |
|||
# The original implementatin of a1_to_rowcol and rowcol_to_a1 can be found here: |
|||
# https://github.com/burnash/gspread/blob/master/gspread/utils.py |
|||
def a1_to_rowcol(label:str): |
|||
"""Translates a cell's address in A1 notation to a tuple of integers. |
|||
:param str label: A cell label in A1 notation, e.g. 'B1'. Letter case is ignored. |
|||
:returns: a tuple containing `row` and `column` numbers. Both indexed from 1 (one). |
|||
:rtype: tuple |
|||
Example: |
|||
>>> a1_to_rowcol('A1') |
|||
(1, 1) |
|||
""" |
|||
match = CELL_ADDR_RE.match(label) |
|||
row = int(match.group(2)) |
|||
column_label = match.group(1).upper() |
|||
column = 0 |
|||
for i, c in enumerate(reversed(column_label)): |
|||
column += (ord(c) - MAGIC_NUMBER) * (26**i) |
|||
return (row, column) |
|||
def rowcol_to_a1(row:int, column:int): |
|||
col = sel[0].column() |
|||
"""Translates a row and column cell address to A1 notation. |
|||
cellIndices=[] #will be list of tuples in form of (row,col) |
|||
:param row: The row of the cell to be converted. Rows start at index 1. |
|||
for c in sel: |
|||
:type row: int, str |
|||
cellIndices.append((c.row(),c.column())) |
|||
:param col: The column of the cell to be converted. Columns start at index 1. |
|||
return cellIndices |
|||
:type row: int, str |
|||
:returns: a string containing the cell's coordinates in A1 notation. |
|||
Example: |
|||
>>> rowcol_to_a1(1, 1) |
|||
A1 |
|||
""" |
|||
row = int(row) |
|||
def getCellIndexNextColumn(ci): |
|||
return (ci[0],ci[1]+1) #(ci[0],ci[1]+1) gets cellIndex of the next cell to the right (next column) |
|||
column = int(column) |
|||
dividend = column |
|||
column_label = "" |
|||
while dividend: |
|||
(dividend, mod) = divmod(dividend, 26) |
|||
if mod == 0: |
|||
mod = 26 |
|||
dividend -= 1 |
|||
column_label = chr(mod + MAGIC_NUMBER) + column_label |
|||
label = "{}{}".format(column_label, row) |
|||
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 obj.Object.TypeId: |
|||
return obj.Object |
|||
elif "App::Link" in obj.Object.TypeId: |
|||
if 'Spreadsheet::Sheet' in obj.Object.LinkedObject.TypeId: |
|||
return obj.Object.LinkedObject |
|||
return label |
|||
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 |
def textToAlias(text:str): |
||
# support for custom aliases between parentheses |
|||
chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' |
|||
match = CUSTOM_ALIAS_RE.match(text) |
|||
r,c = cellIndex |
|||
if |
if match: |
||
return match.group(1) |
|||
raise StandardError('Columns beyond Z are not supported at this time.') |
|||
address = chars[c]+str(r+1) |
|||
return address |
|||
for character in REPLACEMENTS: |
|||
#thanks to Ouriço for this modification to allow parentheses to define the alias |
|||
text = text.replace(character,REPLACEMENTS.get(character)) |
|||
#within the substring |
|||
return text |
|||
def setAlias(sheet, cellIndex, alias): |
|||
address = cellIndexToAddress(cellIndex) |
|||
# extract any text between () and use that as the alias. |
|||
# If brackets not found then use the original alias. |
|||
firstidx = alias.find('(') |
|||
secondidx = alias.find(')') |
|||
if (firstidx != -1) and (secondidx != -1) and (firstidx < secondidx): |
|||
alias = alias[firstidx + 1 : secondidx] |
|||
sheet.setAlias(address, alias) |
|||
def main(): |
|||
s = getSpreadsheet() |
|||
spreadsheets = getSpreadsheets() |
|||
if not s: |
|||
if not spreadsheets: |
|||
raise Exception('No spreadsheet selected. Please select a spreadsheet in the tree view.') |
|||
QtGui.QMessageBox.critical(None, "Error", |
|||
cellIndices = getSelectedCellIndices(s) |
|||
"No spreadsheet selected.\nPlease select a spreadsheet in the tree view.") |
|||
if len(cellIndices) == 0: |
|||
return |
|||
FreeCAD.Console.PrintWarning("Unable to get selected cell indices.\n"); |
|||
for spreadsheet in spreadsheets: |
|||
s.Document.openTransaction("EasyAlias") |
|||
for selectedCell in spreadsheet.ViewObject.getView().selectedCells(): |
|||
for ci in cellIndices: |
|||
contents = spreadsheet.getContents(selectedCell) |
|||
#FreeCAD.Console.PrintMessage("setting alias: "+s.Name+'['+str(cellIndexToAddress(getCellIndexNextColumn(ci)))+"] ---> "+getCellContent(s,ci).replace(' ','_')+"\n") |
|||
if contents: |
|||
# credit to red6rick for this bit of code to allow for skipped rows |
|||
alias = textToAlias(contents) |
|||
row, column = a1_to_rowcol(selectedCell) |
|||
if (len(s.getContents(addr)) > 0): |
|||
nextCell = rowcol_to_a1(row, column + 1) |
|||
try: |
|||
try: |
|||
setAlias(s, getCellIndexNextColumn(ci), str(getCellContent(s,ci).replace(' ','_').replace('.','_'))) #use e.g. content of A5 as alias for B5 |
|||
spreadsheet.setAlias(nextCell, alias) |
|||
except: |
|||
except: |
|||
FreeCAD.Console.PrintError("Error. Unable to set alias: "+getCellContent(s,ci).replace(' ','_')+" for spreadsheet: "+str(s)+" cell "+cellIndexToAddress(getCellIndexNextColumn(ci))+"\n") |
|||
QtGui.QMessageBox.critical(None, "Error", |
|||
FreeCAD.Console.PrintError("Remember, aliases cannot begin with a numeral or an underscore or contain any invalid characters.\n") |
|||
"Unable to set alias <i>" + alias + "</i> at cell " + nextCell + |
|||
s.Document.commitTransaction() |
|||
"<br>in spreadsheet <i>" + spreadsheet.FullName + "</i>." + |
|||
"<br><br><b>Remember, aliases cannot begin with a numeral or an " + |
|||
"underscore or contain any invalid characters.</b>") |
|||
App.ActiveDocument.recompute() |
|||
main() |
|||
App.ActiveDocument.recompute() |
|||
}} |
}} |
Revision as of 15:34, 27 April 2023
Description |
---|
Use this to quickly and easily create aliases for cells in your spreadsheets. It takes the text labels you will have already created in one column and uses those labels as aliases in the next column. Macro version: 2022.07.31 Last modified: 2022-07-31 FreeCAD version: All Download: ToolBar Icon Author: TheMarkster |
Author |
TheMarkster |
Download |
ToolBar Icon |
Links |
Macros recipes How to install macros How to customize toolbars |
Macro Version |
2022.07.31 |
Date last modified |
2022-07-31 |
FreeCAD Version(s) |
All |
Default shortcut |
None |
See also |
None |
Description
Use this to quickly and easily create aliases for cells in your spreadsheets. It takes the text labels you will have already created in one column and uses those labels as aliases in the next column. For example, the text labels in Column A can be used to create aliases for the cells in Column B. Since version 2022.03.21 if you include text inside parentheses only that text will be the alias. For example, "Height of top end (topHeight)" as the label (without the quotes) would make the alias of topHeight in the next column.
Usage
Highlight the cells containing the text labels and run the macro. Adjacent cells in the next column will now contain aliases made from the text values from the highlighted cells.
EasyAlias screenshot1, Text labels from Column A are used to create the aliases in Column B.
Script
Macro_EasyAlias.FCMacro
# -*- coding: utf-8 -*- import FreeCAD import re from PySide import QtGui """ EasyAlias.FCMacro.py This macro can be used to easily create aliases based on the contents of selected spreadsheet cells in the previous column. As an example, suppose you wish to have the following: A1: content = 'radius', B1: content = '5', alias = 'radius' A2: content = 'height', B1: content = '15', alias = 'height' The traditional way to set this up would be: Select A1 Enter radius Select B1 Enter 5 Right-click B1 Select properties Select Alias Enter radius click OK Select A2 Enter height Select B2 Enter 15 Right-click B2 Select Properties Select Alias Enter height Click OK Using this macro, the work flow becomes: Select A1 Enter radius Select B1 Enter 5 Select A2 Enter height Select B2 Enter 15 Select A1 through A2 Run the EasyAlias macro Done """ __title__ = "EasyAlias" __author__ = "TheMarkster and rosta" __url__ = "https://wiki.freecadweb.org/Macro_EasyAlias" __Wiki__ = "https://wiki.freecadweb.org/Macro_EasyAlias" __date__ = "2023.04.27" #year.month.date __version__ = __date__ CELL_ADDR_RE = re.compile(r"([A-Za-z]+)([1-9]\d*)") CUSTOM_ALIAS_RE = re.compile(r".*\((.*)\)") MAGIC_NUMBER = 64 REPLACEMENTS = { " ": "_", ".": "_", "ä": "ae", "ö": "oe", "ü": "ue", "Ä": "Ae", "Ö": "Oe", "Ü": "Ue", "ß": "ss" } def getSpreadsheets(): """ Returns a set of selected spreadsheets in the active document or None if none is selected. :returns: a set of selected spreadsheets in the active document or None if none is selected :rtype: set """ spreadsheets = set() for selectedObject in Gui.Selection.getSelection(): if selectedObject.TypeId == 'Spreadsheet::Sheet': spreadsheets.add(selectedObject) elif selectedObject.TypeId == "App::Link": linkedObject = selectedObject.LinkedObject if linkedObject.TypeId == 'Spreadsheet::Sheet': spreadsheets.add(linkedObject) return spreadsheets # The original implementatin of a1_to_rowcol and rowcol_to_a1 can be found here: # https://github.com/burnash/gspread/blob/master/gspread/utils.py def a1_to_rowcol(label:str): """Translates a cell's address in A1 notation to a tuple of integers. :param str label: A cell label in A1 notation, e.g. 'B1'. Letter case is ignored. :returns: a tuple containing `row` and `column` numbers. Both indexed from 1 (one). :rtype: tuple Example: >>> a1_to_rowcol('A1') (1, 1) """ match = CELL_ADDR_RE.match(label) row = int(match.group(2)) column_label = match.group(1).upper() column = 0 for i, c in enumerate(reversed(column_label)): column += (ord(c) - MAGIC_NUMBER) * (26**i) return (row, column) def rowcol_to_a1(row:int, column:int): """Translates a row and column cell address to A1 notation. :param row: The row of the cell to be converted. Rows start at index 1. :type row: int, str :param col: The column of the cell to be converted. Columns start at index 1. :type row: int, str :returns: a string containing the cell's coordinates in A1 notation. Example: >>> rowcol_to_a1(1, 1) A1 """ row = int(row) column = int(column) dividend = column column_label = "" while dividend: (dividend, mod) = divmod(dividend, 26) if mod == 0: mod = 26 dividend -= 1 column_label = chr(mod + MAGIC_NUMBER) + column_label label = "{}{}".format(column_label, row) return label def textToAlias(text:str): # support for custom aliases between parentheses match = CUSTOM_ALIAS_RE.match(text) if match: return match.group(1) for character in REPLACEMENTS: text = text.replace(character,REPLACEMENTS.get(character)) return text def main(): spreadsheets = getSpreadsheets() if not spreadsheets: QtGui.QMessageBox.critical(None, "Error", "No spreadsheet selected.\nPlease select a spreadsheet in the tree view.") return for spreadsheet in spreadsheets: for selectedCell in spreadsheet.ViewObject.getView().selectedCells(): contents = spreadsheet.getContents(selectedCell) if contents: alias = textToAlias(contents) row, column = a1_to_rowcol(selectedCell) nextCell = rowcol_to_a1(row, column + 1) try: spreadsheet.setAlias(nextCell, alias) except: QtGui.QMessageBox.critical(None, "Error", "Unable to set alias <i>" + alias + "</i> at cell " + nextCell + "<br>in spreadsheet <i>" + spreadsheet.FullName + "</i>." + "<br><br><b>Remember, aliases cannot begin with a numeral or an " + "underscore or contain any invalid characters.</b>") App.ActiveDocument.recompute() main()