Here is a simple Delphi procedure that creates a new Microsoft Excel file with five tabs (i.e., five worksheets), adds some data to each worksheet, and demonstrates how to align the cell contents horizontally and vertically. After these operations, the file is saved. The most relevant parts of the code are highlighted. Comments are included to explain the code. If you copy and paste the code into your program, be sure to change the form and procedure names to match your setup. For additional Excel automation solutions, see Microsoft Excel Automation with Delphi.
 |
 |
 |
 |
 |
— share —
procedure TForm1.MicrosoftExcelHorizontalVerticalAlignmentCellContents(Sender: TObject);
var
W, NumberOfWorksheetsNeeded, Row, Column: Integer;
ExcelFileName, CellAddress: String;
ExcelApplication, ExcelWorkbook, ExcelWorksheet: Variant;
begin
//be sure ComObj and Variants units are included in the "uses" clause
W := 0;
NumberOfWorksheetsNeeded := 5;
Row := 0;
Column := 0;
ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\excel_file_horizonal_and_vertical_cell_alignment.xlsx'; //replace file name with the name of your file
CellAddress := '';
ExcelApplication := Null;
ExcelWorkbook := Null;
ExcelWorksheet := Null;
try
//create Excel OLE
ExcelApplication := CreateOleObject('Excel.Application');
except
ExcelApplication := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelApplication) = False then
begin
try
ExcelApplication.Visible := True; //set to False if you do not want to see the activity in the background
ExcelApplication.DisplayAlerts := False; //ensures message dialogs do not interrupt the flow of your automation process. May be helpful to set to True during testing and debugging.
//Open Excel Workbook
try
ExcelWorkbook := ExcelApplication.WorkBooks.Add(-4167);
//or
//ExcelWorkbook := ExcelApplication.WorkBooks.Add;
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.add
except
ExcelWorkbook := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelWorkbook) = False then
begin
try
//create the desired number of worksheets in this workbook
//if the default number of worksheets created is less than the desired number
If NumberOfWorksheetsNeeded > ExcelWorkbook.Worksheets.Count then
begin
While ExcelWorkbook.Worksheets.Count < NumberOfWorksheetsNeeded do ExcelWorkbook.WorkSheets.Add(Null,ExcelWorkbook.WorkSheets[ExcelWorkbook.Worksheets.Count],1,-4167);
//or use the code below if you do not care about the order in which the sheets are named
//ExcelWorkbook.WorkSheets.Add(Null,Null,(NumberOfWorksheets-ExcelWorkbook.Worksheets.Count),-4167);
end;
//if the default number of worksheets created is more than the desired number
If NumberOfWorksheetsNeeded < ExcelWorkbook.Worksheets.Count then
begin
While ExcelWorkbook.Worksheets.Count > NumberOfWorksheetsNeeded do ExcelWorkbook.WorkSheets[ExcelWorkbook.Worksheets.Count].Delete;
For W := 1 to ExcelWorkbook.Worksheets.Count do ExcelWorkbook.Worksheets[W].Name := 'Sheet'+IntToStr(W); //renames the sheets
end;
//connect to Excel Worksheet 1 using the ExcelWorkbook handle
ExcelWorksheet := Null;
ExcelWorksheet := ExcelWorkbook.WorkSheets[1]; //[1] specifies the first worksheet
If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
For Row := 1 to 10 do
begin
For Column := 1 to 10 do
begin
CellAddress := ExcelWorksheet.Cells[Row,Column].Address;
CellAddress := StringReplace(CellAddress, '$', '', [rfReplaceAll, rfIgnoreCase]);
ExcelWorksheet.Cells[Row,Column] := CellAddress;
end;
end;
//set the width for columns in the used range
ExcelWorksheet.Usedrange.Columns.ColumnWidth := 15;
//set the height for rows in the used range
ExcelWorksheet.Usedrange.Rows.RowHeight := 30;
//horizontal and vertical align the contents of cell E5 - horizontal = cetner, vertical = middle
ExcelWorksheet.Cells[5,5].HorizontalAlignment := -4108;
ExcelWorksheet.Cells[5,5].VerticalAlignment := -4108;
//horizontal and vertical align the contents of cells B3 and H7 - horizontal = cetner, vertical = middle
ExcelWorksheet.Range['B3,H7'].HorizontalAlignment := -4108;
ExcelWorksheet.Range['B3,H7'].VerticalAlignment := -4108;
//highlight cells B3, E5, and H7
ExcelWorksheet.Range['B3,E5,H7'].Interior.Color := clYellow;
//select cells B3, E5, and H7
ExcelWorksheet.Range['B3,E5,H7'].Select;
end;
//connect to Excel Worksheet 2 using the ExcelWorkbook handle
ExcelWorksheet := Null;
ExcelWorksheet := ExcelWorkbook.WorkSheets[2]; //[2] specifies the first worksheet
If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
For Row := 1 to 10 do
begin
For Column := 1 to 10 do
begin
CellAddress := ExcelWorksheet.Cells[Row,Column].Address;
CellAddress := ExcelWorksheet.Cells[Row,Column].Address;
ExcelWorksheet.Cells[Row,Column] := '='+ExcelWorkbook.Worksheets[1].Name+'!'+CellAddress;
end;
end;
//set the width for columns in the used range
ExcelWorksheet.Usedrange.Columns.ColumnWidth := 15;
//set the height for rows in the used range
ExcelWorksheet.Usedrange.Rows.RowHeight := 30;
//for the used range, align all cell contents - horizontal = cetner, vertical = middle
ExcelWorksheet.Usedrange.Rows.HorizontalAlignment := -4108;
ExcelWorksheet.Usedrange.Columns.VerticalAlignment := -4108;
//select cell A1
ExcelWorksheet.Cells[1,1].Select;
end;
//connect to Excel Worksheet 3 using the ExcelWorkbook handle
ExcelWorksheet := Null;
ExcelWorksheet := ExcelWorkbook.WorkSheets[3]; //[3] specifies the first worksheet
If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
For Row := 1 to 10 do
begin
For Column := 1 to 10 do
begin
CellAddress := ExcelWorksheet.Cells[Row,Column].Address;
ExcelWorksheet.Cells[Row,Column] := '='+ExcelWorkbook.Worksheets[1].Name+'!'+CellAddress;
end;
end;
//set the width for columns in the used range
ExcelWorksheet.Usedrange.Columns.ColumnWidth := 15;
//set the height for rows in the used range
ExcelWorksheet.Usedrange.Rows.RowHeight := 30;
//horizontal and vertical alignment for the used range of column 5 (i.e., column E)
ExcelWorksheet.Usedrange.Columns[5].HorizontalAlignment := -4108;
ExcelWorksheet.Usedrange.Columns[5].VerticalAlignment := -4108;
//highlight the used range of column 5 (i.e., column E)
ExcelWorksheet.Usedrange.Columns[5].Interior.Color := clYellow;
//select the used range of column 5 (i.e., column E)
ExcelWorksheet.Usedrange.Columns[5].Select;
end;
//connect to Excel Worksheet 4 using the ExcelWorkbook handle
ExcelWorksheet := Null;
ExcelWorksheet := ExcelWorkbook.WorkSheets[4]; //[4] specifies the first worksheet
If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
For Row := 1 to 10 do
begin
For Column := 1 to 10 do
begin
CellAddress := ExcelWorksheet.Cells[Row,Column].Address;
ExcelWorksheet.Cells[Row,Column] := '='+ExcelWorkbook.Worksheets[1].Name+'!'+CellAddress;
end;
end;
//set the width for columns in the used range
ExcelWorksheet.Usedrange.Columns.ColumnWidth := 15;
//set the height for rows in the used range
ExcelWorksheet.Usedrange.Rows.RowHeight := 30;
//horizontal and vertical alignment for the used range of columns B, C, and D
ExcelWorksheet.Usedrange.Columns['B:D'].HorizontalAlignment := -4108;
ExcelWorksheet.Usedrange.Columns['B:D'].VerticalAlignment := -4108;
//highlight the used range of columns B, C, and D
ExcelWorksheet.Usedrange.Columns['B:D'].Interior.Color := clYellow;
//select the used range of columns B, C, and D
ExcelWorksheet.Usedrange.Columns['B:D'].Select;
end;
//connect to Excel Worksheet 5 using the ExcelWorkbook handle
ExcelWorksheet := Null;
ExcelWorksheet := ExcelWorkbook.WorkSheets[5]; //[5] specifies the first worksheet
If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
For Row := 1 to 10 do
begin
For Column := 1 to 10 do
begin
CellAddress := ExcelWorksheet.Cells[Row,Column].Address;
ExcelWorksheet.Cells[Row,Column] := '='+ExcelWorkbook.Worksheets[1].Name+'!'+CellAddress;
end;
end;
//set the width for all columns of the worksheet
ExcelWorksheet.Columns.ColumnWidth := 15;
//set the height for all rows of the worksheet
ExcelWorksheet.Rows.RowHeight := 30;
//horizontal and vertical align all cells of the worksheet
ExcelWorksheet.Columns.HorizontalAlignment := -4108;
ExcelWorksheet.Columns.VerticalAlignment := -4108;
//select cell A1
ExcelWorksheet.Range['A1'].Select;
end;
//select Worksheet 1
ExcelWorksheet := ExcelWorkbook.WorkSheets[1];
ExcelWorksheet.Select;
ExcelWorkbook.SaveAs(ExcelFileName);
except
ExcelWorksheet := Null;
//add error/exception handling code as desired
end;
end;
finally
ExcelApplication.Workbooks.Close;
ExcelApplication.DisplayAlerts := True;
ExcelApplication.Quit;
ExcelWorksheet := Unassigned;
ExcelWorkbook := Unassigned;
ExcelApplication := Unassigned;
end;
end;
end;
Microsoft Excel allows a number of different ways in which horizontal alignment and vertical alignment can be done. The tables below provide information on different alignment formats and the value for each.
| Name | Value | Description |
| xlHAlignCenter | -4108 | Center |
| xlHAlignCenterAcrossSelection | 7 | Center across selection |
| xlHAlignDistributed | -4117 | Distribute |
| xlHAlignFill | 5 | Fill |
| xlHAlignGeneral | 1 | Align according to data type |
| xlHAlignJustify | -4130 | Justify |
| xlHAlignLeft | -4131 | Left |
| xlHAlignRight | -4152 | Right |
| Name | Value | Description |
| xlVAlignBottom | -4107 | Bottom |
| xlVAlignCenter | -4108 | Center |
| xlVAlignDistributed | -4117 | Distributed |
| xlVAlignJustify | -4130 | Justify |
| xlVAlignTop | -4160 | Top |
Posted: Saturday, January 6, 2024
Last updated: Wednesday, March 19, 2025
Last updated: Wednesday, March 19, 2025
— share —