Test Reporting - Fill out an Excel Spreadsheet using PowerShell

PowerShell Help
1

I’ve created an .xml file that will serve as a test report template for an automated performance test. The test results are saved to a text log file.

I have written a PowerShell script that reads the log file and obtains the execution times for each test case and saves them in an array. Next, the script needs to write each of these values into the cells of a specific column in the spreadsheet. Each test case corresponds to a documented requirement. The test cases are executed in the order they’re listed in the .xml file, so the mapping has one-to-one correspondence. All I need to do is get the values out of the PowerShell array and into the spreadsheet.

example test report
example test report1936×1048 269 KB

I’m not sure how to do this with PowerShell. I’ve looked at the ImportExcel module, but I get the impression it was made to generate spreadsheets from scratch, not to edit a template.

2

Trevor,
Welcome to the forum. :wave:t4:

Waht have you tried so far?

Why do you think so? Of course you can import an excel file as the name of module actually indicates. And when you have imported the data you can alter them.

BTW:

That sounds like an overly complex task if you do it by hand. :wink: There are easier/simpler formats for handling config files like JSON or even PowerShell data files. For these file formats you can use Import-PowerShellDataFile or a combination of Get-Content and ConvertFromJson

3

I think I need to import the Excel file as a COM object, but I’m not sure how to do that. New-Object -ComObject … but passing this command the file path to the template .xlsx file yields an error. I’m new to Excel, so I don’t even know how point at the right column from the script.

4

What’s wrong with Import-Excel?

You may run

Get-Command -Module importexcel

to see all the included cmdlets in the module ImportExcel.

5

This is what I have so far, but it doesn’t seem to be working.

$myLog = Get-Content "C:\Logs\TEST\example test log.txt"
$casetime = @()
$regex = "^\d+\.?\d*$"
$myTemplate = "C:\Users\trevor.judd\Desktop\ExampleTestReport.xlsx"
$worksheet = "Sheet1"

foreach($line in $myLog)
{
    $split = $line.Split()
    $split = $split.TrimEnd('s')
    $casetime = ($split | ? {$_ -match $regex})

    Write-Host $casetime
}


foreach($time in $casetime)
{
    $time | Export-Excel $myTemplate -Append | Set-ExcelColumn -Worksheet $worksheet -Column 5

}

Although I get no errors.

6

That’s not helpful at all. Please keep in mind - we cannot see your screen and we cannot read your mind.

How does your log file look like and what is what you want tha code to do? And where do you import your existing Excel sheet?

When you share sample data please post them in plain text formatted as code so we can copy and play with it easily.

7

Test Log Example

Start: 2022-08-01 19:18:39 - Auto_Performance_Test

TestCase_Import                          SUCCESS                             19.851s

TestCase_VerifySOMETHING_begin           SUCCESS                              1.339s

TestCase_VerifySOMETHING_complete        SUCCESS                              0.586s

TestCase_CheckForSOMETHING               SUCCESS                              0.416s

TestCase_Delete                          SUCCESS                              2.131s

TestCase_Copy                            SUCCESS                              0.510s

End: 2022-08-03 19:20:21 - Auto_Performance_Test

The execution times are stored in an array and need to be placed in the “Test Case Execution Time” column of the Excel template shown in the original post and saved as new file.

I have been going off of the examples shown here: Export and import to and from Excel with the PowerShell module ImportExcel – 4sysops, particularly the section “Adding data to an existing spreadsheet.”

Do I need to add an Import-Excel statement to $myTemplate variable? Why?

8

You like to make it hard, don’t you? :smirk: Is that really the original format of your log file?

If you want to read the Excel sheet … of course. :man_shrugging:t4: Otherwise you define a variable with the path to an Excel sheet.

9

Yes, that is the original format of the test logs.

And I don’t really need to read from the .xlsx file. I need to write to it and then save it as a new file; hence, why I thought Export-Excel was sufficient.

I don’t understand why I’m “making this hard.” Please explain.

10

I understood that you want to combine already existing data with new ones. And in this case you have to read the already existing data to be able to combine them with the new ones.

Because we have to ask every little bit of information explicitly. … and the format of your log looks really weird. I am curious - what kind of application writes such a log file?

11

It’s not an application, per se. It is a typical Selenium test log generated after an automated UI test. I say typical in that it’s the default text file created by the Selenium test reporter.

12

… ok … let’s go …

Since you did not share your Excel sheet I had to create my own one to test and to show … first we generate the data:

$XlsxInputData = @'
"SSSREQT ID","Test Case","Test Case Execution Time"
"Req 1","TestCase_Import",
"Req 2","TestCase_VerifySOMETHING_begin",
"Req 3","TestCase_VerifySOMETHING_complete",
"Req 4","TestCase_CheckForSOMETHING",
"Req 5","TestCase_Delete",
"Req 6","TestCase_Copy",
'@ | 
    ConvertFrom-Csv

You may check the data by simply outputting them to the console

$XlsxInputData

But we need them in an Excel file, right? :wink: (You may adapt the path to your requirements)

$InputExcelFile = 'D:\sample\InputExcelFile.xlsx'
$XlsxInputData | Export-Excel -Worksheet 'Sheet1' -Path $InputExcelFile

Now we have an Excel file we can work with. Check how it looks in Excel. :point_up_2:t4:

Ok … now your log file … I used the data you posted and turned them into something usable: (Again … you may adapt the path to your requirements)

$myLog = Get-Content -Path 'D:\sample\example test log.txt'
$NewData = 
$myLog | 
    Select-Object -Skip 2 -First 11 | 
        Where-Object {$_} |
            ForEach-Object {$_ -replace '\s+',','}|
                ConvertFrom-Csv -Header TestCase, Status, Time

When you output them now it looks like this:

PS>$NewData        

TestCase                          Status  Time
--------                          ------  ----
TestCase_Import                   SUCCESS 19.851s
TestCase_VerifySOMETHING_begin    SUCCESS 1.339s
TestCase_VerifySOMETHING_complete SUCCESS 0.586s
TestCase_CheckForSOMETHING        SUCCESS 0.416s
TestCase_Delete                   SUCCESS 2.131s
TestCase_Copy                     SUCCESS 0.510s

Now we need to combine the data from the existing Excel file with the new data from the log file, right?

$NewCombinedData =
foreach ($DataSet in $ExistingData) {
    $TestCaseMatch = $NewData | 
        Where-Object {$_.'TestCase' -eq $DataSet.'Test Case'}
    $DataSet.'Test Case Execution Time' = $TestCaseMatch.Time
    $DataSet
}

With this code we determine which one of the new data lines has the same content in the cell ‘TestCase’ like the existing data from the Excel file in the cell ‘Test Case’ … !! notice the different header names.
Then we assign the value of the cell ‘Time’ from the new data from the log file to the cell ‘Test Case Execution Time’ of the existing data from the input Excel file and output the whole data set to be able to catch it in a variable for later use.

Now the combined data looks like this:

PS>$NewCombinedData

SSSREQT ID Test Case                         Test Case Execution Time
---------- ---------                         ------------------------
Req 1      TestCase_Import                   19.851s
Req 2      TestCase_VerifySOMETHING_begin    1.339s
Req 3      TestCase_VerifySOMETHING_complete 0.586s
Req 4      TestCase_CheckForSOMETHING        0.416s
Req 5      TestCase_Delete                   2.131s
Req 6      TestCase_Copy                     0.510s

At the end you can export these new combined data to a new Excel file

$OutputExcelFile = 'D:\sample\OutputExcelFile.xlsx'
$NewCombinedData | 
    Export-Excel -Path $OutputExcelFile -Worksheet 'Sheet1'
13

I’m sorry if I’ve not expressed my needs very well.

This is all completely unnecessary.

The regular expression in my code gets everything I need out of the text file. I just need to put that data in the template spreadsheet, one cell at a time, in the proper column. I don’t need to make a new spreadsheet from scratch. I don’t need to do any fancy parsing. I need to put what’s in the array into the spreadsheet and save the spreadsheet under a different filename. That’s it.

You have reinvented the wheel here.

I cannot share the Excel file because I’m not authorized to attach anything other than image files.

14

No. I just showed you how you can accomplish what you asked for. :point_up_2:t4:

I’m afraid it does not work as you might think it does. :wink:

1 Like
15

Your code does not work. The output Excel file is empty. I’d say there’s a problem somewhere in the foreach loop. When I try to print the contents of $NewCombinedData to the prompt, nothing happens. It’s an empty variable.

16

Well. With the data you shared and the ones I created for demo purposes the code works here as expected. :wink:

1 Like
17

If you’re wanting to keep the format of your template then I would recommend figuring out how to build it programmatically first. The importexcel module is amazing so if you can do it with this tool, great. You can simply build a new one with the values.

If not, then I’m afraid you will be restricted to using COM to manipulate the existing file. It may be possible with importexcel to open and save an existing file while retaining the format, but this I do not know. If you’re unable to figure this out you may want to reach out to the author of the module, Doug Finke, and ask him.

1 Like
18

The Excel sheet you shared as screenshot shows that you have some connected cells. I’d recommend to use a more regular layout without conneccted cells. That would make your life a lot easier. :wink: