5
\$\begingroup\$

How can I improve the speed of the script?

$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()

$wSheet = $Excel.Worksheets.Item(1)
$wSheet.Cells.item(1, 1) = "Folder Path:"
$wSheet.Cells.Item(1, 2) = "Users/Groups:"
$wSheet.Cells.Item(1, 3) = "Permissions:"
$wSheet.Cells.Item(1, 4) = "Permissions Inherited:"

$WorkBook = $wSheet.UsedRange
$WorkBook.Interior.ColorIndex = 8
$WorkBook.Font.ColorIndex = 11
$WorkBook.Font.Bold = $True

 ####Change the path to the folder or share you want NTFS perms on####
 $dirToAudit = Get-ChildItem -Path "c:\inetpub" -recurse | Where { $_.psIsContainer -eq $true }

 $intRow = 1
 foreach ($dir in $dirToAudit)
 {
     $colACL = Get-Acl -Path $dir.FullName

foreach ($acl in $colACL)
 {
    $intRow++
    $wSheet.Cells.Item($intRow, 1) = $dir.FullName

    foreach ($accessRight in $acl.Access)
    {
        $wSheet.Cells.Item($intRow, 2) = "$($AccessRight.IdentityReference)"
        $wSheet.Cells.Item($intRow, 3) = "$($AccessRight.FileSystemRights)"
        $wSheet.Cells.Item($intRow, 4) = $acl.AreAccessRulesProtected
        $intRow++
    }
}

}
 $WorkBook.EntireColumn.AutoFit()
\$\endgroup\$
4
  • \$\begingroup\$ @Mast sorry my fault. I have edited my question again thanks \$\endgroup\$
    – Cell-o
    Aug 27, 2016 at 9:58
  • 1
    \$\begingroup\$ Please edit the body of the question again - give a description of what it does rather than what you want. If it works, say it works and it does x and you want to know how to improve it. Rather than it looking like it does x but I want to do y. \$\endgroup\$ Aug 27, 2016 at 10:17
  • 1
    \$\begingroup\$ Better, close vote retracted. \$\endgroup\$
    – Mast
    Aug 27, 2016 at 10:25
  • \$\begingroup\$ Please do not update the code in your question to incorporate feedback from answers, doing so goes against the Question + Answer style of Code Review. This is not a forum where you should keep the most updated version in your question. Please see what you may and may not do after receiving answers. \$\endgroup\$
    – Vogel612
    Aug 29, 2016 at 12:02

2 Answers 2

4
\$\begingroup\$

The only way to improve the speed of the script is to drop the usage of Excel as a COM-object altogether. It is considered bad practice. And it is not possible on servers where Excel is not installed.

It's better to use a library like NPOI (.NET port from Apache POI (java)).

I used this (old) example as a base and adapted it to roughly match your code.

[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.OOXML.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.OpenXml4Net.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.OpenXml4Net.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\NPOI.OpenXmlFormats.dll")
[Reflection.Assembly]::LoadFrom("C:\Users\IEUser\Desktop\Release\Net40\ICSharpCode.SharpZipLib.dll")

$wb = New-Object NPOI.XSSF.UserModel.XSSFWorkbook;
$ws = $wb.CreateSheet("output");
$ws.CreateRow(0)| out-null;

$dirToAudit = Get-ChildItem -Path "C:\Users\IEUser" -recurse | Where { $_.psIsContainer -eq $true }

$intRow = 1

foreach ($dir in $dirToAudit)
{
    $colACL = Get-Acl -Path $dir.FullName

    foreach ($acl in $colACL)
    {
        $fileNameRow = $ws.CreateRow($intRow)
        $fileNameRow.CreateCell(1).SetCellValue($dir.FullName)
        $intRow++

        foreach ($accessRight in $acl.Access)
        {
            $values = $ws.CreateRow($intRow)
            $values.CreateCell(2).SetCellValue($($AccessRight.IdentityReference).ToString())
            $values.CreateCell(3).SetCellValue($($AccessRight.FileSystemRights).ToString())
            $values.CreateCell(4).SetCellValue($($acl.AreAccessRulesProtected).ToString())
            $intRow++
        }
    }

}
$fs = new-object System.IO.FileStream("C:\Users\IEUser\Desktop\test.xlsx",[System.IO.FileMode]'Create',[System.IO.FileAccess]'Write')
$wb.Write($fs);
$fs.Close()

There is also another library for this kind of stuff: EPPLus

And someone else made some nice powershell functions for it.

\$\endgroup\$
7
  • \$\begingroup\$ thanks guy just curious I want to test the speed of script via measure command. how can I do that? \$\endgroup\$
    – Cell-o
    Aug 28, 2016 at 11:04
  • \$\begingroup\$ measure-command { <insert command/script block here> } \$\endgroup\$
    – Adamar
    Aug 28, 2016 at 11:07
  • \$\begingroup\$ OK Lastly I have got very similar question here: stackoverflow.com/questions/39183345/… I have updated my code but no luck. Could you please help me? \$\endgroup\$
    – Cell-o
    Aug 28, 2016 at 11:32
  • \$\begingroup\$ how can I add column header such as Folder Path , Users/Groups , Permissions , Permissions Inherited ? \$\endgroup\$
    – Cell-o
    Aug 29, 2016 at 6:41
  • \$\begingroup\$ I haven't tried out every possible function. But the easiest is to just create a row and fill it with the values you want. $rowHeaders = $ws.CreateRow($row) and put the next in a loop with your headerNames: $rowHeaders.CreateCell($column).SetCellValue($headerName) \$\endgroup\$
    – Adamar
    Aug 29, 2016 at 11:12
1
\$\begingroup\$

I don't see any real need for Excel here at all actually. Any reason you could not just use a CSV that can be opened in Excel? Export-CSV and using more of the pipeline would most certainly be faster here.

$path = 'rootpath'
$exportPath = 'Path to export'
Get-ChildItem -Path $path -Recurse | Where-Object {$_.psIsContainer} | ForEach-Object{
    # Process each folder
    $singleFolder = $_
    $aclSet = $singleFolder | Get-Acl
    $aclset.Access | ForEach-Object{
        # Build a custom object, using a hashtable, for each access control set
        $props = @{
            "Folder Path" = $singleFolder.FullName
            "Users/Groups" = $_.IdentityReference
            Permissions = $_.FileSystemRights
            "Permissions Inherited" = $aclset.AreAccessRulesProtected
        }

        # Create and send the object down the pipeline
        New-Object -TypeName psobject -Property $props
    }

# Ensure that the properties appear in the desired order. Issue with PowerShell 2.0
} | Select-Object "Folder Path", "Users/Groups", Permissions, "Permissions Inherited" |
    Export-CSV -NoTypeInformation -Path $exportPath

Other thoughts

  • psIsContainer is itself a boolean so you don't need to test it being true. PowerShell will do that all on its own.
  • There is no error handling in your or my script snippet. If you don't have the right to read the folders, export the data and etc. the script would fail.
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.