 |
 |
| Physiology? | Figures & Illustrations | Test Questions | Daily Quiz | Calculators | Physiology Tutor | Glossary |
 |
 |
 |
 |
 |
 |
Delphi Code - Microsoft Excel Cell Formatting - Text Format
Here is a simple Delphi procedure that opens a new Microsoft Excel file (i.e., Excel workbook) and demonstrates how to set the number format of cells to text format. A few examples are also provided to show the use of Excel text() function. The file is then 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. We recommend that you run the code and open and examine the Excel file generated. For additional Excel automation solutions, see Microsoft Excel Automation with Delphi.
procedure TForm1.MicrosoftExcelCellFormattingTextFormat(Sender: TObject);
var
ExcelFileName: String;
ExcelApplication, ExcelWorkbook, ExcelWorksheet: Variant;
begin
//be sure ComObj and Variants units are included in the "uses" clause
ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\excel_file_cell_formatting_text.xlsx'; //replace file name with the name of your file
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);
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.add
//https://docs.microsoft.com/en-us/office/vba/api/excel.xlsheettype
except
ExcelWorkbook := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelWorkbook) = False then
begin
//connect to Excel Worksheet using either the ExcelApplication or ExcelWorkbook handle
try
ExcelWorksheet := ExcelWorkbook.WorkSheets[1]; //[1] specifies the first worksheet
//reference
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Worksheets.Item
except
ExcelWorksheet := Null;
//add error/exception handling code as desired
end;
If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
//add text to A1 cell [row,column]
ExcelWorksheet.Cells[1,1] := 'Microsoft Excel - Cell Formatting - Text';
//change the font size in A1 cell
ExcelWorksheet.Cells[1,1].Font.Size := 20;
//make A1 cell font bold
ExcelWorksheet.Cells[1,1].Font.Bold := True;
//change font color to blue
ExcelWorksheet.Cells[1,1].Font.Color := clBlue;
//add text to A3 cell [row,column]
ExcelWorksheet.Cells[3,1] := 'Hold your mouse over cells with numbers to see comments.';
//add text to A5 cell [row,column]
ExcelWorksheet.Cells[5,1] := 'Text Format';
ExcelWorksheet.Cells[5,1].Font.Bold := True;
ExcelWorksheet.Cells[5,1].Interior.Color := clAqua;
//add text to B5 cell [row,column]
ExcelWorksheet.Cells[5,2] := 'Format Code';
ExcelWorksheet.Cells[5,2].Font.Bold := True;
ExcelWorksheet.Cells[5,2].Interior.Color := clAqua;
//add text to C5 cell [row,column]
ExcelWorksheet.Cells[5,3] := 'Format Description';
ExcelWorksheet.Cells[5,3].Font.Bold := True;
ExcelWorksheet.Cells[5,3].Interior.Color := clAqua;
//add number, code, and description to row 6 [row,column]
ExcelWorksheet.Cells[6,1] := '1/1/2023';
ExcelWorksheet.Cells[6,1].AddComment('No number formatting specified for entered date (1/1/2023).');
ExcelWorksheet.Cells[6,3] := 'No number formatting specified for entered date (1/1/2023).';
//add number, code, and description to row 7 [row,column]
ExcelWorksheet.Cells[7,1] := '1/1/2023';
ExcelWorksheet.Cells[7,1].NumberFormat := AnsiChar('@');
ExcelWorksheet.Cells[7,1].AddComment('Number format: AnsiChar(''@'')');
ExcelWorksheet.Cells[7,2] := 'Number format: AnsiChar(''@'')';
ExcelWorksheet.Cells[7,3] := 'Number format is set to text for the entered date (1/1/2023).';
//add number, code, and description to row 8 [row,column]
ExcelWorksheet.Cells[8,1] := '12345';
ExcelWorksheet.Cells[8,1].NumberFormat := AnsiChar('@');
ExcelWorksheet.Cells[8,1].AddComment('Number format: AnsiChar(''@'')');
ExcelWorksheet.Cells[8,2] := 'Number format: AnsiChar(''@'')';
ExcelWorksheet.Cells[8,3] := 'Number format is set to text for the entered number (12345).';
//add number, code, and description to row 10 [row,column]
ExcelWorksheet.Cells[10,1] := AnsiChar('''') + '1/1/2023';
ExcelWorksheet.Cells[10,1].AddComment('Cell value: AnsiChar('''') + ''1/1/2023''');
ExcelWorksheet.Cells[10,2] := 'Cell value: AnsiChar('''''') + ''1/1/2023''';
ExcelWorksheet.Cells[10,3] := 'An apostrophe ('') is placed at the start of the cell, causing all following characters to be treated as text. The cell number format remains as "General".';
//add text and formatting to row 13 [row,column]
ExcelWorksheet.Cells[13,1] := 'Examples of Excel text() funnction for dates';
ExcelWorksheet.Cells[13,1].Font.Bold := True;
ExcelWorksheet.Cells[13,1].Interior.Color := clSilver;
ExcelWorksheet.Cells[13,2].Interior.Color := clSilver;
ExcelWorksheet.Cells[13,3].Interior.Color := clSilver;
//add number, code, and description to row 14 [row,column]
ExcelWorksheet.Cells[14,1] := '1/1/2023';
ExcelWorksheet.Cells[14,1].AddComment('No number formatting specified for entered date (1/21/2023).');
ExcelWorksheet.Cells[14,3] := 'No number formatting specified for entered date (1/21/2023).';
//add number, code, and description to row 15 [row,column]
ExcelWorksheet.Cells[15,1] := '=text(A14, "mmmm dd, yyyy"';
ExcelWorksheet.Cells[15,1].AddComment('Formula: =text(A14, "mmmm dd, yyyy")');
ExcelWorksheet.Cells[15,2] := 'Formula: =text(A14, "mmmm dd, yyyy")';
ExcelWorksheet.Cells[15,3] := 'Date in cell A19 is shown in the following format: full month name day (two digits), year (four digits). The cell number format remains as "General".';
//add number, code, and description to row 16 [row,column]
ExcelWorksheet.Cells[16,1] := '=text(A14, "mmmm d, yyyy"';
ExcelWorksheet.Cells[16,1].AddComment('Formula: =text(A14, "mmmm d, yyyy")');
ExcelWorksheet.Cells[16,2] := 'Formula: =text(A14, "mmmm d, yyyy")';
ExcelWorksheet.Cells[16,3] := 'Date in cell A19 is shown in the following format: full month name day (one or two digits), year (four digits). The cell number format remains as "General".';
//add number, code, and description to row 17 [row,column]
ExcelWorksheet.Cells[17,1] := '=text(A14, "dddd, mmmm d, yyyy"';
ExcelWorksheet.Cells[17,1].AddComment('Formula: =text(A14, "dddd, mmmm d, yyyy")');
ExcelWorksheet.Cells[17,2] := 'Formula: =text(A14, "dddd, mmmm d, yyyy")';
ExcelWorksheet.Cells[17,3] := 'Date in cell A19 is shown in the following format: weekday, full month name day (one or two digits), year (four digits). The cell number format remains as "General".';
//add number, code, and description to row 18 [row,column]
ExcelWorksheet.Cells[18,1] := '=text(A14, "mmm d, yyyy"';
ExcelWorksheet.Cells[18,1].AddComment('Formula: =text(A14, "mmm d, yyyy")');
ExcelWorksheet.Cells[18,2] := 'Formula: =text(A14, "mmm dd, yyyy")';
ExcelWorksheet.Cells[18,3] := 'Date in cell A19 is shown in the following format: short month name day (one or two digits), year (four digits). The cell number format remains as "General".';
//add number, code, and description to row 19 [row,column]
ExcelWorksheet.Cells[19,1] := '=text(A14, "d-mmm-yyyy"';
ExcelWorksheet.Cells[19,1].AddComment('Formula: =text(A14, "d-mmm-yyyy")');
ExcelWorksheet.Cells[19,2] := 'Formula: =text(A14, "d-mmm-yyyy")';
ExcelWorksheet.Cells[19,3] := 'Date in cell A19 is shown in the following format: day (one or two digits)-short month name-year (four digits). The cell number format remains as "General".';
//add number, code, and description to row 20 [row,column]
ExcelWorksheet.Cells[20,1] := '=text(A14, "dd-mm-yy"';
ExcelWorksheet.Cells[20,1].AddComment('Formula: =text(A14, "dd-mm-yy")');
ExcelWorksheet.Cells[20,2] := 'Formula: =text(A14, "dd-mm-yy")';
ExcelWorksheet.Cells[20,3] := 'Date in cell A19 is shown in the following format: day (two digts-month (two digits)-year (two digits). The cell number format remains as "General".';
//add text and formatting to row 23 [row,column]
ExcelWorksheet.Cells[23,1] := 'Other examples of Excel text() funnction';
ExcelWorksheet.Cells[23,1].Font.Bold := True;
ExcelWorksheet.Cells[23,1].Interior.Color := clSilver;
ExcelWorksheet.Cells[23,2].Interior.Color := clSilver;
ExcelWorksheet.Cells[23,3].Interior.Color := clSilver;
//add number, code, and description to row 24 [row,column]
ExcelWorksheet.Cells[24,1] := '=text(0.25, "0.00%")';
ExcelWorksheet.Cells[24,1].AddComment('Formula: =text(0.25, "0.00%")');
ExcelWorksheet.Cells[24,2] := 'Formula: =text(0.25, "0.00%")';
ExcelWorksheet.Cells[24,3] := '0.25 is expressed as percentage with two decimals. The cell number format remains as "General".';
//add number, code, and description to row 25 [row,column]
ExcelWorksheet.Cells[25,1] := '=text(1234567890.123, "#,##0.00")';
ExcelWorksheet.Cells[25,1].AddComment('Formula: =text(1234567890.123, "#,##0.00")');
ExcelWorksheet.Cells[25,2] := 'Formula: =text(1234567890.123, "#,##0.00")';
ExcelWorksheet.Cells[25,3] := '1234567890.123 is expressed with thousands separator and with two decimals. The cell number format remains as "General".';
//add number, code, and description to row 26 [row,column]
ExcelWorksheet.Cells[26,1] := '=text(now(), "mmmm d, yyyy hh:mm:ss AM/PM")';
ExcelWorksheet.Cells[26,1].AddComment('Formula: =text(now(), "mmmm d, yyyy hh:mm:ss AM/PM")');
ExcelWorksheet.Cells[26,2] := 'Formula: =text(now(), "mmmm d, yyyy hh:mm:ss AM/PM")';
ExcelWorksheet.Cells[26,3] := 'Date and time at this moment are expressed with the following format: full month name day (one or two digits), year (four digits) hour (two digist):minutes (two digits):seconds (two digits) AM/PM. The cell number format remains as "General".';
//add number, code, and description to row 27 [row,column]
ExcelWorksheet.Cells[27,1] := '="Today''s date is " & text(now(), "mmmm d, yyyy")';
ExcelWorksheet.Cells[27,1].AddComment('Formula: ="Today''s date is " & text(now(), "mmmm d, yyyy")');
ExcelWorksheet.Cells[27,2] := 'Formula: ="Today''s date is " & text(now(), "mmmm d, yyyy")';
ExcelWorksheet.Cells[27,3] := 'Some text and today''s date are combined in one cell. The cell number format remains as "General".';
//references
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.numberformat
//https://learn.microsoft.com/en-us/office/vba/api/excel.range.numberformat
//https://support.microsoft.com/en-us/office/number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
//https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat
//https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformats
//https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
//autofit the width of Column A
ExcelWorksheet.Columns[1].ColumnWidth := 35; //sets width of Column A
ExcelWorksheet.Columns[2].EntireColumn.AutoFit; //autofits Column A
ExcelWorksheet.Columns[3].EntireColumn.AutoFit; //autofits Column A
//reference
//https://learn.microsoft.com/en-us/office/vba/api/excel.range.columnwidth
//https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofit
//select B1 cell
ExcelWorksheet.Cells[2,1].Select; //selects Cell B1
ExcelWorkbook.SaveAs(ExcelFileName);
//Note: If a file with the new name already exists, it overwrites it. Write additional code to address as desired.
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
end;
end;
finally
ExcelApplication.Workbooks.Close;
ExcelApplication.DisplayAlerts := True;
ExcelApplication.Quit;
ExcelWorksheet := Unassigned;
ExcelWorkbook := Unassigned;
ExcelApplication := Unassigned;
end;
end;
end;
Posted: Saturday, January 21, 2023