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();
}
}