Excel Sheet to Word Report by PowerShell
Report writing from Excel Sheet to Word using PowerShell
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.
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.
$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")
Open specific sheet of the excel sheet
$SheetNames = $WorkBook.sheets | Select-Object -Property Name
$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")
Get the Table Range
$WorksheetRange = $workSheet.UsedRange
$RowCount = $WorksheetRange.Rows.Count
$ColumnCount = $WorksheetRange.Columns.Count
Write-Host "RowCount:" $RowCount
Write-Host "ColumnCount" $ColumnCount
Initialize the column variables in Excel
$ColHeading = 0
$ColObservation = 0
$ColImplication = 0
$ColRecommendation = 0
$ColAffectedResources = 0
$ColSeverity = 0
$hash = @{} # I'll tell u why this is needed in a sec
Assign values to column variables
for($i=1;$i -le $ColumnCount;$i+=1){
$ColHead = $WorkSheet.cells.Item(1, $i).text
if($ColHead -like "Heading"){
Write-Host "[+]Heading Column Found"
$ColHeading = $i
}
if($ColHead -like "Observation"){
Write-Host "[+]Observation Column Found"
$ColObservation = $i
}
if($ColHead -like "Implication"){
Write-Host "[+]Implication Column Found"
$ColImplication = $i
}
if($ColHead -like "Recommendation"){
Write-Host "[+]Recommendation Column Found"
$ColRecommendation = $i
}
if($ColHead -like "Affected Resources"){
Write-Host "[+]Affected Resources Column Found"
$ColAffectedResources = $i
}
if($ColHead -like "Severity"){
Write-Host "[+]Severity Column Found"
$ColSeverity = $i
}
}
Write-Host "`r`n"
Write-Host "Printing Column Status"
Write-Host "Heading:" $ColHeading
Write-Host "Observation:" $ColObservation
Write-Host "Implication:" $ColImplication
Write-Host "Recommendation:" $ColRecommendation
Write-Host "Affected Resources:" $ColAffectedResources
Write-Host "Severity:" $ColSeverity
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.
# Extracting Text Now
for($i=2;$i -le $RowCount; $i+=1){
$TextHeading = $WorkSheet.cells.Item($i, $ColHeading).text
$TextObservation = $WorkSheet.cells.Item($i, $ColObservation).text
$TextImplication = $WorkSheet.cells.Item($i, $ColImplication).text
$TextRecommendation = $WorkSheet.cells.Item($i, $ColRecommendation).text
$TextAffectedResources = $WorkSheet.cells.Item($i, $ColAffectedResources).text
$TextSeverity = $WorkSheet.cells.Item($i, $ColSeverity).text
WorkOnHeading $TextHeading
WorkOnObservation $TextObservation
WorkOnImplication $TextImplication
WorkOnRecommendation $TextRecommendation
WorkOnAffectedResources $TextAffectedResources
WorkOnSeverity $TextSeverity
$hash # Just Printing HashTable
MakeWordReport $i $hash
$hash = @{}
}
Let's define the functions I proposed in the above for loop.
Function MakeWordReport($index,$hash){
$template = "C:\Users\tempuser\Documents\TemplateFinding.docx"
$wd = New-Object –comobject Word.Application
$doc=$wd.documents.Add($template)
$newfile="C:\Users\tempuser\Documents\file_$index.docx"
foreach($key in $hash.keys){
$objrange = $doc.Bookmarks.Item($key).Range
$objrange.Text = $hash[$key]
}
$doc.SaveAs([ref]$newfile)
$doc.Close()
$wd.Quit()
}
function WorkOnSeverity($RawSeverity){
$Severity = $RawSeverity.trim()
$hash["Severity"] = $Severity
}
function WorkOnRecommendation($RawRecommendation){
if($RawRecommendation -like "*Reference:*"){
$temp = $RawRecommendation -split "Reference:",2
$Recommendation = $temp[0].trim()
$Reference = $temp[1].Trim()
}
else{
$Recommendation = $RawRecommendation.Trim()
$Reference = ""
}
$hash["Recommendation"] = $Recommendation
$hash["Reference"] = $Reference
}
function WorkOnAffectedResources($RawAffectedResources){
$AffectedResources = $RawAffectedResources.Trim()
$hash["AffectedResources"] = $AffectedResources
}
function WorkOnImplication($RawImplication){
$Implication = $RawImplication.Trim()
$hash["Implication"] = $Implication
}
function WorkOnObservation($RawObservation){
$Observation = $RawObservation.trim()
$hash["Observation"] = $Observation
}
function WorkOnHeading($RawHeading){
$Heading = $RawHeading.trim()
$hash["Heading"] = $Heading
}
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
Function MakeWordReport($index,$hash){
$template = "C:\Users\tempuser\Documents\TemplateFinding.docx"
$wd = New-Object –comobject Word.Application
$doc=$wd.documents.Add($template)
$newfile="C:\Users\tempuser\Documents\file_$index.docx"
foreach($key in $hash.keys){
$objrange = $doc.Bookmarks.Item($key).Range
$objrange.Text = $hash[$key]
}
$doc.SaveAs([ref]$newfile)
$doc.Close()
$wd.Quit()
}
function WorkOnSeverity($RawSeverity){
$Severity = $RawSeverity.trim()
$hash["Severity"] = $Severity
}
function WorkOnRecommendation($RawRecommendation){
if($RawRecommendation -like "*Reference:*"){
$temp = $RawRecommendation -split "Reference:",2
$Recommendation = $temp[0].trim()
$Reference = $temp[1].Trim()
}
else{
$Recommendation = $RawRecommendation.Trim()
$Reference = ""
}
$hash["Recommendation"] = $Recommendation
$hash["Reference"] = $Reference
}
function WorkOnAffectedResources($RawAffectedResources){
$AffectedResources = $RawAffectedResources.Trim()
$hash["AffectedResources"] = $AffectedResources
}
function WorkOnImplication($RawImplication){
$Implication = $RawImplication.Trim()
$hash["Implication"] = $Implication
}
function WorkOnObservation($RawObservation){
$Observation = $RawObservation.trim()
$hash["Observation"] = $Observation
}
function WorkOnHeading($RawHeading){
$Heading = $RawHeading.trim()
$hash["Heading"] = $Heading
}
$objExcel = New-Object -ComObject Excel.Application
$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")
$SheetNames = $WorkBook.sheets | Select-Object -Property Name
$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")
$WorksheetRange = $workSheet.UsedRange
$RowCount = $WorksheetRange.Rows.Count
$ColumnCount = $WorksheetRange.Columns.Count
Write-Host "RowCount:" $RowCount
Write-Host "ColumnCount" $ColumnCount
$ColHeading = 0
$ColObservation = 0
$ColImplication = 0
$ColRecommendation = 0
$ColAffectedResources = 0
$ColSeverity = 0
$hash = @{} # I'll tell u why this is needed in a sec
for($i=1;$i -le $ColumnCount;$i+=1){
$ColHead = $WorkSheet.cells.Item(1, $i).text
if($ColHead -like "Heading"){
Write-Host "[+]Heading Column Found"
$ColHeading = $i
}
if($ColHead -like "Observation"){
Write-Host "[+]Observation Column Found"
$ColObservation = $i
}
if($ColHead -like "Implication"){
Write-Host "[+]Implication Column Found"
$ColImplication = $i
}
if($ColHead -like "Recommendation"){
Write-Host "[+]Recommendation Column Found"
$ColRecommendation = $i
}
if($ColHead -like "Affected Resources"){
Write-Host "[+]Affected Resources Column Found"
$ColAffectedResources = $i
}
if($ColHead -like "Severity"){
Write-Host "[+]Severity Column Found"
$ColSeverity = $i
}
}
Write-Host "`r`n"
Write-Host "Printing Column Status"
Write-Host "Heading:" $ColHeading
Write-Host "Observation:" $ColObservation
Write-Host "Implication:" $ColImplication
Write-Host "Recommendation:" $ColRecommendation
Write-Host "Affected Resources:" $ColAffectedResources
Write-Host "Severity:" $ColSeverity
for($i=2;$i -le $RowCount; $i+=1){
$TextHeading = $WorkSheet.cells.Item($i, $ColHeading).text
$TextObservation = $WorkSheet.cells.Item($i, $ColObservation).text
$TextImplication = $WorkSheet.cells.Item($i, $ColImplication).text
$TextRecommendation = $WorkSheet.cells.Item($i, $ColRecommendation).text
$TextAffectedResources = $WorkSheet.cells.Item($i, $ColAffectedResources).text
$TextSeverity = $WorkSheet.cells.Item($i, $ColSeverity).text
WorkOnHeading $TextHeading
WorkOnObservation $TextObservation
WorkOnImplication $TextImplication
WorkOnRecommendation $TextRecommendation
WorkOnAffectedResources $TextAffectedResources
WorkOnSeverity $TextSeverity
$hash # Just Printing HashTable
MakeWordReport $i $hash
$hash = @{}
}
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
Before running the script check the variables such as
$template = "C:\Users\tempuser\Documents\TemplateFinding.docx"
$newfile="C:\Users\tempuser\Documents\file_$index.docx"
$WorkBook = $objExcel.Workbooks.Open("C:\Users\tempuser\Documents\VulnList.xlsx")
$WorkSheet = $WorkBook.sheets.item("Network Penetration Testing")
Once you run the script, the output looks like below
RowCount: 5
ColumnCount 7
[+]Heading Column Found
[+]Observation Column Found
[+]Implication Column Found
[+]Recommendation Column Found
[+]Affected Resources Column Found
[+]Severity Column Found
Printing Column Status
Heading: 2
Observation: 3
Implication: 4
Recommendation: 5
Affected Resources: 6
Severity: 7
Name Value
---- -----
Recommendation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...
AffectedResources Affected Host 1...
Observation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...
Heading This is Heading 1
Implication Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....
Severity Critical
Reference www.google.com...
Recommendation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...
AffectedResources Affected Host 1...
Observation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...
Heading This is Heading 2
Implication Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....
Severity High
Reference
Recommendation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...
AffectedResources Affected Host 1...
Observation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...
Heading This is Heading 3
Implication Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....
Severity Medium
Reference
Recommendation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi non neque nunc. Quisque mollis scelerisque dui, a laor...
AffectedResources Affected Host 1...
Observation Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras rhoncus est eget aliquet dictum. Vestibulum sed pretium...
Heading This is Heading 4
Implication Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam vitae nisi vel arcu finibus ultricies eu dignissim urna....
Severity Low
Reference
And you will have the files like this at the destination defined in $newfile
Generated Isolated findings files
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.
Sub Merge()
Dim dlgFile As FileDialog
Dim nTotalFiles As Integer
Dim nEachSelectedFile As Integer
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
With dlgFile
.AllowMultiSelect = True
If .Show <> -1 Then
Exit Sub
Else
nTotalFiles = .SelectedItems.Count
End If
End With
For nEachSelectedFile = 1 To nTotalFiles
Selection.InsertFile dlgFile.SelectedItems.Item(nEachSelectedFile)
If nEachSelectedFile < nTotalFiles Then
Selection.InsertBreak Type:=wdPageBreak
Else
If nEachSelectedFile = nTotalFiles Then
Exit Sub
End If
End If
Next nEachSelectedFile
End Sub
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
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
Last modified 2yr ago