excel - Powershell Excel 自动添加工作表

Question

我正在尝试自动化为月度报告类型工作簿在 Excel 中为每个客户名称添加工作表(包含数据)的过程

我认为它应该是直截了当的......但我使用的方法不起作用......它甚至没有进入纸张制作模式......你能帮我弄清楚我做错了什么吗？

以下是我做的功能

function Excelclientstatstemplate ($clients) {
$Exl = New-Object -ComObject "Excel.Application"
$Exl.Visible = $false
$Exl.DisplayAlerts = $false
$WB = $Exl.Workbooks.Open($excelmonthlysummary) 

$clientws = $WB.worksheets | where {$_.name -like "*$clients*"}

#### Check if Clients worksheet exists, if no then make one with client name ###
$sheetcheck = if (($clientws)) {} Else {


$WS = $WB.worksheets.add
$WS.name = "$clients" 
} 
$sheetcheck 

$WB.Save

# Enter stat labels 
$clientws.cells.item(1,1) = "CPU Count" 
$clientws.cells.item(2,1) =  "RAM"
$clientws.cells.item(3,1) =  "Reserved CPU"
$clientws.cells.item(4,1) =  "Reserved RAM"


### Put in Values in the next column ###

$clientws.cells.item(1,2) =  [int]($cstats.cpuAllocationGHz/2)
$clientws.cells.item(2,2) = [decimal]$cstats.memoryLimitGB
$clientws.cells.item(3,2) = [int]($cstats.rescpuAllocationGHz/2)
$clientws.cells.item(4,2) = [decimal]$cstats.resmemoryLimitGB 



  $WB.save
  $Exl.quit()
    Stop-Process -processname EXCEL 
    Start-Sleep -Seconds 1

    Echo "$clients excel sheet in monthly summary is done.."
}

然后我尝试为它制作一个 Foreach 的东西

  $clientxlmonthlywrite = Foreach ($client in $clientlist){

  $cstats = $Combinedstats | Where {$_.Group -eq "$client"}

  Excelclientstatstemplate -clients $client

} 

函数的整个过程去

1. 取客户名

2. 打开特定的 Excel 工作簿

3. 检查是否有任何带有客户名称的表格

4. 如果没有客户姓名的工作表，请用客户姓名制作一张

5. 用标签填充第一列单元格

6. 用数据填充第二列单元格(数据工作我已经用它们写过 CSV)

7. 保存并退出

Foreach 变量只是为来自客户端列表的每个客户端名称执行功能(客户端列表没有问题)

---

我是不是搞砸了？

感谢您的帮助。

Answer 1

您没有正确调用 .Add() 方法。您缺少末尾的括号。要修复它，您应该能够简单地将行修改为:

$WS = $WB.worksheets.add()

此外，单元格具有您应该引用的属性，因此我还将修改设置单元格值的部分，如下所示:

# Enter stat labels 
$clientws.cells.item(1,1).value2 = "CPU Count" 
$clientws.cells.item(2,1).value2 =  "RAM"
$clientws.cells.item(3,1).value2 =  "Reserved CPU"
$clientws.cells.item(4,1).value2 =  "Reserved RAM"


### Put in Values in the next column ###
$clientws.cells.item(1,2).value2 =  [int]($cstats.cpuAllocationGHz/2)
$clientws.cells.item(2,2).value2 = [decimal]$cstats.memoryLimitGB
$clientws.cells.item(3,2).value2 = [int]($cstats.rescpuAllocationGHz/2)
$clientws.cells.item(4,2).value2 = [decimal]$cstats.resmemoryLimitGB 

我很确定定义类型毫无意义，因为对于 Excel，它们都是字符串，直到您将单元格的格式设置设置为其他内容。我可能是错的，但这是我观察到的行为。

现在，对于您没有要求的其他评论...不要启动 Excel，打开图书，保存图书，然后为每个客户关闭 Excel。在开始时打开 Excel 一次，打开书本，为每个客户进行更新，然后保存并关闭。

测试客户是否有工作表，如果需要添加，然后选择客户的工作表后缀。现在，如果您必须为该客户端添加一个 $clientws，则无需设置任何内容。

默认情况下添加工作表会将其置于事件工作表之前。在我看来，这是一个糟糕的设计选择，但事实就是如此。如果是我，我会添加新工作表，指定工作簿中的最后一个工作表，这将在最后一个工作表之前添加新工作表，使其成为最后一个工作表的第二个。然后我将最后一个工作表移到新工作表的前面，有效地将新工作表添加为最后一个列出的工作表。制作时是否可以将新工作表添加为最后一个工作表？是的，但这对我的口味来说太复杂了。见 here如果你有兴趣这样做。

当测试现有的客户端工作表时，如果它丢失了，请这样做，不要告诉它测试某些东西，什么也不做，把你想要的一切都放在 Else陈述。这只会使事情复杂化。说了这么多，以下是一些付诸实践的建议:

function Excelclientstatstemplate ($clients) {

#### Check if Clients worksheet exists, if no then make one with client name ###
if (($clients -notin $($WB.worksheets).Name)){
    #Find the current last sheet
    $LastSheet = $WB.Worksheets|Select -Last 1
    #Make a new sheet before the current last sheet so it's near the end
    $WS = $WB.worksheets.add($LastSheet)
    #Name it
    $WS.name = "$clients"
    #Move the last sheet up one spot, making the new sheet the new effective last sheet
    $LastSheet.Move($WS)
} 

#Find the current client sheet regardless of if it existed before or not
$clientws = $WB.worksheets | where {$_.name -like "*$clients*"}

# Enter stat labels 
$clientws.cells.item(1,1).value2 = "CPU Count" 
$clientws.cells.item(2,1).value2 =  "RAM"
$clientws.cells.item(3,1).value2 =  "Reserved CPU"
$clientws.cells.item(4,1).value2 =  "Reserved RAM"

### Put in Values in the next column ###
$clientws.cells.item(1,2).value2 =  [int]($cstats.cpuAllocationGHz/2)
$clientws.cells.item(2,2).value2 = [decimal]$cstats.memoryLimitGB
$clientws.cells.item(3,2).value2 = [int]($cstats.rescpuAllocationGHz/2)
$clientws.cells.item(4,2).value2 = [decimal]$cstats.resmemoryLimitGB 

Start-Sleep -Seconds 1

Echo "$clients excel sheet in monthly summary is done.."
}

$Exl = New-Object -ComObject "Excel.Application"
$Exl.Visible = $false
$Exl.DisplayAlerts = $false
$WB = $Exl.Workbooks.Open($excelmonthlysummary) 

$clientxlmonthlywrite = Foreach ($client in $clientlist){
    $cstats = $Combinedstats | Where {$_.Group -eq "$client"}
    Excelclientstatstemplate -clients $client
} 

$WB.save
$Exl.quit()
Stop-Process -processname EXCEL