Creating Data table from tabular report using Groovy
I've been lumbered with the job of converting a list report into tabular data, and for various reasons the only programmatic tool I 开发者_C百科have is groovy (i.e. no shell commands, no C, no perl). The data comes pre-summarised, in five columns,
Grouping (e.g. company), Sub-grouping (e.g. Quarter), sub total, sub-group total, grand total (with the summary data tacked onto the first row of each group) :
Big Co.\t2009 Q4\t29\t88\t308
\t2010 Q1\t38\t\t
\t2010 Q4\t21\t\t
Gargantua Inc.\t2009 Q4\t33\t139
\t2010 Q1\t31\t\t
\t2010 Q2\t36\t\t
\t2010 Q3\t39\t\t
Mediocre Ltd.\t2009 Q4\t39\t81
\t2010 Q4\t42\t\t
And the output needs to be something like:
<table>
<tr><th>Group</th><th>2009 Q4</th><th>2010 Q1</th><th>2010 Q2</th><th>2010 Q3</th><th>2010 Q4</th><th>Subtotal</th></tr>
<tr><th>Big Co</th><td>29</td><td>38</td><td> </td><td> </td><td>38</td><th>308</th></tr>
<tr><th>Gargantua Inc</th><td>29</td><td>31</td><td>36</td><td>39</td><td> </td><th>139</th></tr>
<tr><th>Gargantua Inc</th><td>39</td><td> </td><td> </td><td> </td><td>42</td><th>81</th></tr>
<tr><th> </th><td> </td><td> </td><td> </td><td> </td><td> </td><th>308</th></tr>
</table>
I'd like to be able run some loops over the data; stick things into an associative array of associative arrays, building a complete key list as I go; then loop again to produce the output. I've taken a look at groovy, and the Map syntax has my completely thrown.
Can anyone point me to some clearer examples of groovy Map iterations, so that I can at least get a toe in the water with this?
Any help gratefully received.
(In case anyone thinks this is just a fishing trip, I've kludged together a PHP version that does the trick - essentially I need to be able to translate this into groovy... )
<?php
$rows = explode("\n", $data);
$group = "";
$subgroup = "";
$table = array();
$totals = array();
$subgroup_names = array();
$group_names = array();
foreach($rows as $key => $row) {
$rowData = explode("\t", $row);
if($rowData[0]) {
$group = $rowData[0];
$group_names[$rowData[0]] = true;
}
if($rowData[1]) {
$subgroup = $rowData[1];
$subgroup_names[$rowData[1]] = true;
}
$table[$group][$subgroup]['jobcount'] = $rowData[2];
if($rowData[3]) $totals[$group]['jobcount'] = $rowData[3];
if($rowData[4]) $totals['grandtotal'] = $rowData[4];
}
$group_names = array_keys($group_names);
asort($group_names);
$subgroup_names = array_keys($subgroup_names);
asort($subgroup_names);
$result = array();
$result['header'] = "<th> </th>";
foreach ($subgroup_names as $subgroup) {
$result['header'] .= "<th>$subgroup</th>";
}
$result['header'] .= "<th>Subtotals</th>";
foreach ($group_names as $group) {
$result[$group] = "<th>$group</th>";
foreach ($subgroup_names as $subgroup) {
$value = isset($table[$group][$subgroup]['jobcount'])?
$table[$group][$subgroup]['jobcount']:
' ';
$result[$group] .= "<td>".$value."</td>";
}
$result[$group] .= "<th>".$totals[$group]['jobcount']."</th>";
}
$result['footer'] = "";
foreach ($subgroup_names as $subgroup) {
$result['footer'] .= "<th> </th>";
}
$result['footer'] .= "<th>Total</th>";
$result['footer'] .= "<th>".$totals['grandtotal']."</th>";
echo "<table><tr>".join($result, "</tr>\n<tr>")."</tr></table>";
?>
Ok, got this together... It's maybe not the prettiest -- but then neither is the input format ;-)
String txt = '''Big Co.\t2009 Q4\t29\t88\t308
\t2010 Q1\t38\t\t
\t2010 Q4\t21\t\t
Gargantua Inc.\t2009 Q4\t33\t139
\t2010 Q1\t31\t\t
\t2010 Q2\t36\t\t
\t2010 Q3\t39\t\t
Mediocre Ltd.\t2009 Q4\t39\t81
\t2010 Q4\t42\t\t'''
// Parse the text
def summary = txt.split( '\n' ).inject( [] ) { list, row ->
def split = row.split( '\t' ).collect { it.trim() }
while( split.size() < 5 ) split << ''
if( split[ 0 ] ) {
list << [ group:split[ 0 ], grouptotal:split[ 3 ], grandtotal:split[ 4 ], subtotals:[:] ]
}
list[ -1 ].subtotals << [ (split[ 1 ]):split[ 2 ] ]
list
}
// Get a sorted set of all available quarters
def allQuarters = summary.subtotals*.keySet().flatten() as TreeSet
// Then build our html output
def writer = new StringWriter()
def builder = new groovy.xml.MarkupBuilder( writer )
builder.table() {
// Header row
tr {
th "group"
allQuarters.each { q ->
th q
}
th "summary"
}
// Row for each group
summary.each { group ->
tr {
td group.group
allQuarters.each { q ->
def v = group.subtotals."$q"
if( v )
td v
else
td { mkp.yieldUnescaped ' ' }
}
td group.grouptotal
}
}
// Footer row
tr {
td { mkp.yieldUnescaped ' ' }
allQuarters.each { q ->
td { mkp.yieldUnescaped ' ' }
}
td summary.grandtotal.find { it } // Get the first non-empty grandtotal
}
}
println writer.toString()
精彩评论