SpreadJS 15
Features / Worksheet / Work with Worksheets
In This Topic
    Work with Worksheets
    In This Topic

    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 method.

    JavaScript
    Copy Code 
    $(document).ready(function ()
{
    // Set the number of sheets to 3.
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById("ss"),
    {sheetCount:3});

   $("#button1").click(function()
   {
     // Create a new sheet and add it at 2nd 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 method.

    JavaScript
    Copy Code 
    $(document).ready(function ()
 {
    // Set the number of sheets to 3.
    var spread =
    new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});

    $("#button1").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 method.

    JavaScript
    Copy Code 
    $(document).ready(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 method.

    Custom sheet names in a workbook

    This code example changes the sheet name and sets the sheet tab color.

    JavaScript
    Copy Code 
    $(document).ready(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";
});

    Note: An error is thrown when using duplicate names or special characters in worksheet name within a workbook.

    Bind the sheet with invalidOperation event and set the InvalidOperationType to changeSheetName 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 method. This method accepts the sheet name and the target index number as parameters.

    Alternatively, the moveSheet command can also be used to move a sheet.

    The following example changes the sheet index of "Sheet2" and adds text in a worksheet cell using the new sheet index.

    JavaScript
    Copy Code 
    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});

};

    Copy Worksheet

    You can copy a sheet to a new worksheet using the 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

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

    Original Sheet

    		Copied Sheet

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

    Original Sheet

    		Copied Sheet

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

    JavaScript
    Copy Code 
    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 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 enumeration options as a parameter in Worksheet.visible method.

    JavaScript
    Copy Code 
    // 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
    Copy Code 
    // Very hidden "Sheet3"
spread.getSheet(2).visible(GC.Spread.Sheets.SheetTabVisible.veryHidden);

    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 below example code:

    Default sheet style

    JavaScript
    Copy Code 
    $(document).ready(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);
  
   // Set the default styles.
   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);
       }
    }
});

    Change Gridlines Appearance

    You can change the color or hide the grid lines.

    Changing the color and hiding the grid lines in a worksheet

    This code example sets the grid line color and hides the horizontal grid lines.

    JavaScript
    Copy Code 
    $(document).ready(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();
});

     

     

    Copyright © 2022 GrapeCity, Inc. All rights reserved.

    Product Support Forum | Print this page