ExcelFile Class

Namespace:
GemBox.Spreadsheet
Assembly:
GemBox.Spreadsheet.dll

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

public sealed class ExcelFile
Public NotInheritable Class ExcelFile
Inheritance:
System.Object
ExcelFile

Constructors

ExcelFile()

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

public ExcelFile()
Public Sub New
Remarks

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.

Properties

AutomaticFormulaUpdate

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

public bool AutomaticFormulaUpdate { get; set; }
Public Property AutomaticFormulaUpdate As Boolean
Property Value
System.Boolean
Remarks

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.

CalculationOptions

Gets the calculation engine options.

public CalculationOptions CalculationOptions { get; }
Public ReadOnly Property CalculationOptions As CalculationOptions
Property Value
CalculationOptions

The calculation engine options.

CodeName

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

public string CodeName { get; set; }
Public Property CodeName As String
Property Value
System.String

DefinedNames

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

public DefinedNameCollection DefinedNames { get; }
Public ReadOnly Property DefinedNames As DefinedNameCollection
Property Value
DefinedNameCollection

DocumentProperties

Gets DocumentProperties of this ExcelFile.

public DocumentProperties DocumentProperties { get; }
Public ReadOnly Property DocumentProperties As DocumentProperties
Property Value
DocumentProperties

GroupMethodsAffectedCellsLimit

Maximum number of affected cells in group set methods.

public int GroupMethodsAffectedCellsLimit { get; set; }
Public Property GroupMethodsAffectedCellsLimit As Integer
Property Value
System.Int32
Remarks

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).

MaximumDigitWidth

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 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.

public int MaximumDigitWidth { get; set; }
Public Property MaximumDigitWidth As Integer
Property Value
System.Int32

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

Exceptions
System.ArgumentOutOfRangeException

Value is less than zero.

PivotCaches

Gets the collection of PivotCaches defined in this ExcelFile instance.

Currently supported in XLSX only.

public PivotCacheCollection PivotCaches { get; }
Public ReadOnly Property PivotCaches As PivotCacheCollection
Property Value
PivotCacheCollection

The collection of PivotCaches defined in this ExcelFile instance.

Remarks

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.
note

GemBox.Spreadsheet currently supports creating only WorksheetSource.

All other sources are supported through preservation (to correctly round-trip to XLSX format).

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:

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.

note

If calculated PivotField is added/removed in one PivotTable, then it is also added/removed from all other PivotTables which reference the same PivotCache.

Since adding/removing calculated PivotFields means changing the data scheme, which is defined in PivotCache, actual modification is made in PivotCache and propagated to all PivotTables which are referencing it.

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.

Protected

Gets or sets the workbook protection flag.

public bool Protected { get; set; }
Public Property Protected As Boolean
Property Value
System.Boolean
Remarks

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.

See Also

ProtectionSettings

Gets workbook protection properties. Supported in XLSX only.

public WorkbookProtection ProtectionSettings { get; }
Public ReadOnly Property ProtectionSettings As WorkbookProtection
Property Value
WorkbookProtection

Styles

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

public CellStyleCollection Styles { get; }
Public ReadOnly Property Styles As CellStyleCollection
Property Value
CellStyleCollection
Remarks

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.

note

For performance reasons, cell formatting on CellRange is resolved based just on its top-left cell formatting, except borders which are resolved based on corner cells depending on border side.

Setting cell formatting property on CellRange is propagated to each cell in a range.

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

tip

Preferable way to modify formatting property of multiple cells is to get CellRange to which all those cells belong, and use Style property of that range to make the modification.

If modifying multiple formatting properties of a CellRange, without preserving unmodified formatting properties, preferable way is to create new instance of CellStyle, make modifications on it, and assign it to Style property of that range.

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.

Use1904DateSystem

Gets or sets whether 1904 date system is used.

public bool Use1904DateSystem { get; set; }
Public Property Use1904DateSystem As Boolean
Property Value
System.Boolean
Remarks

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

VbaProject

Represents the VbaProject contained in the ExcelFile.

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

public VbaProject VbaProject { get; }
Public ReadOnly Property VbaProject As VbaProject
Property Value
VbaProject

Worksheets

Collection of all worksheets (ExcelWorksheet) in a workbook.

public ExcelWorksheetCollection Worksheets { get; }
Public ReadOnly Property Worksheets As ExcelWorksheetCollection
Property Value
ExcelWorksheetCollection
See Also

Methods

AutoDetectCsvSeparator(Stream)

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

public static char AutoDetectCsvSeparator(Stream stream)
Public Shared Function AutoDetectCsvSeparator(stream As Stream) As Char
Parameters
stream
System.IO.Stream

Input Stream.

Returns
System.Char

Char that is CSV separator. If there's no separators in the input StreamReader returns (char)0, and if there are two with equal number of occurrence, returns first of them.

Remarks

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.

AutoDetectCsvSeparator(StreamReader, Int32, Char[])

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

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
Parameters
sr
System.IO.StreamReader

Input StreamReader.

rowsToTest
System.Int32

Number of rows to test.

possibleSeparators
System.Char[]

Chars that can be CSV separators.

Returns
System.Char

Char that is CSV separator. If there's no separators in the input StreamReader returns (char)0, and if there are two with equal number of occurrence, returns first of them.

Remarks

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.

Calculate()

Calculates formula for every excel cell in all worksheets.

public void Calculate()
Public Sub Calculate

Clone()

Makes a deep clone of this ExcelFile instance.

public ExcelFile Clone()
Public Function Clone As ExcelFile
Returns
ExcelFile

A deep clone of this ExcelFile instance.

ConvertToImageSource(ImageSaveOptions)

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

public ImageSource ConvertToImageSource(ImageSaveOptions options)
Public Function ConvertToImageSource(options As ImageSaveOptions) As ImageSource
Parameters
options
ImageSaveOptions

The options used when converting a single spreadsheet's page to an image.

Returns
System.Windows.Media.ImageSource

A new System.Windows.Media.ImageSource instance created from a single spreadsheet's page specified in options.

See Also

ConvertToXpsDocument(XpsSaveOptions)

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

public XpsDocument ConvertToXpsDocument(XpsSaveOptions options)
Public Function ConvertToXpsDocument(options As XpsSaveOptions) As XpsDocument
Parameters
options
XpsSaveOptions

The options used when converting to XML Paper Specification (XPS) document.

Returns
System.Windows.Xps.Packaging.XpsDocument

A New System.Windows.Xps.Packaging.XpsDocument instance created from this ExcelFile instance.

See Also

GetPaginator()

Gets the workbook paginator.

public ExcelFilePaginator GetPaginator()
Public Function GetPaginator As ExcelFilePaginator
Returns
ExcelFilePaginator

The workbook paginator.

GetPaginator(PaginatorOptions)

Gets the workbook paginator based on the specified options.

public ExcelFilePaginator GetPaginator(PaginatorOptions options)
Public Function GetPaginator(options As PaginatorOptions) As ExcelFilePaginator
Parameters
options
PaginatorOptions

The paginator options used to specify which parts of the workbook to paginate.

Returns
ExcelFilePaginator

The workbook paginator.

Load(Stream)

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.

public static ExcelFile Load(Stream stream)
Public Shared Function Load(stream As Stream) As ExcelFile
Parameters
stream
System.IO.Stream

The stream from which to load a spreadsheet.

Returns
ExcelFile

A loaded spreadsheet.

Load(Stream, LoadOptions)

Loads a spreadsheet from the specified stream.

public static ExcelFile Load(Stream stream, LoadOptions options)
Public Shared Function Load(stream As Stream, options As LoadOptions) As ExcelFile
Parameters
stream
System.IO.Stream

The stream from which to load a spreadsheet.

options
LoadOptions

The loading options which can be used to define settings for load operation.

Returns
ExcelFile

A loaded spreadsheet.

Load(TextReader, CsvLoadOptions)

Loads a spreadsheet from CSV data using the specified reader.

public static ExcelFile Load(TextReader reader, CsvLoadOptions options)
Public Shared Function Load(reader As TextReader, options As CsvLoadOptions) As ExcelFile
Parameters
reader
System.IO.TextReader

The reader which is used to load a spreadsheet.

options
CsvLoadOptions

The CSV loading options which can be used to define settings for load operation.

Returns
ExcelFile

A loaded spreadsheet.

Load(String)

Loads a spreadsheet from a file with the specified path.

public static ExcelFile Load(string path)
Public Shared Function Load(path As String) As ExcelFile
Parameters
path
System.String

The path from which to load a spreadsheet.

Returns
ExcelFile

A loaded spreadsheet.

Remarks

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)

Load(String, LoadOptions)

Loads a spreadsheet from a file with the specified path.

public static ExcelFile Load(string path, LoadOptions options)
Public Shared Function Load(path As String, options As LoadOptions) As ExcelFile
Parameters
path
System.String

The path from which to load a spreadsheet.

options
LoadOptions

The loading options which can be used to define settings for load operation.

Returns
ExcelFile

A loaded spreadsheet.

Print()

Prints the document to the default printer.

public void Print()
Public Sub Print

Print(String)

Prints the document to the specified printer.

public void Print(string printerName)
Public Sub Print(printerName As String)
Parameters
printerName
System.String

Printer name.

Print(String, PrintOptions)

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

public void Print(string printerName, PrintOptions options)
Public Sub Print(printerName As String, options As PrintOptions)
Parameters
printerName
System.String

Printer name.

options
PrintOptions

Print options.

Save(Stream, SaveOptions)

Saves the spreadsheet to the specified stream.

[ComVisible(false)]
public void Save(Stream stream, SaveOptions options)
<ComVisible(False)>
Public Sub Save(stream As Stream, options As SaveOptions)
Parameters
stream
System.IO.Stream

The stream to which to save the spreadsheet.

options
SaveOptions

The saving options which can be used to define settings for save operation.

Save(TextWriter, CsvSaveOptions)

Save the spreadsheet to CSV format using the specified writer.

[ComVisible(false)]
public void Save(TextWriter writer, CsvSaveOptions options)
<ComVisible(False)>
Public Sub Save(writer As TextWriter, options As CsvSaveOptions)
Parameters
writer
System.IO.TextWriter

The writer which is used to save the spreadsheet.

options
CsvSaveOptions

The CSV saving options which can be used to define settings for save operation.

Save(Object, String)

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.

[ComVisible(false)]
public void Save(object httpResponse, string fileName)
<ComVisible(False)>
Public Sub Save(httpResponse As Object, fileName As String)
Parameters
httpResponse
System.Object

System.Web.HttpResponse object that can be retrieved through the Response property of the System.Web.HttpApplication, System.Web.HttpContext, System.Web.UI.Page or System.Web.UI.UserControl classes or System.Web.HttpResponseBase object or System.Net.Http.HttpResponseMessage object.

fileName
System.String

File name that will be presented in client browser when downloading a file.

Remarks
note

Supported only if updating the whole page with a postback and not if doing partial-page updates (when you use UpdatePanel controls to update selected regions of a page).

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.

Exceptions
System.ArgumentException

If file extension is not specified or not supported. If file extension is not recognized, use Save(Object, String, SaveOptions) method overload instead.

Save(Object, String, SaveOptions)

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.

[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)
Parameters
httpResponse
System.Object

System.Web.HttpResponse object that can be retrieved through the Response property of the System.Web.HttpApplication, System.Web.HttpContext, System.Web.UI.Page or System.Web.UI.UserControl classes or System.Web.HttpResponseBase object or System.Net.Http.HttpResponseMessage object.

fileName
System.String

File name that will be presented in client browser when downloading a file.

options
SaveOptions

The saving options which can be used to define settings for save operation.

Remarks
note

Supported only if updating the whole page with a postback and not if doing partial-page updates (when you use UpdatePanel controls to update selected regions of a page).

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.

Save(String)

Saves the spreadsheet to a file with the specified path.

[ComVisible(true)]
public void Save(string path)
<ComVisible(True)>
Public Sub Save(path As String)
Parameters
path
System.String

The path to which to save the spreadsheet.

Remarks

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
Exceptions
System.ArgumentException

If file extension is not specified or not supported. If file extension is not recognized, use Save(String, SaveOptions) method overload instead.

System.PlatformNotSupportedException

File extension is not supported on the current platform.

Save(String, SaveOptions)

Saves the spreadsheet to a file with the specified path.

[ComVisible(false)]
public void Save(string path, SaveOptions options)
<ComVisible(False)>
Public Sub Save(path As String, options As SaveOptions)
Parameters
path
System.String

The path to which to save the spreadsheet.

options
SaveOptions

The saving options which can be used to define settings for save operation.

Save(XmlWriter, HtmlSaveOptions)

Save the spreadsheet to HTML format using the specified writer.

[ComVisible(false)]
public void Save(XmlWriter writer, HtmlSaveOptions options)
<ComVisible(False)>
Public Sub Save(writer As XmlWriter, options As HtmlSaveOptions)
Parameters
writer
System.Xml.XmlWriter

The writer which is used to save the spreadsheet.

options
HtmlSaveOptions

The HTML saving options which can be used to define settings for save operation.

SupportsType(Type)

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

public static bool SupportsType(Type type)
Public Shared Function SupportsType(type As Type) As Boolean
Parameters
type
System.Type

Queried type.

Returns
System.Boolean

true if the specified type is supported; otherwise, false.

Remarks

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

Examples

See Also