Convert CellSet to HTML Table and From HTML to JSON and to Array

Introduction

For the past few days I have been working on ADOMD and MDX. I encountered a situation where I need to convert my Cell Set to a HTML table and render it to the client-side Grid. So I thought of sharing that with you all.

Please find the original article here : http://sibeeshpassion.com/Convert-CellSet-to-HTML-table-And-From-HTML-To-Json-To-Array.html

This article 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. Microsoft Analysis Services 2005: Displaying a grid using ADOMD.NET and MDX
   
2. Manipulate and Query OLAP Data Using ADOMD and Multidimensional Expressions
   

Why

As I have already said, in my current project we are using MDX cubes, so in the server-side we will get only a CellSet. So I have tried very much to convert the CellSet to the JSON for this JQX grid alone (all other Grids in the project use a HTML table as the 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 to formulate the Array and JSON from an HTML table. Here I am sharing that information.

Please provide your valuable suggestions for improvement.

Using the Code

We modify the code as per our needs from the preceding specified articles and bind to an HtmlTextWriter. We have created a function called renderHTML() that will accept CellSet as an argument. Here, I will show 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 the id "myhtmltab" where all the th, tr and td tags are rendered.

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

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

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

Please read here for more information: Get the HTML contents of the first element in the set of matched elements.

Convert HTML to Array Dynamically in jQuery

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

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

• Bind Dropdownlist in ASP.NET using jQuery AJAX
  
• Jquery Ajax Calling Functions
  
• 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 to get the columns and rows from the dynamic HTML table that 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 the table. :)

Convert Dynamic HTML to JSON Dynamically in jQuery

As we discussed above, here also we are looping through the column and rows. The intent behind this is to formulate a dynamic JSON to assign data to my JQX Grid (You can check this out: Working With JQX Grid With Filtering And Sorting).

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 that I want. This I can directly bind to the JQX Grid. :)

Points of Interest

• ADOMD, MDX

History

• First version: 27-Oct-2014