GrapeCity.Documents.Excel

GrapeCity.Documents.Excel.Workbook

Class Workbook

Namespace: GrapeCity.Documents.Excel

Assembly: GcDocs.Excel.dll

Represents the workbook class.

public sealed class Workbook : IWorkbook

Inheritance: object

Workbook

Implements: IWorkbook

Inherited Members: object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

object.GetType()

Constructors

Workbook()

Creates the workbook.

public Workbook()

Workbook(WorkbookOptions)

Creates the workbook.

public Workbook(WorkbookOptions options)

Parameters

options WorkbookOptions: The workbook options.

Workbook(string, WorkbookOptions)

Creates the workbook.

public Workbook(string licenseKey, WorkbookOptions options)

Parameters

licenseKey string: The license key.
options WorkbookOptions: The workbook options.

Workbook(string)

Creates the workbook.

public Workbook(string licenseKey)

Parameters

licenseKey string: The license key.

Properties

ActiveSheet

Gets the active sheet.

public IWorksheet ActiveSheet { get; }

Property Value

IWorksheet

AllowDynamicArray

This api is obsolete, please use Formula2 to get or set dynamic array formula.

[Obsolete]
public bool AllowDynamicArray { get; set; }

Property Value

bool

Author

Gets or sets the author.

public string Author { get; set; }

Property Value

string

AutoParse

Determines whether to parse automatically when setting range value to string value.

public bool AutoParse { get; set; }

Property Value

bool

AutoRoundValue

Determines whether to round the number to 15 significant figures when getting the value.

public bool AutoRoundValue { get; set; }

Property Value

bool

BookView

Returns an object that represents the view settings of this workbook(read-only).

public IWorkbookView BookView { get; }

Property Value

IWorkbookView

BuiltInDocumentProperties

Gets a collection that represents all the built-in document properties of the workbook.

public IBuiltInDocumentPropertyCollection BuiltInDocumentProperties { get; }

Property Value

IBuiltInDocumentPropertyCollection

Culture

[Init-only] Gets or sets culture for the workbook. The culture must contain both country/region and language.

public CultureInfo Culture { get; set; }

Property Value

System.Globalization.CultureInfo

Remarks

This property must be set within the initialization expression of the workbook instance.

CustomDocumentProperties

Gets a collection that represents all the custom document properties of the workbook.

public ICustomDocumentPropertyCollection CustomDocumentProperties { get; }

Property Value

ICustomDocumentPropertyCollection

CustomViews

Gets the custom views of the workbook.

public ICustomViews CustomViews { get; }

Property Value

ICustomViews

DefaultTableStyle

Specifies the table style that is used as the default TableStyle.

public string DefaultTableStyle { get; set; }

Property Value

string

DeferUpdateDirtyState

Gets or sets whether update the dirty state of the formula cells immediately when changing the value of a cell.

public bool DeferUpdateDirtyState { get; set; }

Property Value

bool

EnableCalculation

Specifies whether the calculation engine is working.

public bool EnableCalculation { get; set; }

Property Value

bool

FontsFolderPath

Gets or sets the location of font files that will be used when exporting pdf.

public static string FontsFolderPath { get; set; }

Property Value

string

FullName

Returns the name of the workbook, including its path on disk.

public string FullName { get; }

Property Value

string

GraphicsInfo

Provides graphics information. If this property doesn't have value, the workbook will use built-in graphic information.

public IGraphicsInfo GraphicsInfo { get; set; }

Property Value

IGraphicsInfo

IconSets

Filters data in a workbook based on a cell icon from the IconSets collection. This property is read-only.

public IIconSets IconSets { get; }

Property Value

IIconSets

LoggerFactory

Gets or sets the logger factory shared by all workbooks.

public static ILoggerFactory LoggerFactory { get; set; }

Property Value

Microsoft.Extensions.Logging.ILoggerFactory: If the value is null (Nothing), workbooks will not print logs. Otherwise, workbooks will print logs with the specified Microsoft.Extensions.Logging.ILoggerFactory.

Name

Get or set a string value that represents the name of the workbook.

public string Name { get; set; }

Property Value

string

Names

Returns the INames collection that represents the workbook-specified names. The Names object is read-only.

public INames Names { get; }

Property Value

INames

Options

Returns the IExcelOptions object that represents some settings to control workbook behavior.

public IExcelOptions Options { get; }

Property Value

IExcelOptions

Path

Gets or sets a string that represents the path to the workbook file that this workbook object represents.

public string Path { get; set; }

Property Value

string

PivotCaches

Gets a PivotCaches collection that represents all the PivotTable caches in the specified workbook.

public IPivotCaches PivotCaches { get; }

Property Value

IPivotCaches

ProtectStructure

True if the order of the sheets in the workbook is protected. Read-only Boolean.

public bool ProtectStructure { get; }

Property Value

bool

ProtectWindows

True if the windows of the workbook are protected. Read-only Boolean.

public bool ProtectWindows { get; }

Property Value

bool

ReferenceStyle

Gets or sets the reference style.

public ReferenceStyle ReferenceStyle { get; set; }

Property Value

ReferenceStyle: The ReferenceStyle style.

ResetAdjacentRangeBorder

Determines whether to reset adjacent range's border when setting border for a range. The default value of this property is true.

public bool ResetAdjacentRangeBorder { get; set; }

Property Value

bool

SelectedSheets

Returns a IWorksheets collection that represents all the selected sheets in the specified workbook.

public IWorksheets SelectedSheets { get; }

Property Value

IWorksheets

Signatures

Gets a collection of Signature objects that correspond to the digital signature attached to a document.

public ISignatureSet Signatures { get; }

Property Value

ISignatureSet

SlicerCaches

Gets the ISlicerCaches object associated with the workbook.

public ISlicerCaches SlicerCaches { get; }

Property Value

ISlicerCaches

Styles

Provides access to the collection of cell styles in the current workbook.

public IStyleCollection Styles { get; }

Property Value

IStyleCollection: The styles.

TableStyles

Gets the table styles used in the current workbook.

public ITableStyleCollection TableStyles { get; }

Property Value

ITableStyleCollection

TagJsonSerializer

Gets or sets the json serializer/deserializer for custom type.

public static IJsonSerializer TagJsonSerializer { get; set; }

Property Value

IJsonSerializer

Theme

Returns an instance of ITheme which provides access to the theme associated with a workbook.

public ITheme Theme { get; set; }

Property Value

ITheme: The theme.

ValueJsonSerializer

Gets or sets the json serializer/deserializer for custom cell values

public static IJsonSerializer ValueJsonSerializer { get; set; }

Property Value

IJsonSerializer: The IJsonSerializer which will be used in all workbooks.

Remarks

Be aware of thread safety and JSON element injection problems when you are implementing the IJsonSerializer.

WebRequestHandler

Gets or sets the singleton instance of the IWebRequestHandler implementation that will be used to handle web requests. This property provides a centralized way to access and set the web request handler for the application.

public static IWebRequestHandler WebRequestHandler { get; set; }

Property Value

IWebRequestHandler

Worksheets

Gets a collection of worksheets contained in the workbook.

public IWorksheets Worksheets { get; }

Property Value

IWorksheets: The worksheet.

WriteProtection

Returns a WriteProtection object that provides access to the workbook write protection options.

public WriteProtection WriteProtection { get; }

Property Value

WriteProtection

Methods

AddCustomFunction(CustomFunction, bool)

Add custom function into the function set.

public static void AddCustomFunction(CustomFunction func, bool canOverride = false)

Parameters

func CustomFunction: the custom function instance.
canOverride bool: can override the exist function.

AddDataSource(string, object)

Add data source for template.

public void AddDataSource(string name, object dataSource)

Parameters

name string: the alias name of the data source.
dataSource object: Can be object of DataSet, DataTable, Custom object and variable.

Calculate()

Calculates formulas in the workbook as needed.

public void Calculate()

ConvertBarcodeToPicture(ImageType)

Convert the calculated barcodes to pictures and place them in their respective positions. The original barcode formulas will be cleared.
Not support EMF and WMF image types.

public void ConvertBarcodeToPicture(ImageType imageType = ImageType.SVG)

Parameters

imageType ImageType: Specify the ImageType of converted image.

Exceptions

System.NotSupportedException: If convert to EMF or WMF image type.

Dirty()

Designates all the formulas of the workbook to be recalculated when the next calculation occurs.

public void Dirty()

FromJson(Stream, DeserializationOptions)

Generates a workbook from a json stream.

public IList<JsonError> FromJson(Stream stream, DeserializationOptions deserializationOptions = null)

Parameters

stream System.IO.Stream: the stream
deserializationOptions DeserializationOptions: the DeserializationOptions object

Returns

System.Collections.Generic.IList<T><JsonError>: The errors in the json

FromJson(string, DeserializationOptions)

Generates a workbook from a json string.

public IList<JsonError> FromJson(string json, DeserializationOptions deserializationOptions = null)

Parameters

json string: The json.
deserializationOptions DeserializationOptions: The DeserializationOptions object.

Returns

System.Collections.Generic.IList<T><JsonError>: The errors in the json.

FromSjsJson(Stream, SjsOpenOptions)

Generates a workbook from a JSON stream containing the contents of .sjs file format.

public void FromSjsJson(Stream stream, SjsOpenOptions openOptions)

Parameters

stream System.IO.Stream: The JSON stream.
openOptions SjsOpenOptions: The open options for opening SpreadJS .sjs file.

FromSjsJson(Stream)

Generates a workbook from a JSON stream containing the contents of .sjs file format.

public void FromSjsJson(Stream stream)

Parameters

stream System.IO.Stream: The JSON stream.

FromSjsJson(string, SjsOpenOptions)

Generates a workbook from a JSON string containing the contents of .sjs file format.

public void FromSjsJson(string json, SjsOpenOptions openOptions)

Parameters

json string: The JSON string.
openOptions SjsOpenOptions: The open options for opening SpreadJS .sjs file.

FromSjsJson(string)

Generates a workbook from a JSON string containing the contents of .sjs file format.

public void FromSjsJson(string json)

Parameters

json string: The JSON string.

GenerateReport()

Process the template and return the instance of report workbook.

public IWorkbook GenerateReport()

Returns

IWorkbook: The new IWorkbook.

GenerateReport(params IWorksheet[])

Process the template and return the instance of report workbook.

public IWorkbook GenerateReport(params IWorksheet[] worksheets)

Parameters

worksheets IWorksheet[]: IWorksheet collection that need to be processed.

Returns

IWorkbook: The new IWorkbook.

GetExcelLinkSources()

Returns the names of the linked excel documents.

public IEnumerable<string> GetExcelLinkSources()

Returns

System.Collections.Generic.IEnumerable<T><string>: An enumerable collection of strings representing the Excel link sources.

GetNames(Stream)

Gets all sheet names and tables names that will be used as the parameter source of ImportData.
If the name is a table name, the worksheet name is qualified before the table name, such as "Sheet1!Table1".

public static string[] GetNames(Stream fileStream)

Parameters

fileStream System.IO.Stream: The fileStream of a workbook.

Returns

string[]: An array of possible import names.

GetNames(string)

public static string[] GetNames(string fileName)

Parameters

fileName string: The path and name for the file.

Returns

string[]: An array of possible import names.

GetUsedFonts()

Gets all fonts information that are used on workbook.

public IEnumerable<FontInfo> GetUsedFonts()

Returns

System.Collections.Generic.IEnumerable<T><FontInfo>

ImportData(Stream, string, int, int, int, int)

Import the data of a specified range from a fileStream.

public static object[,] ImportData(Stream fileStream, string worksheetName, int row, int column, int rowCount, int columnCount)

Parameters

fileStream System.IO.Stream: The fileStream of a workbook.
worksheetName string: The name of the worksheet.
row int: The first row of the range.
column int: The first column of the range.
rowCount int: The count of the rows.
columnCount int: The count of the columns.

Returns

object[,]: An array for the data.

ImportData(Stream, string)

Import all the data of the specified source of the file.

public static object[,] ImportData(Stream fileStream, string sourceName)

Parameters

fileStream System.IO.Stream: The fileStream of a workbook.
sourceName string: The name of the data source. The source name could be:
worksheet "Sheet1"
table "Sheet1!Table1"
range "Sheet1!A1:C5"

Returns

object[,]: An array for the data.

ImportData(string, string, int, int, int, int)

Import the data of a specified range from a file.

public static object[,] ImportData(string fileName, string worksheetName, int row, int column, int rowCount, int columnCount)

Parameters

fileName string: The path and name for the file.
worksheetName string: The name of the worksheet.
row int: The first row of the range.
column int: The first column of the range.
rowCount int: The count of the rows.
columnCount int: The count of the columns.

Returns

object[,]: An array for the data.

ImportData(string, string)

Import all the data of the specified source of the file.

public static object[,] ImportData(string fileName, string sourceName)

Parameters

fileName string: The path and name for the file.
sourceName string: The name of the data source. The source name could be:
worksheet "Sheet1"
table "Sheet1!Table1"
range "Sheet1!A1:C5"

Returns

object[,]: An array for the data.

IsEncryptedFile(Stream)

Specifies whether the file is password protected.

public bool IsEncryptedFile(Stream fileStream)

Parameters

fileStream System.IO.Stream: The file stream.

Returns

bool: True if the file is encrypted, false otherwise.

IsEncryptedFile(string)

Specifies whether the file is password protected.

public bool IsEncryptedFile(string fileName)

Parameters

fileName string: The file name.

Returns

bool: True if the file is encrypted, false otherwise.

Open(Stream, OpenFileFormat)

Opens the stream with specified file format.

public void Open(Stream fileStream, OpenFileFormat fileFormat)

Parameters

fileStream System.IO.Stream: The specified file stream.
fileFormat OpenFileFormat: The format of the file stream.

Open(Stream, OpenOptionsBase)

Opens the stream with specified options.

public void Open(Stream fileStream, OpenOptionsBase options)

Parameters

fileStream System.IO.Stream

The file stream.

options OpenOptionsBase

The format of opening the file stream. Possible types:

Open(Stream, string, OpenOptions)

Opens the specified excel file stream.

[Obsolete]
public void Open(Stream fileStream, string password = null, OpenOptions openOptions = null)

Parameters

fileStream System.IO.Stream: The file stream.
password string: The password of the file.
openOptions OpenOptions: Options for opening.

Open(Stream)

Opens the file stream.

public void Open(Stream fileStream)

Parameters

fileStream System.IO.Stream: The specified file stream.

Open(string, DeserializationOptions)

Opens the json file.

public IList<JsonError> Open(string fileName, DeserializationOptions deserializationOptions)

Parameters

fileName string: The specified json file.
deserializationOptions DeserializationOptions: The json deserialization options.

Returns

System.Collections.Generic.IList<T><JsonError>: The error list of the JSON

Open(string, OpenFileFormat)

Opens the file with specified file format.

public void Open(string fileName, OpenFileFormat fileFormat)

Parameters

fileName string: The specified file.
fileFormat OpenFileFormat: The format of the file.

Open(string, OpenOptionsBase)

Opens the file with specified options.

public void Open(string fileName, OpenOptionsBase options)

Parameters

fileName string

The excel file.

options OpenOptionsBase

The options of opening the file. Possible types:

Open(string, string, OpenOptions)

Opens the specified excel file

[Obsolete]
public void Open(string fileName, string password = null, OpenOptions openOptions = null)

Parameters

fileName string: The excel file.
password string: The password of the file.
openOptions OpenOptions: Options for opening.

Open(string)

Opens the file with default options. The file type is inferred from file extension.

public void Open(string fileName)

Parameters

fileName string: The specified file.

PrintOut(PrintOutOptions)

Prints the workbook.

public void PrintOut(PrintOutOptions options = null)

Parameters

options PrintOutOptions: Options for printing the workbook.

ProcessTemplate()

Start to process the template with global options.

public void ProcessTemplate()

ProcessTemplate(CancellationToken)

Start to process the template with global options and cancellation support.

public void ProcessTemplate(CancellationToken cancellationToken)

Parameters

cancellationToken System.Threading.CancellationToken: The token to monitor for cancellation requests.

Remarks

The caller must decide whether to accept the partially expanded template or revert to the previous state. If the caller needs to revert to the previous state, it must serialize the workbook before calling this method, then deserialize the workbook after canceling the operation.

Exceptions

System.OperationCanceledException: Throws when the System.Threading.CancellationToken was canceled.

Protect(bool, bool)

Protects a workbook so that it cannot be modified.

public void Protect(bool structure = true, bool windows = false)

Parameters

structure bool: True to protect the structure of the workbook (To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets).
windows bool: True to prevent users from moving, resizing, or closing the workbook window, or hide/unhide windows. This option is available only in Excel 2007, Excel 2010, Excel for Mac 2011, and Excel 2016 for Mac.

Protect(string, bool, bool)

Protects a workbook so that it cannot be modified.

public void Protect(string password, bool structure = true, bool windows = false)

Parameters

password string: Password to protect the workbook.
structure bool: True to protect the structure of the workbook (To prevent other users from viewing hidden worksheets, adding, moving, deleting, or hiding worksheets, and renaming worksheets).
windows bool: True to prevent users from moving, resizing, or closing the workbook window, or hide/unhide windows. This option is available only in Excel 2007, Excel 2010, Excel for Mac 2011, and Excel 2016 for Mac.

Save(Stream, SaveFileFormat)

Saves workbook to stream with specified file format.

public void Save(Stream fileStream, SaveFileFormat fileFormat)

Parameters

fileStream System.IO.Stream: The specified file stream.
fileFormat SaveFileFormat: The format of the file stream.

Save(Stream, SaveOptionsBase)

Saves workbook to stream with specified options.

public void Save(Stream fileStream, SaveOptionsBase options)

Parameters

fileStream System.IO.Stream

The specified file stream.

options SaveOptionsBase

The options of saving the file stream. Possible types:

Save(Stream, string, SaveOptions)

Saves workbook to the specified excel file stream.

[Obsolete]
public void Save(Stream fileStream, string password = null, SaveOptions saveOptions = null)

Parameters

fileStream System.IO.Stream: The file stream.
password string: The password of the file.
saveOptions SaveOptions: Options for saving

Save(Stream)

Saves the workbook to the stream.

public void Save(Stream fileStream)

Parameters

fileStream System.IO.Stream: The file stream.

Save(string, SaveFileFormat)

Saves workbook to file with specified file format.

public void Save(string fileName, SaveFileFormat fileFormat)

Parameters

fileName string: The specified file.
fileFormat SaveFileFormat: The format of the file.

Save(string, SaveOptionsBase)

Saves workbook to file with specified options.

public void Save(string fileName, SaveOptionsBase options)

Parameters

fileName string

The specified file.

options SaveOptionsBase

The options of saving the file. Possible types:

Save(string, string, SaveOptions)

Saves data to the specified excel file.

[Obsolete]
public void Save(string fileName, string password = null, SaveOptions saveOptions = null)

Parameters

fileName string: The excel file.
password string: The password of the file.
saveOptions SaveOptions: Options for saving

Save(string)

Saves the workbook to the disk.

public void Save(string fileName)

Parameters

fileName string: The file name.

SetLicenseKey(string)

Set the license key of Documents for Excel.

public static void SetLicenseKey(string key)

Parameters

key string: The license key.

ToJson(SerializationOptions)

Generates a json string from a workbook.

public string ToJson(SerializationOptions serializationOptions = null)

Parameters

serializationOptions SerializationOptions: The SerializationOptions object.

Returns

string: The json string.

ToJson(Stream, SerializationOptions)

Generates a json stream from a workbook.

public void ToJson(Stream stream, SerializationOptions serializationOptions = null)

Parameters

stream System.IO.Stream: The json stream.
serializationOptions SerializationOptions: The SerializationOptions object.

ToSjsJson()

Generates a JSON string from a workbook. It integrates all JSON files from the SpreadJS .sjs file into a single string.

public string ToSjsJson()

Returns

string: Json data.

ToSjsJson(SjsSaveOptions)

Generates a JSON string from a workbook. It integrates all JSON files from the SpreadJS .sjs file into a single string.

public string ToSjsJson(SjsSaveOptions options)

Parameters

options SjsSaveOptions: Option for saving SpreadJS .sjs file.

Returns

string: Json data.

ToSjsJson(Stream, SjsSaveOptions)

Integrates all JSON files from the SpreadJS .sjs file into a single string, then put the string into the stream.

public void ToSjsJson(Stream stream, SjsSaveOptions options)

Parameters

stream System.IO.Stream: The specified file stream.
options SjsSaveOptions: Option for opening SpreadJS .sjs file.

ToSjsJson(Stream)

Integrates all JSON files from the SpreadJS .sjs file into a single string, then put the string into the stream.

public void ToSjsJson(Stream stream)

Parameters

stream System.IO.Stream: The specified file stream.

Unprotect(string)

Removes protection from the workbook.

public void Unprotect(string password = null)

Parameters

password string: Password to protect the workbook.

UpdateExcelLink(string, IWorkbook)

Updates a excel link.

public void UpdateExcelLink(string name, IWorkbook sourceWorkbook)

Parameters

name string: the link name
sourceWorkbook IWorkbook: the workbook instance for the link

UpdateExcelLink(string)

Updates a excel link.

public void UpdateExcelLink(string name)

Parameters

name string

UpdateExcelLinks()

Updates all the excel links.

public void UpdateExcelLinks()

WaitForCalculationToFinish()

Waits for all calculation to complete, includes asynchronous calculations. This method blocks the current thread until all calculations have finished. Users can call this method to ensure that all necessary computations have been performed before proceeding with any other operations that depend on the calculation results.

public void WaitForCalculationToFinish()

Events

AfterSave

Occurs after the workbook is saved.

public event EventHandler AfterSave

Event Type

System.EventHandler

BeforeSave

Occurs before the workbook is saved.

public event EventHandler BeforeSave

Event Type

System.EventHandler

NewSheet

Occurs when a new sheet is created in the workbook.

public event EventHandler<SheetEventArgs> NewSheet

Event Type

System.EventHandler<TEventArgs><SheetEventArgs>

Opened

Occurs when the workbook is opened.

public event EventHandler Opened

Event Type

System.EventHandler

SheetActivate

Occurs when a sheet is active.

public event EventHandler<SheetEventArgs> SheetActivate

Event Type

System.EventHandler<TEventArgs><SheetEventArgs>

SheetBeforeDelete

Occurs before a sheet is deleted.

public event EventHandler<SheetEventArgs> SheetBeforeDelete

Event Type

System.EventHandler<TEventArgs><SheetEventArgs>

SheetChange

Occurs when something changes in the cells of a sheet.

public event EventHandler<RangeEventArgs> SheetChange

Event Type

System.EventHandler<TEventArgs><RangeEventArgs>

SheetDeactivate

Occurs when a sheet is deactivated.

public event EventHandler<SheetEventArgs> SheetDeactivate

Event Type

System.EventHandler<TEventArgs><SheetEventArgs>

SheetSelectionChange

Occurs when the selection changes on a sheet.

public event EventHandler<RangeEventArgs> SheetSelectionChange

Event Type

System.EventHandler<TEventArgs><RangeEventArgs>