User Tools

Site Tools


transformations:exportexcel

Differences

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

Link to this comparison view

Both sides previous revision Previous revision
transformations:exportexcel [2021/07/17 15:35]
craigt
transformations:exportexcel [2022/01/31 14:21] (current)
craigt Add table references, Replace table mode, reformat.
Line 5: Line 5:
 \\  \\ 
 =====Description===== =====Description=====
-This action exports a table into an Excel spreadsheet (.xlsx) file The file name can use either a full or relative path.\\+This action exports the current dataset as new Excel workbook (.xlsx) file, or //into// and existing Excel file, and supports the following modes: 
 + 
 +  * Create new file 
 +  * Create new sheet in existing file 
 +  * Replace sheet/range in existing file 
 +  * Replace table in existing file
  
 \\  \\ 
 =====Use cases===== =====Use cases=====
-You can subdivide a dataset and export it to a multi-tabbed Excel file using iterations and passing the sheet names in using Parameters in the called module.\\+You can subdivide a dataset and export it to a multi-tabbed Excel file using iterations and passing the sheet names in using Parameters in the called module. 
 + 
 +In conjunction with the [[transformations:excelcommand|Excel command]] action, it is possible to generate reports by dynamically updating the source data tables for charts and pivottables in an Excel file, and output to PDF fileS.
  
 \\  \\ 
Line 16: Line 23:
 |File<sup>*</sup>|Enter a fully-qualified file name for the output file (includes relative or absolute path).  | |File<sup>*</sup>|Enter a fully-qualified file name for the output file (includes relative or absolute path).  |
 |Mode|Select whether this action will create a new file, create a new sheet in an existing file,\\ or replace a sheet or range in an existing file .  See the table below for details.| |Mode|Select whether this action will create a new file, create a new sheet in an existing file,\\ or replace a sheet or range in an existing file .  See the table below for details.|
-|File already exists|When selecting to "Create new file" in the option above, choose whether to overwrite the existing file, rename the\\ existing file, or halt the action's execution.  See the "File already exists optionstable below for details.| + 
-|Sheet name<sup>*</sup>|When selecting to "Create new file" or "Create a new sheet in existing file" in the option above, enter the name\\ for the new sheet.| +<sup>*</sup> Setting can be specified using a [[:parameters|parameter]] or the first value of a column .\\ 
-|Sheet or range<sup>*</sup>|When selecting "Replace sheet/range in existing file", select the sheet or named range to replace.| + 
-|Create named range|When "Create new file" is selected in the option abovethe dataset will be written into a named range in the new file.\\  Enter the name of the range in the //Range name// field.| +\\ 
-<sup>*</sup> Setting can be specified using a [[:parameters|parameter]].\\ +====Create new file settings==== 
-\\  +Description:  Create a new Excel file or overwrite an existing file with the same name.  See the //File already exists// options tablebelowfor additional details. 
-====Export mode options==== +^Setting ^Description ^ 
-^Option ^Description ^ +|Sheet name<sup>*</sup>|Enter the name of the worksheet to output the dataset to.| 
-|Create new file|Create a new Excel file or overwrite an existing file with the same name.  See the "File already exists\\ options" table below for additional details.| +|Create named range|When checked, named range will be created within the Excel file for the output dataset.  Enter the name of the\\ range to use.| 
-|Create new sheet in existing file|This option will export the dataset to new sheet inserted into the existing file selected in the //File//\\ setting, above.| +|Create table|When checked, the dataset will be output into a formatted Excel table.  Enter the name of the table to use.| 
-|Replace sheet/range in existing file|This option will output the dataset to an existing sheet or named range within the file selected in the\\ //File// settingoverwriting its content.|+ 
 +<sup>*</sup> Setting can be specified using a [[:parameters|parameter]] or the first value of a column .\\ 
 + 
 +\\ 
 +====Create new sheet in existing file settings==== 
 +Description:  This option will export the dataset to a new sheet inserted into the existing selected file. 
 +^Setting ^Description ^ 
 +|Sheet name<sup>*</sup>|Enter the name of the worksheet to output the dataset to.| 
 +|Create named range|When checked, a named range will be created within the Excel file for the output dataset.  Enter the name of the\\ range to use.| 
 +|Create table|When checked, the dataset will be output into a formatted Excel table.  Enter the name of the table to use.| 
 + 
 +<sup>*</sup> Setting can be specified using a [[:parameters|parameter]] or the first value of a column .\\ 
 + 
 +\\ 
 +====Replace sheet/range in existing file settings==== 
 +Description:  This mode will replace an existing sheet in an existing Excel workbook. 
 +^Setting ^Description ^ 
 +|Sheet or range<sup>*</sup>|Select the sheet or named range to replace.  See //Remarks//, belowfor additional details.| 
 + 
 +<sup>*</sup> Setting can be specified using a [[:parameters|parameter]] or the first value of column .\\ 
 + 
 +\\ 
 +====Replace table in existing file settings==== 
 +Description:  This mode will replace an existing table in an existing Excel workbook. 
 +^Setting ^Description ^ 
 +|Sheet name<sup>*</sup>|Select the sheet to replace the table in.  See //Remarks//, below, for additional details.| 
 + 
 +<sup>*</sup> Setting can be specified using a [[:parameters|parameter]] or the first value of a column .\\ 
 \\  \\ 
 ====File already exists options==== ====File already exists options====
Line 36: Line 71:
 \\  \\ 
 =====Remarks===== =====Remarks=====
-If you're "creating a new sheetor "replacing a sheet/range", or "overwriting an existing file", the target file must be closed prior to running this action or it will result in a warning and no content will be written.  Close the target file and rerun this action to complete it successfully.+====Close target workbooks before running==== 
 +When //creating a new sheet// or //replacing a sheet/range//, or //overwriting an existing file//, the target file must be closed prior to running this action or it will result in a warning and no content will be written.  Close the target file and rerun this action to complete it successfully.\\ 
 +\\  
 +====Worksheet positioning==== 
 +When //creating a new sheet in an existing file//, the new sheet will appear in the rightmost position.  A //sheet being replaced// in an existing file will retain its original position.\\ 
 +\\  
 +====Replacing ranges and tables==== 
 +When //replacing an existing range or table//, the content of the target Sheet will be cleared before the dataset is exported and named with the original range/table name.  New ranges/tables will always be positioned on the sheet starting at cell A1.
  
-When creating a new sheet in an existing file, the new sheet will appear in the rightmost (last) position.  A sheet being replaced in an existing file will retain its original position within the file.\\+An error will occur if //replacing an existing table// on a Sheet containing more than one table.
  
 \\  \\ 
Line 49: Line 91:
 =====See also===== =====See also=====
  
 +  * [[transformations:excelcommand|Excel command]]
   * [[transformations:importexcel|Import Excel spreadsheet]]   * [[transformations:importexcel|Import Excel spreadsheet]]
 +
  
transformations/exportexcel.txt · Last modified: 2022/01/31 14:21 by craigt