PowerShell ImportExcel并跳过/排除特定床单

发布于 01-22 13:40 字数 1822 浏览 3 评论 0原文

我有以下代码用于导入工作簿,在工作簿中排除特定工作表,在某些情况下工作,然后导出单个工作表。一切都起作用,除了它不排除“ 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

遮了一弯2025-01-29 13:40:31

这个问题似乎源于文件启动的方式,其中有一个“ 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.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文