meta data for this page
  •  

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:v8_0_0:designer:menus:accueil:nouvellerequete:restitution:excel [2020/07/09 13:34]
sylvainair [Additional Parameters]
en:v8_0_0:designer:menus:accueil:nouvellerequete:restitution:excel [2021/02/01 16:44] (current)
sylvainair [Mandatory parameters]
Line 1: Line 1:
-<WRAP round todo> 
-#TODO pas réussi à refaire les captures ! 
-</​WRAP>​ 
- 
  
 ====== Excel file====== ====== Excel file======
 +
 +{{:​en:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200709-091613.png?​nolink&​400}}
  
   * **File Type**: Excel file type to be used to output the data:   * **File Type**: Excel file type to be used to output the data:
-      ​* Excel Template ​(*.xlt, *.xltx, *.xltm). +    ​File type "Excel 97 - 2003 workbook" ​(*.xls): The choice of this type of file requires the use of the Excel application to generate the output
-      Classeur ​Excel (*.xls, *.xlsx, *.xlsm​).+    File type "Excel 2007 Workbook" ​(supporting ​*.xlsm macros or not *.xlsx): The choice of this file type allows you to generate the output without using the Excel application. 
 + 
 +<WRAP round important>​ 
 +The use of the Excel application to generate the output requires a lot of system resources if the result of the query contains many rows. This is why it is recommended to use the "Excel 2007 Workbook"​ format if the number of rows returned by the query exceeds a few hundred. 
 +</​WRAP>​
  
   * **Define a file**: define the name of the file and the location where the result of the query will be saved.   * **Define a file**: define the name of the file and the location where the result of the query will be saved.
  
-  ​* **Model**: the file specified as a template allows you to use Excel to finalize the formatting of the query data (pivot table, graph, conditional formatting etc.it is advisable to store the template in the shared directory of Vigilens to be sure that it is accessible during the execution of the query (especially in case of Web type execution).+<WRAP round important>​ 
 +This option is not available by default. To have access to it, users (or the group they belong to) must have been authorized to do so in the AdminTool application (see [[:​en:​v8_0_0:​admintool:​menus:​gestion:​groupe:​start|Restriction of rights\Restitutions\Authorize restitution to particular files]]). 
 + 
 +</​WRAP>​ 
 + 
 +  ​* **Model**: the file specified as a template allows you to use Excel to finalize the formatting of the query data (pivot table, graph, conditional formattingetc). It is advisable to store the template in the shared directory of Vigilens to be sure that it is accessible during the execution of the query (especially in case of Web type execution). 
 + 
 +<WRAP round tip> 
 +The file used as a template can be any type of Excel file. It is not necessarily an "Excel template"​ type file. (*.xlt). 
 +</​WRAP>​ 
  
   * **Location **: Defines the location where Vigilens will deposit the result of the query (often used with templates to ensure consistency between the result obtained from Vigilens and the formatting done by Excel).   * **Location **: Defines the location where Vigilens will deposit the result of the query (often used with templates to ensure consistency between the result obtained from Vigilens and the formatting done by Excel).
Line 20: Line 32:
   * **Macro to launch**: Automatic execution of a macro at the end of editing. For the macro to run, remember to uncheck the box "​Remove personal information from file properties when saving"​ in the Excel options when saving the model.   * **Macro to launch**: Automatic execution of a macro at the end of editing. For the macro to run, remember to uncheck the box "​Remove personal information from file properties when saving"​ in the Excel options when saving the model.
  
-{{:en:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200709-091613.png?​nolink&​400}}+<WRAP round alert> 
 +WarningRunning a macro or updating fields requires the use of the Excel application (even if the document type is "Excel 2007 workbook"​). This being said, in the case of an "Excel 2007 workbook",​ only the execution of the macro or the updating of the fields will require the use of the Excel application (the data insertion will be done without the use of the Excel application,​ therefore, faster). 
 +</​WRAP>​ 
  
-===== Running from Excel by adapting the Macro =====  
-[[:​en:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​excel:​exemplemacro|Macro example to be completed]] 
  
 ===== Running from Excel / Calling up external data ===== ===== Running from Excel / Calling up external data =====
  
-The call of a Vigilens query from Excel is done through the menu''​Data''​→''​External Data''​→''​From web''​. \\ {{:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200702-135346.png?​nolink&​400}}+The call of a Vigilens query from Excel is done through the menu''​Data''​→''​Get & transform data''​→''​From web''​. \\ 
  
-==== Mandatory parameters ====+{{:​en:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200925-143334.png?​nolink&​650}}
  
 +url exemple : ''​http://​my-server/​vigilens/​table.php?​vglCtrlName="​%2FDEMO%2F01%20Fiche%20CLIENT"&​vglOutput="​XLTable"&​CLIENT=4245&​vglNumFormat="​%2C%2E"&​vglUserName="​srivier"''​
  
-{{:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200702-135545.png?​nolink&​400}}+ 
 +{{:​en:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200925-143828.png?​nolink&​650}} 
 + 
 +{{:en:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200925-143941.png?​nolink&​650}} 
 +==== Mandatory parameters ====
  
   * Root Adress: http:<​nowiki>//</​nowiki><​ServeurWeb>/​vigilens/​table.php?​​   * Root Adress: http:<​nowiki>//</​nowiki><​ServeurWeb>/​vigilens/​table.php?​​
Line 39: Line 57:
   * vglUserName=<​NomUtilisateur>​​   * vglUserName=<​NomUtilisateur>​​
  
-[[en:​v8_0_0:​howto:​url|See url syntax guide]]+<WRAP round info> 
 +  * [[:en:​v8_0_0:​howto:​url|See url syntax guide]] 
 +  * use [[:​en:​v8_0_0:​designer:​menus:​accueil:​start|commande line wizard]] in the designer, with output option "* Run query from Excel"​ 
 +</​WRAP>​
  
 <WRAP round tip> Parameters order does not matter​ </​WRAP>​ <WRAP round tip> Parameters order does not matter​ </​WRAP>​
  
-{{:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200702-135950.png?​nolink&​}} \\  +<WRAP round tip> 
-You can keep formats ​by checking the box ''​Format Text Only''​ in the import options. +It is possible to automate this processing ​by using Excel macrosHere is an example of a macro to be adapted according to the needs:[[:en:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​excel:exemplemacro|Example macro]] 
- +</​WRAP>​
- +
-Once result imported into Excel, it is possible ​to refresh data with ''​Update''​ button. +
- +
-{{:v8_0_0:designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200702-140405.png?​nolink&​300}} +
- +
-{{:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:20200702-140435.png?​nolink&​300}} +
- +
-==== Additional Parameters ==== +
- +
-{{:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200702-140951.png?​nolink&​}} +
- +
-For each parameter: ParameterCode = ParameterValue+
  
-{{:​v8_0_0:​designer:​menus:​accueil:​nouvellerequete:​restitution:​pasted:​20200707-144938.png?​nolink&​}} 
  
-Les valeurs numériques restituées utiliseront le premier caractère précisé comme séparateur de décimales, le second précisé comme séparateur de milliers.​