Menu
homeInstructions

Delphi find out the name of a sheet in excel. Working with EXCEL from Delphi

Exchange data with MS Excel V Delphi with help OLE.

Dear colleagues!

We all sooner or later face the task of exchanging data with package applications MS Office. One of them is MS Excel. And specifically about interaction with this product MS Office will be discussed in this article.

One way to interact Delphi c MS Excel- is to connect to it as OLE object.

So.
First of all, to work with MS Excel And OLE add to section Uses modules ComObj And ActiveX.

And the first thing we need to check is whether it is installed MS Excel on the user's computer in principle.
To do this, we will use the function CLSIDFromProgID, which searches the registry CLSID for the transmitted ProgID:
Help from MSDN: Method CLSIDFromProgID
Options:
pszProgID: PoleStr— A string with the name of the object
clsid: TCLSID— Pointer to structure TGUID to which the found object is transferred;
Returns:
HRESULT— Result, which can take the following values:
S_OK— object found;
CO_E_CLASSSTRING— Registered CLSID For ProgID is invalid;
REGDB_E_WRITEREGDB— Write error CLSID to the register.
From the listed results we need S_OK.
Let's write a function to determine the presence Excel from the user:

Function IsXlsInstall: boolean; var CLSID: TCLSID; begin Result:= (CLSIDFromProgID("Excel.Application", CLSID) = S_OK); end;

If Excel installed, then let's connect to it. You can do this in two ways: GetActiveOleObject— Get a link to an already running instance Excel or CreateOleObject— Create a new instance Excel.
If our task is to receive data from a running Excel, then we should use only the first option, in other cases we try to connect and if that doesn’t work, then we create it.
Let's write 2 functions to connect XlsConnect and launching a new XlsStart:
Let's add a variable FXlsApp with type Variant, which will contain a reference to the object Excel.

Private FXlsApp: variant; *** function XlsConnect: boolean; begin Result:= False;

try FXlsApp:= GetActiveOleObject("Excel.Application"); MS Excel Result:= True;

except end; end; procedure XlsStart; begin FXlsApp:= CreateOleObject("Excel.Application"); end; Now you can add a button, when clicked we will connect to using the written functions:

Default window Excel starts in background. Line FXlsApp.Visible:= True; makes a background window Excel visible.

Window Excel it starts empty and needs to be added to it workbook. This is done using the method WorkBooks.Add which adds new book or opens a previously saved one if you specify the path to the file.
Let's add a procedure that will do this:

Procedure XWorkbookAdd(const FilePath: string = ""); begin FXlsApp.WorkBooks.Add(FilePath); end;

The book has been added, now let's try to write something in it.

FXlsApp.Cells := "Test string";

Where Row is the row index, and Col— column index that starts with one.

FXlsApp.Range["A1"] := "Cell A1";

Where Range is an array of cells, and A1- familiar to Excel cell coordinates.
A range can be specified as coordinates. For example, code

FXlsApp.Range["A3:A10"] := 5;

will fill all cells with the number 5 A3 By A10, and the code

FXlsApp.Range["A3:A10"].Interior.Color:= clMoneyGreen;

will highlight the same range in light green.
IN reverse side, that is, to obtain data from Excel, works the same. Line

ShowMessage(FXlsApp.Cells);

Will display a message with the contents of the cell with the coordinates: Row=5, Column=1.

After we have performed the necessary manipulations with Excel, we can save the resulting book to a file with the following command:

FXlsApp.ActiveWorkbook.SaveAs("C:\Test.xlsx");

Where ActiveWorkbook- current book.
And close the application Excel command:

FXlsApp.Quit;

How do you understand these control options? Excel from Delphi are not limited. And there is one fairly simple way to find out how to perform the necessary action with Excel from Delphi.
It's called Macros.

Let's imagine that we need to merge several cells into one and we don't know how to do it. But we want to find out. To do this, we perform the following steps:
1. Launch Excel and create an empty book.
2. Run the “Record Macro” command; by default, the name of the macro will be “Macro1”. (IN different versions Excel this command located in different menu items).
3. Select a certain range of cells and click the “Merge and Place in Center” button.
4. Stop recording the macro.
5. Call up the list of macros and select your recorded macro there.
6. Click the “Change” button
The editor starts Microsoft Visual Basic for Application in which we see the code of the actions taken:

Sub Macro1() " " Macro1 Macro " With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Selection .Merge End Sub

Let's take a closer look at what he wrote to us here:
With Selection— For the selected range of cells, set the properties:
HorizontalAlignment = xlCenter — Horizontal orientation= centered.
VerticalAlignment = xlBottom— Vertical orientation — along the bottom edge.
WrapText = False— Text wrapping by words is disabled.
Orientation = 0— Orientation 0 degrees.
AddIndent = False— Using automatic indentation on/off.
IndentLevel = 0— Indent level 0.
ShrinkToFit = False— Compress text to fit column on/off.
ReadingOrder = xlContext— Reading order according to context.
MergeCells = False— Merged cells on/off
End With— End of the section of work with the selected range.
Selection.Merge— Merge the selected range.

Now let's try to merge cells from Delphi:

We select the range we need.

FXlsApp.Selection.MergeCells:= True;

We merge cells by setting the property. Or using the method:

FXlsApp.Selection.Merge;

In this way you can obtain code for almost any necessary manipulation.
And if some property or method raises questions, you can use the help on MSDN.

Please note the peculiarity of working with arrays in VBA. Indexes in arrays in Delphi are wrapped in square brackets, while in VBA they will be in round ones. And the code in Delphi

FXlsApp.Range["B5:C8"].Select;

V VBA will look like

Range("D5:H14").Select;

Below I will give a small FAQ on the issue of interaction with Excel from Delphi

How to define constant values ​​in Excel for use in Delphi?

In the editor VBA We set a stopping point opposite the constant of interest. Click execute and when execution stops, point to the constant of interest:

How to disable message outputs in Excel?

FXlsApp.DisplayAlerts:= False;

How to get a list of books from Excel?

For i:= 1 to FXlsApp.Workbooks.Count do ListBox1.Items.Add(FXlsApp.Workbooks.Item[i].Name);

How to disable grid display?

FXlsApp.ActiveWindow.DisplayGridlines:= False;

How to display the current sheet as a print preview?

FXlsApp.ActiveWindow.SelectedSheets.PrintPreview;

How to make some text in cells bold?

Var Row: integer; // String index Col: integer; // Cell index TextSelStart: integer; // Starting from the character TextSelLength: integer; // Number of selected characters begin FXlsApp.Cells.Characters(TextSelStart, TextSelLength).Font.Bold:= True; end;

How to auto-fit row height for a glued cell?

Var merge_area: variant; cell_width, cells_width, i: integer begin // Save the range of merged cells into a variable merge_area:= FXlsApp.Range["D5"].MergeArea;// Save the width of the cell for which we will select the height cell_width:= FXlsApp.Range["D5"].ColumnWidth;

cells_width:= 0;

for i:= 1 to merge_area.Columns.Count do // Get the total width of all columns of the merged range cells_width:= cells_width + merge_area.Columns[i].ColumnWidth;

// Unmerge the cells merge_area.UnMerge;

// Set the width of the cell of interest equal to the total width FXlsApp.Range["D5"].ColumnWidth:= cells_width;

// Call Delphi standard method MS Excel autofit row height FXlsApp.Rows.EntireRow.AutoFit; MS Excel// Return the original width of the cell of interest FXlsApp.Range["D5"].ColumnWidth:= cell_width; // Glue the range back together merge_area.Merge; end;, How to get the used cell range?, Result:= exApp.ActiveSheet.UsedRange; How to get column letter by index? Uses Math; *** function ColIdxToStr(const Col: integer): string const CharsCount: integer = 26; Offset: integer = 64; var Rank: byte; Col, Tmp: integer; begin Result:= ""; while Col > 0 do begin Rank:= 0; Tmp:=Col; while Tmp > CharsCount do begin Tmp:= Ceil(Tmp / CharsCount - 1);

  • Inc(Rank);
  • end;
  • Result:= Result + Chr(Tmp + Offset);
  • TDataSource

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

  • end; c Result:= Result + Chr(Tmp + Offset); in property - Connection
  • TDataSource With end; in property DataSet
  • Inc(Rank); c TDataSource in property DataSource

IN Result:= Result + Chr(Tmp + Offset); set the property LongPromt V False. It seems small setup done. Now we come to the direct connection. In property Result:= Result + Chr(Tmp + Offset);- 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 place a pointer to “ Use connection string" and click on 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 date double click and in the window that appears, indicate the path to our Excel-the book we created. After that, click on the button “ Ok". We have all the connections ready. I would like 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 Result:= Result + Chr(Tmp + Offset);- ConnectionString the path to your Excel file will be registered, erase the path and leave just your name 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 launch your application, there will be no errors with an incorrectly specified path to your database. Next in Inc(Rank); Let's double-click on it and create 2 rows in the window that appears, these will be our columns. And they are created by clicking on the button “ Add New (Ins)". Next, selecting each line in the FieldName property, for the first line we will write - Uses Math; *** function ColIdxToStr(const Col: integer): string const CharsCount: integer = 26;, since in Excel-file, this is exactly how I wrote the column name in the first line (for the cell A1 I wrote it like this), and by highlighting the second line that was created in Inc(Rank);, in property FieldName let's write - Col, Tmp: integer; begin Result:= "";, since it was I who wrote this column name (in the cell A2 I wrote it that way). Now we can activate our data. For this event main form — OnCreate let's write the following

As you can see, the same query, only instead of the name of the table, as we had in Tmp:=Col; we indicate the name of our sheet in Excel, enclosing it in square brackets and ending with $ . As you can see, there is nothing complicated. In the next article about the database MS Excel I'll tell you how to insert data, edit, and so on.

I would like to note that if you are a true fan of football, and especially the English championship, which is on this moment is one of the strongest, then you will probably be very interested in reading about the football club

IN this review The basic constructions that allow you to access an Excel workbook from Delphi are discussed.

Organizing access to the EXCEL book

To interact with MS Excel in a program, you must use the ComObj module and declare a variable to access MS Excel variant type.

uses ComObj;
var Excel: Variant;

In the simplest case, you can initialize an Excel variable 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;

To open an existing workbook for read-only:

Excel.Workbooks.Open;

Closing Excel:

Excel.ActiveWorkbook.Close;
Excel.Application.Quit;

Blocking Excel requests (confirmations, notifications), for example, blocking a request to save a file:

Excel.DisplayAlerts:=False;

Show or hide Excel on the screen:

Excel.Visible:= True;
Excel.Visible:= False;

Printing the contents of the active Excel worksheet:

Excel.ActiveSheet.PrintOut;

Reading/writing data in EXCEL

You can access a cell in the current Excel workbook as follows:

Excel.Range["b2"]:="Hello!"; // write a value to a cell
s:=Excel.Range["b2"]; // reading value from cell

Where b2 is the cell address.

Or using the R1C1 link style:

Excel.Range]:="Hello!";

Where is the cell coordinate.

At all, Excel cell You can assign any value (character, integer, fractional, date) and Excel will set the default formatting in the cell.

Format cells in EXCEL

You can select (select) a group of cells for subsequent work like this:

Excel.Range, Excel.Cells].Select;
// either
Excel.Range["A1:C5"].Select;

In this case, the area located between cells A1 and C5 will be selected.

After making the selection, you can set:
1) Merging cells:

Excel.Selection.MergeCells:=True;

2) Word wrapping:

Excel.Selection.WrapText:=True;

3) Horizontal alignment:

Excel.Selection.HorizontalAlignment:=3;

When set to 1, the default alignment is used, when set to 2, alignment is left, 3 is centered, and 4 is right. 4) Vertical alignment

Excel.Selection.VerticalAlignment:=1;

The values ​​assigned are the same as horizontal alignment.
5) Border for cells:

When set to 1, cell borders are drawn with 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 different combination cell edges.
In both cases, you can use values ​​in the range from 1 to 10.

Using passwords in EXCEL

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

try
// attempt to set a password
Excel.ActiveWorkbook.protect("pass");
except
// actions when unsuccessful attempt Set password
end;

Where pass is the password to set for the book.

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

Excel.ActiveWorkbook.Unprotect("pass");

Setting or removing a password for the active sheet Excel workbooks produced by teams

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

Where pass is the password set to protect the book.

Auxiliary operations in EXCEL

Deleting rows with an upward shift (when performing these actions, rows 5 to 15 will be deleted):

Excel.Rows["5:15"].Select;
Excel.Selection.Delete;

Setting an area to be pinned to the active Excel sheet:

// unfreeze the area if it was set
Excel.ActiveWindow.FreezePanes:=False;
// select the desired cell in in this case D3
Excel.Range["D3"].Select;
// set the area to be frozen
Excel.ActiveWindow.FreezePanes:=True;


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

Organizing access to the EXCEL book

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

In the simplest case, initializing an Excel variable 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;

To open an existing workbook as read-only:
Excel.Workbooks.Open;

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

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

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

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

Reading/writing data in EXCEL

You can access a cell in the current Excel workbook as follows:
Excel.Range["B2"]:="Hello!";- to write a value to a cell or
s:=Excel.Range["B2"];
- for reading,

Or using the R1C1 link style:
where B2 is the cell address., where is the cell coordinate.

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

Format cells in EXCEL

You can select (select) a group of cells for subsequent work like this:
Excel.Range, Excel.Cells].Select;
or
Excel.Range["A1:C5"].Select;
in this case, the area located between cells A1 and C5 will be selected.

After making the selection, you can set:
1) merging cells
Excel.Selection.MergeCells:=True;
2) word wrapping
Excel.Selection.WrapText:=True;
3) horizontal alignment
Excel.Selection.HorizontalAlignment:=3;
When set to 1, the default alignment is used, when set to 2, alignment is left, 3 is centered, and 4 is right.
4) vertical alignment
Excel.Selection.VerticalAlignment:=1;
the values ​​assigned are the same as horizontal alignment.
5) border for cells

When set to 1, cell borders are drawn with 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 edges.
In both cases, you can use values ​​in the range from 1 to 10.

Using passwords in EXCEL

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

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

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

Auxiliary operations in EXCEL

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

Set a freezing area on the active Excel worksheet
// unfreeze the area if it was set
Excel.ActiveWindow.FreezePanes:=False;
// select the desired cell, in this case D3
Excel.Range["D3"].Select;
// set the area to be frozen
Excel.ActiveWindow.FreezePanes:=True;

Good luck!

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

Organizing access to the EXCEL book

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

Uses ComObj;

and declare a variable to access MS Excel of the following type:

Var MsExcel: Variant;

In the simplest case, initializing an Excel variable 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;

To open an existing workbook for read-only:

MsExcel.Workbooks.Open;

Closing Excel:

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

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

MsExcel.DisplayAlerts:=False;

Displaying Excel on the screen:

MsExcel.Visible:= True;

or hide:

MsExcel.Visible:= False;

Printing the contents of the active MS Excel sheet:

MsExcel.ActiveSheet.PrintOut;

Reading/writing data in EXCEL

You can access a cell in the current Excel workbook 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, you can assign any value to an Excel cell (character, integer, fractional, date), and Ms Excel will set the default formatting in the cell.

Format cells in EXCEL

You can select (select) a group of cells for subsequent work like this:

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

in this case, the area located between cells A1 and C5 will be selected.

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

// merging cells MsExcel.Selection.MergeCells:=True; // wrapping according to MsExcel.Selection.WrapText:=True; // horizontal alignment MsExcel.Selection.HorizontalAlignment:=3; // vertical alignment MsExcel.Selection.VerticalAlignment:=1;

For vertical and horizontal alignment the following values ​​are used:

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

Cell Border

When set to 1, cell borders are drawn with 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 edges. In both cases, you can use values ​​in the range from 1 to 10.

Using passwords in EXCEL

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

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

Where pass- set a password for the book.

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

MsExcel.ActiveWorkbook.Unprotect("pass");

Where pass

Setting and removing a password for the active sheet of an Excel workbook is done using the commands

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

Where pass- password set to protect the book.

Auxiliary operations in EXCEL

Removing rows with shift up:

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

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

Set a freezing area on the active Excel worksheet

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

Saving an active Excel workbook