The ExcelDoc component provides a simple way to work with Office Open XML spreadsheets.
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.
Navigating a Spreadsheet
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.
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.
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.|
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.|
|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.|
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.|
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.|
|CodePage||The system code page used for Unicode to Multibyte translations.|