Powershell tạo sổ làm việc excel với nhiều trang tính

Trước đây, tôi đã đọc một bài báo của Phil Factor về tự động hóa Microsoft Office thông qua Powershell. Không lâu sau đó, tôi thực sự cần đưa một phần quan trọng của nó vào sử dụng. tự động hóa tập lệnh Excel. Trong trường hợp của tôi, tôi cần thực hiện một số phép chiếu phức tạp thông qua SQL Server và xuất chúng sang Excel. Có quá nhiều bộ đầu ra khiến tôi muốn tự mình thực hiện công việc, vì vậy tôi đã nhờ Powershell thực hiện công việc đó cho mình. Đây là phiên bản sạch của tập lệnh đó, được xây dựng từ AdventureWorks2008. Trong tập lệnh này, chúng tôi sẽ tạo một bảng tính Excel với mỗi lược đồ được liệt kê trong một tab khác nhau và mỗi trang tính sẽ chứa các bảng cho lược đồ đó

#This script was inspired by Phil Factor's article at http://www.simple-talk.com/dotnet/.net-tools/com-automation-of-office-applications-via-powershell/.
Import-Module SqlServer
Import-Module Pscx

#This script will, for each relevant service, create an Excel workbook and populate it with the values from that service.
$sql = Get-SQLServer "."
$db = "AdventureWorks2008"

#Create a directory if it does not already exist.
$DirectoryToSaveTo='c:\temp\'
if (!(Test-Path -path "$DirectoryToSaveTo"))
{
	New-Item "$DirectoryToSaveTo" -type directory | out-null
}
$filename = $DirectoryToSaveTo + "Test File.xlsx"
if (test-path $filename) { rm $filename }	#delete existing files

$Excel = New-Object -Com Excel.Application
$Excel.Visible = $true

#Sleep!  This is because I don't know better and need to wait to add the workbook.
Start-Sleep -s 1
$Workbook = $Excel.Workbooks.Add()

$schemas_query = "select distinct SCHEMA_NAME(schema_id) as Name, schema_id from sys.tables order by Name DESC;"
foreach ($record in Get-SQLData $sql $db $schemas_query)
{
	$query = "select SCHEMA_NAME(schema_id) as SchemaName, name as TableName, object_id as ObjectId, max_column_id_used as MaxColumnId from sys.tables where schema_id =" + $record.schema_id
	$csvResults = Get-SQLData $sql $db $query | Select-Object SchemaName, TableName, ObjectId, MaxColumnId | ConvertTo-CSV -Delimiter "`t" -NoTypeInformation

	#This requires Pscx.
	$csvResults | Out-Clipboard

	#Create the workbook and paste in the board data.  We throw in a sleep here because I don't know how to wait until the workbook is ready to be added...
	Start-Sleep -s 1
	$Worksheet = $Workbook.Sheets.Add()
	$Worksheet.Name = $record.Name
	$Range = $Worksheet.Range("a1","d$($csvResults.count + 1)")
	$Worksheet.Paste($Range, $false)

	#Make this look pretty and copy the data to the clipboard.
	$Worksheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Excel.ActiveCell.CurrentRegion, $null, [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes).Name = "Table2"
	$Worksheet.ListObjects.Item("Table2").TableStyle = "TableStyleMedium2"
	$Range.EntireColumn.Autofit()
}

#Clean up by deleting the Sheet1, Sheet2, and Sheet3 sheets.
$Excel.DisplayAlerts = $false
$Workbook.Worksheets.Item("Sheet1").Delete()
$Workbook.Worksheets.Item("Sheet2").Delete()
$Workbook.Worksheets.Item("Sheet3").Delete()

$Workbook.SaveAs($filename)
#$Excel.quit()

Điều này hơi dài đối với một tập lệnh Powershell, vì vậy tôi sẽ chia mọi thứ thành các phần

Import-Module SqlServer
Import-Module Pscx

Bạn cần SQLPSX và Pscx cho việc này

$sql = Get-SQLServer "."
$db = "AdventureWorks2008"

#Create a directory if it does not already exist.
$DirectoryToSaveTo='c:\temp\'
if (!(Test-Path -path "$DirectoryToSaveTo"))
{
	New-Item "$DirectoryToSaveTo" -type directory | out-null
}
$filename = $DirectoryToSaveTo + "Test File.xlsx"
if (test-path $filename) { rm $filename }	#delete existing files

Đây là một số thứ khá đơn giản. kiểm tra để đảm bảo thư mục tồn tại và nếu tệp đã tồn tại, hãy xóa nó trước khi bắt đầu công việc

$Excel = New-Object -Com Excel.Application
$Excel.Visible = $true

#Sleep!  This is because I don't know better and need to wait to add the workbook.
Start-Sleep -s 1
$Workbook = $Excel.Workbooks.Add()

Sau khi bạn đặt $Excel. Hiển thị với $true, bạn sẽ thấy một cửa sổ Excel mới bật lên. Sau đó, chúng tôi muốn tạo một sổ làm việc, tự động tạo ba trang tính. Đừng lo—chúng tôi sẽ xử lý các trang tính đó sau

Nhân tiện, tôi đưa ra các tuyên bố về giấc ngủ ở đây vì tôi không chắc có cách nào tốt hơn để làm điều đó. Tôi chỉ biết rằng phải có một…

$schemas_query = "select distinct SCHEMA_NAME(schema_id) as Name, schema_id from sys.tables order by Name DESC;"
foreach ($record in Get-SQLData $sql $db $schemas_query)

Điều này sẽ thiết lập danh sách các lược đồ của chúng tôi

$query = "select SCHEMA_NAME(schema_id) as SchemaName, name as TableName, object_id as ObjectId, max_column_id_used as MaxColumnId from sys.tables where schema_id =" + $record.schema_id
$csvResults = Get-SQLData $sql $db $query | Select-Object SchemaName, TableName, ObjectId, MaxColumnId | ConvertTo-CSV -Delimiter "`t" -NoTypeInformation

#This requires Pscx.
$csvResults | Out-Clipboard

Bây giờ bạn có thể thấy truy vấn bên trong. chúng tôi sẽ nhận được các bảng được liên kết với lược đồ này. Trong trường hợp cụ thể này, tốt hơn hết là chỉ lấy toàn bộ danh sách một lần rồi lọc ra bằng Powershell. Nhưng khi tôi thực hiện phiên bản phức tạp hơn của mình, tôi cần thực hiện một số phép tính phức tạp và việc lấy được toàn bộ là điều không tưởng. Vì vậy, mặc dù nó quá mức cần thiết ở đây và hiệu suất thấp hơn, tôi vẫn sẽ trình bày theo cách này

Sau đó, chúng tôi nhận được kết quả của truy vấn này, chọn các cột thích hợp (trong trường hợp của chúng tôi là tất cả chúng) và biến nó thành một tập hợp kết quả được phân định bằng tab. Chúng tôi sẽ sử dụng Pscx để sao chép tệp này vào khay nhớ tạm

Start-Sleep -s 1
$Worksheet = $Workbook.Sheets.Add()
$Worksheet.Name = $record.Name
$Range = $Worksheet.Range("a1","d$($csvResults.count + 1)")
$Worksheet.Paste($Range, $false)

Một lần nữa với tuyên bố ngủ… oy…

Vì vậy, khi chúng tôi có kết quả, chúng tôi tạo một trang tính mới cho lược đồ này và điền nó vào các cột của chúng tôi. Lưu ý rằng một kết quả tốt hơn ở đây có thể là biến “d” thành một biến, vì hiện tại, nó sẽ chỉ sao chép bốn cột. Điều đó tốt cho tập dữ liệu cụ thể này, nhưng nếu bạn muốn khái quát mã ở trên, bạn cần lấy đúng số lượng cột

Cuối cùng, chúng tôi dán nội dung khay nhớ tạm của mình vào trang tính

$Worksheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $Excel.ActiveCell.CurrentRegion, $null, [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes).Name = "Table2"
$Worksheet.ListObjects.Item("Table2").TableStyle = "TableStyleMedium2"
$Range.EntireColumn.Autofit()

Điều này là không cần thiết, nhưng thật dễ dàng để làm cho kết quả của bạn trông đẹp mắt, vậy tại sao không?

Chúng tôi tắt DisplayAlerts để ẩn thông báo hỏi bạn có thực sự muốn xóa trang tính không. Sau đó, chúng tôi có thể loại bỏ ba trang tính mà chúng tôi không sử dụng. Cuối cùng, lưu bảng tính bằng tên chúng tôi đã chọn ở trên và bạn có thể bỏ ghi chú dòng cuối cùng để đóng Excel

Làm cách nào để tạo tệp Excel bằng tập lệnh PowerShell?

Tôi có thể tạo nhiều trang tính trong Excel từ danh sách không?

Excel CSV có thể có nhiều trang tính không?