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
// 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
// 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
// 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
// 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));
}
}