Twitter

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

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 = @{}
}

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

Running The Script

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

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.

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

Tip: Make a copy of the TemplateFinding.docx, delete everything inside it and run the macro. Doing this will preserve your formatting.

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

PreviousAbout Shantanu KhandelwalNextGhostwriter - Add report type

Last updated