PhysiologyWeb Logo  Search
PhysiologyWeb Loading...

Delphi Code - Microsoft Excel Date Formatting
Here is a simple Delphi procedure that opens a new Microsoft Excel file (i.e., Excel workbook) and demonstrates how to apply a variety of date formatting options. 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.MicrosoftExcelDateFormatting(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_date_formatting.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 Number Formatting - Date Formatting';
//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;
//change background color of A1 cell
ExcelWorksheet.Cells[1,1].Interior.Color := clYellow;

//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] := 'Date 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 date, code, and description to row 6 [row,column]
ExcelWorksheet.Cells[6,1] := '=today()'; //adds today's date to A6 cell
ExcelWorksheet.Cells[6,1].AddComment('Default date format');
ExcelWorksheet.Cells[6,3] := 'Default date format';

//add date, code, and description to row 7 [row,column]
ExcelWorksheet.Cells[7,1] := '=today()';
ExcelWorksheet.Cells[7,1].NumberFormat := 'yyyy-m-d';
ExcelWorksheet.Cells[7,1].AddComment('Date format: year-month-day. 1 or 2 digits for month and day.');
ExcelWorksheet.Cells[7,2] := 'yyyy-m-d';
ExcelWorksheet.Cells[7,3] := 'Date format: year-month-day. 1 or 2 digits for month and day.';

//add date, code, and description to row 8 [row,column]
ExcelWorksheet.Cells[8,1] := '=today()';
ExcelWorksheet.Cells[8,1].NumberFormat := 'yyyy-mm-dd';
ExcelWorksheet.Cells[8,1].AddComment('Date format: year-month-day. 2 digits for month and day.');
ExcelWorksheet.Cells[8,2] := 'yyyy-mm-dd';
ExcelWorksheet.Cells[8,3] := 'Date format: year-month-day. 2 digits for month and day.';

//add date, code, and description to row 9 [row,column]
ExcelWorksheet.Cells[9,1] := '=today()';
ExcelWorksheet.Cells[9,1].NumberFormat := 'm/d/yy';
ExcelWorksheet.Cells[9,1].AddComment('Date format: month/day/year. 1 or 2 digits for month and day. 2 digits for year.');
ExcelWorksheet.Cells[9,2] := 'm/d/yy';
ExcelWorksheet.Cells[9,3] := 'Date format: month/day/year. 1 or 2 digits for month and day. 2 digits for year.';

//add date, code, and description to row 10 [row,column]
ExcelWorksheet.Cells[10,1] := '=today()';
ExcelWorksheet.Cells[10,1].NumberFormat := 'mmmm d, yyyy';
ExcelWorksheet.Cells[10,1].AddComment('Date format: month day, year. Full month name.');
ExcelWorksheet.Cells[10,2] := 'mmmm d, yyyy';
ExcelWorksheet.Cells[10,3] := 'Date format: month day, year. Full month name.';

//add date, code, and description to row 11 [row,column]
ExcelWorksheet.Cells[11,1] := '=today()';
ExcelWorksheet.Cells[11,1].NumberFormat := 'mmm d, yyyy';
ExcelWorksheet.Cells[11,1].AddComment('Date format: month day, year. Short month name.');
ExcelWorksheet.Cells[11,2] := 'mmm d, yyyy';
ExcelWorksheet.Cells[11,3] := 'Date format: month day, year. Short month name.';

//add date, code, and description to row 12 [row,column]
ExcelWorksheet.Cells[12,1] := '=today()';
ExcelWorksheet.Cells[12,1].NumberFormat := 'dddd, mmmm d, yyyy';
ExcelWorksheet.Cells[12,1].AddComment('Date format: weekday, month day, year. Full name for weekday and month.');
ExcelWorksheet.Cells[12,2] := 'dddd, mmmm d, yyyy';
ExcelWorksheet.Cells[12,3] := 'Date format: weekday, month day, year. Full name for weekday and month.';

//add date, code, and description to row 13 [row,column]
ExcelWorksheet.Cells[13,1] := '=today()';
ExcelWorksheet.Cells[13,1].NumberFormat := 'ddd, mmm d, yyyy';
ExcelWorksheet.Cells[13,1].AddComment('Date format: weekday, month day, year. Short name for weekday and month.');
ExcelWorksheet.Cells[13,2] := 'ddd, mmm d, yyyy';
ExcelWorksheet.Cells[13,3] := 'Date format: weekday, month day, year. Short name for weekday and month.';

//add date, code, and description to row 14 [row,column]
ExcelWorksheet.Cells[14,1] := '=today()';
ExcelWorksheet.Cells[14,1].NumberFormat := 'mmmm yyyy';
ExcelWorksheet.Cells[14,1].AddComment('Date format: month year. Full name for month.');
ExcelWorksheet.Cells[14,2] := 'mmmm yyyy';
ExcelWorksheet.Cells[14,3] := 'Date format: month year. Full name for month.';

//add date, code, and description to row 15 [row,column]
ExcelWorksheet.Cells[15,1] := '=today()';
ExcelWorksheet.Cells[15,1].NumberFormat := '[$-00000000]dddd, mmmm d, yyyy';
ExcelWorksheet.Cells[15,1].AddComment('Date format: weekday, month day, year. Default system settings for date/time.');
ExcelWorksheet.Cells[15,2] := '[$-00000000]dddd, mmmm d, yyyy';
ExcelWorksheet.Cells[15,3] := 'Date format: weekday, month day, year. Default system settings for date/time.';

//add date, code, and description to row 16 [row,column]
ExcelWorksheet.Cells[16,1] := '=today()';
ExcelWorksheet.Cells[16,1].NumberFormat := '[$-en-US]dddd, mmmm d, yyyy;@';
ExcelWorksheet.Cells[16,1].AddComment('Date format: weekday, month day, year. English - United States locale.');
ExcelWorksheet.Cells[16,2] := '[$-en-US]dddd, mmmm d, yyyy;@';
ExcelWorksheet.Cells[16,3] := 'Date format: weekday, month day, year. English - United States locale.';

//references
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.numberformat
//https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat

//autofit the width of Column A
ExcelWorksheet.Columns[1].EntireColumn.AutoFit; //autofits Column A
ExcelWorksheet.Columns[2].EntireColumn.AutoFit; //autofits Column A
ExcelWorksheet.Columns[3].EntireColumn.AutoFit; //autofits Column A
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.range.autofit

//select A1 cell
ExcelWorksheet.Cells[1,1].Select; //selects Cell A1

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: Sunday, November 13, 2022