How can I create an organisational chart from the google chart tools / virtualization api using a .NET DataTable?

Question

I would like to create an organisational chart as shown here: http://code.google.com/apis/visualization/documentation/gallery/orgchart.html

I can create the JSON google.visualization.DataTable string using the .NET Visualization helper library from here: http://code.google.com/p/bortosky-google-visualization/

Unfortunately with this helper library it is not possible to display a custom format message like President or Vice President for each organisational chart member as the f: key is not generated by the helper library.

Answer 1

I was able to solve this question by writing a method that creates the organisational chart JSON from a C# DataTable:

public string GoogleOrgChartJson(DataTable dt)
        {
            if ((dt == null) || (dt.Columns.Count == 0)) return "";
            var sb = new StringBuilder();
            sb.Append("{cols: [");
            foreach (DataColumn dc in dt.Columns.Cast<DataColumn>().Where(dc => dc.Caption != "Format"))
            {
                sb.Append("{id: '");
                sb.Append(dc.Caption);
                sb.Append("', label: '");
                sb.Append(dc.Caption);
                sb.Append("', type: '");
                sb.Append(dc.DataType.Name.ToLower());
                sb.Append("'}, ");
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append("], rows: [");
            foreach (DataRow dr in dt.Rows)
            {
                sb.Append("{c: [");
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (dt.Columns[i].ToString() == "Format")
                    {
                        sb.Remove(sb.Length - 3, 3);
                        sb.Append(", f: '");
                    }
                    else
                        sb.Append("{v: '");

                    if ((dr[i] != DBNull.Value) && (string)dr[i] != "")
                        sb.Append(dr[i] + "'}, ");
                    else
                        sb.Append("'}, ");

                }
                sb.Remove(sb.Length - 2, 2);
                sb.Append("]}, ");
            }
            sb.Remove(sb.Length - 2, 2);
            sb.Append("]};");
            return sb.ToString();
        }

As you see from the code above we need to have a DataTable with a column called Format in order to generate the f key in the JSON. Please find below an example of a C# DataTable and the call to create the JSON for the google chart API:

var dt = new DataTable();
dt.Columns.Add("Name", typeof (String)).Caption = "Name";
dt.Columns.Add("Format", typeof (String)).Caption = "Format";
dt.Columns.Add("Manager", typeof (String)).Caption = "Manager";
dt.Rows.Add("Mathias Florin","Mathias Florin<div style=color:red; font-style:italic><p style=font-size:0.7em>Technical Leader</p></div>","Christian Florin");
dt.Rows.Add("Christian Florin","Christian Florin<div style=color:red; font-style:italic><p style=font-size:0.7em>CEO</p></div>","Christian Florin");
Page.ClientScript.RegisterStartupScript(GetType(), "vis", string.Format("var fundata = {0}", GoogleOrgChartJson(dt)), true);

Additional columns can be added after the Manager column and can be accessed in JavaScript in the select event of the google chart api.