Gembox Excel CRUD

Description

The Gembox Excel CRUD node provides Create, Read, Update, and Delete operations for Excel files using the GemBox.Spreadsheet library.
It allows workflows to read and modify Excel workbooks quickly and efficiently — ideal for automation tasks involving reporting, templated spreadsheets, or Excel-based data exchange.

This node requires a GemBox service account with a valid API key or license key configured in the system.

---

How to Use

When added to a workflow, this node connects to the GemBox engine using your configured service account, then performs the chosen operation (READ, CREATE, UPDATE, DELETE, PROPERTIES, or CALCULATE).

Field	Type	Description	Required
ServiceAccount - GemBox	Service Account Picker	Select the GemBox license or API key to authorize Excel operations.	✅
Mode	Picklist	Defines which Excel operation to perform (see available modes below).	✅
Excel File Path	Text	Full file path to the Excel file to read or modify.	✅
Worksheet Name	Text	Name of the worksheet to target.	✅

---

Supported Modes

Mode	Description
READ	Reads data from the specified worksheet (and optionally limits columns).
CREATE	Appends new data to a worksheet.
UPDATE	Updates a single cell.
DELETE	Deletes one or more rows.
PROPERTIES	Updates the file’s built-in and/or custom document properties.
CALCULATE	Performs recalculation of all workbook formulas.

---

Mode Details

🟩 READ

Reads data from a worksheet, limited to a number of columns.

Field	Type	Description
Number of Columns	Number	The maximum number of columns to read per row.

Output Example:

{
  "Rows": [
    ["Name", "Department", "Salary"],
    ["Alice", "Engineering", 85000],
    ["Bob", "Finance", 78000]
  ],
  "Message": "3 rows read successfully.",
  "Success": true
}

---

🟨 CREATE

Appends a new row of data to the worksheet.

Field	Type	Description
Cell Values	JSON	JSON object of cell values for the new row.

---

🟦 UPDATE

Updates a specific cell in the worksheet.

Field	Type	Description
Row Number	Number	The row index to update.
Column Number	Number	The column index to update.
Value	Text / Number / Boolean	The new value to assign.

Example: To update cell B3 with the value “Finance”:

• Row Number = 3
• Column Number = 2
• Value = "Finance"

---

🟥 DELETE

Deletes one or more rows from the worksheet.

Field	Type	Description
Row Numbers	JSON / CSV	One or more row numbers to delete.

Example:[3,4,5] → deletes rows 3 to 5.

---

🧾 PROPERTIES

Updates Excel workbook metadata such as author, subject, category, or custom fields.

Field	Type	Description
Built-In Properties	JSON	Dictionary of built-in property names and values.
Custom Properties	JSON	Dictionary of user-defined property names and values.

Example:

{
  "Built-In Properties": {
    "Author": "john smith",
    "Category": "Automation Reports"
  },
  "Custom Properties": {
    "Project": "VaultSync",
    "WorkflowID": "WF-00234"
  }
}

---

⚙️ CALCULATE

Recalculates all Excel formulas and updates formula-driven cells.

Purpose:

• Forces recalculation of dynamic cells after bulk data updates.
• Ensures formula-driven fields (totals, averages, etc.) are accurate.

No additional input fields required.

---

Outputs

After execution, the node returns structured results based on the chosen mode.

Output Variable	Description
Rows	(READ) The table data read from the worksheet.
Message	Summary message (e.g., “Row created successfully”).
Success	Boolean indicating whether the operation succeeded.
PropertiesUpdated	(PROPERTIES) Confirmation of property updates.

---

Example Configurations

Example 1 – Read Worksheet

Setting	Example
Mode	READ
Excel File Path	C:\Reports\Inventory.xlsx
Worksheet Name	Stock
Number of Columns	4

Result: Reads the first 4 columns of the worksheet into a data table.

---

Example 2 – Add a New Row

Setting	Example
Mode	CREATE
Excel File Path	C:\Reports\Sales.xlsx
Worksheet Name	Q1
Cell Values	{"A":"Order123","B":"ProductX","C":45,"D":230.00}

Result: Appends a new row to the Q1 worksheet with order data.

---

Example 3 – Update Document Properties

Setting	Example
Mode	PROPERTIES
Excel File Path	C:\Reports\Summary.xlsx
Built-In Properties	{"Author":"MinuteView Automations"}
Custom Properties	{"Revision":"3.1","ApprovedBy":"QA Team"}

---

Status Messages

Status	Description
Completed	Operation executed successfully.
Fail	Operation failed due to invalid parameters or file issues.

---

Error Handling

The node automatically handles most common Excel issues. If an operation fails, it logs the error in the workflow log and returns a Fail status.

Common causes:

• Invalid file path or inaccessible location
• Worksheet name not found
• Invalid JSON input format for values or properties
• Missing GemBox license key or expired service account

---

Notes

• Requires a valid GemBox service account with an active API or license key.
• Compatible with .xlsx, .xls, and .csv files.
• All operations are performed locally (no external calls).
• Does not require Excel to be installed on the system.
• Ideal for headless or server-based environments.
• Use in combination with File Upload, Download, or Email Attachment nodes for end-to-end automation.

---

Example Workflow Usage

1. Get File → Retrieve an Excel file from SharePoint, Vault, or file system.
2. Gembox Excel CRUD (Mode: READ) → Read structured data.
3. Evaluate Decision Table or AI Agent → Process the data.
4. Gembox Excel CRUD (Mode: UPDATE) → Write calculated results back.
5. Upload File / Send Email → Deliver final output.

---

Category: Excel & Office Automation Task Name: GemboxExcelCRUD