FreeCAD: Spreadsheet WB

[last updated: 2024-07-30]
FreeCAD home page
FC Operation notes
working with multiple bodies
(link to:) FC.org spreadsheet WB
(link to:) FreeCAD Expressions
-----

This page still in edit, kind of a mess ...

--------------------------------------------------------------

• What is Parametrization?
  • If you create a model in Part Design, you do it by creating a sketch.
    When you add elements to your sketch, whether lines, circles, etc, you will almost always want to constrain them,
    which is the process of setting their size/dimensions and positions. These values/dimensions, their constraints, are their parameters.
  • If later you want to change those dimensions, you surely can do it by opening/editing your sketch,
    finding the dimension you want to change, changing it, and closing and saving the sketch.
  • But it's way easier to do it in a spreadsheet.
    Briefly, you create a spreadsheet in the Spreadsheet Workbench, define your dimensions/constraints (names and values) in the spreadsheet,
    then link/bind those spreadsheet values into your sketch using alias's.
    Then if you want to change a dimension in your model, all you have to do is open the spreadsheet, change the dimension there,
    and the change will be reflected in the drawing.
  • If you are going to modify your model, and want to change several dimensions,
    then using a spreadsheet is WAY easier.
  • But the real power of a spreadsheet is when you have inter-dependent parameters.
    That is, the value of one dimension depends on the value of some other dimension.

  • If you have a model, and it contains several objects/features,
    and the dimensions of the features depend on each other,
    that is, if, when the dimension of one of the features changes,
    then you will want the dimension of the dependent feature to change as well,
    then you must somehow tie the dimensions, their dependencies, together.
    You can do this in FC by using "references to external geometry,"
    however doing so puts you at risk of the DTNP, so that method must be avoided.

    The best way to do this, that is, to tie your dependent dimensions to each other,
    is to parametize your drawing using the Spreadsheet Workbench.
    
    This method will capture, in the spreadsheet, the dependencies between parameters

    It is helpful to distinguish between Dependent parameters, and Independent parameters.

  • When a model is described as being "parametric", this means you can change dimensions in a model
    by looking in the spreadsheet to find the dimension that you want to change,
    change the value there,
    and the change will be reflected in the model.
    But most importantly, if you have other dimensions that depend on your first one, then the other dimensions will be changed automatically according to their calculation formula
    to reflect the change you made in your first dimension.
    You don't have to edit a sketch or "re-draw" the model, you just go to your spreadsheet and change that number.
  • Another way of saying this:
    When you build your model, and you create an object or a feature, you need to define its position and dimensions.
    These are the parameters that define that object or feature.
    You can set these dimensions in several ways. The most direct way is to enter them directly into the sketch as constraints.

  --------------------------------------------------------------

• Spreadsheet Workbench.
  What follows here are my notes on how to use the spreadsheet WB to parametize your model:
  • Create a spreadsheet:
    • ... do it first...
    • ...
    • ...rename it...
  • Define parameter NAMES and VALUES:
    In this spreadsheet, you will record two things (at least):
    parameter/variable names
    parameter/variable values
  • Define ALIAS's:
    • Once you have defined the parameter names and values,
      you must link or "bind" them together.
      You do this by creating an "alias".
    • The alias is attached to the value, or more precisely to the cell that contains it.
    • The alias is itself a name. In fact, it is the name of the parameter that it is attached to.
            "alias name" = "parameter name"
      This isn't strictly necessary, and you may use different names for your parameters and their alias', but it makes things easier if you use the same names.
    • Use EasyAlias macro:
      The procedure/steps I've described here are customized to make it easy to use the EasyAlias macro.
      • This macro assumes that a given parameter name and its value
        will be listed in the spreadsheet on the same row,
        with the parameter name immediately to the left of the cell that contains the value of the parameter.
      • Load the EasyAlias macro ...
    • Use Alias's:
      Once the alias' have been defined, there are two ways you can use them:
      • Enter alias names to define constraints in your sketch edit:
        • Open a sketch. Draw some element. Select the element, then select the type of constraint you want to apply (eg. length)
          When the field opens for you to enter a value:
          type " = " (or click the Fn icon - the little circle to the right of the entry field)
          the Formula editor box will open
          type "ss.ss"
          the first "ss" is the name of your spreadsheet
          the second "ss" is the prefix you've added to all your parameter names
          a list will drop down to show all the alias/parameter names you've defined.
          select the one you want, press "enter" twice.
          • Now you can see why it's suggested to rename your spreadsheet "ss" - ie. something short and easy to type,
            and likewise to prefix your alias' with the same characters
          • This drop-down is so very helpful
            But you must enter at least two characters of your parameter name to get it to drop down,
            hence the suggestion to prefix all your parameter names with "ss" - easy and quick to type.
      • Enter alias names in the Data Panel:
        This ends up with the same result as using the method above, (that being entering alias names into your sketch directly).
        Highlight the sketch in the model tree.
        Click arrow beside Constraints in Sketch section of the Data panel to display constraints that have been defined.
        Click in the value column for the constraint you want to bind
        Type ss.ss as before to bring up list of defined parameters.
        Select the one you want

      ---------------------------------------------------------------

    • Expressions:
      • Trig and log functions:
        • enter into a cell in your spreadsheet:
          = tan([angle in degrees])
          cot is not a defined expression, however, cot = cos / sin
        • arc and hyperbolic functions can also be used.
      • Conditional formulae:
        • Defined conditional symbols:
          == ,   != ,   > ,   < ,   >= , and   <=.
          "equals", "not equal", "greater than", "less than", "greater than or equal", and "less than or equal"
        • format: " = [conditional statement] ? [value if True] : [value if False] "
          • "conditional statement" is: " [cell reference or value] [conditional symbol] [cell reference or value] "
          • "value if true or false" can be: a discrete value, or a cell reference to a value, or a calculation using cell references .
        • format: "= B1 == 1 ? 1 : 2"
          reads as: " if B1 = 1, then 1, else 2 "
        • or: "= B1 > B2 ? 1 : 2"
          reads as: " if B1 > B2, then 1, else 2 "
          Note if you instead enter: "=(B1 > B2) ? 1 : 2",
          you'll get an error.
          This is in contrast to FC behavior when you add unnecessary parentheses in cell calculations, where they're merely ignored, but do not give an error.
        • Further, instead of evaluating the expression to a discrete value, eg. "1", or "2",
          you can use a cell reference, eg:
              "= B1 == 1 ? B4 : 2 ",
          or even a calculation, eg:
              "= B1 == 1 ? B8/2 : 2"
        • You cannot, however, use text strings, eg. "Y" or "N"
      • Other functions:
        exp(x) Exponential function all
        log(x) Natural logarithm x > 0
        log10(x) Common logarithm x > 0
        pow(x; y) Exponentiation all
        sqrt(x) Square root x >= 0
        cbrt(x) int
      • Constants: " e " and " pi " can also be used in calculations

      ------------------------------------------------------------------------------------------------

    • Tips for moving things around in the spreadsheet:
      Although the spreadsheet created in the Spreadsheet WB Looks like
      a spreadsheet you might create in Excel or LibreOffice, There are some key differences:
      • When you want to enter a cell reference into a calculation for another cell,
        you cannot simply click in the referenced cell (like you can in Excel). You must type in its cell reference, eg. B15.
      • You must capitalize the column ID ("B"). as the software will not error-correct, like Excel and LibreOffice will, if you instead type "b".
      • You can insert and delete rows by right clicking and selecting your desired option.
        Calculated cells that have cell references in them will be updated correctly.
      • You can cut cells (ctrl-x) and paste them (ctrl-v) to new locations.
        Alias assignments will be preserved.
      • If you cut, move, and paste cells that are used in calculations for other cells, the calculations will break.
        If you cut, move, and paste cells that contain calculations based on other cells (that have not moved),
        the calculation refs will be preserved IF your cell row# is prefaced with "$", eg. "=B$30"
      • if, after you have defined an alias and used it to constrain some dimension in your drawing,
        you later decide you want to change the name of the paramater/alias,
        first change the parameter name in column A of your spreadsheet,
        then re-define the alias attached to the value cell in your spreadsheet.
        The new alias name will be automatically updated in your sketch.

      ---------------------------------------------------------------------------------

    • Example/demo:
      • Open a new drawing.
      • Create a spreadsheet:
              You don't Have to do this first,
              it just makes things cleaner IMO if you do.
        Select Spreadsheet WB.
        Create a new spreadsheet.
            It will show up in the Model tree.
        Rename the spreadsheet to something short and convenient.
            eg. "ss"
                This likewise is not a "must do", but it makes things easier later.

      • Define parameters:
        These are the "variable" or parameter names that you want to use to constrain dimensions in your sketch.

        
        click in spreadsheet cell A1
        enter name of parameter:

        Parameter names:
        • suggestion is to add the spreadsheet name as a prefix to parameter name:
          eg. if your spreadsheet is named "ss", and you want to define a parameter "widgetLength",
          then you would define/name your parameter: "ss_widgetLength".
          Again, this is not required, but it will make things way easier later.
        • Note you cannot use "-" (hyphen/dash) in parameter name

        You can enter next parameters in A2 (and below) now or later.

      • Enter desired parameter value:
        click in spreadsheet cell B1
        enter the value you want
            You may need to type "=" before the value number,
            else the spreadsheet may think you're entering text.
            If you enter a value (without an "="), and get error when trying to bind it,
            ie. your parameter does not show up in the list, then try with the "=".
        press enter

        Proceed similarly for the rest of the parameters you've defined.

      • Define parameter alias':
        This takes the name you've entered in column A, and converts it to an "alias", that is linked to the value of your parameter (in column B).
        Basically, when you enter a constraint into your sketch,
        instead of entering the actual value, eg. "45", you enter the name or alias that you've linked that value to.

        
        click in spreadsheet cell B1
        right-click --> properties --> alias
        enter alias name desired, press enter

        suggestion to use the same alias name as your parameter name

        Note the background color of the cell changes to yellow (after you've clicked outside the cell), indicating success.
        Note that if you mouse-over the alias cell, the tool tip will show the alias name you've defined.

        Highly recommended:
        Load the ... macro for quickly/easily defining alias'.

      • Create a model:
        Create a sketch, and draw the shape you want, suppose a simple rectangle.
        Set constraints for length and width.
        Close and save sketch.

      • Bind alias name to sketch constraint:
        • One way:
          Click to highlight sketch in model tree.
          In Data block, under Sketch, click arrow next to Constraints to show all constraints defined.
          Click in Value field of the constraint you want to bind.
          Type " = " or click the " Fn " icon at right end of the field to open the Formula Editor.
          Type " ss.ss "
          this is your spreadsheet name (ss), then a " . " (dot), then the first chars of your desired parameter alias (ss)

          A list will display of all the parameter alias' that you've defined that start with 'ss'.
          Select the one desired, click OK.

        • Another way:
          Open a sketch
          Double-click an aleady-defined constraint,
          click the Fn icon, or press "="
          enter "ss.ss"
          note the list of defined alias' will drop down
          select the one you want ...
        • Another way:
          If you have an object (line, whatever) that has not yet been constrained...
          Open sketch
          Select the object, ... ...

        ------------------------------------------------------------------------------------------------

      • Use a Calculation as a parameter value:
        Two ways to do this:
        • Calculate the parameter in the spreadsheet,
          using data contained in other cells in the spreadsheet.
          • If the desired calculated parameter does not already exist, create it as before in column A of your spreadsheet
                assign alias as before
          • If your calculation will use a value that does not already exist in the spreadsheet,
            create a new parameter in column A, named something relevant,
            and enter your value into column B.
            If you are only going to do the calculation in the spreadsheet,
            then you do not need to assign an alias.
          • Click in the value field (column B) for the calculated parameter
            Type " = " then the calculation expression you desire, based on other cells in the spreadsheet
            eg. type: "= B2 / 2" or "=B2 + B6"
            Press enter.
            Note: It does not work to click in B2 cell to select it for your calculation.
            You must type in "B2"
          • Bind the new alias to the desired drawing element as before.

          ------------------------------------------------------------------------------------------------

        • Change drawing by changing parameters in spreadsheet:
          Click the spreadsheet tab to open it.
          Click in the parameter value field for the parameter you want to change.
          Enter your new desired value for the parameter you're changing. Press Enter.
          Go back to the drawing tab and see that the changes have been made.

        ------------------------------------------------------------------------------------------------

      • Importing and exporting spreadsheet files:
        Preliminary notes...
        • in Spreadsheet WB:
          Open/view a tab in your drawing main window of the spreadsheet you want to export.
              If your drawing itself is shown in your main window, then the "export spreadsheet" option will be grayed out and unavailable in the spreadsheet menu.
          Spreadsheet --> Export spreadsheet
          enter/select location and name, click Save
        • This will create a file at the location you specified. It will not have an extension, however it will open as a text file, showing all the cells that were in your spreadsheet.
        • If you now do: spreadsheet --> import
          and select your newly saved, text spreadsheet file,
          it will create a new spreadsheet in the model tree of your drawing.
          formatting (cell colors, column widths, etc) will not be preserved.
          alias' will not be preserved.

        ------------------------------------------------------------------------------------------------

      • pulling a dimension from one sketch into the spreadsheet:
        • Here's what worked for me:
          My starting point in this was the link below, however I did not have to add the "Shape" and "Length" things...
          • First, the dimension you want to pull in must be constrained. It can be a reference constraint (blue)
          • Open/edit the sketch that has the constraint.
            The default name of the constraint, as listed in the sketch edit window under Constraints,
            will be something like: Constraint44 (2.55 mm)
            Left-click the constraint in the edit window to select it, then right-click and rename it,
            to eg. newLength (2.55 mm)
            Note you cannot use "-" hyphens in the name, though "_" underscores are OK
          • Use the value directly in a new sketch:
            • Open the sketch where you want to use this value,
            • select the constraint type, select the drawing element you want to constrain,
            • type " = " or click the formula editor icon,
            • At this point you have some apparently equally effective options:
              <<cradleSketch>>.Constraints.cradleW   ...   worked
              <<nanoVNA-01d>>#<<cradleSketch>>.Constraints.cradleW   ...   worked
              <<testSketch>>.Constraints.testD   ...   worked, even though the testSketch was in a different body...
              It turns out that...
              FC will not allow two sketches to have the same name in the same drawing,
              even if they are in different bodies.
              Therefore, a sketch name is unique/unambiguous within a drawing,
              and being such, it is by itself alone sufficient to fully designate a given sketch.
          • OR: enter it into a cell in your spreadsheet:
            Use the descriptors in green above into a spreadsheet cell (prefaced of course with " = ")
            In summary, click in a cell in your spreadsheet and enter:
            = <<[sketchName]>>.Constraints.[constraintName]     (use NO spaces)

            Alias them as usual.

          --------------

        • Reference material from: https://forum.freecad.org/viewtopic.php?t=19372
          • the dimension you want to pull into the spreadsheet must be constrained,
            though it can be a "reference" constraint (blue)
          • ??? "You can use the expression engine to set dimension based on other dimension.
            See here: https://wiki.freecad.org/index.php?title=Expressions"
          • "In order to get the right name, what needs to be typed in the expression, move your mouse cursor first to the respective edge in the 3D-view.
            FreeCAD will display the fully name of the edge in the lower left corner of the FreeCAD window."
            In my FC 20.2, the line in the lower left read:
            Preselected: nanoVNA_01.Body001.Pad003.Edge15 (-66mm, -45mm, 0mm)

            But FreeCAD does not tell all to you. As you can see in the dependency-graph, you need to add ".Shape" to the shown name of your edge and add ".Length" to get the length of the edge.

            Code:
            Pad.Shape.Edge1.Length

            ...
            I have made an example, that uses the nondriving constraint, in order to set the pad-height of the part "pad" in an expression. (click on the blue thingy in order to see the expression)
            The height of the box is then set to the length of an edge of the Pad.

            You have to use the same naming in the spreadsheet, in order to get the data into a cell.

          ------------------------------------------------------------------------------------------------
          • Links:
            • https://www.youtube.com/watch?v=Kr9V-lYbAGI - using spreadsheet workbench - Excellent. continue from 17:00
            • https://wiki.freecadweb.org/Spreadsheet_Workbench
            • https://wiki.freecad.org/Macro_EasyAlias

          -----------------------------------------------------------------------------------------------

          .
          

          .
          

          .
          

          eof