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:

• CellFormat
• CellFormula
• CellStyle
• CellText
• CellType
• CellValue

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.