😎
😎
😎
😎
Intruder
Twitter
Search…
About Shantanu Khandelwal
Reporting
Excel Sheet to Word Report by PowerShell
Ghostwriter - Add report type
Red Team
HTTPS C2 Done Right NGINX
Domain Front
GoLang
ShellCode Injection
PowerShell
Database
Dealing with the Errors
Setting Up OPENVAS in KALI 2020.3
Phishing
Connecting GoPhish with Office365
SharpLoginPrompt - Success and a Curious Case
Gophish MODs
Talks I Like
Talks Worth Checking Out
Powered By GitBook
Excel Sheet to Word Report by PowerShell
Report writing from Excel Sheet to Word using 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
​
Previous
About Shantanu Khandelwal
Next - Reporting
Ghostwriter - Add report type
Last modified 1yr ago
Copy link
Contents
Introduction
Parsing Excel With PowerShell
Making the Word Template
Running The Script
Making the Final Report
Conclusion