Djeeni Documentation

User Tools

Site Tools

Trace:

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
en:process_steps:worksheet:wsheet_use [2021/05/24 10:12]
tiger [Creating a new workbook with its first worksheet] 		en:process_steps:worksheet:wsheet_use [2024/02/18 01:56] (current)
tiger
Line 1: Line 1:
 {{indexmenu_n>1}} {{indexmenu_n>1}}
 ====== WSheet Use ====== ====== WSheet Use ======
- 
-**See the older version 1 of this step (before May 2021) below**  
  
 WSheet Use is the most frequent process step in Djeeni to tell that the process has something to do with a worksheet. It does much more than just 'open' an existing workbook with a specified worksheet in it: WSheet Use is the most frequent process step in Djeeni to tell that the process has something to do with a worksheet. It does much more than just 'open' an existing workbook with a specified worksheet in it:
Line 18: Line 16:
     * you can specify dynamic path like c:\finance\budget\[=wsSettings!B4]\plan.xlsx to use a value from another (parameter) worksheet; in this case the value of cell B4 from the worksheet identified by the Djeeni name wsSettings; or     * you can specify dynamic path like c:\finance\budget\[=wsSettings!B4]\plan.xlsx to use a value from another (parameter) worksheet; in this case the value of cell B4 from the worksheet identified by the Djeeni name wsSettings; or
     * even combine Excel functions with Djeeni formulas like c:\finance\budget\[+FORMAT(TODAY(),"yyyy")]\actual.xlsx to access the current year folder on the day the process runs.     * even combine Excel functions with Djeeni formulas like c:\finance\budget\[+FORMAT(TODAY(),"yyyy")]\actual.xlsx to access the current year folder on the day the process runs.
-  * **File type**: Djeeni currently supports standard XLSX and CSV (Comma Separated Values) files. We are continuously expanding the covered file types.+  * **File type**: Djeeni currently supports standard XLSX, XLS and CSV (Comma Separated Values) or TXT files. We are continuously expanding the covered file types.
   * **Using Template**: Fill this optional parameter if you want to copy the content and formatting of another worksheet to it. It can be used only for Target worksheets (see the next parameter). The template data will overwrite the existing content. If you use it then you should follow the Excel syntax by adding the worksheet Excel name behind the workbook path separated by an exclamation mark. Example: if you want to use the worksheet **EmptyBudget** from the workbook **c:\finance\templates\budget.xlsx** then this parameter gets the value **c:\finance\templates\budget.xlsx!EmptyBudget**.   * **Using Template**: Fill this optional parameter if you want to copy the content and formatting of another worksheet to it. It can be used only for Target worksheets (see the next parameter). The template data will overwrite the existing content. If you use it then you should follow the Excel syntax by adding the worksheet Excel name behind the workbook path separated by an exclamation mark. Example: if you want to use the worksheet **EmptyBudget** from the workbook **c:\finance\templates\budget.xlsx** then this parameter gets the value **c:\finance\templates\budget.xlsx!EmptyBudget**.
   * **Use worksheet as**: You can choose from Source and Target. Source worksheets will be protected by Djeeni and their data will not be accidentally overwritten or otherwise modified. Target worksheets can still be used to read data from them for processing.   * **Use worksheet as**: You can choose from Source and Target. Source worksheets will be protected by Djeeni and their data will not be accidentally overwritten or otherwise modified. Target worksheets can still be used to read data from them for processing.
   * **Target worksheet Exists / Not exists action**: It can be specified what should happen if a target worksheet already exists or does not exist yet. Possible settings:   * **Target worksheet Exists / Not exists action**: It can be specified what should happen if a target worksheet already exists or does not exist yet. Possible settings:
-    * //If the worksheet does exist then use it as is; If the worksheet does not exist then stop with error:// this option is useful when we want to add data to an existing worksheet +    * **Fill (Use as is/Error): ** //If the worksheet does exist then use it as is; If the worksheet does not exist then stop with error:// this option is useful when we want to add data to an existing worksheet 
-    * //If the worksheet does exist then use it as is; If the worksheet does not exist then create it:// this option is useful when we want to create a worksheet and then add data to it (e.g. start a new worksheet every month and then add daily data to it) +    * **Append (Use as is/Create): **//If the worksheet does exist then use it as is; If the worksheet does not exist then create it:// this option is useful when we want to create a worksheet and then add data to it (e.g. start a new worksheet every month and then add daily data to it) 
-    * //If the worksheet does exist then delete and recreate it; If the worksheet does not exist then create it:// this option is useful when we want to create the worksheet always from scratch (e.g. if the source is modified after an error is discovered in it then everything should be reprocessed again) +    * **Overwrite(Delete and Recreate/Create): **//If the worksheet does exist then delete and recreate it; If the worksheet does not exist then create it:// this option is useful when we want to create the worksheet always from scratch (e.g. if the source is modified after an error is discovered in it then everything should be reprocessed again) 
-    * //If the worksheet does exist then stop with Error; If the worksheet does not exist then create it:// this option avoids accidentally overwriting an existing worksheet (probably an older version that must be kept)+    * **Create(Error/Create): **//If the worksheet does exist then stop with Error; If the worksheet does not exist then create it:// this option avoids accidentally overwriting an existing worksheet (probably an older version that must be kept)
   * **Options**: You can alter the behaviour of WSheet Use by specifying one or more of the following options separated by **;**:   * **Options**: You can alter the behaviour of WSheet Use by specifying one or more of the following options separated by **;**:
     * **UpdateLinks**: When the worksheet is opened the data from external links will be updated     * **UpdateLinks**: When the worksheet is opened the data from external links will be updated
Line 47: Line 45:
 ==== Adding a new worksheet to an existing workbook ==== ==== Adding a new worksheet to an existing workbook ====
  
-As it will be a new worksheet the **Use worksheet as** parameter must be set to **Target**. The **Target worksheet Exists / Not exists action** can have two values: either **Delete and Recreate / Create** or **Error / Create**. You do not have to take care of the workbook. If it does exist then Djeeni simply uses it. If it does not exist then Djeeni will create it as your request to have a new worksheet in this workbook implies that the workbook also must exist.+As it will be a new worksheet the **Use worksheet as** parameter must be set to **Target**. The **Target worksheet Exists / Not exists action** can have two values: either **Delete and Recreate / Create** or **Error / Create**. You do not have to take care of the workbook. If the workbook does exist then Djeeni simply uses it. If the workbook does not exist then Djeeni will create it as your request to have a new worksheet in this workbook implies that the workbook also must exist. 
 ==== Creating a new workbook with its first worksheet ==== ==== Creating a new workbook with its first worksheet ====
  
Line 55: Line 54:
  
 CSV files contain exactly one 'worksheet'. Unlike MS Excel, Djeeni lets you give this worksheet an Excel name next to its Djeeni name but currently it has no use (future versions may utilize it). Simply specify the Excel name parameter as 1. Choose the File Type parameter value 'csv'. CSV files can only be Source. Specify the **CSV:** option to avoid incorrect import. CSV files contain exactly one 'worksheet'. Unlike MS Excel, Djeeni lets you give this worksheet an Excel name next to its Djeeni name but currently it has no use (future versions may utilize it). Simply specify the Excel name parameter as 1. Choose the File Type parameter value 'csv'. CSV files can only be Source. Specify the **CSV:** option to avoid incorrect import.
-===== WSheet Use - version 1 ===== 
- 
-**Note: this version is still supported but made obsolete. For new processes use the latest version and also update your processes to the new version.** 
- 
-WSheet Use is an acronym for worksheet use - in this process step the user can specify the worksheet and the workbook that will be used in the next steps of the process and define the parameters, such as writing permissions (declaring the worksheet either as a source or a target), updating links and passwords. 
- 
-{{djeeni:wsheet_use.png}} 
- 
-Every worksheet that is used during a process (either source or target) should be declared within Djeeni with the following parameters: 
-  * **Worksheet Djeeni name:** this name identifies the worksheet during the process 
-  * **Workbook folder:** The physical location of the workbook to be used 
-  * **Workbook name (without filetype/extension):** the name of the workbook file 
-  * **Workbook filetype/extension:** the extension, identifying the version of an excel document (either .xls or .xlsx) 
-  * **Worksheet Excel name:** name of the worksheet as displayed in Excel that you would like to use 
-  * **Use worksheet as:** here you can either choose Source or Target (default). Source worksheets are not allowed to be modified so Djeeni protects your data sources from accidentally being overwritten. Target worksheets on the other hand can be modified in consecutive process steps 
-  * **Update links in the workbook?:** If links in the workbook must be updated before using it: Yes (default) / No 
-  * **If Target then worksheet action:** It can be decided what should happen if the worksheet already exists or does not yet exist. Possible settings: 
-    * //If worksheet does exist then use it as is; If worksheet does not exist then stop with error:// this option is useful when we want to add data to an existing worksheet 
-    * //If worksheet does exist then use it as is; If worksheet does not exist then create it:// this option is useful when we want to create a worksheet and then add data to it (e.g. start a new worksheet every month and then add daily data to it) 
-    * //If worksheet does exist then delete and recreate it; If worksheet does not exist then create it:// this option is useful when we want to create the worksheet always from scratch (e.g. if the source is modified after an error is discovered in it then everything should be reprocessed again) 
-    * //If worksheet does exist then stop with Error; If worksheet does not exist then create it:// this option avoids accidentally overwriting an existing worksheet 
-  * **Workbook open password (Optional):** Password for opening the containing workbook  
-  * **Workbook write-reserved password (Optional):** Password for writing the containing workbook  
  
 +{{ :en:process_steps:worksheet:wsheetuse.png?nolink&800 |}}

Page Tools

en/process_steps/worksheet/wsheet_use.1621843930.txt.gz · Last modified: 2021/05/24 10:12