Hyia,
In the code below, I implemented a one way communication between my Excel AddIn and the Main java application of the World cancer research fund Continuous Update project.
Basically, when a user highlight a cell with a WCRF_CODE (string code attributed to a publication), the button "View In App" is enabled and once clicked, launch the window of the selected publication in the main java application. (If the java application is not found, then it starts a new process).
Here are the steps that lead to functional interprocess communication between a custom excel Ribbon and a java application.
1) An Excel 2010 ribbon with one button with callback that is triggered when the cell selection change or if the content change in the workbook.
2) Now that my controls exists there is plenty code to write to make them actually do something useful. First of all I need to catch the events "SheetChange" and "SheetSelectionChange". Since my excel file is an addin I can't just write them down in ThisWorkbook as this is never called. Instead I need to write a class module: "clsApplicationEvents" with the following code.
In the code below, I implemented a one way communication between my Excel AddIn and the Main java application of the World cancer research fund Continuous Update project.
Basically, when a user highlight a cell with a WCRF_CODE (string code attributed to a publication), the button "View In App" is enabled and once clicked, launch the window of the selected publication in the main java application. (If the java application is not found, then it starts a new process).
Here are the steps that lead to functional interprocess communication between a custom excel Ribbon and a java application.
1) An Excel 2010 ribbon with one button with callback that is triggered when the cell selection change or if the content change in the workbook.
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="ribbonLoad">
<ribbon startFromScratch="false">
<qat/>
<tabs>
<tab id="cupTabAddIn" label="CUP">
<group id="MyGroup2"
label="Java Application">
<button id="button_launch" label="See in App" imageMso="MailMergeResultsPreview" onAction="seeWCRF" getEnabled="isWCRFSelected" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
2) Now that my controls exists there is plenty code to write to make them actually do something useful. First of all I need to catch the events "SheetChange" and "SheetSelectionChange". Since my excel file is an addin I can't just write them down in ThisWorkbook as this is never called. Instead I need to write a class module: "clsApplicationEvents" with the following code.
Option Explicit
Private WithEvents oApp As Excel.Application
Property Set XL(Application As Excel.Application)
Set oApp = Application
End Property
Property Get XL() As Excel.Application
Set XL = oApp
End Property
Private Sub oApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)
ribbon.ribbon.InvalidateControl "button_launch"
End Sub
Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
ribbon.ribbon.InvalidateControl "button_launch"
End Sub
In a normal module called "clsApplicationEventStandard", I wrote
Option Explicit
Global AnyWorkbook As clsApplicationEvents
And finally I wrote, in ThisWorkbook:
Option Explicit
Private Sub Workbook_Open()
Set AnyWorkbook = New clsApplicationEvents
Set AnyWorkbook.XL = Excel.Application
Debug.Print "Opening"
End Sub
Now we have an addin with buttons, and we can use the workbook event as in non-addins environment. But our button if pressed are not doing anything yet.
We need to define some more methods...
Option Explicit
' We keep a reference to the loaded Ribbon
Public ribbon As IRibbonUI
'-----------------------------------------------------------------------------
' Save a reference to the Ribbon
' This is called from the ribbon's OnLoad event
'-----------------------------------------------------------------------------
Public Sub ribbonLoad(rb As IRibbonUI)
Set ribbon = rb
End Sub
Public Sub splitribbon(control As IRibbonControl)
Call wcrf_buttons.splitup
End Sub
Public Sub mergeribbon(control As IRibbonControl)
Call wcrf_buttons.getConcatenatedList
End Sub
Public Sub isWCRFSelected(control As IRibbonControl, ByRef enabled)
enabled = method_util.isWCRFCode(ActiveCell.Value)
End Sub
Public Sub seeWCRF(control As IRibbonControl)
Call JavaInterprocess.sendApp(ActiveCell.Value)
End Sub
Now, we have methods behind our buttons and these methods of course call other methods (so that I can use these methods without passing by the ribbon as well). The methods behind splitribbon and mergeribbon are trivials as well as the "isWCRFSelectedMethod" but I will write them down so you can have an idea of what they do. It is worth to note that isWCRFSelectedMethod uses regex and so need to have "Microsoft Vb Script Regular Expression 5.5" activated.
''Normally this method is in a module called wcrf_buttonsSub getConcatenatedList()
Dim clipboard As MSForms.DataObject
Dim rng As Range
Set clipboard = New MSForms.DataObject
Dim text$
Dim isFirst As Boolean
isFirst = True
Set rng = Selection.SpecialCells(xlCellTypeVisible)
For Each cell In rng
If (isFirst) Then
text = Trim(cell.Value)
isFirst = False
Else
text = text & ";" & Trim(cell.Value)
End If
Next
clipboard.SetText text
clipboard.PutInClipboard
End SubGiven a selected range of cells, the method above will concatenate the value in a string with a semi colon as separator. This string will then be placed in the clipboard.
Sub splitup()
Dim arrayOfRs
Dim delimitor$
Dim output$
Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject
delimitor = InputBox("Delimiter please", "Parameter required")
arrayOfRs = split(ActiveCell.Value, delimitor)
For i = 0 To UBound(arrayOfRs)
If (i <> 0) Then
output = output & vbCrLf & arrayOfRs(i)
Else
output = arrayOfRs(i)
End If
Next i
On Error Resume Next
clipboard.SetText output
clipboard.PutInClipboard
End Sub Given a String of item separated by a certain character, this method will split the text in row than can then be paste in Excel.
The method below return true if the format of the text is the one of a WCRF code.
''Normally this method is in a module called method_util
Public Function isWCRFCode(text As String) As String
Dim strPattern As String
Dim wcrfCodeList$
strPattern = "(LUN|COL|BRE|STM|OES|oes|SKI|NAS|CER|PAN|PRO|LIV)[0-9]{4,7}"
Dim regEx As New RegExp
Dim strInput As String
Dim Myrange As Range
Dim matches
With regEx
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
isWCRFCode = regEx.test(text)
End Function
Now come the big deal, we want to send data to the server (our main java application) For that I created a module called "JavaInterProcess" In the module you can find these methods
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) This is just a small definition of a kernel32 class that when called, will pause the application for dwMilliseconds. It is needed because if our application is not started when we send the command... nothing will happen. So we will use this to wait 10 seconds before the time we launch our application and the time we send the command to the server of our application.
Public Sub sendApp(wcrfcode As String)
Dim ProcID As Integer
'Find process of java application
'On error StartApp
On Error GoTo startApp
ProcID = findProcess()
GoTo manipulateApp
startApp:
ProcID = startApp()
manipulateApp:
' Activate the Java Application application.
On Error GoTo erreurmsg:
AppActivate (ProcID)
'send Command via server
Call serverSocket.sendMsg("127.0.0.1", 8084, "JAVACUP:" & wcrfcode)
Exit Sub
erreurmsg:
MsgBox ("unexpected error occured!")
End Sub This is the main method of our interprocess communication, it first see if our java application process is running by calling the method "findProcess()". If a process is there, it is fine we can activate the window of our current java process then call the method serverSocket.sendMsg(add, port, msg). Conversely if the application is not running then, we start the java application, wait 10 seconds and send the command.
Private Function findProcess() As Integer
Dim oShell As Object
Set oShell = CreateObject("WScript.Shell")
Dim oExec As Object
Dim oOutput As Object
Set oExec = oShell.Exec("jps")
Set oOutput = oExec.StdOut
Dim s As String
Dim sLine As String
While Not oOutput.AtEndOfStream
sLine = oOutput.ReadLine
'in debug ApplicationEntryPoint
If (InStr(1, sLine, "Program")) Then
s = Replace(sLine, "Program", "")
s = Replace(s, " ", "")
End If
Wend
On Error GoTo erreur:
findProcess = CInt(s)
Exit Function
erreur:
findProcess = CVErr(xlErrValue)
End Function The method findProcess above return a PID of the process or an errer xlErrValue if there is no process running.
Below is the code to start our application.
Private Function startApp() As Integer
Dim pid%
pid = Shell("java -jar ""C:\Program Files (x86)\wcrfapp\wcrfapp.jar"" ", vbHide)
Sleep (10000)
startApp = pid
End Function
I found the code of a server in VBA on this forum. So I will just copy paste it here as a reference:
Public Const AF_INET = 2
Public Const SOCK_STREAM = 1
Public Const SOCKET_ERROR = 1
Public Const FD_SETSIZE = 64
Public Const FIONBIO = 2147772030#
Public Const SOCKADDR_IN_SIZE = 16
Public Const FORMAT_MESSAGE_FROM_SYSTEM = &H1000
Public Type WSADATA
wVersion As Integer
wHighVersion As Integer
szDescription As String * 257
szSystemStatus As String * 129
iMaxSockets As Integer
iMaxUdpDg As Integer
lpVendorInfo As Long
End Type
Public Type SOCKADDR_IN
sin_family As Integer
sin_port As Integer
sin_addr As Long
sin_zero As String * 8
End Type
Public Type fd_set
fd_count As Long
fd_array(FD_SETSIZE) As Long
End Type
Public Type timeval
tv_sec As Long
tv_usec As Long
End Type
Public Declare Function WSAStartup Lib "wsock32.dll" (ByVal intVersionRequested As Integer, lpWSAData As WSADATA) As Long
Public Declare Function WSACleanup Lib "wsock32.dll" () As Long
Public Declare Function w_socket Lib "wsock32.dll" Alias "socket" (ByVal lngAf As Long, ByVal lngType As Long, ByVal lngProtocol As Long) As Long
Public Declare Function w_closesocket Lib "wsock32.dll" Alias "closesocket" (ByVal SocketHandle As Long) As Long
Public Declare Function w_bind Lib "wsock32.dll" Alias "bind" (ByVal socket As Long, Name As SOCKADDR_IN, ByVal namelen As Long) As Long
Public Declare Function w_connect Lib "wsock32.dll" Alias "connect" (ByVal socket As Long, Name As SOCKADDR_IN, ByVal namelen As Long) As Long
Public Declare Function w_send Lib "wsock32.dll" Alias "send" (ByVal socket As Long, buf As Any, ByVal length As Long, ByVal flags As Long) As Long
Public Declare Function w_recv Lib "wsock32.dll" Alias "recv" (ByVal socket As Long, buf As Any, ByVal length As Long, ByVal flags As Long) As Long
Public Declare Function w_select Lib "wsock32.dll" Alias "select" (ByVal nfds As Long, readfds As fd_set, writefds As fd_set, exceptfds As fd_set, timeout As timeval) As Long
Public Declare Function htons Lib "wsock32.dll" (ByVal hostshort As Integer) As Integer
Public Declare Function ntohl Lib "wsock32.dll" (ByVal netlong As Long) As Long
Public Declare Function inet_addr Lib "wsock32.dll" (ByVal Address As String) As Long
Public Declare Function ioctlsocket Lib "wsock32.dll" (ByVal socket As Long, ByVal cmd As Long, argp As Long) As Long
Public Declare Function FormatMessage Lib "kernel32" Alias "FormatMessageA" (ByVal dwFlags As Long, lpSource As Any, ByVal dwMessageId As Long, ByVal dwLanguageId As Long, ByVal lpBuffer As String, ByVal nSize As Long, Arguments As Long) As Long
Private Sub CloseSocket(socket As Long)
If socket <> -1 Then
w_closesocket socket
End If
WSACleanup
End Sub
Public Function sendMsg(Address As String, port As Integer, URI As String)
Dim ret As Long
Dim SocketHandle As Long
Dim wd As WSADATA
Dim localAddress As SOCKADDR_IN
Dim serverAddress As SOCKADDR_IN
Dim URIRequest As String
Dim retBuff(1024) As Byte
Dim retString As String
Dim tempString As String
VcitajURI = ""
SocketHandle = -1
ret = WSAStartup(&H101, wd)
If ret <> 0 Then GoTo ErrorHandler
SocketHandle = w_socket(AF_INET, SOCK_STREAM, 0)
If SocketHandle = -1 Then GoTo ErrorHandler
localAddress.sin_family = AF_INET
localAddress.sin_port = 0
localAddress.sin_addr = 0
ret = w_bind(SocketHandle, localAddress, SOCKADDR_IN_SIZE)
If ret = -1 Then GoTo ErrorHandler
serverAddress.sin_family = AF_INET
serverAddress.sin_port = htons(port)
serverAddress.sin_addr = inet_addr(Address)
ret = w_connect(SocketHandle, serverAddress, SOCKADDR_IN_SIZE)
If ret = -1 Then GoTo ErrorHandler
URIRequest = URI & vbCrLf
ret = w_send(SocketHandle, ByVal URIRequest, Len(URIRequest), 0)
If ret = -1 Then GoTo ErrorHandler
ErrorHandler:
CloseSocket SocketHandle
End Function
We now have a functioning client that will send data to our application. Now we need to program the application so that it understand the command (WCRF code) send by the client.
This is the start of our application.
public static void main(String[] args) {
/**
* Listener, if any key is sent from Excel the code of the key will be
* */
Thread server=new Thread(new Listener());
server.start();
All the code of the server is centralised in the class "Listener below":
package GUI.InterProcessCommunication;
import BO.Incidence.IncidenceMain_BO;
import GUI.Incidence.IncidenceSearch_Launcher;
import GUI.SplashScreen.SplashScreen_DataLoading;
import java.awt.*;
import java.awt.event.KeyEvent;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.net.InetSocketAddress;
import java.net.ServerSocket;
import java.net.Socket;
import java.nio.ByteBuffer;
import java.nio.channels.ServerSocketChannel;
import java.nio.channels.SocketChannel;
import java.util.ArrayList;
import java.util.Optional;
/**
* Created by casteven on 11/10/2016.
*/
public class Listener implements Runnable {
int port = 8084;
@Override
public void run() {
int portNumber = 8084;
try {
ServerSocket socket = new ServerSocket(portNumber);
while (true) {
Socket clientSocket = socket.accept();
BufferedReader in = new BufferedReader(new InputStreamReader(clientSocket.getInputStream()));
// Initiate conversation with client
String inputLine = "";
while ((inputLine = in.readLine()) != null) {
if (inputLine.startsWith("JAVACUP:")) {
String wcrf = inputLine.replaceAll("JAVACUP:", "");
Optional<IncidenceMain_BO> publicationFromExcel = BL.Incidence.IncidenceMain_BL.selectAll_Studies_per_WCRF(wcrf).stream().findAny();
if (publicationFromExcel.isPresent()) {
Runnable r = new IncidenceSearch_Launcher(SplashScreen_DataLoading.Instance(), publicationFromExcel.get());
Thread t = new Thread(r);
t.start();
}
}
}
}
}catch(IOException e){
e.printStackTrace();
}
}
}
As you can see it is an infinite loop in a thread that accepts connections and in case that the received WCRF_Code exists, launch the window with right publication data.
This is how to ribbon looks like for those interested:
Now the idea is to extend this communications with more objects and methods! Yeeepie yeah
No comments:
Post a Comment