Prepare Data"

From Documentation
 
(11 intermediate revisions by 4 users not shown)
Line 7: Line 7:
 
==Construct a PivotModel==
 
==Construct a PivotModel==
  
TabularPivotModel is a standard implementation of PivotModel. It's constructor takes two <tt>List</tt>, for raw data and column labels.
+
<javadoc directory="zkpvt">org.zkoss.pivot.impl.TabularPivotModel</javadoc> is a standard implementation of <javadoc directory="zkpvt">org.zkoss.pivot.PivotModel</javadoc>. It's constructor takes an Iterable and a String List, for raw data and column labels respectively.
  
 
<source lang="java">
 
<source lang="java">
public TabularPivotModel(List<? extends List<?>> data, List<String> columns) { ... }
+
public TabularPivotModel(Iterable<? extends List<?>>  
 +
data, List<String> columns) { ... }
 
</source>
 
</source>
  
Line 21: Line 22:
 
return new TabularPivotModel(getData(), getColumns());
 
return new TabularPivotModel(getData(), getColumns());
 
}
 
}
+
private static final long TODAY = new Date().getTime();
 +
private static final long DAY = 1000 * 60 * 60 * 24;
 +
private static Date dt(int i){
 +
return new Date(TODAY + i * DAY);
 +
}
 
// raw data
 
// raw data
 
public static List<List<Object>> getData() {
 
public static List<List<Object>> getData() {
 
Object[][] objs = new Object[][] {
 
Object[][] objs = new Object[][] {
{ "Carlene Valone", "Tameka Meserve",    "ATB Air", "AT15",  "Berlin",    "Paris",    186.6, 545  },
+
{ "Carlene Valone", "Tameka Meserve",    "ATB Air", "AT15",  dt(-7), "Berlin",    "Paris",    186.6, 545  },
{ "Antonio Mattos", "Sharon Roundy",    "Jasper",  "JS1",  "Frankfurt",  "Berlin",    139.5, 262  },
+
{ "Antonio Mattos", "Sharon Roundy",    "Jasper",  "JS1",  dt(-5), "Frankfurt",  "Berlin",    139.5, 262  },
{ "Russell Testa",  "Carl Whitmore",    "Epsilon", "EP2",  "Dublin",    "London",    108.0, 287  },
+
{ "Russell Testa",  "Carl Whitmore",    "Epsilon", "EP2",  dt(-3), "Dublin",    "London",    108.0, 287  },
{ "Antonio Mattos", "Velma Sutherland",  "Epsilon", "EP5",  "Berlin",    "London",    133.5, 578  },
+
{ "Antonio Mattos", "Velma Sutherland",  "Epsilon", "EP5",  dt(-1), "Berlin",    "London",    133.5, 578  },
{ "Carlene Valone", "Cora Million",      "Jasper",  "JS30",  "Paris",      "Frankfurt", 175.4, 297  },
+
{ "Carlene Valone", "Cora Million",      "Jasper",  "JS30",  dt(-4), "Paris",      "Frankfurt", 175.4, 297  },
{ "Richard Hung",  "Candace Marek",    "DTB Air", "BK201", "Manchester", "Paris",    168.5, 376  },
+
{ "Richard Hung",  "Candace Marek",    "DTB Air", "BK201", dt(-5), "Manchester", "Paris",    168.5, 376  },
{ "Antonio Mattos", "Albert Briseno",    "Fujito",  "FJ1",  "Berlin",    "Osaka",    886.9, 5486 },
+
{ "Antonio Mattos", "Albert Briseno",    "Fujito",  "FJ1",  dt(-7), "Berlin",    "Osaka",    886.9, 5486 },
{ "Russell Testa",  "Louise Knutson",    "HST Air", "HT6",  "Prague",    "London",    240.6, 643  },
+
{ "Russell Testa",  "Louise Knutson",    "HST Air", "HT6",  dt(-2), "Prague",    "London",    240.6, 643  },
{ "Antonio Mattos", "Jessica Lunsford",  "Jasper",  "JS9",  "Munich",    "Lisbon",    431.6, 1222 },
+
{ "Antonio Mattos", "Jessica Lunsford",  "Jasper",  "JS9",  dt(-4), "Munich",    "Lisbon",    431.6, 1222 },
 
// more rows...
 
// more rows...
{ "Russell Testa",  "Velma Sutherland",  "Epsilon", "EP4",  "London",    "Berlin",    155.7, 578  }
+
{ "Russell Testa",  "Velma Sutherland",  "Epsilon", "EP4",  dt(-7), "London",    "Berlin",    155.7, 578  }
 
};
 
};
 
 
Line 47: Line 52:
 
public static List<String> getColumns() {
 
public static List<String> getColumns() {
 
return Arrays.asList(new String[]{
 
return Arrays.asList(new String[]{
"Agent", "Customer", "Airline", "Flight", "Origin", "Destination", "Price", "Mileage"
+
"Agent", "Customer", "Airline", "Flight", "Date", "Origin", "Destination", "Price", "Mileage"
 
});
 
});
 
}
 
}
Line 58: Line 63:
 
-->
 
-->
  
==Determine fields on rows and columns==
+
===Determine fields on rows and columns===
 
In addition to providing data, you also need to specify how you want to categorize it. For example, given the previously constructed TabularPivotModel
 
In addition to providing data, you also need to specify how you want to categorize it. For example, given the previously constructed TabularPivotModel
  
 
<source lang="java">
 
<source lang="java">
 
// what to show on column headers (how you categorize the x-axis)
 
// what to show on column headers (how you categorize the x-axis)
model.addColumnField("Airline");
+
model.setFieldType("Airline", PivotField.Type.COLUMN);
  
 
// what to show on row headers (how you categorize the y-axis)
 
// what to show on row headers (how you categorize the y-axis)
model.addRowField("Agent");
+
model.setFieldType("Agent", PivotField.Type.ROW);
  
 
// which field to show in data cell
 
// which field to show in data cell
model.addDataField("Price");
+
model.setFieldType("Price", PivotField.Type.DATA);
 
</source>
 
</source>
 +
 +
Here we don't set a Calculator, the [https://www.zkoss.org/javadoc/latest/zkpvt/org/zkoss/pivot/impl/TabularPivotField.html#DEFAULT_CALCULATOR default calculator "sum or count"] will be used.
  
 
This will result in a Pivottable that looks like
 
This will result in a Pivottable that looks like
  
[[Image: ZKPivotEsn_work_pivot_01.png]]
+
[[Image: ZKPivotEsn_work_pivot_01.png | center | 900px]]
  
 
Of course, to utilize the power of Pivottable, you can specify multiple fields as column, row, and data fields.
 
Of course, to utilize the power of Pivottable, you can specify multiple fields as column, row, and data fields.
Line 80: Line 87:
 
<source lang="java">
 
<source lang="java">
 
// columns are categorized by Airline, then Flight
 
// columns are categorized by Airline, then Flight
model.addColumnField("Airline");
+
model.setFieldType("Airline", PivotField.Type.COLUMN);
model.addColumnField("Flight");
+
model.setFieldType("Flight", PivotField.Type.COLUMN);
  
 
// rows are categorized by Agent, then Customer
 
// rows are categorized by Agent, then Customer
model.addRowField("Agent");
+
model.setFieldType("Agent", PivotField.Type.ROW);
model.addRowField("Customer");
+
model.setFieldType("Customer", PivotField.Type.ROW);
  
 
// show sum of Price and Mileage in each cell, if any
 
// show sum of Price and Mileage in each cell, if any
model.addDataField("Price");
+
model.setFieldType("Price", PivotField.Type.DATA);
model.addDataField("Mileage");
+
model.setFieldType("Mileage", PivotField.Type.DATA);
 
</source>
 
</source>
  
 
Now the Pivottable shall look like
 
Now the Pivottable shall look like
  
[[Image: ZKPivotEsn_work_pivot_11.png]]
+
[[Image: ZKPivotEsn_work_pivot_11.png| center | 900px]]
  
 
Note:
 
Note:
Line 105: Line 112:
  
  
nbsp;
+
&nbsp;
==Summary and Subtotals==
+
 
 +
===Summary and Subtotals===
 
After adding fields on a PivotModel, you can change the summary type for the data, or set subtotals to rows or columns.
 
After adding fields on a PivotModel, you can change the summary type for the data, or set subtotals to rows or columns.
  
Line 113: Line 121:
 
// assume "Price" and "Mileage" were added as data fields
 
// assume "Price" and "Mileage" were added as data fields
 
TabularPivotField field = model.getField("Price");
 
TabularPivotField field = model.getField("Price");
field.setSummary(Calculators.AVERAGE);  
+
model.setFieldSummary(field, StandardCalculator.AVERAGE);
 
// now the values in data cells are averages of Prices, instead of sums
 
// now the values in data cells are averages of Prices, instead of sums
 
// Mileage values are still sum, not average
 
// Mileage values are still sum, not average
Line 133: Line 141:
 
// assume "Agent", "Customer" are column fields
 
// assume "Agent", "Customer" are column fields
 
TabularPivotField field = model.getField("Agent");
 
TabularPivotField field = model.getField("Agent");
field.setSubtotals(new Calculator[]{
+
model.setFieldSubtotals(field, new Calculator[]{
Calculators.AVERAGE, Calculators.COUNT
+
StandardCalculator.AVERAGE, StandardCalculator.COUNT
 
});
 
});
 
// now when you open a header node of Agent, there will be two subtotal columns
 
// now when you open a header node of Agent, there will be two subtotal columns
Line 150: Line 158:
 
&nbsp;
 
&nbsp;
  
==Grouping==
+
===Header Grouping===
 
On row and column, we categorize data by fields in the given order, but sometimes you may want to group by a coarser fashion. For example, you may have a field of dates, but you want to categorize the data just by month.
 
On row and column, we categorize data by fields in the given order, but sometimes you may want to group by a coarser fashion. For example, you may have a field of dates, but you want to categorize the data just by month.
  
Line 171: Line 179:
  
 
&nbsp;
 
&nbsp;
 +
===Header Sorting===
 +
You can provide a custom comparator on each field to sort the nodes on the corresponding level of header tree. For example:
 +
 +
<source lang="java">
 +
// for Customer field, compare their last names, and then their first names
 +
model.setFieldKeyComparator("Customer", new Comparator<Object>() {
 +
public int compare(Object k1, Object k2) {
 +
String s1 = (String) k1;
 +
String s2 = (String) k2;
 +
int i1 = s1.lastIndexOf(' ');
 +
int i2 = s2.lastIndexOf(' ');
 +
int cmp = s1.substring(i1 + 1).compareTo(s2.substring(i2 + 1));
 +
if (cmp != 0)
 +
return cmp;
 +
String fname1 = i1 < 0 ? "" : s1.substring(0, i1).trim();
 +
String fname2 = i2 < 0 ? "" : s2.substring(0, i2).trim();
 +
return fname1.compareTo(fname2);
 +
}
 +
});
 +
</source>
 +
 +
<!-- TODO: img -->
 +
 +
You can set the comparator on any type of fields, including data fields and usused fields, but the comparator is only in effect on row and column fields.
 +
 +
The setFieldKeyOrder method is a shortcut of the method above, which is equivalent to setting the comparator to one of the defaults.
 +
 +
<source lang="java">
 +
// sets the comparator to the default one with descending natural order
 +
model.setFieldKeyOrder("Date", false);
 +
// sets the comparator to the default one with ascending natural order
 +
// this is the default setting
 +
model.setFieldKeyOrder("Date", true);
 +
</source>
 +
 +
 +
&nbsp;
 +
 
==Use data from database==
 
==Use data from database==
 
To use data from database, simply prepare the List by an SQL query.
 
To use data from database, simply prepare the List by an SQL query.
Line 176: Line 222:
 
<source lang="java">
 
<source lang="java">
 
// you can get webapp from Desktop
 
// you can get webapp from Desktop
public List<List<Object>> getData(WebApp webapp, final String[] columns) throws SQLException {
+
public List<List<Object>> getData(WebApp webapp, final String[] columns) throws SQLException {  
final boolean selectStar = columns == null || columns.length == 0;
+
    final boolean selectStar = columns == null || columns.length == 0;
final List<List<Object>> list = new ArrayList<List<Object>>();
+
    final List<List<Object>> list = new ArrayList<List<Object>>();
Connection conn = null;
+
    Connection conn = null;
try {
+
    try {
// use your own database name, table name, etc
+
        // use your own database name, table name, etc
conn = DriverManager.getConnection("jdbc:h2:" + webapp.getRealPath("/WEB-INF/h2db/mydb"), "myname", "mypwd");
+
        conn = DriverManager.getConnection("jdbc:h2:" + webapp.getRealPath("/WEB-INF/h2db/mydb"), "myname", "mypwd");
Statement stmt = conn.createStatement();
+
        Statement stmt = conn.createStatement();
String query = "SELECT " + (selectStar ? "*" : join(columns)) + " FROM mytable";
+
        String query = "SELECT " + (selectStar ? "*" : join(columns)) + " FROM mytable";
//query += " WHERE ... "; // add your own WHERE clause to filter
+
        //query += " WHERE ... "; // add your own WHERE clause to filter
ResultSet res = stmt.executeQuery(query);
+
        ResultSet res = stmt.executeQuery(query);
while (res.next()) {
+
        while (res.next()) {
List<Object> row = new ArrayList<Object>();
+
            List<Object> row = new ArrayList<Object>();
for (String col : columns)
+
            for (String col : columns)
row.add(res.getObject(col));
+
                row.add(res.getObject(col));
list.add(row);
+
            list.add(row);
}
+
        }
res.close();
+
        res.close();
+
   
} finally {
+
    } finally {
if (conn != null && !conn.isClosed())
+
        if (conn != null && !conn.isClosed())
conn.close();
+
            conn.close();
}
+
    }
 
}
 
}
  
 
// helper //
 
// helper //
 
private static String join(String ... strs) {
 
private static String join(String ... strs) {
if (strs.length == 0)
+
    if (strs.length == 0)
return "";
+
        return "";
StringBuilder sb = new StringBuilder(strs[0]);
+
    StringBuilder sb = new StringBuilder(strs[0]);
for (int i = 1; i < strs.length; i++)
+
    for (int i = 1; i < strs.length; i++)
sb.append(", ").append(strs[i]);
+
        sb.append(", ").append(strs[i]);
return sb.toString();
+
    return sb.toString();
 
}
 
}
  
Line 215: Line 261:
  
 
&nbsp;
 
&nbsp;
 +
 +
==Filtering input data==
 +
To filter the input data of a TabularPivotModel, you can either prepare it by yourself (in the case of data from database), or use the utility class PivotModels to help:
 +
 +
<source lang="java">
 +
Iterable<List<Object>> data = ... // your input data
 +
Iterable<List<Object>> filteredData = PivotModels.filter
 +
(data, new Filter<List<Object>>() {
 +
public boolean keep(List<Object> row) {
 +
return row.get(0) != null; // filter out rows with null value on first column.
 +
}
 +
});
 +
</source>
 +
 +
 +
&nbsp;
 +
 
==Version History==
 
==Version History==
 
{{LastUpdated}}
 
{{LastUpdated}}

Latest revision as of 10:18, 17 July 2017


Pivottable takes a model for its data population. Analogous to ListModel for Grid component, the model interface we use for Pivottable is PivotModel.

However, unlike Grid or Listbox, due to the logic of pivot table, we cannot specify data as children components of a Pivottable, so you always need to prepare a PivotModel prior to using Pivottable.

Construct a PivotModel

TabularPivotModel is a standard implementation of PivotModel. It's constructor takes an Iterable and a String List, for raw data and column labels respectively.

	public TabularPivotModel(Iterable<? extends List<?>> 
		data, List<String> columns) { ... }


Here is a simple example of constructing a TabularPivotModel.

	public static TabularPivotModel getModel() {
		return new TabularPivotModel(getData(), getColumns());
	}
	private static final long TODAY = new Date().getTime();
	private static final long DAY = 1000 * 60 * 60 * 24;
	private static Date dt(int i){
		return new Date(TODAY + i * DAY);
	}
	// raw data
	public static List<List<Object>> getData() {
		Object[][] objs = new Object[][] {
				{ "Carlene Valone", "Tameka Meserve",    "ATB Air", "AT15",  dt(-7), "Berlin",     "Paris",     186.6, 545  },
				{ "Antonio Mattos", "Sharon Roundy",     "Jasper",  "JS1",   dt(-5), "Frankfurt",  "Berlin",    139.5, 262  },
				{ "Russell Testa",  "Carl Whitmore",     "Epsilon", "EP2",   dt(-3), "Dublin",     "London",    108.0, 287  },
				{ "Antonio Mattos", "Velma Sutherland",  "Epsilon", "EP5",   dt(-1), "Berlin",     "London",    133.5, 578  },
				{ "Carlene Valone", "Cora Million",      "Jasper",  "JS30",  dt(-4), "Paris",      "Frankfurt", 175.4, 297  },
				{ "Richard Hung",   "Candace Marek",     "DTB Air", "BK201", dt(-5), "Manchester", "Paris",     168.5, 376  },
				{ "Antonio Mattos", "Albert Briseno",    "Fujito",  "FJ1",   dt(-7), "Berlin",     "Osaka",     886.9, 5486 },
				{ "Russell Testa",  "Louise Knutson",    "HST Air", "HT6",   dt(-2), "Prague",     "London",    240.6, 643  },
				{ "Antonio Mattos", "Jessica Lunsford",  "Jasper",  "JS9",   dt(-4), "Munich",     "Lisbon",    431.6, 1222 },
				// more rows...
				{ "Russell Testa",  "Velma Sutherland",  "Epsilon", "EP4",   dt(-7), "London",     "Berlin",    155.7, 578  }
		};
		
		List<List<Object>> list = new ArrayList<List<Object>>();
		for(Object[] a : objs)
			list.add(Arrays.asList(a));
		return list;
	}
	
	// column labels
	public static List<String> getColumns() {
		return Arrays.asList(new String[]{
				"Agent", "Customer", "Airline", "Flight", "Date", "Origin", "Destination", "Price", "Mileage"
		});
	}


Determine fields on rows and columns

In addition to providing data, you also need to specify how you want to categorize it. For example, given the previously constructed TabularPivotModel

// what to show on column headers (how you categorize the x-axis)
model.setFieldType("Airline", PivotField.Type.COLUMN);

// what to show on row headers (how you categorize the y-axis)
model.setFieldType("Agent", PivotField.Type.ROW);

// which field to show in data cell
model.setFieldType("Price", PivotField.Type.DATA);

Here we don't set a Calculator, the default calculator "sum or count" will be used.

This will result in a Pivottable that looks like

ZKPivotEsn work pivot 01.png

Of course, to utilize the power of Pivottable, you can specify multiple fields as column, row, and data fields.

// columns are categorized by Airline, then Flight
model.setFieldType("Airline", PivotField.Type.COLUMN);
model.setFieldType("Flight", PivotField.Type.COLUMN);

// rows are categorized by Agent, then Customer
model.setFieldType("Agent", PivotField.Type.ROW);
model.setFieldType("Customer", PivotField.Type.ROW);

// show sum of Price and Mileage in each cell, if any
model.setFieldType("Price", PivotField.Type.DATA);
model.setFieldType("Mileage", PivotField.Type.DATA);

Now the Pivottable shall look like

ZKPivotEsn work pivot 11.png

Note:

  • If you do not specify any fields on row or column, the model will still work (as it makes sense for the model). In this case, only grand total row/column will be shown.
  • You need at least one data field.


Hint: If you can't wait to play around with the component, you can jump to next section and come back to read further if necessary.


 

Summary and Subtotals

After adding fields on a PivotModel, you can change the summary type for the data, or set subtotals to rows or columns.

For example, to change summary type:

// assume "Price" and "Mileage" were added as data fields
TabularPivotField field = model.getField("Price");
model.setFieldSummary(field, StandardCalculator.AVERAGE);
// now the values in data cells are averages of Prices, instead of sums
// Mileage values are still sum, not average


Note:


To set subtotals on column or row, you have to specify on a column field or row field:

// assume "Agent", "Customer" are column fields
TabularPivotField field = model.getField("Agent");
model.setFieldSubtotals(field, new Calculator[]{
		StandardCalculator.AVERAGE, StandardCalculator.COUNT
});
// now when you open a header node of Agent, there will be two subtotal columns


Note:

  • By default, TabularPivotField has no subtotal on each field.
  • Setting subtotals on a field is only effective if it is a column or row field, and it is NOT the last field among column or row fields.
  • Subtotals are shown only when the corresponding node is open. When the node is closed, it's underlying data are summarized by data field's summary.
  • Setting subtotals on a field does not affect other fields. In other words, if you have A, B, C as column fields and specify a subtotal on B, then A has no subtotal.


 

Header Grouping

On row and column, we categorize data by fields in the given order, but sometimes you may want to group by a coarser fashion. For example, you may have a field of dates, but you want to categorize the data just by month.


In this case we can specify a GroupHandler to the field:

// suppose "Date" is a column field, whose value is a Java Date object
TabularPivotField field = model.getField("Date");
field.setGroupHandler(new GroupHandler(){
	public Object getGroup(Object data) {
		SimpleDateFormat format = new SimpleDateFormat("yyyy/MM");
		return format.format((Date) data);
	}
});
// now it converts the Date to a String of format "yyyy/MM" for comparison, so the data will be categorized by month


 

Header Sorting

You can provide a custom comparator on each field to sort the nodes on the corresponding level of header tree. For example:

// for Customer field, compare their last names, and then their first names
model.setFieldKeyComparator("Customer", new Comparator<Object>() {
	public int compare(Object k1, Object k2) {
		String s1 = (String) k1;
		String s2 = (String) k2;
		int i1 = s1.lastIndexOf(' ');
		int i2 = s2.lastIndexOf(' ');
		int cmp = s1.substring(i1 + 1).compareTo(s2.substring(i2 + 1));
		if (cmp != 0)
			return cmp;
		String fname1 = i1 < 0 ? "" : s1.substring(0, i1).trim();
		String fname2 = i2 < 0 ? "" : s2.substring(0, i2).trim();
		return fname1.compareTo(fname2);
	}
});


You can set the comparator on any type of fields, including data fields and usused fields, but the comparator is only in effect on row and column fields.

The setFieldKeyOrder method is a shortcut of the method above, which is equivalent to setting the comparator to one of the defaults.

// sets the comparator to the default one with descending natural order
model.setFieldKeyOrder("Date", false);
// sets the comparator to the default one with ascending natural order
// this is the default setting
model.setFieldKeyOrder("Date", true);


 

Use data from database

To use data from database, simply prepare the List by an SQL query.

// you can get webapp from Desktop
public List<List<Object>> getData(WebApp webapp, final String[] columns) throws SQLException {    
    final boolean selectStar = columns == null || columns.length == 0;
    final List<List<Object>> list = new ArrayList<List<Object>>();
    Connection conn = null;
    try {
        // use your own database name, table name, etc
        conn = DriverManager.getConnection("jdbc:h2:" + webapp.getRealPath("/WEB-INF/h2db/mydb"), "myname", "mypwd");
        Statement stmt = conn.createStatement();
        String query = "SELECT " + (selectStar ? "*" : join(columns)) + " FROM mytable";
        //query += " WHERE ... "; // add your own WHERE clause to filter
        ResultSet res = stmt.executeQuery(query);
        while (res.next()) {
            List<Object> row = new ArrayList<Object>();
            for (String col : columns)
                row.add(res.getObject(col));
            list.add(row);
        }
        res.close();
    
    } finally {
        if (conn != null && !conn.isClosed())
            conn.close();
    }
}

// helper //
private static String join(String ... strs) {
    if (strs.length == 0)
        return "";
    StringBuilder sb = new StringBuilder(strs[0]);
    for (int i = 1; i < strs.length; i++)
        sb.append(", ").append(strs[i]);
    return sb.toString();
}


 

Filtering input data

To filter the input data of a TabularPivotModel, you can either prepare it by yourself (in the case of data from database), or use the utility class PivotModels to help:

Iterable<List<Object>> data = ... // your input data
Iterable<List<Object>> filteredData = PivotModels.filter
(data, new Filter<List<Object>>() {
	public boolean keep(List<Object> row) {
		return row.get(0) != null; // filter out rows with null value on first column.
	}
});


 

Version History

Last Update : 2017/07/17


Version Date Content
     



Last Update : 2017/07/17

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.