ColumnCount Property

Applies to TestComplete 15.47, last modified on January 20, 2023

Note: To work with Excel files in your tests, you do not need to have Microsoft Office Excel installed on your computer.

Description

The ExcelSheet.ColumnCount property returns the column index of the last non-empty cell in the current Excel sheet.

Declaration

ExcelSheetObj.ColumnCount

Read-Only Property Integer
ExcelSheetObj An expression, variable or parameter that specifies a reference to an ExcelSheet object

Applies To

The property is applied to the following object:

Property Value

A column index of the last non-empty cell.

Example

The code below demonstrates how you can use the ExcelSheet.ColumnCount property in your script:

JavaScript, JScript

function ExcelExample()
{
  var fileName = "c:\\temp\\MyFile.xlsx";
  var searchValue = "MyValue";
  
  // Open the Excel file
  var excelFile = Excel.Open(fileName);
  
  // Search the specified value in the Excel file
  var found = false;
  for (var i = 0; i < excelFile.SheetCount; i++)
  {
    var excelSheet = excelFile.SheetByIndex(i);
    for (var j = 1; j <= excelSheet.ColumnCount; j++)
    {
      for (var k =1; k <= excelSheet.RowCount; k++)
      if (excelSheet.Cell(j, k).Value == searchValue)
      {
        Log.Message("The specified value is found on the " + excelSheet.Title + " sheet in the (" + j + "," + k + ") cell");
        found = true;
      }
    }
  }
  if (found == false)
    Log.Message("The specified value isn't found");
}

Python

def ExcelExample():
  
  fileName = "c:\\temp\\MyFile.xlsx"
  searchValue = "MyValue"
  
  # Open the Excel file
  excelFile = Excel.Open(fileName)
  
  # Search the specified value in the Excel file
  found = 0
  for i in range (0, excelFile.SheetCount):
    excelSheet = excelFile.SheetByIndex[i]
    for j in range (1, excelSheet.ColumnCount):    
      for k in range (1, excelSheet.RowCount):
        if (excelSheet.Cell[j, k].Value == searchValue):
          Log.Message("The specified value is found on the " + excelSheet.Title + " sheet in the (" + str(j) + "," + str(k) + ") cell")
          found = 1
  if (found == 0):
    Log.Message("The specified value isn't found")

VBScript

Sub ExcelExample
  Dim fileName, searchValue, excelFile, found, i, excelSheet, j, k
  fileName = "c:\temp\MyFile.xlsx"
  searchValue = "MyValue"
  
  ' Open the Excel file
  Set excelFile = Excel.Open(fileName)
  
  ' Search the specified value in the Excel file
  found = false
  For i = 0 To excelFile.SheetCount - 1

    Set excelSheet = excelFile.SheetByIndex(i)
    For j = 1 to excelSheet.ColumnCount

      For k =1 to excelSheet.RowCount
        If excelSheet.Cell(j, k).Value = searchValue Then
          Log.Message("The specified value is found on the " & excelSheet.Title & " sheet in the (" & j & "," & k & ") cell")
          found = true
        End If
      Next
    Next
   Next
  If found = false Then
    Log.Message("The specified value isn't found")
  End If
End Sub

DelphiScript

procedure ExcelExample;
var
  fileName, excelFile, searchValue, found, excelSheet, i, j, k;
begin
  fileName := 'c:\\temp\\MyFile.xlsx';
  searchValue := 'MyValue';
  
  // Open the Excel file
  excelFile := Excel.Open(fileName);
  
  // Search the specified value in the Excel file
  found := false;
  for i := 0 to excelFile.SheetCount -1 do
  begin
    excelSheet := excelFile.SheetByIndex(i);
    for j := 1 to excelSheet.ColumnCount do
    begin
      for k := 1 to excelSheet.RowCount do
      begin
        if (VarToStr(excelSheet.Cell(j, k).Value) = VarToStr(searchValue)) then
        begin
          Log.Message('The specified value is found on the ' + VarToStr(excelSheet.Title) + ' sheet in the (' + VarToStr(j) + ',' + VarToStr(k) + ') cell');
          found := true;
        end;
      end;
    end;
   end;
  if found = false then
  begin
    Log.Message('The specified value is not found')
  end;
end;

C++Script, C#Script

function ExcelExample()
{
  var fileName = "c:\\temp\\MyFile.xlsx";
  var searchValue = "MyValue";
  // Open the Excel file
  var excelFile = Excel["Open"](fileName);
  
  // Search the specified value in the Excel file
  var found = false;
  for (var i = 0; i < excelFile["SheetCount"]; i++)
  {
    var excelSheet = excelFile["SheetByIndex"](i);
    for (var j = 1; j <= excelSheet["ColumnCount"]; j++)
    {
      for (var k =1; k <= excelSheet["RowCount"]; k++)
      if (excelSheet["Cell"](j, k)["Value"] == searchValue)
      {
        Log["Message"]("The specified value is found on the " + excelSheet.Title + " sheet in the (" + j + "," + k + ") cell");
        found = true;
      }
    }
  }
  if (found == false)
    Log["Message"]("The specified value isn't found");
}

See Also

ExcelSheet Object
Working with Microsoft Excel Files

Highlight search results