Powershell - 在Excelfile中导出到多个工作表 [英] Powershell - Export to multiple sheets in an Excelfile

问题描述

您好，

我希望有人可以帮助我。我有一个Powershell脚本，我使用它来导出Active Directory中的几个OU。

这个导出每个都有自己的CSV文件（作为备份）。我真的希望导出转到一个Excel文件，每个文件都在不同的表格中。



所以例如，如果我有3个OU：员工，OU：功能邮箱​​和OU ：已禁用帐户。

我希望OU在.XLSX文件中有自己的工作表，这样我就可以轻松地报告帐户。

下面是我用来导出一个OU并将CSV文件作为备份并导出到XLSX的脚本。

我想念多个CSV的导入部分：

 clear-host 
 
 import-module ActiveDirectory 
 
 $ Employees =" OU = Employees，OU = Accounts，OU = LocationA， DC = DOMAIN，DC = COM" 
 
 $ workbook = $ excel.workbooks.add（）
 $ sheet = $ workbook.workbooks.Item（1）
 $ sheet.cells.item（1,1） ="测试" 
 
 $ Users = Get-ADUser -Filter * -SearchBase $ Employees -Properties * | Select-Object -Property名称，描述| 
 Sort-Object -Property Name | Where-Object {$ _。Name-like" *" -and $ _。启用-ne $ TRUE} | 
 Export-Csv -Delimiter"，" -  Path" C：\ export_users.csv"-NoTypeInformation 
 
 $ excel = new-object -comobject excel.application 
 $ excel.visible = $ true 
 $ workbook = $ excel.workbooks.add（"C：\ _export_users.csv"）
 $ workbook.workSheets.Item（3）.delete（）
 $ workbook.workSheets.Item（2）.delete（）
 $ workbook.WorkSheets.Item（1）.Name =" Users" 
 $ sheet = $ workbook.WorkSheets.Item（" Users"）
 $ x = 2 
 $ sheet.cells.item（1,1）=" Name" 
 $ sheet.cells.item（1,2）=" Description" 
 
 foreach（$ ADUser in $ Users）
 {
 $ sheet.cells.item（$ x，1）= $ Users.name 
 $ sheet.cells .item（$ x，2）= $ Users.workingSetSize 
 $ x ++ 
} 
 
 $ range = $ sheet.usedRange 
 $ range.EntireColumn.AutoFit（ ）| out-null 
 
 $ table = $ workbook.ActiveSheet.ListObjects.add（1，$ workbook.ActiveSheet.UsedRange，0,1）
 $ workbook.ActiveSheet.UsedRange.EntireColumn.AutoFit （）
 $ excel.DisplayAlerts = $ False 
 $ workbook.SaveAs（" C：\ Export.xlsx"，51）

On这张纸2和3将被删除，但我真的很喜欢床单过滤与其他OU的

我希望有人可以帮我这个。

抱歉蹩脚的英语。

Greets，

Hendrik。

解决方案

脚本和您的请求似乎不匹配。 我只看到一张纸。 我看不到出口了。 TH =他的工作簿保存为完整的工作簿：

workbook.SaveAs（" C ： <跨度> \Export <跨度> <跨度> XLSX <跨度>"，51）

Hello,

Ik hope somebody can help me. Ik have a Powershell script and i use this to export a couple OU's in the Active Directory.
This exports go each to there own CSV file (as backup). I realy would like the exports go to one Excel file, each in different sheets.

So for example if i have 3 OU's: Employees, OU: Functional Mailboxes and OU: Disabled Accounts.

I want the OU's have there own sheet in an .XLSX-file so i can easely make an report of the accounts.

Below is the script that i use to export one OU and make an CSV file as backup and export to XLSX.

I miss the import part for multiple CSV's:

clear-host

import-module ActiveDirectory

$Employees="OU=Employees,OU=Accounts,OU=LocationA,DC=DOMAIN,DC=COM"

$workbook = $excel.workbooks.add()
$sheet = $workbook.workbooks.Item(1)
$sheet.cells.item(1,1) = "Test"

$Users = Get-ADUser -Filter * -SearchBase $Employees -Properties * | Select-Object -Property Name,Description | 
Sort-Object -Property Name | Where-Object {$_.Name -like "*" -and $_.Enabled -ne $TRUE }| 
Export-Csv -Delimiter "," -Path "C:\export_users.csv" -NoTypeInformation

$excel = new-object -comobject excel.application
$excel.visible = $true
$workbook = $excel.workbooks.add("C:\export_users.csv")
$workbook.workSheets.Item(3).delete()
$workbook.workSheets.Item(2).delete()
$workbook.WorkSheets.Item(1).Name = "Users"
$sheet = $workbook.WorkSheets.Item("Users")
$x = 2
$sheet.cells.item(1,1) = "Name"
$sheet.cells.item(1,2) = "Description"

foreach($ADUser in $Users)
{
 $sheet.cells.item($x, 1) = $Users.name
 $sheet.cells.item($x,2) = $Users.workingSetSize
 $x++
} 

$range = $sheet.usedRange
$range.EntireColumn.AutoFit() | out-null

$table=$workbook.ActiveSheet.ListObjects.add( 1,$workbook.ActiveSheet.UsedRange,0,1)
$workbook.ActiveSheet.UsedRange.EntireColumn.AutoFit()
$excel.DisplayAlerts=$False
$workbook.SaveAs("C:\Export.xlsx",51)

On this pont the sheets 2 and 3 will be deleted but i would realy like the sheets filt with de other OU's

Ik hope someone can help me with this.

Sorry for crappy English.

Greets,

Hendrik.

解决方案

The script and you request do not appear to match.  I see only one sheet.  I see no export. TH=he workbook is save as a complete workbook:

workbook.SaveAs("C:\Export.xlsx",51)

这篇关于Powershell - 在Excelfile中导出到多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！