PowerShell ImportExcel并跳过/排除特定床单
我有以下代码用于导入工作簿,在工作簿中排除特定工作表,在某些情况下工作,然后导出单个工作表。一切都起作用,除了它不排除“ Get-Excelsheetinfo”行中指定的一些工作表。
$param = @{
Path = $inputfileName
StartRow = 5
HeaderName = 'Setting', 'Current settings', 'Proposed settings', 'Notes'
}
# Loop and obtain worksheets in workbook
Get-ExcelSheetInfo -Path $inputfileName | Where-Object {$_.Name -notlike "Sheet1" -and $_.Name -notlike "Sheet3" -and $_.Name -notlike "Sheet5" -and $_.Name -notlike "Sheet8" -and $_.Name -notlike "Sheet11" -and $_.Name -notlike "Change Record" -and $_.Name -notlike "LIST"} | ForEach-Object {
# Set the worksheetname name in $param
$param['WorksheetName'] = $_.Name
# Import the worksheet and enumerate it
foreach($line in Import-Excel @param) {
$currSettings = $line.'Current settings'
$propSettings = $line.'Proposed settings'
# If the value of 'Current Settings' cell is equal to the adjacent value of
# 'Proposed Settings' cell OR is empty (white spaces), skip and go to next iteration
if($currSettings -eq $propSettings -or [string]::IsNullOrWhiteSpace($currSettings)) {
continue
}
# If we're here, condition before this was not true, hence we want to
# Output the line (row) and add a new column (A) with the name of the worksheet the setting was obtained from
$line | Select-Object @{N='Workstream';E={$param['WorksheetName']}}, *
}
} | Export-Excel -Path $outputfileName -WorksheetName 'Change List' -AutoSize -BoldTopRow -TableName table1 -TableStyle Medium6 -FreezeTopRow -CellStyleSB {
param($workSheet)
$WorkSheet.Cells["C:D"].Style.HorizontalAlignment="Center"}
我想知道是否可能是因为其中一些床单是“ if($ currsettings -eq $ propsettings -or [string] :: isnullorwhitespace($ currsettings))){ 即使我指定不应从一开始就进口它们。有什么想法吗?
I have the code below for importing a workbook, exclude specific worksheets in the workbook, work on some conditions and then export a single worksheet. Everything works except that it doesn't exclude some of the worksheets specified in the "Get-ExcelSheetInfo" line.
$param = @{
Path = $inputfileName
StartRow = 5
HeaderName = 'Setting', 'Current settings', 'Proposed settings', 'Notes'
}
# Loop and obtain worksheets in workbook
Get-ExcelSheetInfo -Path $inputfileName | Where-Object {$_.Name -notlike "Sheet1" -and $_.Name -notlike "Sheet3" -and $_.Name -notlike "Sheet5" -and $_.Name -notlike "Sheet8" -and $_.Name -notlike "Sheet11" -and $_.Name -notlike "Change Record" -and $_.Name -notlike "LIST"} | ForEach-Object {
# Set the worksheetname name in $param
$param['WorksheetName'] = $_.Name
# Import the worksheet and enumerate it
foreach($line in Import-Excel @param) {
$currSettings = $line.'Current settings'
$propSettings = $line.'Proposed settings'
# If the value of 'Current Settings' cell is equal to the adjacent value of
# 'Proposed Settings' cell OR is empty (white spaces), skip and go to next iteration
if($currSettings -eq $propSettings -or [string]::IsNullOrWhiteSpace($currSettings)) {
continue
}
# If we're here, condition before this was not true, hence we want to
# Output the line (row) and add a new column (A) with the name of the worksheet the setting was obtained from
$line | Select-Object @{N='Workstream';E={$param['WorksheetName']}}, *
}
} | Export-Excel -Path $outputfileName -WorksheetName 'Change List' -AutoSize -BoldTopRow -TableName table1 -TableStyle Medium6 -FreezeTopRow -CellStyleSB {
param($workSheet)
$WorkSheet.Cells["C:D"].Style.HorizontalAlignment="Center"}
I'm wondering if it may be because some of those sheets are matching conditions from "if($currSettings -eq $propSettings -or [string]::IsNullOrWhiteSpace($currSettings)) { continue"
even though I'm specifying that they should not be imported from the beginning. Any ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

这个问题似乎源于文件启动的方式,其中有一个“ Invoke-item” CMDLET。我删除了该行,并在导出末端使用了“ - 显示”,现在正正确输出。不确定如何或为什么引起问题。
The problem seemed to have stemmed from the way the file was launching with an "invoke-item" cmdlet I had in place. I removed that line and used "- Show" at the end of the Export and it's outputting properly now. Not sure how or why that caused an issue.