在Powershell中查找Excel文件的内容 [英] Finding content of Excel file in Powershell

问题描述

我目前正在研究一个相当大的PowerShell脚本。但是，我陷入了一部分。问题是如下。

我有各种具有相同文件名的报告，最后只有不同的时间戳。在报告中，我有一个字段显示从何时到报告的日期。

---> 2/1/2015 5:00:00 AM to 3/1/2015 5:00:00 AM< ---这是它的样子。

此字段是随机放置在Excel工作表上。相当于A5到Z16的范围。我想要的脚本是：

阅读文件 / 检查范围单元格的日期，如果找到日期并符合我的搜索条件，请关闭工作表并将其移动到其他文件夹 / 如果日期不匹配，关闭和检查下一个XLS文件

这是我到目前为止：

 code> $ File =C：\test.XLS
 $ SheetName =Sheet1
＃设置Excel，打开$文件并设置第一个工作表
 $ Excel = New-Object -ComObject Excel.Application 
 $ Excel.visible = $ true 
 $ Workbook = $ Excel.workbooks.open（$ file）
 $ Worksheets = $ Workbooks.worksheets 
 $ WorkSheet = $ WorkBook.sheets.item（$ SheetName）
 $ SearchString =AM#just用于测试目的，因为它在每个报告中
 $ Range = $ Worksheet.Range（A1 ：Z1）。EntireColumn 
 $ Search = $ Range.find（$ SearchString）
  

解决方案

如果要搜索A到Z的整列，您可以指定范围：

  $ Range = $ Worksheet.Range（A：Z）
  

然后你应该能够执行一个 $ Range.Find（$ SearchText）如果找到文本，它会将其发现的第一个单元格回放，否则返回任何内容。所以像你一样启动Excel，然后执行一个 ForEach 循环，并在里面打开一个工作簿，搜索你的文本，如果它被发现关闭它，移动它，停止循环。如果找不到工作簿，并移动到下一个文件。以下工作对我来说很好：

  $ Destination ='C：\Temp\Backup'
 $ SearchText ='3/23/2015 10:12:19 AM'
 $ Excel = New-Object -ComObject Excel.Application 
 
 $ Files = Get-ChildItem$ env：USERPROFILE\\ \\Documents\ * .xlsx|选择-Expand FullName 
 $ counter = 1 
 ForEach（$ Files in $ Files）{
 Write-Progress -Activity检查：$ file-Status$ $ $ files.count）-PercentComplete（$ counter * 100 / $ files.count）
 $ Workbook = $ Excel.Workbooks.Open（$ File）
 If（$ Workbook.Sheets.Item（1） .Range（A：Z）。Find（$ SearchText））{
 $ Workbook.Close（$ false）
 Move-Item -Path $ File -Destination $ Destination 
 $文件到$目的地
 break 
} 
 $ workbook.close（$ false）
 $ counter ++ 
} 
  

我甚至有足够的野心在这里添加一个进度条，以便您可以看到有可能看到的文件数量，完成了多少个文件，那么它正在看什么文件。

现在这一切都假定您确切知道该单元格中的字符串（至少是部分）。如果你错了，那就不行了。检查含糊的事情需要更长的时间，因为您不能使用Excel的匹配功能，并且必须让PowerShell一次检查每个单元格范围。

I am currently working on a fairly large powershell script. However, I got stuck at one part. The issue is the following.

I have various reports with the same file name, they just have a different time stamp at the end. Within the report, I have a field displaying the date from when to when the report is from.

---> 2/1/2015 5:00:00AM to 3/1/2015 5:00:00AM <--- This is what it looks like.

This field is randomly placed on the Excel Sheet. Pretty much in the range of A5 to Z16. What I would like the script to do is:

Read the file / Check the range of cells for the dates, if the date is found and it matches my search criteria, close the sheet and move it to a different folder / If date does not match, close and check next XLS file

This is what I got so far:

$File = "C:\test.XLS" 
$SheetName = "Sheet1"
# Setup Excel, open $File and set the the first worksheet
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($file)
$Worksheets = $Workbooks.worksheets
$WorkSheet = $WorkBook.sheets.item($SheetName)
$SearchString = "AM" #just for test purposes since it is in every report
$Range = $Worksheet.Range("A1:Z1").EntireColumn
$Search = $Range.find($SearchString)

解决方案

If you want it to search the entire column for A to Z you would specify the range:

$Range = $Worksheet.Range("A:Z")

Then you should be able to execute a $Range.Find($SearchText) and if the text is found it will spit back the first cell it finds it in, otherwise it returns nothing. So start Excel like you did, then do a ForEach loop, and inside that open a workbook, search for your text, if it is found close it, move it, stop the loop. If it is not found close the workbook, and move to the next file. The following worked just fine for me:

$Destination = 'C:\Temp\Backup'
$SearchText = '3/23/2015  10:12:19 AM'
$Excel = New-Object -ComObject Excel.Application

$Files = Get-ChildItem "$env:USERPROFILE\Documents\*.xlsx" | Select -Expand FullName
$counter = 1
ForEach($File in $Files){
    Write-Progress -Activity "Checking: $file" -Status "File $counter of $($files.count)" -PercentComplete ($counter*100/$files.count)
    $Workbook = $Excel.Workbooks.Open($File)
    If($Workbook.Sheets.Item(1).Range("A:Z").Find($SearchText)){
        $Workbook.Close($false)
        Move-Item -Path $File -Destination $Destination
        "Moved $file to $destination"
        break
    }
    $workbook.close($false)
    $counter++
}

I even got ambitious enough to add a progress bar in there so you can see how many files it has to potentially look at, how many it's done, and what file it's looking at right then.

Now this does all assume that you know exactly what the string is going to be (at least a partial) in that cell. If you're wrong, then it doesn't work. Checking for ambiguous things takes much longer, since you can't use Excel's matching function and have to have PowerShell check each cell in the range one at a time.

这篇关于在Powershell中查找Excel文件的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！