Как я могу использовать JavaScript в макросе Excel?
Здесь есть классный класс diff, размещенный Google:
http://code.google.com/p/google-diff-match-patch/
Я использовал его раньше на нескольких веб-сайтах, но теперь мне нужно использовать его в макросе Excel для сравнения текста между двумя ячейками.
Однако он доступен только в JavaScript, Python, Java и C++, но не в VBA.
Мои пользователи ограничены Excel 2003, поэтому чистое решение.NET не сработает. Перевод кода в VBA вручную занял бы слишком много времени и затруднил бы обновление.
Один из вариантов, который я рассмотрел, состоял в том, чтобы скомпилировать исходный код JavaScript или Java с использованием компиляторов.NET (JScript.NET или J#), использовать Reflector для вывода в виде VB.NET, а затем, наконец, вручную понизить код VB.NET до VBA, что дает мне Решение VBA. После проблем с его компиляцией любым компилятором.NET я отказался от этого пути.
Предполагая, что я мог бы получить работающую библиотеку.NET, я мог бы также использовать ExcelDna ( http://www.codeplex.com/exceldna), надстройку Excel с открытым исходным кодом, чтобы упростить интеграцию кода.NET.
Моя последняя идея состояла в том, чтобы разместить объект Internet Explorer, отправить ему исходный код JavaScript и вызвать его. Даже если бы я получил это на работу, я думаю, что это будет грязно-медленно и грязно.
ОБНОВЛЕНИЕ: Решение найдено!
Я использовал метод WSC, описанный ниже принятым ответом. Мне пришлось немного изменить код WSC, чтобы очистить различия и вернуть мне VBA-совместимый массив массивов:
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();
}
Я зарегистрировал WSC, и он работал просто отлично. Код в VBA для его вызова выглядит следующим образом:
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, чтобы мне не пришлось создавать / уничтожать их для каждой ячейки, но, похоже, это не повлияло на производительность.)
Затем я написал свой основной макрос. Он принимает три параметра: диапазон (один столбец) исходных значений, диапазон новых значений и диапазон, в котором diff должен выводить результаты. Предполагается, что у всех одинаковое количество строк, здесь нет серьезной проверки ошибок.
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
Следующие три строки ускоряют обновление, не нарушая предпочтительный режим вычислений пользователя позже:
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
Стирание предыдущих различий, если таковые имеются, важно:
Erase diffs
Этот тест является визуальным ярлыком для моих пользователей, поэтому он понятен, когда нет никаких изменений:
ElseIf OriginalValue = NewValue Then
difftext = "No change."
Erase diffs
Else
Объедините весь текст вместе как значение дельта-ячейки, независимо от того, был ли текст идентичным, вставленным или удаленным:
diffs = GetDiffs(OriginalValue, NewValue)
For idiff = 0 To UBound(diffs)
thisDiff = diffs(idiff)
difftext = difftext & thisDiff(1)
Next
End If
Вы должны установить значение перед началом форматирования:
DeltaCell.value2 = difftext
Call FormatDiff(diffs, DeltaCell)
Next
Application.ScreenUpdating = True
Application.Calculation = CalcMode
End Sub
Вот код, который интерпретирует различия и форматирует дельта-ячейку:
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
Есть некоторые возможности для оптимизации, но пока она работает просто отлично. Спасибо всем, кто помог!
4 ответа
Самым простым подходом может быть встраивание логики сравнения Javascript в компонент COM напрямую с использованием Javascript. Это возможно через то, что называется " Компоненты скрипта Windows".
Компонент Windows Script - это компонент COM, определенный в сценарии. Интерфейс к компоненту через COM, что означает, что это VBA дружественных. Логика реализована на любом языке, совместимом с Windows Scripting Hosting, например, JavaScript или VBScript. WSC определен в одном XML-файле, который включает логику, идентификатор класса компонента, методы, логику регистрации и т. Д.
Также имеется инструмент, помогающий в создании WSC. По сути, это вопрос типа мастера, который задает вам вопросы и заполняет шаблон XML. Я сам начал с примера файла.wsc и отредактировал его вручную с помощью текстового редактора. Это довольно очевидно.
Компонент COM, определенный таким образом в скрипте (в файле.wsc), вызывается так же, как и любой другой компонент COM, из любой среды, которая может танцевать с COM.
ОБНОВЛЕНИЕ: я взял несколько минут и произвел WSC для GoogleDiff. Вот.
<?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:\scripts\GoogleDiff.wsc
Я не пытался использовать его из VBA, но вот код VBScript, который использует компонент.
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
Скриптовый движок Windows позволит вам запустить библиотеку JavaScript. Это хорошо работает по моему опыту.
Мое предложение было бы, что бы вы ни делали, вы оборачиваете это в оболочку COM. VBA лучше всего работает с COM-объектами, поэтому вы можете скомпилировать их как компонент.NET, а затем представить как COM-объект, используя функциональные возможности взаимодействия.NET.
В качестве альтернативы вы также можете использовать объекты хоста сценариев Windows для выполнения файла Javascript и возврата вам результата.
Вот еще один вариант для рассмотрения, хотя я ни в коем случае не утверждаю, что он лучший.
- Убедитесь, что версия Python компилируется в IronPython. (Здесь не должно быть никаких проблем, или не более, чем небольшое количество портирования.)
- Создайте библиотеку надстроек Excel с использованием C# и создайте для нее ссылку на IronPython.
- Оберните необходимые функции в вашей надстройке C# Excel.