Excel Sheet to Word Report by PowerShell

Introduction
Report writing is one of the most tedious tasks when it comes to a Penetration Tester's life. It's unavoidable, and I think the Penetration Test's quality has a lot to do with the report. I cannot emphasize enough on the report. The report is an integral part of the Penetration Test. With that being said, let's move forward and do some PowerShell Magic ;)
A lot of organizations prepare an excel sheet to track the list of vulnerabilities. This sheet may also be shared with the client as an interim report. Let's calls this excel sheet as "Vuln Sheet". Vuln Sheet contains a list of vulnerabilities discovered, its description, the vulnerability impact, affected hosts, and remediation steps. If you look closely to this "Vuln Sheet" it has mostly all of the components which a tester uses to generate a Penetration Test report. Ofcourse, the report has a lot more elements such as scope, executive summary, disclaimers etc. but if we are just focusing on the "Findings" section of the report, I guess the "Vuln Sheet" covers it all.
PowerShell has a lot of capability in reading and writing both Word and Excel. So without wasting time, lets take a deep dive into parsing excel sheet for the vulnerabilities and writing a word document from the parsed contents.
Parsing Excel With PowerShell
Following is how my Excel Sheet of Vulnerabilities look like
Sheet of Vulnerabilities ("VulnSheet.xlsx")
Open Excel Sheet with PowerShell with the help of COM.
1
$objExcel = New-Object -ComObject Excel.Application
2
$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")
Copied!
Open specific sheet of the excel sheet
1
$SheetNames = $WorkBook.sheets | Select-Object -Property Name
2
$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")
Copied!
Get the Table Range
1
$WorksheetRange = $workSheet.UsedRange
2
$RowCount = $WorksheetRange.Rows.Count
3
$ColumnCount = $WorksheetRange.Columns.Count
4
Write-Host "RowCount:" $RowCount
5
Write-Host "ColumnCount" $ColumnCount
Copied!
Initialize the column variables in Excel
1
$ColHeading = 0
2
$ColObservation = 0
3
$ColImplication = 0
4
$ColRecommendation = 0
5
$ColAffectedResources = 0
6
$ColSeverity = 0
7
$hash = @{} # I'll tell u why this is needed in a sec
Copied!
Assign values to column variables
1
for($i=1;$i -le $ColumnCount;$i+=1){
2
    $ColHead = $WorkSheet.cells.Item(1, $i).text
3
    if($ColHead -like "Heading"){
4
        Write-Host "[+]Heading Column Found"
5
        $ColHeading = $i
6
    }
7
    if($ColHead -like "Observation"){
8
        Write-Host "[+]Observation Column Found"
9
        $ColObservation = $i
10
    }
11
    if($ColHead -like "Implication"){
12
        Write-Host "[+]Implication Column Found"
13
        $ColImplication = $i
14
    }
15
    if($ColHead -like "Recommendation"){
16
        Write-Host "[+]Recommendation Column Found"
17
        $ColRecommendation = $i
18
    }
19
    if($ColHead -like "Affected Resources"){
20
        Write-Host "[+]Affected Resources Column Found"
21
        $ColAffectedResources = $i
22
    }
23
    if($ColHead -like "Severity"){
24
        Write-Host "[+]Severity Column Found"
25
        $ColSeverity = $i
26
    }
27
​
28
}
29
​
30
Write-Host "`r`n"
31
Write-Host "Printing Column Status"
32
Write-Host "Heading:" $ColHeading
33
Write-Host "Observation:" $ColObservation
34
Write-Host "Implication:" $ColImplication
35
Write-Host "Recommendation:" $ColRecommendation
36
Write-Host "Affected Resources:" $ColAffectedResources
37
Write-Host "Severity:" $ColSeverity
Copied!
We can now start extracting text from the columns. We are going to introduce a lot of functions in the following loop. Function "MakeWordReport" requires a Word Template. I'll go thorough that in later sections of this post.
1
# Extracting Text Now
2
​
3
for($i=2;$i -le $RowCount; $i+=1){
4
    $TextHeading = $WorkSheet.cells.Item($i, $ColHeading).text
5
    $TextObservation = $WorkSheet.cells.Item($i, $ColObservation).text 
6
    $TextImplication = $WorkSheet.cells.Item($i, $ColImplication).text
7
    $TextRecommendation = $WorkSheet.cells.Item($i, $ColRecommendation).text
8
    $TextAffectedResources = $WorkSheet.cells.Item($i, $ColAffectedResources).text
9
    $TextSeverity = $WorkSheet.cells.Item($i, $ColSeverity).text
10
    WorkOnHeading $TextHeading
11
    WorkOnObservation $TextObservation
12
    WorkOnImplication $TextImplication
13
    WorkOnRecommendation $TextRecommendation
14
    WorkOnAffectedResources $TextAffectedResources
15
    WorkOnSeverity $TextSeverity
16
    $hash # Just Printing HashTable 
17
    MakeWordReport $i $hash 
18
    $hash = @{}
19
}
Copied!
Let's define the functions I proposed in the above for loop.
1
Function MakeWordReport($index,$hash){
2
    $template = "C:\Users\tempuser\Documents\TemplateFinding.docx"
3
    $wd = New-Object –comobject Word.Application
4
    $doc=$wd.documents.Add($template)
5
    $newfile="C:\Users\tempuser\Documents\file_$index.docx"
6
    foreach($key in $hash.keys){
7
        $objrange = $doc.Bookmarks.Item($key).Range 
8
        $objrange.Text = $hash[$key]
9
    }
10
    $doc.SaveAs([ref]$newfile)
11
    $doc.Close()
12
    $wd.Quit()
13
}
14
​
15
function WorkOnSeverity($RawSeverity){
16
    $Severity = $RawSeverity.trim()
17
    $hash["Severity"] = $Severity
18
}
19
    
20
function WorkOnRecommendation($RawRecommendation){
21
    if($RawRecommendation -like "*Reference:*"){
22
        $temp =  $RawRecommendation -split "Reference:",2
23
        $Recommendation = $temp[0].trim()
24
        $Reference = $temp[1].Trim()
25
    }
26
    else{
27
        $Recommendation = $RawRecommendation.Trim()
28
        $Reference = ""
29
    }
30
​
31
    $hash["Recommendation"] = $Recommendation
32
    $hash["Reference"] = $Reference
33
}
34
​
35
function WorkOnAffectedResources($RawAffectedResources){
36
    $AffectedResources = $RawAffectedResources.Trim()
37
    $hash["AffectedResources"] = $AffectedResources
38
}
39
​
40
function WorkOnImplication($RawImplication){
41
    $Implication = $RawImplication.Trim()
42
    $hash["Implication"] = $Implication
43
​
44
}
45
​
46
function WorkOnObservation($RawObservation){
47
    $Observation = $RawObservation.trim()
48
    $hash["Observation"] = $Observation
49
}
50
​
51
function WorkOnHeading($RawHeading){
52
    $Heading = $RawHeading.trim()
53
    $hash["Heading"] = $Heading
54
}
Copied!
We have now defined the WorkOn* functions in PowerShell. Most functions defined are very basic here but I want to draw your attention to the WorkOnRecommendation fuction. Its not a basic function. Basically here i wanted to show that you can do string manipulation here. This is just an example.
There is one more importtant thing for you to note here. We here see that we are populating our hashtable $hash here. We are using key such as "Observation", "AffectedResources" etc. This key is important because we will use this key while making the Micorsoft Word Template
In the function MakeWordReport you can see that we are running a foreach loop on the hashtable keys. In the loop we are replacing the bookmarks predefined with those specific keys. 
  By the way final script as following 
1
Function MakeWordReport($index,$hash){
2
    $template = "C:\Users\tempuser\Documents\TemplateFinding.docx"
3
    $wd = New-Object –comobject Word.Application
4
    $doc=$wd.documents.Add($template)
5
    $newfile="C:\Users\tempuser\Documents\file_$index.docx"
6
    foreach($key in $hash.keys){
7
        $objrange = $doc.Bookmarks.Item($key).Range 
8
        $objrange.Text = $hash[$key]
9
    }
10
    $doc.SaveAs([ref]$newfile)
11
    $doc.Close()
12
    $wd.Quit()
13
}
14
​
15
function WorkOnSeverity($RawSeverity){
16
    $Severity = $RawSeverity.trim()
17
    $hash["Severity"] = $Severity
18
}
19
    
20
function WorkOnRecommendation($RawRecommendation){
21
    if($RawRecommendation -like "*Reference:*"){
22
        $temp =  $RawRecommendation -split "Reference:",2
23
        $Recommendation = $temp[0].trim()
24
        $Reference = $temp[1].Trim()
25
    }
26
    else{
27
        $Recommendation = $RawRecommendation.Trim()
28
        $Reference = ""
29
    }
30
​
31
    $hash["Recommendation"] = $Recommendation
32
    $hash["Reference"] = $Reference
33
}
34
​
35
function WorkOnAffectedResources($RawAffectedResources){
36
    $AffectedResources = $RawAffectedResources.Trim()
37
    $hash["AffectedResources"] = $AffectedResources
38
}
39
​
40
function WorkOnImplication($RawImplication){
41
    $Implication = $RawImplication.Trim()
42
    $hash["Implication"] = $Implication
43
​
44
}
45
​
46
function WorkOnObservation($RawObservation){
47
    $Observation = $RawObservation.trim()
48
    $hash["Observation"] = $Observation
49
}
50
​
51
function WorkOnHeading($RawHeading){
52
    $Heading = $RawHeading.trim()
53
    $hash["Heading"] = $Heading
54
}
55
​
56
$objExcel = New-Object -ComObject Excel.Application
57
$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")
58
$SheetNames = $WorkBook.sheets | Select-Object -Property Name
59
$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")
60
$WorksheetRange = $workSheet.UsedRange
61
$RowCount = $WorksheetRange.Rows.Count
62
$ColumnCount = $WorksheetRange.Columns.Count
63
Write-Host "RowCount:" $RowCount
64
Write-Host "ColumnCount" $ColumnCount
65
$ColHeading = 0
66
$ColObservation = 0
67
$ColImplication = 0
68
$ColRecommendation = 0
69
$ColAffectedResources = 0
70
$ColSeverity = 0
71
$hash = @{} # I'll tell u why this is needed in a sec
72
​
73
​
74
for($i=1;$i -le $ColumnCount;$i+=1){
75
    $ColHead = $WorkSheet.cells.Item(1, $i).text
76
    if($ColHead -like "Heading"){
77
        Write-Host "[+]Heading Column Found"
78
        $ColHeading = $i
79
    }
80
    if($ColHead -like "Observation"){
81
        Write-Host "[+]Observation Column Found"
82
        $ColObservation = $i
83
    }
84
    if($ColHead -like "Implication"){
85
        Write-Host "[+]Implication Column Found"
86
        $ColImplication = $i
87
    }
88
    if($ColHead -like "Recommendation"){
89
        Write-Host "[+]Recommendation Column Found"
90
        $ColRecommendation = $i
91
    }
92
    if($ColHead -like "Affected Resources"){
93
        Write-Host "[+]Affected Resources Column Found"
94
        $ColAffectedResources = $i
95
    }
96
    if($ColHead -like "Severity"){
97
        Write-Host "[+]Severity Column Found"
98
        $ColSeverity = $i
99
    }
100
​
101
}
102
​
103
Write-Host "`r`n"
104
Write-Host "Printing Column Status"
105
Write-Host "Heading:" $ColHeading
106
Write-Host "Observation:" $ColObservation
107
Write-Host "Implication:" $ColImplication
108
Write-Host "Recommendation:" $ColRecommendation
109
Write-Host "Affected Resources:" $ColAffectedResources
110
Write-Host "Severity:" $ColSeverity
111
​
112
​
113
for($i=2;$i -le $RowCount; $i+=1){
114
    $TextHeading = $WorkSheet.cells.Item($i, $ColHeading).text
115
    $TextObservation = $WorkSheet.cells.Item($i, $ColObservation).text 
116
    $TextImplication = $WorkSheet.cells.Item($i, $ColImplication).text
117
    $TextRecommendation = $WorkSheet.cells.Item($i, $ColRecommendation).text
118
    $TextAffectedResources = $WorkSheet.cells.Item($i, $ColAffectedResources).text
119
    $TextSeverity = $WorkSheet.cells.Item($i, $ColSeverity).text
120
    WorkOnHeading $TextHeading
121
    WorkOnObservation $TextObservation
122
    WorkOnImplication $TextImplication
123
    WorkOnRecommendation $TextRecommendation
124
    WorkOnAffectedResources $TextAffectedResources
125
    WorkOnSeverity $TextSeverity
126
    $hash # Just Printing HashTable 
127
    MakeWordReport $i $hash 
128
    $hash = @{}
129
}
Copied!
Making the Word Template
To make word template, just modify your existing word template to have bookmarks at places you want your text in. To place a BookMark click insert and then Bookmark. If you are still unsure, follow the screenshots below
Step 1: Simple Word Template
Step 1.1: Locate Bookmark option
Step 2: Select the Text and click Bookmark option
Step 3: Type the bookmark name. MAKE SURE IT MATCHES TO YOUR KEY IN THE HASHTABLE(@hash)
Final Look of the Word Document
Running The Script
Before running the script check the variables such as 
1
$template = "C:\Users\tempuser\Documents\TemplateFinding.docx"
2
$newfile="C:\Users\tempuser\Documents\file_$index.docx"
3
$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")
4
$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")
Copied!
Once you run the script, the output looks like below 
1
RowCount: 5
2
ColumnCount 7
3
[+]Heading Column Found
4
[+]Observation Column Found
5
[+]Implication Column Found
6
[+]Recommendation Column Found
7
[+]Affected Resources Column Found
8
[+]Severity Column Found
9
​
10
​
11
Printing Column Status
12
Heading: 2
13
Observation: 3
14
Implication: 4
15
Recommendation: 5
16
Affected Resources: 6
17
Severity: 7
18
​
19
Name                           Value                                                                                                                   
20
----                           -----                                                                                                                   
21
Recommendation                 Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...
22
AffectedResources              Affected Host 1...                                                                                                      
23
Observation                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...
24
Heading                        This is Heading 1                                                                                                       
25
Implication                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....
26
Severity                       Critical                                                                                                                
27
Reference                      www.google.com...                                                                                                       
28
Recommendation                 Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...
29
AffectedResources              Affected Host 1...                                                                                                      
30
Observation                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...
31
Heading                        This is Heading 2                                                                                                       
32
Implication                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....
33
Severity                       High                                                                                                                    
34
Reference                                                                                                                                              
35
Recommendation                 Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...
36
AffectedResources              Affected Host 1...                                                                                                      
37
Observation                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...
38
Heading                        This is Heading 3                                                                                                       
39
Implication                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....
40
Severity                       Medium                                                                                                                  
41
Reference                                                                                                                                              
42
Recommendation                 Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...
43
AffectedResources              Affected Host 1...                                                                                                      
44
Observation                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...
45
Heading                        This is Heading 4                                                                                                       
46
Implication                    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....
47
Severity                       Low                                                                                                                     
48
Reference                                 
Copied!
And you will have the files like this at the destination defined in $newfile
Generated Isolated findings files
Making the Final Report
So the final task is to combine all this file_*.docx files. 
Use the following macro to combine the docx. The macro allows multiselect so select all the files you want to merge and click ok.
1
Sub Merge()
2
  Dim dlgFile As FileDialog
3
  Dim nTotalFiles As Integer
4
  Dim nEachSelectedFile As Integer
5
​
6
  Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
7
 
8
  With dlgFile
9
    .AllowMultiSelect = True
10
    If .Show <> -1 Then
11
      Exit Sub
12
    Else
13
      nTotalFiles = .SelectedItems.Count
14
    End If
15
  End With
16
 
17
  For nEachSelectedFile = 1 To nTotalFiles
18
    Selection.InsertFile dlgFile.SelectedItems.Item(nEachSelectedFile)
19
    If nEachSelectedFile < nTotalFiles Then
20
      Selection.InsertBreak Type:=wdPageBreak
21
    Else
22
      If nEachSelectedFile = nTotalFiles Then
23
        Exit Sub
24
      End If
25
    End If
26
  Next nEachSelectedFile
27
End Sub
Copied!
Running the Merge Macro
Select the  file_* using multiselect
Tip: Make a copy of the TemplateFinding.docx, delete everything inside it and run the macro. Doing this will preserve your formatting.
Final Result 
Conclusion
So we are hackers and hackers don't copy paste 😜. Basically, save time copy pasting stuff and use Powershell. I know this is not revolutionary but yeah, its a script I wrote and I found no reference of something similar over internet. Maybe you guys can find it and let me know.  
By the way, for your ease of access, I have uploaded all these scripts and documents on my github for your quick reference. Feel free to change/modify/enhance to fit your needs  
​