Calling Microsoft Excel from the Delphi environment. Accessing Excel from Delphi

This review discusses the basic constructions that allow you to access an Excel workbook from DELPHI.

Organization of access to the EXCEL workbook

To interact with MS excel in the program, you must use the ComObj module
uses ComObj;
and declare a variable for accessing MS excel of the following type:
var Excel: Variant;

Initializing an Excel variable in the simplest case can be done like this:
Excel:= CreateOleObject("Excel.Application");

Creating a new book:
Excel.Workbooks.Add;

Opening an existing book (where path is the path to the file with the xls extension.):
Excel.Workbooks.Open;

Opening an existing read-only workbook:
Excel.Workbooks.Open;

Closing Excel:
Excel.ActiveWorkbook.Close;
Excel.Application.Quit;

Blocking requests (confirmations, notifications) Excel, for example, prohibit the request to save the file:
Excel.DisplayAlerts:=False;

We display Excel on the screen:
Excel.Visible:= True;
or hide:
Excel.Visible:= False;

Printing the contents of the active excel sheet:
Excel.ActiveSheet.PrintOut;

Read/write data in EXCEL

A cell in the current Excel workbook can be accessed as follows:
Excel.Range["B2"]:="Hi!";- to write a value to a cell, or
s:=Excel.Range["B2"]; - for reading,
where B2 is the cell address.

Or using the R1C1 link style:
Excel.Range]:="Hi!";, where is the cell coordinate.

In general, you can assign any value (character, integer, fractional, date) to an Excel cell, while Excel will set the default formatting in the cell.

Cell format in EXCEL

You can select (select) a group of cells for further work as follows:
Excel.Range, Excel.Cells].Select;
or
Excel.Range["A1:C5"].Select;
this will select the area between cell A1 and C5.

After the selection is done, you can install:
1) merging cells
Excel.Selection.MergeCells:=True;
2) word wrap
Excel.Selection.WrapText:=True;
3) horizontal alignment
Excel.Selection.HorizontalAlignment:=3;
when assigning a value of 1, the default alignment is used, with 2 - left alignment, 3 - center, 4 - right.
4) vertical alignment
Excel.Selection.VerticalAlignment:=1;
the values ​​assigned are similar to horizontal alignment.
5) border for cells

When set to 1, cell borders are drawn as thin, solid lines.
In addition, you can specify values ​​for the Borders property, for example, equal to 3. Then only the upper border for the selection block will be set:
Excel.Selection.Borders.LineStyle:=1;
The value of the Borders property specifies a different combination of cell faces.
In both cases, values ​​between 1 and 10 can be used.

Using passwords in EXCEL

Setting a password for an active workbook can be done as follows:
try
// attempt to set a password
Excel.ActiveWorkbook.protect("pass");
except
// actions on unsuccessful attempt to set a password
end;
where pass is the password to be set for the book.

Removing a password from a book is similar, we use the command
Excel.ActiveWorkbook.Unprotect("pass");

Setting and removing a password for the active sheet of an Excel workbook is performed by the commands
Excel.ActiveSheet.protect("pass"); // set password
Excel.ActiveSheet.Unprotect("pass"); // remove password
where pass is the password set to protect the workbook.

Auxiliary operations in EXCEL

Removing rows with a shift up:
Excel.Rows["5:15"].Select;
Excel.Selection.Delete;
when performing these actions, lines 5 to 15 will be deleted.

Setting a Freeze Pane on the Active Excel Sheet
// unpin the area if it was set
Excel.ActiveWindow.FreezePanes:=False;
// select the desired cell, in this case D3
Excel.Range["D3"].Select;
// set area pinning
Excel.ActiveWindow.FreezePanes:=True;

Successful work!

In this article, we will look at the basic constructions that allow you to access an MS Excel workbook from Delphi.

Organization of access to the EXCEL workbook

To interact with MS Excel in the program, you must use the ComObj module

UsesComObj;

and declare a variable for accessing MS Excel of the following type:

Var MsExcel: Variant;

Initializing an Excel variable in the simplest case can be done like this:

MsExcel:= CreateOleObject("Excel.Application");

Creating a new book:

MsExcel.Workbooks.Add;

Opening an existing book (where path is the path to the file with the xls extension.):

MsExcel.Workbooks.Open;

Opening an existing read-only workbook:

MsExcel.Workbooks.Open;

Closing Excel:

MsExcel.ActiveWorkbook.Close; MsExcel.Application.Quit;

Blocking requests (confirmations, notifications) Ms Excel, for example, prohibit the request to save the file:

MsExcel.DisplayAlerts:=False;

We display Excel on the screen:

MsExcel.Visible:= True;

or hide:

MsExcel.Visible:= False;

Printing the contents of the active MS Excel sheet:

MsExcel.ActiveSheet.PrintOut;

Read/write data in EXCEL

A cell in the current Excel workbook can be accessed as follows:

To write a value to a cell:

MsExcel.Range["B2"]:="Hello!";

To read a value from a cell:

S:=MsExcel.Range["B2"];

where B2- cell address.

Or using the R1C1 link style:

MsExcel.Range]:="Hello!";

where - cell coordinate.

In general, an Excel cell can be assigned any value (character, integer, fractional, date), while Ms Excel will set the default formatting in the cell.

Cell format in EXCEL

You can select (select) a group of cells for further work as follows:

MsExcel.Range, MsExcel.Cells].Select; // or MsExcel.Range["A1:C5"].Select;

this will select the area between cell A1 and C5.

After making a selection, you can set cell merging, word wrapping, as well as horizontal and vertical alignment:

// merging cells MsExcel.Selection.MergeCells:=True; // word wrap MsExcel.Selection.WrapText:=True; // horizontal alignment MsExcel.Selection.HorizontalAlignment:=3; // vertical alignment MsExcel.Selection.VerticalAlignment:=1;

The following values ​​are used for vertical and horizontal alignment:

1 - default alignment is used
2 - left alignment
3 - in the center
4 - right.

cell border

When set to 1, cell borders are drawn as thin, solid lines.

In addition, you can specify values ​​for the Borders property, for example, equal to 3. Then only the upper border for the selection block will be set:

MsExcel.Selection.Borders.LineStyle:=1;

The value of the Borders property specifies a different combination of cell faces. In both cases, values ​​between 1 and 10 can be used.

Using passwords in EXCEL

Setting a password for an active workbook can be done as follows:

Try // attempt to set a password MsExcel.ActiveWorkbook.protect("pass"); except // what to do if an attempt to set a password fails end;

where pass- set password for the book.

Removing a password from a book is similar, we use the command

MsExcel.ActiveWorkbook.Unprotect("pass");

where pass

Setting and removing a password for the active sheet of an Excel workbook is performed by the commands

MsExcel.ActiveSheet.protect("pass"); // set password MsExcel.ActiveSheet.Unprotect("pass"); // remove password

where pass- the password set to protect the book.

Auxiliary operations in EXCEL

Removing rows with a shift up:

MsExcel.Rows["5:15"].Select; MsExcel.Selection.;

when performing these actions, lines 5 to 15 will be deleted.

Setting a Freeze Pane on the Active Excel Sheet

// unfreeze the pane if it was set MsExcel.ActiveWindow.FreezePanes:=False; // select the desired cell, in this case D3 MsExcel.Range["D3"].Select; // set pane freeze MsExcel.ActiveWindow.FreezePanes:=True;

Saving the Active Excel Workbook

We reviewed in sect. 6.4.2 basic operations related to books. Now let's move on to operations with the sheets of the book. The worksheet collection is contained in the Worksheets property of the workbook object. This collection is similar in its properties to the previously discussed Workbooks collection. A sheet can be accessed by index or by name. For example, the following statements, when working with COM servers, open and activate the first sheet of the workbook represented by the Excel Workbook 1 object, pass a pointer to this sheet to the Excel Worksheet 1 variable, and activate the sheet, i.e. bring it to the fore in the Excel window:

ExcelWorksheetl:= ExcelWorkbookl.Worksheets as ExcelWorksheet; ExcelWorksheetl.Activate(LOCALE_USER_DEFAULT);

ExcelWorksheetl:= ExcelWorkbookl.Worksheets; ExcelWorksheetl.Activate;

The Worksheets property is also present on the server object. This property applies to the active workbook. So the following statements, when working with COM servers, operate on the active workbook, opening and activating the sheet in it, the name of which (for example, "Sheet1") is given in the Editl window:

Worksheets as ExcelWorksheet; ExcelWorksheetl.Activate(LOCALE USER DEFAULT);

When working with OLE automation servers, similar statements look like this:

ExcelWorksheetl:= ExcelApplicationl.Worksheets; ExcelWorksheetl.Activate;

If the sheet with the given name is not in the workbook, an exception will be thrown. So if there is such a danger, this exception should be caught, for example, as follows:

ExcelWorksheetl:= ExcelApplicationl.

Worksheets as ExcelWorksheet; ExcelWorksheetl.Activate(LOCALE_USER_DEFAULT); except

ShowMessage("We couldn't open sheet "" + Editl.Text + """); end;

You can add a new sheet to a workbook using the Add method of the Worksheets object:

Function Add(Before: OleVariant; After: OleVariant; Count: OleVariant; Type_: OleVariant; lcid: Integer): IDispatch;

The Before or After parameters are the sheet object before or after which the paste is made. It is usually enough to set only one of these parameters and set the other to EmptyParam. If both parameters are equal to EmptyParam, then new sheets are inserted before the currently active sheet. The Count parameter specifies the number of sheets to insert. If this parameter is equal to EmptyParam, then one sheet is inserted. The Type__ parameter determines the type of insertion. When set to EmptyParam , a new empty sheet is inserted.

For example, the following code, when working with COM servers, inserts one new sheet before the active worksheet in the active workbook and passes a pointer to it into the ExcelWorksheetl variable:

EmptyParam, EmptyParam, EmptyParam, EmptyParam, LOCALE_USER_DEFAULT) as ExcelWorksheet;

And the following code inserts two new sheets after the third sheet of the active workbook:

VarAfter, Num: OleVariant; After:= ExcelApplicationl.Worksheets; Num:= 2;

ExcelWorksheetl:= ExcelApplicationl.Worksheets.Add(

EmptyParam,After,Num,EmptyParam, LOCALE_USER_DEFAULT) as ExcelWorksheet;

When working with OLE automation servers, similar statements look like this:

ExcelWorksheetl:= ExcelApplicationl.Worksheets.Add;

ExcelWorksheetl:= ExcelApplicationl.Worksheets.Add(

After:= ExcelApplicationl.Worksheets, Count:= 2);

The name of an inserted or any existing sheet can be changed using the Name property. For example:

ExcelWorksheetl.Name:= "Invoice"; You can delete a sheet from a book using the Delete method:

ExcelWorksheetl.Delete(LOCALE_USER_DEFAULT);

You can print a sheet using the Printout method, which is no different from the similar method described earlier for the book. For example, printing the current page when working with COM servers can be formatted like this:

ExcelWorksheetl:= ExcelApplicationl.ActiveSheet as, ExcelWorksheet; ExcelWorksheetl.Printout(EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LOCALE_USER_DEFAULT);

When working with automation servers, OLE is simpler:

ExcelWorksheetl:= ExcelApplicationl.ActiveSheet; ExcelWorksheetl.Printout;

You can preview a sheet before printing using the PrintPreview method:

Print ProcedurePreview(EnableChanges: OleVariant; leid: Integer);

The EnableChanges parameter specifies whether changes can be made while viewing. For example, the following statement provides a preview of the active sheet of a workbook when working with COM servers:

(ExcelApplicationl.ActiveSheet as ExcelWorksheet).

PrintPreview (true, LOCALE_USER_DEFAULT) ;

For OLE servers, the analogous statement is:

ExcelApplicationl.ActiveSheet;PrintPreview(true);

Hello, in this article I will tell you how to use in your application (program) on Delphi DB in the form MS Excel. Yes yes exactly MS Excel. There is nothing complicated here. First, let's fill in our worksheet in MS Excel. The first row in each column will always take our application as the column name, i.e. it does not take the column name as − A, B, C etc. So in the first line of column A and column B I wrote Full name And Grade respectively for each column. These will be our headings, then below I filled in the data that will be in my database, well, these are the names and grades, write them yourself. So the database is ready. Now let's create a connection to it. the connection is created similarly as in MS Access, so there is nothing complicated here. On the form we have the old components

• TDB Grid
• TADOQuery
• TADOConnection
• TDataSource

How to connect these components, I will tell you quickly, since this has already been said by me.

• TADOQuery c TADOConnection in property - connection
• TDataSource from TADOQuery in property dataset
• TDB Grid c TDataSource in property data source

IN TADOConnection set property LongPromt in False. Looks like a little tweak has been made. Now we come to the direct connection. In property TADOConnection- ConnectionString click on the button " … ", then we have a view window

Click on the button " Build...” and the following window appears

In this window, select the following provider: Microsoft OLE DB Provider for ODBC Drivers and press the button " Next>> and see the following window

In this window, immediately put a pointer to " Use connection string” and press the button “ Assembly", after which a window appears

In this window, go to the Computer Data Source tab, in the list that appears, select - Excel files, or rather, left-click on this period by double-clicking and in the window that appears, specify the path to our excel-books that we created. After that, click on the button " Ok". All connections are ready. I want to add more if you have a file excel, where your table is located in the same directory with the program, then in the component property TADOConnection- ConnectionString the path to your Excel file will be written, erase the path, and leave just the name of your excel file with the extension, and in the property DefaultDataBase the same component, write the name of your excel-file with extension . Then, when you start your application, there will be no errors with an incorrectly specified path to your database. Next in TDB Grid double-click on it and in the window that appears, create 2 rows, these will be our columns. And they are created by clicking in this window on the button " Add New (Ins)". Next, highlighting each line in the FieldName property for the first line, we write - Full name, because in excel-file, I just wrote the name of the column in the first line (for the cell A1 I wrote this), and highlighting the second line that was created in TDB Grid, in the property field name let's write - Grade, since it was I who wrote the name of the column (in the cell A2 I wrote that). All now we can activate our data. To do this, on the event of the main form - OnCreate write the following

As you can see, the same query, only instead of the table name, as we had in MS Access we specify the name of our sheet in excel, enclosing it in square brackets and ending with the sign $ . As you can see, there is nothing complicated. In the next article about the database MS Excel I will tell you how to insert data, edit and so on.

At each of the steps of this model, you can stop and study it for months. If there is a special need to study any additional properties and methods, we will definitely return and study. And now, let's start working in Delphi with workbook sheets.

2. How to activate an Excel workbook sheet?

As you know when creating a blank workbook excel automatically adds 3 blank sheets to this workbook. To work with a specific sheet ( worksheet or simply sheet) this sheet must be activated. One way to activate a workbook sheet excel in Delphi as follows:

resourcestring rsEInvalidSheetIndex = "Invalid index specified for WorkBooks. Sheet activation aborted"; rsEInvalidSheetActivate= "Sheet activation completed with an error"; function ActivateSheet(WBIndex: integer ; SheetName: string ) : boolean ; var i: integer ; begin Result := false ; if WBIndex › MyExcel. workbooks. Count then raise Exception. Create(rsEInvalidSheetIndex) ; try for i := 1 to MyExcel. WorkBooks [WBIndex] . Sheets. Count do if AnsiLowerCase (MyExcel. WorkBooks [ WBIndex] . Sheets . Item [ i] . Name ) = AnsiLowerCase (SheetName) then begin MyExcel. WorkBooks [WBIndex] . Sheets. Item[i] . Activate ; Result := true ; break; end ; except raise Exception. Create(rsEInvalidSheetActivate) ; end ; end ;

Here, the index of the workbook (WBIndex) in the collection is set as function parameters. WorkBooks and sheet name.

If you want, you can activate the same sheet by index in the collection worksheets- at the same time, the function itself is slightly simplified (the condition for checking the sheet name is not required).

Also, if you are sure that the workbook you need is active at the moment, then you can avoid using an extra variable (workbook index WBIndex) and activate the sheet like this:

My Excel. active workbook. Sheets. Item[i] . Activate ;

Now let's take a closer look at the methods used by the object. worksheet.

3. Excel worksheet sheet methods

Now let's take a closer look at the object worksheet.

The figure shows the methods that I have ever used in my work.

Total object worksheet has 30 different methods you can use.

Let's now consider each of the above methods.

Below, as expression sheet should protrude ( worksheet), unless otherwise stated.

Method Calculate

Calculation of a workbook, sheet or range of cells.

It should be noted that the method call Calculate possible not only for a specific worksheet of an Excel workbook. Consider the options for calling the Calculate method.

var MyExcel:OleVariant; My Excel. Calculate

in the below code snippet the active sheet is calculated

My Excel. active workbook. ActiveWorkSheet. Calculate

Calculate all formulas in columns A, B and C:

My Excel. active workbook. ActiveWorkSheet. UsedRange . Columns("A:C") . Calculate

CheckSpelling Method

The method call looks like this:

At the same time, as expression sheet should protrude ( worksheet).

How to check grammar in excel sheet in Delphi?

My Excel. active workbook. ActiveWorkSheet. CheckSpelling(CustomDictionary:= EmptyParam, IgnoreUppercase:=false, AlwaysSuggest:=EmptyParam, SpellLang:=1033)

in this case MS Excel will check the current sheet, including the check of uppercase words. The verification language is Russian. Please note that in order to call the method, we explicitly indicated which parameter to assign which value. Moreover, in order to skip any parameter, we used EmptyParam(empty parameter), which in Delphi is a variable of type OleVariant.

It is clear that such a way of calling methods (with an explicit indication of all parameters) is not entirely convenient for you and is not widely practiced in programming in Delphi , but nevertheless, only in this way and in no other way can you call the methods used in excel .

Save As Method

Saving an Excel worksheet.

Method call:

MyExcel.ActiveWorkBook.ActiveWorkSheet.SaveAs("C\:MyExcelFile.xls")

In this case, the default settings will be used when saving.

If you need to save the Excel file in a different format, then in the parameter file format one of the enumerator values ​​can be used xlFileFormat . Some Meanings xlFileFormat are presented in the table below:

Let's write a small procedure that allows you to save an Excel sheet in various formats:

const xlCSV = 6 ; xlExcel8 = 56 ; xlHtml = 44 ; xlOpenDocumentSpreadsheet = 60 ; xlOpenXMLWorkbook = 51 ; resourcestring rsESaveActiveSheet = "Error saving active worksheet"; procedure SaveAs(const AFileName: TFileName; AFileFormat: integer ) ; try MyExcel. active workbook. ActiveSheet. SaveAs(AFileName, AFileFormat) ; except raise Exception. Create(rsESaveActiveSheet) ; end ; end ;

Delete Method

Deleting an Excel worksheet.

MyExcel.ActiveWorkBook.WorkSheets.Item.Select

Highlights the third sheet of the book

Copy Method

Copying a sheet to another location in the workbook.

Method call:

However, if you use the parameter Before then you should not use After and vice versa. I would like to draw your attention to the fact that it is the SHEET that is indicated, and not the sheet index.

How to copy excel sheet in Delphi?

MyExcel.ActiveWorkBook.WorkSheets.Item.Copy(After:=MyExcel.ActiveWorkBook.WorkSheets.Item)

In this case, the first sheet will be copied and pasted after the third.

Paste Method

Paste the contents of the clipboard into an Excel sheet.

Method call:

How to paste the contents of the clipboard into an Excel sheet in Delphi?

MyExcel.ActiveWorkBook.ActiveSheet.Paste(Destination:=MyExcel.ActiveWorkBook.ActiveSheet.Range("D1:D5"))

In this case, the contents of the buffer will be inserted into column D in rows 1 to 5. It should be noted that if the contents of the buffer do not satisfy the condition, for example, there is only 1 number in the buffer, then an exception is thrown.

Method ChartObjects

Returns an object that represents either a single chart (object ChartObject) or the collection of all diagrams (object ChartObjects) on the sheet.

Method call:

How to get a chart in an excel sheet in Delphi?

MyExcel.ActiveWorkBook.ActiveSheet.ChartObjects

In this case, the first one will be obtained.

Move method

Sheet movement. This method is similar to the . The only difference is that after pasting, the copied sheet is removed from the book.

Here are some of the methods that I have ever used when working with excel in Delphi . In general, it can be said that Delphi can be controlled MS Excel no worse than when working directly with this application, the main thing is not to forget which method or property is responsible for what :). Well, in order not to forget, I created a small MindMap, which I will periodically supplement and update. By clicking on the link, you can see Excel objects, methods used by them, method parameters and their types in accordance with Delphi types. In general, a small graphic cheat sheet for those who like to deal with Excel in Delphi.