Spreadsheet.refreshCells not working

Flow
1

I’ve implemented a user defined function (UDF) in POI. It works very nicely.

The problem is that the value calculated by the function changes over time and I need to refresh the spreadsheet to reflect the latest value.

So I’ve written the following code to do the refresh:

[code]
ScheduledExecutorService executor = Executors.newSingleThreadScheduledExecutor();
executor.scheduleAtFixedRate(new Runnable() {

        @Override
        public void run()
        {
            for (Cell cell : cellTracker)
            {
                CalculateMortgage.incDelta();
                //formulaEvaluator.evaluateFormulaCell(cell);
                formulaEvaluator.clearAllCachedResultValues();
                formulaEvaluator.notifyUpdateCell(cell);
                
            }
            System.out.println("refresh");
            spreadsheet.refreshCells(cellTracker);
            
        }}, 5, 5, TimeUnit.SECONDS);
    

}

[/code]When I call spreadsheet.refreshCells I can see that it calls my UDF evaluate method and then new value is correctly returned.
The problem is that the UI isn’t reflecting the updated value.
If I click the cell to edit it and then save the edit the latest value will appear, but I need to do this programatically.

Any suggestions as to what I’m doing wrong?

2

P.S.
I’ve traced through the refreshCells code and I can see that it calls my evaluate method and appears to mark my cell for update. But nothing happens in the ui.

3

So I’ve created a complete working example that demonstrates the problem:

package au.com.noojee.dashboard;

import java.util.Collection;
import java.util.Iterator;
import java.util.Random;
import java.util.Set;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

import javax.servlet.annotation.WebServlet;

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.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.vaadin.alump.gridstack.GridStackButton;
import org.vaadin.alump.gridstack.GridStackLayout;

import com.google.gwt.thirdparty.guava.common.collect.Lists;
import com.vaadin.addon.spreadsheet.Spreadsheet;
import com.vaadin.annotations.Theme;
import com.vaadin.annotations.VaadinServletConfiguration;
import com.vaadin.annotations.Widgetset;
import com.vaadin.data.util.BeanItemContainer;
import com.vaadin.event.LayoutEvents;
import com.vaadin.server.FontAwesome;
import com.vaadin.server.Resource;
import com.vaadin.server.VaadinRequest;
import com.vaadin.server.VaadinServlet;
import com.vaadin.ui.Alignment;
import com.vaadin.ui.Button;
import com.vaadin.ui.Component;
import com.vaadin.ui.CssLayout;
import com.vaadin.ui.Grid;
import com.vaadin.ui.HorizontalLayout;
import com.vaadin.ui.Label;
import com.vaadin.ui.Notification;
import com.vaadin.ui.Panel;
import com.vaadin.ui.PasswordField;
import com.vaadin.ui.TextArea;
import com.vaadin.ui.TextField;
import com.vaadin.ui.UI;
import com.vaadin.ui.VerticalLayout;
import com.vaadin.ui.themes.ValoTheme;

/**
 * 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")
public class Dashboard extends UI
{

    private Spreadsheet spreadsheet;

    @WebServlet(urlPatterns = "/*", name = "DashboardServlet", asyncSupported = true)
    @VaadinServletConfiguration(ui = Dashboard.class, productionMode = false)
    public static class DashboardServlet extends VaadinServlet
    {
    }

    @Override
    protected void init(VaadinRequest request)
    {

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

        layout.setSizeFull();

        layout.addComponent(createSpreadsheet());

        ScheduledExecutorService executor = Executors.newSingleThreadScheduledExecutor();
        executor.scheduleAtFixedRate(new Runnable()
        {

            @Override
            public void run()
            {
                UI.getCurrent().access(new Runnable()
                {

                    @Override
                    public void run()
                    {

                        Cell cell = spreadsheet.getCell(1, 1);
                        if (cell == null)
                            cell = spreadsheet.createCell(1, 1, "Hello World");

                        // Clone Cell CellStyle
                        CellStyle style = cloneStyle(cell);
                        // Clone CellStyle Font
                        Font font = cloneFont(style);
                        // Toggle current bold state
                        font.setBold(!font.getBold());
                        style.setFont(font);
                        cell.setCellStyle(style);

                        System.out.println("refresh");
                        spreadsheet.refreshCells(cell);
                    }

                });

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

    }

    //
    Component createSpreadsheet()
    {
        VerticalLayout layout = new VerticalLayout();
        layout.setSizeFull();
        spreadsheet = new Spreadsheet();
        spreadsheet.setSizeFull();

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

        return layout;
    }

    private Font cloneFont(CellStyle cellstyle)
    {
        Font newFont = spreadsheet.getWorkbook().createFont();
        Font originalFont = spreadsheet.getWorkbook().getFontAt(cellstyle.getFontIndex());
        if (originalFont != null)
        {
            newFont.setBold(originalFont.getBold());
            newFont.setItalic(originalFont.getItalic());
            newFont.setFontHeight(originalFont.getFontHeight());
            newFont.setUnderline(originalFont.getUnderline());
            newFont.setStrikeout(originalFont.getStrikeout());
            // This cast can only be done when using .xlsx files
            XSSFFont originalXFont = (XSSFFont) originalFont;
            XSSFFont newXFont = (XSSFFont) newFont;
            newXFont.setColor(originalXFont.getXSSFColor());
        }
        return newFont;
    }

    private CellStyle cloneStyle(Cell cell)
    {
        CellStyle newStyle = spreadsheet.getWorkbook().createCellStyle();
        newStyle.cloneStyleFrom(cell.getCellStyle());
        return newStyle;
    }

}
4

I should note that example doesn’t have a UDF. It simply changes the font bold/not bold and does the update from a background thread.

Most of the code was copied from the Vaadin Spreadsheet Tutorial.

So the only thing unusual here is the background thread.

5

I’ve attached the complete eclipse project to make creating a test environment easier.

6

OK, so found my (stupid) problem.

As I was doing the refresh from a background thread there was nothing to get the client to refresh the UI.
Simply adding the @Push annotation to the UI fixed the problem.