{{ transformations:ExportExcelAction.png}} ====== EXPORT INTO EXCEL SPREADSHEET ====== Category: Export / File\\ \\ =====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: * 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*|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.| * 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*|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.| * 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*|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.| * 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*|Select the sheet or named range to replace. See //Remarks//, below, for additional details.| * 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*|Select the sheet to replace the table in. See //Remarks//, below, for additional details.| * 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.\\ \\ ====Number formatting==== This action formats numbers in spreadsheets according to their number formatting in EasyMorph. For example, percentages will appear as percentages rounded values will appear as rounded values, etc. \\ =====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]]