When testing, you may need to use data from external storages. Microsoft Excel files are among the most popular data storages. TestComplete allows you to retrieve data from Excel files and use them in your tests. Also, you can add data to existing Excel files or create new ones.
Video Tutorial
A file with the XLS file extension is a Microsoft Excel 97-2003 Worksheet file. Later versions of Excel save spreadsheet files in the XLSX format by default. XLS files store data in tables of rows and columns with support for formatted text, images, charts, and more. We can set the page according to our requirements. G) Share: - We use this option to share the file with multiple users and send it over email. To share the file we can follow the steps: - Click on File tab Share. H) Export: - We use this option to export the file in PDF or XPS document and we can change the file type as well.
Macro is program or a code which resides within the Excel file, which can be used to automate a lot of tasks in Excel. Even though macros are mainly associated with Microsoft Excel, this feature is available in various products of the Microsoft Of.
In Keyword Tests
Note: To work with Excel files in your tests, you do not need to have Microsoft Office Excel installed on your computer.
![File File](https://download-basket.giveawayoftheday.com/icons/microsoft-office-onenote_48.png)
Excel will continue to open files in XLSTART, but it will also open files in the alternate location. 7: Text Wrap Long entries often extend past the cell's right border if the cell isn't wide. Microsoft Excel now has a built-in Auto-Recover feature that has replaced the Auto-Save add-in that exists in versions of Excel that are earlier than Microsoft Excel 2002. The Auto-Recover feature saves copies of all open Excel files at a user-definable fixed interval.
Use special operations grouped into the Excel section to read data from Excel cells and write data to them.
When you write data via these operations, you do not need to add additional operations to save changed data. Excel operations will do it automatically.
In Script Tests
If you work with Excel files in script tests, use the
Excel
object. The following script illustrates how to read data from Excel cells and write it into a new row:JavaScript, JScript
function ExcelExample()
{
// Get the sheet of the Excel file
var excelFile = Excel.Open('C:tempDataStorageExcel.xlsx');
var excelSheet = excelFile.SheetByTitle('Sheet1');
// Read data from the Excel file
var valueA = excelSheet.Cell('A', 3).Value;
var valueB = excelSheet.Cell(2, 3).Value;
var valueC = excelSheet.CellByName('C3').Value;
// Write the obtained data into a new row of the file
var 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:tempDataStorageExcel_new.xlsx');
}
{
// Get the sheet of the Excel file
var excelFile = Excel.Open('C:tempDataStorageExcel.xlsx');
var excelSheet = excelFile.SheetByTitle('Sheet1');
// Read data from the Excel file
var valueA = excelSheet.Cell('A', 3).Value;
var valueB = excelSheet.Cell(2, 3).Value;
var valueC = excelSheet.CellByName('C3').Value;
// Write the obtained data into a new row of the file
var 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:tempDataStorageExcel_new.xlsx');
}
Python
VBScript
Sub ExcelExample
Dim excelFile, excelSheet, valueA, valueB, valueC, rowIndex
' Get the sheet of the Excel file
Set excelFile = Excel.Open('C:tempDataStorageExcel.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:tempDataStorageExcel_new.xlsx')
EndSub
Dim excelFile, excelSheet, valueA, valueB, valueC, rowIndex
' Get the sheet of the Excel file
Set excelFile = Excel.Open('C:tempDataStorageExcel.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:tempDataStorageExcel_new.xlsx')
EndSub
DelphiScript
procedure ExcelExample;
var
excelFile, excelSheet, valueA, valueB, valueC, rowIndex;
begin
// Get the sheet of the Excel file
excelFile := Excel.Open('C:tempDataStorageExcel.xlsx');
excelSheet := excelFile.SheetByTitle('Sheet1');
// Read data from the Excel file
valueA := excelSheet.Cell('A', 3).Value;
valueB := excelSheet.Cell(2, 3).Value;
valueC := 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:tempDataStorageExcel_new.xlsx');
end;
var
excelFile, excelSheet, valueA, valueB, valueC, rowIndex;
begin
// Get the sheet of the Excel file
excelFile := Excel.Open('C:tempDataStorageExcel.xlsx');
excelSheet := excelFile.SheetByTitle('Sheet1');
// Read data from the Excel file
valueA := excelSheet.Cell('A', 3).Value;
valueB := excelSheet.Cell(2, 3).Value;
valueC := 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:tempDataStorageExcel_new.xlsx');
end;
C++Script, C#Script
function ExcelExample()
{
// Get the sheet of the Excel file
var excelFile = Excel['Open']('C:tempDataStorageExcel.xlsx');
var excelSheet = excelFile['SheetByTitle']('Sheet1');
// Read data from the Excel file
var valueA = excelSheet['Cell']('A', 3)['Value'];
var valueB = excelSheet['Cell'](2, 3)['Value'];
var valueC = excelSheet['CellByName']('C3')['Value'];
// Write the obtained data into a new row of the file
var 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:tempDataStorageExcel.xlsx');
}
{
// Get the sheet of the Excel file
var excelFile = Excel['Open']('C:tempDataStorageExcel.xlsx');
var excelSheet = excelFile['SheetByTitle']('Sheet1');
// Read data from the Excel file
var valueA = excelSheet['Cell']('A', 3)['Value'];
var valueB = excelSheet['Cell'](2, 3)['Value'];
var valueC = excelSheet['CellByName']('C3')['Value'];
// Write the obtained data into a new row of the file
var 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:tempDataStorageExcel.xlsx');
}
Checkpoints
To validate data in Excel files, you can use the Excel checkpoint. It allows you to compare an entire Excel file or a selected sheet with the baseline Excel file.
For this purpose, use the Excel Checkpoint operation or the
Files.Excel_File_Name.CheckExcelWorkbook
method in your keyword or script tests, respectively.Alternatively, you can iterate through your Excel file and compare the desired data via
if � then � else
statements and comparison operators.Common Tasks
Keeweb android. Typically, in TestComplete, you need to do the following tasks with Excel files:
Remarks
The Microsoft Excel File In General Is A Set Of One
- Excel files secured with passwords are not supported.
- Target Excel files must not be open in third-party programs during test executions.