Table of Contents

Class XLWorkbook

Namespace
SlapKit.Excel.Excel
Assembly
SlapKit.Excel.dll
public class XLWorkbook : IXLWorkbook, IXLProtectable<IXLWorkbookProtection, XLWorkbookProtectionElements>, IXLProtectable, IDisposable
Inheritance
XLWorkbook
Implements
Inherited Members
Extension Methods

Constructors

XLWorkbook()

Creates a new Excel workbook.

public XLWorkbook()

XLWorkbook(LoadOptions) 

public XLWorkbook(LoadOptions loadOptions)

Parameters

loadOptions LoadOptions

XLWorkbook(Stream)

Opens an existing workbook from a stream.

public XLWorkbook(Stream stream)

Parameters

stream Stream

The stream to open.

XLWorkbook(Stream, LoadOptions) 

public XLWorkbook(Stream stream, LoadOptions loadOptions)

Parameters

stream Stream
loadOptions LoadOptions

XLWorkbook(string)

Opens an existing workbook from a file.

public XLWorkbook(string file)

Parameters

file string

The file to open.

XLWorkbook(string, LoadOptions) 

public XLWorkbook(string file, LoadOptions loadOptions)

Parameters

file string
loadOptions LoadOptions

Properties

Author 

public string Author { get; set; }

Property Value

string

CalculateMode

Gets or sets the workbook's calculation mode.

public XLCalculateMode CalculateMode { get; set; }

Property Value

XLCalculateMode

CalculationOnSave 

public bool CalculationOnSave { get; set; }

Property Value

bool

CellSetValueBehavior

Behavior for Value.set

public static XLCellSetValueBehavior CellSetValueBehavior { get; set; }

Property Value

XLCellSetValueBehavior

Chartsheets

Gets an object to manipulate the chartsheets.

public IXLChartsheets Chartsheets { get; }

Property Value

IXLChartsheets

ColumnWidth

Gets or sets the default column width for the workbook.

All new worksheets will use this column width.

public double ColumnWidth { get; set; }

Property Value

double

CustomProperties 

public IXLCustomProperties CustomProperties { get; }

Property Value

IXLCustomProperties

DefaultColumnWidth 

public static double DefaultColumnWidth { get; }

Property Value

double

DefaultOutline 

public static IXLOutline DefaultOutline { get; }

Property Value

IXLOutline

DefaultPageOptions 

public static IXLPageSetup DefaultPageOptions { get; }

Property Value

IXLPageSetup

DefaultRightToLeft 

public bool DefaultRightToLeft { get; }

Property Value

bool

DefaultRowHeight 

public static double DefaultRowHeight { get; }

Property Value

double

DefaultShowFormulas 

public bool DefaultShowFormulas { get; }

Property Value

bool

DefaultShowGridLines 

public bool DefaultShowGridLines { get; }

Property Value

bool

DefaultShowOutlineSymbols 

public bool DefaultShowOutlineSymbols { get; }

Property Value

bool

DefaultShowRowColHeaders 

public bool DefaultShowRowColHeaders { get; }

Property Value

bool

DefaultShowRuler 

public bool DefaultShowRuler { get; }

Property Value

bool

DefaultShowWhiteSpace 

public bool DefaultShowWhiteSpace { get; }

Property Value

bool

DefaultShowZeros 

public bool DefaultShowZeros { get; }

Property Value

bool

DefaultStyle 

public static IXLStyle DefaultStyle { get; }

Property Value

IXLStyle

FileSharing 

public IXLFileSharing FileSharing { get; }

Property Value

IXLFileSharing

ForceFullCalculation 

public bool ForceFullCalculation { get; set; }

Property Value

bool

FullCalculationOnLoad 

public bool FullCalculationOnLoad { get; set; }

Property Value

bool

FullPrecision 

public bool FullPrecision { get; set; }

Property Value

bool

IsPasswordProtected

Gets a value indicating whether this instance is protected with a password.

public bool IsPasswordProtected { get; }

Property Value

bool

true if this instance is password protected; otherwise, false.

IsProtected

Gets a value indicating whether this instance is protected, either with or without a password.

public bool IsProtected { get; }

Property Value

bool

true if this instance is protected; otherwise, false.

LockStructure 

public bool LockStructure { get; set; }

Property Value

bool

LockWindows 

public bool LockWindows { get; set; }

Property Value

bool

NamedRanges

Gets an object to manipulate this workbook's named ranges.

public IXLNamedRanges NamedRanges { get; }

Property Value

IXLNamedRanges

Outline

Gets or sets the default outline options for the workbook.

All new worksheets will use these outline options.

public IXLOutline Outline { get; set; }

Property Value

IXLOutline

PageOptions

Gets or sets the default page options for the workbook.

All new worksheets will use these page options.

public IXLPageSetup PageOptions { get; set; }

Property Value

IXLPageSetup

PivotCaches

All pivot caches in the workbook, whether they have a pivot table or not.

public IXLPivotCaches PivotCaches { get; }

Property Value

IXLPivotCaches

PredefinedStyles 

public IXLPredefinedStyles PredefinedStyles { get; }

Property Value

IXLPredefinedStyles

Properties

Gets or sets the workbook's properties.

public XLWorkbookProperties Properties { get; set; }

Property Value

XLWorkbookProperties

ReferenceStyle

Gets or sets the workbook's reference style.

public XLReferenceStyle ReferenceStyle { get; set; }

Property Value

XLReferenceStyle

RightToLeft 

public bool RightToLeft { get; set; }

Property Value

bool

RowHeight

Gets or sets the default row height for the workbook.

All new worksheets will use this row height.

public double RowHeight { get; set; }

Property Value

double

Sheets

Gets an object to manipulate the sheets.

public IXLSheets Sheets { get; }

Property Value

IXLSheets

ShowFormulas 

public bool ShowFormulas { get; set; }

Property Value

bool

ShowGridLines 

public bool ShowGridLines { get; set; }

Property Value

bool

ShowOutlineSymbols 

public bool ShowOutlineSymbols { get; set; }

Property Value

bool

ShowRowColHeaders 

public bool ShowRowColHeaders { get; set; }

Property Value

bool

ShowRuler 

public bool ShowRuler { get; set; }

Property Value

bool

ShowWhiteSpace 

public bool ShowWhiteSpace { get; set; }

Property Value

bool

ShowZeros 

public bool ShowZeros { get; set; }

Property Value

bool

Style

Gets or sets the default style for the workbook.

All new worksheets will use this style.

public IXLStyle Style { get; set; }

Property Value

IXLStyle

Theme

Gets an object to manipulate this workbook's theme.

public IXLTheme Theme { get; }

Property Value

IXLTheme

Use1904DateSystem 

public bool Use1904DateSystem { get; set; }

Property Value

bool

Worksheets

Gets an object to manipulate the worksheets.

public IXLWorksheets Worksheets { get; }

Property Value

IXLWorksheets

Methods

AddChartsheet() 

public IXLChartsheet AddChartsheet()

Returns

IXLChartsheet

AddChartsheet(IXLChartsheet) 

public void AddChartsheet(IXLChartsheet chartsheet)

Parameters

chartsheet IXLChartsheet

AddChartsheet(int) 

public IXLChartsheet AddChartsheet(int position)

Parameters

position int

Returns

IXLChartsheet

AddChartsheet(string) 

public IXLChartsheet AddChartsheet(string sheetName)

Parameters

sheetName string

Returns

IXLChartsheet

AddChartsheet(string, int) 

public IXLChartsheet AddChartsheet(string sheetName, int position)

Parameters

sheetName string
position int

Returns

IXLChartsheet

AddWorksheet() 

public IXLWorksheet AddWorksheet()

Returns

IXLWorksheet

AddWorksheet(IXLWorksheet) 

public void AddWorksheet(IXLWorksheet worksheet)

Parameters

worksheet IXLWorksheet

AddWorksheet(DataSet) 

public void AddWorksheet(DataSet dataSet)

Parameters

dataSet DataSet

AddWorksheet(DataTable)

Add a worksheet with a table at Cell(row:1, column:1). The dataTable's name is used for the worksheet name. The name of a table will be generated as Table{number suffix}.

public IXLWorksheet AddWorksheet(DataTable dataTable)

Parameters

dataTable DataTable

Datatable to insert

Returns

IXLWorksheet

Inserted Worksheet

AddWorksheet(DataTable, string)

Add a worksheet with a table at Cell(row:1, column:1). The sheetName provided is used for the worksheet name. The name of a table will be generated as Table{number suffix}.

public IXLWorksheet AddWorksheet(DataTable dataTable, string sheetName)

Parameters

dataTable DataTable

dataTable to insert as Excel Table

sheetName string

Worksheet and Excel Table name

Returns

IXLWorksheet

Inserted Worksheet

AddWorksheet(DataTable, string, string)

Add a worksheet with a table at Cell(row:1, column:1).

public IXLWorksheet AddWorksheet(DataTable dataTable, string sheetName, string tableName)

Parameters

dataTable DataTable

dataTable to insert as Excel Table

sheetName string

Worksheet name

tableName string

Excel Table name

Returns

IXLWorksheet

Inserted Worksheet

AddWorksheet(int) 

public IXLWorksheet AddWorksheet(int position)

Parameters

position int

Returns

IXLWorksheet

AddWorksheet(string) 

public IXLWorksheet AddWorksheet(string sheetName)

Parameters

sheetName string

Returns

IXLWorksheet

AddWorksheet(string, int) 

public IXLWorksheet AddWorksheet(string sheetName, int position)

Parameters

sheetName string
position int

Returns

IXLWorksheet

Cell(string) 

public IXLCell Cell(string namedCell)

Parameters

namedCell string

Returns

IXLCell

CellFromFullAddress(string, out IXLWorksheet) 

public IXLCell CellFromFullAddress(string cellAddress, out IXLWorksheet ws)

Parameters

cellAddress string
ws IXLWorksheet

Returns

IXLCell

Cells(string) 

public IXLCells Cells(string namedCells)

Parameters

namedCells string

Returns

IXLCells

Chartsheet(int) 

public IXLChartsheet Chartsheet(int position)

Parameters

position int

Returns

IXLChartsheet

Chartsheet(string) 

public IXLChartsheet Chartsheet(string name)

Parameters

name string

Returns

IXLChartsheet

CreateEmptyStyle() 

public IXLStyle CreateEmptyStyle()

Returns

IXLStyle

CustomProperty(string) 

public IXLCustomProperty CustomProperty(string name)

Parameters

name string

Returns

IXLCustomProperty

Dispose() 

public void Dispose()

Evaluate(string)

Evaluate a formula expression.

public XLCellValue Evaluate(string expression)

Parameters

expression string

Formula expression to evaluate.

Returns

XLCellValue

Exceptions

MissingContextException

If the expression contains a function that requires a context (e.g. current cell or worksheet).

EvaluateExpr(string)

Evaluate a formula and return a value. Formulas with references don't work and culture used for conversion is invariant.

public static XLCellValue EvaluateExpr(string expression)

Parameters

expression string

Returns

XLCellValue

FindCells(Func<IXLCell, bool>) 

public IXLCells FindCells(Func<IXLCell, bool> predicate)

Parameters

predicate Func<IXLCell, bool>

Returns

IXLCells

FindColumns(Func<IXLColumn, bool>) 

public IXLColumns FindColumns(Func<IXLColumn, bool> predicate)

Parameters

predicate Func<IXLColumn, bool>

Returns

IXLColumns

FindRows(Func<IXLRow, bool>) 

public IXLRows FindRows(Func<IXLRow, bool> predicate)

Parameters

predicate Func<IXLRow, bool>

Returns

IXLRows

GetAnchorFromImageId(DrawingsPart, string) 

public static OpenXmlElement GetAnchorFromImageId(DrawingsPart drawingsPart, string relId)

Parameters

drawingsPart DrawingsPart
relId string

Returns

OpenXmlElement

GetAnchorFromImageIndex(WorksheetPart, int) 

public static OpenXmlElement GetAnchorFromImageIndex(WorksheetPart worksheetPart, int index)

Parameters

worksheetPart WorksheetPart
index int

Returns

OpenXmlElement

GetImageRelIdFromAnchor(OpenXmlElement) 

public static string GetImageRelIdFromAnchor(OpenXmlElement anchor)

Parameters

anchor OpenXmlElement

Returns

string

GetPropertiesFromAnchor(OpenXmlElement) 

public static NonVisualDrawingProperties GetPropertiesFromAnchor(OpenXmlElement anchor)

Parameters

anchor OpenXmlElement

Returns

NonVisualDrawingProperties

NamedRange(string) 

public IXLNamedRange NamedRange(string rangeName)

Parameters

rangeName string

Returns

IXLNamedRange

OpenFromTemplate(string) 

public static XLWorkbook OpenFromTemplate(string path)

Parameters

path string

Returns

XLWorkbook

Protect(Algorithm)

Protects this instance without a password.

public IXLWorkbookProtection Protect(XLProtectionAlgorithm.Algorithm algorithm = Algorithm.SimpleHash)

Parameters

algorithm XLProtectionAlgorithm.Algorithm

Returns

IXLWorkbookProtection

Protect(Algorithm, XLWorkbookProtectionElements) 

public IXLWorkbookProtection Protect(XLProtectionAlgorithm.Algorithm algorithm, XLWorkbookProtectionElements allowedElements)

Parameters

algorithm XLProtectionAlgorithm.Algorithm
allowedElements XLWorkbookProtectionElements

Returns

IXLWorkbookProtection

Protect(XLWorkbookProtectionElements) 

public IXLWorkbookProtection Protect(XLWorkbookProtectionElements allowedElements)

Parameters

allowedElements XLWorkbookProtectionElements

Returns

IXLWorkbookProtection

Protect(string, Algorithm)

Protects this instance using the specified password and password hash algorithm.

public IXLWorkbookProtection Protect(string password, XLProtectionAlgorithm.Algorithm algorithm = Algorithm.SimpleHash)

Parameters

password string

The password.

algorithm XLProtectionAlgorithm.Algorithm

The algorithm.

Returns

IXLWorkbookProtection

Protect(string, Algorithm, XLWorkbookProtectionElements) 

public IXLWorkbookProtection Protect(string password, XLProtectionAlgorithm.Algorithm algorithm, XLWorkbookProtectionElements allowedElements)

Parameters

password string
algorithm XLProtectionAlgorithm.Algorithm
allowedElements XLWorkbookProtectionElements

Returns

IXLWorkbookProtection

Range(string) 

public IXLRange Range(string range)

Parameters

range string

Returns

IXLRange

RangeFromFullAddress(string, out IXLWorksheet) 

public IXLRange RangeFromFullAddress(string rangeAddress, out IXLWorksheet ws)

Parameters

rangeAddress string
ws IXLWorksheet

Returns

IXLRange

Ranges(string) 

public IXLRanges Ranges(string ranges)

Parameters

ranges string

Returns

IXLRanges

RecalculateAllFormulas()

Force recalculation of all cell formulas.

public void RecalculateAllFormulas()

Save()

Saves the current workbook.

public void Save()

Save(SaveOptions) 

public void Save(SaveOptions options)

Parameters

options SaveOptions

Save(bool, bool)

Saves the current workbook and optionally performs validation

public void Save(bool validate, bool evaluateFormulae = false)

Parameters

validate bool
evaluateFormulae bool

SaveAs(Stream)

Saves the current workbook to a stream.

public void SaveAs(Stream stream)

Parameters

stream Stream

SaveAs(Stream, SaveOptions) 

public void SaveAs(Stream stream, SaveOptions options)

Parameters

stream Stream
options SaveOptions

SaveAs(Stream, bool, bool)

Saves the current workbook to a stream and optionally validates it.

public void SaveAs(Stream stream, bool validate, bool evaluateFormulae = false)

Parameters

stream Stream
validate bool
evaluateFormulae bool

SaveAs(string)

Saves the current workbook to a file.

public void SaveAs(string file)

Parameters

file string

SaveAs(string, SaveOptions) 

public void SaveAs(string file, SaveOptions options)

Parameters

file string
options SaveOptions

SaveAs(string, bool, bool)

Saves the current workbook to a file and optionally validates it.

public void SaveAs(string file, bool validate, bool evaluateFormulae = false)

Parameters

file string
validate bool
evaluateFormulae bool

Search(string, CompareOptions, bool)

Searches the cells' contents for a given piece of text

public IEnumerable<IXLCell> Search(string searchText, CompareOptions compareOptions = CompareOptions.Ordinal, bool searchFormulae = false)

Parameters

searchText string

The search text.

compareOptions CompareOptions

The compare options.

searchFormulae bool

if set to true search formulae instead of cell values.

Returns

IEnumerable<IXLCell>

SetLockStructure(bool) 

public XLWorkbook SetLockStructure(bool value)

Parameters

value bool

Returns

XLWorkbook

SetLockWindows(bool) 

public XLWorkbook SetLockWindows(bool value)

Parameters

value bool

Returns

XLWorkbook

SetUse1904DateSystem() 

public XLWorkbook SetUse1904DateSystem()

Returns

XLWorkbook

SetUse1904DateSystem(bool) 

public XLWorkbook SetUse1904DateSystem(bool value)

Parameters

value bool

Returns

XLWorkbook

Sheet(int) 

public IXLSheet Sheet(int position)

Parameters

position int

Returns

IXLSheet

Sheet(string) 

public IXLSheet Sheet(string name)

Parameters

name string

Returns

IXLSheet

Table(string, StringComparison)

Gets the Excel table of the given name

public IXLTable Table(string tableName, StringComparison comparisonType = StringComparison.OrdinalIgnoreCase)

Parameters

tableName string

Name of the table to return.

comparisonType StringComparison

One of the enumeration values that specifies how the strings will be compared.

Returns

IXLTable

The table with given name

Exceptions

ArgumentOutOfRangeException

If no tables with this name could be found in the workbook.

ToString() 

public override string ToString()

Returns

string

TryGetChartsheet(string, out IXLChartsheet) 

public bool TryGetChartsheet(string name, out IXLChartsheet chartsheet)

Parameters

name string
chartsheet IXLChartsheet

Returns

bool

TryGetSheet(string, out IXLSheet) 

public bool TryGetSheet(string name, out IXLSheet sheet)

Parameters

name string
sheet IXLSheet

Returns

bool

TryGetWorksheet(string, out IXLWorksheet) 

public bool TryGetWorksheet(string name, out IXLWorksheet worksheet)

Parameters

name string
worksheet IXLWorksheet

Returns

bool

Unprotect()

Unprotects this instance without a password.

public IXLWorkbookProtection Unprotect()

Returns

IXLWorkbookProtection

Unprotect(string)

Unprotects this instance using the specified password.

public IXLWorkbookProtection Unprotect(string password)

Parameters

password string

The password.

Returns

IXLWorkbookProtection

Worksheet(int) 

public IXLWorksheet Worksheet(int position)

Parameters

position int

Returns

IXLWorksheet

Worksheet(string) 

public IXLWorksheet Worksheet(string name)

Parameters

name string

Returns

IXLWorksheet