Class SfSpreadsheet

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

Inheritance
System.Object
SfSpreadsheet
Implements
System.ComponentModel.INotifyPropertyChanged
System.ComponentModel.INotifyPropertyChanging
System.IDisposable
Namespace: Syncfusion.UI.Xaml.Spreadsheet
Assembly: Syncfusion.SfSpreadsheet.WPF.dll
Syntax
public class SfSpreadsheet : Control, INotifyPropertyChanged, INotifyPropertyChanging, IDisposable
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

SfSpreadsheet()

Initializes a new instance of the SfSpreadsheet class.

Declaration
public SfSpreadsheet()

Fields

FormulaBarVisibilityProperty

Identifies the namespace FormulaBarVisibilityProperty dependency property.

Declaration
public static readonly DependencyProperty FormulaBarVisibilityProperty
Field Value
Type
System.Windows.DependencyProperty
Remarks

The identifier for the FormulaBarVisibilityProperty dependency property.

SelectedTabIndexProperty

Identifies the namespace SelectedTabIndexProperty dependency property.

Declaration
public static readonly DependencyProperty SelectedTabIndexProperty
Field Value
Type
System.Windows.DependencyProperty
Remarks

The identifier for the SelectedTabIndexProperty dependency property.

ShowSheetTabsProperty

Using a DependencyProperty as the backing store for setting the state of the sheetTab panel.

Declaration
public static readonly DependencyProperty ShowSheetTabsProperty
Field Value
Type
System.Windows.DependencyProperty

SpreadsheetResourceDictionaryProperty

The Dependency property for Syncfusion.UI.Xaml.Spreadsheet.SfSpreadsheet.SpreadsheetResourceDictionary

Declaration
public static readonly DependencyProperty SpreadsheetResourceDictionaryProperty
Field Value
Type
System.Windows.DependencyProperty

WorkbookProperty

Using a DependencyProperty as the backing store for Workbook. This enables animation, styling, binding, etc...

Declaration
public static readonly DependencyProperty WorkbookProperty
Field Value
Type
System.Windows.DependencyProperty

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 SfSpreadsheet 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 SfSpreadsheet 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 SfSpreadsheet; 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 SfSpreadsheet 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 SfSpreadsheet 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 SfSpreadsheet 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.

Commands

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

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

An instance of Commands.

CopyPaste

Holds the instance of SpreadsheetCopyPaste.

Declaration
public SpreadsheetCopyPaste CopyPaste { get; set; }
Property Value
Type
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 SfSpreadsheet .

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

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

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

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

Name of the file.

FormulaBarVisibility

Gets or sets the value indicating the System.Windows.Visibility state of Syncfusion.UI.Xaml.Spreadsheet.SfSpreadsheet.FormulaBar.

Declaration
public Visibility FormulaBarVisibility { get; set; }
Property Value
Type Description
System.Windows.Visibility

Specifies the display state of the formula bar.

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

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

An instance of HistoryManager.
Remarks

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

IsCustomTabItemContextMenuEnabled

Gets or sets a value indicating whether the Custom context menu of TabItem in SfSpreadsheet 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.

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
SearchManager

SelectedTabIndex

Gets or sets a value indicating the selected tab index of SfSpreadsheet .

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

The index of the tab.

SelectedTabItem

Declaration
public object SelectedTabItem { get; set; }
Property Value
Type
System.Object

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.

ShowSheetTabs

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

TabItemContextMenu

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

Declaration
public ContextMenu TabItemContextMenu { get; set; }
Property Value
Type Description
System.Windows.Controls.ContextMenu

An instance of System.Windows.Controls.ContextMenu

Workbook

Gets or sets the value indicating the instance of IWorkbook which is loaded into the Spreadsheet.

Declaration
public IWorkbook Workbook { get; set; }
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)

Declaration
protected virtual 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.

Group(IWorksheet, GridRangeInfo, ExcelGroupBy)

Used to Group the specified range in the SfSpreadsheet.

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

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.

OnApplyTemplate()

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

Declaration
public override void OnApplyTemplate()
Remarks

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

OnCreateCommands()

Create commands during the initialization of SfSpreadsheet.

Declaration
protected virtual Commands OnCreateCommands()
Returns
Type Description
Commands

An instance of Commands.

OnWorkbookLoaded()

Invoked to load the objects of the workbook in SfSpreadsheet.

Declaration
protected void OnWorkbookLoaded()
Remarks

This method raises the WorkbookLoaded event.

OnWorkbookUnloaded(IWorkbook)

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

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

This method raises the WorkbookUnloaded 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 SfSpreadsheet.

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 WorksheetAdded event.

OnWorksheetAdding(WorksheetAddingEventArgs)

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

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

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

Name of the worksheet.
Remarks

This method raises the WorksheetRemoved event.

OnWorksheetRemoving(String)

Invoked when a worksheet is going to be removed in SfSpreadsheet.

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 WorksheetRemoving event.

Open(IWorkbook)

Opens the specifed workbook in SfSpreadsheet .

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.

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)

Renames the specified sheet name in the workbook by start editing on tab header.

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

name of the sheet

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

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

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

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
System.ComponentModel.PropertyChangedEventHandler

PropertyChanging

Occurs when property value is changing.

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

WorkbookCreating

Occurs when new IWorkbook is going to be created in SfSpreadsheet.

Declaration
public event WorkbookCreatingEventHandler WorkbookCreating
Event Type
Type
WorkbookCreatingEventHandler

WorkbookLoaded

Occurs after the IWorkbook is loaded into SfSpreadsheet.

Declaration
public event WorkbookLoadedEventHandler WorkbookLoaded
Event Type
Type
WorkbookLoadedEventHandler

WorkbookUnloaded

Occurs after the exisiting IWorkbook is unloaded from SfSpreadsheet.

Declaration
public event WorkbookUnloadedEventHandler WorkbookUnloaded
Event Type
Type
WorkbookUnloadedEventHandler

WorksheetAdded

Occurs when new IWorksheet is added in SfSpreadsheet.

Declaration
public event WorksheetAddedEventHandler WorksheetAdded
Event Type
Type
WorksheetAddedEventHandler

WorksheetAdding

Occurs when new IWorksheet is going to be added in SfSpreadsheet.

Declaration
public event WorksheetAddingEventHandler WorksheetAdding
Event Type
Type
WorksheetAddingEventHandler

WorksheetRemoved

Occurs when the IWorksheet is removed in SfSpreadsheet.

Declaration
public event WorksheetRemovedEventHandler WorksheetRemoved
Event Type
Type
WorksheetRemovedEventHandler

WorksheetRemoving

Occurs when the IWorksheet is going to be removed in SfSpreadsheet

Declaration
public event WorksheetRemovingEventHandler WorksheetRemoving
Event Type
Type
WorksheetRemovingEventHandler

ZoomFactorChanged

Occurs when the zoom factor in SfSpreadsheet is changed.

Declaration
public event ZoomFactorChangedEventHandler ZoomFactorChanged
Event Type
Type
ZoomFactorChangedEventHandler

ZoomFactorChanging

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

Declaration
public event ZoomFactorChangingEventHandler ZoomFactorChanging
Event Type
Type
ZoomFactorChangingEventHandler

Implements

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

Extension Methods