Workbook module

class Workbook.Workbook

Bases: SpireObject

AcceptAllTrackedChanges()

Accept all tracked changes in the workbook.

property ActiveSheet: Worksheet

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.

property ActiveSheetIndex: int

Gets or sets index of the active worksheet.

property AddInFunctions: AddInFunctionsCollection

Returns collection of add-in functions.

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

Computes the string formula

Parameters:

text –

CalculateAllValue()

Caculate all formula for the workbook

property CalculationMode: ExcelCalculationMode

Get or set calculation mode.

ChangePaletteColor(color: Color, index: int)

Changes the palette for the spreadsheet in the specified index.

Parameters:

  • color – Color structure

  • index – Palette index,Value should be from 0 to 55

Example:

#Create worksheet
workbook = Workbook()
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")
property Charts: ChartsCollection

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

property Chartsheets: List[ChartSheet]
property CheckComptiliblity: bool
property CodeName: str

Name which used by macros to access to workbook items.

property Colors: List[Color]

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

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get colors
System.Drawing.Color[] colors = workbook.Colors
#Get color
System.Drawing.color = colors[2]
#Set color
worksheet["B2"].Style.Color = color
#Save to file
workbook.SaveToFile("CellFormats.xlsx")
ColumnWidthToPixels(columnWidth: float) → float

onverts column width in characters into column width in pixels.

Parameters:

columnWidth – Column width in characters.

Returns:

Column width in pixels.

ContainsFont(font: ExcelFont) → bool

Indicates whether the workbook contains specified font.

Parameters:

font –

property ConverterSetting: ConverterSetting
CopyTheme(srcWorkbook: Workbook)

Copy the theme from source workbook

Parameters:

srcWorkbook – source workbook.

CopyThemeColor(srcWorkbook: Workbook)

Copy the theme color from source workbook

Parameters:

srcWorkbook – source workbook.

CopyToClipboard

Copies whole workbook to the clipboard.

CreateEmptySheet

Create a new worksheet.

CreateEmptySheets

Create workbook with specified number of worksheets.

Parameters:

sheetCount –

CreateFont

Creates a font object and add it to the workbook. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set text
richText = worksheet["B2"].RichText
#Create font
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")
CreatePivotStyle() → PivotStyle
property CultureInfo: CultureInfo
property CustomDocumentProperties: ICustomDocumentProperties

Returns collection that represents all the custom document properties for the specified workbook. Read-only. Example:

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Get the document properties
documentProperties = workbook.CustomDocumentProperties
property CustomFontFileDirectory: List[str]

Sets the custom directory of font files.

property CustomFontFilePaths: List[str]

Returns or sets the custom path of font files.

property DataConns: DataConnections
property DataSorter: DataSorter

Gets Data sorter to sort the data..

property Date1904: bool

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

property DefaultFontName: str

Returns or sets the name of the default font. Example:

#Create worksheet
workbook = Workbook()
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")
property DefaultFontSize: float

Returns or sets the default font size. Example:

#Create worksheet
workbook = Workbook()
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")
property DisableMacrosStart: bool

Allows users to disable load of macros from document.

Dispose()
property DocumentProperties: BuiltInDocumentProperties

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

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Get the built in document properties
builtInDocumentProperties = workbook.DocumentProperties
property FileName: str

Gets file name.

FindBool(boolValue: bool) → CellRange

Finds the cell with the input bool.

Parameters:

boolValue – Bool value to search for.

Returns:

Found range.

Example:

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Find cell with specified bool value
result = workbook.FindBool(true)
FindDateTime(dateTimeValue: DateTime) → CellRange

Finds the cell with the input datetime.

Parameters:

dateTimeValue – Datetime value to search for.

Returns:

Found range.

Example:

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Find cell with specified DataTime value
dateTime = DateTime.Now
result = workbook.FindDateTime(dateTime)
FindNumber(doubleValue: float, formulaValue: bool) → CellRange

Finds the cell with the input number.

Parameters:

  • doubleValue – Double value to search for.

  • formulaValue – Indicates whether includes formula value to search for.

Returns:

Found range.

Example:

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Find cell with specified double value
value = 9.00
result = workbook.FindNumber(value, false)
FindString(stringValue: str, formula: bool, formulaValue: bool) → CellRange

Finds the cell with the input string.

Parameters:

  • stringValue – String value to search for.

  • formula – Indicates whether includes formula to search for.

  • formulaValue – Indicates whether includes formula value to search for.

Returns:

Found range.

Example:

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Find cell with specified string value
value = "value"
result = workbook.FindString(value, false, false)
FindTimeSpan(timeSpanValue: TimeSpan) → CellRange

Finds the cell with the input time span.

Parameters:

timeSpanValue – Time span value to search for.

Returns:

Found range.

Example:

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Find cell with specified TimeSpan value
timeSpan = new TimeSpan(2, 30, 30)
result = workbook.FindTimeSpan(timeSpan)
static FixDateTimeNow() → DateTime
static FixDateTimeToday() → DateTime
GetChartSheetByName(name: str) → ChartSheet
GetDigitalSignatures() → IDigitalSignatures

Get collection of DigitalSignature in this file.

Returns:

Collection of DigitalSignature

GetMatchingColor

Find best matching Color in workbook palette.

Parameters:

color – Color to search for.

Returns:

Workbook palette color.

Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get color
color = workbook.GetMatchingColor(System.Drawing.Color.Red)
#Set color
worksheet["B2"].Style.KnownColor = color
#Save to file
workbook.SaveToFile("CellFormats.xlsx")
GetOleSize() → IXLSRange

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

Returns:

if there does not set the size ,return null

GetPaletteColor(color: ExcelColors) → Color

Gets excel color from workbook palette.

Parameters:

color – Index from palette array.

Returns:

RGB Color.

Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get color
System.Drawing.color = workbook.GetPaletteColor(ExcelColors.Red)
#Set color
worksheet["B2"].Style.Color = workbook.Colors[10]
#Save to file
workbook.SaveToFile("CellFormats.xlsx")
GetSafeSheetName
GetThemeColor(type: ThemeColorType) → Color

Gets theme color.

Parameters:

type – The theme color type.

Returns:

The theme color.

property HasMacros: bool

Indicates whether contains VBA macros.

property HasTrackedChanges: bool

Indicates whether the workbook has any tracked changes

InitCalcEngine()

Initilize Calc engine

property IsCellProtection: bool

Indicates whether cells are protected.

property IsDigitallySigned: bool

Indicates whether this spreadsheet is digitally signed.

property IsHScrollBarVisible: bool

Returns or sets whether show horizontal scroll bar. Example:

#Create workbook
workbook = Workbook()
#Hide horizontal scroll bar
workbook.IsHScrollBarVisible = false
#Save to file
workbook.SaveToFile("IsHScrollBarVisible.xlsx")
property IsHideWindow: bool

Hide window

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

Indicates whether worksheet is displayed right to left.

property IsSaved: bool

Indicates whether workbook changes have been saved.

property IsVScrollBarVisible: bool

Returns or sets whether show vertical scroll bar. Example:

#Create workbook
workbook = Workbook()
#Hide vertical scroll bar
workbook.IsVScrollBarVisible = false
#Save to file
workbook.SaveToFile("IsVScrollBarVisible.xlsx")
property IsWindowProtection: bool

Indicates window is protected.

LoadFromFile

Loads a file and imports its data.

Parameters:

FileName – File name.

LoadFromHtml

Loads a html file and imports its data.

Parameters:

fileName – File name.

LoadFromMHtml

Loads a Mhtml file and imports its data.

Parameters:

fileName – File name.

LoadFromStream

Load workbook from the stream.

Parameters:

stream – Stream contains data.

LoadFromXml

Loads a xml file and imports its data.

Parameters:

fileName – File name.

LoadTemplateFromFile

Load workbook from the excel template.

Parameters:

fileName – File name

property MarkerDesigner: MarkerDesigner
property MaxColumnCount: int
property MaxDigitWidth: int
property MaxRowCount: int
property MaxRowsOfSharedFormula: int
property NameRanges: INameRanges

Gets Names collection that represents all the names in the specified workbook. Example:

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Get names
names = workbook.NameRanges
property OpenPassword: str

Returns or sets password to encrypt document.

property OptimizeImport: bool

Setting this property to True can decrease memory significantly, but will increase the performance of data import .

Type:

Indicates whether to optimize Import data. This option will take effect only on Import methods that are available with the worksheet WARNING

PasteFromClipboard()

Copies workbook and all its worksheets from the clipboard.

property PivotCaches: PivotCachesCollection

Returns pivot caches collection. Read-only. Example:

#Create worksheet
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
worksheet = workbook.Worksheets[0]
#Gets pivot caches collection
pivotCaches = workbook.PivotCaches
PixelsToColumnWidth(pixels: float) → float

Converts column width in pixels into column width in characters.

Parameters:

pixels – Column width in pixels.

Returns:

Column width in characters.

property ProhibitDtd: bool

prohibt dtd processing switch

Protect

protect file also protect workbook window and structure.

Parameters:

passwordToOpen – password to open file.

ProtectWorkbook(bIsProtectWindow: bool, bIsProtectContent: bool, password: str)

Sets protection for workbook.

Parameters:

  • bIsProtectWindow – Indicates if protect workbook window.

  • bIsProtectContent – Indicates if protect workbook content.

  • password – password

property ReadOnly: bool

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

RejectAllTrackedChanges()

Reject all tracked changes in the workbook.

RemoveAllDigitalSignatures()

Remove all DigitalSignature in this file.

Replace

Replaces cell’s value by specified value.

Parameters:

  • oldValue – Value to replace.

  • newValue – New value

Example:

#Create workbook
workbook = Workbook()
workbook.LoadFromFile("Sample.xlsx")
#Replace the oldValue by dateTime
oldValue = "Find"
dateTime = DateTime.Now
workbook.Replace(oldValue, dateTime)
#Save to file
workbook.SaveToFile("Replace.xlsx")
ResetPalette()

Resets the color palette to the default colors. Example:

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Get colors
System.Drawing.Color[] colors = workbook.Colors
#Check color
print(colors[2].Name)
#Set color
colors[2] = System.Drawing.Color.Yellow
#Reset palette
workbook.ResetPalette()
#Check color
print(workbook.Colors[2].Name)
#Save to file
workbook.SaveToFile("CellFormats.xlsx")
Save()

Saves changes to the specified workbook

SaveAsImage

Save workbook to image.

Parameters:

  • sheetIndex – sheet Index

  • dpiX – Dpi X

  • dpiY – Dpi Y

SaveAsTemplate

Save workbook as template to file.

Parameters:

fileName – File name.

SaveAsXml

Save workbook to an XML data file.

Parameters:

fileName – XML file name.

SaveChartAsEmfImage

Save chart to vector image.

Parameters:

  • worksheet – work sheet

  • chartIndex – chart index

SaveChartAsImage

Save chart to image.

Parameters:

worksheet – chart sheet

SaveToFile

Saves changes to the workbook in a different file

Parameters:

fileName – File name.

SaveToHtml

Saves the workbook to html

Parameters:

fileName – File name.

SaveToStream

Save workbook the stream

Parameters:

Stream – Stream object

property SelectedTab: int

Gets or sets tab index of selected.

SetGlobalCustomFontsFolders

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.

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

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

Parameters:

  • firstRow – the visible range’s first row index

  • firstColumn – the visible range’s first column index

  • lastRow – the visible range’s last row index

  • lastColumn – the visible range’s last column index

SetThemeColor(type: ThemeColorType, color: Color)

Sets the theme color

Parameters:

  • type – The theme color type.

  • color – the theme color

SetWriteProtectionPassword(password: str)

Sets write protection password.

Parameters:

password – Password to set

property SheetTabBarWidth: int

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

property ShowTabs: bool

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

property Styles: StylesCollection

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

#Create worksheet
workbook = Workbook()
worksheet = workbook.Worksheets[0]
#Set styles
styles = workbook.Styles
TrackedChanges(switchValue: bool)

Turn revision mode on or off.

UnProtect

unprotect file also upprotect workbook window and structure

UnProtectWorkbook(password: str)

unprotect workbook window and structure

Parameters:

password – password

property UseStorageMode: bool

User Compound storage mode

property Version: ExcelVersion
property Worksheets: WorksheetsCollection

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

property WriteProtection: WriteProtection

Returns options of the workbook write protection.