正如你所知道的Excel有1048574行。 我怎样才能在我的代码下面输出超过一百万行？ 如果达到最大行，那么我怎样才能继续循环中的新工作表？ 如何适应它？ 我find了很好的PowerShellfunction后googlesearch一下。
[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() 使用您当前行号的现有知识来确定何时到达最大行，然后移动到新工作表。
[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()
这里的重要一点：
if ($intRow -eq $maxRow) { $ws = $wb.CreateSheet("Company_NTFS_Permissions" + $intNextSheet); $intNextSheet++ $intRow = 0 }
这会在每次$intRow获得递增后运行，检查$maxRow是否已经达到。 如果是这样，它将移动到一个带有编号名称的新工作表，并从第一行重新开始。