PhysiologyWeb Logo  Search
PhysiologyWeb Loading...

Delphi Code - Get Number of Rows and Columns Used in Excel Worksheet
Here is a simple Delphi procedure that opens an existing Microsoft Excel file and reads information about the used range of cells, the number of rows and columns used, and the last row and last column used. Before saving, the background color for all cells in the used range is changed, and the entire used range is selected. The file is saved using the same name (overwrites the original file). 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.GetNumberOfRowsAndColumnsUsedInExcelWorksheet(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.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.Open(ExcelFileName);
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open
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
except
ExcelWorksheet := Null;
//add error/exception handling code as desired
end;

If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;


//be sure a Memo1 VCL object exists

//get the range of cells used
Memo1.Lines.Add('ExcelWorksheet.Usedrange.Address = ' + ExcelWorksheet.Usedrange.Address);

//get the number of used rows
Memo1.Lines.Add('ExcelWorksheet.Usedrange.Rows.Count = ' + IntToStr(ExcelWorksheet.Usedrange.Rows.Count));

//get the number of used columns
Memo1.Lines.Add('ExcelWorksheet.Usedrange.Columns.Count = ' + IntToStr(ExcelWorksheet.Usedrange.Columns.Count));

//get the number of the last row used
Memo1.Lines.Add('ExcelWorksheet.Usedrange.SpecialCells(11).Row = ' + IntToStr(ExcelWorksheet.Usedrange.SpecialCells(11).Row));

//get the number of the last column used
Memo1.Lines.Add('ExcelWorksheet.Usedrange.SpecialCells(11).Column = ' + IntToStr(ExcelWorksheet.Usedrange.SpecialCells(11).Column));

//references
//https://docs.microsoft.com/en-us/office/vba/api/excel.range.address
//https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xlcelltype?view=excel-pia#microsoft-office-interop-excel-xlcelltype-xlcelltypelastcell

//do what you need with the file before saving. here we will change the background color for all cells in the used range
ExcelWorksheet.Usedrange.Interior.Color := clYellow;
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.interior.color

ExcelWorksheet.Usedrange.Select; //selects the entire used range

ExcelWorkbook.Save;
end;
end;
finally
ExcelApplication.Workbooks.Close;
ExcelApplication.DisplayAlerts := True;
ExcelApplication.Quit;

ExcelWorksheet := Unassigned;
ExcelWorkbook := Unassigned;
ExcelApplication := Unassigned;
end;
end;
end;






Posted: Saturday, April 16, 2022