Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
en:user_guide:worksheet_lists [2020/06/12 17:12] tiger [Referring to the current worksheet and row in a workbook or row list] |
en:user_guide:worksheet_lists [2021/01/10 14:44] (current) tiger [Know your process for one worksheet; then switch to multiple] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| {{indexmenu_n> | {{indexmenu_n> | ||
| - | ====== Working | + | ====== Working |
| Naming and using a worksheet is a very useful concept in case of a few, known worksheets. There are cases, though, when several or many worksheets with the same structure must be batch processed or created. Typical cases are consolidation or splitting tasks. | Naming and using a worksheet is a very useful concept in case of a few, known worksheets. There are cases, though, when several or many worksheets with the same structure must be batch processed or created. Typical cases are consolidation or splitting tasks. | ||
| Line 7: | Line 7: | ||
| ===== Consolidating data from multiple source worksheets to a single target worksheet ===== | ===== Consolidating data from multiple source worksheets to a single target worksheet ===== | ||
| - | The source worksheets that should be consolidated do already exist having their filenames at hand. Therefore best practice is to use the **WBook List Start** and **WBook List Next** process steps (under categories Worksheet and If / Lists on the [[en: | + | The source worksheets that should be consolidated do already exist having their filenames at hand. Therefore best practice is to use the **[[en: |
| Under the workbook list parameter the workbooks of the source worksheets can be specified using: | Under the workbook list parameter the workbooks of the source worksheets can be specified using: | ||
| * wildcard characters ? and * in filenames (e.g. c: | * wildcard characters ? and * in filenames (e.g. c: | ||
| * multiple folders or files separated by ; (e.g. c: | * multiple folders or files separated by ; (e.g. c: | ||
| - | |||
| - | |||
| ==== Know your process for one worksheet; then switch to multiple ==== | ==== Know your process for one worksheet; then switch to multiple ==== | ||
| - | The first step is to specify the process for one source worksheet just like it is done for a single worksheet case. Use **WSheet Use** to specify the only source | + | The first step is to specify the process for one source worksheet just like it is done for a single worksheet case. Use **[[en: |
| - | - replace **WSheet Use** for the only source worksheet by **WBook List Start** for all the worksheets at the beginning (NB: keep **WSheet Use** for the target!); | + | - replace **WSheet Use** for the only source worksheet by **WBook List Start** for all the worksheets at the beginning |
| - specify all the worksheets in the Workbook list parameter | - specify all the worksheets in the Workbook list parameter | ||
| - add **WBook List Next** to the end of the operations; | - add **WBook List Next** to the end of the operations; | ||
| - | - use the [[en: | + | - use the [[en: |
| - replace the target cell of cell and range operations to allow for | - replace the target cell of cell and range operations to allow for | ||
| * appending rows; | * appending rows; | ||
| * inserting rows; and | * inserting rows; and | ||
| - | * accumulating values instead of overwriting the same cell again and again. See detailed at the below sections. | + | * accumulating values instead of overwriting the same cell again and again. See these steps detailed at the sections |
| Done! | Done! | ||
| Line 83: | Line 81: | ||
| Cell Set Cell: wsTarget!C5 | Cell Set Cell: wsTarget!C5 | ||
| Value: 1 | Value: 1 | ||
| - | Cell Set Cell: wsTarget!C4 | + | Cell Set Cell: wsTarget!C4 |
| Value: [=wsSource!D5] | Value: [=wsSource!D5] | ||
| Line 103: | Line 101: | ||
| - | ===== Dividing | + | ===== Split data from a source worksheet to multiple target worksheets ===== |
| A typical task is to split an MS Excel source worksheet from an external source (it can be e.g. a supplier provided data set or an IT system report) according to certain criteria. The question is, how it is decided which target worksheets should be created? They do not exist before the process so the filenames are not known. Further, the set of the to be created files can be different every time based on source or supplementary info. Djeeni can create target worksheets in three ways: | A typical task is to split an MS Excel source worksheet from an external source (it can be e.g. a supplier provided data set or an IT system report) according to certain criteria. The question is, how it is decided which target worksheets should be created? They do not exist before the process so the filenames are not known. Further, the set of the to be created files can be different every time based on source or supplementary info. Djeeni can create target worksheets in three ways: | ||
| Line 110: | Line 108: | ||
| - If the target worksheets are to be created using the information from the source worksheet itself then the **WSheet Split** process step can be used | - If the target worksheets are to be created using the information from the source worksheet itself then the **WSheet Split** process step can be used | ||
| + | Let's see these ways in details. | ||
| ==== Creating target worksheets in a WBook List ==== | ==== Creating target worksheets in a WBook List ==== | ||
| Line 229: | Line 228: | ||
| WSheet Use | WSheet Use | ||
| WSheet Use | WSheet Use | ||
| - | Row List Start Djeeni name: rlOuter | + | Row List Start Djeeni name: rlOuter |
| - | Worksheet: wsOuter | + | |
| - | Row List Start Djeeni name: rlInner | + | Row List Start Djeeni name: rlInner |
| - | Worksheet: wsInner | + | |
| - | Cell Set Cell: weOuter!D[# | + | Cell Set |
| - | Cell Set Cell: wsInner!D# | + | Cell Set |
| </ | </ | ||