Class Spreadsheet

SfSpreadsheet is a control for viewing, editing and creating Excel files.

Inheritance
System.Object
Spreadsheet
Implements
System.ComponentModel.INotifyPropertyChanged
System.ComponentModel.INotifyPropertyChanging
System.IDisposable
Namespace: Syncfusion.Windows.Forms.Spreadsheet
Assembly: Syncfusion.Spreadsheet.Windows.dll
Syntax
public class Spreadsheet : Control, INotifyPropertyChanged, INotifyPropertyChanging, IDisposable, IThemeProvider, IVisualStyle
Remarks

SfSpreadsheet is an Excel inspired control which represents the data as cells organized in the form of rows and columns It is built on XlsIO which features a full-fledged object model similar to the Microsoft Office Automation libraries.

Examples


  				


using System;
using System.Windows;
using System.IO;
using System.Windows.Controls;
using System.Windows.Input;
using System.Windows.Media;
using using Syncfusion.UI.Xaml.Spreadsheet;
namespace SpreadsheetDemo
{
/// 
/// Interaction logic for MainWindow.xaml
/// 
public partial class MainWindow : Window
{
public Window1()
{
 InitializeComponent();
 SfSpreadsheet spreadsheet = new SfSpreadsheet();
 grid.Children.Add(spreadsheet);
}
}
}

Constructors

Spreadsheet()

Initializes a new instance of the SfSpreadsheet class.

Declaration
public Spreadsheet()

Properties

ActiveGrid

Gets the value indicating the instance of SpreadsheetGrid for Active sheet .

Declaration
public SpreadsheetGrid ActiveGrid { get; }
Property Value
Type Description
SpreadsheetGrid

An instance of SpreadsheetGrid.

ActiveSheet

Gets or sets a value indicating the instance of active IWorksheet of Workbook.

Declaration
public IWorksheet ActiveSheet { get; set; }
Property Value
Type Description
IWorksheet

An instance of IWorksheet.

AllowCellContextMenu

Gets or sets a value indicating whether the context menu for cells in is to be displayed or not .

Declaration
public bool AllowCellContextMenu { get; set; }
Property Value
Type Description
System.Boolean

true if the user wants to display the cell level context menu; otherwise, false.

AllowExtendRowColumnCount

Gets or sets a value indicating whether the row/column count in can be extended or not .

Declaration
public bool AllowExtendRowColumnCount { get; set; }
Property Value
Type Description
System.Boolean

true if the row and column count can be extended in ; otherwise, false.

AllowFiltering

Gets or sets a value indicating whether the spreadsheet allows to filter the data or not.

Declaration
public bool AllowFiltering { get; set; }
Property Value
Type Description
System.Boolean

true if the user wants to filter the data in the spreadsheet; otherwise, false.

AllowFormulaRangeSelection

Gets or sets a value indicating whether the user is allowed to perform the formula range selection in or not.

Declaration
public bool AllowFormulaRangeSelection { get; set; }
Property Value
Type Description
System.Boolean

true if the user can perform the formula range selection ; otherwise, false.

AllowTabItemContextMenu

Gets or sets a value indicating whether the context menu for the TabItem in is displayed or not.

Declaration
public bool AllowTabItemContextMenu { get; set; }
Property Value
Type Description
System.Boolean

trueif the user wants to display the TabItem context menu ; otherwise, false.

AllowZooming

Gets or sets a value indicating whether to allow the end users to zoom the or not .

Declaration
public bool AllowZooming { get; set; }
Property Value
Type Description
System.Boolean

true if the user can perform the zooming in sfspreadsheet; otherwise, false.

BaseThemeName

Gets or sets the base theme name for the control.

Declaration
public string BaseThemeName { get; set; }
Property Value
Type Description
System.String

BusyIndicator

Holds the instance of BusyIndicator.

Declaration
public BusyIndicator BusyIndicator { get; set; }
Property Value
Type Description
BusyIndicator

CanApplyTheme

Gets or sets a value indicating whether a Spreadsheet theme settings can be applied to the control.

Declaration
public bool CanApplyTheme { get; set; }
Property Value
Type Description
System.Boolean

The default value is true.
Remarks

This property must be initialized before applying the VisualTheme />

CanOverrideStyle

Gets or sets a value indicating whether control elements styles can be overridden by theme style settings.

Declaration
public bool CanOverrideStyle { get; set; }
Property Value
Type Description
System.Boolean

Default value is false.
Remarks

By default, the control's element styles will not be overridden by theme style settings if the style is set in sample level. If this property is enabled, element style will be overridden by theme style settings event if it is set in sample level. This property should be enabled or disabled before calling the ThemeName property of the control.

Commands

Gets or sets the value indicating the instance of /> for performing the common actions in .

Declaration
public Commands Commands { get; set; }
Property Value
Type Description
Commands

An instance of .

CopyPaste

Holds the instance of SpreadsheetCopyPaste.

Declaration
public SpreadsheetCopyPaste CopyPaste { get; set; }
Property Value
Type Description
SpreadsheetCopyPaste

CurrentCellRange

Gets the value indicating the instance of IRange of current cell.

Declaration
public IRange CurrentCellRange { get; }
Property Value
Type Description
IRange

An instance of IRange.

CurrentCellStyle

Gets the value indicating the instance of IStyle of current cell.

Declaration
public IStyle CurrentCellStyle { get; }
Property Value
Type Description
IStyle

An instance of IStyle.

CurrentCellValue

Gets the value of the current cell in .

Declaration
public string CurrentCellValue { get; }
Property Value
Type Description
System.String

Value of the current cell.

DefaultColumnCount

Gets or sets a value indicating the default column count used by the .

Declaration
public int DefaultColumnCount { get; set; }
Property Value
Type Description
System.Int32

Number of columns

DefaultRowCount

Gets or sets a value indicating the default row count used by the .

Declaration
public int DefaultRowCount { get; set; }
Property Value
Type Description
System.Int32

Number of rows.

DisplayAlerts

Gets or sets a value indicating whether warning or alert messages should be shown in Spreadsheet or not.

Declaration
public bool DisplayAlerts { get; set; }
Property Value
Type Description
System.Boolean

True or False.

FileName

Gets or sets the value indicating the name of the file in .

Declaration
public string FileName { get; set; }
Property Value
Type Description
System.String

Name of the file.

FormulaBarVisibility

Gets or sets the visibilty of formulabar.

Declaration
public bool FormulaBarVisibility { get; set; }
Property Value
Type Description
System.Boolean

FormulaRangeSelectionController

Gets or sets the FormulaRangeSelectionController for formula range selection.

Declaration
public FormulaRangeSelectionController FormulaRangeSelectionController { get; set; }
Property Value
Type Description
FormulaRangeSelectionController

An instance of FormulaRangeSelectionController.

GraphicCopyPaste

Holds the instance of GraphicCopyPasteController used to perform cut,copy,paste operations on ShapeImpl (TextBox, Picture, Charts, etc.).

Declaration
public GraphicCopyPasteController GraphicCopyPaste { get; set; }
Property Value
Type Description
GraphicCopyPasteController

GridCollection

Gets the collection of SpreadsheetGrid.

Declaration
public Dictionary<string, SpreadsheetGrid> GridCollection { get; }
Property Value
Type Description
System.Collections.Generic.Dictionary<System.String, SpreadsheetGrid>

A collection that contains the SpreadsheetGrid.

HistoryManager

Gets the instance of HistoryManager which maintains the undo/redo operations in .

Declaration
public HistoryManager HistoryManager { get; }
Property Value
Type Description
HistoryManager

An instance of HistoryManager.
Remarks

By default, it is enabled in and it maintains in the command stack.

IsCustomTabItemContextMenuEnabled

Gets or sets a value indicating whether the Custom context menu of TabItem in can be enabled or not.

Declaration
public bool IsCustomTabItemContextMenuEnabled { get; set; }
Property Value
Type Description
System.Boolean

true if the custom TabItem context menu is enabled ; otherwise, false.

IsVisualStyleEnabled

Gets a value indicating whether the visual style based theme is applied to the control and also indicates whether the theme files are referred from external assemblies or not.

Declaration
public bool IsVisualStyleEnabled { get; }
Property Value
Type Description
System.Boolean

Return true, if the visual style based theme is applied to the control. Otherwise returns false.

SearchManager

Holds the instance of SearchManager class that used to find or replace the text in the document with advanced search options.

Declaration
public SearchManager SearchManager { get; set; }
Property Value
Type Description
SearchManager

SelectedTabIndex

Gets or sets the selected tab index of Spreadsheet

Declaration
public int SelectedTabIndex { get; set; }
Property Value
Type Description
System.Int32

SelectedTabItem

Gets or sets the selected tab item of spreadsheet.

Declaration
public TabBarPage SelectedTabItem { get; set; }
Property Value
Type Description
TabBarPage

ShowBusyIndicator

Gets or sets the value indicating whether BusyIndicator is to be displayed in Spreadsheet or not.

Declaration
public bool ShowBusyIndicator { get; set; }
Property Value
Type Description
System.Boolean

true if the busy indicator is to be displayed ; otherwise, false.

TabItemContextMenu

Gets or sets a value indicating the context menu items in TabItem of .

Declaration
public ContextMenuStrip TabItemContextMenu { get; set; }
Property Value
Type Description
System.Windows.Forms.ContextMenuStrip

An instance of System.Windows.Forms.ContextMenu

ThemeName

Gets or sets the theme name of the control.

Declaration
public string ThemeName { get; set; }
Property Value
Type Description
System.String

The default value is null.
Remarks

This ThemeStyle settings will be applied only when the VisualStyleBased theme has been applied to the control.

ThemeStyle

Gets or sets the SpreadsheetVisualStyle value used to customize the appearance of the Spreadsheet.

Declaration
public SpreadsheetVisualStyle ThemeStyle { get; set; }
Property Value
Type Description
SpreadsheetVisualStyle

Workbook

Gets the value indicating the instance of IWorkbook which represents an Excel Workbook.

Declaration
public IWorkbook Workbook { get; }
Property Value
Type Description
IWorkbook

An instance of IWorkbook.

Methods

AddNamedRange(String, String, String)

Used to add new name for the specified range in the workbook.

Declaration
public void AddNamedRange(string namerange, string refersto, string scope)
Parameters
Type Name Description
System.String namerange

Name of the range.
System.String refersto

Address for the range.
System.String scope

scope of the named range. It can be workbook or any worksheet in a workbook. If any worksheet name is provided, then the name can be used only within that worksheet.
Remarks

The address should be a string such as Sheet1!$D$5.

Examples
spreadsheet.AddNamedRange("sample", "A3:B3", "Sheet1");

AddSheet()

Adds a new worksheet in the workbook.

Declaration
public void AddSheet()
Remarks

By default, this method sets the index for the newly added sheet.

AddSheet(String, Int32)

Add the new worksheet at the given position.

Declaration
public void AddSheet(string name, int insertAt)
Parameters
Type Name Description
System.String name

Name of the sheet.
System.Int32 insertAt

Index position where the sheet is to be inserted.
Remarks

Inserts the newly added sheet at the user defined position.

Create(Int32)

Creates a new workbook with sheets based on the sheet count.

Declaration
public void Create(int sheetCount)
Parameters
Type Name Description
System.Int32 sheetCount

Number of sheets to be present in the workbook.

DeleteNamedRange(IName)

Used to delete the defined name for the ranges in the workbook.

Declaration
public void DeleteNamedRange(IName iname)
Parameters
Type Name Description
IName iname

An instace of IName.
Remarks

Removes the name object from collection and also updates the namebox.

Dispose()

Dispose all the resources used by the Spreadsheet.

Declaration
public void Dispose()

Dispose(Boolean)

Releases all resources used by the Spreadsheet.

Declaration
protected override void Dispose(bool disposing)
Parameters
Type Name Description
System.Boolean disposing

EditNamedRange(String, String, IName)

Used to edit the already defined name in the workbook.

Declaration
public void EditNamedRange(string namerange, string refersto, IName name)
Parameters
Type Name Description
System.String namerange

New name for the range.
System.String refersto

Address for the range.
IName name

An interface of IName.
Remarks

It also updates the named range collection and the NameBox.

GetActiveThemeName()

Gets the active theme of control.

Declaration
public string GetActiveThemeName()
Returns
Type Description
System.String

Group(IWorksheet, GridRangeInfo, ExcelGroupBy)

Used to Group the specified range in the .

Declaration
public void Group(IWorksheet worksheet, GridRangeInfo gridRange, ExcelGroupBy GroupBy)
Parameters
Type Name Description
IWorksheet worksheet

An instance of IWorksheet.
GridRangeInfo gridRange

Range to be grouped.
ExcelGroupBy GroupBy

Grouping Types such as group by rows or group by columns.
Remarks

Based on the group types, the specified range is grouped in the .

HideSheet(String)

Hides the specified sheet in the workbook.

Declaration
public void HideSheet(string name)
Parameters
Type Name Description
System.String name

Name of the sheet to be hided.

OnCreateCommands()

Create commands during the initialization of .

Declaration
protected virtual Commands OnCreateCommands()
Returns
Type Description
Commands

An instance of .

OnCreateControl()

When overridden in a derived class, is invoked whenever application code or internal processes call System.Windows.FrameworkElement.ApplyTemplate.

Declaration
protected override void OnCreateControl()
Remarks

Builds a visual tree for the when a new template is applied.

OnSizeChanged(EventArgs)

Raises the SizeChanged event. When the size is changed this event made to fire.

Declaration
protected override void OnSizeChanged(EventArgs e)
Parameters
Type Name Description
System.EventArgs e

An EventArgs that contains the event data.

OnWorkbookLoaded()

Invoked to load the objects of the workbook in .

Declaration
protected void OnWorkbookLoaded()
Remarks

This method raises the event.

OnWorkbookUnloaded(IWorkbook)

Invoked to unload or clean the objects of the workbook in .

Declaration
protected void OnWorkbookUnloaded(IWorkbook workbook)
Parameters
Type Name Description
IWorkbook workbook
Remarks

This method raises the event and it is the last event to be called while removing the workbook object.

OnWorksheetAdded(String)

Invoked when a new worksheet is added in .

Declaration
protected void OnWorksheetAdded(string name)
Parameters
Type Name Description
System.String name

Name of the worksheet to be added.
Remarks

This method raises the event.

OnWorksheetAdding(WorksheetAddingEventArgs)

Invoked when a new worksheet is going to be added in .

Declaration
protected bool OnWorksheetAdding(WorksheetAddingEventArgs args)
Parameters
Type Name Description
WorksheetAddingEventArgs args

An WorksheetAddingEventArgs that contains the event data.
Returns
Type Description
System.Boolean

True or False.
Remarks

This method raises the WorksheetAdding event.

OnWorksheetRemoved(String)

Invoked when a worksheet is removed in .

Declaration
protected void OnWorksheetRemoved(string name)
Parameters
Type Name Description
System.String name

Name of the worksheet.
Remarks

This method raises the event.

OnWorksheetRemoving(String)

Invoked when a worksheet is going to be removed in .

Declaration
protected bool OnWorksheetRemoving(string name)
Parameters
Type Name Description
System.String name

Name of the worksheet.
Returns
Type Description
System.Boolean

True or False.
Remarks

This method raises the event.

Open(IWorkbook)

Opens the specifed workbook in .

Declaration
public void Open(IWorkbook workbook)
Parameters
Type Name Description
IWorkbook workbook

An instance of IWorkbook.

Open(Stream)

Opens the excel workbook with the given stream.

Declaration
public void Open(Stream file)
Parameters
Type Name Description
System.IO.Stream file

Stream of the file to be imported.

Open(String)

Opens the excel workbook with the given file name.

Declaration
public void Open(string file)
Parameters
Type Name Description
System.String file

Name of the file to be imported.

Protect(Boolean, Boolean, String)

Protect the workbook with the specified password.

Declaration
public void Protect(bool isProtectWindow, bool isProtectContent, string password)
Parameters
Type Name Description
System.Boolean isProtectWindow

Indicates if workbook window is to be protected.
System.Boolean isProtectContent

Indicates if workbook content is to be protected.
System.String password

Password to protect.

ProtectSheet(IWorksheet, String)

Protects the worksheet's content with password.

Declaration
public void ProtectSheet(IWorksheet worksheet, string password)
Parameters
Type Name Description
IWorksheet worksheet

An instance of IWorksheet.
System.String password

Password to lock.

ProtectSheet(IWorksheet, String, ExcelSheetProtection)

Protects the worksheet based on option.

Declaration
public void ProtectSheet(IWorksheet worksheet, string password, ExcelSheetProtection option)
Parameters
Type Name Description
IWorksheet worksheet

An instance of IWorksheet.
System.String password

Password to lock.
ExcelSheetProtection option

One of the enum options ExcelSheetProtection, which user selected.

RaiseThemeChanged(Object, ThemeChangedEventArgs)

Raises the ThemeNameChanged event when theme name changed.

Declaration
protected virtual void RaiseThemeChanged(object sender, ThemeChangedEventArgs args)
Parameters
Type Name Description
System.Object sender

The sender value.
ThemeChangedEventArgs args

A ThemeChangedEventArgs contains the event data.

RefreshOutlines(Boolean, Boolean)

Used to refresh the outline(grouping) elements in the worksheet.

Declaration
public void RefreshOutlines(bool refreshRows, bool refreshColumns)
Parameters
Type Name Description
System.Boolean refreshRows

True if you need to refresh rows;otherwise false.
System.Boolean refreshColumns

True if you need to refresh columns;otherwise false.
Remarks

This function is invoked while Insert, Delete,Hide and Unhide of rows/columns are perfomed to refresh the outline panel elements.

RemoveSheet(String)

Removes the specified sheet from the Workbook.

Declaration
public void RemoveSheet(string name)
Parameters
Type Name Description
System.String name

Name of the sheet to be removed.
Remarks

If the total sheet count is 1 or if the sheet contains some data, then a warning message is shown.

RenameSheet(String, String)

Renames the specified sheet name in Spreadsheet.

Declaration
public void RenameSheet(string oldName, string newName)
Parameters
Type Name Description
System.String oldName

Old name of the sheet.
System.String newName

New name of the sheet.

ResumeFormulaCalculation()

Resumes the automatic formula calculation

Declaration
public void ResumeFormulaCalculation()

Save()

Save the changes that are made in the file else if file is not created yet, then it prompts to enter the filename to save

Declaration
public void Save()

SaveAs()

Save the changes made in the file using SaveFileDialog.

Declaration
public bool SaveAs()
Returns
Type Description
System.Boolean

True if the file saved successfully; otherwise false.

SaveAs(Stream)

Save the workbook as stream.

Declaration
public void SaveAs(Stream stream)
Parameters
Type Name Description
System.IO.Stream stream

Stream that will receive workbook data

SaveAs(String)

Save the changes that are made in the specified file name.

Declaration
public void SaveAs(string filename)
Parameters
Type Name Description
System.String filename

file name to save into

SetActiveSheet(String)

Used to change the active sheet of the Spreadsheet.

Declaration
public bool SetActiveSheet(string sheetName)
Parameters
Type Name Description
System.String sheetName

Name of the sheet to be activated
Returns
Type Description
System.Boolean

If true, mentioned sheet is activated.

SetGridLinesVisibility(Boolean)

Used to set the visibility of the grid lines in .

Declaration
public void SetGridLinesVisibility(bool isVisible)
Parameters
Type Name Description
System.Boolean isVisible

True or False.
Remarks

if true, then the grid lines are displayed; else no grid lines are displayed in the view.

SetRowColumnHeadersVisibility(Boolean)

Used to set the visibility of row/column header in .

Declaration
public void SetRowColumnHeadersVisibility(bool isVisible)
Parameters
Type Name Description
System.Boolean isVisible

True or false.
Remarks

If true, header row/column are visible;otherwise header rows/column are hidden.

SetZoomFactor(String, Int32)

Sets the zoom level of the worksheet. Value must be within 50 to 400.

Declaration
public void SetZoomFactor(string sheetName, int zoomFactor)
Parameters
Type Name Description
System.String sheetName

Name of the .
System.Int32 zoomFactor

Zoom factor.
Remarks

The value with 0 indicates there is no zoom applied to the control.

SuspendFormulaCalculation()

Suspends the automatic formula calculation

Declaration
public void SuspendFormulaCalculation()

UnGroup(IWorksheet, GridRangeInfo, ExcelGroupBy)

Used to UnGroup the specified range in the .

Declaration
public void UnGroup(IWorksheet worksheet, GridRangeInfo gridRange, ExcelGroupBy GroupBy)
Parameters
Type Name Description
IWorksheet worksheet

An instance of IWorksheet.
GridRangeInfo gridRange

Range to be ungrouped.
ExcelGroupBy GroupBy

Grouping Types such as group by rows or group by columns.
Remarks

Based on the group types, the specified range is ungrouped in the spreadsheet.

UnhideSheet(String)

Unhides the specified sheet in the workbook.

Declaration
public void UnhideSheet(string name)
Parameters
Type Name Description
System.String name

Name of the sheet to be unhided.

Unprotect(String)

Unprotect the workbook with the specified password.

Declaration
public void Unprotect(string password)
Parameters
Type Name Description
System.String password

Password to unprotect.

UnProtectSheet(IWorksheet, String)

Unprotect the worksheet with given password.

Declaration
public bool UnProtectSheet(IWorksheet worksheet, string password)
Parameters
Type Name Description
IWorksheet worksheet

An instance of IWorksheet.
System.String password

Password to unlock.
Returns
Type Description
System.Boolean

Returns true if the sheet is unprotected ,else false.

Events

PropertyChanged

Occurs when property value has changed.

Declaration
public event PropertyChangedEventHandler PropertyChanged
Event Type
Type Description
System.ComponentModel.PropertyChangedEventHandler

PropertyChanging

Occurs when property value is changing.

Declaration
public event PropertyChangingEventHandler PropertyChanging
Event Type
Type Description
System.ComponentModel.PropertyChangingEventHandler

ThemeNameChanged

Occurs when theme name of the control has changed.

Declaration
public event ThemeChangedEventHandler ThemeNameChanged
Event Type
Type Description
ThemeChangedEventHandler

WorkbookCreating

Occurs when new IWorkbook is going to be created in .

Declaration
public event WorkbookCreatingEventHandler WorkbookCreating
Event Type
Type Description
WorkbookCreatingEventHandler

WorkbookLoaded

Occurs after the IWorkbook is loaded into .

Declaration
public event WorkbookLoadedEventHandler WorkbookLoaded
Event Type
Type Description
WorkbookLoadedEventHandler

WorkbookUnloaded

Occurs after the exisiting IWorkbook is unloaded from .

Declaration
public event WorkbookUnloadedEventHandler WorkbookUnloaded
Event Type
Type Description
WorkbookUnloadedEventHandler

WorksheetAdded

Occurs when new IWorksheet is added in .

Declaration
public event WorksheetAddedEventHandler WorksheetAdded
Event Type
Type Description
WorksheetAddedEventHandler

WorksheetAdding

Occurs when new IWorksheet is going to be added in .

Declaration
public event WorksheetAddingEventHandler WorksheetAdding
Event Type
Type Description
WorksheetAddingEventHandler

WorksheetRemoved

Occurs when the IWorksheet is removed in SfSpreadsheet.

Declaration
public event WorksheetRemovedEventHandler WorksheetRemoved
Event Type
Type Description
WorksheetRemovedEventHandler

WorksheetRemoving

Occurs when the IWorksheet is going to be removed in

Declaration
public event WorksheetRemovingEventHandler WorksheetRemoving
Event Type
Type Description
WorksheetRemovingEventHandler

ZoomFactorChanged

Occurs when the zoom factor in is changed.

Declaration
public event ZoomFactorChangedEventHandler ZoomFactorChanged
Event Type
Type Description
ZoomFactorChangedEventHandler

ZoomFactorChanging

Occurs when the zoom factor in is going to be changed.

Declaration
public event ZoomFactorChangingEventHandler ZoomFactorChanging
Event Type
Type Description
ZoomFactorChangingEventHandler

Explicit Interface Implementations

IVisualStyle.VisualTheme

Gets or sets the VisualTheme of the control.

Declaration
string IVisualStyle.VisualTheme { get; set; }
Returns
Type Description
System.String

IThemeProvider.ControlName

Gets the name of the control.

Declaration
string IThemeProvider.ControlName { get; }
Returns
Type Description
System.String

Implements

System.ComponentModel.INotifyPropertyChanged
System.ComponentModel.INotifyPropertyChanging
System.IDisposable

Extension Methods