Working with Rows, Columns and Cell Ranges — Insert, Delete, Clear, Copy

level: basic ／ verified: 2026-10-01 ／ tabs: Node.js・Python・Java・WebAssembly ／ 日本語

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

OperationBehaviorFor rows / columnsFor cell ranges
insertInserts blanks and shifts existing datainsertRow(start, n) / insertCol(start, n)insertCell(A1C1, direction)
deleteDeletes and shifts existing datadeleteRow(start, n) / deleteCol(start, n)deleteCell(A1C1, direction)
clearResets contents and formatting to the initial state. Does not shiftclearRow(start, n) / clearCol(start, n)clearCell(A1C1)
copyOverwrite-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.

ValueNumberPurpose
InsertDirectionRight1Shift cells right when inserting
InsertDirectionDown2Shift cells down when inserting
DeleteDirectionLeft3Shift cells left when deleting
DeleteDirectionUp4Shift 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

Caveats

See also