-
Notifications
You must be signed in to change notification settings - Fork 528
excel sheet set
Modify worksheet-level properties: freeze panes, zoom, tab color, protection, page setup, and bulk operations like sort and merge.
Path: /{SheetName}
| Property | Accepted Values | Description |
|---|---|---|
name |
string | Rename the sheet |
freeze |
cell ref (e.g. A2, B2) or none
|
Freeze panes anchor. A2 freezes row 1; B2 freezes row 1 + column A |
zoom |
10-400 | Zoom scale percentage (default 100) |
tabColor |
hex RGB (e.g. 4472C4) or none
|
Sheet tab color |
protect |
true / false | Protect the sheet (prevents editing locked cells) |
password |
string | Protection password (use with protect=true) |
orientation |
portrait / landscape
|
Page orientation |
paperSize |
number | Paper size (1 = Letter, 9 = A4) |
printArea |
range (e.g. A1:F20) |
Print area |
fitToPage |
true / false | Fit content to page when printing |
header |
string | Print header text |
footer |
string | Print footer text |
Reorder rows by one or more columns. Works at two levels:
-
Sheet-level (
set /Sheet1 --prop sort=...) — sorts the entire used range (or the autofilter range if set). -
Range-level (
set '/Sheet1/A1:D100' --prop sort=...) — sorts only the specified range.
| Property | Accepted Values | Description |
|---|---|---|
sort |
COL DIR[, COL DIR ...] |
Sort spec. COL must be a column letter (A, B, ..., AA, ..., up to XFD); DIR is asc or desc (defaults to asc). Multi-key sorts are comma-separated. |
sortHeader |
true / false | Treat the first row of the sort range as a header (excluded from the reorder) |
Note:
sortdoes not accept header names. To find the column letter for a named header, runofficecli get data.xlsx /Sheet1/1:1(or query the header row) first, then pass the corresponding letter.
Sidecar metadata: Hyperlinks, comments, threaded comments, data validations, conditional formatting, and drawing anchors automatically follow their rows during sort.
Rejections: Ranges containing merged cells or shared formulas are rejected. Protected sheets are rejected unless sheetProtection@sort is explicitly set to false. Reversed ranges (e.g. C5:A1) are auto-normalized.
| Property | Accepted Values | Description |
|---|---|---|
range |
range (e.g. A1:F100) |
Set or update the autofilter range |
To remove the autofilter, set /{Sheet}/autofilter --prop range=none.
| Property | Accepted Values | Description |
|---|---|---|
merge |
true / false |
true merges the range; false unmerges |
# Freeze the top row officecli set data.xlsx /Sheet1 --prop freeze=A2 # Zoom and tab color officecli set data.xlsx /Sheet1 --prop zoom=125 --prop tabColor=4472C4 # Protect the sheet with a password officecli set data.xlsx /Sheet1 --prop protect=true --prop password="s3cret" # Sheet-level sort by column C descending (keeping header row 1 pinned) officecli set data.xlsx /Sheet1 --prop sort="C desc" --prop sortHeader=true # Multi-key sort (column A ascending, then column C descending) officecli set data.xlsx /Sheet1 --prop sort="A asc, C desc" --prop sortHeader=true # Range-level sort (only rows within A1:D100) officecli set data.xlsx '/Sheet1/A1:D100' --prop sort="A asc" --prop sortHeader=true # Landscape A4 with fit-to-page officecli set data.xlsx /Sheet1 --prop orientation=landscape --prop paperSize=9 --prop fitToPage=true # Set / update / remove autofilter officecli set data.xlsx /Sheet1/autofilter --prop range=A1:F100 officecli set data.xlsx /Sheet1/autofilter --prop range=none # Merge and unmerge a range officecli set data.xlsx '/Sheet1/A1:D1' --prop merge=true officecli set data.xlsx '/Sheet1/A1:D1' --prop merge=false
- Sheet get - Read sheet properties
- Sheet add - Add a new sheet
- set - Excel - All Excel set paths
Based on OfficeCLI v1.0.64