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;