public void PivotTable_ES() { var pvtData = new PivotData(new[] { "District", "SubCounty", "wrd_name", "Sex", "Quarter", "HES" }, new CountAggregatorFactory()); pvtData.ProcessData(new DataTableReader(ReturnEconomic_strengthening())); var pvtTbl = new PivotTable( new[] { "Quarter", "District", "SubCounty" }, //rows new[] { "HES" }, //columns pvtData); var outputWr = new StringWriter(); var pvtHtmlWr = new PivotTableHtmlWriter(outputWr); pvtHtmlWr.SubtotalRows = true; pvtHtmlWr.AllowHtml = true; pvtHtmlWr.TotalsRowHeaderText = "Grand Total"; pvtHtmlWr.TotalsColumnHeaderText = "Sub County Totals"; pvtHtmlWr.TableClass = "table border = '1' table - bordered table-hover"; pvtHtmlWr.Write(pvtTbl); var pvtTblHtml = outputWr.ToString(); LitPivot.Text = pvtTblHtml; }
public ActionResult PivotTable(int year) { var context = new PivotContext(); var pvtData = GetDataCube(); // in this example filter is applied to in-memory data cube // for large datasets it may be applied on database level (with SQL WHERE expression) var filteredPvtData = new SliceQuery(pvtData).Where("Order Year", year).Execute(); // render pivot table HTML var pvtTbl = new PivotTable( new[] { "Country" }, // rows new[] { "Order Year", "Order Month" }, // cols filteredPvtData ); // sort by row total pvtTbl.SortRowKeys(null, 1, // lets order by measure #1 (sum of unit price) System.ComponentModel.ListSortDirection.Descending); var strHtmlWr = new StringWriter(); var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr); pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable"; pvtHtmlWr.Write(pvtTbl); context.PivotTableHtml = strHtmlWr.ToString(); // prepare data for pie chart (total sum by country) var pvtDataForChart = new SliceQuery(filteredPvtData).Dimension("Country").Measure(1).Execute(); var chartPvtTbl = new PivotTable(new[] { "Country" }, null, pvtDataForChart); // sort by row total chartPvtTbl.SortRowKeys(null, System.ComponentModel.ListSortDirection.Descending); var strJsonWr = new StringWriter(); var jsonWr = new PivotTableJsonWriter(strJsonWr); jsonWr.Write(chartPvtTbl); context.PivotTableJson = strJsonWr.ToString(); return(PartialView(context)); }
protected void ShowCode6Wide() { int labID = Convert.ToInt32(ddlLab.SelectedValue.ToString()); if (labID > 0) { //var x = lstStudy.SelectedValue; var x2 = gridStudy.GetSelectedFieldValues("studyID"); if (x2.Count == 0) //if (lstStudy.SelectedValue == "") { lblSelectOne.Text = "Select some studies."; lblSelectOne.Visible = true; } else if (chkDisplayOnPage.Checked == false && chkXLSX.Checked == false) { lblSelectOne.Text = "Select the output method."; lblSelectOne.Visible = true; } else { SQL_utils sql = new SQL_utils("backend"); string study_csv = String.Join(",", x2); List <SqlParameter> ps = new List <SqlParameter>(); ps.Add(sql.CreateParam("labID", labID.ToString(), "int")); //ps.Add(sql.CreateParam("study_csv", lstStudy.SelectedValue, "text")); ps.Add(sql.CreateParam("study_csv", study_csv, "text")); DataTable dt = sql.DataTable_from_ProcName("spCode6_person_by_Lab", ps); PivotTable pivotTable = utilPivotTable.DataTable_to_PivotTable(dt, new List <string> { "code6" }, new List <string> { "source_of_ID" }, "ID", new List <utilPivotTable.AggrFx> { utilPivotTable.AggrFx.Concat2 }); lblSelectOne.Visible = false; if (chkDisplayOnPage.Checked == true) { //Write back to page var htmlResult = new StringWriter(); var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult); pvtHtmlWr.TotalsColumn = false; pvtHtmlWr.TotalsRow = false; pvtHtmlWr.GrandTotal = false; pvtHtmlWr.Write(pivotTable); Literal xtab = new Literal(); xtab.Text = htmlResult.ToString(); panel_wide.Controls.Add(xtab); } if (chkXLSX.Checked == true) { var dataTblWr = new PivotTableDataTableWriter("Test"); //.Write(pivotTable); var tbl = dataTblWr.Write(pivotTable); string filename = "code6_IDs_wide_" + SpreadsheetGearUtils.DateTime_for_filename(); SpreadsheetGearUtils.SaveDataTableToExcel(tbl, filename, true, "xlsx"); } sql.Close(); } } }
protected void Load_Kappa(int measureID) { SQL_utils sql = new SQL_utils("data"); DataTable dt_score_vars = sql.DataTable_from_SQLstring(" select fldname, minval, maxval from def.vwFld where measureID=" + measureID.ToString() + " and relitem in (1,3) "); List <string> vars = dt_score_vars.AsEnumerable().Select(f => f.Field <string>("fldname")).ToList(); //foreach (string v in vars) foreach (DataRow var_row in dt_score_vars.Rows) { string v = var_row["fldname"].ToString(); string x = "exec spRELv2_Get_Scores_for_var '" + v + "', " + Master.Master_studyID.ToString() + ", " + measureID.ToString() + ", 'exclude missing', 3"; DataTable dt_kappa = sql.DataTable_from_SQLstring(x); var pivotData = new PivotData( new string[] { "orig_val", "rel_val", "fldname" }, new CountAggregatorFactory(), new DataTableReader(dt_kappa)); var pivotTable = new PivotTable( new[] { "orig_val" }, // row dimension(s) new[] { "rel_val" }, // column dimension(s) pivotData); //var q = new SliceQuery(pivotData) // .Dimension("orig_val") // .Dimension("rel_val") // .Measure(0); //var q2 = q.Execute(true); utilStats.Kappa_stats k = utilStats.CalculateKappa(pivotTable); var htmlResult = new StringWriter(); var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult); pvtHtmlWr.Write(pivotTable); Literal xtab = new Literal(); xtab.Text = htmlResult.ToString(); Literal kappa = new Literal(); kappa.Text += utilStats.PrintKappa_stats(k); Table tbl = new Table(); TableRow row = new TableRow(); TableCell cell1 = new TableCell(); TableCell cell2 = new TableCell(); cell1.Controls.Add(xtab); cell2.Controls.Add(kappa); row.Cells.Add(cell1); row.Cells.Add(cell2); tbl.Rows.Add(row); Label v_title = new Label(); v_title.Text = v; v_title.Font.Size = 10; v_title.Font.Bold = true; UpdatePanel_Kappa.ContentTemplateContainer.Controls.Add(v_title); UpdatePanel_Kappa.ContentTemplateContainer.Controls.Add(tbl); UpdatePanel_Kappa.Update(); UpdatePanel_Kappa.Visible = true; } sql.Close(); }
protected void CreatePivot(DataTable dt, List <string> pivot_rows, List <string> pivot_cols, List <string> stats, string cellfield, int decimal_places) { List <utilPivotTable.AggrFx> fx = new List <utilPivotTable.AggrFx>(); if (stats.Contains("N")) { fx.Add(utilPivotTable.AggrFx.N); } if (stats.Contains("Mean")) { fx.Add(utilPivotTable.AggrFx.M); } if (stats.Contains("SD")) { fx.Add(utilPivotTable.AggrFx.SD); } _pivot = utilPivotTable.DataTable_to_PivotTable(dt, pivot_rows, pivot_cols, cellfield, fx); var strHtmlWr = new StringWriter(); var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr); pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable"; pvtHtmlWr.FormatValue = (aggr, measureIdx) => { if (aggr.Count == 0) { return("0"); } // apply default number format if (decimal_places == 0) { return(String.Format("{0:#}", aggr.Value)); } else if (decimal_places == 1) { return(String.Format("{0:0.#}", aggr.Value)); } else if (decimal_places == 2) { return(String.Format("{0:0.##}", aggr.Value)); } else if (decimal_places == 3) { return(String.Format("{0:0.###}", aggr.Value)); } else if (decimal_places == 4) { return(String.Format("{0:0.####}", aggr.Value)); } else if (decimal_places == 5) { return(String.Format("{0:0.#####}", aggr.Value)); } else { return(String.Format("{0:0.##}", aggr.Value)); } }; pvtHtmlWr.Write(_pivot); _htmlContent = strHtmlWr.ToString(); _htmlContent = _htmlContent.Replace("Average of", "Mean"); _htmlContent = _htmlContent.Replace("Variance of", "SD"); _htmlContent = _htmlContent.Replace("Count", "N"); string headerRow = String.Join(" > ", pivot_rows); string headerCol = String.Join(" > ", pivot_cols); _autotitle = String.Format("<b>{0}</b> by <b>{1}</b>", headerRow, headerCol); output = String.Format("{0}{1}", _autotitle, _htmlContent); }
// string GetData(IEnumerable<Dictionary<string,object>> inputData) { // var pvtData = new PivotData( // new [] {"Tournament","Category", "Gender","Player"}, // list of all dimensions used in pivot table // new SumAggregatorFactory("Count"), // true); // pvtData.ProcessData( inputData ); // use appropriate overload for different data sources // var pvtTbl = new PivotTable( // new[] {"Tournament","Category"}, // rows // new[] {"Gender", "Player"}, // pvtData // ); // // var strWr = new StringWriter(); // var htmlPvtTblWr = new PivotTableHtmlWriter(strWr); // htmlPvtTblWr.Write(pvtTbl); // // return strWr.ToString(); // } public override IDisplayResult Display(CrosstabVisualProfile profile, IUpdateModel updater) { var context = new PivotContext(); // var json = new System.Web.Script.Serialization.JavaScriptSerializer(); // var inputData = json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson); // var inputData = JsonConvert.DeserializeObject<IEnumerable<Dictionary<string,object>>>(profile.QueryResult.ToString());//json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson); //var inputData = JsonConvert.DeserializeObject<IEnumerable<Dictionary<string,object>>>(profile.QueryResult.ToString());//json.Deserialize<IEnumerable<Dictionary<string,object>>>(inputDataJson); // var pvtData = GetData(inputData); var converted = JsonConvert.DeserializeObject(profile.QueryResult.ToString()); var inputData = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(converted); // in this example filter is applied to in-memory data cube // for large datasets it may be applied on database level (with SQL WHERE expression) // var filteredPvtData = new SliceQuery(pvtData).Where("Order Year", year).Execute(); var pvtData = new PivotData( new [] { "Tournament", "Category", "Gender", "Player" }, // list of all dimensions used in pivot table new SumAggregatorFactory("Count"), true); pvtData.ProcessData(inputData); // use appropriate overload for different data sources var pvtTbl = new PivotTable( new[] { "Tournament", "Category" }, // rows new[] { "Gender", "Player" }, pvtData ); // render pivot table HTML // var pvtTbl = new PivotTable( // new[] {"Country"}, // rows // new[] {"Order Year", "Order Month"} , // cols // pvtData// filteredPvtData // ); // sort by row total // pvtTbl.SortRowKeys(null, // 1, // lets order by measure #1 (sum of unit price) // System.ComponentModel.ListSortDirection.Descending); var strHtmlWr = new StringWriter(); var pvtHtmlWr = new PivotTableHtmlWriter(strHtmlWr); pvtHtmlWr.TableClass = "table table-bordered table-condensed pvtTable"; pvtHtmlWr.Write(pvtTbl); context.PivotTableHtml = strHtmlWr.ToString(); // prepare data for pie chart (total sum by country) // var pvtDataForChart = new SliceQuery(filteredPvtData).Dimension("Country").Measure(1).Execute(); // var chartPvtTbl = new PivotTable(new[]{"Country"},null,pvtDataForChart); // // sort by row total // chartPvtTbl.SortRowKeys(null, System.ComponentModel.ListSortDirection.Descending); // var strJsonWr = new StringWriter(); // var jsonWr = new PivotTableJsonWriter(strJsonWr); // jsonWr.Write(chartPvtTbl); // context.PivotTableJson = strJsonWr.ToString(); //var pager = await GetPagerAsync(context.Updater, listPart); ////giannis //var settings = GetSettings(listPart); //model.EnablePositioning = settings.EnablePositioning; //model.AdminListViewName = settings.AdminListViewName; //model.ListViewProviders = _listViewService.Providers.ToList(); //model.ListViewProvider = GetListViewProvider(model);// _listViewService.GetDefaultProvider(); //model.ItemsPerPage = listPart.ItemsPerPage; //model.Skip = listPart.Skip; //model.PagerSuffix = listPart.PagerSuffix; //model.MaxItems = listPart.MaxItems; //model.DisplayPager = listPart.DisplayPager; //======== // model.ProjectionPart = listPart; // model.ContentItems = (await QueryListItemsAsync(listPart, pager, true)).ToArray(); //model.PageOfContentItems = (await QueryListItemsAsync(listPart, pager, true)).ToArray(); //model.ContainedContentTypeDefinitions = GetContainedContentTypes(listPart); //model.Context = context; //model.Pager = await context.New.PagerSlim(pager); //model.ListViewShape = model.ListViewProvider.BuildDisplay(context, model);// context.AdminListView.BuildDisplay(context); return(Combine( Initialize <CrosstabVisualProfileViewModel>("CrosstabVisualProfile", model => { model.PivotContext = context; model.QueryResult = profile.QueryResult; model.AutoStart = profile.AutoStart; model.Controls = profile.Controls; model.Indicators = profile.Indicators; model.Interval = profile.Interval; model.Keyboard = profile.Keyboard; model.Pause = profile.Pause; model.Wrap = profile.Wrap; }) .Location("Detail", "Content:1")//, //Shape<TableLayoutProfileViewModel>("TableLayoutProfile_Summary", model => // { // model.AutoStart = profile.AutoStart; // model.Controls = profile.Controls; // model.Indicators = profile.Indicators; // model.Interval = profile.Interval; // model.Keyboard = profile.Keyboard; // model.Pause = profile.Pause; // model.Wrap = profile.Wrap; // }) // .Location("Summary", "Meta:5") )); //return Combine( // Shape("TableLayoutProfile_SummaryAdmin", model => // { // model.Profile = profile; // }).Location("Content:5") // //Shape("BootStrapProfile_Buttons_SummaryAdmin", model => // //{ // // model.Profile = profile; // //}).Location("Actions:2") //); }