Working with EXCEL from Delphi. Excel to Delphi. Methods of the WorkSheet object (sheet)
IN this review The basic structures that allow you to access an Excel workbook from DELPHI are discussed.
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 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;
Display 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,
where B2 is the cell address.
Or using the R1C1 link style: Excel.Range]:="Hello!";
, 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
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 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
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 in this case D3
Excel.Range["D3"].Select;
// set the area to be frozen
Excel.ActiveWindow.FreezePanes:=True;
Happy work!
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 requests (confirmations, notifications) from Ms Excel, for example, block a request to save a file:
MsExcel.DisplayAlerts:=False;
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;
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.
where B2 is the cell address.
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.
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.
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; // wrap 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 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 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 the freezing area MsExcel.ActiveWindow.FreezePanes:=True;
Saving the active Excel workbook
This review discusses the basic constructs that allow you to access an Excel workbook from Delphi.
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;
Print 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!"; // write a value to a cell
s:=Excel.Range["b2"]; // reading value from cell
Where b2 is the cell address.
where B2 is the cell address.
Excel.Range]:="Hello!";
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;
// 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 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
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;
To 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;
Many documents are created and stored in electronic format Microsoft tables Excel. Although these tables have the ability to automatic processing document, it is much more pleasant for us, dolphists, to work in a familiar environment, which also has much more developed capabilities. Let's see how to get data from Excel. Naturally, we will place the tabular data in the familiar StringGrid table.
For working with Excel and other programs from the package Microsoft Office needs to be added to the list uses ComObj module:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, Grids, ComObj;
var
Form1: TForm1;
Excel: Variant;
Next, you need to create an Excel object. Excell Application is created empty, without tables, so you need to add at least one workbook. This needs to be done in some kind of handler, for example a button click handler, although you can do it right away in OnCreate of the Form:
Excel:=CreateOleObject("Excel.Application");
Excel.Application.WorkBooks.Add("File_Name.xls");
If an empty workbook is created, the Add method is applied without a parameter - without a file name. Naturally, you can prompt the user to select a file:
with OpenDialog1 do
if Execute then
Excel.Application.WorkBooks.Add(FileName);
For debugging, it is necessary that the Excel table is visible, and it is also better to disable asking questions about saving data when closing:
Excel.Visible:=True; //After debugging, you can comment out this line
Excel.DisplayAlerts:=False;
Immediately create a method for closing the Excel object, otherwise during debugging, and even when the user is working on the computer, so much will be generated invisible processes Excel, dear mother!.. In the OnCloseQuery handler of the Form write:
try
Excel.Quit;
except
end;
CanClose:=True;
Naturally, Excel will be exited, and then the entire application will close. But if we need to continue working with the program after closing the Excel process, then this code is placed in the button click handler. However, in this case it is not enough. Give it a try and you'll see when you look at the list of processes in the Task Manager that our Excel process is alive and well! This happened because it remains associated with the variable that created it (Excel). To actually destroy a process, you need to break this connection. Complete the above code with the line:
Excel:=Unassigned;
And when we click the close button, our Excel will disappear from the list of processes.
Now you need to get the data from Excel. IN Excel columns are called letters, but in Delphi we refer to them as usual, by serial numbers. Please note that since in Delphi the column index comes first in the index, and in the table Excel index rows, then the indices must be located in opposite places. In the button click handler:
with StringGrid1 do
for i:=1 to RowCount-1 do
for j:=1 to ColCount-1 do
Cells:=Excel.WorkSheets.Item["Sheet1"].Cells;
A small warning: if you check the data entry during debugging, then before pressing our button you need to complete the entry into Excel - press Enter. After all, if the cell Excel tables remains in edit mode, we will receive a refusal from Excel.
And further. Data in Excel is addressed starting from 1. An attempt to get the contents of fixed cells fails. Therefore, if necessary, fixed cells in the StringGrid table must be filled in separately.
And you can get the contents of one cell either by specifying the row and column number, or by directly specifying the cell address:
var S1, S2: String;
begin
S1:=Excel.WorkSheets.Item["Sheet1"].Cells;
S2:=Excel.WorkSheets.Item["Sheet1"].Range["F5"];
end;
Variables S1 and S2 will have the same value.
Now in the StringGrid table we have data to process, and we can do with it what we want. You can then transfer the processed data back into an Excel spreadsheet. This is done in a completely similar way, in the click handler of another button:
for i:=1 to Grid.RowCount-1 do
for j:=1 to Grid.ColCount-1 do
Excel.WorkSheets.Item["Sheet1"].Cells:=Grid.Cells;
If these operations are performed with an active Excel sheet, then you can shorten the writing and instead:
Excel.WorkSheets.Item["Sheet1"].Cells
Excel.Cells
Or you can create a variable and assign it the value of the Excel sheet you are working with:
var Sheet: Variant;
S1, S2: String;
begin
Sheet:=Excel.WorkSheets.Item["Sheet1"];
S1:=Sheet.Cells;
S2:=Sheet.Range["F5"];
end;
Just keep in mind that the table can contain not only data directly in the cells, but also formulas. When writing data from our StringGrid table, everything except the actual text being written will be destroyed!
Finally, you need to force the Excel table to save the processed data:
Excel.ActiveWorkbook.SaveAs("File_Name");// Or SaveAs("OpenDialog1.FileName");
You can print the report. Here's how the print function is defined:
function PrintOut(
From: Variant; //Not necessary. The page number from which printing begins.
To: Variant; //Not necessary. The page number on which printing continues.
Copies: Variant; //Not necessary. Number of copies.
Preview: Variant; //Not necessary. Preview ( True or False).
ActivePrinter: Variant; //Not necessary. The name of the active printer.
PrintToFile: Variant; True printing will go to a file.
Collate: Variant //Not necessary. When value True Copies of pages are merged.
): Workbook;
You can use this function as a method of a variable indicating the page - Sheet(Also Excel.ActiveWorkBook or Excel.WorkSheets):
Sheet.PrintOut(1, 1, 1, False, True);
Printing will be done from the first page to the first, one copy, without preview, without specifying a printer - printing goes to a file. You will first be prompted to specify a file name. A file of type is created *.xps. To view it you need special programs.
Naturally, in Delphi you can also perform cell formatting and other operations with an Excel table. I'll add this information a little later. For now, that's enough for the first time.
Working with Excel cell region
I’d like to continue with the fact that reading and writing data from one cell takes quite a lot of time - you’ve probably already noticed. There is a way to speed up this process. To do this, you need to master simple operations of working with the region. Excel cells.The Excel table cell region also has the Variant type and is specified by a rectangle indicating the upper left and lower right cells:
Var Range: Variant;
begin
end;
In particular, a region can consist of one cell:
Range:=Excel.Range, Excel.Cells];
This entry is easier to do by indicating the address as in an Excel table:
Range:=Excel.Range["A1"];
You can also specify a rectangular region if you know the names of the cells. Here is the 4x4 region:
Range:=Excel.Range["A1:D4"];
Here's how to copy a region of 100X100 Excel cells into a StringGrid table:
var Range: Variant;
i, j: Integer;
begin
Range:=Excel.Range, Excel.Cells];
with StringGrid1 do
for i:=1 to 100 do
for j:=1 to 100 do
Cells:=Range.Cells;
end;
That's all! On my computer, this operation of copying a region of 100x100 Excel cells into a StringGrid table lasts about 300 ms, which is 2 orders of magnitude faster than reading and writing one cell at a time.
And, for example, the operation of entering a single value into all cells of a region is even simpler. Let’s add the word “Hello” to our above-defined 100x100 region:
Excel.Range, Excel.Cells]:="Hello";
Region cleaning is performed using the Clear method:
Excel.Range, Excel.Cells].Clear;
We looked at in Sect. 6.4.2 basic operations related to books. Now let's move on to operations with book sheets. The collection of sheets is contained in the Worksheets property of the book object. This collection is similar in properties to the Workbooks collection discussed earlier. 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 Excel object Workbook 1, pass a pointer to this sheet to the Excel variable Worksheet 1 and activate the sheet, i.e. highlight it in the Excel window:
ExcelWorksheetl:= ExcelWorkbookl.Worksheets as ExcelWorksheet; ExcelWorksheetl.Activate(LOCALE_USER_DEFAULT);
ExcelWorksheetl:= ExcelWorkbookl.Worksheets; ExcelWorksheetl.Activate;
The Worksheets property also exists on the server object. This property applies to the active workbook. So the following statements, when working with COM servers, perform operations on the active workbook by opening and activating a sheet in it, the name of which (for example, “Sheet1”) is specified in the Editl editing 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 a sheet with given name is not in the book, 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 were unable to open the sheet "" + Editl.Text + """); end;
Add new leaf into 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 insertion occurs. Usually it is 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 current 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 insertion type. When set to EmptyParam, a new empty sheet is inserted.
For example, the following code, when working with COM servers, inserts one new worksheet before the active worksheet of the active workbook and passes a pointer to it to 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:
Var After, 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 the inserted sheet or any existing sheet can be changed using the Name property. For example:
ExcelWorksheetl.Name:= "Invoice"; You can delete a sheet from a workbook 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 a book. For example, printing current page when working with COM servers, it can be designed like this:
ExcelWorksheetl:= ExcelApplicationl.ActiveSheet as, ExcelWorksheet; ExcelWorksheetl.Printout(EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LOCALE_USER_DEFAULT);
When working with OLE automation servers, it turns out simpler:
ExcelWorksheetl:= ExcelApplicationl.ActiveSheet; ExcelWorksheetl.Printout;
You can preview a sheet before printing using the PrintPreview method:
Procedure PrintPreview(EnableChanges: OleVariant; leid: Integer);
The EnableChanges parameter specifies whether changes can be made while viewing. For example, the following statement provides when working with COM servers preview active sheet of the book:
(ExcelApplicationl.ActiveSheet as ExcelWorksheet).
PrintPreview (true, LOCALE_USER_DEFAULT) ;
For OLE servers, a similar statement looks like:
ExcelApplicationl.ActiveSheet;PrintPreview(true);
