1

As you know Excel has got 1048574 rows. How can I export more than one million rows below my code ? if max rows is reached then how can I continue over new worksheet in Loop ? how to adapted it? I'm googling some after I find nice powershell functions it. 

[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.OOXML.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.OpenXml4Net.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.OpenXml4Net.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.OpenXmlFormats.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\ICSharpCode.SharpZipLib.dll")

$wb = New-Object NPOI.XSSF.UserModel.XSSFWorkbook;
$ws = $wb.CreateSheet("Company_NTFS_Permissions");
$wr = $ws.CreateRow(0);



$wr.createCell(0).setCellValue("Folder Path");
$wr.createCell(1).setCellValue("Users/Groups");
$wr.createCell(2).setCellValue("Permissions");
$wr.createCell(3).setCellValue("AccessControlType");
$wr.createCell(4).setCellValue("Permissions Inherited")



$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)
    {
        $fileNameRow = $ws.CreateRow($intRow)
        $fileNameRow.CreateCell(0).SetCellValue($dir.FullName)
        $intRow++

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

        }
    }

}
$fs = new-object System.IO.FileStream("C:\DRIVERS\test.xlsx",[System.IO.FileMode]'Create',[System.IO.FileAccess]'Write')
$wb.Write($fs);
$fs.Close()
Improve this question
4
  • 1
    You're already keeping track of the number of rows in $intRow so can't you just check if you've just reached the row limit then re-assign your worksheet variable like $ws = $wb.CreateSheet("Next_Sheet");? Commented Aug 29, 2016 at 13:46
  • how can I adapted variable like $ws = $wb.CreateSheet("Next_Sheet") ? please clarify me Commented Aug 29, 2016 at 14:19
  • You set the variable for the worksheet at the top of the code, and that variable determines everything about what sheet the data is added to. Just re-set that variable later in code to a different worksheet (the exact line I posted, but change the variable name from "Next_Sheet"), and all new data will be added to that different sheet. Commented Aug 29, 2016 at 14:22
  • $wb = New-Object NPOI.XSSF.UserModel.XSSFWorkbook; $ws = $wb.CreateSheet("Company_NTFS_Permissions"); $ws = $wb.CreateSheet("Company_NTFS_Permissions_2"); $wr = $ws.CreateRow(0); Am I correct ? Commented Aug 29, 2016 at 14:27

1 Answer 1

Reset to default
1

Use your existing knowledge of the current row number to determine when the max row is reached, then move to a new sheet.

[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.OOXML.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.OpenXml4Net.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.OpenXml4Net.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\NPOI.OpenXmlFormats.dll")
[Reflection.Assembly]::LoadFrom("C:\DRIVERS\NPOI 2.2.1 binary package\Release\Net40\ICSharpCode.SharpZipLib.dll")

$wb = New-Object NPOI.XSSF.UserModel.XSSFWorkbook;
$ws = $wb.CreateSheet("Company_NTFS_Permissions");
$wr = $ws.CreateRow(0);



$wr.createCell(0).setCellValue("Folder Path");
$wr.createCell(1).setCellValue("Users/Groups");
$wr.createCell(2).setCellValue("Permissions");
$wr.createCell(3).setCellValue("AccessControlType");
$wr.createCell(4).setCellValue("Permissions Inherited")



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

$maxRow = 1048576
$intRow = 1
$intNextSheet = 2

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

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

        if ($intRow -eq $maxRow)
        {
            $ws = $wb.CreateSheet("Company_NTFS_Permissions" + $intNextSheet);
            $intNextSheet++
            $intRow = 0
        }

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

            if ($intRow -eq $maxRow)
            {
                $ws = $wb.CreateSheet("Company_NTFS_Permissions" + $intNextSheet);
                $intNextSheet++
                $intRow = 0
            }
        }
    }

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

The important bit here:

if ($intRow -eq $maxRow)
{
    $ws = $wb.CreateSheet("Company_NTFS_Permissions" + $intNextSheet);
    $intNextSheet++
    $intRow = 0
}

This runs after each time $intRow gets incremented, checking whether the $maxRow has been reached. If so, it moves to a new sheet with a numbered name and restarts from the first row.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

thanks man but I assuming there is logical error because normally I fetching NTFS permissions with 169 rows at this time your modified code 169 worksheets creating instead of this So it doesnt working Max limit logical
Yep, made a mistake forgetting what language I was in. Comparisons use -eq in Powershell. I've edited the answer to reflect the change.

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.