ExcelFile Class

Namespace:

GemBox.Spreadsheet

Assembly:

GemBox.Spreadsheet.dll

Excel file contains one or more worksheets (ExcelWorksheet) and workbook related properties and methods.

• C#
• VB.NET

public sealed class ExcelFile

Public NotInheritable Class ExcelFile

Initializes an empty (no worksheets) instance of the ExcelFile class.

• C#
• VB.NET

public ExcelFile()

Public Sub New

To add new worksheets to a blank file use Worksheets property, Add(String) or Add(SheetType, String) methods.

To load / read a spreadsheet from a file or a stream, use static Load(String) methods.

To save / write a spreadsheet to a file or a stream use Save(String) methods.

Gets or sets a value indicating whether the formula address references are automatically updated.

• C#
• VB.NET

public bool AutomaticFormulaUpdate { get; set; }

Public Property AutomaticFormulaUpdate As Boolean

System.Boolean

If you use address references in a formula, those references will be automatically updated if you insert or delete rows or columns and those changes affect the address reference in some way.

If this property is false, formula address references will not update automatically. This should be used only for performance reasons when working on large files.

Default value of this property is true.

Gets the calculation engine options.

• C#
• VB.NET

public CalculationOptions CalculationOptions { get; }

Public ReadOnly Property CalculationOptions As CalculationOptions

CalculationOptions

Gets or sets the name of the VbaModule that is bound to this ExcelFile.

• C#
• VB.NET

public string CodeName { get; set; }

Public Property CodeName As String

System.String

Gets the collection of defined names that are not directly related with one cell range. Supported only in XLSX, XLSB, XLS and XML.

• C#
• VB.NET

public DefinedNameCollection DefinedNames { get; }

Public ReadOnly Property DefinedNames As DefinedNameCollection

DefinedNameCollection

Gets DocumentProperties of this ExcelFile.

• C#
• VB.NET

public DocumentProperties DocumentProperties { get; }

Public ReadOnly Property DocumentProperties As DocumentProperties

DocumentProperties

Maximum number of affected cells in group set methods.

• C#
• VB.NET

public int GroupMethodsAffectedCellsLimit { get; set; }

Public Property GroupMethodsAffectedCellsLimit As Integer

System.Int32

If user tries to modify all cells in a group which has more cells than specified limit, exception will be thrown. This property was introduced to prevent users from accidentally modifying millions of cells which results in a long delay, a large memory allocation and a big resulting file. You can set this limit to value which suits your needs (minimum is 5).

Gets the collection of workbook links (also called external references).

• C#
• VB.NET

public WorkbookLinkCollection Links { get; }

Public ReadOnly Property Links As WorkbookLinkCollection

WorkbookLinkCollection

Gets or sets the maximum digit width of the numbers 0, 1, 2, ..., 9 as rendered in the Normal style's font.

The MaximumDigitWidth is critically important for getting and setting column widths.

You should set the MaximumDigitWidth to zero, so it gets automatically reinitialized upon next retrieval, if any of the following is changed:

• The FontsBaseUri.
• The Dpi.
• The Normal style's font.

The automatically initialized value might differ from MS Excel's value (usually by 1 pixel). In that case you can override the automatically initialized value by manually setting a value.

• C#
• VB.NET

public int MaximumDigitWidth { get; set; }

Public Property MaximumDigitWidth As Integer

System.Int32

System.ArgumentOutOfRangeException

Gets the collection of PivotCaches defined in this ExcelFile instance.

Currently supported in XLSX only.

• C#
• VB.NET

public PivotCacheCollection PivotCaches { get; }

Public ReadOnly Property PivotCaches As PivotCacheCollection

PivotCacheCollection

Pivot Tables are used to arrange and aggregate complicated data and drill down on details.

Source of data for Pivot Table can be:

• WorksheetSource - CellRange, NamedRange, table, etc.
• External - database, OLAP cube, textual file, web query, etc.
• Other - multiple consolidation ranges in the workbook, another Pivot Table, etc.

Source of the data for Pivot Table contains following information:

• Scheme - data source fields/columns, its data types, etc.
• Data - actual data source records.

Pivot Table does not directly reference its data source, but instead it references a cache of a data source. Cache of a data source contains an in-memory copy of data source scheme and, optionally, data and is stored in a workbook which may contain multiple caches of various sources of data.

Cache can be updated/refreshed from a data source automatically (for example, when opening a workbook) or on request. Cache is used to provide better performance of Pivot Table functionality which requires frequent access to data records and their values and to enable sharing the same data source between multiple Pivot Tables.

GemBox.Spreadsheet represents cache with a PivotCache type. Cache source is represented with a PivotCacheSource type (and its sub-types) and can be accessed from a CacheSource property. PivotCache instances are stored in a PivotCaches collection. PivotCache can be refreshed by using the Refresh() method.

PivotTable is created from a PivotCache with the specified name and the specified cell in the worksheet where it is placed using the Add(PivotCache, String, String) method. PivotTable contains the PivotFields collection which PivotField elements can be Add(Int32) into the following Pivot Table areas:

• Report Filter Area represented by PageFields collection.
• Row Axis represented by RowFields collection.
• Column Axis represented by ColumnFields collection.
• Values Area represented by DataFields collection.

Each (non-calculated) PivotField can contain zero or more PivotItems which are used for filtering and grouping by a specific value.

Except using fields defined in Pivot Table data source (more precisely, in PivotCache) which cannot be removed, Pivot Table can also define calculated PivotFields and calculated PivotItems which use a formula to specify their values. Calculated PivotField can be added using a AddCalculated(String, String) method and removed using a RemoveCalculated(String) method. Property IsCalculated tells if a field is calculated or is a field from the data source. Calculated PivotField is automatically moved to a Values Area (DataFields collection) and only Values Area can contain calculated PivotFields. Calculated PivotItem can be added using a AddCalculated(String, String) method and removed using a RemoveCalculated(String) method. Property IsCalculated tells if an item is calculated.

PivotFields can be re-ordered in a specific area using the Move(Int32, Int32) method. PivotItems can be re-ordered using the Move(Int32, Int32) method.

GemBox.Spreadsheet provides functionality to calculate PivotTables by using the Calculate() method which updates the cells in the ExcelWorksheet. The Calculate() method uses values stored in PivotCache. If the cache is currently empty, or the values are outdated (because the underlying data source has changed), the cache needs to be refreshed using the Refresh() method.

Gets or sets the workbook protection flag.

• C#
• VB.NET

public bool Protected { get; set; }

Public Property Protected As Boolean

System.Boolean

This property is simply written to Excel file and has no effect on the behavior of this library. For more information on workbook protection, consult Microsoft Excel documentation.

Gets workbook protection properties. Supported in XLSX only.

• C#
• VB.NET

public WorkbookProtection ProtectionSettings { get; }

Public ReadOnly Property ProtectionSettings As WorkbookProtection

WorkbookProtection

Gets the collection of workbook (master) Styles defined for this ExcelFile instance.

• C#
• VB.NET

public CellStyleCollection Styles { get; }

Public ReadOnly Property Styles As CellStyleCollection

CellStyleCollection

Conceptually, cell formatting is divided into following groups:

• Number - indicates how to format and render the numeric value of a cell. Associated property is NumberFormat.
• Alignment - formatting information pertaining to text alignment in cells. Associated properties are HorizontalAlignment, VerticalAlignment, Indent, Rotation, IsTextVertical, WrapText and ShrinkToFit.
• Font - defines the properties for the used font. Associated property is Font.
• Border - expresses a single set of cell border formats (left, right, top, bottom and diagonal). Associated property is Borders.
• Fill - specifies fill formatting (pattern or gradient). Associated property is FillPattern.
• Protection - contains protection properties associated with the cell. Associated properties are Locked and FormulaHidden.

Additional CellStyle properties not associated with any formatting group are:

• Name - name of the referenced Style.
• QuotePrefix - true to store numeric value of a cell as text; otherwise, false.
• IsDefault - true if Name Style is default (Normal) and there are no additional modifications of cell formatting; otherwise, false.

ExcelFile contains a set of Styles which can be referenced by multiple cells.

ExcelFile must always contain at least one Style which cannot be removed and is, by default, referenced by all cells. This default style is Normal.

Style can either be built-in or user-defined. Built-in style is accessible from Styles via BuiltInCellStyleName enumeration.

Cell formatting group (Number, Alignment, Font, Border, Fill or Protection) (and its associated properties) is resolved from Name Style, unless cell formatting group or its associated property is modified.

Cell formatting is available for one or more cells through Style property which is available on ExcelCell and CellRange types. Cell formatting specified on ExcelColumn and ExcelRow types through Style property is simply propagated to cell formatting of its Cells.

To set Style to one or more cells, simply assign it to Style property.

GemBox.Spreadsheet internally takes care not to allocate unnecessary cells when formatting a range of cells (for example, when formatting Cells) and to cache formatting information of equally formatted cells, at the appropriate time, to reduce memory footprint.

Gets or sets whether 1904 date system is used.

• C#
• VB.NET

public bool Use1904DateSystem { get; set; }

Public Property Use1904DateSystem As Boolean

System.Boolean

Default value for this property is false. For more information on 1904 date system, consult Microsoft Excel documentation.

Represents the VbaProject contained in the ExcelFile.

If the ExcelFile does not have VbaProject contained, it will be created on accessing this property.

• C#
• VB.NET

public VbaProject VbaProject { get; }

Public ReadOnly Property VbaProject As VbaProject

VbaProject

Collection of all worksheets (ExcelWorksheet) in a workbook.

• C#
• VB.NET

public ExcelWorksheetCollection Worksheets { get; }

Public ReadOnly Property Worksheets As ExcelWorksheetCollection

ExcelWorksheetCollection

Gets write protection properties. Supported in XLSX only.

• C#
• VB.NET

public WriteProtection WriteProtectionSettings { get; }

Public ReadOnly Property WriteProtectionSettings As WriteProtection

WriteProtection

Autodetects CSV separator. If there's no separators in the input stream returns (char)0.

• C#
• VB.NET

public static char AutoDetectCsvSeparator(Stream stream)

Public Shared Function AutoDetectCsvSeparator(stream As Stream) As Char

stream

System.IO.Stream

System.Char

This method counts the number of occurrences of the most common separators (',', ';', '\t') and returns one that has occurred the most times in first 5 rows.

Autodetects CSV separator. If there's no separators in the input stream returns (char)0.

• C#
• VB.NET

public static char AutoDetectCsvSeparator(StreamReader sr, int rowsToTest, char[] possibleSeparators)

Public Shared Function AutoDetectCsvSeparator(sr As StreamReader, rowsToTest As Integer, possibleSeparators As Char()) As Char

sr

System.IO.StreamReader

rowsToTest

System.Int32

possibleSeparators

System.Char[]

System.Char

This method counts the number of occurrences of chars that are specified in the array possibleSeparators, and returns one that has occurred the most times in first rowsToTest rows of StreamReader sr.

Calculates formula for every excel cell in all worksheets.

• C#
• VB.NET

public void Calculate()

Public Sub Calculate

Makes a deep clone of this ExcelFile instance.

• C#
• VB.NET

public ExcelFile Clone()

Public Function Clone As ExcelFile

ExcelFile

Converts single page specified in options of this ExcelFile instance to an System.Windows.Media.ImageSource instance.

• C#
• VB.NET

public ImageSource ConvertToImageSource(ImageSaveOptions options)

Public Function ConvertToImageSource(options As ImageSaveOptions) As ImageSource

options

ImageSaveOptions

System.Windows.Media.ImageSource

Converts this ExcelFile instance to an System.Windows.Xps.Packaging.XpsDocument instance.

• C#
• VB.NET

public XpsDocument ConvertToXpsDocument(XpsSaveOptions options)

Public Function ConvertToXpsDocument(options As XpsSaveOptions) As XpsDocument

options

XpsSaveOptions

System.Windows.Xps.Packaging.XpsDocument

Gets the workbook paginator.

• C#
• VB.NET

public ExcelFilePaginator GetPaginator()

Public Function GetPaginator As ExcelFilePaginator

ExcelFilePaginator

Gets the workbook paginator based on the specified options.

• C#
• VB.NET

public ExcelFilePaginator GetPaginator(PaginatorOptions options)

Public Function GetPaginator(options As PaginatorOptions) As ExcelFilePaginator

options

PaginatorOptions

ExcelFilePaginator

Loads a spreadsheet from the specified stream. This method tries to dynamically determine the file format. If you know the file format, it is more reliable and faster to use Load(Stream, LoadOptions) method.

• C#
• VB.NET

public static ExcelFile Load(Stream stream)

Public Shared Function Load(stream As Stream) As ExcelFile

stream

System.IO.Stream

ExcelFile

Loads a spreadsheet from the specified stream.

• C#
• VB.NET

public static ExcelFile Load(Stream stream, LoadOptions options)

Public Shared Function Load(stream As Stream, options As LoadOptions) As ExcelFile

stream

System.IO.Stream

options

LoadOptions

ExcelFile

Loads a spreadsheet from CSV data using the specified reader.

• C#
• VB.NET

public static ExcelFile Load(TextReader reader, CsvLoadOptions options)

Public Shared Function Load(reader As TextReader, options As CsvLoadOptions) As ExcelFile

reader

System.IO.TextReader

options

CsvLoadOptions

ExcelFile

Loads a spreadsheet from a file with the specified path.

• C#
• VB.NET

public static ExcelFile Load(string path)

Public Shared Function Load(path As String) As ExcelFile

path

System.String

ExcelFile

File extension that is extracted from the path is used to create the appropriate LoadOptions derived class instance that is then passed to the Load(String, LoadOptions) method.

Following file extensions are supported:

• Microsoft Excel: .xlsx, xlsm, .xltx, .xltm, .xls, .xlt, .xlsb
• OpenDocument Spreadsheet: .ods, .ots
• Character Separated Values: .csv, .tsv
• HyperText Markup Language: .htm, .html, .mht, .mhtml
• Extensible Markup Language: .xml (XML Spreadsheet 2003 / SpreadsheetML)

Loads a spreadsheet from a file with the specified path.

• C#
• VB.NET

public static ExcelFile Load(string path, LoadOptions options)

Public Shared Function Load(path As String, options As LoadOptions) As ExcelFile

path

System.String

options

LoadOptions

ExcelFile

Prints the document to the default printer.

• C#
• VB.NET

public void Print()

Public Sub Print

Prints the document to the specified printer.

• C#
• VB.NET

public void Print(string printerName)

Public Sub Print(printerName As String)

printerName

System.String

Prints the document to the specified printer according to specified options.

• C#
• VB.NET

public void Print(string printerName, PrintOptions options)

Public Sub Print(printerName As String, options As PrintOptions)

printerName

System.String

options

PrintOptions

Saves the spreadsheet to the specified stream.

• C#
• VB.NET

[ComVisible(false)]
public void Save(Stream stream, SaveOptions options)

<ComVisible(False)>
Public Sub Save(stream As Stream, options As SaveOptions)

stream

System.IO.Stream

options

SaveOptions

Save the spreadsheet to CSV format using the specified writer.

• C#
• VB.NET

[ComVisible(false)]
public void Save(TextWriter writer, CsvSaveOptions options)

<ComVisible(False)>
Public Sub Save(writer As TextWriter, options As CsvSaveOptions)

writer

System.IO.TextWriter

options

CsvSaveOptions

Saves the spreadsheet to a System.Web.HttpResponse or System.Web.HttpResponseBase output stream or a System.Net.Http.HttpResponseMessage and sets appropriate HTTP response headers (Content-Type and Content-Disposition). Use this method in ASP.NET application to send the spreadsheet to a client browser. File format is resolved from fileName extension. See remarks why httpResponse parameter is of type System.Object.

• C#
• VB.NET

[ComVisible(false)]
public void Save(object httpResponse, string fileName)

<ComVisible(False)>
Public Sub Save(httpResponse As Object, fileName As String)

httpResponse

System.Object

fileName

System.String

This method accepts System.Object rather than HttpResponse, HttpResponseBase or HttpResponseMessage because otherwise GemBox.Document assembly would have a dependency on System.Web or System.Net.Http assemblies and could not be used in applications which target Client Profile .NET Framework.

HttpResponse can be retrieved through the Response property of the HttpApplication, HttpContext, Page or UserControl classes.

For supported file extensions, see remarks for Save(String) method.

System.ArgumentException

Saves the spreadsheet to a System.Web.HttpResponse or System.Web.HttpResponseBase output stream or a System.Net.Http.HttpResponseMessage and sets appropriate HTTP response headers (Content-Type and Content-Disposition). Use this method in ASP.NET application to send the spreadsheet to a client browser. File format is defined with options parameter. See remarks why httpResponse parameter is of type System.Object.

• C#
• VB.NET

[ComVisible(false)]
public void Save(object httpResponse, string fileName, SaveOptions options)

<ComVisible(False)>
Public Sub Save(httpResponse As Object, fileName As String, options As SaveOptions)

httpResponse

System.Object

fileName

System.String

options

SaveOptions

This method accepts System.Object rather than HttpResponse, HttpResponseBase or HttpResponseMessage because otherwise GemBox.Document assembly would have a dependency on System.Web or System.Net.Http assemblies and could not be used in applications which target Client Profile .NET Framework.

HttpResponse can be retrieved through the Response property of the HttpApplication, HttpContext, Page or UserControl classes.

Saves the spreadsheet to a file with the specified path.

• C#
• VB.NET

[ComVisible(true)]
public void Save(string path)

<ComVisible(True)>
Public Sub Save(path As String)

path

System.String

File extension that is extracted from the path is used to create the appropriate SaveOptions derived class instance that is then passed to the Save(String, SaveOptions) method.

Following file extensions are supported:

• Microsoft Excel: .xlsx, xlsm, .xltx, .xltm, .xls, .xlt, .xlsb
• OpenDocument Spreadsheet: .ods, .ots
• Character Separated Values: .csv, .tsv
• HyperText Markup Language: .htm, .html, .mht, .mhtml
• Portable Document Format: .pdf
• XML Paper Specification: .xps
• Image: .bmp, .gif, .jpg, .jpeg, .png, .tif, .tiff, .wdp

System.ArgumentException

System.PlatformNotSupportedException

Saves the spreadsheet to a file with the specified path.

• C#
• VB.NET

[ComVisible(false)]
public void Save(string path, SaveOptions options)

<ComVisible(False)>
Public Sub Save(path As String, options As SaveOptions)

path

System.String

options

SaveOptions

Save the spreadsheet to HTML format using the specified writer.

• C#
• VB.NET

[ComVisible(false)]
public void Save(XmlWriter writer, HtmlSaveOptions options)

<ComVisible(False)>
Public Sub Save(writer As XmlWriter, options As HtmlSaveOptions)

writer

System.Xml.XmlWriter

options

HtmlSaveOptions

Gets a value indicating whether the objects of specified type can be assigned to Value property.

• C#
• VB.NET

public static bool SupportsType(Type type)

Public Shared Function SupportsType(type As Type) As Boolean

type

System.Type

System.Boolean

Currently supported types are:

• System.DBNull
• System.Byte
• System.SByte
• System.Int16
• System.UInt16
• System.Int64
• System.UInt64
• System.UInt32
• System.Int32
• System.Single
• System.Double
• System.Boolean
• System.Char
• System.Text.StringBuilder
• System.Decimal
• System.DateTime
• System.String