ExcelFile.AddSheet Method

Applies to TestComplete 15.45, last modified on December 01, 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 ExcelFile.AddSheet method adds a new sheet with the specified title to an Excel file.

Declaration

ExcelFile.AddSheet(SheetTitle)

SheetTitle [in]    Required    String    
Result An ExcelSheet object

Applies To

The method is applied to the following object:

ExcelFile

Parameters

The method has the following parameter:

SheetTitle

A title of the sheet that will be added.

If a sheet with the specified title already exists in the file, an error will occur.

Result Value

An ExcelSheet object that corresponds to the added sheet. To perform operations over the created sheet, use the methods and properties of the returned object.

Example

The code below demonstrates how you can use the ExcelFile.AddSheet method in your script:

Note: To check if the sheet with the specified title already exists in the file, the sample code uses the SheetWithTitleExists helper routine.

JavaScript
JScript
Python
VBScript
DelphiScript
C++Script, C#Script
Copy Code

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