Row and Column in ASP.NET MVC Pivot Table Component
21 Dec 202224 minutes to read
Width and Height
Allows end user to set the pivot table’s height and width by using Height
and Width
properties in PivotView
class respectively. The supported formats to set Height
and Width
properties are,
- Pixel: For example - 100, 200, “100px”, “200px”.
- Percentage: For example - “100%”, “200%”.
- Auto: It is applicable for
Height
property alone in-order to render the pivot table beyond its parent container height without vertical scrollbar. The parent container here would show its vertical scrollbar as soon as the component reaches beyond its dimension.
NOTE
The pivot table will not be displayed less than 400px, since it’s the minimum width of the component.
@Html.EJS().PivotView("PivotView").Width("550").Height("315px").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Row Height
Allows end user to set the height of each pivot table rows commonly using the RowHeight
property in PivotViewGridSettings
class.
NOTE
By default, the
RowHeight
property is set as 36 pixels for desktop layout and 48 pixels for mobile layout.
The height of the column headers alone may vary when grouping bar feature is enabled.
In the below code sample, the RowHeight
property is set as 60 pixels.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(new PivotViewGridSettings { RowHeight = 60 }).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Column Width
Allows end user to set the width of each pivot table columns commonly using the ColumnWidth
property in PivotViewGridSettings
class.
NOTE
By default, the
ColumnWidth
property is set as 110 pixels to each columns except the first column. For first column, 250 pixels and 200 pixels are set respectively with and without grouping bar.
In the below example, the ColumnWidth
property is set as 200 pixels.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings( new PivotViewGridSettings { ColumnWidth=120 }).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Adjust width based on columns
By default, if the component width set in code-behind is more than the width of the total columns, then the columns will be stretched to make it fit. To avoid the stretching, set the AllowAutoResizing
property in the PivotViewGridSettings
to false. By doing so, the component will be adjusted (shrinked) based on the width of total columns.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FilterSettings(filtersettings =>
{
filtersettings.Name("Year").Type(Syncfusion.EJ2.PivotView.FilterType.Exclude).Items(ViewBag.filterMembers).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add();
})).GridSettings( new PivotViewGridSettings { AllowAutoResizing=true }).Render()
Reorder
Allows end user to reorder a particular column header from one index to another index within the pivot table through drag-and-drop option. It can be enabled by setting the AllowReordering
property in PivotViewGridSettings
class to true.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings( new PivotViewGridSettings { AllowReordering=true }).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Column Resizing
Allows end user to resize the columns by clicking and dragging the right edge of the column header. While dragging, the width of the respective column will be resized immediately. To enable column resizing option, set the AllowResizing
property in PivotViewGridSettings
class to true.
NOTE
By default, the column resizing option is enabled.
In RTL mode, user can click and drag the left edge of the header cell to resize the column.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings( new PivotViewGridSettings { AllowResizing=true }).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Text Wrap
Allows end user to wrap the cell content to the next line when it exceeds the boundary of the cell width. To enable text wrap, set the AllowTextWrap
property in PivotViewGridSettings
class to true.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings( new PivotViewGridSettings { AllowTextWrap=true }).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Text Align
Allows end user to align the content of the pivot table’s row and column headers and value cells by using the TextAlign
and HeaderTextAlign
properties in the ColumnRender
event under GridSettings
. The following alignments are:
-
Left
- It allows the content to be positioned on the left. -
Right
- It allows the content to be positioned on the right. -
Center
- It allows the content to be positioned in the middle. -
Justify
- It allows the content to be as flexible as possible, when the cell does not occupy the entire available area.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").Width(650).DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(true)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.ColumnRender("columnRender")).Render()
<script>
function columnRender(args) {
if(args.stackedColumns[0]){
// Content for the row headers is right-aligned here.
args.stackedColumns[0].textAlign="Right";
}
if(args.stackedColumns[1]){
// Content for the column header "FY 2015" is center-aligned here.
args.stackedColumns[1].textAlign = 'Center';
}
if(args.stackedColumns[1] && args.stackedColumns[1].columns[0]){
// Content for the column header "Q1" is right-aligned here.
args.stackedColumns[1].columns[0].textAlign = 'Right';
}
if(args.stackedColumns[1] && args.stackedColumns[1].columns[0] && args.stackedColumns[1].columns[0].columns[0]){
// Content for the value header "Units Sold" is right-aligned here.
args.stackedColumns[1].columns[0].columns[0].headerTextAlign = 'Right';
}
if(args.stackedColumns[1] && args.stackedColumns[1].columns[0] && args.stackedColumns[1].columns[0].columns[0]){
// Content for the values are left-aligned here.
args.stackedColumns[1].columns[0].columns[0].textAlign = 'Left';
}
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
AutoFit
Allows the user to fit the Pivot Table columns as wide as the content of the cell without wrapping. It auto fits all of the Pivot Table columns by invoking the autoFitColumns
method from the grid instance.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").Width(650).DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).DataBound("ondataBound").Render()
<script>
function ondataBound(args) {
var pivotTableObj = document.getElementById('PivotView').ej2_instances[0];
pivotTableObj.grid.autoFitColumns();
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
NOTE
The minimum width of 250 pixels is set by default with the grouping bar UI for the first column and cannot be reduced further. So, when the grouping bar is enabled, one can auto fit the Pivot Table columns by calling the
autoFitColumns
method from the grid instance with the parameter contained pivot table columns field name excluding first column.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").Width(650).DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).DataBound("ondataBound").ShowGroupingBar(true).Render()
<script>
function ondataBound(args) {
var pivotTableObj = document.getElementById('PivotView').ej2_instances[0];
var columns = [];
for (var i = 1; i < pivotTableObj.grid.columnModel.length; i++) {
columns.push(pivotTableObj.grid.columnModel[i].field);
}
pivotTableObj.grid.autoFitColumns(columns);
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Autofit specific columns
During initial rendering, the parameter autoFit
in the ColumnRender
event under PivotViewGridSettings
can be set to true to auto fit specific columns.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").Width(650).DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.ColumnRender("columnRender")).Render()
<script>
function columnRender(args) {
for (var i = 0; i < args.columns.length; i++) {
args.columns[i].autoFit = true;
}
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Grid Lines
Allows end user to display cell border for each cells using GridLines
property in PivotViewGridSettings
class.
Available mode of grid lines are:
Modes | Actions |
---|---|
Both | Displays both the horizontal and vertical grid lines. |
None | No grid lines are displayed. |
Horizontal | Displays the horizontal grid lines only. |
Vertical | Displays the vertical grid lines only. |
Default | Displays grid lines based on the theme. |
NOTE
By default, pivot table renders grid lines in Both mode.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings( new PivotViewGridSettings { GridLines= "Vertical" }).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Selection
Selection provides an option to highlight a row or a column or a cell. It can be done through simple mouse down or arrow keys. To enable selection in the pivot table, set the AllowSelection
property in PivotViewGridSettings
class to true.
The pivot table supports two types of selection that can be set using Type
property in PivotViewSelectionSettings
class. The selection types are:
-
Single
: It is set by default, and it only allows selection of a single row or a column or a cell. -
Multiple
: Allows you to select multiple rows or columns or cells.
To perform multi-selection, press and hold “CTRL” key and click the desired rows or cells. To select range of rows or cells, press and hold the “SHIFT” key and click the rows or columns or cells.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.AllowSelection(true).SelectionSettings(selectionSettings => selectionSettings.Type("Multiple"))).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Selection Mode
The pivot table supports four types of selection mode that can be set using Mode
property in PivotViewSelectionSettings
class.. The selection modes are:
- SelectionMode.Row: It is set by default, and allows user to select only rows.
- SelectionMode.Column: Allows user to select only columns.
- SelectionMode.Cell: Allows user to select only cells.
- SelectionMode.Both: Allows user to select rows and columns at the same time.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.AllowSelection(true).SelectionSettings(selectionSettings => selectionSettings.Mode(SelectionMode.Both))).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Cell Selection Mode
The pivot table supports two types of cell selection mode that can be set using CellSelectionMode
in PivotViewSelectionSettings
class. The cell selection modes are:
- PivotCellSelectionMode.Flow: It is set by default. The range of cells are selected between the start index and end index that includes in-between cells of rows.
- PivotCellSelectionMode.Box: Range of cells are selected from the start and end column indexes that includes in-between cells of rows within the range.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.AllowSelection(true).SelectionSettings(selectionSettings => selectionSettings.CellSelectionMode("Box").Type("Multiple").Mode(SelectionMode.Both))).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
NOTE
Cell selection requires
Mode
property inPivotViewSelectionSettings
class to be SelectionMode.Cell or SelectionMode.Both, andType
property should beMultiple
.
Changing background color of the selected cell
The background-color of the selected cell can be changed using built-in CSS names. To do so, refer to the code sample below, which shows that the selected cells are changed to a green yellow color.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.AllowSelection(true).SelectionSettings(selectionSettings => selectionSettings.CellSelectionMode("Box").Type("Multiple").Mode(SelectionMode.Both))).Render()
<style>
.e-pivotview .e-cellselectionbackground,
.e-pivotview .e-selectionbackground,
.e-pivotview .e-grid .e-rowsheader.e-selectionbackground,
.e-pivotview .e-grid .e-columnsheader.e-selectionbackground {
background-color: greenYellow !important;
}
</style>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Event
CellSelected
The event CellSelected
is triggered when cell selection gets completed. It provides selected cells information with its corresponding column and row headers. It has following parameters - selectedCellsInfo
, currentCell
and target
. This event allows user to view selected cells information and user can pass those selected cells information to any external component for data binding.
<div>
<div class="column-8">
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("pivotview").Width("100%").Height("400").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>
)ViewBag.DataSource).ExpandAll(true).EnableSorting(true)
.Rows(rows =>
{
rows.Name("Country").Add();
rows.Name("Products").Add();
})
.Columns(columns =>
{
columns.Name("Year").Add();
columns.Name("Order_Source").Caption("Order Source").Add();
})
.Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add();
values.Name("Amount").Caption("Sold Amount").Add();
})
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
})
.Filters(filters =>
{
filters.Name("Product_Categories").Caption("Product Categories").Add();
})).GridSettings(gridSettings => gridSettings.AllowSelection(true).SelectionSettings(selectionSettings => selectionSettings.CellSelectionMode(PivotCellSelectionMode.Box).Type("Multiple").Mode(SelectionMode.Cell))).CellSelected("onCellSelected").Render()
</div>
<div class="column-3">
<div class="eventarea" style="height: 230px;overflow: auto">
<span class="EventLog" id="selection-EventLog" style="word-break: normal;"></span>
</div>
</div>
</div>
<script>
function onCellSelected(args) {
document.getElementById('selection-EventLog').innerHTML = '';
if (args.selectedCellsInfo.length > 0) {
for (var cnt = 0; cnt < args.selectedCellsInfo.length; cnt++) {
var cell = args.selectedCellsInfo[cnt];
var summMeasure = this.engineModule.fieldList[cell.measure] ? this.engineModule.fieldList[cell.measure].aggregateType + ' of ' +
this.engineModule.fieldList[cell.measure].caption : '';
appendElement(
(cell.columnHeaders == '' ? '' : 'Column Headers: ' + '<b>' + cell.columnHeaders.split('.').join(' - ') + '</b></br>') +
(cell.rowHeaders == '' ? '' : 'Row Headers: ' + '<b>' + cell.rowHeaders.split('.').join(' - ') + '</b></br>') +
(summMeasure == '' ? '' : 'Measure: ' + '<b>' + summMeasure + '</b></br>') +
'Value: ' + '<b>' + cell.currentCell.formattedText + '</b><hr></br>');
}
}
}
function appendElement(html) {
var span = document.createElement('span');
span.innerHTML = html;
var log = document.getElementById('selection-EventLog');
log.appendChild(span);
}
var modeddl = new ej.dropdowns.DropDownList({
floatLabelType: 'Auto',
width: 150,
change: function (args) {
var pivotObj = document.getElementById('pivotview').ej2_instances[0];
pivotObj.gridSettings.selectionSettings.mode = args.value;
pivotObj.renderModule.updateGridSettings();
}
});
modeddl.appendTo('#mode');
var typeddl = new ej.dropdowns.DropDownList({
floatLabelType: 'Auto',
width: 150,
change: function (args) {
var pivotObj = document.getElementById('pivotview').ej2_instances[0];
pivotObj.gridSettings.selectionSettings.type = args.value;
pivotObj.renderModule.updateGridSettings();
}
});
typeddl.appendTo('#type');
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
CellSelecting
The event CellSelecting
triggers before cell gets selected gets completed. It provides selected cells information with its corresponding column and row headers. It has following parameters - currentCell
, data
and cancel
.
<div>
<div class="column-8">
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("pivotview").Width("100%").Height("400").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>
)ViewBag.DataSource).ExpandAll(true).EnableSorting(true)
.Rows(rows =>
{
rows.Name("Country").Add();
rows.Name("Products").Add();
})
.Columns(columns =>
{
columns.Name("Year").Add();
columns.Name("Order_Source").Caption("Order Source").Add();
})
.Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add();
values.Name("Amount").Caption("Sold Amount").Add();
})
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
})
.Filters(filters =>
{
filters.Name("Product_Categories").Caption("Product Categories").Add();
})).GridSettings(gridSettings => gridSettings.AllowSelection(true).SelectionSettings(selectionSettings => selectionSettings.CellSelectionMode(PivotCellSelectionMode.Box).Type("Multiple").Mode(SelectionMode.Cell))).CellSelecting("cellSelecting").Render()
</div>
<script>
function cellSelecting(args) {
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Clip Mode
The clip mode provides options to display its overflow cell content in the pivot table. It can be configured using the ClipMode
property in PivotViewGridSettings
class. The pivot table supports three types of clip modes which are:
-
Clip
: Truncates the cell content when it overflows its area. -
Ellipsis
: Displays ellipsis when the cell content overflows its area. -
EllipsisWithTooltip
: Displays ellipsis when the cell content overflows its area, also it will display the tooltip while hover on ellipsis is applied.
NOTE
By default,
ClipMode
value is set toEllipsis
.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.ClipMode("Clip")).Render()
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Cell Template
User can customize the pivot table cell element by using the CellTemplate
property in PivotViewTemplates
class. The CellTemplate
property accepts either an HTML string or the element’s ID, which can be used to append additional HTML elements to showcase each cell with custom format.
In this demo, the revenue cost for each year is represented with trend icons.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("pivotview").Width("100%").Height("300").DataBound("trend").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(true).EnableSorting(true)
.DrilledMembers(drilledmembers =>{
drilledmembers.Name("Year").Items(ViewBag.drilledMembers).Add();
})
.FormatSettings(formatsettings => {
formatsettings.Name("ProCost").Format("C0").Add();
})
.Rows(rows => {
rows.Name("Year").Caption("Year").Add(); rows.Name("HalfYear").Caption("Half Year").Add();
})
.Columns(columns => {
columns.Name("EnerType").Caption("Energy Type").Add(); columns.Name("EneSource").Caption("Energy Source").Add();
})
.Values(values => {
values.Name("ProCost").Caption("Revenue Growth").Add();
})).GridSettings(new PivotViewGridSettings { ColumnWidth = 140 }).CellTemplate("${getCellContent(data)}").Render()
<script>
window.getCellContent = function (e) {
var template;
if (e && e.targetCell.className.indexOf('e-valuescontent') > -1) {
template = '<span class="tempwrap sb-icon-neutral pv-icons"></span>';
} else {
template = '';
}
return template;
};
/* jshint ignore:start */
function trend() {
var pivotObj = document.getElementById('pivotview').ej2_instances[0];
var cTable = document.getElementsByClassName("e-table");
var colLen = pivotObj.pivotValues[3].length;
var cLen = cTable[3].children[0].children.length;
var rLen = cTable[3].children[1].children.length;
for (let k = 0; k < rLen; k++) {
if (pivotObj.pivotValues[k] && pivotObj.pivotValues[k][0] !== undefined) {
rowIndx = (pivotObj.pivotValues[k][0]).rowIndex;
break;
}
}
var rowHeaders = [].slice.call(cTable[2].children[1].querySelectorAll('td'));
var rows = pivotObj.dataSourceSettings.rows;
if (rowHeaders.length > 1) {
for (var i = 0, Cnt = rows; i < Cnt.length; i++) {
var fields = {};
var fieldHeaders = [];
for (var j = 0, Lnt = rowHeaders; j < Lnt.length; j++) {
var header = rowHeaders[j];
if (header.className.indexOf('e-gtot') === -1 && header.className.indexOf('e-rowsheader') > -1 && header.getAttribute('fieldname') === rows[i].name) {
var headerName = rowHeaders[j].getAttribute('fieldname') + '_' + rowHeaders[j].textContent;
fields[rowHeaders[j].textContent] = j;
fieldHeaders.push(rowHeaders[j].textContent);
}
}
if (i === 0) {
for (var rnt = 0, Lnt = fieldHeaders; rnt < Lnt.length; rnt++) {
if (rnt !== 0) {
var row = fields[fieldHeaders[rnt]];
var prevRow = fields[fieldHeaders[rnt - 1]];
for (var j = 0, ci = 1; j < cLen && ci < colLen; j++ , ci++) {
if (!cTable[3].children[1].children[row]) {
break;
}
var node = cTable[3].children[1].children[row].childNodes[j];
var prevNode = cTable[3].children[1].children[prevRow].childNodes[j];
var ri = undefined;
var prevRi = undefined;
if (node) {
ri = node.getAttribute('index');
}
if (prevNode) {
prevRi = prevNode.getAttribute('index');
}
if (ri && ri < pivotObj.pivotValues.length) {
if ((pivotObj.pivotValues[prevRi][ci]).value > (pivotObj.pivotValues[ri][ci]).value && node.querySelector('.tempwrap')) {
var trendElement = node.querySelector('.tempwrap');
trendElement.className = trendElement.className.replace('sb-icon-neutral', 'sb-icon-loss');
} else if ((pivotObj.pivotValues[prevRi][ci]).value < (pivotObj.pivotValues[ri][ci]).value && node.querySelector('.tempwrap')) {
var trendElement = node.querySelector('.tempwrap');
trendElement.className = trendElement.className.replace('sb-icon-neutral', 'sb-icon-profit');
}
}
}
}
}
} else {
for (var rnt = 0, Lnt = fieldHeaders; rnt < Lnt.length; rnt++) {
var row = fields[fieldHeaders[rnt]];
for (var j = 0, ci = 1; j < cLen && ci < colLen; j++ , ci++) {
if (!cTable[3].children[1].children[row]) {
break;
}
var node = cTable[3].children[1].children[row].childNodes[j];
var prevNode = cTable[3].children[1].children[row - 1].childNodes[j];
var ri = undefined;
var prevRi = undefined;
if (node) {
ri = node.getAttribute('index');
}
if (prevNode) {
prevRi = prevNode.getAttribute('index');
}
if (ri && ri < pivotObj.pivotValues.length) {
var cRowFieldName = cTable[2].children[1].children[row].childNodes[0].getAttribute('fieldname');
var prevRowFieldName = cTable[2].children[1].children[row - 1].childNodes[0].getAttribute('fieldname');
if ((pivotObj.pivotValues[prevRi][ci]).value > (pivotObj.pivotValues[ri][ci]).value && node.querySelector('.tempwrap') &&
cRowFieldName === prevRowFieldName) {
var trendElement = node.querySelector('.tempwrap');
trendElement.className = trendElement.className.replace('sb-icon-neutral', 'sb-icon-loss');
} else if ((pivotObj.pivotValues[prevRi][ci]).value < (pivotObj.pivotValues[ri][ci]).value && node.querySelector('.tempwrap') &&
cRowFieldName === prevRowFieldName) {
var trendElement = node.querySelector('.tempwrap');
trendElement.className = trendElement.className.replace('sb-icon-neutral', 'sb-icon-profit');
}
}
}
}
}
}
}
}
/* jshint ignore:end */
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
Events
QueryCellInfo
The event queryCellInfo
triggers while rendering each row and value cells in the pivot table. It allows the user to customize the current cell like adding or removing styles, editing value, etc. It has the following parameters:
-
cell
- It holds the current cell information. -
data
- It holds the entire row data besides the current cell. -
column
- It holds the entire column data besides the current cell. -
pivotview
- It holds pivot table instance.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.QueryCellInfo("querycell")).Render()
<script>
function queryCell(args) {
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
HeaderCellInfo
The event headerCellInfo
triggers while rendering each column header cell in the pivot table. It allows the user to customize the element of the current header cell like adding or removing styles, editing value, etc. It has the following parameters:
-
node
- It holds the current header cell information.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.HeaderCellInfo("headerCellInfo")).Render()
<script>
function headerCellInfo(args) {
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
ColumnRender
The event ColumnRender
triggers while framing each columns for rendering in the pivot table. It allows the user to customize the text alignment, column visibility, autofit, re-ordering, minimum and maximum width for a specific column. It has the following parameters:
-
columns
- It holds the leaf level columns (i.e., value headers) information. -
dataSourceSettings
- It holds the current data source settings such as input data source, rows, columns, values, filters, format settings and so on. -
name
- It holds the name of the event. -
stackedColumns
- It holds the drilled columns (i.e., including column and value headers) information.
@using Syncfusion.EJ2.PivotView
@Html.EJS().PivotView("PivotView").Height("300").Width(650).DataSourceSettings(dataSourceSettings => dataSourceSettings.DataSource((IEnumerable<object>)ViewBag.DataSource).ExpandAll(false)
.FormatSettings(formatsettings =>
{
formatsettings.Name("Amount").Format("C0").MaximumSignificantDigits(10).MinimumSignificantDigits(1).UseGrouping(true).Add();
}).Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).GridSettings(gridSettings => gridSettings.ColumnRender("columnRender")).Render()
<script>
function columnRender(args) {
for (var i = 0; i < args.columns.length; i++) {
args.columns[i].autoFit = true;
args.columns[i].textAlign="Right";
}
}
</script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}
CellClick
The event CellClick
triggers while clicking a cell in the pivot table. For instance, using this event end-user can either add or remove styles, edit value and also perform any other DOM manipulations. It has the following parameters:
-
currentCell
- It holds the current cell information. -
data
- It holds the clicked cell’s data like axis, formatted text, actual text, row header, column header and value informations.
@Html.EJS().PivotView("PivotView").Height(300).DataSourceSettings(dataSource => dataSource.DataSource((IEnumerable<object>)ViewBag.DataSource)
.Rows(rows =>
{
rows.Name("Country").Add(); rows.Name("Products").Add();
}).Columns(columns =>
{
columns.Name("Year").Caption("Year").Add(); columns.Name("Quarter").Add();
}).Values(values =>
{
values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Add();
})).ShowGroupingbar(true).CellClick("cellClick").Render()
<script>
function cellClick(args){
args.currentCell.setAttribute("style", "background-color: red;");
}
<script>
public ActionResult Index()
{
var data = GetPivotData();
ViewBag.DataSource = data;
return View();
}