Click or drag to resize

IWorkbook Interface

Represents an MS Excel Workbook.

Namespace: Spire.Xls.Core
Assembly: Spire.XLS (in Spire.XLS.dll) Version: 15.4.0.0 (15.4.0.5046)
Syntax
Copy 
public interface IWorkbook : IExcelApplication

The IWorkbook type exposes the following members.

Properties
Name Description
Public property ActiveSheet
Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active. Read-only.
Public property ActiveSheetIndex
Gets / sets index of the active sheet.
Public property AddInFunctions
Returns collection of all workbook's add-in functions. Read-only.
Public property Allow3DRangesInDataValidation
Indicates whether to allow usage of 3D ranges in DataValidation list property (MS Excel doesn't allow).
Public property ArgumentsSeparator
Formula arguments separator.
Public property Author
Returns or sets the author of the comment. Read-only String.
Public property Code example BuiltInDocumentProperties
Returns collection that represents all the built-in document properties for the specified workbook. Read-only.
Examples
The following code snippet illustrates how to get the built in document properties:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Get the built in document properties</para><para>IBuiltInDocumentProperties builtInDocumentProperties = workbook.DocumentProperties;</para>
Public property Charts
Collection of the chart objects.
Public property CodeName
Name which is used by macros to access the workbook items.
Public property Code example CustomDocumentProperties
Returns collection that represents all the custom document properties for the specified workbook. Read-only.
Examples
The following code snippet illustrates how to get the custom document properties:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Get the document properties</para><para>ICustomDocumentProperties documentProperties = workbook.CustomDocumentProperties;</para>
Public property Date1904
True if the workbook uses the 1904 date system. Read / write Boolean.
Public property DetectDateTimeInValue
Indicates whether library should try to detect string value passed to Value (and Value2) property as DateTime. Setting this property to false can increase performance greatly for such operations especially on Framework 1.0 and 1.1. Default value is true.
Public property DisableMacrosStart
This Property allows users to disable load of macros from document. Excel on file open will simply skip macros and will work as if document does not contain them. This options works only when file contains macros (HasMacros property is True).
Public property DisplayedTab
Index of tab which will be displayed on document open.
Public property DisplayWorkbookTabs
Indicates whether tabs are visible.
Public property HasMacros
True indicate that opened workbook contains VBA macros.
Public property IsCellProtection
True if cell is protected.
Public property IsDisplayPrecision
True if cell is protected.
Public property Code example IsHScrollBarVisible
Gets or sets a value indicating whether to display horizontal scroll bar.
Examples
This sample shows how to hide horizontal scroll bar:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>//Hide horizontal scroll bar</para><para>workbook.IsHScrollBarVisible = false;</para><para>//Save to file</para><para>workbook.SaveToFile("IsHScrollBarVisible.xlsx");</para>
Public property IsRightToLeft
Indicates whether worksheet is displayed right to left.
Public property Code example IsVScrollBarVisible
Gets or sets a value indicating whether to display vertical scroll bar.
Examples
This sample shows how to hide vertical scroll bar:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>//Hide vertical scroll bar</para><para>workbook.IsVScrollBarVisible = false;</para><para>//Save to file</para><para>workbook.SaveToFile("IsVScrollBarVisible.xlsx");</para>
Public property IsWindowProtection
True if window is protected.
Public property MaxColumnCount
Returns maximum column count for each worksheet in this workbook. Read-only.
Public property MaxRowCount
Returns maximum row count for each worksheet in this workbook. Read-only.
Public property Code example Names
For an ReservedHandle object, returns a Names collection that represents all the names in the active workbook. For a Workbook object, returns a Names collection that represents all the names in the specified workbook (including all worksheet-specific names).
Examples
The following code snippet illustrates how to get names:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Get names</para><para>INameRanges names = workbook.NameRanges;</para>
Public property Code example Palette
Get Palette of colors which an Excel document can have. Here is a table of color indexes to places in the color tool box provided by Excel application: -------------------------------------------- || 1| 2| 3| 4| 5| 6| 7| 8| ---+---------------------------------------- |1 | 00 | 51 | 50 | 49 | 47 | 10 | 53 | 54 | |2 | 08 | 45 | 11 | 09 | 13 | 04 | 46 | 15 | |3 | 02 | 44 | 42 | 48 | 41 | 40 | 12 | 55 | |4 | 06 | 43 | 05 | 03 | 07 | 32 | 52 | 14 | |5 | 37 | 39 | 35 | 34 | 33 | 36 | 38 | 01 | ---+---------------------------------------- |6 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | |7 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | --------------------------------------------
Examples
The following code illustrates how to access the default colors of excel color palette:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get colors</para><para>System.Drawing.Color[] colors = workbook.Colors;</para><para>//Get color</para><para>System.Drawing.Color color = colors[2];</para><para>//Set color</para><para>worksheet["B2"].Style.Color = color;</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public property Parent
Gets the parent object of the current instance.
(Inherited from IExcelApplication .)
Public property PasswordToOpen
Gets / sets password to encrypt document.
Public property Code example PivotCaches
Returns pivot caches collection. Read-only.
Examples
The following code snippet illustrates how to get pivot caches:
Copy 
<para>//Load workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Gets pivot caches collection</para><para>IPivotCaches pivotCaches = workbook.PivotCaches;</para>
Public property ReadOnly
True if the workbook has been opened as Read-only. Read-only Boolean.
Public property ReadOnlyRecommended
True to display a message when the file is opened, recommending that the file be opened as read-only.
Public property RowSeparator
Gets / sets row separator for array parsing.
Public property Saved
True if no changes have been made to the specified workbook since it was last saved. Read/write Boolean.
Public property Code example StandardFont
Returns or sets the name of the standard font. Read/write String.
Examples
The following code illustrates how to set the standard font for the workbook:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["B2"].Text = "Text";</para><para>//Set standard font</para><para>workbook.DefaultFontName = "Arial";</para><para>//Set standard font size</para><para>workbook.DefaultFontSize = 18;</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public property Code example StandardFontSize
Returns or sets the standard font size, in points. Read/write.
Examples
The following code illustrates how to set the standard font size for the workbook:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>worksheet["B2"].Text = "Text";</para><para>//Set standard font</para><para>workbook.DefaultFontName = "Arial";</para><para>//Set standard font size</para><para>workbook.DefaultFontSize = 18;</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public property Code example Styles
Returns a Styles collection that represents all the styles in the specified workbook. Read-only.
Examples
The following code snippet illustrates how to get the Styles:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set styles</para><para>IStyles styles = workbook.Styles;</para>
Public property TabSheets
Returns collection of tab sheets. Read-only.
Public property ThrowOnUnknownNames
Indicates whether exception should be thrown when unknown name was found in a formula.
Public property Version
Gets / sets excel version.
Public property Worksheets
Returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.
Top
Methods
Name Description
Public method Activate
Activates the first window associated with the workbook.
Public method AddFont
Adds font to the inner fonts collection and makes this font read-only.
Public method Clone
Creates copy of the current instance.
Public method Close
Closes the object without saving.
Public method Close(Boolean)
Closes the object.
Public method Close(String)
Closes the object and saves changes into specified file.
Public method Close(Boolean, String)
Closes the object.
Public method CopyToClipboard
Copies workbook to the clipboard.
Public method Code example CreateFont
Method to create a font object and register it in the workbook.
Examples
The following code illustrates how to create IFont object:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set text</para><para>IRichTextString richText = worksheet["B2"].RichText;</para><para>//Create font</para><para>IFont font = workbook.CreateFont();</para><para>//Set color</para><para>font.Color = Color.Red;</para><para>//Set text</para><para>richText.Text = "Sample";</para><para>//Set font</para><para>richText.SetFont(0, 5, font);</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public method CreateFont(Font)
Method creates a font object based on native font and register it in the workbook.
Public method CreateFont(IFont)
Method that creates font object based on another font object and registers it in the workbook.
Public method CreateTemplateMarkersProcessor
Creates object that can be used for template markers processing.
Public method Code example FindAll(Boolean)
This method seraches for the all cells with specified bool value.
Examples
This sample shows how to find all cells with specified bool value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cells with specified bool value</para><para>CellRange[] result = workbook.FindAllBool(true);</para>
Public method Code example FindAll(DateTime)
This method seraches for the all cells with specified DateTime value.
Examples
This sample shows how to find all cells with specified DateTime value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cells with specified DateTime value</para><para>CellRange[] result = workbook.FindAllDateTime(DateTime.Now);</para>
Public method Code example FindAll(TimeSpan)
This method seraches for the all cells with specified TimeSpan value.
Examples
This sample shows how to find all cells with specified TimeSpan value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cells with specified TimeSpan value</para><para>TimeSpan value = new TimeSpan(2, 30, 30);</para><para>CellRange[] result = workbook.FindAllTimeSpan(value);</para>
Public method Code example FindAll(Double, FindType)
This method seraches for the all cells with specified double value.
Examples
This sample shows how to find all cells with specified doulbe value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cells with specified double value</para><para>CellRange[] result = workbook.FindAllNumber(100.32 , false);</para>
Public method Code example FindAll(String, FindType)
This method seraches for the all cells with specified string value.
Examples
This sample shows how to find all cells with specified string value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cells with specified string value</para><para>string value = "value";</para><para>CellRange[] result = workbook.FindAllString(value , false , false);</para>
Public method Code example FindOne(Boolean)
This method seraches for the first cell with specified bool value.
Examples
This sample shows how to find the first cell with specified bool value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cell with specified bool value</para><para>IXLSRange result = workbook.FindBool(true);</para>
Public method Code example FindOne(DateTime)
This method seraches for the first cell with specified DateTime value.
Examples
This sample shows how to find the first cell with specified DateTime value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cell with specified DateTime value</para><para>DateTime dateTime = DateTime.Now;</para><para>IXLSRange result = workbook.FindDateTime(dateTime);</para>
Public method Code example FindOne(TimeSpan)
This method seraches for the first cell with specified TimeSpan value.
Examples
This sample shows how to find the first cell with specified TimeSpan value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cell with specified TimeSpan value</para><para>TimeSpan timeSpan = new TimeSpan(2, 30, 30);</para><para>IXLSRange result = workbook.FindTimeSpan(timeSpan);</para>
Public method Code example FindOne(Double, FindType)
This method seraches for the first cell with specified double value.
Examples
This sample shows how to find the first cell with specified double value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cell with specified double value</para><para>double value = 9.00;</para><para>IXLSRange result = workbook.FindNumber(value, false);</para>
Public method Code example FindOne(String, FindType)
This method seraches for the first cell with specified string value.
Examples
This sample shows how to find the first cell with specified string value:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Find cell with specified string value</para><para>string value = "value";</para><para>IXLSRange result = workbook.FindString(value, false, false);</para>
Public method Code example GetNearestColor(Color)
Gets the nearest color to the specified Color structure from Workbook palette.
Examples
The following code illustrates how to get the indexed color from ExcelColors for the given color from Color structure:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get color</para><para>ExcelColors color = workbook.GetMatchingColor(System.Drawing.Color.Red);</para><para>//Set color</para><para>worksheet["B2"].Style.KnownColor = color;</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public method Code example GetNearestColor(Int32, Int32, Int32)
Gets the nearest color to the specified by red, green, and blue values color from Workbook palette.
Examples
The following code illustrates how to get the indexed color from ExcelColors for the given color from Color structure:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get color</para><para>ExcelColors color = workbook.GetMatchingColor(255, 0, 0);</para><para>//Set color</para><para>worksheet["B2"].Style.KnownColor = color;</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public method Code example GetPaletteColor
Method return Color object from workbook palette by its index.
Examples
The following code illustrates how to get the RGB color value for the specified color from ExcelColors enumeration:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get color</para><para>System.Drawing.Color color = workbook.GetPaletteColor(ExcelColors.Red);</para><para>//Set color</para><para>worksheet["B2"].Style.Color = workbook.Colors[10];</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public method Protect
Sets protection for workbook.
Public method Code example Replace(String, DateTime)
Replaces specified string by specified value.
Examples
The following code illustrates how to replace the string value with datetime:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Replace the oldValue by dateTime</para><para>string oldValue = "Find";</para><para>DateTime dateTime = DateTime.Now;</para><para>workbook.Replace(oldValue, dateTime);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>
Public method Code example Replace(String, Double)
Replaces specified string by specified value.
Examples
The following code snippet illustrates how to replace the string with double:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Replace the oldValue by double</para><para>string oldValue = "Ten";</para><para>workbook.Replace(oldValue, 10.0);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>
Public method Code example Replace(String, String)
Replaces specified string by specified value.
Examples
The following code snippet illustrates how to replace the string with another string:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Replace the oldValue by newValue</para><para>string oldValue = "Find";</para><para>string newValue = "NewValue";</para><para>workbook.Replace(oldValue, newValue);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>
Public method Code example Replace(String, DataColumn, Boolean)
Replaces specified string by data column values.
Examples
The following code snippet illustrates how to replace the string value with data column:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Replace the oldValue by data column</para><para>string oldValue = "Find";</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("Dosage", typeof(int));</para><para>table.Rows.Add(1);</para><para>System.Data.DataColumn dataColumn = table.Columns[0];</para><para>workbook.Replace(oldValue, dataColumn, true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>
Public method Code example Replace(String, DataTable, Boolean)
Replaces specified string by data table values.
Examples
The following code snippet illustrates how to replace the string value with data table:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Replace the oldValue by data table</para><para>string oldValue = "Find";</para><para>System.Data.DataTable table = new System.Data.DataTable();</para><para>table.Columns.Add("Dosage", typeof(int));</para><para>table.Rows.Add(1);</para><para>workbook.Replace(oldValue, table, true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>
Public method Code example Replace(String, Double , Boolean)
Replaces specified string by data from array.
Examples
The following code snippet illustrates how to replace the string with array of double values:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Replace the oldValue by array of double values</para><para>string oldValue = "Find";</para><para>double[] newValues = { 1.0, 2.0 };</para><para>workbook.Replace(oldValue, newValues, true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>
Public method Code example Replace(String, Int32 , Boolean)
Replaces specified string by data from array.
Examples
The following code snippet illustrates how to replace the string with array of int values:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Replace the oldValue by array of int values</para><para>string oldValue = "Find";</para><para>int[] newValues = { 1, 2 };</para><para>workbook.Replace(oldValue, newValues, true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>
Public method Code example Replace(String, String , Boolean)
Replaces specified string by data from array.
Examples
The following code snippet illustrates how to replace the string with array of string values:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Replace the oldValue by array of string values</para><para>string oldValue = "Find";</para><para>string[] newValues = { "X values", "Y values" };</para><para>workbook.Replace(oldValue, newValues , true);</para><para>//Save to file</para><para>workbook.SaveToFile("Replace.xlsx");</para>
Public method Code example ResetPalette
Recover palette to default values.
Examples
The following code snippets illustrates how to reset the palette:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Get colors</para><para>System.Drawing.Color[] colors = workbook.Colors;</para><para>//Check color</para><para>Console.WriteLine(colors[2].Name);</para><para>//Set color</para><para>colors[2] = System.Drawing.Color.Yellow;</para><para>//Reset palette</para><para>workbook.ResetPalette();</para><para>//Check color</para><para>Console.WriteLine(workbook.Colors[2].Name);</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public method Code example Save
Saves changes to the specified workbook.
Examples
This sample shows how to save changes to the specified workbook:
Copy 
<para>//Load workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Save to file</para><para>workbook.Save();</para>
Public method SaveAs(Stream)
Saves changes to the specified stream.
Public method SaveAs(String)
Short variant of SaveAs method.
Public method SaveAs(Stream, ExcelSaveType)
Saves changes to the specified stream.
Public method Code example SaveAs(Stream, String)
Save active WorkSheet using separator.
Examples
The following code illustrates how to saves the active worksheet as stream with separator:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Save to stream</para><para>Stream stream = new MemoryStream();</para><para>workbook.SaveToFile(stream , ",");</para>
Public method SaveAs(String, ExcelSaveType)
Short variant of SaveAs method.
Public method Code example SaveAs(String, String)
Save active WorkSheet using separator.
Examples
The following code illustrates how to save the active worksheet in a different file with separator:
Copy 
<para>//Create workbook</para><para>Workbook workbook = new Workbook();</para><para>workbook.LoadFromFile("Sample.xlsx");</para><para>//Save to file</para><para>workbook.SaveToFile("Result.csv" , ",");</para>
Public method SaveAs(String, HttpResponse)
Saves changes to the specified HttpResponse.
Public method SaveAs(String, ExcelSaveType, HttpResponse)
Saves changes to the specified HttpResponse.
Public method SaveAsHtml
Saves changes to the specified stream.
Public method SetColorOrGetNearest(Color)
If there is at least one free color, define a new color; if not, search for the closest one.
Public method SetColorOrGetNearest(Int32, Int32, Int32)
If there is at least one free color, define a new color; if not, search for the closest one.
Public method Code example SetPaletteColor
Set user color for specified element in Color table.
Examples
The following code snippet illustrates how to set palette color:
Copy 
<para>//Create worksheet</para><para>Workbook workbook = new Workbook();</para><para>Worksheet worksheet = workbook.Worksheets[0];</para><para>//Set palette color</para><para>workbook.ChangePaletteColor(System.Drawing.Color.Red , 10);</para><para>//Set color</para><para>worksheet["B2"].Style.Color = workbook.Colors[10];</para><para>//Save to file</para><para>workbook.SaveToFile("CellFormats.xlsx");</para>
Public method SetSeparators
Sets separators for formula parsing.
Public method SetWriteProtectionPassword
This method sets write protection password.
Public method Unprotect
Unprotects workbook.
Top
See Also