 |
 |
| Physiology? | Figures & Illustrations | Test Questions | Daily Quiz | Calculators | Physiology Tutor | Glossary |
 |
 |
 |
 |
 |
 |
Delphi Code - Microsoft Excel Cell 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 cell formatting options, including font formatting, background color, horizontal cell alignment, number formatting, cell borders, cell notes and comments, etc. 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. For additional Excel automation solutions, see Microsoft Excel Automation with Delphi.
procedure TForm1.MicrosoftExcelCellFormatting(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.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'; //[row,column]
//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 background color of A1 cell
ExcelWorksheet.Cells[1,1].Interior.Color := clYellow;
//align A1 cell to center
ExcelWorksheet.Cells[1,1].HorizontalAlignment := -4108;
//add date formula to A2 cell
ExcelWorksheet.Cells[2,1] := '=today()'; //adds today's date to A2 cell
//align A2 cell to left
ExcelWorksheet.Cells[2,1].HorizontalAlignment := -4131; //xlHAlignLeft = -4131
//add some text
ExcelWorksheet.Cells[4,1] := '<--- Aligned to left';
ExcelWorksheet.Cells[5,1] := 'Aligned to right --->';
ExcelWorksheet.Cells[6,1] := '<--- Aligned to cetner --->';
ExcelWorksheet.Cells[7,1] := 'Bold font (applied to whole cell)';
ExcelWorksheet.Cells[8,1] := 'Underline (applied to whole cell)';
ExcelWorksheet.Cells[9,1] := 'Double Underline (applied to whole cell)';
ExcelWorksheet.Cells[10,1] := 'Sky blue background color (applied to whole cell)';
ExcelWorksheet.Cells[11,1] := 'Blue font (applied to whole cell)';
ExcelWorksheet.Cells[12,1] := 'Arial font (applied to whole cell)';
ExcelWorksheet.Cells[13,1] := 'Italic font (applied to whole cell)';
ExcelWorksheet.Cells[14,1] := 'Superscript (applied to whole cell)';
ExcelWorksheet.Cells[15,1] := 'Superscript for part of cell (E = MC2)';
ExcelWorksheet.Cells[16,1] := 'Subscript (applied to whole cell)';
ExcelWorksheet.Cells[17,1] := 'Subscript for part of cell (H2O)';
ExcelWorksheet.Cells[19,1] := 'Cell with borders';
ExcelWorksheet.Cells[21,1] := 'This cell has a comment. Hover your mourse over the cell to see the comment.';
ExcelWorksheet.Cells[23,1] := 'Multiple formatting in the same cell (Blue font | H2O | E = MC2 | Arial Black font | bold and underlined | italic | strikethrough)';
ExcelWorksheet.Cells[25,1] := 'Cells below all contain 0.2, but different number formatting is applied to each cell (hover mouse over cell to see comment).';
ExcelWorksheet.Cells[26,1] := '0.2';
ExcelWorksheet.Cells[27,1] := '0.2';
ExcelWorksheet.Cells[28,1] := '0.2';
ExcelWorksheet.Cells[29,1] := '0.2';
//align cell to left
ExcelWorksheet.Cells[4,1].HorizontalAlignment := -4131; //xlHAlignLeft = -4131
//align cell to right
ExcelWorksheet.Cells[5,1].HorizontalAlignment := -4152; //xlHAlignRight = -4152
//align cell to center
ExcelWorksheet.Cells[6,1].HorizontalAlignment := -4108; //xlHAlignCenter = -4108
//change font to bold
ExcelWorksheet.Cells[7,1].Font.Bold := True;
//underline
ExcelWorksheet.Cells[8,1].Font.Underline := 2; //xlUnderlineStyleSingle = 2
//double underline
ExcelWorksheet.Cells[9,1].Font.Underline := -4119; //xlUnderlineStyleDouble = -4119
//change background color to sky blue
ExcelWorksheet.Cells[10,1].Interior.Color := clSkyBlue;
//change font color to blue
ExcelWorksheet.Cells[11,1].Font.Color := clBlue;
//change font type to Arial
ExcelWorksheet.Cells[12,1].Font.Name := 'Arial';
//change font to italic
ExcelWorksheet.Cells[13,1].Font.Italic := True;
//superscript for whole cell
ExcelWorksheet.Cells[14,1].Font.Superscript := True;
//superscript for part of cell (position,length)
ExcelWorksheet.Cells[15,1].Characters(37,1).Font.Superscript := True;
//subscript for whole cell
ExcelWorksheet.Cells[16,1].Font.Subscript := True;
//subscript for part of cell (position,length)
ExcelWorksheet.Cells[17,1].Characters(30,1).Font.Subscript := True;
//set cell borders
ExcelWorksheet.Cells[19,1].Borders.LineStyle := 1;
//add comment to cell
ExcelWorksheet.Cells[21,1].AddComment('Hello there! How are you doing today?');
//multiple formatting in the same cell (position,length)
ExcelWorksheet.Cells[23,1].Characters(38,9).Font.Color := clBlue;
ExcelWorksheet.Cells[23,1].Characters(54,1).Font.Subscript := True;
ExcelWorksheet.Cells[23,1].Characters(67,1).Font.Superscript := True;
ExcelWorksheet.Cells[23,1].Characters(72,17).Font.Name := 'Arial Black';
ExcelWorksheet.Cells[23,1].Characters(94,19).Font.Bold := True;
ExcelWorksheet.Cells[23,1].Characters(94,19).Font.Underline := 2; //xlUnderlineStyleSingle = 2
ExcelWorksheet.Cells[23,1].Characters(118,6).Font.Italic := True;
ExcelWorksheet.Cells[23,1].Characters(129,13).Font.Strikethrough := True;
//cell number format = General
ExcelWorksheet.Cells[26,1].NumberFormat := 'General';
ExcelWorksheet.Cells[26,1].AddComment('Cell Format = General');
//cell number format set to numbers with five decimal places
ExcelWorksheet.Cells[27,1].NumberFormat := '0.00000';
ExcelWorksheet.Cells[27,1].AddComment('Cell Format = Number (with five decimals)');
//cell number format set to US dollar currency with two decimal places
ExcelWorksheet.Cells[28,1].NumberFormat := '$0.00';
ExcelWorksheet.Cells[28,1].AddComment('Cell Format = USD Currency (with two decimals)');
//cell number format set to percentage with two decimal places
ExcelWorksheet.Cells[29,1].NumberFormat := '0.00%';
ExcelWorksheet.Cells[29,1].AddComment('Cell Format = Percentage (with two decimals)');
//references
//https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.cells
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.interior
//https://learn.microsoft.com/en-us/office/vba/api/excel.font.size
//https://learn.microsoft.com/en-us/office/vba/api/excel.font.bold
//https://learn.microsoft.com/en-us/office/vba/api/excel.font.italic
//https://learn.microsoft.com/en-us/office/vba/api/excel.font.name
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Font.Color
//https://learn.microsoft.com/en-us/office/vba/api/excel.font.subscript
//https://learn.microsoft.com/en-us/office/vba/api/excel.font.superscript
//https://learn.microsoft.com/en-us/office/vba/api/excel.font.strikethrough
//https://docs.microsoft.com/en-us/office/vba/api/excel.borders
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Comment
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.numberformat
//https://learn.microsoft.com/en-us/office/vba/api/excel.cellformat.horizontalalignment
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Range.HorizontalAlignment
//https://docs.microsoft.com/en-us/office/vba/api/excel.xlhalign
//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
//https://learn.microsoft.com/en-us/office/vba/api/excel.font.underline
//https://learn.microsoft.com/en-us/office/vba/api/excel.xlunderlinestyle
//Name Value Description
//xlUnderlineStyleDouble -4119 Double thick underline.
//xlUnderlineStyleDoubleAccounting 5 Two thin underlines placed close together.
//xlUnderlineStyleNone -4142 No underlining.
//xlUnderlineStyleSingle 2 Single underlining.
//xlUnderlineStyleSingleAccounting 4 Not supported.
//autofit the width of Column A
ExcelWorksheet.Columns[1].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: Saturday, September 17, 2022