//
// GET: /PivotTable/
public ActionResult PivotTable(string button, string Filter, string RowFilter, string ColumnFilter, string MultiplePageFilter, string PageFilter)
{
if (button == null)
{
return(View());
}
string basePath = _hostingEnvironment.WebRootPath;
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook = null;
application.DefaultVersion = ExcelVersion.Excel2016;
if (button == "Customize Pivot Table")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/PivotTable.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[1];
sheet.Activate();
//Access the collection of Pivot Table in the worksheet.
IPivotTables pivotTables = sheet.PivotTables;
//Access the Single pivot table from the collection.
IPivotTable pivotTable = pivotTables[0];
//Access collection of pivot fields from the pivot table.
IPivotFields fields = pivotTable.Fields;
//Access a Pivot field from the collection.
IPivotField field = fields[2];
//Add the field to page axis
field.Axis = PivotAxisTypes.Page;
fields[1].Axis = PivotAxisTypes.None;
fields[0].Axis = PivotAxisTypes.None;
fields[3].Axis = PivotAxisTypes.Row;
fields[4].Axis = PivotAxisTypes.Column;
IPivotField dataField = fields[5];
//Accessing the Calculated fields from the pivot table .
IPivotCalculatedFields calculatedfields = pivotTable.CalculatedFields;
if (RowFilter == "RowFilter")
{
if (Filter == "LabelFilter")
{
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Parent", null);
}
else if (Filter == "ValueFilter")
{
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
}
else
{
pivotTable.Fields[3].Items[0].Visible = false;
}
}
if (ColumnFilter == "ColumnFilter")
{
if (Filter == "LabelFilter")
{
pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Binder", null);
}
else if (Filter == "ValueFilter")
{
pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
}
else
{
pivotTable.Fields[4].Items[0].Visible = false;
}
}
//Adding Calculatd field to the pivot table.
// IPivotField calculatedField = calculatedfields.Add("Percent", "Units/3000*100");
if (PageFilter == "PageFilter")
{
//Create Pivot Filter object to apply filter to page Fields
IPivotFilter filterValue = pivotTable.Fields[2].PivotFilters.Add();
//Page Field would be filtered with value 'Binder'
filterValue.Value1 = "East";
//XlsIO layout the Pivot table like Microsoft Excel
if (Filter != "ValueFilter")
{
pivotTable.Layout();
}
}
else if (MultiplePageFilter == "MultiplePageFilter")
{
pivotTable.Fields[2].Items[0].Visible = false;
}
sheet.Range[1, 1, 1, 14].ColumnWidth = 11;
sheet.SetColumnWidth(1, 15.29);
sheet.SetColumnWidth(2, 15.29);
try
{
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "PivotTable.xlsx"));
}
catch (Exception)
{
}
}
else
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/PivotCodeDate.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
//Access the sheet to draw pivot table.
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Activate();
//Select the data to add in cache
IPivotCache cache = workbook.PivotCaches.Add(sheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[4].Axis = PivotAxisTypes.Page;
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[6].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField field = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field, "Sum of Units", PivotSubtotalTypes.Sum);
#region Apply RowField Filter
if (RowFilter == "RowFilter")
{
if (Filter == "LabelFilter")
{
pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.CaptionEqual, null, "East", null);
}
else if (Filter == "ValueFilter")
{
pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.ValueEqual, field, "1341", null);
}
else
{
pivotTable.Fields[2].Items[0].Visible = false;
pivotTable.Fields[2].Items[1].Visible = false;
}
}
#endregion
#region Column Field Filter
if (ColumnFilter == "ColumnFilter")
{
if (Filter == "LabelFilter")
{
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Jones", null);
}
else if (Filter == "ValueFilter")
{
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueEqual, field, "398", null);
}
else
{
pivotTable.Fields[3].Items[0].Visible = false;
pivotTable.Fields[3].Items[1].Visible = false;
}
}
#endregion
if (PageFilter == "PageFilter")
{
//'Create Pivot Filter object to apply filter to page Fields
IPivotFilter filterValue = pivotTable.Fields[4].PivotFilters.Add();
//Page Field would be filtered with value 'Binder'
filterValue.Value1 = "Binder";
//XlsIO layout the Pivot table like Microsoft Excel
if (Filter != "ValueFilter")
{
pivotTable.Layout();
}
}
else
{
pivotTable.Fields[4].Items[1].Visible = false;
pivotTable.Fields[4].Items[2].Visible = false;
}
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotSheet.Activate();
try
{
workbook.Version = ExcelVersion.Excel2016;
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "PivotTable.xlsx"));
}
catch (Exception)
{
}
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
private void btnCustomize_Click(object sender, EventArgs e)
{
#region Workbook Initialize
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
//Set the default version as Excel 2007;
if (rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
//Set the default version as Excel 2010;
if (rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
//Set the default version as Excel 2013;
if (rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//Get the Path of the Input File
string inputPath = GetFullTemplatePath("PivotTable.xlsx");
IWorkbook workbook = application.Workbooks.Open(inputPath);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[1];
worksheet.Activate();
#endregion
#region Customize the Pivot Table
//Access the collection of Pivot Table in the worksheet.
IPivotTables pivotTables = worksheet.PivotTables;
//Access the Single pivot table from the collection.
IPivotTable pivotTable = pivotTables[0];
//Access collection of pivot fields from the pivot table.
IPivotFields fields = pivotTable.Fields;
//Access a Pivot field from the collection.
IPivotField field = fields[2];
//Add the field to page axis
field.Axis = PivotAxisTypes.Page;
fields[1].Axis = PivotAxisTypes.None;
fields[0].Axis = PivotAxisTypes.None;
fields[3].Axis = PivotAxisTypes.Row;
fields[4].Axis = PivotAxisTypes.Column;
//Accessing the Calculated fields from the pivot table .
IPivotCalculatedFields calculatedfields = pivotTable.CalculatedFields;
IPivotField dataField = fields[5];
//Adding Calculatd field to the pivot table.
// IPivotField calculatedField = calculatedfields.Add("Percent", "Units/3000*100");
#if !SyncfusionFramework2_0
if (ckbRowFieldFilter.Checked)
{
if (rdbRCLabelFilter.Checked)
{
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Parent", null);
}
else if (rdbRCValueFilter.Checked)
{
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
}
else
{
pivotTable.Fields[3].Items[0].Visible = false;
}
}
if (ckbColumnFieldFilter.Checked)
{
if (rdbRCLabelFilter.Checked)
{
pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Binder", null);
}
else if (rdbRCValueFilter.Checked)
{
pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
}
else
{
pivotTable.Fields[4].Items[0].Visible = false;
}
}
if (ckbPageFilter.Checked)
{
//'Create Pivot Filter object to apply filter to page Fields
IPivotFilter filterValue = pivotTable.Fields[2].PivotFilters.Add();
//Page Field would be filtered with value 'East'
filterValue.Value1 = "East";
//XlsIO layout the Pivot table like MS Excel
if (!rdbRCValueFilter.Checked)
{
pivotTable.Layout();
}
}
else if (ckbMultiplePageFilter.Checked)
{
pivotTable.Fields[2].Items[0].Visible = false;
}
#endif
#endregion
#region Workbook Save
//Saving the workbook to disk.
worksheet.Range[1, 1, 1, 14].ColumnWidth = 11;
worksheet.SetColumnWidth(1, 15.29);
worksheet.SetColumnWidth(2, 15.29);
workbook.SaveAs("CustomizedPivotTable.xlsx");
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("CustomizedPivotTable.xlsx");
//Exit
this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("MS Excel is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
if (this.rdButtonxlsx.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
else if (this.rdButtonexcel2010.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
else if (this.rdButtonexcel2013.IsChecked.Value)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\PivotTable.xlsx");
// The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[1];
sheet.Activate();
//Access the collection of Pivot Table in the worksheet.
IPivotTables pivotTables = sheet.PivotTables;
//Access the Single pivot table from the collection.
IPivotTable pivotTable = pivotTables[0];
//Access collection of pivot fields from the pivot table.
IPivotFields fields = pivotTable.Fields;
//Access a Pivot field from the collection.
IPivotField field = fields[2];
//Add the field to page axis
field.Axis = PivotAxisTypes.Page;
fields[1].Axis = PivotAxisTypes.None;
fields[0].Axis = PivotAxisTypes.None;
fields[3].Axis = PivotAxisTypes.Row;
fields[4].Axis = PivotAxisTypes.Column;
IPivotField dataField = fields[5];
//Accessing the Calculated fields from the pivot table .
IPivotCalculatedFields calculatedfields = pivotTable.CalculatedFields;
//Adding Calculatd field to the pivot table.
//IPivotField calculatedField = calculatedfields.Add("Percent", "Units/3000*100");
if (chkRowFilter.IsChecked.Value)
{
if (rdbLabelFilter.IsChecked.Value)
{
//Applying label based filter to row field
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Parent", null);
}
else if (rdbValueFilter.IsChecked.Value)
{
//Applying value based filter to row field
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
}
else
{
//Applying multiple item filter to row field
pivotTable.Fields[3].Items[0].Visible = false;
}
}
if (chkColumnFilter.IsChecked.Value)
{
if (rdbLabelFilter.IsChecked.Value)
{
//Applying label based filter to column field
pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Binder", null);
}
else if (rdbValueFilter.IsChecked.Value)
{
//Applying value based filter to column field
pivotTable.Fields[4].PivotFilters.Add(PivotFilterType.ValueGreaterThan, dataField, "100", null);
}
else
{
//Applying multiple item filter to column field
pivotTable.Fields[4].Items[0].Visible = false;
}
}
if (chkPageFilter.IsChecked.Value)
{
//'Create Pivot Filter object to apply filter to page Fields
IPivotFilter filterValue = pivotTable.Fields[2].PivotFilters.Add();
//Page Field would be filtered with value 'East'
filterValue.Value1 = "East";
//XlsIO layout the Pivot table like MS Excel
if (!rdbValueFilter.IsChecked.Value)
{
pivotTable.Layout();
}
}
else if (chkMultiplePageFilter.IsChecked.Value)
{
pivotTable.Fields[2].Items[0].Visible = false;
if (!rdbValueFilter.IsChecked.Value)
{
pivotTable.Layout();
}
}
sheet.Range[1, 1, 1, 14].ColumnWidth = 11;
sheet.SetColumnWidth(1, 15.29);
sheet.SetColumnWidth(2, 15.29);
try
{
//Saving the workbook to disk.
workbook.SaveAs("Sample.xlsx");
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButton.YesNo, MessageBoxImage.Information) == MessageBoxResult.Yes)
{
try
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("Sample.xlsx");
//Exit
this.Close();
}
catch (Win32Exception ex)
{
MessageBox.Show("Excel 2007 is not installed in this system");
Console.WriteLine(ex.ToString());
}
}
else
{
// Exit
this.Close();
}
}
catch
{
MessageBox.Show("Sorry, Excel can't open two workbooks with the same name at the same time.\nPlease close the workbook and try again.", "File is already open", MessageBoxButton.OK);
}
}
