使用电源外壳将Excel工作簿数据从文件夹合并到csv文件

Question

在一个文件夹中，我有大约 20 个 excel 工作簿，每个工作簿都有用于上传 excel 工作表的 MIS，我想合并来自 MIS 的每个工作簿中的所有数据，以便使用 Powershell 将 excel 工作表上传到新的 csv 文件

以下是我尝试过的代码。但是我想要浏览文件夹方法。

#Get a list of files to copy from
$Files = GCI 'C:Usersr.shishodiaDesktopMay 2018' | ?{$_.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..20]){
$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
$Source.WorkSheets.item("MIS for Upload").Activate()
[void]$source.ActiveSheet.Range("A1","R$(($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
$Source.WorkSheets.item("MIS for Upload").Activate()
[void]$source.ActiveSheet.Range("A2","R$(($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:Usersr.shishodiaDesktopBook2.xlsx",51)
$Dest.close()
$Excel.Quit()

Answer 1

为此，您可以使用 ImportExcel 模块 - 包含在 repo README 中的安装指南。

安装此模块后，可以轻松使用 cmdletImport-Excel如下所示：

$Files = GCI 'C:Usersr.shishodiaDesktopMay 2018' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName
$Temp = @()
ForEach ($File in $Files[0..20]) { # or 19 if you want to have exactly 20 files imported
$Temp += Import-Excel -Path $File -WorksheetName 'MIS for Upload' `
| Select Property0, Property1, Property2, Property3, Property4, Property5
}

要导出(您编写了 CSV，但目标文件格式显示xlsx(：

$Temp | Export-Excel 'C:Usersr.shishodiaDesktopBook2.xlsx'

或

$Temp | Export-Csv 'C:Usersr.shishodiaDesktopBook2.csv'

Answer 2

那个 ImportExcel 模块真的很方便 ;-(