Macro EasyAlias: Difference between revisions

From FreeCAD Documentation
(2022.02.28 -- update to support Links)
(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=2020.10.06
|Version=2023.11.06
|Date=2020-10.06
|Date=2023-11-06
|FCVersion=All
|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. For example, the text labels in Column A can be used to create aliases for the cells in Column B.
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 content of selected spreadsheet
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:
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__ = "2022.02.28" #year.month.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)


col = sel[0].column()
column = int(column)
dividend = column
cellIndices=[] #will be list of tuples in form of (row,col)
for c in sel:
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 getSpreadsheet():
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 not selObj:
if match:
return None
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 cellIndexToAddress(cellIndex):
def main():
spreadsheets = getSpreadsheets()
chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
r,c = cellIndex
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

Other languages:

Macro EasyAlias

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
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

ToolBar icon

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()