比较两个excel中的列，如果匹配，则将相应的其他列值从第二个excel复制到第一个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

请告诉我该怎么做

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