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

问题描述

我有大约70个excel文件，我想结合成一个单一的文件。每个文件只有一张表格，并遵循以下格式：

• 列A与列的标题


• 行B与第一个条目


• 行C与第二个条目


• 某些工作表上最多150行

我想从每列的列AF中刮取信息，并将其组合成一个新文件，其中包含我在同一目录中的所有其他文件的信息

注意：我只想捕获列AF，因为在列G中存在一个是，否数据集来管理列F中的下拉列表。

我尝试使用dugan的答案来自将Excel工作表从一个工作簿复制到另一个使用Powershell ，但它导致一个文件的一部分数据分布在两张表中。

这是代码：

  $ file1 ='C： \Users\Matthew.Andress\Documents\Excel Test\Book1.xlsx'＃source的fullpath 
 $ file2 ='C：\Users\Matthew.Andress\Documents\Excel Test\\ \\Book2.xlsx'＃目的地的完整路径
 $ xl = new-object -c excel.application 
 $ xl.displayAlerts = $ false＃不提示用户
 $ wb2 = $ xl 。开放源码，只读
 $ wb1 = $ xl.workbooks.open（$ file2）＃open target 
 $ sh1_wb1 = $ wb1.sheets（$ file1，$ null，$ true） .item（2）目标工作簿中的第二张表
 $ sheetToCopy = $ wb2.sheets.item（'Sheet1'）＃复制源代码
 $ sheetToCopy.copy（$ sh1_wb1）＃复制源表到目标工作簿
 $ wb2.close（$ false）＃关闭源代码工作簿w / o保存
 $ wb1.close（$ true）＃关闭并保存目标工作簿
 $ xl.quit（ ）
 spps -n excel 
  

任何建议？谢谢。

解决方案

好的，几天后，可以感谢周末。看看这个，看看你喜欢它。

 ＃获取从
 $复制的文件列表Files = GCI'C：\Users\Matt\Documents\Excel Test\'| ？{$ _。Extension -Matchxlsx？} |选择-ExpandProperty FullName 
 
 #Launch Excel，并按其声明（按钮确认）
 $ Excel = New-Object -ComObject Excel.Application 
 $ Excel.Visible = $ True 
 $ Excel.DisplayAlerts = $ False 
 
＃打开一个新的工作簿
 $ Dest = $ Excel.Workbooks.Add（）
 
＃遍历文件，打开每个，选择使用范围，只抓取它的前6列。然后在目标工作表上找到下一个可用的行，并粘贴数据
 ForEach（$ Files in $ Files [0..4]）{
 $ Source = $ Excel.Workbooks.Open（$ File，$ true，$ true）
如果（（$ Dest.ActiveSheet.UsedRange.Count -eq 1） - 和（[String] :: IsNullOrEmpty（$ Dest.ActiveSheet.Range（A1）。Value2））） {＃如果只有一个使用的单元格，它是空白的选择A1 
 [void] $ source.ActiveSheet.Range（A1，F $（（$ Source.ActiveSheet.UsedRange.Rows | Select -ast 1）.Row））Copy（）
 [void] $ Dest.Activate（）
 [void] $ Dest.ActiveSheet.Range（A1）。Select（）
否则{＃如果有数据转到下一个空行，并选择列A 
 [void] $ source.ActiveSheet.Range（A2，F $（（$ Source.ActiveSheet.UsedRange.Rows |选择-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：\Users\Matt\Documents\Excel Test\Book1.xlsx，51）
 $ Dest.close（）
 $ Excel.Quit（）
  

这将获得excel文件列表，打开Excel并创建一个新的文档，然后循环遍历文件列表，打开它们，选择列AF，复制这些列，返回新工作簿并选择下一个可用行，并从其他工作簿中粘贴数据。然后关闭该文件并移动到下一个文件。最后，它将您的工作簿保存在所有数据中并关闭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:

• Row A with Columns A-F of headings
• Row B with the first entry
• Row C with the second entry
• Up to 150 rows on some sheets

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.

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.

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.

Here is that code:

$file1 = 'C:\Users\Matthew.Andress\Documents\Excel Test\Book1.xlsx' # source's fullpath
$file2 = 'C:\Users\Matthew.Andress\Documents\Excel Test\Book2.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

Any suggestions? Thank you.

解决方案

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:\Users\Matt\Documents\Excel 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:\Users\Matt\Documents\Excel Test\Book1.xlsx",51)
$Dest.close()
$Excel.Quit()

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屋！