Differences

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

--- transformations:exportexcel [2015/10/01 22:04] – created dmitry
+++ transformations:exportexcel [2022/01/31 14:21] (current) – Add table references, Replace table mode, reformat. craigt
@@ Line 1: / Line 1: @@
-===== Export into Excel spreadsheet =====
+{{ transformations:ExportExcelAction.png}}
+====== EXPORT INTO EXCEL SPREADSHEET ======
+Category: Export / File\\
-This transformation exports table into a spreadsheet.
+\\
+=====Description=====
+This action exports the current dataset as a new Excel workbook (.xlsx) file, or //into// and existing Excel file, and supports the following modes:
-File name can use either full or relative path.
+  * Create new file
+  * Create new sheet in existing file
+  * Replace sheet/range in existing file
+  * Replace table in existing file
+\\
+=====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.
+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.
+\\
+=====Action settings=====
+^ Setting  ^ Description  ^
+|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.|
+<sup>*</sup> Setting can be specified using a [[:parameters|parameter]] or the first value of a column .\\
+\\
+====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 table, below, for additional details.
+^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 .\\
+\\
+====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//, below, for additional details.|
+<sup>*</sup> Setting can be specified using a [[:parameters|parameter]] or the first value of a 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====
+^Option ^Description ^
+|Overwrite|The new file replaces the original file.|
+|Rename|The original file will be renamed with "backup" and a serial number appended to the file name.  The new file will\\ possess the name specified in the //File// setting.|
+|Halt project execution|The project will stop processing and this action will display a warning symbol.|
+\\
+=====Remarks=====
+====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.
+An error will occur if //replacing an existing table// on a Sheet containing more than one table.
+\\
+=====Community examples=====
+  * [[https://community.easymorph.com/t//1273/6|Split Excel spreadsheets into multiple spreadsheets]] ([[https://community.easymorph.com/uploads/short-url/6iWYdZJc4yAvqYMbTGZWXtVFmvL.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 1//; Action position: //4//)
+  * [[https://community.easymorph.com/t//1392/6|Data validation list]] ([[https://community.easymorph.com/uploads/short-url/aZNapMreakaQZa83qs7fB3TS2oq.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Change validation list values//; Action position: //2//)
+  * [[https://community.easymorph.com/t//1625/4|Create a new table with columns based on row values]] ([[https://community.easymorph.com/uploads/short-url/4oGLUNDHrUfB971H80ciuOAtOTG.morph|Project]]; Module: //Module 1//; Group: //Tab 1//; Table: //Table 1//; Action position: //7//)
+\\
+=====See also=====
+  * [[transformations:excelcommand|Excel command]]
+  * [[transformations:importexcel|Import Excel spreadsheet]]
-The transformation can operate in two modes:
-  * Create new file - creates a new spreadsheet
-  * Replace sheet in existing file - takes existing spreadsheet and replaces specified sheet content with table from EasyMorph. Other sheets remain intact.