Automating Excel - Useful Excel Automation examples

Edraw Office Viewer Component provides a solution for developers to host Excel program in a window or web page. The component offers the New, Open, Save, Print methods. It has also wrapped some common-used excel automation interfaces. More importantly, the component has the ActiveDocument dispatch interface which means the developers can do the Excel Automation with it. Now the Office Viewer Component can supporte seamless integration with Office 97, Office 2000, Office 2003, Office 2007 and Office 2010.

Figure 1: Automating Excel in the Window Form

Methods

boolean ExcelAddWorkSheet([in] long Index)

Adds a new worksheet to an opened Excel file.

boolean ExcelDeleteWorkSheet([in] long Index)

Deletes a new worksheet from an opened Excel file.

boolean ExcelActivateWorkSheet([in] long Index)

Activates the worksheet by the index.

long ExcelGetWorkSheetCount()

Returns the counts of worksheets in the opened workbook.

boolean ExcelSetCellValue([in] long Column, [in] long Row, [in] BSTR Value)

Populates a specified cell with a string value.

BSTR ExcelGetCellValue([in] long Column, [in] long Row)

Returns the contents of the specified cell.

boolean ExcelSetRowHeight([in] long Row, [in] double Height)

Sets the height of the specified rows.

boolean ExcelSetColumnWidth([in] long Column, [in] double Width)

Sets the width of the specified columns.

boolean ExcelDeleteRow([in] long Row)

Deletes the specified row.

boolean ExcelDeleteColumn([in] long Column)

Deletes the specified column.

boolean ExcelInsertRow([in] long Row)

Inserts a new row after the specified row.

boolean ExcelInsertColumn([in] long Column)

Inserts a new column after the specified column.

boolean ExcelInsertPageBreakInRow([in] long Row)

Inserts a page break after the specified row.

boolean ExcelInsertPageBreakInColumn([in] long Column)

Inserts a page break after the specified column.

boolean ExcelCopyToClipboard()

Copies the whole sheet to clipboard.

boolean ExcelPasteStringToWorksheet([in] BSTR bstText)

Inserts data to the worksheet like clipboard.

Event

The following PowerPoint events have been included since the Edraw Office Viewer Component V7.x.

[id(17), helpstring("Occurs when the sheet activates.")]
void SheetActivate();

[id(18), helpstring("Occurs when the sheet deactivate.")]
void SheetDeactivate();

[id(19), helpstring("Occurs when the sheet calculates.")]
void SheetCalculate();

[id(20), helpstring("Occurs when the active sheet changes.")]
void SheetChange();

[id(21), helpstring("Occurs when the new sheet is created.")]
void WorkbookNewSheet();

[id(1), helpstring("Occurs when the component is ready to open document.")]
void NotifyCtrlReady();

[id(2), helpstring("Occurs when the new document is created.")]
void NewDocument();

[id(3), helpstring("Occurs before document is opened or new document added.")]
void BeforeDocumentOpened();

[id(4), helpstring("Occurs when document is opened or new document added.")]
void DocumentOpened();

[id(5), helpstring("Occurs before document is closed.")]
void BeforeDocumentClosed();

[id(6), helpstring("Occurs before document is saved.")]
void BeforeDocumentSaved();

[id(7), helpstring("Occurs before window is right clicked.")]
void WindowBeforeRightClick();

[id(8), helpstring("Occurs before window is double clicked.")]
void WindowBeforeDoubleClick();

[id(9), helpstring("Occurs before selection is changed.")]
void WindowSelectionChange();

[id(10), helpstring("Occurs before document is printed.")]
void DocumentBeforePrint();

Automating Word with VBA and VBScript

Many developers are familiar with the excel automation in VB, C#, VC or Delphi. That is very good. It will help you get started rapidly to write code using word automation capabilities in office viewer component.

Firstly, you can get the Document or Application object by calling the ActiveDocument method.

IDispatch* ActiveDocument();

Returns the Automation interface of the document object.

The method allows you to obtain a reference to the IDispatch interface of the embedded object. From this interface you can automate the object to perform tasks, edit parts of the document, or gather information about what a user has added or removed.

For example, you can create a new worksheet then write the cells:

IDispatch* GetApplication();

Returns the Automation interface of the application.

Word Automation

Embedding Office Viewer Component in VB.NET and Do the Excel Automation

Disable the Save, Print and Copy Hotkey

Do office automation using C#