如何使这个脚本循环遍历目录中的所有文件?

How can I make this script loop through all the files in the directory?

如何使这个脚本循环遍历目录中的所有文件?

I believe I got it to save the files the way I want them, but I can do it one at time.

我相信我可以按我想要的方式保存文件，但我可以一次做一个。

I am learning Powershell...

我正在学习Powershell……

How can I save each worksheet from the workbook (excel 2010) to this format: file name + "-" + sheet name as CSV?

如何将工作表(excel 2010)中的每个工作表保存到以下格式:文件名+ "-" +工作表名作为CSV?

• I have up to 3 worksheets per workbook on some workbooks (might be more...)
• 我在一些工作簿上有多达3个工作表(可能更多…)
• Is there is an optimal way of performing this?
• 有没有一种最优的方法来实现这一点?

Thank you,

谢谢你！

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$excel = new-object -ComObject "Excel.Application";
$excel.DisplayAlerts=$True;
$excel.Visible =$false;

$wb = $excel.Workbooks.Open($scriptPath + "\1B1195.xlsb");

   foreach($ws in $wb.Worksheets) {
    if($ws.name -eq "OP10" -or $ws.name -eq "OP20" -or $ws.name -eq "OP30") {
        Write-Host $ws.name;

   $ws.SaveAs($scriptPath + "\" + $wb.name + "-" + $ws.name + ".csv", [Object] [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVMSDOS);

}
}

$wb.close($False)
$excel.Quit();
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel);

1 个解决方案

#1

I haven't tested it, but I think it should work. I've explained the changes in the code:

我还没有测试过，但我认为它应该有用。我已经解释了代码的变化:

$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$excel = new-object -ComObject "Excel.Application";
$excel.DisplayAlerts=$True;
$excel.Visible =$false;

#Find every xlsb file in $scriptpath. 
#If you want to search through subdirectories also, add " -Recurse" before "| Foreach-Object"
Get-ChildItem -Path $scriptPath -Filter ".xlsb" | ForEach-Object {

    #Inside this loop, $_ is the processed xlsb-file.
    #$_.Fullname includes the full path, like c:\test\myexcelfile.xlsb"

    #File-specific code
    $wb = $excel.Workbooks.Open($_.FullName);

    foreach($ws in $wb.Worksheets) {
        if($ws.name -eq "OP10" -or $ws.name -eq "OP20" -or $ws.name -eq "OP30") {
            Write-Host $ws.name;

            $ws.SaveAs($scriptPath + "\" + $wb.name + "-" + $ws.name + ".csv", [Object] [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVMSDOS);
            }
    }

    $wb.close($False)
    #End file-specific code

}    

$excel.Quit();
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel);