ExcelFile.AddSheet Method

Applies to TestComplete 14.71, last modified on April 22, 2021

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 the current Excel file.

Declaration

ExcelFile.AddSheet(SheetTitle)

SheetTitle [in]    Required    String    
Result An ExcelSheet object

Applies To

The method is applied to the following object:

Parameters

The method has the following parameter:

SheetTitle

A title of the sheet that will be added.

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:

JavaScript, JScript

function ExcelExample()
{
  // Get the data that will be written into an Excel file
  var curTime = aqDateTime.Now();
  var cpu = Sys.CPUUsage;
  
  var fileName = "c:\\temp\\MyFile.xlsx";
  var excelFile;
  var excelSheet;
  
  if (aqFile.Exists(fileName))
  {
    // Open the existing Excel file
    excelFile = Excel.Open(fileName);
    excelSheet = excelFile.SheetByIndex(0);
    
    // Write the data into a new row of the existing file
    var rowIndex = excelSheet.RowCount + 1;
    excelSheet.Cell("A", rowIndex).Value = curTime;
    excelSheet.Cell(2, rowIndex).Value = cpu;
  }
  else
  {
    // Create a new Excel file
    excelFile = Excel.Create(fileName);
    excelSheet = excelFile.AddSheet("Sheet1");
    
    // Write the data into the first row of the created file
    excelSheet.Cell("A", 1).Value = curTime;
    excelSheet.Cell(2, 1).Value = cpu;
  }

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

Python

def ExcelExample():

  # Get the data that will be written into an Excel file
  curTime = aqDateTime.Now()
  cpu = Sys.CPUUsage
  
  fileName = "c:\\temp\\MyFile.xlsx"

  if  (aqFile.Exists(fileName)):
    # Open the existing Excel file
    excelFile = Excel.Open(fileName)
    excelSheet = excelFile.SheetByIndex[0]
    
    # Write the data into a new row of the existing file
    rowIndex = excelSheet.RowCount + 1
    excelSheet.Cell["A", rowIndex].Value = curTime
    excelSheet.Cell[2, rowIndex].Value = cpu

  else:
    # Create a new Excel file
    excelFile = Excel.Create(fileName)
    excelSheet = excelFile.AddSheet("Sheet1")
    
    # Write the data into the first row of the created file
    excelSheet.Cell["A", 1].Value = curTime
    excelSheet.Cell[2, 1].Value = cpu

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

VBScript

Sub ExcelExample
  Dim curTime, cpu, fileName, excelFile, excelSheet, rowIndex

  ' Get the data that will be written into an Excel file
  curTime = aqDateTime.Now()
  cpu = Sys.CPUUsage
  
  fileName = "c:\temp\MyFile.xlsx"
  
  If aqFile.Exists(fileName) Then
    ' Open the existing Excel file
    Set excelFile = Excel.Open(fileName)
    Set excelSheet = excelFile.SheetByIndex(0)
    
    ' Write the data into a new row of the existing file
    rowIndex = excelSheet.RowCount + 1
    excelSheet.Cell("A", rowIndex).Value = curTime
    excelSheet.Cell(2, rowIndex).Value = cpu
    
  Else

    ' Create a new Excel file
    Set excelFile = Excel.Create(fileName)
    Set excelSheet = excelFile.AddSheet("Sheet1")
    
    ' Write the data into the first row of the created file
    excelSheet.Cell("A", 1).Value = curTime
    excelSheet.Cell(2, 1).Value = cpu
  End If

  ' Save the file to apply the changes
  excelFile.Save()
End Sub

DelphiScript

procedure ExcelExample;
var
  curTime, cpu, fileName, excelFile, excelSheet, rowIndex;
begin
  // Get the data that will be written into an Excel file
  curTime := aqDateTime.Now();
  cpu := Sys.CPUUsage;
  
  fileName := 'c:\\temp\\MyFile.xlsx';
  
  if (aqFile.Exists(fileName)) then
    begin
    // Open the existing Excel file
    excelFile := Excel.Open(fileName);
    excelSheet := excelFile.SheetByIndex(0);
    
    // Write the data into a new row of the existing file
    rowIndex := excelSheet.RowCount + 1;
    excelSheet.Cell('A', rowIndex).Value := curTime;
    excelSheet.Cell(2, rowIndex).Value := cpu;
    end
  else
    begin
    // Create a new Excel file
    excelFile := Excel.Create(fileName);
    excelSheet := excelFile.AddSheet('Sheet1');
    
    // Write the data into the first row of the created file
    excelSheet.Cell('A', 1).Value := curTime;
    excelSheet.Cell(2, 1).Value := cpu;
    end;

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

C++Script, C#Script

function ExcelExample()
{
  // Get the data that will be written into an Excel file
  var curTime = aqDateTime["Now"]();
  var cpu = Sys["CPUUsage"];
  
  var fileName = "c:\\temp\\MyFile.xlsx";
  var excelFile;
  var excelSheet;
  
  if (aqFile["Exists"](fileName))
  {
    // Open the existing Excel file
    excelFile = Excel["Open"](fileName);
    excelSheet = excelFile["SheetByIndex"](0);
    
    // Write the data into a new row of the existing file
    var rowIndex = excelSheet["RowCount"] + 1;
    excelSheet["Cell"]("A", rowIndex)["Value"] = curTime;
    excelSheet["Cell"](2, rowIndex)["Value"] = cpu;
  }
  else
  {
    // Create a new Excel file
    excelFile = Excel["Create"](fileName);
    excelSheet = excelFile["AddSheet"]("Sheet1");
    
    // Write the data into the first row of the created file
    excelSheet["Cell"]("A", 1)["Value"] = curTime;
    excelSheet["Cell"](2, 1)["Value"] = cpu;
  }

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

See Also

ExcelFile Object
Working with Microsoft Excel Files

Highlight search results