如何使用powershell复制多个excel工作表并制作一个新的? [英] How to use powershell to copy several excel worksheets and make a new one?

问题描述

我有大约 70 个 excel 文件要合并到一个文档中.每个文档只有一张纸并遵循以下格式:

I have about 70 excel files I want to combine into a single document. Each document has only one sheet and follows this format:

• A 行 A-F 列标题
• B 行第一个条目
• C 行第二个条目
• 某些工作表上最多 150 行

我想从 A-F 列中为每一行抓取信息，并将其与我在同一目录中的所有其他文件中的信息合并到一个新文件中.

I want to scrape the information from Columns A-F for each row, and combine it into a new file with the information from all of the other files I have in the same directory.

注意:我只想捕获 A-F 列，因为在 G 列中存在一个 Yes, No 数据集来管理 F 列中的下拉列表.

Note: I only want to capture Columns A-F since in Column G there exists a Yes, No dataset to manage the drop down list in Column F.

我尝试使用来自 Copy Excel使用 Powershell 将工作簿从一个工作簿转移到另一个工作簿，但它产生了一个文件，其中部分数据分布在两张工作表上.

I tried using dugan's answer from Copy Excel Worksheet from one Workbook to another with Powershell but it resulted in a file with part of the data spread across two sheets.

代码如下:

$file1 = 'C:UsersMatthew.AndressDocumentsExcel TestBook1.xlsx' # source's fullpath
$file2 = 'C:UsersMatthew.AndressDocumentsExcel TestBook2.xlsx' # destination's fullpath
$xl = new-object -c excel.application
$xl.displayAlerts = $false # don't prompt the user
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly
$wb1 = $xl.workbooks.open($file2) # open target
$sh1_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook
$sheetToCopy = $wb2.sheets.item('Sheet1') # source sheet to copy
$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook
$wb2.close($false) # close source workbook w/o saving
$wb1.close($true) # close and save destination workbook
$xl.quit()
spps -n excel

有什么建议吗?谢谢.

推荐答案

好吧，这是几天后，但你可以感谢周末.看看这个，看看你喜欢它.

Ok, it's a few days later, but you can thank the weekend for that. Take a look at this and see how you like it.

#Get a list of files to copy from
$Files = GCI 'C:UsersMattDocumentsExcel Test' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName

#Launch Excel, and make it do as its told (supress confirmations)
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$Excel.DisplayAlerts = $False

#Open up a new workbook
$Dest = $Excel.Workbooks.Add()

#Loop through files, opening each, selecting the Used range, and only grabbing the first 6 columns of it. Then find next available row on the destination worksheet and paste the data
ForEach($File in $Files[0..4]){
    $Source = $Excel.Workbooks.Open($File,$true,$true)
    If(($Dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($Dest.ActiveSheet.Range("A1").Value2))){ #If there is only 1 used cell and it is blank select A1
        [void]$source.ActiveSheet.Range("A1","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        [void]$Dest.Activate()
        [void]$Dest.ActiveSheet.Range("A1").Select()
    }Else{ #If there is data go to the next empty row and select Column A
        [void]$source.ActiveSheet.Range("A2","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        [void]$Dest.Activate()
        [void]$Dest.ActiveSheet.Range("A$(($Dest.ActiveSheet.UsedRange.Rows|Select -last 1).row+1)").Select()
    }
    [void]$Dest.ActiveSheet.Paste()
    $Source.Close()
}
$Dest.SaveAs("C:UsersMattDocumentsExcel TestBook1.xlsx",51)
$Dest.close()
$Excel.Quit()

这将得到一个 Excel 文件列表，打开 Excel 并创建一个新文档，然后循环浏览文件列表，打开它们，选择 AF 列，复制这些列，返回到新工作簿并选择下一个可用行，并粘贴其他工作簿中的数据.然后它关闭该文件并移至下一个文件.最后，它会保存包含所有数据的工作簿并关闭 Excel.

That'll get a list of excel files, open Excel and create a new document, then cycle through the list of files, opening them, selecting Columns A-F, copying those columns, going back to the new workbook and selecting the next available row, and pasting the data from the other workbook. Then it closes that file and moves on to the next one. At the end it saves your workbook with all the data and closes excel.

这篇关于如何使用powershell复制多个excel工作表并制作一个新的?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！