public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
object[,] sourceData = new object[, ] {
{ "Order ID", "Product", "Category", "Amount", "Date", "Country" },
{ 1, "Carrots", "Vegetables", 4270, new DateTime(2018, 1, 6), "United States" },
{ 2, "Broccoli", "Vegetables", 8239, new DateTime(2018, 1, 7), "United Kingdom" },
{ 3, "Banana", "Fruit", 617, new DateTime(2018, 1, 8), "United States" },
{ 4, "Banana", "Fruit", 8384, new DateTime(2018, 1, 10), "Canada" },
{ 5, "Beans", "Vegetables", 2626, new DateTime(2018, 1, 10), "Germany" },
{ 6, "Orange", "Fruit", 3610, new DateTime(2018, 1, 11), "United States" },
{ 7, "Broccoli", "Vegetables", 9062, new DateTime(2018, 1, 11), "Australia" },
{ 8, "Banana", "Fruit", 6906, new DateTime(2018, 1, 16), "New Zealand" },
{ 9, "Apple", "Fruit", 2417, new DateTime(2018, 1, 16), "France" },
{ 10, "Apple", "Fruit", 7431, new DateTime(2018, 1, 16), "Canada" },
{ 11, "Banana", "Fruit", 8250, new DateTime(2018, 1, 16), "Germany" },
{ 12, "Broccoli", "Vegetables", 7012, new DateTime(2018, 1, 18), "United States" },
{ 13, "Carrots", "Vegetables", 1903, new DateTime(2018, 1, 20), "Germany" },
{ 14, "Broccoli", "Vegetables", 2824, new DateTime(2018, 1, 22), "Canada" },
{ 15, "Apple", "Fruit", 6946, new DateTime(2018, 1, 24), "France" },
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F16"].Value = sourceData;
worksheet.Range["A:F"].ColumnWidth = 15;
//Create pivot cache.
IPivotCache pivotcache = workbook.PivotCaches.Create(worksheet.Range["A1:F16"]);
//Create pivot tables.
IPivotTable pivottable1 = worksheet.PivotTables.Add(pivotcache, worksheet.Range["K5"], "pivottable1");
IPivotTable pivottable2 = worksheet.PivotTables.Add(pivotcache, worksheet.Range["N3"], "pivottable2");
worksheet.Range["D2:D16"].NumberFormat = "$#,##0.00";
//Config pivot fields
IPivotField field_product1 = pivottable1.PivotFields[1];
field_product1.Orientation = PivotFieldOrientation.RowField;
IPivotField field_Amount1 = pivottable1.PivotFields[3];
field_Amount1.Orientation = PivotFieldOrientation.DataField;
IPivotField field_product2 = pivottable2.PivotFields[5];
field_product2.Orientation = PivotFieldOrientation.RowField;
IPivotField field_Amount2 = pivottable2.PivotFields[2];
field_Amount2.Orientation = PivotFieldOrientation.DataField;
field_Amount2.Function = ConsolidationFunction.Count;
//create slicer cache, the slicers base the slicer cache just control pivot table1.
ISlicerCache cache = workbook.SlicerCaches.Add(pivottable1, "Product");
ISlicer slicer1 = cache.Slicers.Add(workbook.Worksheets["Sheet1"], "p1", "Product", 30, 550, 100, 200);
//add pivot table2 for slicer cache, the slicers base the slicer cache will control pivot tabl1 and pivot table2.
cache.PivotTables.AddPivotTable(pivottable2);
}
/// <summary>
/// Creates Spreadsheet with Styles and Formatting
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreate_Click(object sender, EventArgs e)
{
#region Worksheet Initialize
//New instance of XlsIO is created.[Equivalent to launching MS 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;
//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 Input file
string inputPath = GetFullTemplatePath("PivotCodeDate.xlsx");
IWorkbook workbook = application.Workbooks.Open(inputPath);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Create Pivot Table
//Access the worksheet to draw pivot table.
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Activate();
//Select the data to add in cache
IPivotCache cache = workbook.PivotCaches.Add(worksheet["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);
#if !SyncfusionFramework2_0
#region Apply RowField Filter
if (ckbRowFieldFilter.Checked)
{
if (rdbRCLabelFilter.Checked)
{
pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.CaptionEqual, null, "East", null);
}
else if (rdbRCValueFilter.Checked)
{
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 (ckbColumnFieldFilter.Checked)
{
if (rdbRCLabelFilter.Checked)
{
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Jones", null);
}
else if (rdbRCValueFilter.Checked)
{
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
#region PageField Filter
if (ckbPageFilter.Checked)
{
//'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 MS Excel
if (!rdbRCValueFilter.Checked)
{
pivotTable.Layout();
}
}
else if (ckbMultiplePageFilter.Checked)
{
pivotTable.Fields[4].Items[1].Visible = false;
pivotTable.Fields[4].Items[2].Visible = false;
if (!rdbRCValueFilter.Checked)
{
pivotTable.Layout();
}
}
#endregion
#endif
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
//Activate the pivot worksheet.
pivotSheet.Activate();
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("PivotTable.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("PivotTable.xlsx");
//Exit
//this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("Excel 2007 is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
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 static void CreatePivotTable(IWorkbook workbook, string layoutOption)
{
// 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:G20"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[0].Axis = PivotAxisTypes.Row;
pivotTable.Fields[1].Axis = PivotAxisTypes.Row;
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
IPivotField field1 = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field1, "Sum of Land Area", PivotSubtotalTypes.Sum);
IPivotField field2 = pivotSheet.PivotTables[0].Fields[6];
pivotTable.DataFields.Add(field2, "Sum of Water Area", PivotSubtotalTypes.Sum);
if (layoutOption == "Outline")
{
pivotTable.Options.RowLayout = PivotTableRowLayout.Outline;
pivotTable.Location = pivotSheet.Range[1, 1, 51, 5];
//Apply Inline formatting to pivot table
IPivotCellFormat cellFormat1 = pivotTable.GetCellFormat("B3:E4");
cellFormat1.BackColorRGB = Color.FromArgb(255, 169, 208, 142);
IPivotCellFormat cellFormat2 = pivotTable.GetCellFormat("B31:E32");
cellFormat2.BackColorRGB = Color.FromArgb(255, 244, 176, 132);
}
else if (layoutOption == "Tabular")
{
pivotTable.Location = pivotSheet.Range[1, 1, 51, 5];
pivotTable.Options.RowLayout = PivotTableRowLayout.Tabular;
//Apply Inline formatting to pivot table
IPivotCellFormat cellFormat1 = pivotTable.GetCellFormat("B2:E2");
cellFormat1.BackColorRGB = Color.FromArgb(255, 169, 208, 142);
IPivotCellFormat cellFormat2 = pivotTable.GetCellFormat("B30:E30");
cellFormat2.BackColorRGB = Color.FromArgb(255, 244, 176, 132);
}
else
{
pivotTable.Location = pivotSheet.Range[1, 1, 51, 3];
//Apply Inline formatting to pivot table
IPivotCellFormat cellFormat1 = pivotTable.GetCellFormat("A3:C4");
cellFormat1.BackColorRGB = Color.FromArgb(255, 169, 208, 142);
IPivotCellFormat cellFormat2 = pivotTable.GetCellFormat("A31:C32");
cellFormat2.BackColorRGB = Color.FromArgb(255, 244, 176, 132);
}
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium9;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
if (pivotTable.Options.RowLayout == PivotTableRowLayout.Compact)
{
pivotSheet.SetColumnWidth(4, 1.0);
pivotSheet.SetColumnWidth(5, 2.0);
pivotSheet.SetColumnWidth(6, 0.5);
pivotSheet.Range[2, 5, 2, 5].CellStyle.Color = Color.FromArgb(255, 169, 208, 142);
pivotSheet.Range[4, 5, 4, 5].CellStyle.Color = Color.FromArgb(255, 244, 176, 132);
pivotSheet.Range[2, 7, 2, 7].Text = "County with largest land area";
pivotSheet.Range[4, 7, 4, 7].Text = "County with smallest land area";
}
else
{
pivotSheet.SetColumnWidth(6, 1.0);
pivotSheet.SetColumnWidth(7, 2.0);
pivotSheet.SetColumnWidth(8, 0.5);
pivotSheet.Range[2, 7, 2, 7].CellStyle.Color = Color.FromArgb(255, 169, 208, 142);
pivotSheet.Range[4, 7, 4, 7].CellStyle.Color = Color.FromArgb(255, 244, 176, 132);
pivotSheet.Range[2, 9, 2, 9].Text = "County with largest land area";
pivotSheet.Range[4, 9, 4, 9].Text = "County with smallest land area";
}
//Activate the pivot sheet.
pivotSheet.Activate();
}
//
// 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 async void btnCreate_Click(object sender, RoutedEventArgs e)
{
#region Setting output location
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "PivotTableCreateSample";
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("PivotTableCreateSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
#endregion
#region Initializing Workbook
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(PivotTable).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.PivotCodeData.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
IWorksheet dataSheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
#endregion
#region Creating Pivot Table
IPivotCache cache = workbook.PivotCaches.Add(dataSheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].Axis = PivotAxisTypes.Row;
pivotTable.Fields[3].Axis = PivotAxisTypes.Column;
IPivotField field1 = pivotSheet.PivotTables[0].Fields[5];
pivotTable.DataFields.Add(field1, "Sum of Units", PivotSubtotalTypes.Sum);
pivotTable.ShowDrillIndicators = true;
pivotTable.RowGrand = true;
pivotTable.DisplayFieldCaptions = true;
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
pivotSheet.Activate();
#region Dynamic source change
#region Adding rows
string accountingFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)";
string dateFormat = "[$-409]d-mmm-yy;@";
dataSheet["A51"].NumberFormat = dateFormat;
dataSheet["A51"].DateTime = DateTime.Parse("5/12/2012", CultureInfo.InvariantCulture);
dataSheet["B51"].Formula = "=TEXT(A51,\"dddd\")";
dataSheet["C51"].Value = "Central";
dataSheet["D51"].Value = "Allan";
dataSheet["E51"].Value = "Binder";
dataSheet["F51"].Number = 87;
dataSheet["G51"].Number = 3.21;
dataSheet["G51"].NumberFormat = accountingFormat;
dataSheet["H51"].Formula = "G51*F51";
dataSheet["H51"].NumberFormat = accountingFormat;
dataSheet["A52"].NumberFormat = dateFormat;
dataSheet["A52"].DateTime = DateTime.Parse("5/15/2012", CultureInfo.InvariantCulture);
dataSheet["B52"].Formula = "=TEXT(A52,\"dddd\")";
dataSheet["C52"].Value = "Central";
dataSheet["D52"].Value = "Andrew";
dataSheet["E52"].Value = "Binder";
dataSheet["F52"].Number = 95;
dataSheet["G52"].Number = 2.48;
dataSheet["G52"].NumberFormat = accountingFormat;
dataSheet["H52"].Formula = "G52*F52";
dataSheet["H52"].NumberFormat = accountingFormat;
dataSheet["A53"].NumberFormat = dateFormat;
dataSheet["A53"].DateTime = DateTime.Parse("5/18/2012", CultureInfo.InvariantCulture);
dataSheet["B53"].Formula = "=TEXT(A53,\"dddd\")";
dataSheet["C53"].Value = "West";
dataSheet["D53"].Value = "Kevin";
dataSheet["E53"].Value = "Binder";
dataSheet["F53"].Number = 68;
dataSheet["G53"].Number = 1.75;
dataSheet["G53"].NumberFormat = accountingFormat;
dataSheet["H53"].Formula = "G53*F53";
dataSheet["H53"].NumberFormat = accountingFormat;
dataSheet["A54"].NumberFormat = dateFormat;
dataSheet["A54"].DateTime = DateTime.Parse("5/21/2012", CultureInfo.InvariantCulture);
dataSheet["B54"].Formula = "=TEXT(A54,\"dddd\")";
dataSheet["C54"].Value = "West";
dataSheet["D54"].Value = "Jack";
dataSheet["E54"].Value = "Binder";
dataSheet["F54"].Number = 19;
dataSheet["G54"].Number = 1.01;
dataSheet["G54"].NumberFormat = accountingFormat;
dataSheet["H54"].Formula = "G54*F54";
dataSheet["H54"].NumberFormat = accountingFormat;
dataSheet["A55"].NumberFormat = dateFormat;
dataSheet["A55"].DateTime = DateTime.Parse("5/24/2012", CultureInfo.InvariantCulture);
dataSheet["B55"].Formula = "=TEXT(A55,\"dddd\")";
dataSheet["C55"].Value = "East";
dataSheet["D55"].Value = "Allan";
dataSheet["E55"].Value = "File Folder";
dataSheet["F55"].Number = 20;
dataSheet["G55"].Number = 0.28;
dataSheet["G55"].NumberFormat = accountingFormat;
dataSheet["H55"].Formula = "G55*F55";
dataSheet["H55"].NumberFormat = accountingFormat;
dataSheet["A56"].NumberFormat = dateFormat;
dataSheet["A56"].DateTime = DateTime.Parse("5/27/2012", CultureInfo.InvariantCulture);
dataSheet["B56"].Formula = "=TEXT(A56,\"dddd\")";
dataSheet["C56"].Value = "East";
dataSheet["D56"].Value = "Jack";
dataSheet["E56"].Value = "File Folder";
dataSheet["F56"].Number = 32;
dataSheet["G56"].Number = 0.45;
dataSheet["G56"].NumberFormat = accountingFormat;
dataSheet["H56"].Formula = "G56*F56";
dataSheet["H56"].NumberFormat = accountingFormat;
dataSheet["A57"].NumberFormat = dateFormat;
dataSheet["A57"].DateTime = DateTime.Parse("5/30/2012", CultureInfo.InvariantCulture);
dataSheet["B57"].Formula = "=TEXT(A57,\"dddd\")";
dataSheet["C57"].Value = "West";
dataSheet["D57"].Value = "Kevin";
dataSheet["E57"].Value = "Binder";
dataSheet["F57"].Number = 23;
dataSheet["G57"].Number = 1.19;
dataSheet["G57"].NumberFormat = accountingFormat;
dataSheet["H57"].Formula = "G57*F57";
dataSheet["H57"].NumberFormat = accountingFormat;
dataSheet["A58"].NumberFormat = dateFormat;
dataSheet["A58"].DateTime = DateTime.Parse("6/2/2012", CultureInfo.InvariantCulture);
dataSheet["B58"].Formula = "=TEXT(A58,\"dddd\")";
dataSheet["C58"].Value = "West";
dataSheet["D58"].Value = "Andrew";
dataSheet["E58"].Value = "Binder";
dataSheet["F58"].Number = 43;
dataSheet["G58"].Number = 1.92;
dataSheet["G58"].NumberFormat = accountingFormat;
dataSheet["H58"].Formula = "G58*F58";
dataSheet["H58"].NumberFormat = accountingFormat;
#endregion
//Adding new named range to the workbook
IName name = workbook.Names.Add("DynamicRange", dataSheet.UsedRange);
//Setting pivot table source range from named range
workbook.PivotCaches[0].SourceRange = name.RefersToRange;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
pivotSheet.SetColumnWidth(14, 10.43);
#endregion
#endregion
#region Saving workbook and disposing objects
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Save accknowledgement and Launching of output file
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
#endregion
}
private void Button_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS 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;
//Set the default version as Excel 2007;
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\PivotCodeDate.xlsx");
// 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[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].Axis = PivotAxisTypes.Page;
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);
if (chkRowFilter.IsChecked.Value)
{
if (rdbLabelFilter.IsChecked.Value)
{
//Applying label based filter to row field
pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.CaptionEqual, null, "East", null);
}
else if (rdbValueFilter.IsChecked.Value)
{
//Applying value based filter to row field
pivotTable.Fields[2].PivotFilters.Add(PivotFilterType.ValueEqual, field, "1341", null);
}
else
{
//Applying multiple item filter to row field
pivotTable.Fields[2].Items[0].Visible = false;
pivotTable.Fields[2].Items[1].Visible = false;
}
}
if (chkColumnFilter.IsChecked.Value)
{
if (rdbLabelFilter.IsChecked.Value)
{
//Applying label based filter to row field
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.CaptionNotEqual, null, "Jones", null);
}
else if (rdbValueFilter.IsChecked.Value)
{
//Applying value based filter to column field
pivotTable.Fields[3].PivotFilters.Add(PivotFilterType.ValueEqual, field, "398", null);
}
else
{
//Applying multiple item filter to Column field
pivotTable.Fields[3].Items[0].Visible = false;
pivotTable.Fields[3].Items[1].Visible = false;
}
}
if (chkPageFilter.IsChecked.Value)
{
//Create Pivot Filter object to apply filter to page Fields
IPivotFilter filterValue = pivotTable.Fields[4].PivotFilters.Add();
//Page Field would be filtered with value 'East'
filterValue.Value1 = "Binder";
if (!rdbValueFilter.IsChecked.Value)
{
//XlsIO layout the Pivot table like MS Excel
pivotTable.Layout();
}
}
else if (chkMultiplePageFilter.IsChecked.Value)
{
//Applying multiple item filter page field
pivotTable.Fields[4].Items[1].Visible = false;
pivotTable.Fields[4].Items[2].Visible = false;
if (!rdbValueFilter.IsChecked.Value)
{
pivotTable.Layout();
}
}
//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
{
//Saving the workbook to disk.
workbook.SaveAs("PivotTable.xlsx");
//Close the workbook.
workbook.Close();
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("PivotTable.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);
}
}
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);
}
}
/// <summary>
/// Creates Spreadsheet with Styles and Formatting
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreate_Click(object sender, EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS 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;
//Set the default version as Excel 2007;
if (rdbExcel2007.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2007;
}
//Set the default version as Excel 2010;
else if (rdbExcel2010.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2010;
}
else if (rdbExcel2013.Checked)
{
application.DefaultVersion = ExcelVersion.Excel2013;
}
//Get the path of the input file
string inputPath = GetFullTemplatePath("PivotCodeDate.xlsx");
IWorkbook workbook = application.Workbooks.Open(inputPath);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Add Pivot Table
//Access the worksheet to draw pivot table.
IWorksheet pivotSheet = workbook.Worksheets[1];
//Select the data to add in cache
IPivotCache cache = workbook.PivotCaches.Add(worksheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].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);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//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);
#endregion
#region Add Pivot Chart
//Add the Pivot chart worksheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
//Activate the pivot worksheet.
pivotChartSheet.Activate();
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("PivotChart.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("PivotChart.xlsx");
//Exit
this.Close();
}
catch (Win32Exception)
{
MessageBox.Show("MS Excel is not installed in this system");
}
}
else
{
// Exit
this.Close();
}
#endregion
}
//
// GET: /PivotChart/
public ActionResult PivotChart(string button)
{
if (button == null)
{
return(View());
}
//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 = application.Workbooks.Open(ResolveApplicationDataPath("PivotCodeDate.xlsx"));
workbook.Version = ExcelVersion.Excel2016;
// 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];
//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[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].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);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
//Add the Pivot chart sheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotChartSheet.Activate();
try
{
return(excelEngine.SaveAsActionResult(workbook, "PivotChart.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
private void Button_Click(object sender, RoutedEventArgs e)
{
//New instance of XlsIO is created.[Equivalent to launching MS 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;
//Set the default version as Excel 2007;
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;
}
#if NETCORE
IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\PivotCodeDate.xlsx");
#else
IWorkbook workbook = application.Workbooks.Open(@"..\..\..\..\..\..\Common\Data\XlsIO\PivotCodeDate.xlsx");
#endif
// 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];
//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[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].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);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
//Add the Pivot chart sheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotChartSheet.Activate();
try
{
//Saving the workbook to disk.
workbook.SaveAs("PivotChart.xlsx");
//Close the workbook.
workbook.Close();
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]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("PivotChart.xlsx")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("PivotChart.xlsx");
#endif
//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);
}
}
private async void btnCreate_Click(object sender, RoutedEventArgs e)
{
#region Setting output location
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "PivotChartCreateSample";
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("PivotChartCreateSample.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
#endregion
#region Initializing Workbook
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(PivotChart).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.PivotCodeData.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
IWorksheet dataSheet = workbook.Worksheets[0];
IWorksheet pivotSheet = workbook.Worksheets[1];
#endregion
#region Creating Pivot chart
IPivotCache cache = workbook.PivotCaches.Add(dataSheet["A1:H50"]);
//Insert the pivot table.
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A1"], cache);
pivotTable.Fields[2].Axis = PivotAxisTypes.Row;
pivotTable.Fields[4].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);
//Show expand/collapse button.
pivotTable.ShowDrillIndicators = true;
//Shows row grand.
pivotTable.RowGrand = true;
//Shows filter and field caption.
pivotTable.DisplayFieldCaptions = true;
//Apply built in style.
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium2;
//Add the Pivot chart sheet to the workbook.
IChart pivotChartSheet = workbook.Charts.Add();
pivotChartSheet.Name = "PivotChart";
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Clustered;
pivotSheet.Range[1, 1, 1, 14].ColumnWidth = 11;
pivotSheet.SetColumnWidth(1, 15.29);
pivotSheet.SetColumnWidth(2, 15.29);
//Activate the pivot sheet.
pivotChartSheet.Activate();
#endregion
#region Saving workbook and disposing objects
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Save accknowledgement and Launching of output file
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
#endregion
}
public ActionResult DistInd([Bind(Include = "Region,Province,District,Round,Measure")] requestfilter req)
{
Int16 Region;
Int16 Province;
Int16 District;
Int16 Round;
string Measure;
Region = req.Region;
Province = req.Province;
District = req.District;
Round = req.Round;
Measure = req.Measure;
var data = _context.IndDistircts.Select
(x => new
{
Region = x.Region,
Province = x.Province,
District = x.District,
Year = x.Year,
Month = x.Month,
Round = x.Round,
ShorName = x.ShortName,
Indicator = x.Indicator,
Measure = x.Measure,
Numerator = x.Numerator,
Denominator = x.Denominator,
RegionId = x.RegionId,
ProvinceId = x.ProvinceId,
DistrictId = x.DistrictId,
RoundId = x.RoundId,
CampaignType = x.CampaignType,
Form = x.Form
}
).ToList();
if (!Region.Equals(0) & !Province.Equals(0) & !District.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province) & m.DistrictId.Equals(District)).ToList();
}
else if (!Region.Equals(0) & !Province.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province)).ToList();
}
else if (!Region.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region)).ToList();
}
if (!Round.Equals(0))
{
data = data.Where(m => m.RoundId.Equals(Round)).ToList();
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook;
workbook = application.Workbooks.Create(3);
IWorksheet sheet = workbook.Worksheets[0];
if (!data.Any())
{
return(HttpNotFound());
}
sheet.ImportData(data, 1, 1, true);
sheet.Name = "Data";
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Name = "Percentage";
IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);
//Adding calculated fields to cache.
PivotCacheFieldImpl Result = (cash_data as PivotCacheImpl).CacheFields.AddNewField("Result", "Numerator/Denominator");
PivotCacheFieldImpl Result2 = (cash_data as PivotCacheImpl).CacheFields.AddNewField("Result2", "Numerator*1");
//Percentage indicator - Pivot Table (Percentage)
#region "Percentage Pivot Table"
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);
IPivotField field = pivotTable.Fields["Result"];
pivotTable.DataFields.Add(field, "%", PivotSubtotalTypes.Sum);
pivotTable.Fields["Region"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["District"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Round"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Form"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Indicator"].Axis = PivotAxisTypes.Row;
IPivotField num = pivotTable.Fields["Numerator"];
IPivotField denom = pivotTable.Fields["Denominator"];
if (Measure.Equals("1"))
{
pivotTable.Fields["Region"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by region";
}
else if (Measure.Equals("2"))
{
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by province";
}
else if (Measure.Equals("3"))
{
pivotTable.Fields["District"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by district";
}
else if (Measure.Equals("4"))
{
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by year";
}
else if (Measure.Equals("5"))
{
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by month";
IPivotField pivotField = pivotTable.Fields["Month"];
pivotField.Sort(new string[12] {
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
});
}
pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
pivotSheet.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].CellStyle.Font.Italic = true;
pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
IPivotField pageField = pivotTable.Fields["Measure"];
pageField.Items[0].Visible = false;
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;
pivotOption.ShowGridDropZone = true;
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "NA";
option.ColumnHeaderCaption = "CompareBy";
option.RowHeaderCaption = "Indicator";
#endregion
//Number indicator - Pivot Table (Numeric)
#region "Numeric Pivot Table"
IWorksheet pivotSheet2 = workbook.Worksheets[2];
pivotSheet2.Name = "Numeric";
IPivotTable pivotTable2 = pivotSheet2.PivotTables.Add("PivotTable2", pivotSheet["A5"], cash_data);
IPivotField field2 = pivotTable2.Fields["Result2"];
pivotTable2.DataFields.Add(field2, "#", PivotSubtotalTypes.Sum);
pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["District"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Round"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Form"].Axis = PivotAxisTypes.Page;
pivotTable2.Fields["Indicator"].Axis = PivotAxisTypes.Row;
IPivotField num2 = pivotTable2.Fields["Numerator"];
IPivotField denom2 = pivotTable2.Fields["Denominator"];
if (Measure.Equals("1"))
{
pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by region";
}
else if (Measure.Equals("2"))
{
pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by province";
}
else if (Measure.Equals("3"))
{
pivotTable2.Fields["District"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by district";
}
else if (Measure.Equals("4"))
{
pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by year";
}
else if (Measure.Equals("5"))
{
pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Column;
pivotSheet2["A2"].Text = "Indicators comparison by month";
IPivotField pivotField = pivotTable2.Fields["Month"];
pivotField.Sort(new string[12] {
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
});
}
pivotSheet2.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet2.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet2.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
pivotSheet2.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet2.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet2.Range["A3"].CellStyle.Font.Italic = true;
pivotTable2.Fields["Measure"].Axis = PivotAxisTypes.Page;
IPivotField pageField2 = pivotTable2.Fields["Measure"];
pageField2.Items[1].Visible = false;
pivotTable2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
pivotTable2.ColumnGrand = false;
pivotTable2.RowGrand = false;
PivotTableOptions pivotOption2 = pivotTable2.Options as PivotTableOptions;
pivotOption2.ShowGridDropZone = true;
IPivotTableOptions option2 = pivotTable2.Options;
option2.ErrorString = "NA";
option2.ColumnHeaderCaption = "CompareBy";
option2.RowHeaderCaption = "Indicator";
pivotTable.Fields["Result"].NumberFormat = "#.0%";
pivotTable2.Fields["Result2"].NumberFormat = "#";
(pivotTable.Options as PivotTableOptions).ShowGridDropZone = true;
(pivotTable2.Options as PivotTableOptions).ShowGridDropZone = true;
(pivotTable as PivotTableImpl).Cache.IsRefreshOnLoad = true;
(pivotTable2 as PivotTableImpl).Cache.IsRefreshOnLoad = true;
pivotSheet2.Range["A5"].Activate();
pivotSheet2.Activate();
#endregion
string ContentType = "Application/msexcel";
string filename = "Report_" + "donor" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}
public ActionResult pivotNewInpatients([Bind(Include = "ProvCode,FacilityId,Year")] CreateReq req)
{
string ProvinceCode;
int FacilityId, Year;
ProvinceCode = req.ProvCode;
FacilityId = req.FacilityId;
Year = req.Year;
var data = _context.PvtNewInpatientCases.AsNoTracking().ToList();
if (ProvinceCode != "" && FacilityId != 0 && Year != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Year.Equals(Year) && m.Province.Equals(ProvinceCode) && m.FacilityId.Equals(FacilityId)).ToList();
}
else if (ProvinceCode != "" & FacilityId != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode) && m.FacilityId.Equals(FacilityId)).ToList();
}
else if (ProvinceCode != "" && Year != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode) && m.Year.Equals(Year)).ToList();
}
else if (ProvinceCode != "")
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Province.Equals(ProvinceCode)).ToList();
}
else if (FacilityId != 0 && Year != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.FacilityId.Equals(FacilityId) && m.Year.Equals(Year)).ToList();
}
else if (FacilityId != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.FacilityId.Equals(FacilityId)).ToList();
}
else if (Year != 0)
{
data = _context.PvtNewInpatientCases.AsNoTracking().Where(m => m.Year.Equals(Year)).ToList();
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
sheet.ImportData(data, 1, 1, true);
sheet.Name = "Data";
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Name = "PivotTable";
pivotSheet["A2"].Text = "HMIR New Inpatient Cases";
pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
pivotSheet.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].CellStyle.Font.Italic = true;
if (sheet == null)
{
ModelState.AddModelError("Error", "There was error in selection or there is no data for the selected criteria");
return(RedirectToAction("Index"));
}
IPivotCache cash_data = workbook.PivotCaches.Add(sheet.UsedRange);
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);
IPivotTableOptions options = pivotTable.Options;
options.ShowFieldList = false;
pivotTable.Fields["District"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["FacilityId"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["FacilityName"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["FacilityType"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["ElementName"].Axis = PivotAxisTypes.Row;
IPivotField FemaleOver5 = pivotTable.Fields["FemaleOver5"];
IPivotField FemaleUnder5 = pivotTable.Fields["FemaleUnder5"];
IPivotField MaleOver5 = pivotTable.Fields["MaleOver5"];
IPivotField MaleUnder5 = pivotTable.Fields["MaleUnder5"];
IPivotField ReferreddIn = pivotTable.Fields["ReferredIn"];
IPivotField ReferredOut = pivotTable.Fields["ReferredOut"];
IPivotField Deaths = pivotTable.Fields["Deaths"];
IPivotField Total = pivotTable.Fields["Total"];
pivotTable.DataFields.Add(FemaleOver5, "FO5", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(FemaleUnder5, "FU5", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(MaleOver5, "MO5", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(MaleUnder5, "MU5", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(ReferreddIn, "ReferIns", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(ReferredOut, "ReferOuts", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(Deaths, "Deaths", PivotSubtotalTypes.Sum);
pivotTable.DataFields.Add(Total, "Total", PivotSubtotalTypes.Sum);
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "X";
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark20;
pivotSheet.Activate();
string ContentType = "Application/msexcel";
string filename = "HMIR_" + "NewInpatientCases" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}
public ActionResult CreateChart()
{
var data = _context.lqasLust.Select
(x => new
{
Region = x.Region,
Province = x.Province,
District = x.District,
Year = x.Year,
Month = x.Month,
Round = x.Round,
MonthName = x.MonthName,
Comments = x.Comments,
LotNo = x.LotNo
}
).Where(m => m.Month < 5).ToList();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook templateWorkbook;
templateWorkbook = application.Workbooks.Open(Server.MapPath("/App_Data/book1.xlsx"));
IWorksheet sheet = templateWorkbook.Worksheets[0];
IWorkbook workbook;
workbook = application.Workbooks.Create(3);
IWorksheet worksheet = workbook.Worksheets[0];
if (!data.Any())
{
return(HttpNotFound());
}
sheet.ImportData(data, 1, 1, true);
sheet.Name = "Data";
IWorksheet pivotSheet = templateWorkbook.Worksheets[1];
pivotSheet.Name = "Pivot";
IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);
//Percentage indicator - Pivot Table (Percentage)
#region "Percentage Pivot Table"
IWorksheet pivotTableSheet = templateWorkbook.Worksheets[1];
IPivotTable pivotTable = pivotTableSheet.PivotTables[0];
IPivotField pivotField = pivotTable.Fields["MonthName"];
pivotField.Sort(new string[12] {
"January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
});
IChartShape pivotChartSheet = templateWorkbook.Worksheets[2].Charts[0];
//Set the Pivot source for the Pivot Chart.
pivotChartSheet.PivotSource = pivotTable;
//Set the Pivot Chart Type.
pivotChartSheet.PivotChartType = ExcelChartType.Column_Stacked_100;
pivotChartSheet.Series[0].SerieFormat.CommonSerieOptions.Overlap = 100;
pivotChartSheet.Series[0].SerieFormat.CommonSerieOptions.GapWidth = 10;
pivotChartSheet.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
pivotChartSheet.Series[1].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
pivotChartSheet.Series[2].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;
pivotChartSheet.Series[0].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.LightGreen;
pivotChartSheet.Series[1].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Green;
pivotChartSheet.Series[2].SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Red;
pivotChartSheet.TopRow = 2;
pivotChartSheet.BottomRow = 30;
pivotChartSheet.LeftColumn = 1;
pivotChartSheet.RightColumn = 20;
pivotChartSheet.ChartTitle = "This is a sample chart";
pivotChartSheet.ShowAllFieldButtons = false;
(pivotTable as PivotTableImpl).Cache.SourceRange = sheet.UsedRange;
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;
pivotOption.ShowGridDropZone = true;
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "NA";
#endregion
(pivotTable.Options as PivotTableOptions).ShowGridDropZone = true;
(pivotTable as PivotTableImpl).Cache.IsRefreshOnLoad = true;
IWorksheet pivotChart = templateWorkbook.Worksheets[2];
pivotChart.Name = "Chart";
pivotChart.Activate();
string ContentType = "Application/msexcel";
string filename = "Report_" + "my" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";
MemoryStream ms = new MemoryStream();
templateWorkbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}
public ActionResult DistInd([Bind(Include = "Region,Province,District,Round,Measure,Filter")] requestfilter req)
{
Int16 Region;
Int16 Province;
Int16 District;
Int16 Round;
string Measure;
string Filter;
Region = req.Region;
Province = req.Province;
District = req.District;
Round = req.Round;
Measure = req.Measure;
Filter = req.Filter;
var data = _context.IndDistircts.Select
(x => new
{
Region = x.Region,
Province = x.Province,
District = x.District,
Year = x.Year,
Month = x.Month,
Round = x.Round,
ShorName = x.ShortName,
Indicator = x.Indicator,
Measure = x.Measure,
Numerator = x.Numerator,
Denominator = x.Denominator,
RegionId = x.RegionId,
ProvinceId = x.ProvinceId,
DistrictId = x.DistrictId,
RoundId = x.RoundId,
CampaignType = x.CampaignType,
Form = x.Form
}
).ToList() /*.Where(m=>m.Measure.Equals(Measure) & m.Denominator>0)*/;
if (!Region.Equals(0) & !Province.Equals(0) & !District.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province) & m.DistrictId.Equals(District)).ToList();
}
else if (!Region.Equals(0) & !Province.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region) & m.ProvinceId.Equals(Province)).ToList();
}
else if (!Region.Equals(0))
{
data = data.Where(m => m.RegionId.Equals(Region)).ToList();
}
if (!Round.Equals(0))
{
data = data.Where(m => m.RoundId.Equals(Round)).ToList();
}
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook;
workbook = application.Workbooks.Create(2);
IWorksheet sheet = workbook.Worksheets[0];
if (!data.Any())
{
return(HttpNotFound());
}
sheet.ImportData(data, 1, 1, true);
sheet.Name = "Data";
IWorksheet pivotSheet = workbook.Worksheets[1];
pivotSheet.Name = "Result";
IPivotCache cash_data = workbook.PivotCaches.Add(sheet[sheet.Range.AddressLocal]);
//Percentage indicator
IPivotTable pivotTable = pivotSheet.PivotTables.Add("PivotTable1", pivotSheet["A5"], cash_data);
//IPivotTableOptions options = pivotTable.Options;
pivotTable.Fields["Region"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["District"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Round"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Form"].Axis = PivotAxisTypes.Page;
pivotTable.Fields["Indicator"].Axis = PivotAxisTypes.Row;
IPivotField num = pivotTable.Fields["Numerator"];
IPivotField denom = pivotTable.Fields["Denominator"];
if (Measure.Equals("1"))
{
pivotTable.Fields["Region"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by region";
}
else if (Measure.Equals("2"))
{
pivotTable.Fields["Province"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by province";
}
else if (Measure.Equals("3"))
{
pivotTable.Fields["District"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by district";
}
else if (Measure.Equals("4"))
{
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by year";
}
else if (Measure.Equals("5"))
{
pivotTable.Fields["Year"].Axis = PivotAxisTypes.Column;
pivotTable.Fields["Month"].Axis = PivotAxisTypes.Column;
pivotSheet["A2"].Text = "Indicators comparison by month";
IPivotField pivotField = pivotTable.Fields["Month"];
pivotField.Sort(new string[1] {
pivotField.Items[1].Text
});
}
pivotSheet.Range["A2"].CellStyle.Font.Size = 14f;
pivotSheet.Range["A2"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString();;
pivotSheet.Range["A3"].CellStyle.Font.Size = 10f;
pivotSheet.Range["A3"].CellStyle.Font.Bold = true;
pivotSheet.Range["A3"].CellStyle.Font.Italic = true;
pivotTable.Fields["Measure"].Axis = PivotAxisTypes.Page;
IPivotField pageField = pivotTable.Fields["Measure"];
pageField.Items[0].Visible = false;
if (Filter == "%")
{
IPivotField Result = pivotTable.CalculatedFields.Add("Result", "Numerator/Denominator");
Result.Name = " Result";
Result.NumberFormat = "#.0%";
}
else if (Filter == "#")
{
IPivotField Result = pivotTable.CalculatedFields.Add("Result", "Numerator*1");
Result.Name = " Result";
Result.NumberFormat = "#";
}
//IPivotField pivotField = pivotTable.Fields[0];
//pivotField.Sort(new string[1] { pivotField.Items[4].Text });
pivotTable.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
pivotTable.ColumnGrand = false;
pivotTable.RowGrand = false;
PivotTableOptions pivotOption = pivotTable.Options as PivotTableOptions;
pivotOption.ShowGridDropZone = true;
IPivotTableOptions option = pivotTable.Options;
option.ErrorString = "NA";
option.ColumnHeaderCaption = "CompareBy";
option.RowHeaderCaption = "Indicator";
pivotSheet.Activate();
//else if (Filter == "#")
//{
// //Number indicator
// IWorksheet pivotSheet2 = workbook.Worksheets[1];
// pivotSheet2.Name = "Numeric";
// IPivotTable pivotTable2 = pivotSheet2.PivotTables.Add("PivotTable2", pivotSheet["A5"], cash_data);
// //IPivotTableOptions options2 = pivotTable2.Options;
// //options2.ShowFieldList = true;
// pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["District"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["CampaignType"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Round"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Form"].Axis = PivotAxisTypes.Page;
// pivotTable2.Fields["Indicator"].Axis = PivotAxisTypes.Row;
// IPivotField num2 = pivotTable2.Fields["Numerator"];
// IPivotField denom2 = pivotTable2.Fields["Denominator"];
// if (Measure.Equals("1"))
// {
// pivotTable2.Fields["Region"].Axis = PivotAxisTypes.Column;
// pivotSheet2["A2"].Text = "Indicators comparison by region";
// }
// else if (Measure.Equals("2"))
// {
// pivotTable2.Fields["Province"].Axis = PivotAxisTypes.Page;
// pivotSheet2["A2"].Text = "Indicators comparison by province";
// }
// else if (Measure.Equals("3"))
// {
// pivotTable2.Fields["District"].Axis = PivotAxisTypes.Page;
// pivotSheet2["A2"].Text = "Indicators comparison by district";
// }
// else if (Measure.Equals("4"))
// {
// pivotTable2.Fields["Year"].Axis = PivotAxisTypes.Page;
// pivotSheet2["A2"].Text = "Indicators comparison by year";
// }
// else if (Measure.Equals("5"))
// {
// pivotTable2.Fields["Month"].Axis = PivotAxisTypes.Page;
// pivotSheet2["A2"].Text = "Indicators comparison by month";
// }
// pivotSheet2.Range["A2"].CellStyle.Font.Size = 14f;
// pivotSheet2.Range["A2"].CellStyle.Font.Bold = true;
// pivotSheet2.Range["A3"].Text = "Date extracted: " + DateTime.Now.ToString(); ;
// pivotSheet2.Range["A3"].CellStyle.Font.Size = 10f;
// pivotSheet2.Range["A3"].CellStyle.Font.Bold = true;
// pivotSheet2.Range["A3"].CellStyle.Font.Italic = true;
// IPivotField result2 = pivotTable2.CalculatedFields.Add("Result2", "Numerator*1");
// result2.Name = " #";
// result2.NumberFormat = "#";
// pivotTable2.Fields["Measure"].Axis = PivotAxisTypes.Page;
// IPivotField pageField2 = pivotTable2.Fields["Measure"];
// pageField2.Items[1].Visible = false;
// pivotTable2.BuiltInStyle = PivotBuiltInStyles.PivotStyleDark7;
// pivotTable2.ColumnGrand = false;
// pivotTable2.RowGrand = false;
// PivotTableOptions pivotOption2 = pivotTable2.Options as PivotTableOptions;
// pivotOption2.ShowGridDropZone = true;
// IPivotTableOptions option2 = pivotTable2.Options;
// option2.ErrorString = "NA";
// option2.ColumnHeaderCaption = "CompareBy";
// option2.RowHeaderCaption = "Indicator";
// pivotSheet2.Range["A5"].Activate();
// pivotSheet2.Activate();
//}
string ContentType = "Application/msexcel";
string filename = "NEOC_" + "Indicators" + "_" + DateTime.Now.Year + "_" + DateTime.Now.Month + "_" + DateTime.Now.Day + ".xlsx";
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
workbook.Close();
excelEngine.Dispose();
return(File(ms, ContentType, filename));
}