ExcelDoc Component

Properties Methods Events Configuration Settings Errors

The ExcelDoc component provides a simple way to work with Office Open XML spreadsheets.

Syntax

InSpreadsheet.Exceldoc

Remarks

ExcelDoc implements support for Office Open XML spreadsheets used by Excel 2007 and later.

The component provides an easy-to-use API that facilitates reading and modifying spreadsheets. To begin call the Load method to open an existing file, or simply start working with the component without loading a file to create a new spreadsheet. Call the Save method to write the spreadsheet to disk.

There are many operations that are available including MergeCells, UnmergeCells, MoveSheet, and more. Some common operations are discussed below.

Navigating a Spreadsheet

To select an individual cell or a range of cells set the Cell property in A1 notation. After setting Cell the following properties will be populated:

To change or update a cell set any of the above properties after selecting the cell.

The SheetCount property specifies how many sheets are present in the workbook. Set the SheetIndex to navigate to a different sheet.

Working with Rows and Columns

There are several operations that can be performed on rows and columns. InsertRows and InsertColumns can be used to add new rows and columns. DeleteRows and DeleteColumns can be used to remove rows and columns. GetRowHeight, GetColumnWidth, SetRowHeight, and SetColumnWidth allow getting and setting row height and column width.

Rows and columns may also be frozen and unfrozen using the Freeze and Unfreeze methods.

Working with Formulas

Setting and evaluating formulas in the component is designed to be simple. To set a cell's formula the CellType property must first be set to 4 (ctFormulaString). Then set CellFormula to the formula, for instance "A1+B2". Querying CellFormula will return the cell's current formula.

When changing cell values that a formula relies on, the sheet should be recalculated so that all values are up to date. Call Recalculate to update the values.

To evaluate a formula without assigning it to a cell call EvaluateFormula.

The component supports a number of functions that are listed in CellFormula, however it's possible that CellFormula uses an unsupported function. In the case when an unsupported function is encountered the Function event is fired to allow calculation of a result outside of the component.

Property List

The following is the full list of the properties of the component with short descriptions. Click on the links for further details.


Cell	The selected cell or range in A1 notation.
CellFormat	The number or date format of the cell.
CellFormula	The formula of the selected cell.
CellStyle	The style of the cell.
CellText	The displayed text of the cell.
CellType	The type of cell.
CellValue	The cell value.
Overwrite	Indicates whether or not the component should overwrite the file when saving.
SheetCount	The number of sheets in the workbook.
SheetIndex	The current sheet index.
SheetName	The name of the selected sheet.
SheetVisible	Whether the sheet is visible.
TemplateFile	The path to a template file.

Method List

The following is the full list of the methods of the component with short descriptions. Click on the links for further details.


config	Sets or retrieves a configuration setting .
copyCellStyle	Copies a cell style from a template.
copySheet	Copies a sheet to the specified index.
deleteColumns	Deletes the specified number of columns.
deleteRows	Deletes the specified number of rows.
deleteSheet	Deletes the selected sheet.
evaluateFormula	Evaluates the specified formula.
freeze	Freezes rows and columns.
getColumnType	Get column type.
getColumnWidth	Gets the column width.
getRowHeight	Gets the row height.
insertColumns	Inserts columns at the specified index.
insertRows	Inserts rows at the specified index.
insertSheet	Inserts a sheet at the specified index.
interrupt	Interrupts the current recalculation operation.
load	Loads the specified file.
mergeCells	Merges the specified range.
moveSheet	Moves a sheet to the specified index.
recalculate	Recalculates formulas that are out of date.
recalculateAll	Recalculates all formulas in the workbook.
reset	Reset the component.
save	Saves the spreadsheet to disk.
selectCells	Selects the cells in the specified range.
setColumnWidth	Sets the column width.
setInputStream	The stream to read the excel document from.
setOutputStream	The stream to write the excel document to.
setRowHeight	Sets the row height.
unfreeze	Unfreezes all rows and columns.
unmergeCells	Unmerges the specified range.

Event List

The following is the full list of the events fired by the component with short descriptions. Click on the links for further details.


Error	Information about errors during data delivery.
Function	Fired when an unsupported function is encountered.

Configuration Settings

The following is a list of configuration settings for the component with short descriptions. Click on the links for further details.


ActiveSheet	Gets or sets the active sheet.
AutoRecalculate	Whether cell values are automatically recalculated.
CloseInputStreamAfterLoad	Determines whether or not the input stream is closed after loading.
CloseOutputStreamAfterSave	Determines whether or not the output stream is closed after saving.
FrozenColumns	The number of frozen columns.
FrozenRows	The number of frozen rows.
MergedRangeCount	The number of merged ranges in the current sheet.
MergedRange[i]	The merged range in A1 notation.
RangeRowDelimiter	The delimiter to use to separate row data in a range.
RefDimension	The range of cells with values.
Use1904Dates	Whether to use the 1904 date system.
UseR1C1Reference	Determines if R1C1 notation is used in formulas.
GUIAvailable	Tells the component whether or not a message loop is available for processing events.
UseDaemonThreads	Whether threads created by the component are daemon threads.
UseInternalSecurityAPI	Tells the component whether or not to use the system security libraries or an internal implementation.