从PowerShell函数返回对Excel工作表的引用 [英] Return a reference to an Excel Worksheet from a PowerShell Function
问题描述
出于代码易读性的考虑，我想将Excel内容移至某个函数，并在程序处理内容时使工作表对象可用于写入单元格值.如何调用创建Excel电子表格并返回工作表引用的函数，以便我可以继续访问已创建的打开/活动Excel应用程序对象?
For the sake of code readability, I'd like to move my Excel stuff to a function and leave the worksheet object available to write cell values as my program processes stuff. How do I call a function that creates an Excel spreadsheet and return a worksheet reference so I can continue to access the open/active Excel app object I've created?
Function Create-Excel-Spreadsheet() {
# open excel
$excel = New-Object -ComObject excel.application
$excel.visible = $True
# add a worksheet
$workbook = $excel.Workbooks.Add()
$xl_wksht= $workbook.Worksheets.Item(1)
$xl_wksht.Name = 'Cut-off' #give the worksheet a name
#Create a Title for the first worksheet and adjust the font
$title_row = 1
$xl_wksht.Cells.Item($title_row, 1)= 'Cut-off Processing Ran ' + $startday + ' at ' + $starttime_str
#merging a few cells on the top row to make the title look nicer
$MergeCells = $xl_wksht.Range("A1:Q1")
$MergeCells.Select()
$MergeCells.MergeCells = $true
#formatting the title and giving it a font & color
$xl_wksht.cells($title_row, 1).HorizontalAlignment = -4108 # center the title
$xl_wksht.cells.Item($title_row,1).Font.Size = 18
$xl_wksht.cells.Item($title_row,1).Font.Bold = $True
$xl_wksht.cells.Item($title_row,1).Font.Name = "Cambria"
$xl_wksht.cells.Item($title_row,1).Font.ThemeFont = 1
$xl_wksht.cells.Item($title_row,1).Font.ThemeColor = 4
$xl_wksht.cells.Item($title_row,1).Font.ColorIndex = 55
$xl_wksht.cells.Item($title_row,1).Font.Color = 8210719
$xl_wksht.cells.Item($title_row,1).Font.Color = 8210719
$xl_wksht.Rows[$title_row].RowHeight = 39
$xl_wksht.Rows[$title_row].VerticalAlignment = 2
#create the column headers
$header_row = 2
$data_row_start = 3
$xl_wksht.Rows[$header_row].WrapText = $True
$xl_wksht.Rows[$header_row].Font.Bold = $True
$xl_wksht.Rows[$header_row].columnWidth = 12.57
$xl_wksht.Rows[$header_row].HorizontalAlignment = -4108
$xl_wksht.cells.Item($header_row, 1).value2 = 'Current Load Date'
$xl_wksht.Columns[1].HorizontalAlignment = -4108
$xl_wksht.Columns[1].NumberFormat = "@"
$xl_wksht.cells.Item($header_row, 2).value2 = 'Export File Type'
$xl_wksht.Columns[2].columnWidth = 26
$xl_wksht.cells.Item($header_row,3).value2 = 'File Name to Downloaded'
$xl_wksht.Columns[3].columnWidth = 37
$xl_wksht.cells.Item($header_row,4).value2 = 'Source Path'
$xl_wksht.Columns[4].columnWidth = 23
return $xl_wksht
}
推荐答案
如果您希望获取对工作表的引用，则可以更新代码以确保除了查找之外没有打印/返回其他内容.就目前而言，您将返回两个变量.
If you are looking to get the reference to the worksheet, you can update your code to make sure nothing else is printed / returned besides what you are looking for. As it stands, you are returning two variables..
$wksht = Create-Excel-Spreadsheet
-
$ wksht [0]->布尔值(True) -
$ wksht [1]->工作表参考.
$wksht[0]-> Boolean (True)$wksht[1]-> Worksheet reference.
返回变量的第二个索引将具有电子表格引用，其中第一个只是布尔值.
Second index of your return variable will have your spreadsheet reference where first is just a boolean.
这是一本不错的书，摘自powershell的创建者，以了解发生这种情况的原因.
This is a good read from the founder of powershell as to why this happens.
如果您采用自己的代码并执行它，您将发现在打印对象 $ xl_wksht 本身之前已打印 True .您要做的就是确保除了所需的变量/引用之外，其他任何内容都不会显示在屏幕上.
If you take your code and execute it, you will find that there is a True printed before the object $xl_wksht itself is printed. What you have to do is to make sure nothing else prints on the screen except for the variable/reference you need.
在代码中更改此行，便可以获取引用.
Change this line in your code and you'll be able to get the reference out.
$MergeCells.Select() | Out-Null # line 17 of your code. Add | Out-Null. Or, Simply remove this line as it doesnt do anything.
更新代码后，您可以使用变量从方法中获取值
once you have updated your code, you can use a variable to get the value from your method
$wksht = Create-Excel-Spreadsheet
$wksht.Cells(5, 1) = "Test" // Writes Test on Row 5, Col 1.
这篇关于从PowerShell函数返回对Excel工作表的引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
