Advanced span cells

This tutorial shows how to implement such a common task as totals row.

It's recommended to examine span cells tutorial before starting this one.

Let's change the table model from span cells tutorial a little:

IModelFieldGroup groups[] = new IModelFieldGroup[] {
  new ModelField( "DEPARTMENT", "Department" ),
  new ModelField( "USER_ID", "User identifier" )
    .withVisible( false ),
  new ModelField( "FAMILY_ID", "Family identifier" )
    .withVisible( false ),
  new ModelFieldGroup( "NAME", "Person name" )
    .withChild( new ModelField( "FIRST_NAME", "First name" ) )
    .withChild( new ModelField( "LAST_NAME", "Last name" ) ),
  new ModelField( "PHONE", "Phone number" )
};

We have hidden a column USER_ID and added a column DEPARTMENT. Let's fill the latter one with data: half of employees would be from one department and another half - from another one.

for ( int i = 0; i < rows.length; i++ )
  data.setValue( i, "DEPARTMENT", i < rows.length / 2 ? "Development" : "Testing" );

Also, we'll add a rule to merge cells with equal departments:

table.setUI( new JBroTableUI()
  .withSpan( new ModelSpan( "FAMILY_ID", "LAST_NAME" ).withColumns( "LAST_NAME", "PHONE" ) )
  .withSpan( new ModelSpan( "DEPARTMENT", "DEPARTMENT" ).withColumns( "DEPARTMENT" ) ) );

The result is on the picture (row sorting and column rearrangement work as expected):

The next picture is the result we want to gain:

There are three totals rows. Two of them show employees quantity in a department. The third one shows a sum over all departments. This library doesn't provide a way to calculate totals values. They should be calculated at the model level (e. g., in SQL). This library only provides a way to merge the totals cells according to given rules. So, we need to redefine our model and data to contain the totals values.

IModelFieldGroup groups[] = new IModelFieldGroup[] {
  new ModelField( "DEPARTMENT", "Department" ),
  new ModelField( "TOTAL_ID", "Total identifier for merging cells" )
    .withVisible( false ),
  new ModelField( "USER_ID", "User identifier" )
    .withVisible( false ),
  new ModelField( "FAMILY_ID", "Family identifier" )
    .withVisible( false ),
  new ModelFieldGroup( "NAME", "Person name" )
    .withChild( new ModelField( "FIRST_NAME", "First name" ) )
    .withChild( new ModelField( "LAST_NAME", "Last name" ) ),
  new ModelField( "PHONE", "Phone number" )
};

Here is a new field added: TOTAL_ID. It would be equal to 0 for a common totals row and to null for any other rows. We'll use the USER_ID for two intermediate totals rows. USER_ID would have distinct values for intermediate totals (0 and 1 - there are only two intermediate rows) and would be equal to null for the rest rows.

Why the values should be distinct? If they are equal then the two totals rows would be merged if they become sequential (after sorting, for example). We don't want them to merge.

Why the values for the rest rows should be null? It's a special value meaning that a row with null should not be checked by this rule. So, if the only non-null rows are totals rows then only totals rows would be merged according to this rule.

We'll have to rearrange our data and add totals:

data.setValue( 0, "FIRST_NAME", "John" );
data.setValue( 0, "LAST_NAME", "Doe" );
data.setValue( 1, "FIRST_NAME", "Jane" );
data.setValue( 1, "LAST_NAME", "Doe" );
data.setValue( 2, "FIRST_NAME", "Anony" );
data.setValue( 2, "LAST_NAME", "Mouse" );
data.setValue( 3, "FIRST_NAME", "William" );
data.setValue( 3, "LAST_NAME", "Perry" );
data.setValue( 4, "FIRST_NAME", "Morgan" );
data.setValue( 4, "LAST_NAME", "McQueen" );
    
data.setValue( 6, "FIRST_NAME", "Vanessa" );
data.setValue( 6, "LAST_NAME", "McQueen" );
data.setValue( 7, "FIRST_NAME", "Albert" );
data.setValue( 7, "LAST_NAME", "Newmann" );
data.setValue( 8, "FIRST_NAME", "John" );
data.setValue( 8, "LAST_NAME", "Goode" );
data.setValue( 9, "FIRST_NAME", "William" );
data.setValue( 9, "LAST_NAME", "Key" );
data.setValue( 10, "FIRST_NAME", "Robert" );
data.setValue( 10, "LAST_NAME", "Peterson" );
    
data.setValue( 5, "USER_ID", 0 );
data.setValue( 5, "FIRST_NAME", "Total" );
data.setValue( 5, "PHONE", 5 );
    
data.setValue( 11, "USER_ID", 1 );
data.setValue( 11, "FIRST_NAME", "Total" );
data.setValue( 11, "PHONE", 5 );
    
data.setValue( 12, "TOTAL_ID", 0 );
data.setValue( 12, "DEPARTMENT", "Total" );
data.setValue( 12, "PHONE", 10 );

for ( int i = 0; i < rows.length - 1; i++ )
  data.setValue( i, "DEPARTMENT", i < rows.length / 2 ? "Development" : "Testing" );

// Some random phone numbers (skipping totals rows):
int p = 0;
for ( int i = 0; i < rows.length - 1; i++ )
  if ( i % 6 != 5 )
    data.setValue( i, "PHONE", "3456" + p++ );

Finally, the new rules of merging:

table.setUI( new JBroTableUI()
  .withSpan( new ModelSpan( "FAMILY_ID", "LAST_NAME" ).withColumns( "LAST_NAME" ) )
  .withSpan( new ModelSpan( "USER_ID", "FIRST_NAME" ).withColumns( "FIRST_NAME", "LAST_NAME" ) )
  .withSpan( new ModelSpan( "TOTAL_ID", "DEPARTMENT" ).withColumns( "DEPARTMENT", "FIRST_NAME", "LAST_NAME" ) )
  .withSpan( new ModelSpan( "DEPARTMENT", "DEPARTMENT" ).withColumns( "DEPARTMENT" ) ) );

• First rule was covered in span cells tutorial: it just merges people from one family.
• Second rule merges intermediate totals: cells of columns FIRST_NAME and LAST_NAME would be merged if USER_ID is not null. The caption would be equal to the value in FIRST_NAME (equal to string Total).
• Third rule merges common totals: cells of columns DEPARTMENT, FIRST_NAME and LAST_NAME would be merged if TOTAL_ID is not null. The caption would be equal to the value in DEPARTMENT (equal to string Total).
• Fourth rule merges cells with equal departments.

So, we have gained the desired result. Column rearrangement works as expected. And what's with row sorting?

Oh, gosh! Departments interchange often and totals rows are mixed. It's explainable from the rules point of view but may be weird for an end-user. The user may expect employees to be sorted among a department.

How can we solve this? By using an obligatory sorting by DEPARTMENT column. If the user clicks another column to sort then the data would be sorted by DEPARTMENT first and then by user chosen column. A class PredefinedRowSorter allows to achieve this. Or JBroPredefinedRowSorter which is specific for JBroTable (PredefinedRowSorter can be used for regular JTables) and allows to set predefined sorting by column names.

table.setRowSorter( new JBroPredefinedRowSorter( table )
  .withPreColumnsByName( new SortKey( "TOTAL_ID", SortOrder.ASCENDING ),
                         new SortKey( "DEPARTMENT", SortOrder.ASCENDING ),
                         new SortKey( "USER_ID", SortOrder.ASCENDING ) ) );

• First, sort by TOTAL_ID. It makes common totals row always be the last.
• Then by DEPARTMENT. Try to sort by DEPARTMENT by clicking on column. The order of sorting is kept if you sort then by other columns. That's what a regular RowSorter does.
• And the last one is USER_ID. It makes department totals be the last rows inside a department.

And the last thing: it's possible to make table cells look like header cells.

.withSpan( new ModelSpan( "DEPARTMENT", "DEPARTMENT" )
             .withColumns( "DEPARTMENT" )
             .withDrawAsHeader( true ) )

Just set drawAsHeader property of a span to true.

That's it!

Full source code of this tutorial.