Excel& Powershell：Bulk查找并替换公式中使用的URL [英] Excel & Powershell: Bulk Find and replace URL's used in formulas
问题描述
我正在创建一个脚本，我将提供一个SharePoint 2007文档库列表，其中将执行以下操作：
I'm working on creating a script that I will provide a list of SharePoint 2007 document libraries to which will do the following:
- 在Excel中，找到链接到其他工作簿+表单（XLS / XLSX）的单元格中的公式
- 如果找到，请替换公式链接
http：// serverold /网站/ doclib /与http：// servernew / sites / sitecollection / doclib并保存 - 其他，关闭工作簿并移动到下一个日志完整URL和任何更改的文件名
- In Excel, find formulas within cells that link to other workbooks + sheets (XLS/XLSX)
- If found, replace the formula link
http://serverold/site/doclib/withhttp://servernew/sites/sitecollection/docliband save - Else, close the workbook and move onto the next Log full URL and filename of any changes
使用此代码链接作为我的开始点我不能得到以下工作：
Using the code on this link as my starting point I cannot get the following working:
- 正则表达式使脚本检测公式中的URL
- 修改脚本以用单元格中公式中的新路径替换旧路径。
- a 分支处理匹配发现（保存并关闭），何时找不到（只是关闭）
我不是详细介绍我所做的所有研究（信息在地面上很轻），只是在另一个线程上提到，您可以在Excel中集中列举这些链接，但没有提供示例或链接，当我试图枚举链接集合，我使用的示例工作簿为空，我认为这是链接 。
I'm not going into detail on all the research I've done (info is very light on the ground), just that it is mentioned on another thread that you can enumerate these links centrally in Excel but no example or links were given and when I've tried to enumerate the links collection in PowerShell (with Excel 2010 installed) it is empty with the example workbook I'm using which I know as "links" in that sense.
枚举链接集的示例
$File = "C:\temp\example.xls"
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $true
$Workbook = $Excel.workbooks.open($file)
$Workbook.LinkSources
所以它提出了一个问题，哪个方法是对的？
So it begs the question, which method is right?
Excel公式示例
=+'http://server.old/site/site/Work in Progress `enter code here`Documents/Statements/[Hierarchy2011.xls]Reports'!$AD$37+'http://server.old/site/site/Work in Progress Documents/
脚本枚举链接（从我提到的链接作为起点） -
Script to enumerate links (from the link I mentioned as my starting point) -
$path = "C:\temp"
$excelSheets = Get-Childitem -Path $path -Include *.xls,*.xlsx -Recurse
$excel = New-Object -comobject Excel.Application
$excel.visible = $false
foreach($excelSheet in $excelSheets)
{
$workbook = $excel.Workbooks.Open($excelSheet)
"There are $($workbook.Sheets.count) sheets in $excelSheet"
For($i = 1 ; $i -le $workbook.Sheets.count ; $i++)
{
$worksheet = $workbook.sheets.item($i)
"`tLooking for links on $($worksheet.name) worksheet"
$rowMax = ($worksheet.usedRange.rows).count
$columnMax = ($worksheet.usedRange.columns).count
For($row = 1 ; $row -le $rowMax ; $row ++)
{
For($column = 1 ; $column -le $columnMax ; $column ++)
{
[string]$formula = $workSheet.cells.item($row,$column).formula
if($formula -match "\w?:\\\w*\\\[\w*\.xls\w?\]") {"`t`t$($formula)"}
} #end for $column
} #end for $row
$worksheet = $rowmax = $columnMax = $row = $column = $formula = $null
} #end for
$workbook.saved = $true
$workbook.close()
} #end foreach
$excel.quit()
$excel = $null
[gc]::collect()
[gc]::WaitForPendingFinalizers()
感谢任何人谁可以帮助和你的时间。
Bests，
Ash
Thanks to anyone who can help and for your time. Bests, Ash
推荐答案
你会考虑简单地使用：
$formula -replace 'http://server.old/','http://server.new/'
更新：我最初以为你正在使用正则表达式（仅）问题，但是阅读该脚本，我想你需要一点更多的帮助。
Update: I initially thought that you were having a problem with the regex (only), but reading through that script, I think you need a bit more help than that.
让我参与相关代码的一部分：
Let me take the part of the code that's relevant:
For($i = 1 ; $i -le $workbook.Sheets.count ; $i++)
{
$worksheet = $workbook.sheets.item($i)
"`tLooking for links on $($worksheet.name) worksheet"
$rowMax = ($worksheet.usedRange.rows).count
$columnMax = ($worksheet.usedRange.columns).count
For($row = 1 ; $row -le $rowMax ; $row ++)
{
For($column = 1 ; $column -le $columnMax ; $column ++)
{
[string]$formula = $workSheet.cells.item($row,$column).formula
## This is irrelavant, it's trying to match a file...
## if($formula -match "\w?:\\\w*\\\[\w*\.xls\w?\]") {"`t`t$($formula)"}
$changed = $formula -replace 'http://server.old/','http://server.new/'
if ($formula -ne $changed) {
$workSheet.cells.item($row,$column).formula = $changed
}
} #end for $column
} #end for $row
$worksheet = $rowmax = $columnMax = $row = $column = $formula = $null
} #end for
## The line below is actually cause file to be not saved when closing it as you are telling excel, hey you are saved.
## $workbook.saved = $true
## I would use this:
if (!$Workbook.saved) { $workbook.save() }
$workbook.close()
} #end foreach
现在，当我在这里，让我告诉你，在尝试修改别人的脚本之前，我将如何调查这个业务：在控制台调查对象！
Now, while I am on this, let me tell you how I would go about investigating this business before attempting to modify someone else's script: In console investigate objects!
对于这个例子，我创建了一个简单的电子表格（a2 .xls）在c：\temp\a\
For this example, I created a simple spreadsheet (a2.xls) in c:\temp\a\
PS H:\> $excel = New-Object -com Excel.Application
PS H:\> $workbook = $excel.Workbooks.Open("C:\temp\a\a2.xls")
PS H:\> $worksheet=$workbook.Sheets.item(1)
乐趣开始：
PS H:\> $worksheet |get-member -Type Properties
TypeName: System.__ComObject#{000208d8-0000-0000-c000-000000000046}
Name MemberType Definition
---- ---------- ----------
Application Property Application Application () {get}
AutoFilter Property AutoFilter AutoFilter () {get}
AutoFilterMode Property bool AutoFilterMode () {get} {set}
Cells Property Range Cells () {get}
CircularReference Property Range CircularReference () {get}
CodeName Property string CodeName () {get}
Columns Property Range Columns () {get}
Comments Property Comments Comments () {get}
ConsolidationFunction Property XlConsolidationFunction ConsolidationFunction () {get}
ConsolidationOptions Property Variant ConsolidationOptions () {get}
ConsolidationSources Property Variant ConsolidationSources () {get}
Creator Property XlCreator Creator () {get}
CustomProperties Property CustomProperties CustomProperties () {get}
DisplayAutomaticPageBreaks Property bool DisplayAutomaticPageBreaks () {get} {set}
DisplayPageBreaks Property bool DisplayPageBreaks () {get} {set}
DisplayRightToLeft Property bool DisplayRightToLeft () {get} {set}
EnableAutoFilter Property bool EnableAutoFilter () {get} {set}
EnableCalculation Property bool EnableCalculation () {get} {set}
EnableFormatConditionsCalculation Property bool EnableFormatConditionsCalculation () {get} {set}
EnableOutlining Property bool EnableOutlining () {get} {set}
EnablePivotTable Property bool EnablePivotTable () {get} {set}
EnableSelection Property XlEnableSelection EnableSelection () {get} {set}
FilterMode Property bool FilterMode () {get}
HPageBreaks Property HPageBreaks HPageBreaks () {get}
Hyperlinks Property Hyperlinks Hyperlinks () {get}
Index Property int Index () {get}
（缩短输出）。看到超链接的属性吗？那是你指的是什么？
看看：
(shortened output). See the hyperlinks property there? Was that what you were referring to? Take a look:
PS H:\> $worksheet.hyperlinks
Application : Microsoft.Office.Interop.Excel.ApplicationClass
Creator : 1480803660
Parent : System.__ComObject
Name : http://old.server/adil1/hellow
Range : System.__ComObject
Shape :
SubAddress :
Address : http://old.server/adil1/hellow
Type : 0
EmailSubject :
ScreenTip :
TextToDisplay :
你没有看到我的来源，但让我告诉你：它是从公式中提取URL。所以，如果我更改名称和地址...不幸的是，我不能。实际上，我可以改变地址来指向新的服务器，但它的'name'属性是只读的（见它没有设置）：
You don't see my source but let me tell you this: It's extracting URLs from the formulas. so, if I change the name and the address... Unfortunately, I cannot. I can in fact change the address to point to new server, but its 'name' property is read-only (see it does not have set):
PS H:\> $worksheet.hyperlinks |gm
TypeName: System.__ComObject#{00024431-0000-0000-c000-000000000046}
Name MemberType Definition
---- ---------- ----------
Address Property string Address () {get} {set}
Name Property string Name () {get}
这就是为什么你可能更好：
This is why you're probably better off:
- 将单元格内容作为字符串读取，
- 仅替换服务器名称
- 如果单元格没有你正在寻找的东西，则不会做任何事情，而$ changed =
$ formula - 如果没有，请回到新的公式。 >
- read the content of the cell as string,
- replace just the server name
- if cell does not have what you are looking for -replace won't do anything and $changed = $formula
- If not, right back the new formula.
当然，你不需要使用-replace不是唯一的方法，但你得到的想法...
Of course, you do not need to use -replace is not the only way but you get the idea...
希望这有帮助！
这篇关于Excel& Powershell：Bulk查找并替换公式中使用的URL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
