Excel CRUD

Description

The Excel CRUD node provides comprehensive Create, Read, Update, and Delete operations for Microsoft Excel workbooks.
It allows workflows to dynamically interact with Excel files — including reading ranges, inserting data, updating cells, formatting, filtering, sorting, and even creating pivot tables — all without manual Excel interaction.

This node supports both .xlsx and .xls file formats and uses the MinuteView Excel Service to perform operations in a structured and automation-safe way.

---

Supported Modes

The node operates in Mode-based configuration.
Each mode determines which parameters are required and what type of operation will be performed.

Mode	Description
READ	Read data from a defined range of rows/columns.
CREATE	Append or insert a new row of data.
INSERT	Insert a new row at a specific row number.
UPDATE	Update a single cell by row and column number.
UPDATE_CELLS	Update multiple cells using a key/value dictionary.
UPDATE_RANGE	Update a 2D range of cells with array data.
DELETE	Delete one or more rows.
DELETE_COLUMNS	Delete one or more columns.
ADD_FORMULA	Insert a formula into a specific cell.
GET_CELL	Retrieve a single cell’s value.
FIND_REPLACE	Perform text find/replace across a worksheet.
ADD_WORKSHEET	Create a new worksheet in the workbook.
DELETE_WORKSHEET	Delete a worksheet by name.
FORMAT	Apply formatting (bold, colors, alignment, etc.) to a range.
MERGE	Merge or unmerge cell ranges.
AUTOFIT	Auto-fit specified columns.
SORT	Sort a range of data by a specified column.
FILTER	Apply filters to a range based on criteria.
COPY	Copy a range to another worksheet and location.
PIVOT_TABLE	Create a pivot table from a source range.
EXPORT_DATATABLE	Export worksheet data to a DataTable object for use in subsequent nodes.
CREATE_WORKBOOK	Create a new workbook with an initial worksheet.
CALCULATE	Trigger Excel to recalculate all formulas.

---

How to Use

Each mode requires different fields.
Below is an overview of the common inputs and mode-specific parameters.

Common Inputs

Field	Type	Description	Required
Mode	Picklist	The Excel operation to perform (see list above).	✅
Excel File Path	Text	Full file path to the Excel workbook.	✅
Worksheet Name	Text	Name of the worksheet to operate on (ignored for CREATE_WORKBOOK).	✅

---

Mode Details

🟩 READ

Reads data from a specific cell range.

Field	Type	Description
Start Row	Number	Row number to start reading.
End Row	Number	Row number to end reading.
Start Column	Number	Column index to start from.
End Column	Number	Column index to stop at.

Output:
DataTable or list of rows and columns returned as result.Data["Rows"].

---

🟨 CREATE

Appends or inserts new data rows.

Field	Type	Description
Cell Values	JSON	Key/value map or array defining cell contents for the new row.
At Row Number	Number	Optional — specify row number to insert at (defaults to append).

---

🟦 UPDATE / UPDATE_CELLS / UPDATE_RANGE

Mode	Description	Key Fields
UPDATE	Update a single cell.	Row Number, Column Number, Value
UPDATE_CELLS	Update multiple cells.	Cell Updates (dictionary)
UPDATE_RANGE	Update a rectangular range.	Range Address (e.g. A1:C3), Values (2D array)

---

🟥 DELETE / DELETE_COLUMNS

Mode	Description	Key Fields
DELETE	Delete one or more rows.	Row Numbers (JSON array or CSV)
DELETE_COLUMNS	Delete columns by name or index.	Column Identifiers (JSON array or CSV)

---

🧮 ADD_FORMULA

Adds or replaces a formula in a cell.

Field	Type	Description
Row Number	Number	Target row.
Column Number	Number	Target column.
Formula	Text	Formula expression (e.g., =SUM(A1:A10)).

---

🔍 GET_CELL

Retrieve the value of a single cell.

Field	Type	Description
Row Number	Number	Row index.
Column Number	Number	Column index.

---

🔎 FIND_REPLACE

Performs search and replace operations in a worksheet.

Field	Type	Description
Find Text	Text	Text to search for.
Replace Text	Text	Replacement text.
Match Case	Boolean	Case-sensitive match.
Match Whole Cell	Boolean	Match entire cell content only.

---

🧾 ADD_WORKSHEET / DELETE_WORKSHEET

Mode	Description	Key Fields
ADD_WORKSHEET	Add a new worksheet.	New Worksheet Name, Position (optional)
DELETE_WORKSHEET	Delete an existing worksheet.	Worksheet Name

---

🎨 FORMAT

Applies cell styling and visual formatting.

Field	Type	Description
Range Address	Text	Excel range (e.g., A1:D5).
Bold / Italic / Underline	Boolean	Apply text styling.
Font Name	Text	Font family (e.g., Arial).
Font Size	Number	Font size.
Background Color	Text	Hex or named color (e.g., #FFFF00 or Yellow).
Font Color	Text	Font color.
Number Format	Text	Format mask (e.g., #,##0.00).
Wrap Text	Boolean	Enable text wrapping.
Horizontal Alignment	Text	Left, Center, Right.

---

🔗 MERGE / AUTOFIT

Mode	Description	Key Fields
MERGE	Merge or unmerge cells.	Range Address, Unmerge (bool)
AUTOFIT	Adjust column widths automatically.	Column Identifiers (CSV or JSON)

---

📊 SORT / FILTER / COPY / PIVOT_TABLE

Mode	Description	Key Fields
SORT	Sorts data in a range by column.	Range Address, Sort Column, Ascending
FILTER	Filters rows in a range.	Range Address, Filter Criteria (dictionary of column index → value)
COPY	Copies a range to another worksheet and cell.	Source Range, Target Worksheet, Target Cell
PIVOT_TABLE	Creates a pivot table.	Source Range, Target Worksheet, Target Cell

---

🧱 EXPORT_DATATABLE

Exports worksheet contents to a structured DataTable object for downstream nodes.

Field	Type	Description
First Row As Header	Boolean	Whether to treat the first row as column headers.

---

🆕 CREATE_WORKBOOK

Creates a new workbook with the specified sheet name.

Field	Type	Description
Worksheet Name	Text	Name of the first sheet to create.

---

⚙️ CALCULATE

Forces recalculation of all formulas in the workbook.

Field	Type	Description
Force Full Calculation	Boolean	When true, forces full workbook recalculation instead of dependency-only.

---

Outputs

After successful execution, the node produces structured output depending on the operation.

Output Variable	Description
Rows	When reading data — contains the rows retrieved.
Updated Cells / Deleted Rows / Exported Range	Contextual to the performed mode.
Message	Descriptive result message (e.g., “3 rows updated”).
Success	Boolean result indicator.
Workbook Path	Returned path for create/export operations.

---

Example Workflow Usage

1. Use File Download or Vault Get File node to fetch an Excel file.
2. Add Excel CRUD node with mode READ to extract specific data.
3. Use Evaluate Decision Table or AI Node to process the extracted data.
4. Chain a second Excel CRUD node (mode UPDATE_RANGE) to update results.
5. Finish with Upload File or Email Attachment node to distribute the file.

---

Status Messages

Status	Description
Completed	Operation successful.
Fail	Operation failed (invalid file, worksheet not found, or incorrect parameters).

---

Error Handling

If an operation fails, the node logs the issue and returns a Fail state. Common causes include:

• Invalid or missing file path
• Non-existent worksheet name
• Invalid range or row/column reference
• Incorrect JSON structure in “Cell Values” or “Filter Criteria”
• File locked by another process

---

Notes

• The node uses the ClosedXML-based CRUD Service for fast, local Excel manipulation.
• It supports both static and dynamic placeholders in file paths and worksheet names.
• All cell coordinates are 1-based (e.g., Row 1 = first row).
• Use the READ → FILTER → EXPORT_DATATABLE pattern to extract and transform data efficiently.
• Formulas and formatting are preserved when possible.
• Merge and Pivot functions require .xlsx format.

---

Category: Excel & Office Automation Task Name: ExcelCRUD