Spread WPF 18
Features / Worksheet
In This Topic
    Worksheet
    In This Topic

    Spread for WPF lets you store, manipulate, and display data in a worksheet.

    A worksheet is comprised of numerous cells where you can enter data, use formulas, perform calculations, analyze data, and review results. Each cell is represented by the intersection of a row and a column. In a worksheet, rows are labeled with numeric characters, such as 1, 2, 3…, and columns are labeled with alphabetical characters, such as A, B, C... For example, cell B4 refers to the cell in column B and row 4 in a worksheet.

    The following sections give you information about various operations that can be performed on worksheets.

    Get a Worksheet

    All worksheets in a workbook are stored in the Worksheets collection. You can access a specific worksheet by its index or its name. Additionally, you can use the ActiveSheet property to get the active sheet in a workbook.

    Refer to the following example code to get a specific worksheet.

    Copy Code 
    // Get the active sheet.
GrapeCity.Spreadsheet.IWorksheet worksheet1 = spreadSheet1.Workbook.ActiveSheet; 
// Get a specific worksheet.
// Use sheet index to access the worksheet.
GrapeCity.Spreadsheet.IWorksheet worksheet2 = spreadSheet1.Workbook.Worksheets[0];
// Use sheet name to access the worksheet.
GrapeCity.Spreadsheet.IWorksheet worksheet3 = spreadSheet1.Workbook.Worksheets["MyWorkSheet3"];
    Copy Code 
    ' Get the active sheet.
Dim worksheet1 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.ActiveSheet 
'Get a specific worksheet.
'Use sheet index to access the worksheet.
Dim worksheet2 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets(0)
'Use sheet name to access the worksheet.
Dim worksheet3 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets("MyWorkSheet3")

    Add New Worksheet

    A workbook can contain multiple worksheets. However, a workbook has only one worksheet by default.

    You can add one or more worksheets to the workbook. Using the Add method of the IWorksheets interface, you can add a new worksheet to a workbook before or after a specific worksheet.

    Refer to the following example code to add new worksheets.    

    Copy Code 
    // Add a single worksheet.
GrapeCity.Spreadsheet.IWorksheet worksheet1 = spreadSheet1.Workbook.Worksheets.Add();
// Add a worksheet at specific position.
GrapeCity.Spreadsheet.IWorksheet worksheet2 = spreadSheet1.Workbook.Worksheets.Add("MyWorksheet", 0);
// Add multiple worksheets.
GrapeCity.Spreadsheet.IWorksheet worksheet3 = spreadSheet1.Workbook.Worksheets.Add();
GrapeCity.Spreadsheet.IWorksheet worksheet4 = spreadSheet1.Workbook.Worksheets.Add("MyWorkSheet4");
    Copy Code 
    ' Add a worksheet.
Dim worksheet1 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add()
Dim worksheet2 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add("MyWorksheet", 0)
' Add multiple worksheets.
Dim worksheet3 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add()
Dim worksheet4 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add("MyWorkSheet4")

    Additionally, Spread for WPF also allows you to add multiple worksheets in the XAML view. To do this, you must define all the worksheets inside the <gss:GcSpreadSheet.Sheets> element.

    Refer to the following example code to create two worksheets in a workbook with the names "Sheet1" and "Sheet2", where Sheet1 has one column and five rows and Sheet2 has two columns and ten rows. 

    XAML
    Copy Code 
    <Grid>
     <gss:GcSpreadSheet x:Name="spreadSheet1">
         <gss:GcSpreadSheet.Sheets>
             <gss:SheetInfo Name="Sheet1" RowCount="5">
                 <gss:SheetInfo.Columns>
                     <gss:ColumnInfo/>
                 </gss:SheetInfo.Columns>
             </gss:SheetInfo>
             <gss:SheetInfo Name="Sheet2" RowCount="10">
                 <gss:SheetInfo.Columns>
                     <gss:ColumnInfo/>
                     <gss:ColumnInfo/>
                 </gss:SheetInfo.Columns>
             </gss:SheetInfo>
         </gss:GcSpreadSheet.Sheets>
     </gss:GcSpreadSheet>
</Grid>

    Set Active Worksheet

    The active sheet is the selected worksheet that currently receives any user interaction in the workbook. You can set a specific worksheet as active using the Activate method of the IWorksheet interface.

    Refer to the following example code to set the active worksheet.

    Copy Code 
    // Set the active worksheet.
spreadSheet1.Workbook.Worksheets["Sheet2"].Activate();
    Copy Code 
    'Set the active worksheet.
spreadSheet1.Workbook.Worksheets("Sheet2").Activate()

    Move a Worksheet

    If you have multiple worksheets, you can move them to a specific location on the tab strip. While moving, a worksheet does not change its name. To move a worksheet within a workbook, use the Move method of the IWorksheet interface.

    Refer to the following example code to move your worksheet to another location.

    Copy Code 
    // Move worksheet.
// Add multiple worksheets.
GrapeCity.Spreadsheet.IWorksheet worksheet2 = spreadSheet1.Workbook.Worksheets.Add();
GrapeCity.Spreadsheet.IWorksheet worksheet3 = spreadSheet1.Workbook.Worksheets.Add(); 
// Move the active sheet at second position.
spreadSheet1.Workbook.ActiveSheet.Move(2);
// Move the second worksheet at first poisition.
GrapeCity.Spreadsheet.IWorksheet move_worksheet = spreadSheet1.Workbook.Worksheets[2];
move_worksheet.Move(1);
    Copy Code 
    ' Move worksheet.
' Add multiple worksheets.
Dim worksheet2 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add()
Dim worksheet3 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add() 
' Move the active sheet at second position.
spreadSheet1.Workbook.ActiveSheet.Move(2)
' Move the second worksheet at first poisition.
Dim move_worksheet As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets(2)
move_worksheet.Move(1)

    Copy a Worksheet

    You can copy a worksheet to a new worksheet using the Copy method of the IWorksheet interface. The copied worksheet looks exactly the same as the original worksheet.

    Refer to the following example code to copy your worksheet.

    Copy Code 
    // Create an instance of GcSpreadSheet workbook. 
GrapeCity.Spreadsheet.IWorkbook workbook = spreadSheet1.Workbook;
 
// Get the active worksheet. 
GrapeCity.Spreadsheet.IWorksheet activeWorksheet = spreadSheet1.Workbook.ActiveSheet;
 
// Set the data.
activeWorksheet.Cells[0, 0].Value = 123;
activeWorksheet.Cells[0, 1].Value = 456;
activeWorksheet.Cells[0, 2].Value = 789;
 
// Copy the active sheet to the end of the current workbook.
activeWorksheet.Copy(1);
 
GrapeCity.Spreadsheet.IWorksheet copy_worksheet = workbook.Worksheets[1];
copy_worksheet.Name = "Copy of " + activeWorksheet.Name;
    Copy Code 
    ' Create an instance of GcSpreadSheet workbook.
Dim workbook As GrapeCity.Spreadsheet.IWorkbook = spreadSheet1.Workbook
' Get the active worksheet. 
Dim activeWorksheet As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.ActiveSheet
' Set the data.
activeWorksheet.Cells(0, 0).Value = 123
activeWorksheet.Cells(0, 1).Value = 456
activeWorksheet.Cells(0, 2).Value = 789
' Copy the active sheet to the end of the current workbook.
activeWorksheet.Copy(1)
Dim copy_worksheet As GrapeCity.Spreadsheet.IWorksheet = workbook.Worksheets(1)
copy_worksheet.Name = "Copy of " & activeWorksheet.Name.ToString()

    Show/Hide Worksheet

    You can hide a specific worksheet from a workbook. When you hide a worksheet, the corresponding sheet tab disappears from the tab strip. You can manage the visibility of a worksheet using the SheetVisibility enumeration values.

    To hide a worksheet, you can set the value of the SheetVisibility enumeration to Hidden. However, the hidden worksheet remains a part of your workbook and can be displayed again by changing the value to Visible.

    Refer to the following example code to hide a specific worksheet.

    Copy Code 
    GrapeCity.Spreadsheet.IWorksheet worksheet1 = spreadSheet1.Workbook.Worksheets.Add();
// Set sheet visibility to hidden for hiding the worksheet.
worksheet1.Visible = GrapeCity.Spreadsheet.SheetVisibility.Hidden;
    Copy Code 
    Dim worksheet1 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.Workbook.Worksheets.Add()
' Set sheet visibility to hidden for hiding the worksheet.
worksheet1.Visible = GrapeCity.Spreadsheet.SheetVisibility.Hidden

    Protect a Worksheet

    You can protect a worksheet with a password so that other users cannot edit the data accidentally or intentionally. To restrict editing of a worksheet, use the Protect method of the IWorksheet interface.

    Refer to the following example code to protect a worksheet from any modifications.

    Copy Code 
    // Protect the worksheet.
spreadSheet1.Workbook.ActiveSheet.Protect(GrapeCity.Spreadsheet.WorksheetLocks.Default, "password");
    Copy Code 
    'Protect the worsheet.
spreadSheet1.Workbook.ActiveSheet.Protect(GrapeCity.Spreadsheet.WorksheetLocks.Default, "password")
    Note: Only the Default member of the WorksheetLocks enumeration protects the cell contents. However, the other members of this enumeration are only supported for Excel import/export and do not support protection in the user interface.

    Delete a Worksheet

    You can delete one or more worksheets from a workbook at once using the Delete method of the IWorksheet interface. The deleted worksheets will be automatically removed from the worksheets collection.

    Refer to the following example code to delete a specific sheet from the workbook.

    Note: A workbook must contain at least one visible worksheet. If the only visible worksheet is deleted, then it will throw an exception.
    Copy Code 
    GrapeCity.Spreadsheet.IWorksheet worksheet1 = spreadSheet1.workbook.Worksheets.Add();
// Delete worksheet.
worksheet1.Delete();
    Copy Code 
    Dim worksheet1 As GrapeCity.Spreadsheet.IWorksheet = spreadSheet1.workbook.Worksheets.Add()
' Delete Worksheet.
worksheet1.Delete()

    Customize Gridlines on Worksheet

    Gridlines are used to distinguish between cells on a worksheet. You can change the default color of the gridlines by using the GridlineColorIndex property.

    Note: To customize the color of the gridlines, ensure that the DefaultGridlineColor property is set to false.

    The DisplayGridlines property of the IWorksheetView interface allows you to show or hide gridlines. By default, the property value is set to true. To hide gridlines, you can change this value to false.

    Refer to the following example code to customize the gridlines on a worksheet.

    Copy Code 
    // Customize the grid lines.
spreadSheet1.Workbook.ActiveSheet.View.DisplayOutline = false;
spreadSheet1.Workbook.ActiveSheet.View.DefaultGridlineColor = false;
spreadSheet1.Workbook.ActiveSheet.View.GridlineColorIndex = 10;
// Hide the grid lines.
spreadSheet1.Workbook.ActiveSheet.View.DisplayGridlines = false;
    Copy Code 
    ' Customize the grid lines.
spreadSheet1.Workbook.ActiveSheet.View.DisplayOutline = False
spreadSheet1.Workbook.ActiveSheet.View.DefaultGridlineColor = False
spreadSheet1.Workbook.ActiveSheet.View.GridlineColorIndex = 10
' Hide the grid lines.
spreadSheet1.Workbook.ActiveSheet.View.DisplayGridlines = False