Click here to Skip to main content
15,938,507 members
Articles / Database Development / SQL Server / SQL Server 2008R2
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.80/5 (18 votes)
17 Oct 2014CPOL2 min read 42.8K   18   25
Convert CellSet to HTML table

Introduction

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

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 only CellSet. So I have tried a lot to convert the CellSet to the 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 codes as per our needs 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.

C#
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 as follows:

JavaScript
$('#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:

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:

JavaScript
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. :)

JavaScript
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: http://www.codeproject.com/Tips/828806/Working-With-JQX-Grid-With-Filtering-And-Sorting).

JavaScript
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

History

  • First version: 17-Oct-2014

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
GeneralIt nice and Useful Pin
Shafeeque Punnakkadan7-Nov-14 18:26
Shafeeque Punnakkadan7-Nov-14 18:26 
GeneralRe: It nice and Useful Pin
Sibeesh KV7-Nov-14 19:40
professionalSibeesh KV7-Nov-14 19:40 
AnswerThe fastest way is using XSL with C# Pin
manoloarbulu31-Oct-14 8:23
manoloarbulu31-Oct-14 8:23 
GeneralRe: The fastest way is using XSL with C# Pin
Sibeesh KV2-Nov-14 18:21
professionalSibeesh KV2-Nov-14 18:21 
QuestionSuper work Pin
Member 938105329-Oct-14 6:42
Member 938105329-Oct-14 6:42 
AnswerRe: Super work Pin
Sibeesh KV29-Oct-14 6:44
professionalSibeesh KV29-Oct-14 6:44 
QuestionMy vote of 5 Pin
Member 1117423926-Oct-14 23:37
Member 1117423926-Oct-14 23:37 
AnswerRe: My vote of 5 Pin
Sibeesh KV26-Oct-14 23:40
professionalSibeesh KV26-Oct-14 23:40 
QuestionGreat Job Pin
Member 938105324-Oct-14 6:34
Member 938105324-Oct-14 6:34 
AnswerRe: Great Job Pin
Sibeesh KV24-Oct-14 6:36
professionalSibeesh KV24-Oct-14 6:36 
Questionfine coding...! Pin
manivinof523-Oct-14 21:35
manivinof523-Oct-14 21:35 
AnswerRe: fine coding...! Pin
Sibeesh KV23-Oct-14 22:13
professionalSibeesh KV23-Oct-14 22:13 
QuestionWould you like to... Pin
Nelek22-Oct-14 23:26
protectorNelek22-Oct-14 23:26 
AnswerRe: Would you like to... Pin
Sibeesh KV22-Oct-14 23:33
professionalSibeesh KV22-Oct-14 23:33 
GeneralRe: Would you like to... Pin
Nelek24-Oct-14 0:50
protectorNelek24-Oct-14 0:50 
GeneralRe: Would you like to... Pin
Sibeesh KV24-Oct-14 0:52
professionalSibeesh KV24-Oct-14 0:52 
Questioncomment Pin
Annamalai0922-Oct-14 20:51
Annamalai0922-Oct-14 20:51 
AnswerRe: comment Pin
Sibeesh KV22-Oct-14 20:53
professionalSibeesh KV22-Oct-14 20:53 
GeneralGood Job Pin
Shanto Thomas22-Oct-14 3:33
Shanto Thomas22-Oct-14 3:33 
GeneralRe: Good Job Pin
Sibeesh KV22-Oct-14 3:43
professionalSibeesh KV22-Oct-14 3:43 
Questioncode....... Pin
Asaru vellat18-Oct-14 3:12
Asaru vellat18-Oct-14 3:12 
AnswerRe: code....... Pin
Sibeesh KV18-Oct-14 3:15
professionalSibeesh KV18-Oct-14 3:15 
AnswerRe: code....... Pin
Divyam Sharma19-Oct-14 18:49
professionalDivyam Sharma19-Oct-14 18:49 
GeneralRe: code....... Pin
Sibeesh KV19-Oct-14 19:05
professionalSibeesh KV19-Oct-14 19:05 
AnswerRe: code....... Pin
Sibeesh KV19-Oct-14 19:07
professionalSibeesh KV19-Oct-14 19:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.