 |
 |
| Physiology? | Figures & Illustrations | Test Questions | Daily Quiz | Calculators | Physiology Tutor | Glossary |
 |
 |
 |
 |
 |
 |
Delphi Code - Microsoft Excel Number, Percentage, Fraction, and Scientific 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 number formatting options, including number, percentage, fraction, and scientific notation formats. 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.MicrosoftExcelNumberPercentageFractionScientificFormatting(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_number_percentage_fraction_scientific_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 - Number, Percentage, Fraction, and Scientific 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;
//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] := 'Number 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] := '0.2';
ExcelWorksheet.Cells[6,1].AddComment('No number formatting specified for entered number (0.2).');
ExcelWorksheet.Cells[6,3] := 'No number formatting specified for entered number (0.2).';
//add number, code, and description to row 7 [row,column]
ExcelWorksheet.Cells[7,1] := '0.20000';
ExcelWorksheet.Cells[7,1].AddComment('No number formatting specified for entered number (0.20000).');
ExcelWorksheet.Cells[7,3] := 'No number formatting specified for entered number (0.20000).';
//add number, code, and description to row 8 [row,column]
ExcelWorksheet.Cells[8,1] := '0.2';
ExcelWorksheet.Cells[8,1].NumberFormat := '0.00';
ExcelWorksheet.Cells[8,1].AddComment('Number format: 0.00 (two decimals) for entered number (0.2)');
ExcelWorksheet.Cells[8,2] := 'Format code: 0.00';
ExcelWorksheet.Cells[8,3] := 'Number format: 0.00 (two decimals) for entered number (0.2)';
//add number, code, and description to row 9 [row,column]
ExcelWorksheet.Cells[9,1] := '0.2';
ExcelWorksheet.Cells[9,1].NumberFormat := '0.00000';
ExcelWorksheet.Cells[9,1].AddComment('Number format: 0.00000 (five decimals) for entered number (0.2)');
ExcelWorksheet.Cells[9,2] := 'Format code: 0.00000';
ExcelWorksheet.Cells[9,3] := 'Number format: 0.00000 (five decimals) for entered number (0.2)';
//add number, code, and description to row 10 [row,column]
ExcelWorksheet.Cells[10,1] := '126.3596895214';
ExcelWorksheet.Cells[10,1].NumberFormat := '0.00000';
ExcelWorksheet.Cells[10,1].AddComment('Number format: 0.00000 (five decimals) for entered number (126.3596895214)');
ExcelWorksheet.Cells[10,2] := 'Format code: 0.00000';
ExcelWorksheet.Cells[10,3] := 'Number format: 0.00000 (five decimals) for entered number (126.3596895214)';
//add number, code, and description to row 11 [row,column]
ExcelWorksheet.Cells[11,1] := '0.23654';
ExcelWorksheet.Cells[11,1].NumberFormat := '0.00';
ExcelWorksheet.Cells[11,1].AddComment('Number format: 0.00 (two decimals) for entered number (0.23654). Rounded to two decimals.');
ExcelWorksheet.Cells[11,2] := 'Format code: 0.00';
ExcelWorksheet.Cells[11,3] := 'Number format: 0.00 (two decimals) for entered number (0.23654). Rounded to two decimals.';
//add number, code, and description to row 12 [row,column]
ExcelWorksheet.Cells[12,1] := '0.23654';
ExcelWorksheet.Cells[12,1].NumberFormat := '#.00';
ExcelWorksheet.Cells[12,1].AddComment('Number format: #.00 for entered number (0.23654). Skips the zero to the left of decimal. Two decimal places.');
ExcelWorksheet.Cells[12,2] := 'Format code: #.00';
ExcelWorksheet.Cells[12,3] := 'Number format: #.00 for entered number (0.23654). Skips the zero to the left of decimal. Two decimal places.';
//add number, code, and description to row 13 [row,column]
ExcelWorksheet.Cells[13,1] := '1.2358123';
ExcelWorksheet.Cells[13,1].NumberFormat := '000.000';
ExcelWorksheet.Cells[13,1].AddComment('Number format: 000.000 for entered number (1.2358123). Leading zeros and three decimals.');
ExcelWorksheet.Cells[13,2] := 'Format code: 000.000';
ExcelWorksheet.Cells[13,3] := 'Number format: 000.000 for entered number (1.2358123). Leading zeros and three decimals.';
//add number, code, and description to row 14 [row,column]
ExcelWorksheet.Cells[14,1] := '12';
ExcelWorksheet.Cells[14,1].NumberFormat := '000';
ExcelWorksheet.Cells[14,1].AddComment('Number format: 000 (three digits) for entered number (12. Adds leading zeros.');
ExcelWorksheet.Cells[14,2] := 'Format code: 000';
ExcelWorksheet.Cells[14,3] := 'Number format: 000 (three digits) for entered number (12). Adds leading zeros.';
//add number, code, and description to row 15 [row,column]
ExcelWorksheet.Cells[15,1] := '12';
ExcelWorksheet.Cells[15,1].NumberFormat := '0000';
ExcelWorksheet.Cells[15,1].AddComment('Number format: 0000 (four digits) for entered number (12). Adds leading zeros.');
ExcelWorksheet.Cells[15,2] := 'Format code: 0000';
ExcelWorksheet.Cells[15,3] := 'Number format: 0000 (four digits) for entered number (12). Adds leading zeros.';
//add number, code, and description to row 16 [row,column]
ExcelWorksheet.Cells[16,1] := '12';
ExcelWorksheet.Cells[16,1].NumberFormat := '00000';
ExcelWorksheet.Cells[16,1].AddComment('Number format: 00000 (five digits) for entered number (12). Adds leading zeros.');
ExcelWorksheet.Cells[16,2] := 'Format code: 00000';
ExcelWorksheet.Cells[16,3] := 'Number format: 00000 (five digits) for entered number (12). Adds leading zeros.';
//add number, code, and description to row 17 [row,column]
ExcelWorksheet.Cells[17,1] := '12345';
ExcelWorksheet.Cells[17,1].NumberFormat := '#,###;-#,###;0';
ExcelWorksheet.Cells[17,1].AddComment('Number format: #,###;-#,###;0 for entered number (12345). Adds thousands separator comma. - for negative numbers. No decimal places.');
ExcelWorksheet.Cells[17,2] := 'Format code: #,###;-#,###;0';
ExcelWorksheet.Cells[17,3] := 'Number format: #,###;-#,###;0 for entered number (12345). Adds thousands separator comma. - for negative numbers. No decimal places.';
//add number, code, and description to row 18 [row,column]
ExcelWorksheet.Cells[18,1] := '12345';
ExcelWorksheet.Cells[18,1].NumberFormat := '#,###.00;-#,###.00;0.00';
ExcelWorksheet.Cells[18,1].AddComment('Number format: #,###.00;-#,###.00;0.00 for entered number (12345). Adds thousands separator comma. - for negative numbers. Two decimal places.');
ExcelWorksheet.Cells[18,2] := 'Format code: #,###.00;-#,###.00;0.00';
ExcelWorksheet.Cells[18,3] := 'Number format: #,###.00;-#,###.00;0.00 for entered number (12345). Adds thousands separator comma. - for negative numbers. Two decimal places.';
//add number, code, and description to row 19 [row,column]
ExcelWorksheet.Cells[19,1] := '-12345';
ExcelWorksheet.Cells[19,1].NumberFormat := '#,###.00;[red]-#,###.00;0.00';
ExcelWorksheet.Cells[19,1].AddComment('Number format: #,###.00;[red]-#,###.00;0.00 for entered number (-12345). Adds thousands separator comma. - and red for negative numbers. Two decimal places.');
ExcelWorksheet.Cells[19,2] := 'Format code: #,###.00;[red]-#,###.00;0.00';
ExcelWorksheet.Cells[19,3] := 'Number format: #,###.00;[red]-#,###.00;0.00 for entered number (-12345). Adds thousands separator comma. - and red for negative numbers. Two decimal places.';
//add number, code, and description to row 20 [row,column]
ExcelWorksheet.Cells[20,1] := '-12345';
ExcelWorksheet.Cells[20,1].NumberFormat := '#,###.00;[red](#,###.00);0.00';
ExcelWorksheet.Cells[20,1].AddComment('Number format: #,###.00;[red](#,###.00);0.00 for entered number (-12345). Adds thousands separator comma. Parentheses and red for negative numbers. Two decimal places.');
ExcelWorksheet.Cells[20,2] := 'Format code: #,###.00;[red](#,###.00);0.00';
ExcelWorksheet.Cells[20,3] := 'Number format: #,###.00;[red](#,###.00);0.00 for entered number (-12345). Adds thousands separator comma. Parentheses and red for negative numbers. Two decimal places.';
//add number, code, and description to row 21 [row,column]
ExcelWorksheet.Cells[21,1] := '0.23657';
ExcelWorksheet.Cells[21,1].NumberFormat := '0.00%';
ExcelWorksheet.Cells[21,1].AddComment('Number format: 0.00% for entered number (0.23657). Percentage format. Two decimal places.');
ExcelWorksheet.Cells[21,2] := 'Format code: 0.00%';
ExcelWorksheet.Cells[21,3] := 'Number format: 0.00% for entered number (0.23657). Percentage format. Two decimal places.';
//add number, code, and description to row 22 [row,column]
ExcelWorksheet.Cells[22,1] := '0.23657';
ExcelWorksheet.Cells[22,1].NumberFormat := '0.0%';
ExcelWorksheet.Cells[22,1].AddComment('Number format: 0.0% for entered number (0.23657). Percentage format. One decimal place.');
ExcelWorksheet.Cells[22,2] := 'Format code: 0.0%';
ExcelWorksheet.Cells[22,3] := 'Number format: 0.0% for entered number (0.23657). Percentage format. One decimal place.';
//add number, code, and description to row 23 [row,column]
ExcelWorksheet.Cells[23,1] := '0.23657';
ExcelWorksheet.Cells[23,1].NumberFormat := '0%';
ExcelWorksheet.Cells[23,1].AddComment('Number format: 0% for entered number (0.23657). Percentage format. No decimal places.');
ExcelWorksheet.Cells[23,2] := 'Format code: 0%';
ExcelWorksheet.Cells[23,3] := 'Number format: 0% for entered number (0.23657). Percentage format. No decimal places.';
//add number, code, and description to row 24 [row,column]
ExcelWorksheet.Cells[24,1] := '-0.23657';
ExcelWorksheet.Cells[24,1].NumberFormat := '0.00%;[red]0.00%';
ExcelWorksheet.Cells[24,1].AddComment('Number format: 0.00%;[red]0.00% for entered number (-0.23657). Percentage format. Red for negative. Two decimal places.');
ExcelWorksheet.Cells[24,2] := 'Format code: 0.00%;[red]0.00%';
ExcelWorksheet.Cells[24,3] := 'Number format: 0.00%;[red]0.00% for entered number (-0.23657). Percentage format. Red for negative. Two decimal places.';
//add number, code, and description to row 25 [row,column]
ExcelWorksheet.Cells[25,1] := '3.25';
ExcelWorksheet.Cells[25,1].NumberFormat := '# ?/?';
ExcelWorksheet.Cells[25,1].AddComment('Number format: # ?/? for entered number (3.25). Fraction format. Up to one digit.');
ExcelWorksheet.Cells[25,2] := 'Format code: # ?/?';
ExcelWorksheet.Cells[25,3] := 'Number format: # ?/? for entered number (3.25). Fraction format. Up to one digit.';
//add number, code, and description to row 26 [row,column]
ExcelWorksheet.Cells[26,1] := '3.55';
ExcelWorksheet.Cells[26,1].NumberFormat := '# ??/??';
ExcelWorksheet.Cells[26,1].AddComment('Number format: # ??/?? for entered number (3.55). Fraction format. Up to two digits.');
ExcelWorksheet.Cells[26,2] := 'Format code: # ??/??';
ExcelWorksheet.Cells[26,3] := 'Number format: # ??/?? for entered number (3.55). Fraction format. Up to two digits.';
//add number, code, and description to row 27 [row,column]
ExcelWorksheet.Cells[27,1] := '3.555';
ExcelWorksheet.Cells[27,1].NumberFormat := '# ???/???';
ExcelWorksheet.Cells[27,1].AddComment('Number format: # ???/??? for entered number (3.555). Fraction format. Up to three digits.');
ExcelWorksheet.Cells[27,2] := 'Format code: # ???/???';
ExcelWorksheet.Cells[27,3] := 'Number format: # ???/??? for entered number (3.555). Fraction format. Up to three digits.';
//add number, code, and description to row 28 [row,column]
ExcelWorksheet.Cells[28,1] := '125';
ExcelWorksheet.Cells[28,1].NumberFormat := '0E+0';
ExcelWorksheet.Cells[28,1].AddComment('Number format: 0E+0 for entered number (125). Scientific notation. No decimals.');
ExcelWorksheet.Cells[28,2] := 'Format code: 0E+0';
ExcelWorksheet.Cells[28,3] := 'Number format: 0E+0 for entered number (125). Scientific notation. No decimals.';
//add number, code, and description to row 29 [row,column]
ExcelWorksheet.Cells[29,1] := '125';
ExcelWorksheet.Cells[29,1].NumberFormat := '0.0E+0';
ExcelWorksheet.Cells[29,1].AddComment('Number format: 0.0E+0 for entered number (125). Scientific notation. One decimal place.');
ExcelWorksheet.Cells[29,2] := 'Format code: 0.0E+0';
ExcelWorksheet.Cells[29,3] := 'Number format: 0.0E+0 for entered number (125). Scientific notation. One decimal place.';
//add number, code, and description to row 30 [row,column]
ExcelWorksheet.Cells[30,1] := '125';
ExcelWorksheet.Cells[30,1].NumberFormat := '0.00E+0';
ExcelWorksheet.Cells[30,1].AddComment('Number format: 0.00E+0 for entered number (125). Scientific notation. Two decimal places.');
ExcelWorksheet.Cells[30,2] := 'Format code: 0.00E+0';
ExcelWorksheet.Cells[30,3] := 'Number format: 0.00E+0 for entered number (125). Scientific notation. Two decimal places.';
//add number, code, and description to row 31 [row,column]
ExcelWorksheet.Cells[31,1] := '125';
ExcelWorksheet.Cells[31,1].NumberFormat := '0.00E+00';
ExcelWorksheet.Cells[31,1].AddComment('Number format: 0.00E+00 for entered number (125). Scientific notation. Two decimal places. Two digits for exponent.');
ExcelWorksheet.Cells[31,2] := 'Format code: 0.00E+00';
ExcelWorksheet.Cells[31,3] := 'Number format: 0.00E+00 for entered number (125). Scientific notation. Two decimal places. Two digits for exponent.';
//references
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.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
//autofit the width of Column A
ExcelWorksheet.Columns[1].ColumnWidth := 20; //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: Friday, November 25, 2022