EasyAreaAlias/EasyAreaAlias.FCMacro

201 lines
7.6 KiB
Plaintext

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