Rows, Columns and Ranges — insert, delete, copy
level: basic / verified: 2026-10-04 / tabs: Node.js・Python・Java・PHP・WebAssembly / Back to Index / 日本語
What you want to do
Insert / delete / clear / copy rows, columns and cell ranges on a worksheet. Also set row height, column width, hidden state and more with the Row / Col objects.
The four operations compared
| Operation | Behavior | For rows / columns | For cell ranges |
|---|---|---|---|
| insert | Inserts blanks and shifts existing data | insertRow(start, n) / insertCol(start, n) | insertCell(A1C1, direction) |
| delete | Deletes and shifts existing data | deleteRow(start, n) / deleteCol(start, n) | deleteCell(A1C1, direction) |
| clear | Resets contents and formatting to the initial state. Does not shift | clearRow(start, n) / clearCol(start, n) | clearCell(A1C1) |
| copy | Overwrite-copies from another sheet (or the same sheet) | copyRow(fromSheet, fromRow, n, toRow, copyType?) | copyCell(fromSheet, fromA1C1, toA1, copyType?) |
The direction of insertCell / deleteCell uses enums.XlInsertDirection.
| Value | Number | Purpose |
|---|---|---|
InsertDirectionRight | 1 | Shift cells right when inserting |
InsertDirectionDown | 2 | Shift cells down when inserting |
DeleteDirectionLeft | 3 | Shift cells left when deleting |
DeleteDirectionUp | 4 | Shift cells up when deleting |
Code 1: Rows, columns and cell ranges
Output:
// rows-cols.js — insert/delete/clear/copy for rows, columns and cell ranges
const { nodeosbxl, enums } = require("nodeosbxl");
const app = new nodeosbxl.App();
const wb = app.createWorkBook("rows-cols.xlsx");
const ws = wb.openWorkSheet("Sheet1");
// Print a range on one line, ordered by row → column (· marks an empty cell)
const dump = (label, range) => {
const v = ws.getRange(range).getValue();
const keys = Object.keys(v).map((k) => /^([A-Z]+)(\d+)$/.exec(k)).sort((a, b) =>
Number(a[2]) - Number(b[2]) ||
app.convertToColumnNumber(a[1]) - app.convertToColumnNumber(b[1]));
console.log(`${label} ${keys.map((m) => `${m[1]}${m[2]}=${v[m[1] + m[2]] || "·"}`).join(" ")}`);
};
// Fill A1:C5 with r{row}c{col}
const fill = () => {
for (let r = 1; r <= 5; r++) for (let c = 1; c <= 3; c++) ws.getCells(r, c).setValue(`r${r}c${c}`);
};
fill();
dump("[initial] col A:", "A1:A5");
// ---------- rows ----------
ws.insertRow(3, 2); // insert 2 rows BEFORE row 3 → existing data shifts down
dump("[insertRow] col A:", "A1:A7");
ws.deleteRow(3, 2); // delete 2 rows starting at row 3 → rows below move up
dump("[deleteRow] col A:", "A1:A5");
ws.clearRow(2, 1); // clear row 2 (positions do not shift)
dump("[clearRow] col A:", "A1:A5");
ws.copyRow("Sheet1", 1, 1, 4); // overwrite-copy row 1 of the same sheet onto row 4
dump("[copyRow] col A:", "A1:A5");
fill();
// ---------- columns ----------
ws.insertCol(2, 1); // insert 1 column before column B
dump("[insertCol] row 1:", "A1:D1");
ws.deleteCol(2, 1); // delete the column B just inserted
dump("[deleteCol] row 1:", "A1:C1");
ws.clearCol(3, 1); // clear column C
dump("[clearCol] row 1:", "A1:C1");
fill();
ws.copyCol("Sheet1", 1, 2, 5); // copy columns A,B onto columns E,F
dump("[copyCol] row 1:", "A1:F1");
// ---------- cell ranges ----------
ws.insertCell("B2:B3", enums.XlInsertDirection.InsertDirectionDown);
dump("[insertCell] col B:", "B1:B5");
ws.deleteCell("B2:B3", enums.XlInsertDirection.DeleteDirectionUp);
dump("[deleteCell] col B:", "B1:B5");
ws.clearCell("A1:B2");
dump("[clearCell] A1:B2:", "A1:B2");
ws.copyCell("Sheet1", "A3:B4", "F8");
dump("[copyCell] F8:G9:", "F8:G9");
wb.save();
wb.close();
[initial] col A: A1=r1c1 A2=r2c1 A3=r3c1 A4=r4c1 A5=r5c1
[insertRow] col A: A1=r1c1 A2=r2c1 A3=· A4=· A5=r3c1 A6=r4c1 A7=r5c1
[deleteRow] col A: A1=r1c1 A2=r2c1 A3=r3c1 A4=r4c1 A5=r5c1
[clearRow] col A: A1=r1c1 A2=· A3=r3c1 A4=r4c1 A5=r5c1
[copyRow] col A: A1=r1c1 A2=· A3=r3c1 A4=r1c1 A5=r5c1
[insertCol] row 1: A1=r1c1 B1=· C1=r1c2 D1=r1c3
[deleteCol] row 1: A1=r1c1 B1=r1c2 C1=r1c3
[clearCol] row 1: A1=r1c1 B1=r1c2 C1=·
[copyCol] row 1: A1=r1c1 B1=r1c2 C1=r1c3 D1=· E1=r1c1 F1=r1c2
[insertCell] col B: B1=r1c2 B2=· B3=· B4=r2c2 B5=r3c2
[deleteCell] col B: B1=r1c2 B2=r2c2 B3=r3c2 B4=r4c2 B5=r5c2
[clearCell] A1:B2: A1=· B1=· A2=· B2=·
[copyCell] F8:G9: F8=r3c1 G8=r3c2 F9=r4c1 G9=r4c2
· marks an empty cell. You can see that insertRow left rows 3 and 4 blank and shifted the existing data down, that clearRow did not shift positions, and that copyRow overwrote row 4 with the contents of row 1.
Code 2: Row / Col objects
Output:
// row-col-props.js — row height, column width, hidden state, autofit
const { nodeosbxl } = require("nodeosbxl");
const app = new nodeosbxl.App();
const wb = app.createWorkBook("row-col-props.xlsx");
const ws = wb.openWorkSheet("Sheet1");
// ---------- Row ----------
const row = ws.getRow(1); // row 1
console.log("default height =", row.getHeight());
row.setHeight(30);
console.log("after setHeight(30) =", row.getHeight());
console.log("getHidden default =", row.getHidden(), "/ getOutlineLevel =", row.getOutlineLevel());
ws.getRow(2).setHidden(true); // hide row 2
console.log("row 2 hidden =", ws.getRow(2).getHidden());
// ---------- Col ----------
const col = ws.getCol(1); // column 1 (column A). Column numbers are passed as numbers
console.log("default col width = columnWidth:", col.getColumnWidth(), "/ width:", col.getWidth());
col.setColumnWidth(20);
console.log("setColumnWidth(20) -> columnWidth=" + col.getColumnWidth(),
"/ width=" + col.getWidth());
ws.getCol(2).setHidden(true); // hide column B
console.log("col B hidden =", ws.getCol(2).getHidden());
ws.getRange("C1:C1").setValue("Sample string for autofit");
console.log("col C bestFit default =", ws.getCol(3).getBestFit());
ws.getCol(3).setBestFit(true); // autofit the column width to the contents
console.log("setBestFit(true) -> bestFit =", ws.getCol(3).getBestFit(),
"/ columnWidth =", ws.getCol(3).getColumnWidth());
// Outlines (grouping) are set on the WorkSheet side
ws.setRowOutline(4, 6, 1); // group rows 4-6 at level 1
ws.setColOutline(2, 3, 1); // group columns B-C at level 1
console.log("row 4 outlineLevel =", ws.getRow(4).getOutlineLevel(),
"/ col B outlineLevel =", ws.getCol(2).getOutlineLevel());
wb.save();
wb.close();
default height = 18.75
after setHeight(30) = 30
getHidden default = false / getOutlineLevel = 0
row 2 hidden = true
default col width = columnWidth: 8.38 / width: 72
setColumnWidth(20) -> columnWidth=20 / width=165
col B hidden = true
col C bestFit default = false
setBestFit(true) -> bestFit = true / columnWidth = 26.38
row 4 outlineLevel = 1 / col B outlineLevel = 1
The column width produced by setBestFit(true) is computed from the cell contents and the book's default font, so the value varies by environment (above it is 26.38).
Notes
- Row and column numbers are all 1-based numeric values. Columns are passed as
1, not"A". To convert from a string, useapp.convertToColumnNumber("B")→2. insert/deleteshift data;cleardoes not. To just empty a row useclearRow()(existing data keeps its position); to remove the row itself usedeleteRow().- The
copyfamily takes the copy source as a "sheet name". Same-sheet copies also pass the sheet name.
Passingenums.XlCopyContentTypeas the fifth argumentcopyTypenarrows what gets copied.Value Number What gets copied CopyContentTypeAll0 Values, cell formats, and formats of formula cells (default) CopyContentTypeValueOnly1 Values only CopyContentTypeValueAndFormat2 Values and cell formats CopyContentTypeFormatOnly3 Cell formats only Col.getWidth()andCol.getColumnWidth()use different units. Measured: for a default column A,getWidth()= 72 andgetColumnWidth()= 8.38. AftersetColumnWidth(20),getWidth()= 165 andgetColumnWidth()= 20. OnlysetColumnWidth()(character width) can be set;getWidth()is read-only.setBestFit(true)recomputes the column width from the cell contents at the time of the call. It does not follow later content changes automatically, so call it after all data is in place.- Outlines (grouping) are
WorkSheetmethods. Set them withsetRowOutline(startRow, endRow, level)/setColOutline(startCol, endCol, level)and release them withclearRowOutline(level)/clearColOutline(level). Check the result withRow.getOutlineLevel()/Col.getOutlineLevel().
Caveats
- Use the
directionofinsertCell/deleteCellaccording to the operation. For insertion the intended values areInsertDirectionRight(1) /InsertDirectionDown(2); for deletion,DeleteDirectionLeft(3) /DeleteDirectionUp(4). - Some things are not copied by the
copyfamily. Even withCopyContentTypeAll, shapes (including charts), pivot tables, queryTable-style tables and data tables are not copied. - The third argument of
copyCellis a single "top-left cell of the destination".copyCell("Sheet1", "A3:B4", "F8")expands to F8:G9 (the range size is determined by the source). - For
copyRow/copyCol, the source is a sheet name and positions are row/column numbers (numeric). Do not use A1-style range strings — they are easy to confuse, so be careful.
See also
- Index
- Previous: Cell value types and number formats / Next: Bulk input of large data
- API reference