JavaScript利用数据库记录构建分栏的table表头

我在morningstar的第一项工作,就是构建一个带分栏表头的grid表格,如下:

------------------------------------
|                   |                  |
------------------------------------
|         |        |         |        |
------------------------------------
|         |        |         |        |

上面的表格中,第一行是父栏,第二行是子栏,第三行开始才是数据。而在table中还要实现这样的表头,还是有点麻烦的。

为了构建这种有父子栏的表头,我想到把表格头部的数据转化为按照table tr td的排列顺序,利用td的colspan, rowspan来进行分割的原理,构建一个按行分开的数据结果,利用这个数据结果按照table的加载顺序,一个一个td进行渲染。

而我们拿到的原始数据往往是像下面这样的:

[
    {"id":"a2","pid":"","name":"Name",type:"tree",align:"left","dataType":"VARCHAR"},
    {"id":"a3","pid":"","name":"Ticker",type:"ro",align:"left","dataType":"VARCHAR"},
    {"id":"a4","pid":"","name":"Inception Date",type:"ro",align:"left","dataType":"VARCHAR"},
    {"id":"a5","pid":"","name":"Expense Ratios"},
        {"id":"b1","pid":"a5","name":"Gross expense ratio",type:"ro",align:"left","dataType":"VARCHAR"},
        {"id":"b2","pid":"a5","name":"Net expense ratio",type:"ro",align:"left","dataType":"VARCHAR"},
        {"id":"b3","pid":"a5","name":"Contractual waiver through",type:"ro",align:"left","dataType":"VARCHAR"},
    {"id":"a6","pid":"","name":"Average annual return [%]"},
        {"id":"c1","pid":"a6","name":"Lastest week as of 02/11/2011"},
            {"id":"d1","pid":"c1","name":"YTD",type:"ro",align:"left","dataType":"INT"},
            {"id":"d2","pid":"c1","name":"1-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"d3","pid":"c1","name":"3-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"d4","pid":"c1","name":"5-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"d5","pid":"c1","name":"10-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"d6","pid":"c1","name":"Since Inception",type:"ro",align:"left","dataType":"INT"},
        {"id":"c2","pid":"a6","name":"Lastest quarter as of 12/31/2010 [unadjusted]"},
            {"id":"e1","pid":"c2","name":"YTD",type:"ro",align:"left","dataType":"INT"},
            {"id":"e2","pid":"c2","name":"1-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"e3","pid":"c2","name":"3-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"e4","pid":"c2","name":"5-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"e5","pid":"c2","name":"10-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"e6","pid":"c2","name":"Since Inception",type:"ro",align:"left","dataType":"INT"},
        {"id":"c3","pid":"a6","name":"Lastest week as of 02/11/2011 [adjusted]"},
            {"id":"f1","pid":"c3","name":"YTD",type:"ro",align:"left","dataType":"INT"},
            {"id":"f2","pid":"c3","name":"1-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"f3","pid":"c3","name":"3-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"f4","pid":"c3","name":"5-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"f5","pid":"c3","name":"10-Year",type:"ro",align:"left","dataType":"INT"},
            {"id":"f6","pid":"c3","name":"Since Inception",type:"ro",align:"left","dataType":"INT"},
    {"id":"a7","pid":"","name":"Last return [%]",type:"ro",align:"left","dataType":"VARCHAR"}
]

我这里为了让每条数据之间的父子关系清晰一点,把子栏目的数据都堆到了父栏目下面。但是实际上,我们拿到的数据也应该按照这个顺序进行排列,否则你怎么确定父栏目与父栏目之间的顺序,子栏目与子栏目之间的顺序。当然,如果更加理想化,我们可以增加一个order/sort字段,用来保存排列顺序,并可以通过这个字段进行数组元素的排序计算,这样就不怕给的原始数据是随机排序的了。但是,我们本文仍然把这种情况排除,我们假设data api返回的数据就是上面这样的,已经确定好顺序了。

下面就是我进行处理的javascript代码,需要在node下运算,或者改写成可以在浏览器下运行的函数。

'use strtic';

require('jquery');

module.exports = function (origin) {
    // deep copy origin data, and origin data will not be change
    var data = $.extend([],origin);
    var origin = {};
    for(var i = 0,len = data.length;i < len;i ++) {
        var item = data[i];
        
        item.children = [];
        item.level = 1;
        origin[item.id] = item;// change data to key=>value
    }

    /*
     * build table tr
     * find item children
     * find item level
     */

    var _nodes_ = [];

    // add child to parent's children
    for(var i = 0,len = data.length;i < len;i ++) {
        var item = data[i];
        if(item.pid != "") {
            origin[item.pid].children.push(item.id);
        }
    }

    function pushEndNodeToAncestors(item) {
        var id = item.id;
        var pid = item.pid;

        if(pid == "" && item.children.length == 0) { // only one level (top) item
            return;
        }

        while(pid != "") {
            if(!_nodes_[pid]) _nodes_[pid] = [];
            if(_nodes_[pid].indexOf(id) < 0) _nodes_[pid].push(id);

            item = origin[pid];
            pid = item.pid;
        }
    }
    function pushLevelOfAncestors(item) {
        var level = 1;

        while(true) {
            if(level > item.level) { // if this item is the same parent of different children
                item.level = level;
            }

            if(level > headerRowsTotalCount) {
                headerRowsTotalCount = level;
            }

            if(item.pid == "") {
                break;
            }

            item = origin[item.pid];
            level ++;
        }
    }

    var headerRowsTotalCount = 1;
    for(var i = 0,len = data.length,index = 0;i < len;i ++) {
        var item = data[i];
        if(item.children.length == 0) { // end nodes
            item.index = index;
            index ++;
            pushEndNodeToAncestors(item);
            pushLevelOfAncestors(item); // get headerRowsTotalCount at the same time
        }
    }

    var headerRows = new Array(headerRowsTotalCount);
    for(var i = 0,len = data.length;i < len;i ++) {
        var item = data[i];
        var nodes = _nodes_[item.id];

        // kids
        item.kids = item.children ? item.children.join(',') : "";

        // (end)nodes
        item.nodes = nodes ? nodes.join(',') : "";

        // colspan
        var colspan = 1;
        if(nodes) {
            colspan = nodes.length;
        }
        item.colspan = colspan;
        
        // rowspan
        var rowspan = 1;
        if(item.pid == "") {
            rowspan = 1 + (headerRowsTotalCount - item.level); // roots rowspan
        }
        else {
            rowspan = origin[item.pid].level - item.level;
        }
        item.rowspan = rowspan;

        // push item into table tr row
        var row = headerRowsTotalCount - (item.level + item.rowspan - 1);
        if(!headerRows[row]) headerRows[row] = [];
        headerRows[row].push(item);
    }
    
    return headerRows;
}

完成上面的运算之后,实际上,我们得到的是一个3xn的数组,数组整体上有三个元素,每个元素都是一个数组,而这个数组中存放的,就是每一行tr内应该显示的td相关信息。接下来,我们把这个数据结果赋值给data,并在页面内进行table的构建:

var td = '<td colspan="{%s}" rowspan="{%s}" align="{%s}" data-id="{%s}" data-pid="{%s}" data-kids="{%s}" data-nodes="{%s}" data-index="{%s}" title="{%s}" class="header-cell"><div class="header-name">{%s}</div></td>';
var table = '';
for(var i = 0,len = data.length;i < len;i ++) {
    var rowItems = data[i];
    table += '<tr>';
    for(var c = 0,leng = rowItems.length;c < leng;c ++) {
        var item = rowItems[c];             
        table += sprintf(td,item.colspan,item.rowspan,item.align,item.id,item.pid,item.kids,item.nodes,item.index,item.name,item.name);
    }
    table += '</tr>';
}

sprintf是我自己写的一个函数,用来实现和c语言中sprintf一样的功能(只能替换{%s}),你也可以自己写过。这样,就可以在页面内构建能够分栏的tr行了,把它们插入到你需要显示的地方即可。

2016-07-26