Spreadsheet Integrator - Online Help
Available for:
Spreadsheet Integrator
Questions / Feedback?

ExcelDoc Component

Properties   Methods   Events   Configuration Settings   Errors  

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

Syntax

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

CellThe selected cell or range in A1 notation.
CellFormatThe number or date format of the cell.
CellFormulaThe formula of the selected cell.
CellStyleThe style of the cell.
CellTextThe displayed text of the cell.
CellTypeThe type of cell.
CellValueThe cell value.
OverwriteIndicates whether or not the component should overwrite the file when saving.
SheetCountThe number of sheets in the workbook.
SheetIndexThe current sheet index.
SheetNameThe name of the selected sheet.
SheetVisibleWhether the sheet is visible.
TemplateFileThe 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.

ConfigSets or retrieves a configuration setting .
CopyCellStyleCopies a cell style from a template.
CopySheetCopies a sheet to the specified index.
DeleteColumnsDeletes the specified number of columns.
DeleteRowsDeletes the specified number of rows.
DeleteSheetDeletes the selected sheet.
EvaluateFormulaEvaluates the specified formula.
FreezeFreezes rows and columns.
GetColumnWidthGets the column width.
GetRowHeightGets the row height.
InsertColumnsInserts columns at the specified index.
InsertRowsInserts rows at the specified index.
InsertSheetInserts a sheet at the specified index.
InterruptInterrupts the current recalculation operation.
LoadLoads the specified file.
MergeCellsMerges the specified range.
MoveSheetMoves a sheet to the specified index.
RecalculateRecalculates formulas that are out of date.
RecalculateAllRecalculates all formulas in the workbook.
ResetReset the component.
SaveSaves the spreadsheet to disk.
SelectCellsSelects the cells in the specified range.
SetColumnWidthSets the column width.
SetInputStreamThe stream to read the excel document from.
SetOutputStreamThe stream to write the excel document to.
SetRowHeightSets the row height.
UnfreezeUnfreezes all rows and columns.
UnmergeCellsUnmerges 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.

ErrorInformation about errors during data delivery.
FunctionFired 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.

ActiveSheetGets or sets the active sheet.
AutoRecalculateWhether cell values are automatically recalculated.
CloseInputStreamAfterLoadDetermines whether or not the input stream is closed after loading.
CloseOutputStreamAfterSaveDetermines whether or not the output stream is closed after saving.
FrozenColumnsThe number of frozen columns.
FrozenRowsThe number of frozen rows.
MergedRangeCountThe number of merged ranges in the current sheet.
MergedRange[i]The merged range in A1 notation.
RangeRowDelimiterThe delimiter to use to separate row data in a range.
RefDimensionThe range of cells with values.
Use1904DatesWhether to use the 1904 date system.
UseR1C1ReferenceDetermines if R1C1 notation is used in formulas.
CodePageThe system code page used for Unicode to Multibyte translations.

 
 
Copyright (c) 2016 /n software inc. - All rights reserved.
Build 1.0.6094.0