Communication with MS Excel in Delphi with help OLE.
Dear colleagues!
Sooner or later, we all face the task of exchanging data with the applications of the package. MS office. One of them is MS Excel. And it is about the 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 if MS Excel on the user's computer in principle.
To do this, we use the function CLSIDFromProgID, which is searched in the registry CLSID for transmitted ProgID:
Help from MSDN: Method CLSIDFromProgID
Options:
pszProgID: POleStr— String with object name
clsid: TCLSID— Pointer to structure TGUID to which the found object is passed;
Returns:
HRESULT- Result, which can take values:
S_OK— the object is found;
CO_E_CLASSSTRING— Registered CLSID for ProgID is invalid;
REGDB_E_WRITEREGDB- Write error CLSID to the register.
From the above results, we need S_OK.
Let's write a function to determine the presence excel at the user:
Function IsXlsInstall: boolean; var CLSID: TCLSID; begin Result:= (CLSIDFromProgID("Excel.Application", CLSID) = S_OK); end;
If a excel installed, then 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 we have a task to receive data from a running excel, then we should use only the first option, in other cases we try to connect and if it doesn’t work, then we create it.
Let's write 2 functions to connect Xls Connect 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"); Result:= true; except end; end; procedure XlsStart; begin FXlsApp:= CreateOleObject("Excel.Application"); end;
Now you can add a button, on click of which we will connect to MS Excel using the written functions:
Procedure btnConnectClick(Sender: TObject); begin if not IsXlsInstall then raise Exception.Create("MS Excel application not found on this computer!"); if not XlsConnect then XlsStart; FXlsApp.Visible:= True; end;
Default window excel starts in background. Line FXlsApp.Visible:= True; makes a background window excel visible.
Window excel 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 into it.
FXlsApp.Cells := "Test string";
Where row is the row index, and Col is the index of a column that starts with one.
FXlsApp.Range["A1"] := "Cell A1";
Where Range is an array of cells, and A1- accustomed to excel cell coordinates.
A range can be specified as coordinates. For example, code
FXlsApp.Range["A3:A10"] := 5;
will fill with the number 5 all cells with A3 on A10, and the code
FXlsApp.Range["A3:A10"].Interior.Color:= clMoneyGreen;
will highlight the same range in light green.
AT reverse side, that is, to obtain data from excel, works the same way. Line
ShowMessage(FXlsApp.Cells);
Will display a message with the contents of the cell with 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 active workbook is the current book.
And close the app excel command:
FXlsApp.Quit;
How do you understand this control 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 know. To do this, perform the following steps:
1. Launch excel and create an empty workbook.
2. Run the "Record macro" command, by default the name of the macro will be "Macro1". (AT different versions excel given command located in different menu items).
3. Select a range of cells and press the "Merge and Center" button.
4. Stop macro recording.
5. We call the list of macros and select our 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 performed:
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- Wrap text in words - disabled.
Orientation = 0— Orientation 0 degrees.
AddIndent = False- Use auto indent on/off.
IndentLevel = 0- Indent level to 0.
ShrinkToFit = False— Shrink text to fit column on/off.
ReadingOrder = xlContext— Reading order by context.
MergeCells = False- Merged cells on/off
end with— End of the section for working 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;
Merge cells by setting a property. Or using the method:
FXlsApp.Selection.Merge;
In this way, you can get the code for almost any necessary manipulation.
And if some property or method raises questions, then you can use the help on MSDN.
Pay attention to the peculiarity of working with arrays in VBA. Indexes in arrays in Delphi turn into square brackets, while in VBA they will be in rounds. And the code in Delphi
FXlsApp.Range["B5:C8"].Select;
in VBA will look like
Range("D5:H14").Select;
Below is a small FAQ on the issue of interaction with excel from Delphi
How to define constant values in Excel for use in Delphi?
In editor VBA put a stop point opposite the constant of interest. Click execute and when the execution stops, point to the constant of interest:
How to disable message output 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 in print preview?
FXlsApp.ActiveWindow.SelectedSheets.PrintPreview;
How to make text bold in cells?
VarRow: integer; // String index Col: integer; // Cell index TextSelStart: integer; // Starting at character TextSelLength: integer; // Number of selected characters begin FXlsApp.Cells.Characters(TextSelStart, TextSelLength).Font.Bold:= True; end;
How to autofit row height for glued cell?
merge_area: variant; cell_width, cells_width, i: integer begin // Store 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 in the merged range cells_width:= cells_width + merge_area.Columns[i].ColumnWidth; // Merge cells merge_area.UnMerge; // Set the width of the cell of interest to the total width FXlsApp.Range["D5"].ColumnWidth:= cells_width; // Call standard method autofit row height FXlsApp.Rows.EntireRow.AutoFit; // Return the original width of the cell of interest FXlsApp.Range["D5"].ColumnWidth:= cell_width; // Merge back the range merge_area.Merge; end;
How to get used range of cells?
Result:= exApp.ActiveSheet.UsedRange;
How to get column letter by index?
Use Math; *** function ColIdxToStr(const Col: integer): string const CharsCount: integer = 26; Offset: integer = 64; varRank: 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); Col:= Col - Trunc(Power(CharsCount,Rank)) * Tmp; end; end;
AT this review the basic constructions that allow accessing an Excel workbook from DELPHI are considered.
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 workbook (where path is the path to the file with 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) of Excel, for example, prohibit a request to save a 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"]:="Hello!";- to write a value to a cell, or s:=Excel.Range["B2"]; - for reading, where B2 is the address of the cell.
Or using the R1C1 link style:
Excel.Range]:="Hi!";, where is the cell coordinate.
Generally, cell in Excel you can assign any value (character, integer, fractional, date) 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) cell merging 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 Excel.Selection.Borders.LineStyle:=1; 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 different combination cell edges. 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 when failed attempt Set 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 excel books produced by teams
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 pinned area on 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!
Content-Disposition: form-data; name="sort" 50
uses ComObj;
var Excel: Variant;
Creating a new book:
Excel.Workbooks.Add;
Excel.Workbooks.Open;
Closing Excel:
Excel.ActiveWorkbook.Close;
Excel.Application.Quit;
Excel.DisplayAlerts:=False;
We display Excel on the screen:
Excel.Visible:= True;
or hide:
Excel.Visible:= False;
Excel.ActiveSheet.PrintOut;
Read/write data in EXCEL
where B2 is the cell address.
Cell format in EXCEL
or
1) merging cells
2) word wrap
5) border for cells
Excel.Selection.Borders.LineStyle:=1;
Using passwords in EXCEL
try
except
end;
where pass is the password set to protect the workbook.
Excel.Rows["5:15"].Select;
Excel.Selection.Delete;
Excel.Range["D3"].Select;
Successful work!
Source:
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) of Excel, for example, prohibit a request to save a 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
Excel.Selection.Borders.LineStyle:=1;
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");
where pass is the password set to protect the workbook.
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;
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, and 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;
For vertical and horizontal alignment the following values are used:
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
