How to Implement Excel-style Overflowing Cells with FlexGrid (in Less Than 5 Minutes)

Overflowing Cells in Excel

In Excel, cells with long content overflow into empty adjacent cells:

In this image, cells A2 and A3 have long content that extends into cells B2, C2, and B3.

Of course, if the neighbor cells are not empty, the cell content is truncated as usual: In this image, cell C2 is no longer empty, so cell A2 no longer spills into it.

Overflowing Cells in FlexGrid

When a customer asked us about supporting cell overflow in the FlexGrid, our first idea was to do it using cell merging, a feature that the grid fully supports.

However, allowing cells to overflow into adjacent cells is not the same as merging the cells. When cells are merged, they become one, and users cannot select portions of the merged cell:

If we used cell merging, users would not be able to select cell B11 and enter some content (which would automatically prevent A11 from overflowing into it).

Because of this difference, we decided to use a different approach:

1. Use the FlexGrid's formatItem event to detect non-empty cells next to empty ones and add a "spill" class to those cells.
2. Use CSS to allow cells with the "spill" class to overflow into adjacent cells.

The first part was trivial. The formatItem event provides a reference to the grid and to the cell being rendered, so all we had to do was look for non-empty cells followed by empty ones:

// add "spill" class if this cell is not empty and the next one is
theGrid.formatItem.addHandler((s, e) => {
  if (e.panel == s.cells) {
    var spill = e.col < s.columns.length - 1 && 
        e.cell.innerHTML && !s.getCellData(e.row, e.col + 1);
    wijmo.toggleClass(e.cell, 'spill', spill);
  }
}

The code starts by checking that the cell belongs to the "cells" panel. That means we're dealing with a regular data cell, not a header or footer cell. Then it sets the "spill" variable to true if the cell is not empty and is followed by an empty cell. Finally, it sets or clears the "spill" class on the cell element.

The next step was to use the "spill" class identifier in CSS rules to make the cells overflow. This was a little more complicated.

First, we added a rule to make the "spill" cells overflow. This is done by setting their "overflow" property to "visible" (they are clipped by default). We also had to set their z-index so they render over adjacent empty cells:

.wj-cell.spill {
  overflow: visible;
  z-index: 1; /* render over empty cells */
}

We also had to adjust the z-index of the grid's marquee element and header panels to ensure they all render above the spill cells:

.wj-flexgrid .wj-marquee
.wj-flexgrid .wj-colheaders,
.wj-flexgrid .wj-rowheaders,
.wj-flexgrid .wj-topleft {
  z-index: 2; /* render over spill cells */
}

Almost there. The final step was to change the style applied to cells in the extended selection, so their foreground color remains black and the overflowing content remains visible:

.wj-state-multi-selected {
    background: rgba(0, 0, 0, 0.1);
    color: #000;
}

That's it. Our FlexGrid now supports overflowing, selectable, editable cells just like Excel:

You can check this out yourself, and play with the code and CSS in this fiddle: https://jsfiddle.net/Wijmo5/j7Lp4k8s/

Happy coding! If you have questions or comments be sure to enter them below.