# Microsoft Excel writer The **Microsoft Excel writer** step writes incoming rows to a Microsoft Excel file. It supports both: * `.xls` (binary format; best for simple content) * `.xlsx` (Open XML format; better for templates because it can preserve charts and other objects) ### Step name **Step name** specifies the unique name of the step on the canvas. Default: `Microsoft Excel writer`. ### Configure the step (tabs) The Microsoft Excel writer step includes two tabs: * **File & Sheet** * **Content** #### File & Sheet tab ![Microsoft Excel writer File and Sheet tab](/files/G4sqcOSmhstOut3Jxz9t) **File panel** Use this panel to define the output file name, extension, and splitting behavior. | Field | Description | | ------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Filename** | Output file name and location. Supports variables and parameters. Do not include a file extension when date/time is appended and **Extension** is specified. | | **Extension** | Output format: **.xls Excel 97 and above** or **.xlsx Excel 2007 and above**. Default: `.xls`. | | **Create parent folder** | Creates the parent folder if it does not exist. If cleared and the folder does not exist, the step errors. | | **Stream XLSX data** | Streams `.xlsx` output for large files, avoiding memory restrictions (up to Excel’s limits: 1,048,575 rows and 16,384 columns). Available only when **Extension** is `.xlsx`. | | **Split every ... data rows** | Creates a new output file every *N* data rows (files are numbered). | | **Include stepnr in filename?** | Appends the copy number (for example, `_0`) when running multiple copies. | | **Include date in filename?** | Appends system date (for example, `_20181231`). | | **Include time in filename?** | Appends system time (for example, `_235959`). | | **Specify Date time format?** | Appends date/time using **Date time format**. | | **Date time format** | Date/time format. See [Common Formats](/pdia-data-integration/pdi-transformation-steps-reference-overview/common-formats.md). | | **Show filename(s)...** | Shows a simulated list of the files that will be generated. | | **If output file exists** | Choose whether to **Use existing file for writing** or **Replace with new output file**. | | **Wait for first row before creating file** | Creates a file only after the first row arrives. Prevents empty files when no rows are written. | | **Add filenames to result** | Adds output file names to the transformation result file list. | **Sheet panel** Use this panel to control sheet naming, default sheet behavior, and (for `.xls`) sheet protection. | Field | Description | | ------------------------------------ | ---------------------------------------------------------------------------- | | **Sheet name (max. 31 characters)** | Worksheet name. Supports variables and parameters. | | **Make this the active sheet** | When selected, the workbook opens to this sheet by default. | | **If sheet exists in output file** | Choose whether to **Write to existing sheet** or **Replace with new sheet**. | | **Protect sheet? (XLS format only)** | Protects the sheet from editing using a user name and password. | | **Protected by user** | User name to display as the protector. Supports variables and parameters. | | **Password** | Password required to unprotect the sheet. Supports variables and parameters. | **Template panel** Use this panel to create output files/sheets from templates. {% hint style="info" %} If you use a template file, it must match the selected output type (`.xls` vs `.xlsx`). {% endhint %} | Field | Description | | ----------------------------------------- | -------------------------------------------------------------------------------------- | | **Use template when creating new files** | Creates new output files by copying a template file. | | **Template file** | Template workbook to copy (when **Use template when creating new files** is selected). | | **Use template when creating new sheets** | Creates new sheets by copying a template sheet. | | **Template sheet** | Template sheet to copy (when **Use template when creating new sheets** is selected). | | **Hide Template sheet** | Hides the template sheet in the output workbook. | #### Content tab ![Microsoft Excel writer Content tab](/files/Sywx57xIpuwSg9C2cYqx) **Content options panel** Use these options to control where and how data is written. | Field | Description | | -------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Start writing at cell** | Starting cell in Excel notation (for example, `A1`). | | **When writing rows** | Controls behavior when the sheet already contains data: **overwrite existing cells** or **shift existing cells down**. | | **Write Header** | Writes a header row using field titles (see **Fields panel**). | | **Write Footer** | Writes a footer row using field titles (see **Fields panel**). | | **Auto size columns** | Attempts to auto-size columns to fit content. Results may vary; install appropriate fonts on the server for best accuracy. | | **Retain NULL values** | Keeps null cell values. If cleared, nulls are replaced with empty strings. | | **Force formula recalculation** | For `.xls`, sets a flag so Excel recalculates formulas when the file opens. For `.xlsx`, attempts recalculation, but the underlying POI library may not support all formulas. | | **Leave styles of existing cells unchanged** | Preserves styles in existing cells (useful for pre-formatted templates). | | **Extend data validation range** | Extends template data validation to include all rows written. | **When writing to existing sheet panel** Use these options when writing to a sheet that already exists. | Field | Description | | --------------------------------------------------- | ------------------------------------------------------------------------------------------------------- | | **Start writing at end of sheet (appending lines)** | Appends rows after the last defined row. If cleared, overwrites existing defined rows. | | **Offset by ... rows** | Moves the write position by N rows before writing. Use negative values to preserve a pre-styled footer. | | **Begin by writing ... empty lines** | Leaves N empty rows before writing. Useful when visually separating appended blocks of rows. | | **Omit header** | Omits the header when appending to an existing sheet (useful to avoid repeating headers). | **Fields panel** Use the **Fields** table to define how fields are written to Excel. | Column | Description | | --------------------------------- | --------------------------------------------------------------------------------------------------- | | **Name** | Field name from the incoming stream. | | **Type** | Data type: String, Date, or Number. | | **Format** | Excel format string. Use Excel documentation or references for valid formats. | | **Style from cell** | Cell reference (for example, `A1`) to copy styling from (typically a pre-styled template cell). | | **Field title** | Header/footer title to use instead of the field name. | | **Header/Footer style from cell** | Cell reference to copy styling for header/footer cells. | | **field contains formula** | Set to `Yes` when the field contains an Excel formula. Do not include the leading `=` in the value. | | **Hyperlink** | Field containing hyperlink targets (cell links, URLs, FTP, email, local documents). | | **Cell comment (XLSX)** | Field containing a cell comment (available in `.xlsx`). | | **Cell comment author (XLSX)** | Field containing the author name for the comment (available in `.xlsx`). | ### Metadata injection support This step supports metadata injection. You can use it with [ETL metadata injection](/pdia-data-integration/pdi-transformation-steps-reference-overview/etl-metadata-injection.md) to pass metadata to your transformation at runtime. The following **Value** fields support metadata injection: * Name * Type * Format * Style from cell * Field title * Header/Footer style from cell * field contains formula * Hyperlink * Cell comment (XLSX) * Cell comment author (XLSX) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.pentaho.com/pdia-data-integration/pdi-transformation-steps-reference-overview/microsoft-excel-writer.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.