Excelx
Read and write Excel xlsx documents cell by cell (load, sheets, get, set, format, save), or read and write a whole file as JSON rows in one call with excelx read, excelx sheets and excelx write. Typical uses: import a supplier price list into a database, produce a monthly report.
excelx loadexcelx load emptyexcelx showexcelx saveexcelx existexcelx sheet addexcelx sheet deleteexcelx sheet max_rowexcelx sheet showexcelx cell getexcelx cell evalexcelx cell refexcelx cell setexcelx cell formatexcelx closeexcelx close_allexcelx readexcelx readexcelx sheetsexcelx writeexcelx write

excelx load <excelId> <path>


Description

    Load an ExcelX document.

Parameters

    excelId:   The Excel id - string - required
    path:   The file path - string - required
admin
excelx load "excelId" "/Users/jimmitry/Desktop/test.xlsx";
mentdb
1
Used in use cases (3 recipes)

    SQL developer in MQL › Export a query to CSV, Excel and PDF files
    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
Often used with

    excelx cell get  ·  excelx close  ·  excelx sheet show  ·  excelx cell eval  ·  excelx cell set  ·  excelx load empty  ·  excelx save  ·  excelx sheet add

excelx load empty <excelId>


Description

    Load an empty ExcelX document.

Parameters

    excelId:   The Excel id - string - required
admin
excelx load empty "excelId";
mentdb
1
Used in use cases (8 recipes)

    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Loan amortization schedule computed by Excel formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
    Excel reporting analyst › Investment appraisal: NPV, IRR and discounted payback
    Excel reporting analyst › Sales ledger workbook queried with SQL
    Excel reporting analyst › Fixed assets register: straight-line and declining balance
    and 2 more in the Use cases
Often used with

    excelx cell eval  ·  excelx cell set  ·  excelx sheet add  ·  excelx close  ·  excelx save  ·  json doc  ·  excelx cell get  ·  excelx cell format

excelx show


Description

    Show all loaded ExcelX documents.

admin
excelx show
mentdb
["excelId"]
Used in use cases (1 recipe)

    Excel reporting analyst › Quarterly sales workbook with growth formulas
Often used with

    excelx cell eval  ·  excelx cell format  ·  excelx cell get  ·  excelx cell set  ·  excelx close  ·  excelx exist  ·  excelx load  ·  excelx load empty

excelx save <excelId> <path>


Description

    Save an ExcelX document to an xlsx file.

Parameters

    excelId:   The Excel id - string - required
    path:   The file path - string - required
admin
excelx save "excelId" "/Users/jimmitry/Desktop/test.xlsx";
mentdb
1
Used in use cases (7 recipes)

    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
    Excel reporting analyst › Investment appraisal: NPV, IRR and discounted payback
    Excel reporting analyst › Sales ledger workbook queried with SQL
    Excel reporting analyst › Fixed assets register: straight-line and declining balance
    Excel reporting analyst › Clean a contact list: trim, validate emails, flag duplicates
    and 1 more in the Use cases
Often used with

    excelx cell eval  ·  excelx cell set  ·  excelx close  ·  excelx load empty  ·  excelx sheet add  ·  excelx cell get  ·  json doc  ·  excelx cell format

excelx exist <excelId>


Description

    Check if an ExcelX document is already loaded.

Parameters

    excelId:   The Excel id - string - required
admin
excelx exist "excelId"
mentdb
1
Used in use cases (1 recipe)

    Excel reporting analyst › Quarterly sales workbook with growth formulas
Often used with

    excelx cell eval  ·  excelx cell format  ·  excelx cell get  ·  excelx cell set  ·  excelx close  ·  excelx load  ·  excelx load empty  ·  excelx save

excelx sheet add <excelId> <sheetName>


Description

    Add a new sheet to an ExcelX document.

Parameters

    excelId:   The Excel id - string - required
    sheetName:   The sheet name - string - required
admin
excelx sheet add "excelId" "sheet2";
mentdb
1
Used in use cases (8 recipes)

    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Loan amortization schedule computed by Excel formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
    Excel reporting analyst › Investment appraisal: NPV, IRR and discounted payback
    Excel reporting analyst › Sales ledger workbook queried with SQL
    Excel reporting analyst › Fixed assets register: straight-line and declining balance
    and 2 more in the Use cases
Often used with

    excelx cell eval  ·  excelx cell set  ·  excelx load empty  ·  excelx close  ·  excelx save  ·  json doc  ·  excelx cell get  ·  excelx cell format

excelx sheet delete <excelId> <sheetName>


Description

    Delete a sheet from an ExcelX document.

Parameters

    excelId:   The Excel id - string - required
    sheetName:   The sheet name - string - required
admin
excelx sheet delete "excelId" "sheet2";
mentdb
1
Used in use cases (1 recipe)

    Excel reporting analyst › Bank CSV to a category summary with frozen values
Often used with

    csv parse  ·  excelx cell eval  ·  excelx cell get  ·  excelx cell set  ·  excelx close  ·  excelx load  ·  excelx load empty  ·  excelx save

excelx sheet max_row <excelId> <sheetName>


Description

    Get the last row number of a specific sheet.

Parameters

    excelId:   The Excel id - string - required
    sheetName:   The sheet name - string - required
admin
excelx sheet max_row "excelId" "sheet2"
mentdb
432
Used in use cases (3 recipes)

    SQL developer in MQL › Export a query to CSV, Excel and PDF files
    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Sales ledger workbook queried with SQL
Often used with

    excelx cell get  ·  excelx close  ·  cm get  ·  excelx cell eval  ·  excelx cell set  ·  excelx load  ·  excelx load empty  ·  excelx save

excelx sheet show <excelId>


Description

    Get all the sheets of an ExcelX document.

Parameters

    excelId:   The Excel id - string - required
admin
excelx sheet show "excelId"
mentdb
["sheet2"]
Used in use cases (3 recipes)

    SQL developer in MQL › Export a query to CSV, Excel and PDF files
    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
Often used with

    excelx cell get  ·  excelx close  ·  excelx load  ·  excelx cell eval  ·  excelx cell set  ·  excelx load empty  ·  excelx save  ·  excelx sheet add

excelx cell get <excelId> <sheetName> <rowIndex> <colIndex>


Description

    Get a cell value.

Parameters

    excelId:   The Excel id - string - required
    sheetName:   The sheet name - string - required
    rowIndex:   The row index - number - required
    colIndex:   The column index - number - required
admin
excelx cell get "excelId" "sheet1" 1 1
mentdb
25
Used in use cases (5 recipes)

    SQL developer in MQL › Export a query to CSV, Excel and PDF files
    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
    Excel reporting analyst › Sales ledger workbook queried with SQL
    Excel reporting analyst › Clean a contact list: trim, validate emails, flag duplicates
Often used with

    excelx close  ·  excelx cell eval  ·  excelx cell set  ·  excelx load empty  ·  excelx save  ·  excelx sheet add  ·  excelx load  ·  excelx sheet max_row

excelx cell eval <excelId> <sheetName> <rowIndex> <colIndex>


Description

    Evaluate a cell.

Parameters

    excelId:   The Excel id - string - required
    sheetName:   The sheet name - string - required
    rowIndex:   The row index - number - required
    colIndex:   The column index - number - required
admin
excelx cell eval "excelId" "sheet1" 1 1
mentdb
25
Used in use cases (8 recipes)

    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Loan amortization schedule computed by Excel formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
    Excel reporting analyst › Investment appraisal: NPV, IRR and discounted payback
    Excel reporting analyst › Sales ledger workbook queried with SQL
    Excel reporting analyst › Fixed assets register: straight-line and declining balance
    and 2 more in the Use cases
Often used with

    excelx cell set  ·  excelx load empty  ·  excelx sheet add  ·  excelx close  ·  excelx save  ·  json doc  ·  excelx cell get  ·  excelx cell format

excelx cell ref <cell>


Description

    Get a cell reference.

Parameters

    cell:   The cell (ex: Z3) - string - required
admin
excelx cell ref "Z3";
mentdb
{"col": 25,"row": 2}
Used in use cases (2 recipes)

    Excel reporting analyst › Loan amortization schedule computed by Excel formulas
    Excel reporting analyst › Investment appraisal: NPV, IRR and discounted payback
Often used with

    excelx cell eval  ·  excelx cell set  ·  excelx load empty  ·  excelx sheet add  ·  excelx close  ·  excelx close_all  ·  excelx save  ·  json doc

excelx cell set <excelId> <sheetName> <rowIndex> <colIndex> <value> <type>


Description

    Set the value of a cell.

Parameters

    excelId:   The Excel id - string - required
    sheetName:   The sheet name - string - required
    rowIndex:   The row index - number - required
    colIndex:   The column index - number - required
    value:   The value - string - required
    type:   The type (STR|NUM|BOOL|DATETIME|FORMULA|BLANK) - string - required
admin
excelx cell set "excelId" "sheet1" 1 1 "value" STR|NUM|BOOL|DATETIME|FORMULA|BLANK;
mentdb
1
admin
excelx close_all; excelx load empty "excelId"; excelx sheet add "excelId" "sheet1"; excelx cell set "excelId" "sheet1" 1 1 "str" STR; excelx cell set "excelId" "sheet1" 1 2 ".3478" NUM; excelx cell set "excelId" "sheet1" 1 3 true BOOL; excelx cell set "excelId" "sheet1" 1 4 (date systimestamp;) DATETIME; excelx cell set "excelId" "sheet1" 1 5 "C2+1" FORMULA; excelx cell set "excelId" "sheet1" 1 6 "" BLANK; excelx save "excelId" "/Users/jimmitry/Desktop/test.xlsx";
mentdb
1
Used in use cases (8 recipes)

    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Loan amortization schedule computed by Excel formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
    Excel reporting analyst › Investment appraisal: NPV, IRR and discounted payback
    Excel reporting analyst › Sales ledger workbook queried with SQL
    Excel reporting analyst › Fixed assets register: straight-line and declining balance
    and 2 more in the Use cases
Often used with

    excelx cell eval  ·  excelx load empty  ·  excelx sheet add  ·  excelx close  ·  excelx save  ·  json doc  ·  excelx cell get  ·  excelx cell format

excelx cell format <excelId> <sheetName> <rowIndex> <colIndex> <format> <config>


Description

    Set the format of a cell.

Parameters

    excelId:   The Excel id - string - required
    sheetName:   The sheet name - string - required
    rowIndex:   The row index - number - required
    colIndex:   The column index - number - required
    format:   The format (example: 'm/d/yy h:mm' | '0%' ...) - string - required
    config:   The JSON format configuration - string - required
admin
json load "style" "{}"; json iobject "style" / "HorizontalAlignment" "CENTER" STR; json iobject "style" / "HorizontalAlignment" "CENTER_SELECTION" STR; json iobject "style" / "HorizontalAlignment" "DISTRIBUTED" STR; json iobject "style" / "HorizontalAlignment" "FILL" STR; json iobject "style" / "HorizontalAlignment" "GENERAL" STR; json iobject "style" / "HorizontalAlignment" "JUSTIFY" STR; json iobject "style" / "HorizontalAlignment" "LEFT" STR; json iobject "style" / "HorizontalAlignment" "RIGHT" STR; json iobject "style" / "VerticalAlignment" "BOTTOM" STR; json iobject "style" / "VerticalAlignment" "TOP" STR; json iobject "style" / "VerticalAlignment" "DISTRIBUTED" STR; json iobject "style" / "VerticalAlignment" "JUSTIFY" STR; json iobject "style" / "VerticalAlignment" "CENTER" STR; json iobject "style" / "BorderBottom" "DASH_DOT" STR; json iobject "style" / "BorderBottom" "DASH_DOT_DOT" STR; json iobject "style" / "BorderBottom" "DASHED" STR; json iobject "style" / "BorderBottom" "DOTTED" STR; json iobject "style" / "BorderBottom" "DOUBLE" STR; json iobject "style" / "BorderBottom" "HAIR" STR; json iobject "style" / "BorderBottom" "MEDIUM" STR; json iobject "style" / "BorderBottom" "MEDIUM_DASH_DOT" STR; json iobject "style" / "BorderBottom" "MEDIUM_DASH_DOT_DOT" STR; json iobject "style" / "BorderBottom" "MEDIUM_DASHED" STR; json iobject "style" / "BorderBottom" "NONE" STR; json iobject "style" / "BorderBottom" "SLANTED_DASH_DOT" STR; json iobject "style" / "BorderBottom" "THICK" STR; json iobject "style" / "BorderBottom" "THIN" STR; json iobject "style" / "BorderTop" "DASH_DOT" STR; json iobject "style" / "BorderTop" "DASH_DOT_DOT" STR; json iobject "style" / "BorderTop" "DASHED" STR; json iobject "style" / "BorderTop" "DOTTED" STR; json iobject "style" / "BorderTop" "DOUBLE" STR; json iobject "style" / "BorderTop" "HAIR" STR; json iobject "style" / "BorderTop" "MEDIUM" STR; json iobject "style" / "BorderTop" "MEDIUM_DASH_DOT" STR; json iobject "style" / "BorderTop" "MEDIUM_DASH_DOT_DOT" STR; json iobject "style" / "BorderTop" "MEDIUM_DASHED" STR; json iobject "style" / "BorderTop" "NONE" STR; json iobject "style" / "BorderTop" "SLANTED_DASH_DOT" STR; json iobject "style" / "BorderTop" "THICK" STR; json iobject "style" / "BorderTop" "THIN" STR; json iobject "style" / "BorderLeft" "DASH_DOT" STR; json iobject "style" / "BorderLeft" "DASH_DOT_DOT" STR; json iobject "style" / "BorderLeft" "DASHED" STR; json iobject "style" / "BorderLeft" "DOTTED" STR; json iobject "style" / "BorderLeft" "DOUBLE" STR; json iobject "style" / "BorderLeft" "HAIR" STR; json iobject "style" / "BorderLeft" "MEDIUM" STR; json iobject "style" / "BorderLeft" "MEDIUM_DASH_DOT" STR; json iobject "style" / "BorderLeft" "MEDIUM_DASH_DOT_DOT" STR; json iobject "style" / "BorderLeft" "MEDIUM_DASHED" STR; json iobject "style" / "BorderLeft" "NONE" STR; json iobject "style" / "BorderLeft" "SLANTED_DASH_DOT" STR; json iobject "style" / "BorderLeft" "THICK" STR; json iobject "style" / "BorderLeft" "THIN" STR; json iobject "style" / "BorderRight" "DASH_DOT" STR; json iobject "style" / "BorderRight" "DASH_DOT_DOT" STR; json iobject "style" / "BorderRight" "DASHED" STR; json iobject "style" / "BorderRight" "DOTTED" STR; json iobject "style" / "BorderRight" "DOUBLE" STR; json iobject "style" / "BorderRight" "HAIR" STR; json iobject "style" / "BorderRight" "MEDIUM" STR; json iobject "style" / "BorderRight" "MEDIUM_DASH_DOT" STR; json iobject "style" / "BorderRight" "MEDIUM_DASH_DOT_DOT" STR; json iobject "style" / "BorderRight" "MEDIUM_DASHED" STR; json iobject "style" / "BorderRight" "NONE" STR; json iobject "style" / "BorderRight" "SLANTED_DASH_DOT" STR; json iobject "style" / "BorderRight" "THICK" STR; json iobject "style" / "BorderRight" "THIN" STR; json iobject "style" / "BottomBorderColor" "0,0,0" STR; json iobject "style" / "LeftBorderColor" "0,0,0" STR; json iobject "style" / "RightBorderColor" "0,0,0" STR; json iobject "style" / "TopBorderColor" "0,0,0" STR; json iobject "style" / "Hidden" false STR; json iobject "style" / "Locked" false STR; json iobject "style" / "QuotePrefixed" false STR; json iobject "style" / "ShrinkToFit" false STR; json iobject "style" / "WrapText" false STR; json iobject "style" / "Font" "{}" OBJ; json iobject "style" /Font "fontName" "Arial" STR; json iobject "style" /Font "size" 16 STR; json iobject "style" /Font "color" "255,0,0" STR; json iobject "style" /Font "bold" true STR; json iobject "style" /Font "italic" true STR; json iobject "style" / "Rotation" 90 STR; json iobject "style" / "FillPattern" "solid_foreground" STR; json iobject "style" / "FillPattern" "alt_bars" STR; json iobject "style" / "FillPattern" "big_spots" STR; json iobject "style" / "FillPattern" "bricks" STR; json iobject "style" / "FillPattern" "diamonds" STR; json iobject "style" / "FillPattern" "fine_dots" STR; json iobject "style" / "FillPattern" "least_dots" STR; json iobject "style" / "FillPattern" "less_dots" STR; json iobject "style" / "FillPattern" "no_fill" STR; json iobject "style" / "FillPattern" "sparse_dots" STR; json iobject "style" / "FillPattern" "squares" STR; json iobject "style" / "FillPattern" "thick_backward_diag" STR; json iobject "style" / "FillPattern" "thick_forward_diag" STR; json iobject "style" / "FillPattern" "thick_horz_bands" STR; json iobject "style" / "FillPattern" "thick_vert_bands" STR; json iobject "style" / "FillPattern" "thin_backward_diag" STR; json iobject "style" / "FillPattern" "thin_forward_diag" STR; json iobject "style" / "FillPattern" "thin_horz_bands" STR; json iobject "style" / "FillPattern" "thin_vert_bands" STR; json iobject "style" / "FillForegroundColor" "AQUA" STR; json iobject "style" / "FillForegroundColor" "AUTOMATIC" STR; json iobject "style" / "FillForegroundColor" "BLACK" STR; json iobject "style" / "FillForegroundColor" "BLUE" STR; json iobject "style" / "FillForegroundColor" "BLUE_GREY" STR; json iobject "style" / "FillForegroundColor" "BRIGHT_GREEN" STR; json iobject "style" / "FillForegroundColor" "BROWN" STR; json iobject "style" / "FillForegroundColor" "CORAL" STR; json iobject "style" / "FillForegroundColor" "CORNFLOWER_BLUE" STR; json iobject "style" / "FillForegroundColor" "DARK_BLUE" STR; json iobject "style" / "FillForegroundColor" "DARK_GREEN" STR; json iobject "style" / "FillForegroundColor" "DARK_RED" STR; json iobject "style" / "FillForegroundColor" "DARK_TEAL" STR; json iobject "style" / "FillForegroundColor" "DARK_YELLOW" STR; json iobject "style" / "FillForegroundColor" "GOLD" STR; json iobject "style" / "FillForegroundColor" "GREEN" STR; json iobject "style" / "FillForegroundColor" "GREY_25_PERCENT" STR; json iobject "style" / "FillForegroundColor" "GREY_40_PERCENT" STR; json iobject "style" / "FillForegroundColor" "GREY_50_PERCENT" STR; json iobject "style" / "FillForegroundColor" "GREY_80_PERCENT" STR; json iobject "style" / "FillForegroundColor" "INDIGO" STR; json iobject "style" / "FillForegroundColor" "LAVENDER" STR; json iobject "style" / "FillForegroundColor" "LEMON_CHIFFON" STR; json iobject "style" / "FillForegroundColor" "LIGHT_BLUE" STR; json iobject "style" / "FillForegroundColor" "LIGHT_CORNFLOWER_BLUE" STR; json iobject "style" / "FillForegroundColor" "LIGHT_GREEN" STR; json iobject "style" / "FillForegroundColor" "LIGHT_ORANGE" STR; json iobject "style" / "FillForegroundColor" "LIGHT_TURQUOISE" STR; json iobject "style" / "FillForegroundColor" "LIGHT_YELLOW" STR; json iobject "style" / "FillForegroundColor" "LIME" STR; json iobject "style" / "FillForegroundColor" "MAROON" STR; json iobject "style" / "FillForegroundColor" "OLIVE_GREEN" STR; json iobject "style" / "FillForegroundColor" "ORANGE" STR; json iobject "style" / "FillForegroundColor" "ORCHID" STR; json iobject "style" / "FillForegroundColor" "PALE_BLUE" STR; json iobject "style" / "FillForegroundColor" "PINK" STR; json iobject "style" / "FillForegroundColor" "PLUM" STR; json iobject "style" / "FillForegroundColor" "RED" STR; json iobject "style" / "FillForegroundColor" "ROSE" STR; json iobject "style" / "FillForegroundColor" "ROYAL_BLUE" STR; json iobject "style" / "FillForegroundColor" "SEA_GREEN" STR; json iobject "style" / "FillForegroundColor" "SKY_BLUE" STR; json iobject "style" / "FillForegroundColor" "TAN" STR; json iobject "style" / "FillForegroundColor" "TEAL" STR; json iobject "style" / "FillForegroundColor" "TURQUOISE" STR; json iobject "style" / "FillForegroundColor" "VIOLET" STR; json iobject "style" / "FillForegroundColor" "WHITE" STR; json iobject "style" / "FillForegroundColor" "YELLOW" STR; json iobject "style" / "FillBackgroundColor" "AQUA" STR; json iobject "style" / "FillBackgroundColor" "AUTOMATIC" STR; json iobject "style" / "FillBackgroundColor" "BLACK" STR; json iobject "style" / "FillBackgroundColor" "BLUE" STR; json iobject "style" / "FillBackgroundColor" "BLUE_GREY" STR; json iobject "style" / "FillBackgroundColor" "BRIGHT_GREEN" STR; json iobject "style" / "FillBackgroundColor" "BROWN" STR; json iobject "style" / "FillBackgroundColor" "CORAL" STR; json iobject "style" / "FillBackgroundColor" "CORNFLOWER_BLUE" STR; json iobject "style" / "FillBackgroundColor" "DARK_BLUE" STR; json iobject "style" / "FillBackgroundColor" "DARK_GREEN" STR; json iobject "style" / "FillBackgroundColor" "DARK_RED" STR; json iobject "style" / "FillBackgroundColor" "DARK_TEAL" STR; json iobject "style" / "FillBackgroundColor" "DARK_YELLOW" STR; json iobject "style" / "FillBackgroundColor" "GOLD" STR; json iobject "style" / "FillBackgroundColor" "GREEN" STR; json iobject "style" / "FillBackgroundColor" "GREY_25_PERCENT" STR; json iobject "style" / "FillBackgroundColor" "GREY_40_PERCENT" STR; json iobject "style" / "FillBackgroundColor" "GREY_50_PERCENT" STR; json iobject "style" / "FillBackgroundColor" "GREY_80_PERCENT" STR; json iobject "style" / "FillBackgroundColor" "INDIGO" STR; json iobject "style" / "FillBackgroundColor" "LAVENDER" STR; json iobject "style" / "FillBackgroundColor" "LEMON_CHIFFON" STR; json iobject "style" / "FillBackgroundColor" "LIGHT_BLUE" STR; json iobject "style" / "FillBackgroundColor" "LIGHT_CORNFLOWER_BLUE" STR; json iobject "style" / "FillBackgroundColor" "LIGHT_GREEN" STR; json iobject "style" / "FillBackgroundColor" "LIGHT_ORANGE" STR; json iobject "style" / "FillBackgroundColor" "LIGHT_TURQUOISE" STR; json iobject "style" / "FillBackgroundColor" "LIGHT_YELLOW" STR; json iobject "style" / "FillBackgroundColor" "LIME" STR; json iobject "style" / "FillBackgroundColor" "MAROON" STR; json iobject "style" / "FillBackgroundColor" "OLIVE_GREEN" STR; json iobject "style" / "FillBackgroundColor" "ORANGE" STR; json iobject "style" / "FillBackgroundColor" "ORCHID" STR; json iobject "style" / "FillBackgroundColor" "PALE_BLUE" STR; json iobject "style" / "FillBackgroundColor" "PINK" STR; json iobject "style" / "FillBackgroundColor" "PLUM" STR; json iobject "style" / "FillBackgroundColor" "RED" STR; json iobject "style" / "FillBackgroundColor" "ROSE" STR; json iobject "style" / "FillBackgroundColor" "ROYAL_BLUE" STR; json iobject "style" / "FillBackgroundColor" "SEA_GREEN" STR; json iobject "style" / "FillBackgroundColor" "SKY_BLUE" STR; json iobject "style" / "FillBackgroundColor" "TAN" STR; json iobject "style" / "FillBackgroundColor" "TEAL" STR; json iobject "style" / "FillBackgroundColor" "TURQUOISE" STR; json iobject "style" / "FillBackgroundColor" "VIOLET" STR; json iobject "style" / "FillBackgroundColor" "WHITE" STR; json iobject "style" / "FillBackgroundColor" "YELLOW" STR; excelx cell format "excelId" "sheet1" 1 1 "@" (json doc "style")
mentdb
1
Used in use cases (3 recipes)

    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Fixed assets register: straight-line and declining balance
    Excel reporting analyst › Clean a contact list: trim, validate emails, flag duplicates
Often used with

    excelx cell eval  ·  excelx cell set  ·  excelx close  ·  excelx load empty  ·  excelx save  ·  excelx sheet add  ·  json doc  ·  excelx cell get

excelx close <excelId>


Description

    Close an ExcelX document.

Parameters

    excelId:   The Excel id - string - required
admin
excelx close "excelId";
mentdb
1
Used in use cases (8 recipes)

    SQL developer in MQL › Export a query to CSV, Excel and PDF files
    Excel reporting analyst › Quarterly sales workbook with growth formulas
    Excel reporting analyst › Bank CSV to a category summary with frozen values
    Excel reporting analyst › Investment appraisal: NPV, IRR and discounted payback
    Excel reporting analyst › Sales ledger workbook queried with SQL
    Excel reporting analyst › Fixed assets register: straight-line and declining balance
    and 2 more in the Use cases
Often used with

    excelx cell eval  ·  excelx cell set  ·  excelx load empty  ·  excelx save  ·  excelx sheet add  ·  excelx cell get  ·  json doc  ·  excelx cell format

excelx close_all


Description

    Close all ExcelX documents.

admin
excelx close_all;
mentdb
1
Used in use cases (1 recipe)

    Excel reporting analyst › Loan amortization schedule computed by Excel formulas
Often used with

    excelx cell eval  ·  excelx cell ref  ·  excelx cell set  ·  excelx load empty  ·  excelx sheet add  ·  json doc  ·  json iarray  ·  plot chart

excelx read <path>


Description

    Read the first sheet of an xls or xlsx file into JSON rows: the first line gives the names, numbers become integers or decimals, dates become yyyy-MM-dd (or yyyy-MM-ddTHH:mm:ss), formulas give their last computed value, empty rows are skipped

Parameters

    path:   The file path - String - required
admin
excelx write "tmp/tuto_etl/report.xlsx" "[{\"shop\": \"Paris\", \"month\": \"2026-01\", \"amount\": 1250.5, \"code\": \"00042\"}, {\"shop\": \"Lyon\", \"month\": \"2026-01\", \"amount\": 980, \"code\": \"00043\"}, {\"shop\": \"Paris\", \"month\": \"2026-02\", \"amount\": 1310.25, \"code\": \"00042\"}]" "{\"sheet\": \"Sales\"}"; -> "[res]" (excelx read "tmp/tuto_etl/report.xlsx"); file delete "tmp/tuto_etl/report.xlsx"; [res]
mentdb
[{"shop": "Paris", "month": "2026-01", "amount": 1250.5, "code": "00042"}, {"shop": "Lyon", "month": "2026-01", "amount": 980, "code": "00043"}, {"shop": "Paris", "month": "2026-02", "amount": 1310.25, "code": "00042"}]

excelx read <path> <json_params>


Description

    Read a sheet with parameters: sheet (name, or index from 0: a text is a sheet name first, so a sheet named 2024 is found by its name), header_row (1; 0 = no header, columns c1, c2...), first_row (first data line, from 1), max_rows, columns (forced names), types (auto | string: the text shown by Excel, numbers in the General format written in full without exponent), skip_empty_rows (true), password

Parameters

    path:   The file path - String - required
    json_params:   The parameters - String - required
admin
excelx write "tmp/tuto_etl/book.xlsx" "{\"Sales\": [{\"shop\": \"Paris\", \"month\": \"2026-01\", \"amount\": 1250.5, \"code\": \"00042\"}, {\"shop\": \"Lyon\", \"month\": \"2026-01\", \"amount\": 980, \"code\": \"00043\"}, {\"shop\": \"Paris\", \"month\": \"2026-02\", \"amount\": 1310.25, \"code\": \"00042\"}], \"Shops\": [{\"shop\": \"Paris\", \"opened\": \"2019-03-01\"}, {\"shop\": \"Lyon\", \"opened\": \"2021-09-15\"}]}" "{\"date_columns\": [\"opened\"]}"; -> "[res]" (excelx read "tmp/tuto_etl/book.xlsx" "{\"sheet\": \"Shops\"}"); file delete "tmp/tuto_etl/book.xlsx"; [res]
mentdb
[{"shop": "Paris", "opened": "2019-03-01"}, {"shop": "Lyon", "opened": "2021-09-15"}]
Used in use cases (1 recipe)

    Integrator & ETL developer › From a REST API answer to an Excel report: nested orders flattened into lines
Often used with

    excelx sheets  ·  excelx write  ·  file delete  ·  file exist  ·  file mkdir  ·  json to_rows  ·  table group_by

excelx sheets <path>


Description

    The sheets of an xls or xlsx file: name, index, number of rows (last used row) and of columns

Parameters

    path:   The file path - String - required
admin
excelx write "tmp/tuto_etl/sheets.xlsx" "{\"Sales\": [{\"shop\": \"Paris\", \"month\": \"2026-01\", \"amount\": 1250.5, \"code\": \"00042\"}, {\"shop\": \"Lyon\", \"month\": \"2026-01\", \"amount\": 980, \"code\": \"00043\"}, {\"shop\": \"Paris\", \"month\": \"2026-02\", \"amount\": 1310.25, \"code\": \"00042\"}], \"Empty\": []}"; -> "[res]" (excelx sheets "tmp/tuto_etl/sheets.xlsx"); file delete "tmp/tuto_etl/sheets.xlsx"; [res]
mentdb
[{"name": "Sales", "index": 0, "rows": 4, "columns": 4}, {"name": "Empty", "index": 1, "rows": 1, "columns": 0}]
Used in use cases (1 recipe)

    Integrator & ETL developer › From a REST API answer to an Excel report: nested orders flattened into lines
Often used with

    excelx read  ·  excelx write  ·  file delete  ·  file exist  ·  file mkdir  ·  json to_rows  ·  table group_by

excelx write <path> <json_rows>


Description

    Write JSON rows into an xlsx (or xls) file: an array of rows gives one sheet, an object {"sheet name": rows, ...} gives one sheet per key; bold grey header, frozen first line, column widths adjusted

Parameters

    path:   The file path (.xlsx or .xls) - String - required
    json_rows:   The rows, or an object of sheets - String - required
admin
-> "[res]" (excelx write "tmp/tuto_etl/two.xlsx" "{\"Sales\": [{\"shop\": \"Paris\", \"month\": \"2026-01\", \"amount\": 1250.5, \"code\": \"00042\"}, {\"shop\": \"Lyon\", \"month\": \"2026-01\", \"amount\": 980, \"code\": \"00043\"}, {\"shop\": \"Paris\", \"month\": \"2026-02\", \"amount\": 1310.25, \"code\": \"00042\"}], \"Summary\": [{\"total\": 3540.75}]}"); file delete "tmp/tuto_etl/two.xlsx"; [res]
mentdb
{"file": "tmp/tuto_etl/two.xlsx", "sheets": [{"name": "Sales", "rows": 3}, {"name": "Summary", "rows": 1}]}

excelx write <path> <json_rows> <json_params>


Description

    Write with parameters: sheet (name of the sheet for an array, Sheet1), columns (order and subset), mode (create: new file | replace_sheet: keep the other sheets of an existing file), date_columns (columns of yyyy-MM-dd[THH:mm:ss] texts written as real Excel dates), freeze (true), autosize (true, up to 10 000 rows). Large sheets (more than 50 000 rows) are written in streaming

Parameters

    path:   The file path - String - required
    json_rows:   The rows - String - required
    json_params:   The parameters - String - required
admin
excelx write "tmp/tuto_etl/monthly.xlsx" "{\"Sales\": [{\"shop\": \"Paris\", \"month\": \"2026-01\", \"amount\": 1250.5, \"code\": \"00042\"}, {\"shop\": \"Lyon\", \"month\": \"2026-01\", \"amount\": 980, \"code\": \"00043\"}, {\"shop\": \"Paris\", \"month\": \"2026-02\", \"amount\": 1310.25, \"code\": \"00042\"}], \"Notes\": [{\"note\": \"draft\"}]}"; excelx write "tmp/tuto_etl/monthly.xlsx" "[{\"note\": \"final\", \"date\": \"2026-03-01\"}]" "{\"sheet\": \"Notes\", \"mode\": \"replace_sheet\", \"date_columns\": [\"date\"]}"; -> "[res]" (concat (excelx sheets "tmp/tuto_etl/monthly.xlsx") " " (excelx read "tmp/tuto_etl/monthly.xlsx" "{\"sheet\": \"Notes\"}")); file delete "tmp/tuto_etl/monthly.xlsx"; [res]
mentdb
[{"name": "Sales", "index": 0, "rows": 4, "columns": 4}, {"name": "Notes", "index": 1, "rows": 2, "columns": 2}] [{"note": "final", "date": "2026-03-01"}]
Used in use cases (1 recipe)

    Integrator & ETL developer › From a REST API answer to an Excel report: nested orders flattened into lines
Often used with

    excelx read  ·  excelx sheets  ·  file delete  ·  file exist  ·  file mkdir  ·  json to_rows  ·  table group_by