Workbook module

class Workbook.Workbook

Bases: SpireObject

AcceptAllTrackedChanges()
<summary>

Accept all tracked changes in the workbook.

</summary>

property ActiveSheet: Worksheet
<summary>
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.

</summary>

property ActiveSheetIndex: int
<summary>

Gets or sets index of the active worksheet.

</summary>

property AddInFunctions: AddInFunctionsCollection
<summary>

Returns collection of add-in functions.

</summary>

AddPivotTableStyle(pts: PivotTableStyle)
property Allow3DRangesInDataValidation: bool
CaculateFormulaValue(text: str) → str
<summary>

Computes the string formula

</summary> <param name=”text”></param> <returns></returns>

CalculateAllValue()
<summary>

Caculate all formula for the workbook

</summary>

property CalculationMode: ExcelCalculationMode
<summary>

Get or set calculation mode.

</summary>

ChangePaletteColor(color: Color, index: int)
<summary>

Changes the palette for the spreadsheet in the specified index.

<example>The following code snippet illustrates how to set palette color: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Set palette color workbook.ChangePaletteColor(System.Drawing.Color.Red , 10); //Set color worksheet[“B2”].Style.Color = workbook.Colors[10]; //Save to file workbook.SaveToFile(“CellFormats.xlsx”); </code> </example>

</summary> <param name=”color”>Color structure</param> <param name=”index”>Palette index,Value should be from 0 to 55</param>

property Charts: ChartsCollection
<summary>

Returns a Sheets collection that represents all the chart sheets in the specified workbook. Read-only.

</summary>

property CheckComptiliblity: bool
property CodeName: str
<summary>

Name which used by macros to access to workbook items.

</summary>

property Colors: List[Color]
<summary>

Returns colors in the palette for the workbook. The palette has 56 entries, each represented by an RGB value. Read/write Object.

<example>The following code illustrates how to access the default colors of excel color palette: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Get colors System.Drawing.Color[] colors = workbook.Colors; //Get color System.Drawing.Color color = colors[2]; //Set color worksheet[“B2”].Style.Color = color; //Save to file workbook.SaveToFile(“CellFormats.xlsx”); </code> </example>

</summary>

ColumnWidthToPixels(columnWidth: float) → float
<summary>

onverts column width in characters into column width in pixels.

</summary> <param name=”columnWidth”>Column width in characters.</param> <returns>Column width in pixels.</returns>

ContainsFont(font: ExcelFont) → bool
<summary>

Indicates whether the workbook contains specified font.

</summary> <param name=”font”></param> <returns></returns>

property ConverterSetting: ConverterSetting
CopyTheme(srcWorkbook: Workbook)
<summary>

Copy the theme from source workbook

</summary> <param name=”srcWorkbook”>source workbook.</param>

CopyThemeColor(srcWorkbook: Workbook)
<summary>

Copy the theme color from source workbook

</summary> <param name=”srcWorkbook”>source workbook.</param>

CopyToClipboard
<summary>

Copies whole workbook to the clipboard.

</summary>

CreateEmptySheet
<summary>

Create a new worksheet.

</summary> <returns></returns>

CreateEmptySheets
<summary>

Create workbook with specified number of worksheets.

</summary> <param name=”sheetCount”></param>

CreateFont
<summary>

Creates a font object and add it to the workbook.

<example>The following code illustrates how to create IFont object: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Set text IRichTextString richText = worksheet[“B2”].RichText; //Create font IFont font = workbook.CreateFont(); //Set color font.Color = Color.Red; //Set text richText.Text = “Sample”; //Set font richText.SetFont(0, 5, font); //Save to file workbook.SaveToFile(“CellFormats.xlsx”); </code> </example>

</summary> <returns></returns>

CreatePivotStyle() → PivotStyle
property CultureInfo: CultureInfo
property CustomDocumentProperties: ICustomDocumentProperties
<summary>
Returns collection that represents all the custom document properties

for the specified workbook. Read-only.

<example>The following code snippet illustrates how to get the custom document properties: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Get the document properties ICustomDocumentProperties documentProperties = workbook.CustomDocumentProperties; </code> </example>

</summary>

property CustomFontFileDirectory: List[str]
<summary>

Sets the custom directory of font files.

</summary>

property CustomFontFilePaths: List[str]
<summary>

Returns or sets the custom path of font files.

</summary>

property DataConns: DataConnections
property DataSorter: DataSorter
<summary>

Gets Data sorter to sort the data..

</summary>

property Date1904: bool
<summary>

True if the workbook uses the 1904 date system. Read/write Boolean.

</summary>

property DefaultFontName: str
<summary>

Returns or sets the name of the default font.

<example>The following code illustrates how to set the standard font for the workbook: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Set text worksheet[“B2”].Text = “Text”; //Set standard font workbook.DefaultFontName = “Arial”; //Set standard font size workbook.DefaultFontSize = 18; //Save to file workbook.SaveToFile(“CellFormats.xlsx”); </code> </example>

</summary>

property DefaultFontSize: float
<summary>

Returns or sets the default font size.

<example>The following code illustrates how to set the standard font size for the workbook: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Set text worksheet[“B2”].Text = “Text”; //Set standard font workbook.DefaultFontName = “Arial”; //Set standard font size workbook.DefaultFontSize = 18; //Save to file workbook.SaveToFile(“CellFormats.xlsx”); </code> </example>

</summary>

property DisableMacrosStart: bool
<summary>

Allows users to disable load of macros from document.

</summary>

Dispose()
property DocumentProperties: BuiltInDocumentProperties
<summary>

Returns a BuiltInDocumentProperties collection that represents all the built-in document properties for the specified workbook. Read-only.

<example>The following code snippet illustrates how to get the built in document properties: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Get the built in document properties IBuiltInDocumentProperties builtInDocumentProperties = workbook.DocumentProperties; </code> </example>

</summary>

property FileName: str
<summary>

Gets file name.

</summary>

FindBool(boolValue: bool) → CellRange
<summary>

Finds the cell with the input bool.

<example>This sample shows how to find the first cell with specified bool value: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Find cell with specified bool value IXLSRange result = workbook.FindBool(true); </code> </example>

</summary> <param name=”boolValue”>Bool value to search for.</param> <returns>Found range.</returns>

FindDateTime(dateTimeValue: DateTime) → CellRange
<summary>

Finds the cell with the input datetime.

<example>This sample shows how to find the first cell with specified DataTime value: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Find cell with specified DataTime value DateTime dateTime = DateTime.Now; IXLSRange result = workbook.FindDateTime(dateTime); </code> </example>

</summary> <param name=”dateTimeValue”>Datetime value to search for.</param> <returns>Found range.</returns>

FindNumber(doubleValue: float, formulaValue: bool) → CellRange
<summary>

Finds the cell with the input number.

<example>This sample shows how to find the first cell with specified double value: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Find cell with specified double value double value = 9.00; IXLSRange result = workbook.FindNumber(value, false); </code> </example>

</summary> <param name=”doubleValue”>Double value to search for.</param> <param name=”formulaValue”>Indicates whether includes formula value to search for.</param> <returns>Found range.</returns>

FindString(stringValue: str, formula: bool, formulaValue: bool) → CellRange
<summary>

Finds the cell with the input string.

<example>This sample shows how to find the first cell with specified string value: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Find cell with specified string value string value = “value”; IXLSRange result = workbook.FindString(value, false, false); </code> </example>

</summary> <param name=”stringValue”>String value to search for.</param> <param name=”formula”>Indicates whether includes formula to search for.</param> <param name=”formulaValue”>Indicates whether includes formula value to search for.</param> <returns>Found range.</returns>

FindTimeSpan(timeSpanValue: TimeSpan) → CellRange
<summary>

Finds the cell with the input time span.

<example>This sample shows how to find the first cell with specified TimeSpan value: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Find cell with specified TimeSpan value TimeSpan timeSpan = new TimeSpan(2, 30, 30); IXLSRange result = workbook.FindTimeSpan(timeSpan); </code> </example>

</summary> <param name=”timeSpanValue”>Time span value to search for.</param> <returns>Found range.</returns>

static FixDateTimeNow() → DateTime
static FixDateTimeToday() → DateTime
GetChartSheetByName(name: str) → ChartSheet
GetDigitalSignatures() → IDigitalSignatures
<summary>

Get collection of DigitalSignature in this file.

</summary> <returns>Collection of DigitalSignature</returns>

GetMatchingColor
<summary>

Find best matching Color in workbook palette.

<example>The following code illustrates how to get the indexed color from ExcelColors for the given color from Color structure: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Get color ExcelColors color = workbook.GetMatchingColor(System.Drawing.Color.Red); //Set color worksheet[“B2”].Style.KnownColor = color; //Save to file workbook.SaveToFile(“CellFormats.xlsx”); </code> </example>

</summary> <param name=”color”>Color to search for.</param> <returns>Workbook palette color.</returns>

GetOleSize() → IXLSRange
<summary>

get the size of the visible range of cells when this workbook is displyed as an embedded obect in another document;

</summary> <returns>if there does not set the size ,return null</returns>

GetPaletteColor(color: ExcelColors) → Color
<summary>

Gets excel color from workbook palette.

<example>The following code illustrates how to get the RGB color value for the specified color from ExcelColors enumeration: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Get color System.Drawing.Color color = workbook.GetPaletteColor(ExcelColors.Red); //Set color worksheet[“B2”].Style.Color = workbook.Colors[10]; //Save to file workbook.SaveToFile(“CellFormats.xlsx”); </code> </example>

</summary> <param name=”color”>Index from palette array.</param> <returns>RGB Color.</returns>

GetSafeSheetName
GetThemeColor(type: ThemeColorType) → Color
<summary>

Gets theme color.

</summary> <param name=”type”>The theme color type.</param> <returns>The theme color.</returns>

property HasMacros: bool
<summary>

Indicates whether contains VBA macros.

</summary>

property HasTrackedChanges: bool
<summary>

Indicates whether the workbook has any tracked changes

</summary>

InitCalcEngine()
<summary>

Initilize Calc engine

</summary>

property IsCellProtection: bool
<summary>

Indicates whether cells are protected.

</summary>

property IsDigitallySigned: bool
<summary>

Indicates whether this spreadsheet is digitally signed.

</summary>

property IsHScrollBarVisible: bool
<summary>

Returns or sets whether show horizontal scroll bar.

<example>This sample shows how to hide horizontal scroll bar: <code> //Create workbook Workbook workbook = new Workbook(); //Hide horizontal scroll bar workbook.IsHScrollBarVisible = false; //Save to file workbook.SaveToFile(“IsHScrollBarVisible.xlsx”); </code> </example>

</summary>

property IsHideWindow: bool
<summary>

Hide window

</summary>

IsPasswordProtected

A function that is part of a class.

This wrapper provides a mechanism that allows the function to be constructed with a reference to the class that it owns. The construction is deferred until __set_name__ is called.

Parameters:

  • class_name (str) – Name of the class that owns the function.

  • construct_function (function) – Constructor for the function: takes in a reference to the class that owns the function and gives back the function.

property IsRightToLeft: bool
<summary>

Indicates whether worksheet is displayed right to left.

</summary>

property IsSaved: bool
<summary>

Indicates whether workbook changes have been saved.

</summary>

property IsVScrollBarVisible: bool
<summary>

Returns or sets whether show vertical scroll bar.

<example>This sample shows how to hide vertical scroll bar: <code> //Create workbook Workbook workbook = new Workbook(); //Hide vertical scroll bar workbook.IsVScrollBarVisible = false; //Save to file workbook.SaveToFile(“IsVScrollBarVisible.xlsx”); </code> </example>

</summary>

property IsWindowProtection: bool
<summary>

Indicates window is protected.

</summary>

LoadFromFile
<summary>

Loads a file and imports its data.

</summary> <param name=”FileName”>File name.</param>

LoadFromHtml
<summary>

Loads a html file and imports its data.

</summary> <param name=”fileName”>File name.</param>

LoadFromMHtml
<summary>

Loads a Mhtml file and imports its data.

</summary> <param name=”fileName”>File name.</param>

LoadFromStream
<summary>

Load workbook from the stream.

</summary> <param name=”stream”>Stream contains data.</param>

LoadFromXml
<summary>

Loads a xml file and imports its data.

</summary> <param name=”fileName”>File name.</param>

LoadTemplateFromFile
<summary>

Load workbook from the excel template.

</summary> <param name=”fileName”>File name</param>

property MarkerDesigner: MarkerDesigner
property MaxColumnCount: int
property MaxDigitWidth: int
property MaxRowCount: int
property MaxRowsOfSharedFormula: int
property NameRanges: INameRanges
<summary>
Gets Names collection that represents all the names in the specified

workbook.

<example>The following code snippet illustrates how to get names: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Get names INameRanges names = workbook.NameRanges; </code> </example>

</summary>

property OpenPassword: str
<summary>

Returns or sets password to encrypt document.

</summary>

property OptimizeImport: bool
<summary>
Indicates whether to optimize Import data. This option will

take effect only on Import methods that are available with the worksheet WARNING: Setting this property to True can decrease memory significantly, but will increase the performance of data import .

</summary>

PasteFromClipboard()
<summary>

Copies workbook and all its worksheets from the clipboard.

</summary>

property PivotCaches: PivotCachesCollection
<summary>
Returns pivot caches collection. Read-only.

<example>The following code snippet illustrates how to get pivot caches: <code>

//Create worksheet Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); Worksheet worksheet = workbook.Worksheets[0]; //Gets pivot caches collection IPivotCaches pivotCaches = workbook.PivotCaches; </code> </example>

</summary>

PixelsToColumnWidth(pixels: float) → float
<summary>

Converts column width in pixels into column width in characters.

</summary> <param name=”pixels”>Column width in pixels.</param> <returns>Column width in characters.</returns>

Protect
<summary>

protect file also protect workbook window and structure.

</summary> <param name=”passwordToOpen”>password to open file.</param>

ProtectWorkbook(bIsProtectWindow: bool, bIsProtectContent: bool, password: str)
<summary>

Sets protection for workbook.

</summary> <param name=”bIsProtectWindow”>Indicates if protect workbook window.</param> <param name=”bIsProtectContent”>Indicates if protect workbook content.</param> <param name=”password”>password</param>

property ReadOnly: bool
<summary>

Indicates whether the workbook has been opened as Read-only.

</summary>

RejectAllTrackedChanges()
<summary>

Reject all tracked changes in the workbook.

</summary>

RemoveAllDigitalSignatures()
<summary>

Remove all DigitalSignature in this file.

</summary>

Replace
<summary>

Replaces cell’s value by specified value.

<example>The following code illustrates how to replace the string value with datetime: <code> //Create workbook Workbook workbook = new Workbook(); workbook.LoadFromFile(“Sample.xlsx”); //Replace the oldValue by dateTime string oldValue = “Find”; DateTime dateTime = DateTime.Now; workbook.Replace(oldValue, dateTime); //Save to file workbook.SaveToFile(“Replace.xlsx”); </code> </example>

</summary> <param name=”oldValue”>Value to replace.</param> <param name=”newValue”>New value</param>

ResetPalette()
<summary>

Resets the color palette to the default colors.

<example>The following code snippets illustrates how to reset the palette: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Get colors System.Drawing.Color[] colors = workbook.Colors; //Check color Console.WriteLine(colors[2].Name); //Set color colors[2] = System.Drawing.Color.Yellow; //Reset palette workbook.ResetPalette(); //Check color Console.WriteLine(workbook.Colors[2].Name); //Save to file workbook.SaveToFile(“CellFormats.xlsx”); </code> </example>

</summary>

Save()
<summary>

Saves changes to the specified workbook

</summary>

SaveAsImage
<summary>

Save workbook to image.

</summary> <param name=”sheetIndex”>sheet Index</param> <param name=”dpiX”>Dpi X</param> <param name=”dpiY”>Dpi Y</param> <returns></returns>

SaveAsTemplate
<summary>

Save workbook as template to file.

</summary> <param name=”fileName”>File name.</param>

SaveAsXml
<summary>

Save workbook to an XML data file.

</summary> <param name=”fileName”>XML file name.</param>

SaveChartAsEmfImage
<summary>

Save chart to vector image.

</summary> <param name=”worksheet”>work sheet</param> <param name=”chartIndex”>chart index</param>

SaveChartAsImage
<summary>

Save chart to image.

</summary> <param name=”worksheet”>chart sheet</param>

SaveToFile
<summary>

Saves changes to the workbook in a different file

</summary> <param name=”fileName”>File name.</param>

SaveToHtml
<summary>

Saves the workbook to html

</summary> <param name=”fileName”>File name.</param>

SaveToStream
<summary>

Save workbook the stream

</summary> <param name=”Stream”>Stream object</param>

property SelectedTab: int
<summary>

Gets or sets tab index of selected.

</summary>

SetMaxDigitWidth(w: int)
SetOleSize(firstRow: int, firstColumn: int, lastRow: int, lastColumn: int)
<summary>

set the size of the visible range of cells when this workbook is displyed as an embedded obect in another document;

</summary> <param name=”firstRow”>the visible range’s first row index</param> <param name=”firstColumn”>the visible range’s first column index</param> <param name=”lastRow”>the visible range’s last row index</param> <param name=”lastColumn”>the visible range’s last column index</param>

SetThemeColor(type: ThemeColorType, color: Color)
<summary>

Sets the theme color

</summary> <param name=”type”>The theme color type.</param> <param name=”color”>the theme color</param>

SetWriteProtectionPassword(password: str)
<summary>

Sets write protection password.

</summary> <param name=”password”>Password to set</param>

property SheetTabBarWidth: int
<summary>

Returns or sets the width of worksheet tab bar. 1/1000 of window width.

</summary>

property ShowTabs: bool
<summary>

Get or sets a value whether the Workbook tabs are displayed.

</summary>

property Styles: StylesCollection
<summary>

Returns a Styles collection that represents all the styles in the specified workbook. Read-only.

<example>The following code snippet illustrates how to get the Styles: <code> //Create worksheet Workbook workbook = new Workbook(); Worksheet worksheet = workbook.Worksheets[0]; //Set styles IStyles styles = workbook.Styles; </code> </example>

</summary>

UnProtect
<summary>
unprotect file

also upprotect workbook window and structure

</summary>

UnProtectWorkbook(password: str)
<summary>

unprotect workbook window and structure

</summary> <param name=”password”>password</param>

property UseStorageMode: bool
<summary>

User Compound storage mode

</summary>

property Version: ExcelVersion
property Worksheets: WorksheetsCollection
<summary>

Returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.

</summary>

property WriteProtection: WriteProtection
<summary>

Returns options of the workbook write protection.

</summary>