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.

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.

  1. try    
  2. {    
  3.     System.Text.StringBuilder result = new System.Text.StringBuilder();    
  4.     //check if any axes were returned else throw error.    
  5.     int axes_count = cst.Axes.Count;    
  6.     if (axes_count == 0)    
  7.         throw new Exception("No data returned for the selection");    
  8.   
  9.     //if axes count is not 2    
  10.     if (axes_count != 2)    
  11.         throw new Exception("The sample code support only queries with two axes");    
  12.   
  13.     //if no position on either row or column throw error    
  14.     if (!(cst.Axes[0].Positions.Count > 0) && !(cst.Axes[1].Positions.Count > 0))    
  15.         throw new Exception("No data returned for the selection");    
  16.   
  17.     int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;    
  18.     cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;    
  19.   
  20.     //Number of dimensions on the column    
  21.     col_dim_count = cst.Axes[0].Positions[0].Members.Count;    
  22.   
  23.     //Number of dimensions on the row    
  24.     if (cst.Axes[1].Positions[0].Members.Count > 0)    
  25.         row_dim_count = cst.Axes[1].Positions[0].Members.Count;    
  26.   
  27.     //Total rows and columns    
  28.     row_count = cst.Axes[1].Positions.Count +     
  29.         col_dim_count;  //number of rows + rows for column headers    
  30.     col_count = cst.Axes[0].Positions.Count +     
  31.         row_dim_count;  //number of columns + columns for row headers    
  32.   
  33.     //gridPanel.ClientIDMode = System.Web.UI.ClientIDMode.AutoID;    
  34.     //////lets clear any controls under the grid panel    
  35.     //gridPanel.Controls.Clear();    
  36.   
  37.     ////Add new server side table control to gridPanel    
  38.     Table tblgrid = new Table();    
  39.     tblgrid.Attributes.Add("Id""myhtmltab");    
  40.     tblgrid.Attributes.Add("class""display");    
  41.     //We will use label control to add text to the table cell    
  42.     Label lbl;    
  43.     string previousText = "";    
  44.     int colSpan = 1;    
  45.     for (cur_row = 0; cur_row < row_count; cur_row++)    
  46.     {    
  47.         //add new row to table    
  48.         TableRow tr = new TableRow();    
  49.   
  50.         for (cur_col = 0; cur_col < col_count; cur_col++)    
  51.         {    
  52.             //create new cell and instance of label    
  53.             TableCell td = new TableCell();    
  54.             TableHeaderCell th = new TableHeaderCell();    
  55.             lbl = new Label();    
  56.   
  57.             //check if we are writing to a ROW having column header    
  58.             if (cur_row < col_dim_count)    
  59.             {    
  60.                 //check if we are writing to a cell having row header    
  61.                 if (cur_col < row_dim_count)    
  62.                 {    
  63.                     //this should be empty cell -- it's on top left of the grid.    
  64.                     //result.Append(" ,");    
  65.                     lbl.Text = " ";    
  66.                     td.CssClass = "titleAllLockedCell"//this locks     
  67.                         //the cell so it doesn't scroll upwards nor leftwards    
  68.                 }    
  69.                 else    
  70.                 {    
  71.                     //this is a column header cell -- use member caption for header    
  72.                     //result.Append(cst.Axes[0].Positions[cur_col -     
  73.                     //    row_dim_count].Members[cur_row].Caption + ",");    
  74.                     //if (cur_row < 1)    
  75.                     //{    
  76.                         lbl.Text = cst.Axes[0].Positions[cur_col - row_dim_count].Members[cur_row].Caption;    
  77.                         th.CssClass = "titleTopLockedCell"// this lockeders     
  78.                                 //the cell so it doesn't scroll upwards    
  79.   
  80.                     //}    
  81.                     if (lbl.Text == previousText)    
  82.                     {    
  83.                         colSpan++;    
  84.                     }    
  85.                     else    
  86.                     {    
  87.                         colSpan = 1;    
  88.                     }    
  89.                 }    
  90.             }    
  91.             else    
  92.             {    
  93.                 //We are here.. so we are writing a row having data (not column headers)    
  94.   
  95.                 //check if we are writing to a cell having row header    
  96.                 if (cur_col < row_dim_count)    
  97.                 {    
  98.                     //this is a row header cell -- use member caption for header    
  99.   
  100.                     lbl.Text = cst.Axes[1].Positions[cur_row -     
  101.                     col_dim_count].Members[cur_col].Caption.Replace(","" ");    
  102.                     td.CssClass = "titleLeftLockedCell"// this lockeders     
  103.                         //the cell so it doesn't scroll leftwards    
  104.   
  105.                 }    
  106.                 else    
  107.                 {    
  108.                     //this is data cell.. so we write the Formatted value of the cell.    
  109.                     lbl.Text = cst[cur_col - row_dim_count, cur_row - col_dim_count].FormattedValue;    
  110.                     //td.InnerText = cst[cur_col - row_dim_count,     
  111.                     //cur_row - col_dim_count].FormattedValue;    
  112.                     td.CssClass = "valueCell"//this  right     
  113.                             //aligns the values in the column    
  114.                 }    
  115.   
  116.                 //turn the wrapping off for row header and data cells.    
  117.                 td.Wrap = true;    
  118.             }    
  119.             if (((lbl.Text != previousText) || (lbl.Text == " "))     
  120.                 && (cur_row < col_dim_count))    
  121.             {    
  122.                 tr.TableSection = TableRowSection.TableHeader;    
  123.                 th.Text = "HEADER";    
  124.   
  125.                 th.Controls.Add(lbl);    
  126.                 tr.Cells.Add(th);    
  127.                 tblgrid.Rows.Add(tr);    
  128.             }    
  129.             else if ((lbl.Text != previousText) || (lbl.Text == " ") ||     
  130.                 (lbl.Text == null) || (lbl.Text == ""))    
  131.             {    
  132.                 td.Controls.Add(lbl);    
  133.                 tr.Cells.Add(td);    
  134.                 tblgrid.Rows.Add(tr);    
  135.             }    
  136.             else    
  137.             {    
  138.                 try    
  139.                 {    
  140.                     tr.Cells[tr.Cells.Count - 1].ColumnSpan = colSpan;    
  141.                 }    
  142.                 catch    
  143.                 {    
  144.                 }    
  145.             }    
  146.             if (cur_row < col_dim_count)    
  147.                 previousText = lbl.Text;    
  148.         }    
  149.   
  150.         //result.AppendLine();    
  151.   
  152.     }    
  153.   
  154.     using (StringWriter writer = new StringWriter())    
  155.     {    
  156.         HtmlTextWriter htw = new HtmlTextWriter(writer);    
  157.   
  158.         tblgrid.RenderControl(htw);    
  159.         return htw.InnerWriter.ToString();    
  160.     }    
  161. }    
  162. catch (Exception ex)    
  163. {    
  164.     throw ex;    
  165. } 

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:

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:

  1. var rows = $("#myhtmltab tbody tr"); //Select Rows , looping through every tr  
  2.   
  3. var columns = $("#myhtmltab thead th"); //Select columns , looping through every th 

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

  1. var data = [];  
  2. for (var i = 0; i < rows.length; i++) {  
  3.     var row = rows[i];  
  4.     var datarow = {};  
  5.     for (var j = 0; j < columns.length; j++) {  
  6.         // get column's title.  
  7.         var columnName = $.trim($(columns[j]).text());  
  8.         // select cell.  
  9.         var cell = $(row).find('td:eq(' + j + ')');  
  10.         datarow[columnName] = $.trim(cell.text());  
  11.     }  
  12.     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).

  1. var varDataFields = '[';  
  2. var varDataColumns = '[';  
  3. var typ = 'string';  
  4. var align = 'center';  
  5. var width = '200';  
  6.   
  7. var myColumns = $("#myhtmltab thead th");  
  8. for (var j = 0; j < myColumns.length; j++) {  
  9.     var column = myColumns[j];  
  10.     var col = $(column).text().trim();  
  11.     //col = col.replace('<span>', '');  
  12.     //col = col.replace('</span>', '');  
  13.     //var col = $(columns).find('th:get(' + j + ').find(' < span > ').text()');  
  14.     //if (!col == '') {  
  15.     varDataFields = varDataFields +   
  16.     ' { \"name\" : \"' + col + '\" , \"type\" : \"' + typ + '\"},';  
  17.     varDataColumns = varDataColumns +   
  18.     ' { \"text\" : \"' + col + '\" , \"dataField\" :  \"' +   
  19.     col + '\" , \"align\" :  \"' + align + '\" , \"width\" : \"' + width + '\"},';  
  20.     //}  
  21.       
  22.     if (j == myColumns.length - 1) {  
  23.         varDataFields = varDataFields.slice(0, -1);  
  24.         varDataColumns = varDataColumns.slice(0, -1)  
  25.     }  
  26. }  
  27. varDataFields = varDataFields + ']';  
  28. varDataColumns = varDataColumns + ']';  
  29. varDataFields = varDataFields.trim();  
  30. varDataColumns = varDataColumns.trim();  
  31.   
  32. var DataFields = $.parseJSON(varDataFields);  
  33. 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