Over a million developers have joined DZone.

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

DZone 's Guide to

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

· Java Zone ·
Free Resource

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)

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

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) thrownew Exception("No data returned for the selection"); //if axes count is not 2 if (axes_count != 2) thrownew 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)) thrownew 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); } elseif ((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:

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



  • First version: 05-May-2015

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}