Customizing tables returned from Google.DataTable.Net.Wrapper with Format

Question

I am making a custom report using Google Visualization API. It will have 6 sections with each section having tables on either side and a chart in the middle.

Since the formats differ slightly I was spending a lot of time defining classes for each one-off case.

I decided to try Google.DataTable.Net.Wrapper 3.1.0.0.

I created a stored procedure that returns a DataSet and then walk through the DataSet in my Controller and pass each table that I need.

The Data looks something like this

rownum      charttypeid charttypename
----------- ----------- ------------------
1           1           Membership Sales

rownum      chartareaid chartareaname
----------- ----------- -------------------------
1           1           Membership Sales Overview
2           2           Membership Sales Chart

title                     value       display
------------------------- ----------- ----------
# of Walk-ins             25          25
# of Tours                17          17
# of New Members          35          35
Tour Conversion           78          78%
Percent to Goal           87          87%

Month value       display    goalvalue   goaldisplay
----- ----------- ---------- ----------- -----------
Sep   3125        $3,125.00  1500        $1,500.00
Oct   4500        $4,500.00  1500        $1,500.00

Sometimes the charts will have money formats or other display formats, sometimes dates etc. I can't figure out how to add/modify the "f" part of the cell which provides a string format for display. My Controller code looks like this

[ResponseType(typeof(List<ChartPanel>))]
public IHttpActionResult GetChart(int gym, string dateCategory, string iso8601date, int id = -1)
{            
    if (!String.IsNullOrWhiteSpace(dateCategory))
    {
        dateCategory = dateCategory.ToLower();
        string strConnString = ConfigurationManager.ConnectionStrings["PrimaryDBConnection"].ConnectionString;
        // return DataSet From USP
        DataSet dashBoardDataSet = GetDataSQL(strConnString, gym, dateCategory, iso8601date, 0);

        if (dashBoardDataSet != null)
        {
            int chartPanelCount = dashBoardDataSet.Tables[0].Rows.Count;
            List<ChartPanel> chartTypeList = new List<ChartPanel>(); // list for all the panels
            // first table describes the Chart Panels
            int tableCount = 0;

            for (int chartPanelLoop = 0; chartPanelLoop < chartPanelCount; chartPanelLoop++)
            { // for every panel
                tableCount++;
                ChartPanel chartPanel = new ChartPanel();
                chartPanel.name = dashBoardDataSet.Tables[0].Rows[chartPanelLoop][2].ToString();
                // second table describes the following chart areas for the panel
                int panelAreaCount = dashBoardDataSet.Tables[1].Rows.Count; 
                List<ChartArea> chartAreaList = new List<ChartArea>();
                int areaTableCount = tableCount;
                for (int panelAreaLoop = 0; panelAreaLoop < panelAreaCount; panelAreaLoop++) 
                { // for every area 
                    int areaTable = areaTableCount; 
                    ChartArea chartArea = new ChartArea();
                    chartArea.name = dashBoardDataSet.Tables[areaTable].Rows[panelAreaLoop][2].ToString();
                    int chartAreaRowNum = panelAreaLoop + 1; 
                    System.Data.DataTable systDT = new System.Data.DataTable();
                    systDT = dashBoardDataSet.Tables[areaTable + chartAreaRowNum];
                    var dt = systDT.ToGoogleDataTable(); //convert with wrapper
  //issue ==>       //dt = RemoveColumnsWithTitleLikeDisplayAndPassCellContentsAsFormattedStringToPreviousCell(dt);
                    chartArea.table = JsonConvert.DeserializeObject(dt.GetJson());
                    chartAreaList.Add(chartArea);
                    //}
                    if (chartAreaList.Count() > 0) chartPanel.areas = chartAreaList; 
                    tableCount++;
                }                            
                if (chartPanel.areas != null && chartPanel.areas.Count() > 0) chartTypeList.Add(chartPanel);
            }
            return Ok(chartTypeList);
        }
        else { return NotFound(); }
    }
    else { return NotFound(); }
}

Is there a better way to do this?

score 1 · Accepted Answer · answered Nov 07 '14 at 20:19

Figured it out. Here is my working code with a hack to look for any column where (colName.Contains("_display")) and make it be the formatted ("f") data for the previous column.

To map the column to the formatting column I made a custom class.

Custom Class

class ColumnDisplayMap
{
    public int columnToFormat { get; set; }
    public int formatColumn { get; set; }
}

Method For Building Charts

    [ResponseType(typeof(List<ChartPanel>))]
    public IHttpActionResult GetChart(int gym, string dateCategory, string iso8601date, int id = -1)
    {            
        if (!String.IsNullOrWhiteSpace(dateCategory))
        {
            dateCategory = dateCategory.ToLower();
            string strConnString = ConfigurationManager.ConnectionStrings["PrimaryDBConnection"].ConnectionString;
            // return DataSet From USP
            DataSet dashBoardDataSet = GetDataSQL(strConnString, gym, dateCategory, iso8601date, 0);

            if (dashBoardDataSet != null)
            {
                int chartPanelCount = dashBoardDataSet.Tables[0].Rows.Count;
                List<ChartPanel> chartTypeList = new List<ChartPanel>(); // list for all the panels
                // first table describes the Chart Panels
                int tableCount = 0;

                for (int chartPanelLoop = 0; chartPanelLoop < chartPanelCount; chartPanelLoop++)
                { // for every panel
                    ChartPanel chartPanel = new ChartPanel();
                    chartPanel.name = dashBoardDataSet.Tables[0].Rows[chartPanelLoop][2].ToString();
                    // second table describes the following chart areas for the panel
                    DataRow[] areaTableRows = dashBoardDataSet.Tables[1].Select("charttype = " + (chartPanelLoop + 1).ToString());
                    int panelAreaCount = areaTableRows.Count(); 
                    List<ChartArea> chartAreaList = new List<ChartArea>();
                    for (int panelAreaLoop = 0; panelAreaLoop < panelAreaCount; panelAreaLoop++) 
                    { // for every area 
                        int areaTable = 1; 
                        ChartArea chartArea = new ChartArea();
                        chartArea.name = areaTableRows[panelAreaLoop][3].ToString(); // dashBoardDataSet.Tables[areaTable].Rows[panelAreaLoop][3].ToString();
                        DataColumnCollection columns = dashBoardDataSet.Tables[areaTable + tableCount + 1].Columns;
                        DataRowCollection rows = dashBoardDataSet.Tables[areaTable + tableCount + 1].Rows;
                        Google.DataTable.Net.Wrapper.DataTable gdt = new Google.DataTable.Net.Wrapper.DataTable();
                        List<ColumnDisplayMap> cMap = new List<ColumnDisplayMap>();
                        foreach (DataColumn col in columns)
                        {
                            string colName = col.ToString();
                            if (!colName.Contains("_display"))
                            {
                                ColumnType type = ColumnType.Number;
                                if (!col.IsNumeric()) type = ColumnType.String;
                                gdt.AddColumn(new Column(type, col.ToString(), col.ToString()));
                            }else
                            {
                                ColumnDisplayMap cdm = new ColumnDisplayMap(){columnToFormat = col.Ordinal - 1, formatColumn = col.Ordinal};
                                cMap.Add(cdm);
                            }
                        }
                        foreach (DataRow row in rows)
                        {
                            var r = gdt.NewRow();
                            for (int cellItem = 0; cellItem < row.ItemArray.Count(); cellItem++)
                            {
                                if (cMap.Any(c => c.columnToFormat.Equals(cellItem)))
                                {
                                    r.AddCell(new Cell(row.ItemArray[cellItem], row.ItemArray[cellItem + 1].ToString()));
                                }
                                else if (cMap.Any(c => c.formatColumn.Equals(cellItem)))
                                {
                                    // do nothing
                                }
                                else
                                {
                                    r.AddCell(new Cell(row.ItemArray[cellItem], row.ItemArray[cellItem].ToString()));
                                }
                            }
                            gdt.AddRow(r);
                        }

                        chartArea.table = JsonConvert.DeserializeObject(gdt.GetJson());
                        chartAreaList.Add(chartArea);
                        //}
                        if (chartAreaList.Count() > 0) chartPanel.areas = chartAreaList; 
                        tableCount++;
                    }                            
                    if (chartPanel.areas != null && chartPanel.areas.Count() > 0) chartTypeList.Add(chartPanel);
                }
                return Ok(chartTypeList);
            }
            else { return NotFound(); }
        }
        else { return NotFound(); }
    }

Customizing tables returned from Google.DataTable.Net.Wrapper with Format

1 Answers1

Custom Class

Method For Building Charts