Solved: Spreadsheet with live user defined function

It took me a little while to work out all of the parts so I thought I would document the process he for others.

Bascially I wanted to create a user defined function (UDF) for Vaadins Spreadsheet component which provided live updates to the UI.

Consider the case of a spreadsheet that wants to take live updates for a stock price, perform a calculation and dispaly the results. Each time the stock price changes the calculation should automatically update.

A live UDF lets you do just that.

So the example implements a UDF that allows you to write a spreadsheet formula of the form:
=getprice("IBM", "BID")

The getprice UDF will return the Bid price of IBM each time it changes.

So here are the three classes required.
Enjoy!

package au.com.noojee.dashboard;

import javax.servlet.annotation.WebServlet;

import com.vaadin.addon.spreadsheet.Spreadsheet;
import com.vaadin.annotations.Push;
import com.vaadin.annotations.Theme;
import com.vaadin.annotations.VaadinServletConfiguration;
import com.vaadin.annotations.Widgetset;
import com.vaadin.server.VaadinRequest;
import com.vaadin.server.VaadinServlet;
import com.vaadin.ui.Component;
import com.vaadin.ui.UI;
import com.vaadin.ui.VerticalLayout;

/**
 * This UI is the application entry point. A UI may either represent a browser
 * window (or tab) or some part of a html page where a Vaadin application is
 * embedded.
 * <p>
 * The UI is initialized using {@link #init(VaadinRequest)}. This method is
 * intended to be overridden to add component to the user interface and
 * initialize non-component functionality.
 */
@Theme("dashboard")
@Widgetset("au.com.noojee.dashboard.DashboardWidgetset")
@Push
public class SpreadsheetUI extends UI
{
    private static final long serialVersionUID = 1L;
    private Spreadsheet spreadsheet;

    @WebServlet(urlPatterns = "/*", name = "SpreadsheetUIServlet", asyncSupported = true)
    @VaadinServletConfiguration(ui = SpreadsheetUI.class, productionMode = false)
    public static class SpreadsheetUIServlet extends VaadinServlet
    {
        private static final long serialVersionUID = 1L;
    }

    @Override
    protected void init(VaadinRequest request)
    {

        VerticalLayout layout = new VerticalLayout();
        this.setContent(layout);

        layout.setSizeFull();

        layout.addComponent(createSpreadsheet());
    }

    // Create the spread sheet and inject the UDF.
    Component createSpreadsheet()
    {
        VerticalLayout layout = new VerticalLayout();
        layout.setSizeFull();
        spreadsheet = new Spreadsheet();
        spreadsheet.setSizeFull();

        new DataSource().initFormula(spreadsheet);
        layout.addComponent(spreadsheet);

        return layout;
    }
}

package au.com.noojee.dashboard;

import java.util.HashSet;
import java.util.Set;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;

import org.apache.poi.ss.formula.functions.FreeRefFunction;
import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
import org.apache.poi.ss.formula.udf.DefaultUDFFinder;
import org.apache.poi.ss.formula.udf.UDFFinder;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFFont;

import com.vaadin.addon.spreadsheet.Spreadsheet;
import com.vaadin.ui.UI;

public class DataSource
{
    private static final String UDFNAME = "getstockprice";
    private Set<Cell> cellTracker = new HashSet<>();
    private GetStockPrice formula = new GetStockPrice();

    public void initFormula(Spreadsheet spreadsheet)
    {
        String[] functionNames =
        { UDFNAME };
        FreeRefFunction[] functionImpls =
        {  formula };

        // Get the UDF finder
        UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls);
        UDFFinder udfToolpack = new AggregatingUDFFinder(udfs);

        spreadsheet.getWorkbook().addToolPack(udfToolpack);

        // We need to track what cells use our UDF so we know which ones
        // to refresh each time the UDF value changes.
        
        spreadsheet.addCellValueChangeListener(event -> {
            Set<CellReference> cells = event.getChangedCells();

            // A cell has just changed.
            // Lets see if its using our UDF.
            for (CellReference ref : cells)
            {
                Cell cell = spreadsheet.getCell(ref);
                if (cell.getCellType() == Cell.CELL_TYPE_FORMULA)
                {
                    // The cell contains a formula so lets see if it contains ours.
                    String formula = cell.getCellFormula();
                    if (formula.contains(UDFNAME))
                    {
                        // Yep it contains our formula
                        // so add it to the tracker.
                        System.out.println("adding" + cell);
                        cellTracker.add(cell);
                    }
                }
                else
                {
                    // The cell isn't a formula, but it may have been
                    // previously so lets ensure we remove it from tracking.
                    System.out.println("Removing cell" + cell);
                    if (cellTracker.remove(cell) == true)
                        System.out.println("Removed cell" + cell);
                }
                System.out.println(cellTracker.size());
            }

        });

        
        /**
         * This is not what you want to do!!
         * 
         * Essentially this is a background thread designed to simulate a price change.
         * In reality you would have a link to some external data source that provided 
         * pricing. Each time the price changes you would update the cells
         * that reference that price.
         * Try to be as selective as possible when choosing the cells to update as 
         * the refresh mechanism is expensive.
         */
        ScheduledExecutorService executor = Executors.newSingleThreadScheduledExecutor();
        executor.scheduleAtFixedRate(new Runnable()
        {
            @Override
            public void run()
            {
                UI.getCurrent().access(new Runnable()
                {

                    @Override
                    public void run()
                    {
                        // simulate a stock price change
                        formula.updatePrice();
                        System.out.println("refresh");
                        
                        // refresh all cells that use the stock price UDF.
                        spreadsheet.refreshCells(cellTracker);
                    }

                });

            }
        }, 5, 5, TimeUnit.SECONDS);

    }
}

package au.com.noojee.dashboard;

import java.util.Random;

import org.apache.poi.ss.formula.OperationEvaluationContext;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.eval.EvaluationException;
import org.apache.poi.ss.formula.eval.NumberEval;
import org.apache.poi.ss.formula.eval.OperandResolver;
import org.apache.poi.ss.formula.eval.ValueEval;
import org.apache.poi.ss.formula.functions.FreeRefFunction;

/**
 * 
 * Method to simulate retrieving stock prices
 */
public class GetStockPrice implements FreeRefFunction
{

    // select a random starting price.
    volatile double  currentPrice = 10.50;

    @Override
    public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec)
    {
        double result = 0;
        
        if (args.length != 2)
        {
            return ErrorEval.VALUE_INVALID;
        }

        // The stock code that is being monitored
        String stockCode;
        // The price field that is being pulled (e.g. Bid, Last, High, Low etc)
        String priceField;

        try
        {
            ValueEval v1 = OperandResolver.getSingleValue(args[0], ec.getRowIndex(), ec.getColumnIndex());
            ValueEval v2 = OperandResolver.getSingleValue(args[1], ec.getRowIndex(), ec.getColumnIndex());

            stockCode = OperandResolver.coerceValueToString(v1);
            priceField = OperandResolver.coerceValueToString(v2);

            result = currentPrice;

            checkValue(result);

        }
        catch (EvaluationException e)
        {
            e.printStackTrace();
            return e.getErrorEval();
        }

        return new NumberEval(result);
    }

    /**
     * Excel does not support infinities and NaNs, rather, it gives a #NUM!
     * error in these cases
     * 
     * @throws EvaluationException
     *             (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt>
     */
    final void checkValue(double result) throws EvaluationException
    {
        if (Double.isNaN(result) || Double.isInfinite(result))
        {
            throw new EvaluationException(ErrorEval.NUM_ERROR);
        }
    }

    public void updatePrice()
    {
        this.currentPrice += new Random().nextDouble();

    }

}

Correction..

The formula implemented in the example is:
=getstockprice(<stockcode>, <field>)

e.g.
=getstockcode("IBM", "BID")

In reality the sample ignores the contents of both passed arguments.