有由谷歌托管在这里一个非常酷的差异类：
There’s a really cool diff class hosted by Google here:
http://$c$c.google.com/p/google-diff-match-patch/
我以前用它在几个网站，但现在我需要在的Excel宏两个单元之间的比较文本。
I’ve used it before on a few web sites, but now I need to use it within an Excel macro to compare text between two cells.
然而，这仅仅是在JavaScript，Python和Java的，和C ++进行编程，而不是VBA。
However, it is only available in JavaScript, Python, Java, and C++, not VBA.
我的用户仅限于Excel 2003中，这样一个纯.NET解决方案是行不通的。翻译code到VBA手动将耗费太多的时间，使升级上的困难。
My users are limited to Excel 2003, so a pure .NET solution wouldn't work. Translating the code to VBA manually would take too much time and make upgrading difficult.
一个选择，我认为是使用.NET编译器来编译JavaScript或Java源代码（JScript.NET或J＃），使用反射来为VB.NET输出，最后手动降级的VB.NET code到VBA，给我一个纯净的VBA解决方案。有问题得到它来编译任何.NET编译器后，我放弃了这条道路。
One option I considered was to compile the JavaScript or Java source using the .NET compilers (JScript.NET or J#), use Reflector to output as VB.NET, then finally downgrade the VB.NET code manually to VBA, giving me a pure VBA solution. After having problems getting it to compile with any .NET compiler, I abandoned this path.
假设我能得到一个工作.NET库，我也用ExcelDna（ HTTP：//www.$c $ cplex.com / exceldna ），一个开放源代码的Excel加载项，使.NET code集成变得更加容易。
Assuming I could have gotten a working .NET library, I could have also used ExcelDna (http://www.codeplex.com/exceldna), an open-source Excel add-in to make .NET code integration easier.
我最后的想法是主机的Internet Explorer对象，将其发送JavaScript源，并调用它。即使我得到这个工作，我的猜测是，这将是污垢缓慢和混乱。
My last idea was to host an Internet Explorer object, send it the JavaScript source, and calling it. Even if I got this to work, my guess is it would be dirt-slow and messy.
更新：解决方案找到
我使用的接受的答案如下所述的WSC方法。我不得不改变WSC codea的点点清理比较和给我回阵列的VBA兼容数组：
I used the WSC method described below by the accepted answer. I had to change the WSC code a little to clean up the diffs and give me back a VBA-compatible array of arrays:
function DiffFast(text1, text2)
{
var d = dmp.diff_main(text1, text2, true);
dmp.diff_cleanupSemantic(d);
var dictionary = new ActiveXObject("Scripting.Dictionary"); // VBA-compatible array
for ( var i = 0; i < d.length; i++ ) {
dictionary.add(i, JS2VBArray(d[i]));
}
return dictionary.Items();
}
function JS2VBArray(objJSArray)
{
var dictionary = new ActiveXObject("Scripting.Dictionary");
for (var i = 0; i < objJSArray.length; i++) {
dictionary.add( i, objJSArray[ i ] );
}
return dictionary.Items();
}
我注册了自来水公司和它的工作就好了。在code在VBA调用其计算方法如下：
I registered the WSC and it worked just fine. The code in VBA for calling it is as follows:
Public Function GetDiffs(ByVal s1 As String, ByVal s2 As String) As Variant()
Dim objWMIService As Object
Dim objDiff As Object
Set objWMIService = GetObject("winmgmts:")
Set objDiff = CreateObject("Google.DiffMatchPath.WSC")
GetDiffs = objDiff.DiffFast(s1, s2)
Set objDiff = Nothing
Set objWMIService = Nothing
End Function
（我试图保持一个单一的全球objWMIService为和objDiff身边，所以我就不必创建/销毁这些为每个单元格，但它似乎并没有做出对性能有差异。）
(I tried keeping a single global objWMIService and objDiff around so I wouldn't have to create/destroy these for each cell, but it didn't seem to make a difference on performance.)
然后我写我主要的宏。它有三个参数：一个范围（一列）原始值的，一系列的新的价值观，以及一个范围，其中差异应该转储的结果。所有的假设的有排相同数量的，我没有任何严重的错误检查回事。
I then wrote my main macro. It takes three parameters: a range (one column) of original values, a range of new values, and a range where the diff should dump the results. All are assumed to have the same number of row, I don't have any serious error-checking going on here.
Public Sub DiffAndFormat(ByRef OriginalRange As Range, ByRef NewRange As Range, ByRef DeltaRange As Range)
Dim idiff As Long
Dim thisDiff() As Variant
Dim diffop As String
Dim difftext As String
difftext = ""
Dim diffs() As Variant
Dim OriginalValue As String
Dim NewValue As String
Dim DeltaCell As Range
Dim row As Integer
Dim CalcMode As Integer
这些未来三线加快更新而不后来搞坏了用户的preferred计算模式：
These next three lines speed up the update without botching the user's preferred calculation mode later:
Application.ScreenUpdating = False
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
For row = 1 To OriginalRange.Rows.Count
difftext = ""
OriginalValue = OriginalRange.Cells(row, 1).Value
NewValue = NewRange.Cells(row, 1).Value
Set DeltaCell = DeltaRange.Cells(row, 1)
If OriginalValue = "" And NewValue = "" Then
删除了previous diff文件，如果有的话，是很重要的：
Erasing the previous diffs, if any, is important:
Erase diffs
此测试是一个可视化的快捷键为我的用户所以很清楚时，有没有变化：
This test is a visual shortcut for my users so it's clear when there's no change at all:
ElseIf OriginalValue = NewValue Then
difftext = "No change."
Erase diffs
Else
把所有的文字一起作为增量单元格的值，文本是否一致，插入或删除：
Combine all the text together as the delta cell value, whether the text was identical, inserted, or deleted:
diffs = GetDiffs(OriginalValue, NewValue)
For idiff = 0 To UBound(diffs)
thisDiff = diffs(idiff)
difftext = difftext & thisDiff(1)
Next
End If
您必须设置值的在的开始格式化：
You have to set the value before starting the formatting:
DeltaCell.value2 = difftext
Call FormatDiff(diffs, DeltaCell)
Next
Application.ScreenUpdating = True
Application.Calculation = CalcMode
End Sub
这里的code，国米$ P $点的比较和格式化三角洲细胞：
Here's the code that interprets the diffs and formats the delta cell:
Public Sub FormatDiff(ByRef diffs() As Variant, ByVal cell As Range)
Dim idiff As Long
Dim thisDiff() As Variant
Dim diffop As String
Dim difftext As String
cell.Font.Strikethrough = False
cell.Font.ColorIndex = 0
cell.Font.Bold = False
If Not diffs Then Exit Sub
Dim lastlen As Long
Dim thislen As Long
lastlen = 1
For idiff = 0 To UBound(diffs)
thisDiff = diffs(idiff)
diffop = thisDiff(0)
thislen = Len(thisDiff(1))
Select Case diffop
Case -1
cell.Characters(lastlen, thislen).Font.Strikethrough = True
cell.Characters(lastlen, thislen).Font.ColorIndex = 16 ' Dark Gray http://www.microsoft.com/technet/scriptcenter/resources/officetips/mar05/tips0329.mspx
Case 1
cell.Characters(lastlen, thislen).Font.Bold = True
cell.Characters(lastlen, thislen).Font.ColorIndex = 32 ' Blue
End Select
lastlen = lastlen + thislen
Next
End Sub
有优化一些机会，但到目前为止，它的工作就好了。感谢大家谁帮助！
There are some opportunities for optimization, but so far it's working just fine. Thanks to everyone who helped!
推荐答案
最简单的方法可能是嵌入的Javascript差异的逻辑将直接使用Javascript COM组件。这种通过一种叫 Windows脚本组件是可能的。
The simplest approach may be to embed the Javascript diff logic into a COM component directly using Javascript. This is possible via something called "Windows Script Components".
下面的打造供水公司的教程。
一个Windows脚本组件是在脚本中定义的COM组件。的接口的组件是通过COM，这意味着它是VBA友好。该逻辑是在任何Windows脚本主机兼容的语言来实现，如JavaScript或者VBScript。自来水公司在一个单独的XML文件，其中嵌入逻辑，组件类别ID，这些方法，注册逻辑，等等定义。
A Windows Script Component is a COM component that is defined in script. The interface to the component is via COM, which means it is VBA friendly. The logic is implemented in any Windows Scripting Hosting -compatible language, like JavaScript or VBScript. The WSC is defined in a single XML file, which embeds the logic, the component Class ID, the methods, the registration logic, and so on.
还有一个tool可创建一个WSC 帮助。基本上它是一个向导式的东西，询问您的问题，并填充XML模板研究。我自己，我刚开始用一个例子的.wsc文件和编辑它手动使用文本编辑器。这是pretty的不言自明的。
There's also a tool available to help in creating a WSC. Basically it is a wizard-type thing that asks you questions and fills in the XML template. Myself, I just started with an example .wsc file and edited it by hand with a text editor. It's pretty self-explanatory.
这种方法定义的脚本（在的.wsc文件）COM组件是可调用就像任何其他COM组件，从可以与COM跳舞的环境。
A COM component defined this way in script (in a .wsc file) is callable just like any other COM component, from any environment that can dance with COM.
更新：我花了几分钟，并产生了WSC的GoogleDiff。在这里，它是。
UPDATE: I took a few minutes and produced the WSC for GoogleDiff. Here it is.
<?xml version="1.0"?>
<package>
<component id="Cheeso.Google.DiffMatchPatch">
<comment>
COM Wrapper on the Diff/Match/Patch logic published by Google at http://code.google.com/p/google-diff-match-patch/.
</comment>
<?component error="true" debug="true"?>
<registration
description="WSC Component for Google Diff/Match/Patch"
progid="Cheeso.Google.DiffMatchPatch"
version="1.00"
classid="{36e400d0-32f7-4778-a521-2a5e1dd7d11c}"
remotable="False">
<script language="VBScript">
<![CDATA[
strComponent = "Cheeso's COM wrapper for Google Diff/Match/Patch"
Function Register
MsgBox strComponent & " - registered."
End Function
Function Unregister
MsgBox strComponent & " - unregistered."
End Function
]]>
</script>
</registration>
<public>
<method name="Diff">
<parameter name="text1"/>
<parameter name="text2"/>
</method>
<method name="DiffFast">
<parameter name="text1"/>
<parameter name="text2"/>
</method>
</public>
<script language="Javascript">
<![CDATA[
// insert original google diff code here...
// public methods on the component
var dpm = new diff_match_patch();
function Diff(text1, text2)
{
return dpm.diff_main(text1, text2, false);
}
function DiffFast(text1, text2)
{
return dpm.diff_main(text1, text2, true);
}
]]>
</script>
</component>
</package>
要使用的东西，你必须注册。在资源管理器中，右键单击它，然后选择注册。或者，在命令行： REGSVR32文件： C：脚本 GoogleDiff.wsc
To use that thing, you have to register it. In Explorer, right click on it, and select "Register". or, from the command line: regsvr32 file:c:scriptsGoogleDiff.wsc
我没有尝试从VBA使用它，但这里是一个使用该组件的一些VBScript中code。
I didn't try using it from VBA, but here is some VBScript code that uses the component.
Sub TestDiff()
dim t1
t1 = "The quick brown fox jumped over the lazy dog."
dim t2
t2 = "The large fat elephant jumped over the cowering flea."
WScript.echo("")
WScript.echo("Instantiating a Diff Component ...")
dim d
set d = WScript.CreateObject("Cheeso.Google.DiffMatchPatch")
WScript.echo("Doing the Diff...")
x = d.Diff(t1, t2)
WScript.echo("")
WScript.echo("Result was of type: " & TypeName(x))
' result is all the diffs, joined by commas.
' Each diff is an integer (position), and a string. These are separated by commas.
WScript.echo("Result : " & x)
WScript.echo("Transform result...")
z= Split(x, ",")
WScript.echo("")
redim diffs(ubound(z)/2)
i = 0
j = 0
For Each item in z
If (j = 0) then
diffs(i) = item
j = j+ 1
Else
diffs(i) = diffs(i) & "," & item
i = i + 1
j = 0
End If
Next
WScript.echo("Results:")
For Each item in diffs
WScript.echo(" " & item)
Next
WScript.echo("Done.")
End Sub
相关推荐
最新文章