All About Traps and Columns
A trap is a set of properties that uniquely identify a cell (or column of cells) among all others in the worksheet. Property sets include the following:
-
Value
-
Font
-
Alignment
-
Cell Properties
For example, traps can be defined as discussed below given the sample data in this worksheet:
Order Number
We can trap Order Number (cell C18) with the following properties:
-
Value: is number and non-blank
-
Font: name= calibre, size = 11, color = black
-
Alignment: horizontal alignment = left
Ship Date
We can trap Ship Date (cell C19) with the following properties:
-
Value: is date
-
Font: name= calibre, size = 11, color = black
-
Alignment: horizontal alignment = left
Media
We can trap Media (cells C21-C24) with the following properties:
-
Value: is non-blank
-
Font: name= calibre, size = 11, color = black
-
Alignment: horizontal alignment = left
Notes
-
A demonstration of how to capture and refine data is provided here.
-
Worksheet Design can differentiate and extract all values from cells in column C, even if these have the same font and alignment properties, because they have different value types.
-
Worksheet Design can differentiate between the header (cell C20) and values (cells C21-C24), even if these have the same value type, because they have different Cell Background values.
-
Use the Cell Information panel, at the right of the Worksheet Design Window, to view cell properties and help you adjust your traps:
Defining Traps Manually
You have several options to define traps manually:
-
Trap on a single cell
-
Add Columns
-
Auto-define Table
Trapping on a SINGLE Cell
This is the most basic method for defining a trap.
Steps:
-
Select a cell. The cell will be highlighted.
-
Right-click and select Add Trap from the context menu or select the Add Trap tool:
The following happens:
-
Worksheet Design traps the cell based on its properties. Cells on the column with the same properties are also trapped:
-
This is indicated by the solid triangle at the upper-corner of the trapped cells.
-
The trap definition is added to the trap list for the current template:
-
-
-
Review and refine the trap.
As mentioned earlier a trap has several property components. You can refine these further to narrow down or expand the cells that can be trapped. Also, you can scroll down your worksheet and determine if any cells were not included, or if some cells you did not want to trap are included.
Refine the trap as needed to include or exclude the right cells. Refer to Refining Traps below for details on how you can do so.
-
Add columns. Select cells, right-click and select Add Column from the context menu or select the Add Column tool:
As you add columns, values are displayed on the Preview Grid.
Trapping by Adding Columns
An easy alternative for trapping many columns is to highlight a row and trap all the columns on that row.
Steps:
-
Select a row on the worksheet:
-
Right-click and select Add Columns from the context menu or select the Add Columns tool:
The following happens:
-
Worksheet Design traps cells on the row based on their properties.
-
-
The traps and columns are added and included in the traps and columns list for the current template:
-
-
Sample extracted data is displayed on the Preview Grid:
-
Note that the header was included. You can just select that row and exclude it, or set it as the header using the Define Headers tool.
-
-
Review and refine the traps and columns.
As mentioned earlier a trap has several property components. You can refine these further to narrow down or expand the rows that can be trapped. You can scroll down your worksheet and determine if any rows were not included, or if some row you did not want to trap are included as well. In the example above for example, the header row was included so you can just exclude this.
Refine the trap as needed to include or exclude the right rows. Refer to Refining Traps below for details on how you can do so.
Trapping by AUTO-DEFINING A Table
You can also highlight a table and have Worksheet Design define the traps and columns automatically. This method works best if you have blocks of cells that contain data.
Steps:
-
Highlight the table you want to trap.
-
Right-click and select Auto-Define Table from the context menu or select the Auto Define Table tool:
The following happens:
-
Worksheet Design traps cells on the table:
-
-
The traps and columns are added to the traps and columns list of the current template:
-
Sample data are displayed on the Preview Grid:
-
-
-
Review and refine the traps and columns.
As mentioned earlier a trap has several property components. You can refine these further to narrow down or expand the cells that can be trapped. Refer to Refining Traps below for details on how you can do so.
Trapping Columns in Multiple Lines
Worksheet Design allows you to trap several columns contained in multiple lines. In the example below, we will define an area and just trap one cell.
A demonstration of how to trap column data spanning multiple cells is shown here.
Steps:
-
Select the first line in your multiple-line area:
-
Drag the stencil (pink area) down to the last line:
-
Trap one of the cells in the stencil area, and then define the columns.
In this example we can trap the Customer name and then define the columns of the address rows:
Defining Traps by Capturing Appends
This technique works only after you have defined your detail template.
Steps:
-
Select Append from the New Template drop-down:
-
Select a cell above a detail or existing append. The cell will be highlighted.
-
Right-click and select Capture this Data as an Append from the context menu or select the Capture Data as an Append tool:
The trap is added and included in the trap list for the current template:
-
Review/refine the trap.
As mentioned earlier a trap has several property components. You can refine these further to narrow down or expand the cells that can be trapped. Refer to Refining Traps for details on how you can do so.
-
Define headers as needed. To do so, follow instructions here.