Spreadsheet-like component

I would like to be able to put up a grid with a variable number of rows, with individually selectable columns that can be edited. Think of an embedded spreadsheet for entering hours worked against Projects with sub-tasks assigned to them.

What is the best component to use to try and implement this behavior in Vaadin? I was thinking TreeTable (allowing for heirarchical projects/sub-tasks as rows) but didn’t see how to get the cells individually selectable to enter in #-hours worked.

Keyboard navigation would also be really nice.

Is this possible using Vaadin?

Hi,

I am not aware of any ready spreadsheet-like add-on component.

You can accomplish quite good interaction by using Table or TreeTable and selection or item click handler, keyboard actions, and generated columns and calculated footers. You could do it either with components in table or in editable mode with a field-factory generated components.

See
this example of interactive footer calculation with keyboard navigation
and
this more advanced navigation example
.

The main problem with using keyboard actions is that they are handled server-side so the navigation is a bit sluggish. There are also some focus difficulties. To make a proper spreadsheet component, you would need to make it with GWT and integrate it with Vaadin.

If someone is interested in going down the path that Marko has suggested – i.e., make a GWT component and integrate with Vaadin – an excellent place to start is the SocialCalc JavaScript code, written by
The Father of the Spreadsheet, Dan Brickin
.

With a few days to weeks of effort in “Vaadinizing” it, you will have an awesome spreadsheet. How much time it takes to Vaadinize it would depend upon how much control you want on the Vaadin side API of the spreadsheet.

The JavaScript code by Dan Bricklin is extremely well written. Very few bugs, if any. It is full featured, with formulas, infinite undo/redo buffer, etc. The code is also very easy to learn and modify. He hasn’t modified the code in about two years, but someone else has forked it on github to make some feature enhancements and bug fixes. You might want to start there. Here are some links:


The more active github fork maintained by infojunkie.


The original code by Dan Bricklin.

Hi,

I had the same problem (
https://vaadin.com/forum/-/message_boards/view_message/1050895
).

I wrote this component to edit bean items like in a spreadsheet. It’s not performant or well designed. It’s just a workarround.

It uses overlays (
https://vaadin.com/directory#addon/overlays
)

SpreadsheetTable


import java.util.Arrays;
import java.util.List;
import java.util.WeakHashMap;

import org.vaadin.overlay.CustomOverlay;

import com.vaadin.data.Container;
import com.vaadin.data.Property;
import com.vaadin.data.util.BeanItem;
import com.vaadin.data.util.MethodProperty.MethodException;
import com.vaadin.event.Action;
import com.vaadin.event.ItemClickEvent;
import com.vaadin.event.ItemClickEvent.ItemClickListener;
import com.vaadin.event.Action.Handler;
import com.vaadin.event.ShortcutAction;
import com.vaadin.ui.AbstractTextField;
import com.vaadin.ui.Alignment;
import com.vaadin.ui.Component;
import com.vaadin.ui.Field;
import com.vaadin.ui.Label;
import com.vaadin.ui.Panel;
import com.vaadin.ui.Table;
import com.vaadin.ui.TableFieldFactory;
import com.vaadin.ui.VerticalLayout;
import com.vaadin.ui.Table.ColumnGenerator;

public class SpreadsheetTable extends CustomTable implements ColumnGenerator
{
  private static final long serialVersionUID = 5319963532174389223L;

  KbdHandler kbdHandler = new KbdHandler();
  com.vaadin.event.Action.Container actionContainer;
  boolean editable=true;

  Object selectedPropertyId;

  public SpreadsheetTable()
  {
    super();
    super.setEditable(false);
    this.setSelectable(true);
    init();
  }

  public SpreadsheetTable(String caption, Container dataSource)
  {
    super(caption, dataSource);
    super.setEditable(false);
    this.setSelectable(true);
    init();
  }

  public SpreadsheetTable(String caption)
  {
    super(caption);
    super.setEditable(false);
    this.setSelectable(true);
    init();
  }

  public boolean isEditable()
  {
    return editable;
  }

  public void setEditable(boolean editable)
  {
    this.editable = editable;
  }

  private void init()
  {
    this.addStyleName("v-spreadsheettable");
    this.setCellStyleGenerator(new Table.CellStyleGenerator()
    {
      private static final long serialVersionUID = -3922656254249393334L;

      @Override
      public String getStyle(Object itemId, Object propertyId)
      {
        if (propertyId != null && propertyId.equals(selectedPropertyId))
        {
          if(!isEditable())
            return "colSelected-disabled";
          else if(getColumnGenerator(selectedPropertyId)!=SpreadsheetTable.this)
            return "colSelected-disabled";
          else
            return "colSelected";
        }
        return null;
      }
    });
    
    this.addListener(new ItemClickListener()
    {
      private static final long serialVersionUID = 5609728015410105798L;

      @Override
      public void itemClick(ItemClickEvent event)
      {
        selectedPropertyId = event.getPropertyId();
        setCellStyleGenerator(getCellStyleGenerator());
      }
    });
  }

  @Override
  public void setVisibleColumns(Object[] visibleColumns)
  {
    super.setVisibleColumns(visibleColumns);
    for(Object c:visibleColumns)
    {
      if(getColumnGenerator(c)==null)
        addGeneratedColumn(c, this);
    }
    if(visibleColumns.length!=0)
      selectedPropertyId=visibleColumns[0]
;
  }  
  
  @Override
  public void attach()
  {
    super.attach();
    Component c = this.getParent();
    while (c != null && !(c instanceof com.vaadin.event.Action.Container))
    {
      c = c.getParent();
    }
    if (c instanceof com.vaadin.event.Action.Container)
    {
      this.actionContainer = (com.vaadin.event.Action.Container) c;
      ((com.vaadin.event.Action.Container) c).addActionHandler(kbdHandler);
    }
  }

  @Override
  public void detach()
  {
    if (this.actionContainer != null)
      this.actionContainer.removeActionHandler(kbdHandler);
    this.actionContainer = null;
    super.detach();
  }

  // Keyboard navigation
  class KbdHandler implements Handler
  {
    private static final long serialVersionUID = 1L;
    Action tab_next = new ShortcutAction("Shift", ShortcutAction.KeyCode.TAB,
        null);
    Action tab_prev = new ShortcutAction("Shift+Tab",
        ShortcutAction.KeyCode.TAB,
        new int[] { ShortcutAction.ModifierKey.SHIFT });
    Action cur_down = new ShortcutAction("Down",
        ShortcutAction.KeyCode.ARROW_DOWN, null);
    Action cur_up = new ShortcutAction("Up", ShortcutAction.KeyCode.ARROW_UP,
        null);
    Action cur_left = new ShortcutAction("Left",
        ShortcutAction.KeyCode.ARROW_LEFT, null);
    Action cur_right = new ShortcutAction("Right",
        ShortcutAction.KeyCode.ARROW_RIGHT, null);
    Action enter = new ShortcutAction("Enter", ShortcutAction.KeyCode.ENTER,
        null);
    Action delete = new ShortcutAction("Delete", ShortcutAction.KeyCode.DELETE,
        null);
    
    public Action[] getActions(Object target, Object sender)
    {
      return new Action[] { tab_next, tab_prev, cur_down, cur_up, enter, delete, 
          cur_left, cur_right};
    }

    public void handleAction(Action action, Object sender, Object target)
    {
      if (target instanceof Table)
      {
        Table table = (Table) target;
        Object selected = table.getValue();

        if (selected == null)
          return;

        List<Object> vc = Arrays.asList(table.getVisibleColumns());

        if (selectedPropertyId == null)
          selectedPropertyId = vc.get(0);
        int pidIndex = vc.indexOf(selectedPropertyId);

        if (action == cur_right || action==tab_next)
        {
          selectedPropertyId = vc.get(Math.min(pidIndex + 1, vc.size() - 1));
          table.setCellStyleGenerator(table.getCellStyleGenerator());
        }
        else if (action == cur_left || action==tab_prev)
        {
          selectedPropertyId = vc.get(Math.max(pidIndex - 1, 0));
          table.setCellStyleGenerator(table.getCellStyleGenerator());
        }
        if (action == enter)
        {
          edit();
        }
        else if (action == delete)
        {
          deleteCell();
        }
      }
    }
  }
  
  
  Field currentEditor;
  CustomOverlay currentOverlay;
  
  ValueChangeListener changer=new ValueChangeListener()
  {
    private static final long serialVersionUID = 1200564874904805453L;
    @Override
    public void valueChange(Property.ValueChangeEvent event)
    {
      try
      {
        refreshRowCache();
        hideOverlay();
      }
      catch(Exception x)
      {
        x.printStackTrace();
      }
    }
  };
  
  void hideOverlay()
  {
    if(currentOverlay!=null && currentOverlay.isVisible())
      currentOverlay.setVisible(false);
    focus();
  }
  
  Handler fieldHandler=new Handler()
  {
    private static final long serialVersionUID = 8366821320667564639L;
    Action escape=new ShortcutAction("Escape", ShortcutAction.KeyCode.ESCAPE, null);
    Action enter=new ShortcutAction("Enter", ShortcutAction.KeyCode.ENTER, null);
    Action tab_next = new ShortcutAction("Shift", ShortcutAction.KeyCode.TAB,
        null);
    Action tab_prev = new ShortcutAction("Shift+Tab",
        ShortcutAction.KeyCode.TAB,
        new int[] { ShortcutAction.ModifierKey.SHIFT });
    
    @Override
    public Action[] getActions(Object target, Object sender)
    {
      return new Action[]{escape,enter,tab_next,tab_prev};
    }

    @Override
    public void handleAction(Action action, Object sender, Object target)
    {
      hideOverlay();
      if(action==tab_next)
        kbdHandler.handleAction(kbdHandler.tab_next, sender, SpreadsheetTable.this);
      else if(action==tab_prev)
        kbdHandler.handleAction(kbdHandler.tab_prev, sender, SpreadsheetTable.this);
    }
  };
  
  private void deleteCell()
  {
    Object value=this.getValue();
    if(value!=null && getColumnGenerator(selectedPropertyId)==this)
    {
      BeanItem<?> bean=new BeanItem<Object>(value);
      Property p=bean.getItemProperty(selectedPropertyId);
      try
      {
        p.setValue(null);
      }
      catch (MethodException e)
      {
        //primitive type editing
        if(p.getClass().equals(boolean.class) || p.getClass().equals(Boolean.class))
          p.setValue(Boolean.FALSE);
        else if(p.getClass().equals(char.class) || p.getClass().equals(Character.class))
          p.setValue('-');
        else
          p.setValue(0);
      }
      refreshRowCache();
    }
  }

  public void edit()
  {
    if(currentOverlay!=null)
    {
      getWindow().removeComponent(currentOverlay);
      currentOverlay=null;
      currentEditor=null;
    }
    
    if(!isEditable())
      return;
    
    TableFieldFactory tff=this.getTableFieldFactory();
    currentEditor=tff.createField(this.getContainerDataSource(), this.getValue(), this.selectedPropertyId,
        this);
    
    if(currentEditor==null)
      return;
    
    final Object value=this.getValue();
    BeanItem<?> bean=new BeanItem<Object>(value);
    currentEditor.setPropertyDataSource(bean.getItemProperty(selectedPropertyId));

    Label l=labelMap.get(value).get(selectedPropertyId);    
    
    Panel p=new Panel();
    p.setStyleName("light");
    p.setHeight("30px");
    p.setWidth("200px");
    p.addActionHandler(fieldHandler);
    p.setContent(new VerticalLayout());
    p.addComponent(currentEditor);
    //para corregir estilos
    p.addStyleName("v-app");
    
    currentOverlay=new CustomOverlay(p,l);
    
    currentEditor.setWidth("100%");

    currentEditor.addListener(changer);
    this.getWindow().addComponent(currentOverlay);
    currentOverlay.setComponentAnchor(Alignment.TOP_LEFT);
    currentOverlay.setOverlayAnchor(Alignment.TOP_LEFT);
    currentOverlay.setXOffset(-5);
    currentOverlay.setYOffset(-3);
    currentEditor.focus();
    if(currentEditor instanceof AbstractTextField)
    {
      ((AbstractTextField) currentEditor).selectAll();
    }
  }
  
  WeakHashMap<Object, WeakHashMap<Object,Label>> labelMap 
      = new WeakHashMap<Object, WeakHashMap<Object,Label>>();
  
  @Override
  public Object generateCell(Table source, Object itemId, Object columnId)
  {
    Label l=new Label();
    BeanItem<?> item=new BeanItem<Object>(itemId);
    l.setValue(formatPropertyValue(itemId, columnId, item.getItemProperty(columnId)));
    
    WeakHashMap<Object,Label> row =labelMap.get(itemId);
    if(row==null)
    {
      row=new WeakHashMap<Object, Label>();
      labelMap.put(itemId,row);
    }
    row.put(columnId,l);
    return l;
  }
}

Styles:



/* Spreadsheet Table*/
.v-spreadsheettable tr.v-selected.v-table-row
{
  background: white ;
  color: black;
  text-shadow: none;
}

.v-spreadsheettable tr.v-selected.v-table-row-odd
{
  background: #EFF0F1 ;
  color: black;
  text-shadow: none;  
}

.v-spreadsheettable tr.v-selected.v-table-row-odd.v-table-focus,
 .v-spreadsheettable tr.v-selected.v-table-row.v-table-focus
{
  border-right-color: #D3D4D5;
  border-top-style: hidden; 
  border-bottom-style: hidden; 
}

.v-spreadsheettable .v-table-focus .v-table-cell-content
{
  border-right-color: #D3D4D5;
  border-top-style: hidden; 
  border-bottom-style: hidden;
  padding-top: 1px; 
  padding-bottom: 1px; 
}

.v-spreadsheettable .v-selected .v-table-cell-content
{
  border-right-color: #D3D4D5;
}

.v-spreadsheettable .v-selected .v-table-cell-content-colSelected-disabled
{
  -moz-box-shadow: inset 0 0 5px #666666;
  -webkit-box-shadow: inset 0 0 5px #666666;
  box-shadow: inner 0 0 5px #666666;
  background: #FEFEFE ;
  border: 1px dotted #0066bd; 
  padding: 0px 5px; 
}

.v-spreadsheettable .v-selected .v-table-cell-content-colSelected
{
  -moz-box-shadow: inset 0 0 5px #93C0FF;
  -webkit-box-shadow: inset 0 0 5px #93C0FF;
  box-shadow: inner 0 0 5px #93C0FF;
  background: #E6F1FF ;
  border: 1px dotted #0066bd; 
  padding: 0px 5px; 
}	

It works fine. You have to use a BeanItemContainer and use the table into a Panel to get keyboard navigation working.
The TableFieldFactory is used to create the overlay editor when you press Enter. The table is not really editable and the tableFieldFactory is not used as in a regular Table.
I used a ColumnGenerator to render cells as Labels and anchor the overlays to them.

I have some problems with keboard navigation when you press left or right at the bottom of the table.

Please, feel free to use or improve this code. Let me know if you find or code a better component.

Regards