# Work with Worksheets

A tutorial showing how to work with worksheets in SpreadJS, including adding, removing, moving, renaming, and copying worksheets

## Content

SpreadJS allows you to perform various operations on worksheets to accomplish several important tasks.

## Add Worksheet

You can add a worksheet to workbook by using the [Workbook.addSheet](/spreadjs/api/classes/GC.Spread.Sheets.Workbook#addSheet) method.

```javascript
window.onload = function () {
    // Set the number of sheets to 3.
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
        sheetCount: 3
    });
    document.getElementById("button1").addEventListener("click", function () {
        // Create a new sheet and add it at the second position.
        var sheet = new GC.Spread.Sheets.Worksheet();
        sheet.name("The added sheet");
        spread.addSheet(1, sheet);
    });
};
```

## Remove Worksheet

You can remove a worksheet from workbook by using the [Workbook.removeSheet](/spreadjs/api/classes/GC.Spread.Sheets.Workbook#removeSheet) method.

```javascript
window.onload = function () {
    // Set the number of sheets to 3.
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
        sheetCount: 3
    });

    document.getElementById("button1").addEventListener("click", function () {
        // Delete the second sheet.
        spread.removeSheet(1);
    });
};
```

## Add Multiple Worksheets

You can set the number of sheets to add in a workbook by using the [Workbook.setSheetCount](/spreadjs/api/classes/GC.Spread.Sheets.Workbook#setSheetCount) method.

```javascript
window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
        sheetCount: 3
    });

    // Set the number of sheets to 5.
    spread.setSheetCount(5);
};
```

## Rename Worksheet

You can change the name of a worksheet by using the [Worksheet.name](/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#name) method.
![SpreadJS workbook showing renamed worksheet tabs and a customized sheet tab color set through worksheet API properties.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260708.afb37a.png?width=400)
This code example changes the sheet name and sets the sheet tab color.

```javascript
window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
        sheetCount: 3
    });

    // Set the number of sheets to 5.
    spread.setSheetCount(5);

    // Change the sheet names.
    spread.sheets[0].name("The first sheet");
    spread.sheets[1].name("The second sheet");

    // Change sheet information such as sheet tab display color.
    spread.sheets[0].options.sheetTabColor = "LemonChiffon";
};
```

>type=note
> **Note:** An error is thrown when using duplicate names or special characters in worksheet names within a workbook.
> Bind the sheet with `undefined` to receive the error message during runtime.

## Move Worksheet

You can change the sheet index and re-order the sheets in the tab strip by using the [Workbook.changeSheetIndex](/spreadjs/api/classes/GC.Spread.Sheets.Workbook#changeSheetIndex) method. This method accepts the sheet name and the target index number as parameters.
Alternatively, the [moveSheet](/spreadjs/api/modules/GC.Spread.Sheets.Commands#moveSheet) command can also be used to move a sheet.
The following example changes the sheet index of "Sheet2" and adds the text in a worksheet cell using the new sheet index.

```javascript
window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
        sheetCount: 5
    });

    spread.changeSheetIndex("Sheet2", 3);

    spread.sheets[3].getCell(0, 0).text("changeSheetIndex successful");

    // Alternatively, change sheet index by command.
    // spread.commandManager().execute({cmd: "moveSheet", sheetName: "Sheet2", targetIndex: 3});
};
```

![SpreadJS workbook showing a worksheet moved to a new tab index with a cell value confirming changeSheetIndex execution.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260708.f820f8.png?width=400)

## Copy Worksheet

You can copy a sheet to a new worksheet using the [copySheet](/spreadjs/api/modules/GC.Spread.Sheets.Commands#copySheet) command. This command accepts parameter values such as the sheet name, target index, and new sheet name. Additionally, you can also state whether to include data source binding in the copied sheet, which is true by default.
The copied sheet looks exactly the same as the original sheet. It modifies the relevant current sheet references in the content and retains references to other sheets.

| **Original Sheet** | **Copied Sheet** |
| -------------- | ------------ |
| ![SpreadJS original worksheet showing a table and formula reference before copying the sheet with the copySheet command.](https://cdn.mescius.io/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/origSheet-func.png) | ![SpreadJS copied worksheet showing updated current-sheet formula references after executing the copySheet command.](https://cdn.mescius.io/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/copySheet-func.png) |

The copied sheet also copies the custom names that belong to the original sheet.

| **Original Sheet** | **Copied Sheet** |
| -------------- | ------------ |
| ![SpreadJS original worksheet showing a custom name associated with the source sheet before the worksheet is copied.](https://cdn.mescius.io/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/origSheet-customName.png) | ![SpreadJS copied worksheet showing custom names retained from the original sheet after the copySheet command runs.](https://cdn.mescius.io/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/copySheet-customName.png) |

The copied sheet modifies all globally unique element names such as tables, slicers, charts, shapes, etc.

| **Original Sheet** | **Copied Sheet** |
| -------------- | ------------ |
| ![SpreadJS original worksheet showing a table element with a globally unique name before copying the worksheet.](https://cdn.mescius.io/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/origSheet-table.png) | ![SpreadJS copied worksheet showing a modified table name that preserves globally unique element names after copying.](https://cdn.mescius.io/document-site-files/images/8d606653-16a0-474d-b9dc-e2b4d01c2446/images/copySheet-table.png) |

The following example copies the "Sheet1" to a new worksheet and modifies the existing sheet reference in the formula and the table name.

```javascript
window.onload = function () {
    // Configure Workbook and Worksheet.
    var spread = new GC.Spread.Sheets.Workbook("ss", {
        sheetCount: 5
    });
    var activeSheet = spread.getActiveSheet();

    var tableData = {
        sales: [
            { orderDate: '1/6/2013', item: 'Pencil', units: 95, cost: 1.99 },
            { orderDate: '4/1/2013', item: 'Binder', units: 60, cost: 4.99 },
            { orderDate: '6/8/2013', item: 'Pen Set', units: 16, cost: 15.99 },
            { orderDate: '8/1/2013', item: 'Pencil', units: 20, cost: 24.99 },
            { orderDate: '10/8/2013', item: 'Binder', units: 31, cost: 16.99 }
        ]
    };

    activeSheet.getCell(7, 2).text("Total");
    activeSheet.getCell(7, 3).formula("=SUM(Sheet1!D2:D6)");

    var table = activeSheet.tables.add('Table1', 0, 0, 7, 4);
    var tableColumn1 = new GC.Spread.Sheets.Tables.TableColumn(1, "orderDate", "Order Date", "yyyy-mm-dd");
    var tableColumn2 = new GC.Spread.Sheets.Tables.TableColumn(2, "item", "Item");
    var tableColumn3 = new GC.Spread.Sheets.Tables.TableColumn(3, "units", "Units");
    var tableColumn4 = new GC.Spread.Sheets.Tables.TableColumn(4, "cost", "Cost");

    table.autoGenerateColumns(false);
    table.bind([tableColumn1, tableColumn2, tableColumn3, tableColumn4], 'sales', tableData);
    activeSheet.autoFitColumn(0);

    spread.commandManager().execute({
        cmd: "copySheet",
        sheetName: "Sheet1",
        targetIndex: 3,
        newName: "Sheet1 (2)",
        includeBindingSource: true
    });
};
```

## Hide or Unhide Worksheet

You can hide or unhide a worksheet in SpreadJS by accessing the sheet tab context menu. Right-click and select the options “Hide” or “Unhide…” to perform the operations.

The [Worksheet.visible](/spreadjs/api/classes/GC.Spread.Sheets.Worksheet#visible) class method can be used to set the visibility status of worksheets in runtime. It accepts boolean values to determine whether the worksheet is displayed. You can also set the visibility of a worksheet by using the [SheetTabVisible](/spreadjs/api/enums/GC.Spread.Sheets.SheetTabVisible) enumeration options as a parameter in **Worksheet.visible** method.

```javascript
// Configure Workbook and Worksheet
var spread = new GC.Spread.Sheets.Workbook("ss", { sheetCount: 5 });

// Hiding "Sheet2"
spread.getSheet(1).visible(false);

// Hiding "Sheet4"
spread.getSheet(3).visible(GC.Spread.Sheets.SheetTabVisible.hidden);
// or
// spread.getSheet(3).visible(0);
```

The **SheetTabVisible.veryHidden** enumeration option allows the application to hide a sheet in the Spread component. It means that a sheet cannot be set to visible through the UI by using the sheet tab context menu item "Unhide...".

A “very hidden” sheet can therefore be only set to visible by using the Worksheet.visible method.

```javascript
// Very hidden "Sheet3"
spread.getSheet(2).visible(GC.Spread.Sheets.SheetTabVisible.veryHidden);
```

>type=note
> **Note**: Users cannot perform the following operations on a “very hidden” sheet.
>
> * Cannot be set as an active worksheet.
> * Cannot select the worksheet.
> * Content of the sheet cannot be retrieved using find or replace.
> * The content of the sheet cannot be affected by GoTo operations.
> * The content of the sheet cannot be referenced by hyperlinks.
> * Cannot print the worksheet.

## Set Default Style

You can set the default sheet style by using the below example code:
![SpreadJS worksheet showing default cell style applied to the viewport, including background color, text color, borders, and formatter.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260708.966898.png?width=400)

```javascript
window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
        sheetCount: 3
    });
    var activeSheet = spread.getActiveSheet();

    // Set default style.
    activeSheet.setRowCount(5, GC.Spread.Sheets.SheetArea.viewport);
    activeSheet.setColumnCount(5, GC.Spread.Sheets.SheetArea.viewport);

    var defaultStyle = new GC.Spread.Sheets.Style();
    defaultStyle.backColor = "LemonChiffon";
    defaultStyle.foreColor = "Red";
    defaultStyle.formatter = "0.00";
    defaultStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
    defaultStyle.borderLeft = new GC.Spread.Sheets.LineBorder("Green", GC.Spread.Sheets.LineStyle.medium);
    defaultStyle.borderTop = new GC.Spread.Sheets.LineBorder("Green", GC.Spread.Sheets.LineStyle.medium);
    defaultStyle.borderRight = new GC.Spread.Sheets.LineBorder("Green", GC.Spread.Sheets.LineStyle.medium);
    defaultStyle.borderBottom = new GC.Spread.Sheets.LineBorder("Green", GC.Spread.Sheets.LineStyle.medium);

    activeSheet.setDefaultStyle(defaultStyle, GC.Spread.Sheets.SheetArea.viewport);

    var rowCount = activeSheet.getRowCount();
    var colCount = activeSheet.getColumnCount();

    for (var i = 0; i < rowCount; i++) {
        for (var j = 0; j < colCount; j++) {
            activeSheet.setValue(i, j, i + j, GC.Spread.Sheets.SheetArea.viewport);
        }
    }
};
```

## Set Font Style

You can set the font style for the sheet using the `font` property which includes values for font style, font weight, font size, and font family.
Alternatively, you can use split font properties such as `fontStyle`, `fontWeight`, `fontSize`, and `fontFamily` to set the value for each property separately.
Refer to the following image which depicts the use of font property and split font properties separately.

![SpreadJS worksheet comparing font property output with split font properties for family, size, weight, and style.](https://cdn.mescius.io/document-site-files/images/ef9b66d1-0ae2-4e94-b8cb-f9f893aacc8d/font-style.7e4478.png?width=400)

The following example code implements the font property on the cell (2,0) (4,0) and (6,0) whereas the split font properties are implemented on other cells.

```javascript
// Font
activeSheet.getCell(2, 0).font('italic normal 12px Mangal');
activeSheet.getCell(4, 0).font('normal bold 15px Arial Black');
activeSheet.getCell(6, 0).font('normal normal 18px Georgia');

// FontFamily
activeSheet.getCell(2, 1).fontFamily('Mangal');
activeSheet.getCell(4, 1).fontFamily('Arial Black');
activeSheet.getCell(6, 1).fontFamily('Georgia');

// FontSize
activeSheet.getCell(2, 2).fontSize('12px');
activeSheet.getCell(4, 2).fontSize('20px');
activeSheet.getCell(6, 2).fontSize('28px');

// FontWeight
activeSheet.getCell(2, 3).fontWeight('bold');
activeSheet.getCell(4, 3).fontWeight('normal');

// Italic
activeSheet.getCell(2, 4).fontStyle('italic');
activeSheet.getCell(4, 4).fontStyle('normal');
```

## Change Gridlines Appearance

You can change the color of grid lines or hide them using the gridline worksheet option.
![SpreadJS worksheet showing gridline option output with red vertical gridlines and hidden horizontal gridlines.](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260708.7c77a9.png?width=400)
This code example sets the grid line color and hides the horizontal grid lines.

```javascript
window.onload = function () {
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {
        sheetCount: 3
    });
    var sheet = spread.getActiveSheet();

    // Hide horizontal grid lines.
    sheet.options.gridline = {
        color: "red",
        showVerticalGridline: true,
        showHorizontalGridline: false
    };

    spread.invalidateLayout();
    spread.repaint();
};
```