public override void Execute(GrapeCity.Documents.Excel.Workbook workbook) { IWorksheet worksheet = workbook.Worksheets.Add(); //Activate new created worksheet. worksheet.Activate(); }
private async void addNewFieldBtn_Click(object sender, EventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; IWorkbook workbook = application.Workbooks.Create(1); IWorksheet namedSheet = workbook.Worksheets.Create("Processed Data"); spreadsheet1.Open(workbook); for (int i = 0; i < 10000; i++) { namedSheet[$"A{i + 1}"].Text = 5 * (i + 1) + ""; namedSheet.Activate(); await Task.Delay(200); } }
/// <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); } }
// // GET: /CallCenterDashboard/ public ActionResult CallCenterDashboard(string button) { if (button == null) { return(View()); } else if (button == "Input Template") { //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; application.EnablePartialTrustCode = true; IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"CallCenterTemplate.xlsx")); return(excelEngine.SaveAsActionResult(workbook, "Template.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } else { #region Workbook Initialize //Initialize the spreadsheet creation engine ExcelEngine excelEngine = new ExcelEngine(); //Initialize the Excel application object IApplication application = excelEngine.Excel; //Set the default application version application.DefaultVersion = ExcelVersion.Excel2016; //Enable the incremental formula application.EnableIncrementalFormula = true; //Load the existing Excel document into IWorkbook IWorkbook workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"CallCenterTemplate.xlsx")); #endregion #region Calculation Sheet //Access the Calculation sheet IWorksheet calculation = workbook.Worksheets["Calculation"]; //Formula calculation is enabled for the sheet calculation.EnableSheetCalculations(); calculation.Range["A1"].Text = "Week"; calculation.Range["B1"].Text = "Sorting Control"; calculation.Range["D2"].NumberFormat = "dd-mmm-yyyy"; calculation.Range["D2"].Formula = "=DATE(2016,1,4)+7*(A2-1)"; calculation.Range["E2"].Formula = "=\"Week # \"&A2"; #region Call data in the week calculation.Range["A4"].Text = "Total Calls"; calculation.Range["B4"].Formula = "=COUNTIF(Data[Column1],TRUE)"; calculation.Range["A5"].Text = "Calls Answered"; calculation.Range["B5"].Formula = "=SUMPRODUCT((Data[Answered (Y/N)]=\"Y\")*(Data[Column1]=TRUE))"; calculation.Range["A6"].Text = "Avg Speed of Answer"; calculation.Range["B6"].Formula = "=SUMPRODUCT((Data[Speed of Answer]),--(Data[Column1]=TRUE))/B4"; calculation.Range["A7"].Text = "Abandon Rate"; calculation.Range["B7"].Formula = "=SUMPRODUCT((Data[Answered (Y/N)]=\"N\")*(Data[Column1]=TRUE))/B4"; calculation.Range["A8"].Text = "Avg Call/Min"; calculation.Range["B8"].Formula = "=B4/(7*9*60)"; calculation.Range["A9"].Text = "Satisfaction Overall"; calculation.Range["B9"].Formula = "=SUMPRODUCT((Data[Satisfaction rating]),--(Data[Column1]=TRUE))/B5"; calculation.Range["A10"].Text = "Calls of Less than 180 Seconds"; calculation.Range["B10"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Answered (Y/N)]=\"Y\")*(Data[AvgTalkDuration]<TIME(0,3,0)))"; calculation.Range["A11"].Text = "% Calls of Less than 180 Seconds"; calculation.Range["B11"].Formula = "=B10/B5"; calculation.Range["A12"].Text = "Satisfaction less than equal to 3"; calculation.Range["B12"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Date]<D2+6)*(Data[Satisfaction rating]<=3))"; #endregion #region Call data of each agent calculation.Range["A15"].Text = "Agent Name"; calculation.Range["B15"].Text = "Total Calls"; calculation.Range["C15"].Text = "Calls Answered"; calculation.Range["D15"].Text = "Avg Speed of Answer"; calculation.Range["E15"].Text = "Call Resolution %"; calculation.Range["F15"].Text = "Call Resolved"; calculation.Range["H15"].Text = "For Sorting"; calculation.Range["M15"].Text = "Total Calls"; calculation.Range["N15"].Text = "Calls Answered"; calculation.Range["O15"].Text = "Avg Speed of Answer"; calculation.Range["P15"].Text = "Call Resolution (%)"; calculation.Range["A16"].Text = "Diane"; calculation.Range["A17"].Text = "Becky"; calculation.Range["A18"].Text = "Stewart"; calculation.Range["A19"].Text = "Greg"; calculation.Range["A20"].Text = "Jim"; calculation.Range["A21"].Text = "Joe"; calculation.Range["A22"].Text = "Martha"; calculation.Range["A23"].Text = "Dan"; calculation.Range["B16:B23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16))"; calculation.Range["C16:C23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Answered (Y/N)]=\"Y\"))"; calculation.Range["D16:D23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16),(Data[Speed of Answer]))/C16"; calculation.Range["E16:E23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Resolved]=\"Y\"))/B16"; calculation.Range["F16:F23"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=A16)*(Data[Resolved]=\"Y\"))"; calculation.Range["H16:H23"].Formula = "=INDEX($B$16:$E$23,ROWS($G$16:G16),$B$2)"; calculation.Range["I16:I23"].Formula = "=H16+ROWS($H$16:H16)/1000000"; calculation.Range["J16:J23"].Formula = "=IF($B$2=3,SMALL($I$16:$I$23,ROWS($I$16:I16)),LARGE($I$16:$I$23,ROWS($I$16:I16)))"; calculation.Range["K16:K23"].Formula = "=MATCH(J16,$I$16:$I$23,0)"; calculation.Range["L16:L23"].Formula = "=INDEX($A$16:$A$23,K16)"; calculation.Range["M16:M23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:L16))"; calculation.Range["N16:N23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:M16))"; calculation.Range["O16:O23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:N16))"; calculation.Range["P16:P23"].Formula = "=INDEX($A$16:$E$23,MATCH($L16,$A$16:$A$23,0),COLUMNS($K$14:O16))"; #endregion #region Overall Satisfaction chart calculation.Range["A25"].Text = "Satisfaction Chart"; calculation.Range["A26"].Number = 50; calculation.Range["A27"].Number = 20; calculation.Range["A28"].Number = 30; calculation.Range["A29"].Number = 100; calculation.Range["B26"].Formula = "=B9*20-2"; calculation.Range["B27"].Number = 2; calculation.Range["B28"].Formula = "=200-B26"; #endregion #region call data of each agent per day calculation.Range["A32"].Text = "Agent Name"; calculation.Range["B32"].Text = "Mon"; calculation.Range["C32"].Text = "Tue"; calculation.Range["D32"].Text = "Wed"; calculation.Range["E32"].Text = "Thu"; calculation.Range["F32"].Text = "Fri"; calculation.Range["G32"].Text = "Sat"; calculation.Range["H32"].Text = "Sun"; calculation.Range["A33:A40"].Formula = "=L16"; calculation.Range["B33:B40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:A33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["C33:C40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:B33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["D33:D40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:C33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["E33:E40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:D33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["F33:F40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:E33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["G33:G40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:F33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; calculation.Range["H33:H40"].Formula = "=SUMPRODUCT((INT(Data[Date])=($D$2+COLUMNS($A$33:G33)-1))*(Data[Agent]=$A33)*(Data[Resolved]=\"Y\"))"; #endregion #region Satisfaction score for each agent calculation.Range["A43"].Text = "Agent Name"; calculation.Range["B43"].Text = "Satisfaction Score"; calculation.Range["C43"].Text = "Target"; calculation.Range["A44:A51"].Formula = "=A33"; calculation.Range["B44:B51"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Agent]=$A44),(Data[Satisfaction rating]))/N16"; calculation.Range["C44:C51"].Number = 3.5; calculation.Range["D44:D51"].Formula = "=IF(B44>C44,$A$52&\" \"&A44,A44)"; calculation.Range["E44:E51"].Formula = "=B44"; #endregion #region call data for each product calculation.Range["B54"].Text = "Total Cell"; calculation.Range["C54"].Text = "Call Answered"; calculation.Range["D54"].Text = "Abandoned Calls %"; calculation.Range["E54"].Text = "SLA Limit"; calculation.Range["F54"].Text = "SLA Breached"; calculation.Range["A55"].Text = "Washing Machine"; calculation.Range["A56"].Text = "Toaster"; calculation.Range["A57"].Text = "Fridge"; calculation.Range["A58"].Text = "Air Conditioner"; calculation.Range["A59"].Text = "Television"; calculation.Range["B55:B59"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Department]=A55))"; calculation.Range["C55:C59"].Formula = "=SUMPRODUCT((Data[Column1]=TRUE)*(Data[Department]=A55)*(Data[Answered (Y/N)]=\"Y\"))"; calculation.Range["D55:D59"].Formula = "=(B55-C55)/B55"; calculation.Range["E55:E59"].NumberFormat = "0%"; calculation.Range["E55:E59"].Value = "20%"; calculation.Range["F55:F59"].Formula = "=IF(D55>E55,D55,NA())"; #endregion //Formula calculation is disabled for the sheet calculation.DisableSheetCalculations(); #endregion #region Dashboard Sheet //Create Dashboard sheet IWorksheet dashboard = workbook.Worksheets.Create("Dashboard"); dashboard.Range["A1"].ColumnWidth = 0.5; dashboard.Range["A1"].RowHeight = 30; dashboard.Range["I1"].ColumnWidth = 0.5; #region Marcos for selecting the week and sort option //Cell Style for B1 to R1 dashboard.Range["B1:R1"].CellStyle.Color = Color.FromArgb(48, 13, 225); //Cell Style and text in B6 - Click to Sort dashboard.Range["B6:B7"].Merge(); dashboard.Range["B6"].Text = "Click to Sort"; dashboard.Range["B6"].CellStyle.Font.RGBColor = Color.FromArgb(48, 13, 225); dashboard.Range["B6"].CellStyle.Font.Italic = true; dashboard.Range["B6"].HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["B6"].VerticalAlignment = ExcelVAlign.VAlignCenter; //Macros for Scroll Bar and Option Buttons IVbaProject project = workbook.VbaProject; IVbaModules vbaModules = project.Modules; IVbaModule scrollBar = vbaModules.Add("scrollBar", VbaModuleType.StdModule); scrollBar.Code = "Sub Auto_Open()" + "\n Dim Worksheet_Name As String" + "\n Worksheet_Name = \"Dashboard\"" + "\n ThisWorkbook.Worksheets(Worksheet_Name).Select" + "\n ThisWorkbook.Worksheets(Worksheet_Name).ScrollBars.Add(12, 3.5, 96.5, 20).Select" + "\n With Selection" + "\n .Value = 0" + "\n .Min = 1" + "\n .Max = 4" + "\n .SmallChange = 1" + "\n .LargeChange = 10" + "\n .LinkedCell = \"=Calculation!A2\"" + "\n .Display3DShading = True" + "\n End With" + "\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(98.5, 97, 63, 17.5).Select" + "\n Selection.Characters.Text = \"\"" + "\n With Selection" + "\n .Value = xlOn" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" + "\n End With" + "\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(166, 97, 63, 17.5).Select" + "\n Selection.Characters.Text = \"\"" + "\n With Selection" + "\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" + "\n End With" + "\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(270, 97, 63, 17.5).Select" + "\n Selection.Characters.Text = \"\"" + "\n With Selection" + "\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" + "\n End With" + "\n ThisWorkbook.Worksheets(Worksheet_Name).OptionButtons.Add(380, 97, 63, 17.5).Select" + "\n Selection.Characters.Text = \"\"" + "\n With Selection" + "\n .Value = xlOff" + "\n .LinkedCell = \"=Calculation!B2\"" + "\n .Display3DShading = False" + "\n End With" + "\n End Sub"; #endregion #region Week display //Week Selected dashboard.Range["D1"].Formula = "=Calculation!E2"; dashboard.Range["D1"].CellStyle.Font.Bold = true; dashboard.Range["D1"].CellStyle.Font.Italic = true; dashboard.Range["D1"].CellStyle.Font.Size = 16; dashboard.Range["D1"].CellStyle.Font.RGBColor = Color.FromArgb(225, 225, 225); dashboard.Range["D1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["D1"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; #endregion #region Call data overview for the week dashboard.Range["B2:D3"].Merge(); dashboard.Range["B4:D5"].Merge(); dashboard.Range["E2:G3"].Merge(); dashboard.Range["E4:G5"].Merge(); dashboard.Range["H2:M3"].Merge(); dashboard.Range["H4:M5"].Merge(); dashboard.Range["N2:R3"].Merge(); dashboard.Range["N4:R5"].Merge(); dashboard.Range["B2:N4"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["B2:N4"].CellStyle.Font.Bold = true; dashboard.Range["B2:N2"].CellStyle.Font.RGBColor = Color.FromArgb(0, 0, 0); dashboard.Range["B4:N4"].CellStyle.Font.RGBColor = Color.FromArgb(48, 13, 225); dashboard.Range["B2:N2"].CellStyle.Font.Size = 12; dashboard.Range["B4:N4"].CellStyle.Font.Size = 18; dashboard.Range["B2:N4"].HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["B2:N4"].VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["B2:N4"].CellStyle.Color = Color.FromArgb(217, 217, 217); dashboard.Range["B2"].Text = "Total Calls"; dashboard.Range["E2"].Text = "Avg. Answer Speed (in sec)"; //Create a rich text string IRichTextString richText = dashboard.Range["E2"].RichText; //Create fonts for rich text formatting IFont font1 = workbook.CreateFont(); font1.FontName = "Verdana"; font1.RGBColor = Color.FromArgb(0, 0, 0); font1.Bold = true; font1.Size = 12; richText.SetFont(0, 17, font1); IFont font2 = workbook.CreateFont(); font2.FontName = "Verdana"; font2.RGBColor = Color.FromArgb(0, 0, 0); font2.Bold = false; font2.Size = 10; richText.SetFont(18, 25, font2); dashboard.Range["H2"].Text = "Abandon rate"; dashboard.Range["N2"].Text = "Avg Calls/Minute"; dashboard.Range["B4"].Formula = "=Calculation!B4"; dashboard.Range["E4"].NumberFormat = "0.0"; dashboard.Range["E4"].Formula = "=Calculation!B6"; dashboard.Range["H4"].NumberFormat = "0.0%"; dashboard.Range["H4"].Formula = "=Calculation!B7"; dashboard.Range["N4"].NumberFormat = "0.000"; dashboard.Range["N4"].Formula = "=Calculation!B8"; dashboard.Range["B2:D5"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["E2:G5"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["H2:M5"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["N2:R5"].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion #region Table to display call data of each agent dashboard.Range["B8"].Text = "Agent Name"; dashboard.Range["C8"].Text = "Total Calls"; dashboard.Range["D8"].Text = "Calls Answered"; dashboard.Range["E8"].Text = "Avg. Speed of Answer"; dashboard.Range["F8"].Text = "Call Resolution (%)"; dashboard.Range["H8"].Text = "CR Trend"; dashboard.Range["B9:B16"].Formula = "=Calculation!L16"; dashboard.Range["C9:C16"].Formula = "=Calculation!M16"; dashboard.Range["D9:D16"].Formula = "=Calculation!N16"; dashboard.Range["E9:E16"].NumberFormat = "0.0"; dashboard.Range["E9:E16"].Formula = "=Calculation!O16"; dashboard.Range["F9:F16"].NumberFormat = "0.0%"; dashboard.Range["F9:F16"].Formula = "=Calculation!P16"; dashboard.Range["G9:G16"].Formula = "=F9"; #region Conditional formats //Create icon sets for the data in the specified range IConditionalFormats conditionalFormats = dashboard.Range["G9:G16"].ConditionalFormats; IConditionalFormat conditionalFormat = conditionalFormats.AddCondition(); conditionalFormat.FormatType = ExcelCFType.IconSet; IIconSet iconSet = conditionalFormat.IconSet; iconSet.IconSet = ExcelIconSetType.ThreeSigns; //Apply three signs icon and hide the data in the specified range IIconConditionValue iconValue2 = iconSet.IconCriteria[1] as IIconConditionValue; iconValue2.IconSet = ExcelIconSetType.ThreeSigns; iconValue2.Index = 1; iconValue2.Type = ConditionValueType.Number; iconValue2.Value = "0.7"; iconValue2.Operator = ConditionalFormatOperator.GreaterThan; IIconConditionValue iconValue3 = iconSet.IconCriteria[2] as IIconConditionValue; iconValue3.IconSet = ExcelIconSetType.ThreeSigns; iconValue3.Index = 2; iconValue3.Type = ConditionValueType.Number; iconValue3.Value = "0.8"; iconValue3.Operator = ConditionalFormatOperator.GreaterThanorEqualTo; iconSet.ShowIconOnly = true; #endregion //Auto-fit columns dashboard.Range["B8:G16"].AutofitColumns(); dashboard.Range["H8"].ColumnWidth = 11; #region Sparklines //Add sparkline groups ISparklineGroup sparklineGroup = dashboard.SparklineGroups.Add(); sparklineGroup.SparklineType = SparklineType.Line; sparklineGroup.MarkersColor = Color.FromArgb(51, 102, 153); sparklineGroup.LowPointColor = Color.FromArgb(192, 0, 0); sparklineGroup.ShowHighPoint = false; sparklineGroup.ShowFirstPoint = false; sparklineGroup.ShowLastPoint = false; sparklineGroup.ShowMarkers = false; //Add sparklines ISparklines sparklines = sparklineGroup.Add(); IRange dataRange = calculation.Range["B33:H40"]; IRange referenceRange = dashboard.Range["H9:H16"]; sparklines.Add(dataRange, referenceRange); #endregion //Apply range formatting using cellstyle properties dashboard.Range["B8:E16"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["B8:E16"].BorderInside(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F8:G8"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F9:G9"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F10:G10"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F11:G11"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F12:G12"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F13:G13"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F14:G14"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F15:G15"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["F16:G16"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["H8:H16"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["B8:H8"].CellStyle.Font.Bold = true; dashboard.Range["B8:H8"].CellStyle.Font.Italic = true; dashboard.Range["B8:H8"].CellStyle.Color = Color.FromArgb(217, 217, 217); #endregion #region Column Chart - Call Abandon Rate By Department //Column Chart - Chart Title dashboard.Range["J8:R8"].Merge(); dashboard.Range["J8"].CellStyle.Font.Italic = true; dashboard.Range["J8"].CellStyle.Font.Size = 12; dashboard.Range["J8"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127); dashboard.Range["J8"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["J8"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["J8"].Text = "Call Abandon Rate - By Department"; //Column Chart - Call Abandon Rate - By Department IChartShape columnChart = dashboard.Charts.Add(); IChartSerie columnserieOne = columnChart.Series.Add(); columnserieOne.Values = calculation.Range["D55:D59"]; columnserieOne.CategoryLabels = calculation.Range["A55:A59"]; columnserieOne.SerieFormat.CommonSerieOptions.GapWidth = 150; columnserieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; columnChart.PrimaryValueAxis.MajorUnit = 0.1; columnChart.PrimaryValueAxis.NumberFormat = "0.0%"; columnChart.PrimaryValueAxis.HasMajorGridLines = false; columnChart.HasLegend = false; columnChart.ChartArea.Border.LineColor = Color.White; columnChart.Left = 1; columnChart.TopRow = 9; columnChart.LeftColumn = 10; columnChart.BottomRow = 17; columnChart.RightColumn = 19; (columnChart as IChart).Height = (columnChart as IChart).Height - 10; (columnChart as IChart).Width = (columnChart as IChart).Width - 10; dashboard.Range["J8:R16"].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion #region SLA Limits dashboard.Range["J18:R18"].Merge(); dashboard.Range["J21:O23"].Merge(); dashboard.Range["J26:O28"].Merge(); dashboard.Range["P21:R23"].Merge(); dashboard.Range["P26:R28"].Merge(); dashboard.Range["J18"].Text = "SLA LIMITS"; dashboard.Range["J18"].CellStyle.Font.Bold = true; dashboard.Range["J18"].CellStyle.Font.Size = 16; dashboard.Range["J18"].CellStyle.Font.FontName = "Calibri (body)"; dashboard.Range["J18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["J18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["J18"].CellStyle.Font.Color = ExcelKnownColors.Black; dashboard.Range["J18"].CellStyle.Color = Color.FromArgb(217, 217, 217); dashboard.Range["J18:R18"].BorderAround(ExcelLineStyle.Thin, Color.Black); dashboard.Range["J21"].Text = "Calls answered in less than 180 Seconds:"; dashboard.Range["J21"].CellStyle.Font.Bold = true; dashboard.Range["J21"].CellStyle.Font.Size = 14; dashboard.Range["J21"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["J21"].CellStyle.WrapText = true; dashboard.Range["J21"].CellStyle.Font.Color = ExcelKnownColors.Black; dashboard.Range["J21"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["P21"].Formula = "=Calculation!B11"; dashboard.Range["P21"].CellStyle.Font.Bold = true; dashboard.Range["P21"].CellStyle.Font.Size = 28; dashboard.Range["P21"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["P21"].NumberFormat = "0.0%"; dashboard.Range["P21"].CellStyle.Font.RGBColor = Color.FromArgb(84, 130, 53); dashboard.Range["P21"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["P21"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft; dashboard.Range["J26"].Text = "Calls with satisfaction score less than 3:"; dashboard.Range["J26"].CellStyle.Font.Bold = true; dashboard.Range["J26"].CellStyle.Font.Size = 14; dashboard.Range["J26"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["J26"].CellStyle.WrapText = true; dashboard.Range["J26"].CellStyle.Font.Color = ExcelKnownColors.Black; dashboard.Range["J26"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["P26"].Formula = "=Calculation!B12"; dashboard.Range["P26"].CellStyle.Font.Bold = true; dashboard.Range["P26"].CellStyle.Font.Size = 28; dashboard.Range["P26"].CellStyle.Font.FontName = "Verdana"; dashboard.Range["P26"].CellStyle.Font.RGBColor = Color.FromArgb(192, 0, 0); dashboard.Range["P26"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["P26"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft; dashboard.Range["J19:R30"].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion #region Bar Chart - Satisfaction Score By Agent //Bar Chart - Chart Title dashboard.Range["E18:H18"].Merge(); dashboard.Range["E18"].CellStyle.Font.Italic = true; dashboard.Range["E18"].CellStyle.Font.Underline = ExcelUnderline.Single; dashboard.Range["E18"].CellStyle.Font.Size = 12; dashboard.Range["E18"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127); dashboard.Range["E18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["E18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["E18"].Text = "Satisfaction Score - By Agent"; //Bar Chart - Satisfaction Score - By Agent IChartShape barChart = dashboard.Charts.Add(); IChartSerie barSerieOne = barChart.Series.Add(); barSerieOne.SerieType = ExcelChartType.Bar_Clustered; barSerieOne.CategoryLabels = calculation.Range["D44:D51"]; barSerieOne.Values = calculation.Range["E44:E51"]; barChart.PrimaryValueAxis.MinimumValue = 0; barChart.PrimaryValueAxis.MaximumValue = 5; barChart.PrimaryValueAxis.HasMajorGridLines = false; barChart.HasLegend = false; barChart.ChartArea.Border.LineColor = Color.White; barChart.Top = 1; barChart.TopRow = 20; barChart.LeftColumn = 5; barChart.BottomRow = 31; barChart.RightColumn = 9; (barChart as IChart).Height = (barChart as IChart).Height - 10; (barChart as IChart).Width = (barChart as IChart).Width - 10; #endregion #region Doughnut and Pie Chart - Overall Satisfaction Score //Doughnut Chart - Chart Title dashboard.Range["B18:D18"].Merge(); dashboard.Range["B18"].CellStyle.Font.Italic = true; dashboard.Range["B18"].CellStyle.Font.Underline = ExcelUnderline.Single; dashboard.Range["B18"].CellStyle.Font.Size = 12; dashboard.Range["B18"].CellStyle.Font.RGBColor = Color.FromArgb(127, 127, 127); dashboard.Range["B18"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["B18"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["B18"].Text = "Overall Satisfaction Score"; //Doughnut Chart - Overall Satisfaction Score IChartShape doughnutChart = dashboard.Charts.Add(); IChartSerie doughnutSerie = doughnutChart.Series.Add(); doughnutChart.ChartType = ExcelChartType.Doughnut; doughnutSerie.Values = calculation.Range["A26:A29"]; doughnutSerie.SerieFormat.CommonSerieOptions.FirstSliceAngle = 270; doughnutSerie.DataPoints[0].DataFormat.Fill.ForeColor = Color.FromArgb(255, 0, 0); doughnutSerie.DataPoints[1].DataFormat.Fill.ForeColor = Color.FromArgb(255, 192, 0); doughnutSerie.DataPoints[2].DataFormat.Fill.ForeColor = Color.FromArgb(0, 176, 80); doughnutSerie.DataPoints[3].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White; doughnutSerie.DataPoints[3].DataFormat.Fill.Transparency = 1.0; doughnutChart.HasLegend = false; doughnutChart.ChartArea.Border.LineColor = Color.White; doughnutChart.ChartArea.Fill.Transparency = 1.0; doughnutChart.PlotArea.Fill.Transparency = 1.0; doughnutChart.Left = 1; doughnutChart.TopRow = 20; doughnutChart.LeftColumn = 2; doughnutChart.BottomRow = 32; doughnutChart.RightColumn = 5; //Pie Chart - Overall Satisfaction Score IChartShape pieChart = dashboard.Charts.Add(); IChartSerie pieSerieOne = pieChart.Series.Add(); pieSerieOne.SerieType = ExcelChartType.Pie; pieSerieOne.Values = calculation.Range["B26:B28"]; pieSerieOne.SerieFormat.CommonSerieOptions.FirstSliceAngle = 270; pieSerieOne.DataPoints[0].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White; pieSerieOne.DataPoints[0].DataFormat.Fill.Transparency = 1.0; pieSerieOne.DataPoints[1].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.Black; pieSerieOne.DataPoints[1].DataFormat.LineProperties.LineColor = Color.White; pieSerieOne.DataPoints[1].DataFormat.LineProperties.LineWeight = ExcelChartLineWeight.Narrow; pieSerieOne.DataPoints[2].DataFormat.Fill.ForeColorIndex = ExcelKnownColors.White; pieSerieOne.DataPoints[2].DataFormat.Fill.Transparency = 1.0; pieChart.HasLegend = false; pieChart.ChartArea.Border.LineColor = Color.White; pieChart.ChartArea.Fill.Transparency = 1.0; pieChart.PlotArea.Fill.Transparency = 1.0; pieChart.Top = 1; pieChart.Left = 1; pieChart.TopRow = 20; pieChart.LeftColumn = 2; pieChart.BottomRow = 33; pieChart.RightColumn = 5; dashboard.Range["B30:C30"].Merge(); dashboard.Range["B30"].Text = "Satisafction Score:"; dashboard.Range["B30"].HorizontalAlignment = ExcelHAlign.HAlignRight; dashboard.Range["B30"].VerticalAlignment = ExcelVAlign.VAlignCenter; dashboard.Range["D30"].NumberFormat = "0.00"; dashboard.Range["D30"].Formula = "=Calculation!B9"; dashboard.Range["D30"].CellStyle.Font.Size = 18; dashboard.Range["D30"].CellStyle.Font.Bold = true; dashboard.Range["D30"].CellStyle.Font.RGBColor = Color.FromArgb(0, 112, 192); dashboard.Range["D30"].HorizontalAlignment = ExcelHAlign.HAlignCenter; dashboard.Range["B18:H30"].BorderAround(ExcelLineStyle.Thin, Color.Black); #endregion //Disable the gridlines dashboard.IsGridLinesVisible = false; //Set the dashboard sheet as active sheet dashboard.Activate(); #endregion #region Save the Workbook //Save the workbook to disk workbook.Version = ExcelVersion.Excel2016; return(excelEngine.SaveAsActionResult(workbook, "CallCenterDashboard.xlsm", ExcelSaveType.SaveAsMacro, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); #endregion } }
private async void btnGenerateExcel_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 = "ChartSample"; if (rdBtn2003.IsChecked.Value) { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xls" }); } else { savePicker.FileTypeChoices.Add("Excel Files", new List <string>() { ".xlsx", }); } storageFile = await savePicker.PickSaveFileAsync(); } else { StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder; if (rdBtn2003.IsChecked.Value) { storageFile = await local.CreateFileAsync("ChartSample.xls", CreationCollisionOption.ReplaceExisting); } else { storageFile = await local.CreateFileAsync("ChartSample.xlsx", CreationCollisionOption.ReplaceExisting); } } if (storageFile == null) { return; } #endregion #region Initializing workbook //Instantiate excel Engine ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; if (rdBtn2003.IsChecked.Value) { application.DefaultVersion = ExcelVersion.Excel97to2003; } else { application.DefaultVersion = ExcelVersion.Excel2013; } IWorkbook workbook; if (application.DefaultVersion != ExcelVersion.Excel97to2003) { Assembly assembly = typeof(Chart).GetTypeInfo().Assembly; string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.Sparkline.xlsx"; Stream fileStream = assembly.GetManifestResourceStream(resourcePath); workbook = await application.Workbooks.OpenAsync(fileStream); } else { workbook = application.Workbooks.Create(1); } IWorksheet sheet = workbook.Worksheets[0]; #endregion //#if WINDOWS_APP if (application.DefaultVersion != ExcelVersion.Excel97to2003) { #region Sparklines #region WholeSale Report //A new Sparkline group is added to the sheet sparklinegroups ISparklineGroup sparklineGroup = sheet.SparklineGroups.Add(); //Set the Sparkline group type as line sparklineGroup.SparklineType = SparklineType.Line; //Set to display the empty cell as line sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Line; //Sparkline group style properties sparklineGroup.ShowFirstPoint = true; sparklineGroup.FirstPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B); sparklineGroup.ShowLastPoint = true; sparklineGroup.LastPointColor = Color.FromArgb(Colors.Orange.A, Colors.Orange.R, Colors.Orange.G, Colors.Orange.B); sparklineGroup.ShowHighPoint = true; sparklineGroup.HighPointColor = Color.FromArgb(Colors.Blue.A, Colors.Blue.R, Colors.Blue.G, Colors.Blue.B); sparklineGroup.ShowLowPoint = true; sparklineGroup.LowPointColor = Color.FromArgb(Colors.Purple.A, Colors.Purple.R, Colors.Purple.G, Colors.Purple.B); sparklineGroup.ShowMarkers = true; sparklineGroup.MarkersColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B); sparklineGroup.ShowNegativePoint = true; sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B); //set the line weight sparklineGroup.LineWeight = 0.3; //The sparklines are added to the sparklinegroup. ISparklines sparklines = sparklineGroup.Add(); //Set the Sparkline Datarange . IRange dataRange = sheet.Range["D6:G17"]; //Set the Sparkline Reference range. IRange referenceRange = sheet.Range["H6:H17"]; //Create a sparkline with the datarange and reference range. sparklines.Add(dataRange, referenceRange); #endregion #region Retail Trade //A new Sparkline group is added to the sheet sparklinegroups sparklineGroup = sheet.SparklineGroups.Add(); //Set the Sparkline group type as column sparklineGroup.SparklineType = SparklineType.Column; //Set to display the empty cell as zero sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Zero; //Sparkline group style properties sparklineGroup.ShowHighPoint = true; sparklineGroup.HighPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B); sparklineGroup.ShowLowPoint = true; sparklineGroup.LowPointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B); sparklineGroup.ShowNegativePoint = true; sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B); //The sparklines are added to the sparklinegroup. sparklines = sparklineGroup.Add(); //Set the Sparkline Datarange . dataRange = sheet.Range["D21:G32"]; //Set the Sparkline Reference range. referenceRange = sheet.Range["H21:H32"]; //Create a sparkline with the datarange and reference range. sparklines.Add(dataRange, referenceRange); #endregion #region Manufacturing Trade //A new Sparkline group is added to the sheet sparklinegroups sparklineGroup = sheet.SparklineGroups.Add(); //Set the Sparkline group type as win/loss sparklineGroup.SparklineType = SparklineType.ColumnStacked100; sparklineGroup.DisplayEmptyCellsAs = SparklineEmptyCells.Zero; sparklineGroup.DisplayAxis = true; sparklineGroup.AxisColor = Color.FromArgb(Colors.Black.A, Colors.Black.R, Colors.Black.G, Colors.Black.B); sparklineGroup.ShowFirstPoint = true; sparklineGroup.FirstPointColor = Color.FromArgb(Colors.Green.A, Colors.Green.R, Colors.Green.G, Colors.Green.B); sparklineGroup.ShowLastPoint = true; sparklineGroup.LastPointColor = Color.FromArgb(Colors.Orange.A, Colors.Orange.R, Colors.Orange.G, Colors.Orange.B); sparklineGroup.ShowNegativePoint = true; sparklineGroup.NegativePointColor = Color.FromArgb(Colors.Red.A, Colors.Red.R, Colors.Red.G, Colors.Red.B); sparklines = sparklineGroup.Add(); dataRange = sheet.Range["D36:G46"]; referenceRange = sheet.Range["H36:H46"]; sparklines.Add(dataRange, referenceRange); #endregion #endregion } //#endif #region Creating chart datasource IWorksheet chartSheet; if (application.DefaultVersion != ExcelVersion.Excel97to2003) { chartSheet = workbook.Worksheets.Create("Chart Data"); } else { chartSheet = workbook.Worksheets[0]; } // Entering the Datas for the chart chartSheet.Range["A1"].Text = "Crescent City, CA"; chartSheet.Range["A1:D1"].Merge(); chartSheet.Range["A1"].CellStyle.Font.Bold = true; chartSheet.Range["B3"].Text = "Precipitation,in."; chartSheet.Range["C3"].Text = "Temperature,deg.F"; chartSheet.Range["A4"].Text = "Jan"; chartSheet.Range["A5"].Text = "Feb"; chartSheet.Range["A6"].Text = "March"; chartSheet.Range["A7"].Text = "Apr"; chartSheet.Range["A8"].Text = "May"; chartSheet.Range["A9"].Text = "June"; chartSheet.Range["A10"].Text = "July"; chartSheet.Range["A11"].Text = "Aug"; chartSheet.Range["A12"].Text = "Sept"; chartSheet.Range["A13"].Text = "Oct"; chartSheet.Range["A14"].Text = "Nov"; chartSheet.Range["A15"].Text = "Dec"; chartSheet.Range["B4"].Number = 10.9; chartSheet.Range["B5"].Number = 8.9; chartSheet.Range["B6"].Number = 8.6; chartSheet.Range["B7"].Number = 4.8; chartSheet.Range["B8"].Number = 3.2; chartSheet.Range["B9"].Number = 1.4; chartSheet.Range["B10"].Number = 0.6; chartSheet.Range["B11"].Number = 0.7; chartSheet.Range["B12"].Number = 1.7; chartSheet.Range["B13"].Number = 5.4; chartSheet.Range["B14"].Number = 9.0; chartSheet.Range["B15"].Number = 10.4; chartSheet.Range["C4"].Number = 4.5; chartSheet.Range["C5"].Number = 2.7; chartSheet.Range["C6"].Number = 9.9; chartSheet.Range["C7"].Number = 4.2; chartSheet.Range["C8"].Number = 6.1; chartSheet.Range["C9"].Number = 5.3; chartSheet.Range["C10"].Number = 3.1; chartSheet.Range["C11"].Number = 7; chartSheet.Range["C12"].Number = 4.5; chartSheet.Range["C13"].Number = 8.4; chartSheet.Range["C14"].Number = 3.1; chartSheet.Range["C15"].Number = 8.8; chartSheet.UsedRange.AutofitColumns(); #endregion //#if WINDOWS_APP #region Column Chart #region Creating ChartSheet with Wall implementation // Adding a New chart to the Existing Worksheet IChart chart = workbook.Charts.Add("Column Chart"); chart.DataRange = chartSheet.Range["A3:C15"]; chart.ChartTitle = "Crescent City, CA"; chart.IsSeriesInRows = false; chart.PrimaryValueAxis.Title = "Precipitation,in."; chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90; chart.PrimaryValueAxis.MaximumValue = 14.0; chart.PrimaryValueAxis.NumberFormat = "0.0"; chart.PrimaryCategoryAxis.Title = "Month"; IChartSerie serieOne = chart.Series[0]; //set the Chart Type chart.ChartType = ExcelChartType.Column_Clustered_3D; //set the Backwall fill option chart.BackWall.Fill.FillType = ExcelFillType.Gradient; //set the Texture Type chart.BackWall.Fill.GradientColorType = ExcelGradientColor.TwoColor; chart.BackWall.Fill.GradientStyle = ExcelGradientStyle.Diagonl_Down; chart.BackWall.Fill.ForeColor = Colors.White; chart.BackWall.Fill.BackColor = Colors.Blue; //set the Border Linecolor chart.BackWall.Border.LineColor = Colors.Brown; //set the Picture Type chart.BackWall.PictureUnit = ExcelChartPictureType.stretch; //set the Backwall thickness chart.BackWall.Thickness = 10; //set the sidewall fill option chart.SideWall.Fill.FillType = ExcelFillType.SolidColor; //set the sidewall foreground and backcolor chart.SideWall.Fill.BackColor = Colors.White; chart.SideWall.Fill.ForeColor = Colors.White; //set the side wall Border color chart.SideWall.Border.LineColor = Colors.Red; //set floor fill option chart.Floor.Fill.FillType = ExcelFillType.Pattern; //set the floor pattern Type chart.Floor.Fill.Pattern = ExcelGradientPattern.Pat_Divot; //Set the floor fore and Back ground color chart.Floor.Fill.ForeColor = Colors.Blue; chart.Floor.Fill.BackColor = Colors.White; //set the floor thickness chart.Floor.Thickness = 3; //Set rotation angles chart.Rotation = 20; chart.Elevation = 15; IChartSerie serieTwo = chart.Series[1]; //Show value as data labels serieOne.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.DataPoints.DefaultDataPoint.DataLabels.IsValue = true; serieTwo.Name = "Temperature,deg.F"; // Legend setting chart.Legend.Position = ExcelLegendPosition.Right; chart.Legend.IsVerticalLegend = false; //Move the worksheet chartSheet.Move(1); #endregion #endregion //#endif #region Pie Chart IWorksheet embeddedChartSheet = workbook.Worksheets.Create("Pie Chart"); IChartShape embeddedChart = embeddedChartSheet.Charts.Add(); embeddedChartSheet.Activate(); embeddedChart.ChartTitle = "Precipitation in Months"; embeddedChart.IsSeriesInRows = false; embeddedChart.ChartType = ExcelChartType.Pie; embeddedChart.DataRange = chartSheet["A4:B15"]; embeddedChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true; embeddedChart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ShowLeaderLines = true; #endregion #if WINDOWS_PHONE_APP workbook.Worksheets[0].Remove(); embeddedChartSheet.Activate(); #endif #region Saving workbook and disposing objects await workbook.SaveAsAsync(storageFile); workbook.Close(); excelEngine.Dispose(); #endregion #region Save acknowledgement and launching of ouput 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)); }