Convert CellSet to HTML Table, and from HTML to Json, Array

Introduction

Since for the past few days, I am working on ADOMD and MDX, there I got a situation where I needed to convert my CellSet to HTML table and render it to the Client Side Grid. So I thought of sharing that with all of you.

This post has been selected as article of the day Monday, November 10, 2014 in http://www.asp.net/community/articles (Convert CellSet to HTML Table, and from HTML to Json, Array)

Background

If you are new to ADOMD, you can refer to the following links:

1. http://www.codeproject.com/Articles/28290/Microsoft-Analysis-Services-Displaying-a-grid
2. http://www.microsoft.com/msj/0899/mdx/mdx.aspx

Why?

As I have already said in my current project, we are using MDX cubes, so in the server side we will get onlyCellSet. So I have tried a lot to convert the CellSet to Json for this JQX grid alone (all other Grids in the project are using HTML table as data source) . But I couldn't find any good way for that. So I thought of getting the HTML table from the CellSet as in the other grid at the server side. And I knew how we can formulate the Array and Json from an HTML table. Here, I am sharing that information.

Please give your valuable suggestions for improvement.

Using the Code

We modified the code as per our requirements from the above mentioned articles, and bind it to an HtmlTextWriter. We have created a function called renderHTML() which will accept CellSet as argument. Here, I will paste the code.

try
            {
                System.Text.StringBuilder result = new System.Text.StringBuilder();
                //check if any axes were returned else throw error.
                int axes_count = cst.Axes.Count;
                if (axes_count == 0)
                    throw new Exception("No data returned for the selection");

                //if axes count is not 2
                if (axes_count != 2)
                    throw new Exception("The sample code support only queries with two axes");

                //if no position on either row or column throw error
                if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0))
                    throw new Exception("No data returned for the selection");

                int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
                cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

                //Number of dimensions on the column
                col_dim_count = cst.Axes[0].Positions[0].Members.Count;

                //Number of dimensions on the row
                if (cst.Axes[1].Positions[0].Members.Count > 0)
                    row_dim_count = cst.Axes[1].Positions[0].Members.Count;

                //Total rows and columns
                row_count = cst.Axes[1].Positions.Count + 
                    col_dim_count;  //number of rows + rows for column headers
                col_count = cst.Axes[0].Positions.Count + 
                    row_dim_count;  //number of columns + columns for row headers

                //gridPanel.ClientIDMode = System.Web.UI.ClientIDMode.AutoID;
                //////lets clear any controls under the grid panel
                //gridPanel.Controls.Clear();

                ////Add new server side table control to gridPanel
                Table tblgrid = new Table();
                tblgrid.Attributes.Add("Id", "myhtmltab");
                tblgrid.Attributes.Add("class", "display");
                //We will use label control to add text to the table cell
                Label lbl;
                string previousText = "";
                int colSpan = 1;
                for (cur_row = 0; cur_row < row_count; cur_row++)
                {
                    //add new row to table
                    TableRow tr = new TableRow();

                    for (cur_col = 0; cur_col < col_count; cur_col++)
                    {
                        //create new cell and instance of label
                        TableCell td = new TableCell();
                        TableHeaderCell th = new TableHeaderCell();
                        lbl = new Label();

                        //check if we are writing to a ROW having column header
                        if (cur_row < col_dim_count)
                        {
                            //check if we are writing to a cell having row header
                            if (cur_col < row_dim_count)
                            {
                                //this should be empty cell -- it's on top left of the grid.
                                //result.Append(" ,");
                                lbl.Text = " ";
                                td.CssClass = "titleAllLockedCell"; //this locks 
                                    //the cell so it doesn't scroll upwards nor leftwards
                            }
                            else
                            {
                                //this is a column header cell -- use member caption for header
                                //result.Append(cst.Axes[0].Positions[cur_col - 
                                //    row_dim_count].Members[cur_row].Caption + ",");
                                //if (cur_row < 1)
                                //{
                                    lbl.Text = cst.Axes[0].Positions
                    [cur_col - row_dim_count].Members[cur_row].Caption;
                                    th.CssClass = "titleTopLockedCell"; // this lockeders 
                                            //the cell so it doesn't scroll upwards

                                //}
                                if (lbl.Text == previousText)
                                {
                                    colSpan++;
                                }
                                else
                                {
                                    colSpan = 1;
                                }
                            }
                        }
                        else
                        {
                            //We are here.. so we are writing a row having data (not column headers)

                            //check if we are writing to a cell having row header
                            if (cur_col < row_dim_count)
                            {
                                //this is a row header cell -- use member caption for header

                                lbl.Text = cst.Axes[1].Positions[cur_row - 
                                col_dim_count].Members[cur_col].Caption.Replace(",", " ");
                                td.CssClass = "titleLeftLockedCell"; // this lockeders 
                                    //the cell so it doesn't scroll leftwards

                            }
                            else
                            {
                                //this is data cell.. so we write the Formatted value of the cell.
                                lbl.Text = cst[cur_col - row_dim_count, 
                    cur_row - col_dim_count].FormattedValue;
                                //td.InnerText = cst[cur_col - row_dim_count, 
                                //cur_row - col_dim_count].FormattedValue;
                                td.CssClass = "valueCell"; //this  right 
                                        //aligns the values in the column
                            }

                            //turn the wrapping off for row header and data cells.
                            td.Wrap = true;
                        }
                        if (((lbl.Text != previousText) || (lbl.Text == " ")) 
                            && (cur_row < col_dim_count))
                        {
                            tr.TableSection = TableRowSection.TableHeader;
                            th.Text = "HEADER";

                            th.Controls.Add(lbl);
                            tr.Cells.Add(th);
                            tblgrid.Rows.Add(tr);
                        }
                        else if ((lbl.Text != previousText) || (lbl.Text == " ") || 
                            (lbl.Text == null) || (lbl.Text == ""))
                        {
                            td.Controls.Add(lbl);
                            tr.Cells.Add(td);
                            tblgrid.Rows.Add(tr);
                        }
                        else
                        {
                            try
                            {
                                tr.Cells[tr.Cells.Count - 1].ColumnSpan = colSpan;
                            }
                            catch
                            {
                            }
                        }
                        if (cur_row < col_dim_count)
                            previousText = lbl.Text;
                    }

                    //result.AppendLine();
                }

                using (StringWriter writer = new StringWriter())
                {
                    HtmlTextWriter htw = new HtmlTextWriter(writer);

                    tblgrid.RenderControl(htw);
                    return htw.InnerWriter.ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

Finally, the function will return the output as an HTML table with id "myhtmltab" where all the th's tr's and td's are rendered.

Now if you want, you can convert the HTML table to Array, Json in the client side.

Now what we have to do is just add the dynamic HTML to the DOM. You can do it as follows:

$('#your element id').html(data);

Please read here for more information: http://api.jquery.com/html/.

Convert HTML to Array Dynamically in Jquery

Let's say you have an Ajax Jquery function which will return the output as I have shown in the output image.

If you are new to Jquery Ajax function, please read here:

• http://www.codeproject.com/Tips/688228/Bind-Dropdownlist-in-ASP-NET-using-jQuery-AJAX
• http://www.codeproject.com/Articles/224827/Jquery-Ajax-Calling-functions
• http://www.codeproject.com/Articles/631438/Code-Builder-for-jQuery-AJAX-Calling-Web-Services

Then in the success of the Ajax function, you can write the code like this to formulate an array.

Next is getting the columns and rows from the dynamic HTML table which you just formulated using CellSet:

var rows = $("#myhtmltab tbody tr"); //Select Rows , looping through every tr

var columns = $("#myhtmltab thead th"); //Select columns , looping through every th

Now what we need is an Array where we can populate the data.

var data = [];
for (var i = 0; i < rows.length; i++) {
    var row = rows[i];
    var datarow = {};
    for (var j = 0; j < columns.length; j++) {
        // get column's title.
        var columnName = $.trim($(columns[j]).text());
        // select cell.
        var cell = $(row).find('td:eq(' + j + ')');
        datarow[columnName] = $.trim(cell.text());
    }
    data[data.length] = datarow;
}

Now this is the time to formulate a Json from your table.

Convert Dynamic HTML to Json Dynamically in Jquery

As we discussed above, here also we are looping through the column and rows. The aim behind this is to formulate dynamic Json to assign data to my JQX Grid (You can check this out here).

var varDataFields = '[';
var varDataColumns = '[';
var typ = 'string';
var align = 'center';
var width = '200';

var myColumns = $("#myhtmltab thead th");
for (var j = 0; j < myColumns.length; j++) {
    var column = myColumns[j];
    var col = $(column).text().trim();
    //col = col.replace('<span>', '');
    //col = col.replace('</span>', '');
    //var col = $(columns).find('th:get(' + j + ').find(' < span > ').text()');
    //if (!col == '') {
    varDataFields = varDataFields + 
    ' { \"name\" : \"' + col + '\" , \"type\" : \"' + typ + '\"},';
    varDataColumns = varDataColumns + 
    ' { \"text\" : \"' + col + '\" , \"dataField\" :  \"' + 
    col + '\" , \"align\" :  \"' + align + '\" , \"width\" : \"' + width + '\"},';
    //}
    
    if (j == myColumns.length - 1) {
        varDataFields = varDataFields.slice(0, -1);
        varDataColumns = varDataColumns.slice(0, -1)
    }
}
varDataFields = varDataFields + ']';
varDataColumns = varDataColumns + ']';
varDataFields = varDataFields.trim();
varDataColumns = varDataColumns.trim();

var DataFields = $.parseJSON(varDataFields);
var DataColumns = $.parseJSON(varDataColumns);

So in DataFields, DataColumns, I will get the json in the way which I want. This I can directly bind to the JQX Grid.

Points of Interest

• ADOMD, MDX