How to read dynamically changing values from Excel sheet in java without saving Excel sheet

Question

I'm using Nest Trader application (Share market Trading related), which provides values in Excel sheet and updates values in cells very fast in milliseconds.

I want to read those values and save them in a database.
I am using apache poi-3.17 library.

Answer 1

How to read dynamically changing values from Excel sheet in Java, without saving Excel sheet ?

If not achieved so far, may resort to use JDDE-library, as detailed below.

---

Milliseconds are plenty of time ...

In ordinary trading environments ( i.e. still not speaking about even a low-end of HFT ), all nanosecond [ns] latencies are curated, best avoided, not to add much above a few [us], so [ms] are indeed plenty of time - yet, still worth to offload any naive-implementations to the "remote" side of a principally distributed-system, as this both "exports" such a workload ( not to spend critical time on your part of the platform ) + it also avoids chained-latencies, as decisions are taken with a shortest possible amount of time ( and next advertised only in cases, that are relevant to your part of the platform ).

---

May re-use this :

JDDE is JNI-based free open source Java library which allows Java applications to communicate with native applications on Windows platform ( if these still permit that - had problems in Vista64, but that goes beyond the scope of this post ) via a Dynamic Data Exchange ( a.k.a. the DDE ) protocol.

---

A brute-DDE-force example :

As an example of using a kind of a brute-force, the code below will setup a DDE connection with a running Microsoft Excel from Java application.

Another, a way smarter way, if the Excel-DDE implementation still supported this in 2018+, is to subscribe to all changes of a "monitored"-content ( as this is way closer to the Trading-eco-system and REUTERS systems were using this as early as from 199x, even in days, Windows O/S was not yet equipped with TCP/IP based networking, that worked using DDE already those days :o)

The naive-brute-DDE-force example performs :
- a REQUEST operation to read data from A1-cell, as was a topic of this subject
+ a POKE operation to change data in A1 cell, and
+ an EXECUTE operation to close the Excel document, if needed for other use cases.

 /*
 * Copyright 2009 www.pretty-tools.com. All rights reserved.
 */

import com.pretty_tools.dde.ClipboardFormat;
import com.pretty_tools.dde.DDEException;
import com.pretty_tools.dde.DDEMLException;
import com.pretty_tools.dde.client.DDEClientConversation;

/**
 * Excel Example.
 *
 * @author Alexander Kozlov
 */

public class ExcelExample
{
    public static void main( String[] args )
    {
        try
        {
                         //                                DDE client
            final DDEClientConversation conversation = new DDEClientConversation();
                         //                     We can use UNICODE format if server prefers it
         // conversation.setTextFormat( ClipboardFormat.CF_UNICODETEXT );

            conversation.setTimeout( 3000 );
                         //                     Establish conversation with opened and active workbook
            conversation.connect( "Excel", "Sheet1" );
                         //                     if you have several opened files,
                         //                        you can establish conversation using file path
         // conversation.connect( "Excel", "C:\\Book1.xlsx" );
                         //                     or you can also specify Sheet
         // conversation.connect( "Excel", "C:\\[Book1.xlsx]Sheet2" );
            try
            {
                             //                  Requesting A1 value
                System.out.println( "A1 value: " + conversation.request( "R1C1" ) );
                             //                  Changing cell A1 value to "We did it!"
                conversation.poke( "R1C1", "We did it!" );
                conversation.poke( "R2C2", "We did it again!".getBytes(), ClipboardFormat.CF_TEXT );
                             //                  Fill several cells of the same row,
                             //                       using \t as separator
                conversation.poke( "R4", "Fill\tthe\trow" );
                             //                  Fill several cells of the same column,
                             //                       using \n as separator
                conversation.poke( "C4", "Fill\nthe\ncolumn" );
                             //                  Fill several cells with matrix,
                             //                       using \t as column separator
                             //                         and \n as row separator
                conversation.poke( "R5C5:R7C7", "1\t2\t3\n4\t5\t6\n7\t8\t9" );
                             //                  Run macro with name Macro1
             // conversation.execute( "[run(\"Macro1\")]" );
                             //                  Sending "close()" command
                conversation.execute( "[close()]" );
                             //                  or we can use byte array to send command
             // conversation.execute( "[close()]\0".getBytes() );
            }
            finally
            {
                conversation.disconnect();
            }
        }
        catch ( DDEMLException e )
        {
            System.out.println( "DDEMLException: 0x"
                              +  Integer.toHexString( e.getErrorCode() )
                              + " "
                              +  e.getMessage()
                                 );
        }
        catch ( DDEException e )
        {
            System.out.println( "DDEClientException: "
                              +  e.getMessage()
                                 );
        }
        catch ( Exception e )
        {
            System.out.println( "Exception: "
                              +  e
                                 );
        }
    }
}

---

A way SMART-er way
- avoid "manual" polling
+ offload to DDE-self-advertised change monitor / propagator :

The following example listens to changes in Excel cells. The .setEventListener() method is used to setup an asynchronous event handler ( an agent, working in a distributed-system to listen for remote-events and being responsible - once it receives any such - for a local-side, responsive reaction(s) to such event(s) that arived ).

In this simple mock-up demo, it will just print any remote A1-cell changes to a local console of this Java application ( and handle a disconnect event, an obviously similarly important step to do ):

/*
 * Copyright 2009 www.pretty-tools.com. All rights reserved.
 */

import com.pretty_tools.dde.DDEException;
import com.pretty_tools.dde.client.DDEClientConversation;
import com.pretty_tools.dde.client.DDEClientEventListener;
import com.pretty_tools.dde.DDEMLException;

/**
 * Excel Advice Example.
 *
 * @author Alexander Kozlov
 */

public class ExcelAdviceExample
{
    public static void main( String[] args )
    {
        try
        {
                         //                                DDE client
            final DDEClientConversation conversation = new DDEClientConversation();
                         //                     We can use UNICODE format if server prefers it
         // conversation.setTextFormat( ClipboardFormat.CF_UNICODETEXT );

            conversation.setEventListener( new DDEClientEventListener()
            {
                public void onDisconnect()
                {
                    System.out.println( "onDisconnect()" );
                }

                public void onItemChanged( String topic, String item, String data )
                {
                    System.out.println( "onItemChanged( "
                                      +  topic
                                      + ","
                                      +  item
                                      + ","
                                      +  data.trim()
                                      + ")"
                                         );
                }
            } );

            System.out.println(            "Connecting..." );
            conversation.connect( "Excel", "Sheet1" );
            try
            {            // .startAdvice(-------------------------------
                conversation.startAdvice(  "R1C1" );

                System.out.println(        "Press Enter to quit" );
                System.in.read();

                conversation.stopAdvice(   "R1C1" );
            }            // .stopAdvice(--------------------------------
            finally
            {
                conversation.disconnect();
            }
        }
        catch ( DDEMLException e )
        {
            System.out.println( "DDEMLException: 0x"
                              +  Integer.toHexString( e.getErrorCode() )
                              + " "
                              +  e.getMessage()
                                 );
        }
        catch ( DDEException e )
        {
            System.out.println( "DDEClientException: "
                              +  e.getMessage()
                                 );
        }
        catch ( Exception e )
        {
            System.out.println( "Exception: "
                              +  e
                                 );
        }
    }
}

---

JDDE is JNI-based library and it requires native code library ( a DLL for Windows ).

So, the DLL ( a == JavaDDE.dll file ) should be placed to current directory from which you run your example, or you should specify JVM java.library.path, parameter that points to a folder, where JavaDDE.dll file is stored.

Example:

 java -Djava.library.path="C:\jdde" ExcelExample