Important Notice - Forums is archived
To simplify things and help our users to be more productive, we have archived the current forum and focus our efforts on helping developers on Stack Overflow. You can post new questions on Stack Overflow or join our Discord channel.

Vaadin lets you build secure, UX-first PWAs entirely in Java.
Free ebook & tutorial.
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.