Forum Discussion

ShahZaib's avatar
ShahZaib
Contributor
3 years ago

Verify Frozen columns in Excel

Hi

 

1. I have frozen a few columns in the MSExcel and I want to verify via script testing how many columns are frozen

2. Similarly, I have marked a few columns as hidden and again I want to verify their visibility

 

Is there any sample script that I can use

 

Thanks

Data-Driven Testing
Desktop Testing
Object Recognition
Script Tests
Variables
  • rraghvani's avatar
    rraghvani
    3 years ago

    For example,

     

    function Test()
{
    var oExec = WshShell.Exec("powershell.exe -Command \"C:\\Temp\\Book2.ps1\" -excelfile \"C:\\Temp\\Book2.xlsx\"");
    while (oExec.Status == 0) {
        aqUtils.Delay(1000);
    }
    while (!oExec.StdOut.AtEndOfStream) {
        Log.Message(oExec.StdOut.ReadLine());
    }
}

     

     

  • ShahZaib's avatar
    ShahZaib
    3 years ago

    Thanks a lot this solves all my problems 

    Thank you once again for all the effort

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    3 years ago

    I doubt this can be done in TestComplete. However, you can call a PowerShell script file from TestComplete, and have appropriate PowerShell script code to retrieve the information you require.

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    3 years ago

    Using your example, I've made a few tweaks.

    Column C is hidden, and TC will output True

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    3 years ago

    Here's the PowerShell script code,

    $excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open('C:\Temp\Book2.xlsx')
$worksheet = $workbook.Worksheets('Sheet1')
Write-Host "Is column 3 hidden?" $worksheet.Columns.Item(3).Hidden
Write-Host "Is row 4 hidden?" $worksheet.Rows.Item(4).Hidden
Write-Host "Is freeze pane enabled?" $excel.ActiveWindow.FreezePanes
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null

     Which checks the Excel spreadsheet,

     

  • ShahZaib's avatar
    ShahZaib
    Contributor
    3 years ago

    Thank you very much for your help this is perfect

    One more question how can I pass a parameter from test complete to Powershell

    for example, in the above script if I want file path to be a variable getting its value from the test complete

    $excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open('C:\Temp\Book2.xlsx')

     

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    3 years ago

    For example,

     

    function Test()
{
    var oExec = WshShell.Exec("powershell.exe -Command \"C:\\Temp\\Book2.ps1\" -excelfile \"C:\\Temp\\Book2.xlsx\"");
    while (oExec.Status == 0) {
        aqUtils.Delay(1000);
    }
    while (!oExec.StdOut.AtEndOfStream) {
        Log.Message(oExec.StdOut.ReadLine());
    }
}

     

     

  • ShahZaib's avatar
    ShahZaib
    Contributor
    3 years ago

    After a few attempts I am able to run the power shell script but I am unable to read the out put from it using the test completely. I have already gone through the documentation but nothing is working for me

    sample code return an empty message

    function GetProcesses()
    {
    var oExec = WshShell.Exec("powershell -File D:\ABS\Untitled1.ps1");
    oExec.StdIn.Close(); // Close standard input before reading output

    // Get PowerShell output
    var strOutput = oExec.StdOut.ReadAll();
    // Trim leading and trailing empty lines
    strOutput = aqString.Trim(strOutput, aqString.stAll);

    // Post PowerShell output to the test log line by line
    aqString.ListSeparator = "\r\n";
    for (var i = 0; i < aqString.GetListLength(strOutput); i++)
    {
    Log.Message(aqString.GetListItem(strOutput,i));
    }
    }

     

     

    PowerShell code

     

    $Path = 'D:\ABS\ABS.xlsx'
    $Excel = New-Object -Com Excel.Application
    $Workbook = $Excel.Workbooks.Open($Path)
    $page = 'PLANT'
    $ws = $Workbook.worksheets | where-object {$_.Name -eq $page}
    $cells = $ws.Columns.Item(9).hidden
    return $cells
    $workbook.Close($true)
    $excel.quit()

  • ShahZaib's avatar
    ShahZaib
    Contributor
    3 years ago

    Thanks a lot this is working like a charm

    One more thing to verify the frozen column in excel I have used the following command 

    $excel = New-Object -Com Excel.Application

    $excel.Visible = $True

    $wb = $Excel.Workbooks.Add()

    $ws = $wb.Worksheets.Add()

    $ws.Activate()

    $ws.Select()

    $excel.Columns.Item(1).Select()

    $excel.ActiveWindow.FreezePanes

    Its always returns true for me, Can you please guide me on this