SpreadJS 14
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";
    });
    

    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});
    
    }
    

    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();
    });