excel - 比较 2 excel 中的列，如果匹配，则将相应的其他列值从 2nd excel 复制到 1st excel

Question

我在下面有这些 excel 文件，我必须在其中比较“主机”，如果 excel-1 值与 excel-2 匹配，那么我必须在 excel-1 中创建一个列并将“HostGroup”从 excel-2 复制到 excel- 1.

在“主机”的 excel-1 列中

和 excel-2 "Host_Name"

e.g. excel-1 

Host

abc
def

excel-2

group     Host_name

grp1      plq
grp2      def
grp3      abc

Final output in excel-1

Group   host

grp3    abc
grp2    def

请需要一些想法如何做到这一点

标签： excelpowershell

Answer 1

这应该有效：

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$excel               = New-Object -ComObject Excel.Application
$excel.Visible       = $false
$excel.DisplayAlerts = $false 

$wb1                 = $excel.Workbooks.Open( "C:\excel1.xlsx", [System.Type]::Missing, $false ) 
$wb2                 = $excel.Workbooks.Open( "C:\excel2.xlsx", [System.Type]::Missing, $true ) 

$ws1                 = $wb1.WorkSheets.item(1)
$ws2                 = $wb2.WorkSheets.item(1)

[void]$ws1.Range( 'A:A' ).EntireColumn.Copy()
$insertRange = $ws1.Range( 'A:A' ).EntireRow
[void]$ws1.Range( 'A:A' ).Insert( [Microsoft.Office.Interop.Excel.XlInsertShiftDirection]::xlShiftToRight )
[void]$ws1.Range( 'A:A' ).EntireColumn.ClearContents()
$ws1.Cells( 1, 1 ).Value2 = 'GROUP'

$searchRange = $ws1.Range( 'B:B' ).EntireColumn

$lineCounter = 2
while( $ws2.Cells( $lineCounter, 1 ).Value2 ) {

    $hostName     = $ws2.Cells( $lineCounter, 2 ).Value2.Trim('" ')
    $searchResult = $searchRange.Find( $hostName, [System.Type]::Missing, [System.Type]::Missing, [Microsoft.Office.Interop.Excel.XlLookAt]::xlWhole, [Microsoft.Office.Interop.Excel.XlSearchOrder]::xlByColumns )

    if( $searchResult ) {
        $group = $ws2.Cells( $lineCounter, 1 ).Value2
        $ws1.Cells( $searchResult.Row, $searchResult.Column - 1 ).Value2 = $group
    }
    $lineCounter++
}

[void]$wb2.Close()
[void]$wb1.SaveAs("C:\excel_new.xlsx")
[void]$wb1.Close()
[void]$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null