SheetByTitle Property

Applies to TestComplete 15.40, last modified on July 05, 2022

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

Description

The SheetByTitle property returns the ExcelSheet object that provides access to the Excel sheet specified by its title.

Declaration

ExcelFileObj.SheetByTitle(SheetTitle)

Read-Only Property An ExcelSheet object
ExcelFileObj An expression, variable or parameter that specifies a reference to an ExcelFile object
SheetTitle [in]    Required    String    

Applies To

The property is applied to the following object:

Parameters

The property has the following parameter:

SheetTitle

A title of the desired sheet.

Property Value

An ExcelSheet object that corresponds to the sheet with the specified title. To perform operations over the Excel sheet, use the methods and properties of the returned object.

If the sheet with the specified title does not exist in the file, the property will return the null value (Nothing in VBScript, nil in DelphiScript, None in Python, null in JavaScript, JScript, C++Script and C#Script) and post an error message to the test log.

Note: If the Error handling > On error property is set to Stop current item or Stop project, TestComplete will stop the test run when the error is posted to the test log.

Example

The code below demonstrates how you can use the SheetByTitle property in your script:

Note: Before getting a sheet by its title, the sample code uses the SheetWithTitleExists helper routine to check if such a sheet exists in the file.

JavaScript

// A helper routine
// Check if the Excel file has a sheet with the specified title
function SheetWithTitleExists(aFile, aTitle)
{
  if (! equal(aFile, null))
  {
    if (aFile.SheetCount > 0)
    {
      for (let i = 0; i < aFile.SheetCount; i++)
      {
        if (equal(aFile.SheetByIndex(i).Title, aTitle))
        {
          return true;
        }
      }
      return false;
    }
    else
    {
      return false;
    }
  }
}

function ExcelExample()
{
  var fileName = "C:\\temp\\DataStorage.xlsx"
  var sheetName = "Sheet1";
  var rowIndex;

  // Set data to write into the file
  var currentTime = aqDateTime.Now();
  var cpu = Sys.CPU;

  if (aqFileSystem.Exists(fileName))
  {
  // Get the sheet of the Excel file
  var excelFile = Excel.Open(fileName);

  // Use the SheetWithTitleExists helper routine
  // to check if the sheet with the specified title exists in the file
  if (SheetWithTitleExists(excelFile, sheetName))
  {
      var excelSheet = excelFile.SheetByTitle(sheetName);
      rowIndex = excelSheet.RowCount;
      rowIndex++;
  }
  else
  {
      Log.Message(aqString.Format("The %s sheet does not exist and will be added.", sheetName));
      var excelSheet = excelFile.AddSheet(sheetName);
      rowIndex = 1;
  }
  // Write data into the file
  excelSheet.Cell("A", rowIndex).Value = aqDateTime.Now();
  excelSheet.Cell(2, rowIndex).Value = Sys.CPU;

  // Save the file to apply the changes
  excelFile.Save();

  // Save the file with another name
  // fileName = "C:\\temp\\DataStorage_new.xlsx";
  // excelFile.SaveAs(fileName);
  }
  else
  {
    Log.Error(aqString.Format("The %s file does not exist.", fileName));
  }

}

JScript

// A helper routine
// Check if the Excel file has a sheet with the specified title
function SheetWithTitleExists(aFile, aTitle)
{
  if (aFile != null)
  {
    if (aFile.SheetCount > 0)
    {
      for (var i = 0; i < aFile.SheetCount; i++)
      {
        if (aFile.SheetByIndex(i).Title == aTitle)
        {
          return true;
        }
      }
      return false;
    }
    else
    {
      return false;
    }
  }
}

function ExcelExample()
{
  var fileName = "C:\\temp\\DataStorage.xlsx"
  var sheetName = "Sheet1";
  var rowIndex;

  // Set data to write into the file
  var currentTime = aqDateTime.Now();
  var cpu = Sys.CPU;

  if (aqFileSystem.Exists(fileName))
  {
  // Get the sheet of the Excel file
  var excelFile = Excel.Open(fileName);

  // Use the SheetWithTitleExists helper routine
  // to check if the sheet with the specified title exists in the file
  if (SheetWithTitleExists(excelFile, sheetName))
  {
      var excelSheet = excelFile.SheetByTitle(sheetName);
      rowIndex = excelSheet.RowCount;
      rowIndex++;
  }
  else
  {
      Log.Message(aqString.Format("The %s sheet does not exist and will be added.", sheetName));
      var excelSheet = excelFile.AddSheet(sheetName);
      rowIndex = 1;
  }
  // Write data into the file
  excelSheet.Cell("A", rowIndex).Value = aqDateTime.Now();
  excelSheet.Cell(2, rowIndex).Value = Sys.CPU;

  // Save the file to apply the changes
  excelFile.Save();

  // Save the file with another name
  // fileName = "C:\\temp\\DataStorage_new.xlsx";
  // excelFile.SaveAs(fileName);
  }
  else
  {
    Log.Error(aqString.Format("The %s file does not exist.", fileName));
  }

}

Python

# A helper routine
# Check if the Excel file has a sheet with the specified title
def SheetWithTitleExists(aFile, aTitle):
  if (aFile != None):
    if (aFile.SheetCount > 0):
      for i in range (0, aFile.SheetCount - 1):
        if (aFile.SheetByIndex[i].Title == aTitle):
          return True
      return False
    else:
      return False

def ExcelExample():
  fileName = "C:\\temp\\DataStorage.xlsx"
  sheetName = "Sheet1"

  # Set data to write into the file
  currentTime = aqDateTime.Now()
  cpu = Sys.CPU

  if (aqFileSystem.Exists(fileName)):
    # Get the sheet of the Excel file
    excelFile = Excel.Open(fileName)

    # Use the SheetWithTitleExists helper routine 
    # to check if the sheet with the specified title exists in the file
    if (SheetWithTitleExists(excelFile, sheetName)):
      excelSheet = excelFile.SheetByTitle[sheetName]
      rowIndex = excelSheet.RowCount + 1
    else:
      Log.Message(aqString.Format("The %s sheet does not exist and will be added.", sheetName))
      excelSheet = excelFile.AddSheet(sheetName)
      rowIndex = 1
    # Write data into the file
    excelSheet.Cell["A", rowIndex].Value = aqDateTime.Now()
    excelSheet.Cell[2, rowIndex].Value = Sys.CPU

    # Save the file to apply the changes
    excelFile.Save()

    # Save the file with another name
    # fileName = "C:\\temp\\DataStorage_new.xlsx"
    # excelFile.SaveAs(fileName)
  else:
    Log.Error(aqString.Format("The %s file does not exist.", fileName))

VBScript

Sub ExcelExample
  Dim excelFile, excelSheet, valueA, valueB, valueC, rowIndex
  
  ' Get the sheet of the Excel file
  Set excelFile = Excel.Open("C:\temp\DataStorageExcel.xlsx")
  Set excelSheet = excelFile.SheetByTitle("Sheet1")
  
  ' Read data from the Excel file
  valueA = excelSheet.Cell("A", 3).Value
  valueB = excelSheet.Cell(2, 3).Value
  valueС = excelSheet.CellByName("C3").Value
  
  ' Write the obtained data into a new row of the file
  rowIndex = excelSheet.RowCount + 1
  excelSheet.Cell("A", rowIndex).Value = valueA
  excelSheet.Cell(2, rowIndex).Value = valueB
  excelSheet.Cell("C", rowIndex).Value = valueC
  
  ' Save the file to apply the changes
  excelFile.Save()
  
  ' Save the file with another name
  ' excelFile.SaveAs("C:\temp\DataStorageExcel_new.xlsx")
End Sub

DelphiScript

// A helper routine
// Check if the Excel file has a sheet with the specified title
function SheetWithTitleExists(aFile, aTitle);
var i;
begin
  Result : = false;
  if aFile <> nil then
  begin
    if aFile.SheetCount > 0 then
    begin
      for i := 0 to aFile.SheetCount - 1 do
      begin
        if (aFile.SheetByIndex(i).Title = aTitle) then
        begin
          Result := true;
          break;
        end;
      end;
    end;
  end;
end;

procedure ExcelExample();
var fileName, sheetName, rowIndex, currentTime, cpu, excelFile, excelSheet;
begin
  fileName := 'C:\\temp\\DataStorage.xlsx';
  sheetName := 'Sheet1';

  // Set data to write into the file
  currentTime := aqDateTime.Now();
  cpu := Sys.CPU;

  if aqFileSystem.Exists(fileName) then
  begin
  // Get the sheet of the Excel file
  excelFile := Excel.Open(fileName);

  // Use the SheetWithTitleExists helper routine
  // to check if the sheet with the specified title exists in the file
  if SheetWithTitleExists(excelFile, sheetName) then
  begin
      excelSheet := excelFile.SheetByTitle(sheetName);
      rowIndex := excelSheet.RowCount + 1;
  end
  else
  begin
      Log.Message(aqString.Format('The %s sheet does not exist and will be added.', sheetName));
      excelSheet := excelFile.AddSheet(sheetName);
      rowIndex := 1;
  end;
  // Write data into the file
  excelSheet.Cell('A', rowIndex).Value := aqDateTime.Now();
  excelSheet.Cell(2, rowIndex).Value := Sys.CPU;

  // Save the file to apply the changes
  excelFile.Save();

  // Save the file with another name
  // fileName := 'C:\\temp\\DataStorage_new.xlsx'";
  // excelFile.SaveAs(fileName);
  end
  else
    Log.Error(aqString.Format('The %s file does not exist.', fileName));


end;

C++Script, C#Script

// A helper routine
// Check if the Excel file has a sheet with the specified title
function SheetWithTitleExists(aFile, aTitle)
{
  if (aFile != null)
  {
    if (aFile["SheetCount"] > 0)
    {
      for (var i = 0; i < aFile["SheetCount"]; i++)
      {
        if (aFile["SheetByIndex"](i)["Title"] == aTitle)
        {
          return true;
        }
      }
      return false;
    }
    else
    {
      return false;
    }
  }
}

function ExcelExample()
{
  var fileName = "C:\\temp\\DataStorage.xlsx"
  var sheetName = "Sheet1";
  var rowIndex;

  // Set data to write into the file
  var currentTime = aqDateTime["Now"]();
  var cpu = Sys["CPU"];

  if (aqFileSystem["Exists"](fileName))
  {
  // Get the sheet of the Excel file
  var excelFile = Excel["Open"](fileName);

  // Use the SheetWithTitleExists helper routine
  // to check if the sheet with the specified title exists in the file
  if (SheetWithTitleExists(excelFile, sheetName))
  {
      var excelSheet = excelFile["SheetByTitle"](sheetName);
      rowIndex = excelSheet["RowCount"];
      rowIndex++;
  }
  else
  {
      Log.Message(aqString.Format("The %s sheet does not exist and will be added.", sheetName));
      var excelSheet = excelFile["AddSheet"](sheetName);
      rowIndex = 1;
  }
  // Write data into the file
  excelSheet["Cell"]("A", rowIndex)["Value"] = aqDateTime["Now"]();
  excelSheet["Cell"](2, rowIndex)["Value"] = Sys["CPU"];

  // Save the file to apply the changes
  excelFile["Save"]();

  // Save the file with another name
  // fileName = "C:\\temp\\DataStorage_new.xlsx";
  // excelFile["SaveAs"](fileName);
  }
  else
  {
    Log["Error"](aqString["Format"]("The %s file does not exist.", fileName));
  }

}

See Also

ExcelFile Object
Working with Microsoft Excel Files

Highlight search results