Features
Overview
Overview
OLAP Component displays data from OData Invoice table using ADO.NET Connector for OData.
Change provider
header
0
header 0 | header Year 0 |
header Country 0 | header Amount (Sum) 0 |
Year:1996;Amount:0;
Year:1997;Amount:0;
Year:1998;Amount:0;
Year:2019;Amount:0;
Amount:0;
238
1,272
305
1,815
17,170
50,624
35,321
10,122
113,237
6,521
19,003
25,529
51,053
1,833
8,883
11,105
1,310
23,130
15,714
41,374
13,468
1,102
71,658
16,738
36,977
37,701
858
92,273
9,142
25,379
22,796
57,317
231
1,314
1,546
593
7,252
1,174
9,019
1,322
5,762
68
7,152
2,360
8,053
7,552
17,965
7,415
28,025
24,084
59,524
1,799
6,658
4,429
12,886
3,710
16,993
22,093
42,795
338
12,231
9,853
710
23,131
2,221
13,869
9,010
25,100
86,875
281,551
226,769
14,406
609,602
Argentina
Austria
Brazil
Canada
France
Germany
Ireland
Italy
Mexico
Portugal
Spain
Sweden
Switzerland
UK
USA
Venezuela
Grand Total
1996
1997
1998
2019
Amount
Country
0
ShowValueFieldHeaders
You can use the viewDefinition property to save and restore view definitions, as shown in the following example:
The PivotGrid control extends the FlexGrid control, so you can export it to any of
the formats supported by the extension modules provided with the FlexGrid. The
list of the supported formats includes .xlsx, .csv, and .pdf.
For example, clicking the button below creates an Excel file with two sheets: the current view and a transposed version of the current view.
Settings
Row Totals: Subtotals
Column Totals: Subtotals
Show Zeros: False
Allow Merging: All
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | using DataConnectorExplorer.Models; using System.Collections; using Microsoft.AspNetCore.Mvc; using System.Linq; using System.Collections.Generic; using C1.Web.Mvc.Olap; using C1.AdoNet.OData; using System.Globalization; using System; using Microsoft.Extensions.Options; using System.IO; namespace DataConnectorExplorer.Controllers { /// <summary> /// Controller for OData /// </summary> public class IndexController : Controller { private readonly IOptions<ODataConnection> config; /// <summary> /// /// </summary> /// <param name="config"></param> public IndexController(IOptions<ODataConnection> config) { this .config = config; } private IEnumerable<ODataInvoiceData> GetData() { //Create the connection to OData server //connstr is connection string to OData server //Exp: //const string url = "..."; //static string connstr = "Url={url};Max Page Size = 100" var config = this .config.Value; string _cacheFolder = "Cache" ; if (!Directory.Exists(_cacheFolder)) { Directory.CreateDirectory(_cacheFolder); } string specifiedCacheFile = Path.Combine(_cacheFolder, "C1CacheFile.OData.db" ); string connectionStringCachePart = string .Format( "Use Cache = 'true'; Cache Location='{0}'; Cache Tolerance = 28800" , specifiedCacheFile); var conectionStr = $ @"Url={config.Url};Max Page Size = 100;" ; conectionStr += connectionStringCachePart; using (C1ODataConnection con = new C1ODataConnection(conectionStr)) { //Open the connection to OData server con.Open(); var cmd = con.CreateCommand(); //query to select from OData server cmd.CommandText = "Select UnitPrice, Quantity, PostalCode, Country, Address, City, Region, CustomerName, ShippedDate from Invoices limit 1000" ; var reader = cmd.ExecuteReader(); List<ODataInvoiceData> result = new List<ODataInvoiceData>(); CultureInfo provider = CultureInfo.InvariantCulture; //Generate List<ODataInvoiceData> from reader while (reader.Read()) { var unitPrice = String.IsNullOrEmpty(reader[ "UnitPrice" ].ToString()) ? 0 : Convert.ToDecimal(reader[ "UnitPrice" ].ToString()); var quantity = String.IsNullOrEmpty(reader[ "Quantity" ].ToString()) ? 0 : Convert.ToDecimal(reader[ "Quantity" ].ToString()); var amount = unitPrice * quantity; var country = reader[ "Country" ].ToString(); var city = reader[ "City" ].ToString(); var region = reader[ "Region" ].ToString(); var shippedDateValue = reader[ "ShippedDate" ].ToString(); DateTime? shippedDate; if (!String.IsNullOrEmpty(shippedDateValue) && DateTime.TryParse(shippedDateValue, out DateTime convertedDate)) { shippedDate = convertedDate; } else { //Put the default value if shippedDate is null. Just use for demo shippedDate = new DateTime(2019, 12, 1); } var customerName = reader[ "CustomerName" ].ToString(); //Add ODataInvoiceData model from the reader result.Add( new ODataInvoiceData { Amount = amount, Country = country, City = city, Region = region, CustomerName = customerName, ShippedDate = shippedDate.Value.ToString(CultureInfo.InvariantCulture), Year = shippedDate.Value.Year.ToString(), Month = shippedDate.Value.ToString( "MMMM" ), }); ; } return result; } } private static string StandardizeName( string input) { if (!String.IsNullOrEmpty(input)) { return input.First().ToString().ToUpper() + input.Substring(1).ToLower(); } else { return "" ; } } private readonly ClientSettingsModel OlapModel = new ClientSettingsModel { Settings = new Dictionary< string , object []> { { "RowTotals" , new object [] { ShowTotals.Subtotals, ShowTotals.None, ShowTotals.GrandTotals} }, { "ColumnTotals" , new object [] { ShowTotals.Subtotals, ShowTotals.None, ShowTotals.GrandTotals} }, { "ShowZeros" , new object [] { false , true } }, { "AllowMerging" , new object [] { C1.Web.Mvc.Grid.AllowMerging.All, C1.Web.Mvc.Grid.AllowMerging.AllHeaders, C1.Web.Mvc.Grid.AllowMerging.Cells, C1.Web.Mvc.Grid.AllowMerging.ColumnHeaders, C1.Web.Mvc.Grid.AllowMerging.None, C1.Web.Mvc.Grid.AllowMerging.RowHeaders } } } }; /// <summary> /// Action Index /// </summary> /// <returns></returns> public IActionResult Index() { IEnumerable Data = GetData().ToList(); OlapModel.ControlId = "indexPanel" ; ViewBag.DemoOptions = OlapModel; return View(Data); } } } |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 | @model IEnumerable< ODataInvoiceData > @ { ClientSettingsModel optionsModel = ViewBag.DemoOptions; } < c1-pivot-engine id = "indexEngine" show-row-totals = "Subtotals" show-column-totals = "Subtotals" totals-before-data = "false" > < c1-items-source source-collection = "Model" ></ c1-items-source > < c1-pivot-field-collection > < c1-pivot-field header = "Country" binding = "Country" ></ c1-pivot-field > < c1-pivot-field header = "City" binding = "City" ></ c1-pivot-field > < c1-pivot-field header = "Region" binding = "Region" ></ c1-pivot-field > < c1-pivot-field header = "Year" binding = "Year" ></ c1-pivot-field > < c1-pivot-field header = "Month" binding = "Month" ></ c1-pivot-field > < c1-pivot-field header = "Customer Name" binding = "CustomerName" ></ c1-pivot-field > < c1-pivot-field header = "Amount" binding = "Amount" ></ c1-pivot-field > </ c1-pivot-field-collection > < c1-view-field-collection c1-property = "RowFields" items = "Country" ></ c1-view-field-collection > < c1-view-field-collection c1-property = "ColumnFields" items = "Year" ></ c1-view-field-collection > < c1-view-field-collection c1-property = "ValueFields" items = "Amount" ca></ c1-view-field-collection > </ c1-pivot-engine > < div class = "row" > < div class = "col-sm-4 col-md-4" > < c1-pivot-panel id = "@(optionsModel.ControlId)" items-source-id = "indexEngine" ></ c1-pivot-panel > </ div > < div class = "col-sm-8 col-md-8" > < c1-pivot-grid id = "indexGrid" items-source-id = "indexEngine" outline-mode = "false" show-value-field-headers = "false" ></ c1-pivot-grid > < p > < span style = "vertical-align:top; color:black;" > ShowValueFieldHeaders < input id = "ShowValueFieldHeaders" type = "checkbox" onchange = "toggleShowValueFieldHeaders(event)" /> </ span > </ p > </ div > </ div > < p > @Html .Raw(OlapRes.Index_Text0)</ p > < button type = "button" class = "btn btn-default" onclick = "saveView()" > @Html .Raw(OlapRes.Index_Text7)</ button > < button type = "button" class = "btn btn-default" onclick = "loadView()" > @Html .Raw(OlapRes.Index_Text8)</ button > < p ></ p > < p > @Html .Raw(OlapRes.Index_Text2)</ p > < p > @Html .Raw(OlapRes.Index_Text3)</ p > < button type = "button" class = "btn btn-default" onclick = "excelExport()" > @Html .Raw(OlapRes.Index_Text9) </ button > < c1-pivot-chart id = "demoChart" items-source-id = "indexEngine" ></ c1-pivot-chart > @section Scripts{ <script type="text/javascript"> function saveView() { var ng = c1.getService( 'indexEngine' ); if (ng && ng.isViewDefined) { localStorage.viewDefinition = ng.viewDefinition; } } function loadView() { var ng = c1.getService( 'indexEngine' ); if (ng && localStorage.viewDefinition) { ng.viewDefinition = localStorage.viewDefinition; var cmbRowTotals = wijmo.Control.getControl( '#RowTotals' ); if (cmbRowTotals) { cmbRowTotals.selectedValue = ng.showRowTotals; } var cmbColTotals = wijmo.Control.getControl( '#ColTotals' ); if (cmbColTotals) { cmbColTotals.selectedValue = ng.showColumnTotals; } var chkShowZeros = document.getElementById( 'ColTotals' ); if (chkShowZeros) { chkShowZeros. checked = ng.showZeros; } } } function excelExport() { var pivotGrid = wijmo.Control.getControl( '#indexGrid' ); // create book with current view var book = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, { includeColumnHeaders: true , includeRowHeaders: true }); book.sheets[0].name = 'Main View' ; addTitleCell(book.sheets[0], getViewTitle(pivotGrid.engine)); // add sheet with transposed view transposeView(pivotGrid.engine); var transposed = wijmo.grid.xlsx.FlexGridXlsxConverter.save(pivotGrid, { includeColumnHeaders: true , includeRowHeaders: true }); transposed.sheets[0].name = 'Transposed View' ; addTitleCell(transposed.sheets[0], getViewTitle(pivotGrid.engine)); book.sheets.push(transposed.sheets[0]); transposeView(pivotGrid.engine); // save the book book.save( 'wijmo.olap.xlsx' ); } // build a title for the current view function getViewTitle(ng) { var title = '' ; for ( var i = 0; i < ng.valueFields.length ; i++) { if (i > 0) title += ', ' ; title += ng.valueFields[i].header; } title += ' by ' ; if (ng.rowFields.length) { for ( var i = 0; i < ng.rowFields.length ; i++) { if (i > 0) title += ', ' ; title += ng.rowFields[i].header; } } if (ng.rowFields.length && ng.columnFields.length) { title += ' and by ' ; } if (ng.columnFields.length) { for ( var i = 0; i < ng.columnFields.length ; i++) { if (i > 0) title += ', ' ; title += ng.columnFields[i].header; } } return title; } function transposeView(ng) { ng.deferUpdate( function () { // save row/col fields var rows = [], cols = []; for ( var r = 0; r < ng.rowFields.length ; r++) { rows.push(ng.rowFields[r].header); } for ( var c = 0 ; c < ng.columnFields.length; c++) { cols.push(ng.columnFields[c].header); } // clear row/col fields ng.rowFields.clear(); ng.columnFields.clear(); // restore row/col fields in transposed order for ( var r = 0 ; r < rows.length; r++) { ng.columnFields.push(rows[r]); } for ( var c = 0 ; c < cols.length; c++) { ng.rowFields.push(cols[c]); } }); } // adds a title cell into an xlsx sheet function addTitleCell(sheet, title) { // create cell var cell = new wijmo.xlsx.WorkbookCell(); cell.value = title ; cell.style = new wijmo.xlsx.WorkbookStyle(); cell.style.font = new wijmo.xlsx.WorkbookFont(); cell.style.font.bold = true ; // create row to hold the cell var row = new wijmo.xlsx.WorkbookRow(); row.cells[0] = cell; // and add the new row to the sheet sheet.rows.splice(0, 0, row); } // toggle outline mode function toggleOulineMode(e) { var pivotGrid = wijmo .Control.getControl( '#indexGrid' ); pivotGrid.outlineMode = e.target. checked ; } // toggle ShowValueFieldHeaders function toggleShowValueFieldHeaders(e) { var pivotGrid = wijmo .Control.getControl( '#indexGrid' ); pivotGrid.showValueFieldHeaders = e.target. checked ; } </script> } @section Settings{ @Html .Partial( "_OptionsMenu" , optionsModel) } @section Summary{ < p > @Html .Raw(OlapRes.Provider_OData_Description)</ p > } |