Macro EasyAlias: Difference between revisions
TheMarkster (talk | contribs) (2022.02.28 -- update to support Links) |
TheMarkster (talk | contribs) (2023.11.06) |
||
(15 intermediate revisions by 3 users not shown) | |||
Line 4: | Line 4: | ||
<!--T:1--> |
<!--T:1--> |
||
{{Macro |
{{Macro |
||
|Name=EasyAlias |
|Name=Macro EasyAlias |
||
|Icon=easy-alias-icon.png |
|Icon=easy-alias-icon.png |
||
|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. |
|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. |
||
|Author=TheMarkster |
|Author=TheMarkster |
||
|Version= |
|Version=2023.11.06 |
||
|Date= |
|Date=2023-11-06 |
||
|FCVersion= |
|FCVersion=0.21 |
||
|Download=[https://www.freecadweb.org/wiki/images/5/5e/Easy-alias-icon.png ToolBar Icon] |
|Download=[https://www.freecadweb.org/wiki/images/5/5e/Easy-alias-icon.png ToolBar Icon] |
||
}} |
}} |
||
Line 17: | Line 17: | ||
<!--T:3--> |
<!--T:3--> |
||
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. |
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== <!--T:4--> |
==Usage== <!--T:4--> |
||
Line 32: | Line 32: | ||
==Script== <!--T:7--> |
==Script== <!--T:7--> |
||
<!--T:8--> |
|||
</translate> |
|||
ToolBar icon [[Image:easy-alias-icon.png]] |
ToolBar icon [[Image:easy-alias-icon.png]] |
||
</translate> |
|||
'''Macro_EasyAlias.FCMacro''' |
'''Macro_EasyAlias.FCMacro''' |
||
Line 40: | Line 41: | ||
# -*- coding: utf-8 -*- |
# -*- coding: utf-8 -*- |
||
import FreeCAD |
import FreeCAD |
||
import re |
|||
from PySide import QtGui |
from PySide import QtGui |
||
Line 45: | 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 87: | 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.11.06" #year.month.date |
||
__version__ = __date__ |
__version__ = __date__ |
||
__icon__ = "https://www.freecadweb.org/wiki/images/5/5e/Easy-alias-icon.png" |
|||
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): |
|||
"""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 |
|||
cellIndices=[] #will be list of tuples in form of (row,col) |
|||
column_label = "" |
|||
while dividend: |
|||
cellIndices.append((c.row(),c.column())) |
|||
(dividend, mod) = divmod(dividend, 26) |
|||
return cellIndices |
|||
if mod == 0: |
|||
mod = 26 |
|||
dividend -= 1 |
|||
column_label = chr(mod + MAGIC_NUMBER) + column_label |
|||
label = "{}{}".format(column_label, 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) |
|||
return label |
|||
def |
def textToAlias(text:str): |
||
# support for custom aliases between parentheses |
|||
"""return first selected spreadsheet object in document or None if none selected.""" |
|||
match = CUSTOM_ALIAS_RE.match(text) |
|||
selObj = FreeCADGui.Selection.getSelectionEx() |
|||
if |
if match: |
||
return |
return match.group(1) |
||
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 |
|||
for character in REPLACEMENTS: |
|||
def getCellContent(sheet, cellIndex): |
|||
text = text.replace(character,REPLACEMENTS.get(character)) |
|||
"""sheet is the spreadsheet object, cellIndex is a tuple in the form of (row,col), e.g. (3,2) to get cell |
|||
return text |
|||
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 main(): |
||
spreadsheets = getSpreadsheets() |
|||
chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' |
|||
if not spreadsheets: |
|||
QtGui.QMessageBox.critical(None, "Error", |
|||
if c > 26: |
|||
"No spreadsheet selected.\nPlease select a spreadsheet in the tree view.") |
|||
raise StandardError('Columns beyond Z are not supported at this time.') |
|||
return |
|||
address = chars[c]+str(r+1) |
|||
for spreadsheet in spreadsheets: |
|||
return address |
|||
for selectedCell in spreadsheet.ViewObject.getView().selectedCells(): |
|||
contents = spreadsheet.getContents(selectedCell) #get() throws exception on empty cell |
|||
def setAlias(sheet, cellIndex, alias): |
|||
if contents: |
|||
address = cellIndexToAddress(cellIndex) |
|||
contents = spreadsheet.get(selectedCell) |
|||
sheet.setAlias(address,alias) |
|||
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() |
|||
s = getSpreadsheet() |
|||
if not s: |
|||
raise Exception('No spreadsheet selected. Please select a spreadsheet in the tree view.') |
|||
cellIndices = getSelectedCellIndices(s) |
|||
if len(cellIndices) == 0: |
|||
FreeCAD.Console.PrintWarning("Unable to get selected cell indices.\n"); |
|||
for ci in cellIndices: |
|||
#FreeCAD.Console.PrintMessage("setting alias: "+s.Name+'['+str(cellIndexToAddress(getCellIndexNextColumn(ci)))+"] ---> "+getCellContent(s,ci).replace(' ','_')+"\n") |
|||
try: |
|||
setAlias(s, getCellIndexNextColumn(ci), str(getCellContent(s,ci).replace(' ','_').replace('.','_'))) #use e.g. content of A5 as alias for B5 |
|||
except: |
|||
FreeCAD.Console.PrintError("Error. Unable to set alias: "+getCellContent(s,ci).replace(' ','_')+" for spreadsheet: "+str(s)+" cell "+cellIndexToAddress(getCellIndexNextColumn(ci))+"\n") |
|||
FreeCAD.Console.PrintError("Remember, aliases cannot begin with a numeral or an underscore or contain any invalid characters.\n") |
|||
main() |
|||
App.ActiveDocument.recompute() |
|||
}} |
}} |
Latest revision as of 15:39, 6 November 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: 2023.11.06 Last modified: 2023-11-06 FreeCAD version: 0.21 Download: ToolBar Icon Author: TheMarkster |
Author |
TheMarkster |
Download |
ToolBar Icon |
Links |
Macros recipes How to install macros How to customize toolbars |
Macro Version |
2023.11.06 |
Date last modified |
2023-11-06 |
FreeCAD Version(s) |
0.21 |
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.11.06" #year.month.date __version__ = __date__ __icon__ = "https://www.freecadweb.org/wiki/images/5/5e/Easy-alias-icon.png" 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) #get() throws exception on empty cell if contents: contents = spreadsheet.get(selectedCell) 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()