private void OnExportClicked(object obj) { var grid = obj as SfDataGrid; var options = new ExcelExportingOptions(); options.ExcelVersion = ExcelVersion.Excel2013; var excelEngine = grid.ExportToExcel(grid.View, options); var workBook = excelEngine.Excel.Workbooks[0]; //Apply conditional format to worksheet IWorksheet worksheet = workBook.Worksheets[0]; IConditionalFormats formats = worksheet["F2:F11"].ConditionalFormats; IConditionalFormat format = formats.AddCondition(); format.FormatType = ExcelCFType.DataBar; IDataBar dataBar = format.DataBar; dataBar.BarColor = Color.Blue; workBook.SaveAs("Sample.xlsx"); }
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e) { StorageFile storageFile; if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))) { FileSavePicker savePicker = new FileSavePicker(); savePicker.SuggestedStartLocation = PickerLocationId.Desktop; savePicker.SuggestedFileName = "ConditionalFormattings"; 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("ConditionalFormattings.xlsx", CreationCollisionOption.ReplaceExisting); } if (storageFile == null) { return; } //Instantiate excel Engine ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; Assembly assembly = typeof(CondFormat).GetTypeInfo().Assembly; string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.CFTemplate.xlsx"; Stream fileStream = assembly.GetManifestResourceStream(resourcePath); IWorkbook myWorkbook = await excelEngine.Excel.Workbooks.OpenAsync(fileStream); IWorksheet sheet = myWorkbook.Worksheets[0]; #region Databar //Add condition for the range IConditionalFormats formats = sheet.Range["C7:C46"].ConditionalFormats; IConditionalFormat format = formats.AddCondition(); //Set Data bar and icon set for the same cell //Set the format type format.FormatType = ExcelCFType.DataBar; IDataBar dataBar = format.DataBar; //Set the constraint dataBar.MinPoint.Type = ConditionValueType.LowestValue; dataBar.MinPoint.Value = "0"; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.MaxPoint.Value = "0"; //Set color for Bar dataBar.BarColor = Color.FromArgb(255, 156, 208, 243); //Hide the value in data bar dataBar.ShowValue = false; #endregion #region Iconset //Add another condition in the same range format = formats.AddCondition(); //Set Icon format type format.FormatType = ExcelCFType.IconSet; IIconSet iconSet = format.IconSet; iconSet.IconSet = ExcelIconSetType.FourRating; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; //Sets Icon sets for another range formats = sheet.Range["E7:E46"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.IconSet; iconSet = format.IconSet; iconSet.IconSet = ExcelIconSetType.ThreeSymbols; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; #endregion #region Duplicate formats = sheet.Range["D7:D46"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.Duplicate; format.BackColorRGB = Color.FromArgb(255, 255, 199, 206); #endregion #region TopBottom and AboveBelowAverage sheet = myWorkbook.Worksheets[1]; formats = sheet.Range["N6:N35"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.TopBottom; format.TopBottom.Type = ExcelCFTopBottomType.Bottom; format.BackColorRGB = Color.FromArgb(255, 51, 153, 102); formats = sheet.Range["M6:M35"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.AboveBelowAverage; format.AboveBelowAverage.AverageType = ExcelCFAverageType.Below; format.FontColorRGB = Color.FromArgb(255, 255, 255, 255); format.BackColorRGB = Color.FromArgb(255, 166, 59, 38); #endregion await myWorkbook.SaveAsAsync(storageFile); //Close the workbook. myWorkbook.Close(); //No exception will be thrown if there are unsaved workbooks. excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); 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); } }
// // GET: /ConditionalFormatting/ public ActionResult ConditionalFormatting(string SaveOption) { string basePath = _hostingEnvironment.WebRootPath; if (SaveOption == null) { return(View()); } //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; string OutputFileName = ""; //Open an existing Excel 2007 file IWorkbook workbook = null; //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = null; //Set the File Format as XLS if (SaveOption == "Xls") { workbook = application.Workbooks.Create(3); sheet = workbook.Worksheets[0]; workbook.Version = ExcelVersion.Excel97to2003; OutputFileName = "ConditionalFormatting.xls"; } //Set the File Format as XLSX else { FileStream inputStream = new FileStream(basePath + @"/XlsIO/CFTemplate.xlsx", FileMode.Open, FileAccess.Read); workbook = excelEngine.Excel.Workbooks.Open(inputStream); sheet = workbook.Worksheets[0]; workbook.Version = ExcelVersion.Excel2016; OutputFileName = "ConditionalFormatting.xlsx"; } if (SaveOption != "Xls") { #region Databar //Add condition for the range IConditionalFormats formats = sheet.Range["C7:C46"].ConditionalFormats; IConditionalFormat format = formats.AddCondition(); //Set Data bar and icon set for the same cell //Set the format type format.FormatType = ExcelCFType.DataBar; IDataBar dataBar = format.DataBar; //Set the constraint dataBar.MinPoint.Type = ConditionValueType.LowestValue; dataBar.MinPoint.Value = "0"; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.MaxPoint.Value = "0"; //Set color for Bar dataBar.BarColor = Color.FromArgb(156, 208, 243); //Hide the value in data bar dataBar.ShowValue = false; #endregion #region Iconset //Add another condition in the same range format = formats.AddCondition(); //Set Icon format type format.FormatType = ExcelCFType.IconSet; IIconSet iconSet = format.IconSet; iconSet.IconSet = ExcelIconSetType.FourRating; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; //Sets Icon sets for another range formats = sheet.Range["E7:E46"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.IconSet; iconSet = format.IconSet; iconSet.IconSet = ExcelIconSetType.ThreeSymbols; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; #endregion #region Databar Negative value settings //Add condition for the range IConditionalFormats conditionalFormats1 = sheet.Range["E7:E46"].ConditionalFormats; IConditionalFormat conditionalFormat1 = conditionalFormats1.AddCondition(); //Set Data bar and icon set for the same cell //Set the conditionalFormat type conditionalFormat1.FormatType = ExcelCFType.DataBar; IDataBar dataBar1 = conditionalFormat1.DataBar; //Set the constraint dataBar1.BarColor = Color.YellowGreen; dataBar1.NegativeFillColor = Color.Pink; dataBar1.NegativeBorderColor = Color.WhiteSmoke; dataBar1.BarAxisColor = Color.Yellow; dataBar1.BorderColor = Color.WhiteSmoke; dataBar1.DataBarDirection = DataBarDirection.context; dataBar1.DataBarAxisPosition = DataBarAxisPosition.middle; dataBar1.HasGradientFill = true; //Hide the value in data bar dataBar1.ShowValue = false; #endregion #region Duplicate formats = sheet.Range["D7:D46"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.Duplicate; format.BackColorRGB = Color.FromArgb(255, 199, 206); #endregion #region TopBottom and AboveBelowAverage sheet = workbook.Worksheets[1]; formats = sheet.Range["N6:N35"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.TopBottom; format.TopBottom.Type = ExcelCFTopBottomType.Bottom; format.BackColorRGB = Color.FromArgb(51, 153, 102); formats = sheet.Range["M6:M35"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.AboveBelowAverage; format.AboveBelowAverage.AverageType = ExcelCFAverageType.Below; format.FontColorRGB = Color.FromArgb(255, 255, 255); format.BackColorRGB = Color.FromArgb(166, 59, 38); #endregion } else { sheet.IsGridLinesVisible = false; sheet.Range["D2"].Text = "Conditional Formatting"; sheet.Range["D2:E2"].Merge(); sheet.Range["D2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["D2"].CellStyle.Font.Bold = true; sheet.Range["D2"].CellStyle.Font.Size = 14; //Applying conditional formatting to "E5" for format type as CellValue( Between) IConditionalFormats condition = sheet.Range["E5"].ConditionalFormats; sheet.Range["E5"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E5"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E5"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E5"].AddComment().Text = "Entering a Number between 10 to 20 will set the backcolor for the cell"; //Adding formats to IConditionalFormats collection IConditionalFormat condition1 = condition.AddCondition(); sheet.Range["D5"].Text = "Enter a Number between 10 to 20"; condition1.FirstFormula = "10"; condition1.SecondFormula = "20"; //Setting format properties. condition1.Operator = ExcelComparisonOperator.Between; condition1.FormatType = ExcelCFType.CellValue; condition1.BackColorRGB = Color.FromArgb(238, 122, 3); condition1.IsBold = true; condition1.IsItalic = true; //Applying conditional formatting to "E8" for format type as CellValue( Equal) IConditionalFormats condition2 = sheet.Range["E8"].ConditionalFormats; sheet.Range["E8"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E8"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E8"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E8"].AddComment().Text = "Entering a Number as 1000 will set the highlight the number with Red color"; //Adding formats to IConditionalFormats collection IConditionalFormat condition3 = condition2.AddCondition(); sheet.Range["D8"].Text = "Enter the Number as 1000"; //Setting format properties. condition3.FormatType = ExcelCFType.CellValue; condition3.Operator = ExcelComparisonOperator.Equal; condition3.FirstFormula = "1000"; condition3.FontColorRGB = Color.FromArgb(0xde, 0x64, 0x13); //Applying conditional formatting to "E11" for format type as CellValue( Not between) IConditionalFormats condition4 = sheet.Range["E11"].ConditionalFormats; sheet.Range["E11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E11"].AddComment().Text = "Entering a Number which is not between 100 to 200 will retain the pattern"; //Adding formats to IConditionalFormats collection IConditionalFormat condition5 = condition4.AddCondition(); sheet.Range["D11"].Text = "Enter a Number not between 100 to 200"; //Setting format properties condition5.FormatType = ExcelCFType.CellValue; condition5.Operator = ExcelComparisonOperator.NotBetween; condition5.FirstFormula = "100"; condition5.SecondFormula = "200"; condition5.FillPattern = ExcelPattern.DarkVertical; //Applying conditional formatting to "E14" for format type as CellValue( LessOrEqual) IConditionalFormats condition6 = sheet.Range["E14"].ConditionalFormats; sheet.Range["E14"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E14"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E14"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E14"].AddComment().Text = "Entering a Number which is less than or equal to 1000 will retain the pattern"; //Adding formats to IConditionalFormats collection IConditionalFormat condition7 = condition6.AddCondition(); sheet.Range["D14"].Text = "Enter a Number which is less than or equal to 1000"; //Setting format properties. condition7.FormatType = ExcelCFType.CellValue; condition7.Operator = ExcelComparisonOperator.LessOrEqual; condition7.FirstFormula = "1000"; condition7.BackColorRGB = Color.FromArgb(204, 212, 230); //Applying conditional formatting to "E17" for format type as CellValue( NotEqual) IConditionalFormats condition8 = sheet.Range["E17"].ConditionalFormats; sheet.Range["E17"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E17"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E17"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E17"].AddComment().Text = "Entering a Number which is not equal to 1000 will retain the pattern"; //Adding formats to IConditionalFormats collection IConditionalFormat condition9 = condition8.AddCondition(); sheet.Range["D17"].Text = "Enter a Number which is not equal to 1000"; //Setting format properties. condition9.FormatType = ExcelCFType.CellValue; condition9.Operator = ExcelComparisonOperator.NotEqual; condition9.FirstFormula = "1000"; condition9.BackColorRGB = Color.ForestGreen; sheet.UsedRange.AutofitColumns(); } try { string ContentType = null; string fileName = null; if (SaveOption == "Xls") { workbook.Version = ExcelVersion.Excel97to2003; ContentType = "Application/vnd.ms-excel"; fileName = "ConditionalFormatting.xls"; } else { workbook.Version = ExcelVersion.Excel2013; ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; fileName = "ConditionalFormatting.xlsx"; } MemoryStream ms = new MemoryStream(); workbook.SaveAs(ms); ms.Position = 0; return(File(ms, ContentType, fileName)); } catch (Exception) { } // Close the workbook workbook.Close(); excelEngine.Dispose(); return(View()); }
private void ConvertToXlsx() { try { using (ExcelEngine excelEngine = new ExcelEngine()) { IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2016; //Preserve data types as per the value application.PreserveCSVDataTypes = true; //Read my CSV file Stream csvStream = File.OpenRead(Path.GetFullPath(saveFile.FileName)); //Reads CSV stream as a workbook IWorkbook workbook = application.Workbooks.Open(csvStream); IWorksheet sheet = workbook.Worksheets[0]; // Formatting CSV data as a table IListObject table = sheet.ListObjects.Create("PortsTable", sheet.UsedRange); table.BuiltInTableStyle = TableBuiltInStyles.TableStyleDark10; IRange location = table.Location; location.AutofitColumns(); // Define/apply header style IStyle headerStyle = workbook.Styles.Add("HeaderStyle"); headerStyle.BeginUpdate(); headerStyle.Color = Syncfusion.Drawing.Color.LightGreen; headerStyle.Font.FontName = "Consolas"; headerStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.Dark_blue; headerStyle.Font.Bold = true; headerStyle.Font.Size = 16; headerStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium; headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium; headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium; headerStyle.EndUpdate(); // Define and apply a body style IStyle bodyStyle = workbook.Styles.Add("BodyStyle"); bodyStyle.BeginUpdate(); bodyStyle.Font.FontName = "Cambria"; bodyStyle.Font.Color = Syncfusion.XlsIO.ExcelKnownColors.BlackCustom; bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium; bodyStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium; bodyStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium; bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Medium; bodyStyle.EndUpdate(); // Define a few additonal tweaks IStyle increaseStyle = workbook.Styles.Add("IncreaseStyle"); increaseStyle.BeginUpdate(); increaseStyle.Color = Syncfusion.Drawing.Color.LightSkyBlue; increaseStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium; increaseStyle.Font.Size = 14; increaseStyle.Font.Bold = true; // Modify this range seperately so it doesn't get messed up sheet.Range["A1:A1"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["A2:A2"].DateTime.ToShortDateString(); sheet.Range["A2:A2"].CellStyle.Color = Syncfusion.Drawing.Color.LightSkyBlue; sheet.Range["A2:A2"].CellStyle.Font.Bold = true; sheet.Range["A2:A2"].CellStyle.Font.Size = 14; sheet.Range["A2:A4"].HorizontalAlignment = ExcelHAlign.HAlignLeft; sheet.Range["A2:A4"].Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Medium; sheet.Range["A2:A2"].Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Medium; sheet.Range["A2:A4"].Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium; sheet.Range["A3:A4"].Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium; sheet.Range["A3:A3"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["B1:B100"].HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["B2:B3"].CellStyle.Color = Syncfusion.Drawing.Color.Black; // Custom widths sheet.Columns[0].ColumnWidth = 20.00; // Then Apply header style sheet.Range["A1:A1"].CellStyle = headerStyle; sheet.Range["A3:A3"].CellStyle = headerStyle; sheet.Range["B1:B1"].CellStyle = headerStyle; // And the body style sheet.Range["A4:A4"].CellStyle = bodyStyle; sheet.Range["B4:B100"].CellStyle = bodyStyle; // Additions sheet.Range["A2:A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["A4:A4"].CellStyle = increaseStyle; sheet.Range["A4:A4"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; // Apply a conditional format for cells with the text 'Open' IConditionalFormats condition = sheet.Range["B4:B100"].ConditionalFormats; IConditionalFormat condition1 = condition.AddCondition(); condition1.FormatType = ExcelCFType.SpecificText; condition1.Text = "Open"; condition1.Operator = ExcelComparisonOperator.ContainsText; condition1.BackColor = ExcelKnownColors.Red; // Save file in the same directory as the initial raw CSV from StreamWriter Stream excelStream; string makeoverPath = Environment.ExpandEnvironmentVariables(@"C:\Users\%USERNAME%\Desktop\makeover.xlsx"); excelStream = File.Create(Path.GetFullPath(makeoverPath)); workbook.SaveAs(excelStream); // Release all resources => IMPORTANT! excelStream.Dispose(); MessageBox.Show("SUCCESS! Your original file " + saveFile.FileName + " has been converted to .XLSX. You can view it at: " + makeoverPath); // Flip this on so that below I can safely delete the ugly .CSV original file prettified = true; } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnCreate_Click(object sender, System.EventArgs e) { #region Initialize Workbook //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. //Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2007; //Get the path of the input file if (rdImagewtSize.Checked) { fileName = "TemplateMarkerImageWithSize.xlsx"; } else if (rdImageOnly.Checked) { fileName = "TemplateMarkerImageOnly.xlsx"; } else if (rdImagewtPosition.Checked) { fileName = "TemplateMarkerImageWithPosition.xlsx"; } else if (rdImagewtSizeAndPosition.Checked) { fileName = "TemplateMarkerImageWithSize&Position.xlsx"; } else if (rdImageFitToCell.Checked) { fileName = "TemplateMarkerImageFitToCell.xlsx"; } inputPath = GetFullTemplatePath(fileName); //Open an existing spreadsheet which will be used as a template for generating the new spreadsheet. //After opening, the workbook object represents the complete in-memory object model of the template spreadsheet. IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet1 = workbook.Worksheets[0]; IWorksheet worksheet2 = workbook.Worksheets[1]; #endregion #region Create Template Marker //Create Template Marker Processor ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor(); IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet1["C5"]); #region Data Bar //Apply markers using Formula IConditionalFormat condition = conditionalFormats.AddCondition(); //Set Data bar and icon set for the same cell //Set the format type condition.FormatType = ExcelCFType.DataBar; IDataBar dataBar = condition.DataBar; //Set the constraint dataBar.MinPoint.Type = ConditionValueType.LowestValue; dataBar.MinPoint.Value = "0"; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.MaxPoint.Value = "0"; //Set color for Bar dataBar.BarColor = Color.FromArgb(156, 208, 243); //Hide the value in data bar dataBar.ShowValue = false; #endregion #region IconSet condition = conditionalFormats.AddCondition(); condition.FormatType = ExcelCFType.IconSet; IIconSet iconSet = condition.IconSet; iconSet.IconSet = ExcelIconSetType.FourRating; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; #endregion conditionalFormats = marker.CreateConditionalFormats(worksheet1["D5"]); #region Color Scale condition = conditionalFormats.AddCondition(); condition.FormatType = ExcelCFType.ColorScale; IColorScale colorScale = condition.ColorScale; //Sets 3 - color scale. colorScale.SetConditionCount(3); colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218); colorScale.Criteria[0].Type = ConditionValueType.LowestValue; colorScale.Criteria[0].Value = "0"; colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178); colorScale.Criteria[1].Type = ConditionValueType.Percentile; colorScale.Criteria[1].Value = "50"; colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171); colorScale.Criteria[2].Type = ConditionValueType.HighestValue; colorScale.Criteria[2].Value = "0"; #endregion conditionalFormats = marker.CreateConditionalFormats(worksheet1["E5"]); #region Iconset condition = conditionalFormats.AddCondition(); condition.FormatType = ExcelCFType.IconSet; iconSet = condition.IconSet; iconSet.IconSet = ExcelIconSetType.ThreeSymbols; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = false; #endregion //Northwind customers table if (rdbDataTable.Checked) { worksheet1["A5"].Value = worksheet1["A5"].Value.Replace("Customers.Hyperlink.", "Customers."); marker.AddVariable("Customers", northwindDt); } else { //New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open]. //The instantiation process consists of two steps. if (this._customers.Count == 0) { this._customers = GetCustomerAsObjects(); } marker.AddVariable("Customers", _customers); } //Stretch Formula. This shows the data getting replaced in the marker specified in another worksheet. marker.AddVariable("NumbersTable", numbersDt); //Process the markers in the template. marker.ApplyMarkers(); #endregion #region Save the Workbook workbook.Version = ExcelVersion.Excel2007; //Saving the workbook to disk. This spreadsheet is the result of opening and modifying //an existing spreadsheet and then saving the result to a new workbook. workbook.SaveAs(fileName); #endregion #region Workbook Close and Dispose //Close the workbook. workbook.Close(); 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) { //Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer] System.Diagnostics.Process.Start(fileName); } #endregion }
/// <summary> /// Apply the conditonal format using workbook /// </summary> /// <param name="worksheet">worksheet used to get the range and set the conditional formats</param> private void ApplyConditionFormatting(IWorksheet worksheet) { IConditionalFormats statusCondition = worksheet["H2:AL31"].ConditionalFormats; IConditionalFormat leaveCondition = statusCondition.AddCondition(); leaveCondition.FormatType = ExcelCFType.CellValue; leaveCondition.Operator = ExcelComparisonOperator.Equal; leaveCondition.FirstFormula = "\"L\""; leaveCondition.BackColorRGB = Color.FromArgb(253, 167, 92); IConditionalFormat absentCondition = statusCondition.AddCondition(); absentCondition.FormatType = ExcelCFType.CellValue; absentCondition.Operator = ExcelComparisonOperator.Equal; absentCondition.FirstFormula = "\"A\""; absentCondition.BackColorRGB = Color.FromArgb(255, 105, 124); IConditionalFormat presentCondition = statusCondition.AddCondition(); presentCondition.FormatType = ExcelCFType.CellValue; presentCondition.Operator = ExcelComparisonOperator.Equal; presentCondition.FirstFormula = "\"P\""; presentCondition.BackColorRGB = Color.FromArgb(67, 233, 123); IConditionalFormat weekendCondition = statusCondition.AddCondition(); weekendCondition.FormatType = ExcelCFType.CellValue; weekendCondition.Operator = ExcelComparisonOperator.Equal; weekendCondition.FirstFormula = "\"WE\""; weekendCondition.BackColorRGB = Color.FromArgb(240, 240, 240); IConditionalFormats presentSummaryCF = worksheet["C2:C31"].ConditionalFormats; IConditionalFormat presentCountCF = presentSummaryCF.AddCondition(); presentCountCF.FormatType = ExcelCFType.DataBar; IDataBar dataBar = presentCountCF.DataBar; dataBar.BarColor = Color.FromArgb(61, 242, 142); IConditionalFormats leaveSummaryCF = worksheet["D2:D31"].ConditionalFormats; IConditionalFormat leaveCountCF = leaveSummaryCF.AddCondition(); leaveCountCF.FormatType = ExcelCFType.DataBar; dataBar = leaveCountCF.DataBar; dataBar.BarColor = Color.FromArgb(242, 71, 23); IConditionalFormats absentSummaryCF = worksheet["E2:E31"].ConditionalFormats; IConditionalFormat absentCountCF = absentSummaryCF.AddCondition(); absentCountCF.FormatType = ExcelCFType.DataBar; dataBar = absentCountCF.DataBar; dataBar.BarColor = Color.FromArgb(255, 10, 69); IConditionalFormats unplannedSummaryCF = worksheet["F2:F31"].ConditionalFormats; IConditionalFormat unplannedCountCF = unplannedSummaryCF.AddCondition(); unplannedCountCF.FormatType = ExcelCFType.DataBar; dataBar = unplannedCountCF.DataBar; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.BarColor = Color.FromArgb(142, 142, 142); IConditionalFormats plannedSummaryCF = worksheet["G2:G31"].ConditionalFormats; IConditionalFormat plannedCountCF = plannedSummaryCF.AddCondition(); plannedCountCF.FormatType = ExcelCFType.DataBar; dataBar = plannedCountCF.DataBar; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.BarColor = Color.FromArgb(56, 136, 254); }
// // 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 } }
// // GET: /ConditionalFormatting/ public ActionResult ConditionalFormatting(string SaveOption) { if (SaveOption == null) { return(View()); } //New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open]. //The instantiation process consists of two steps. //Step 1 : Instantiate the spreadsheet creation engine. ExcelEngine excelEngine = new ExcelEngine(); //Step 2 : Instantiate the excel application object. IApplication application = excelEngine.Excel; string OutputFileName = ""; //Open an existing Excel 2007 file IWorkbook workbook = null; //The first worksheet object in the worksheets collection is accessed. IWorksheet sheet = null; //Set the File Format as XLS if (SaveOption == "Xls") { workbook = application.Workbooks.Create(3); sheet = workbook.Worksheets[0]; workbook.Version = ExcelVersion.Excel97to2003; OutputFileName = "ConditionalFormatting.xls"; } //Set the File Format as XLSX else { workbook = excelEngine.Excel.Workbooks.Open(ResolveApplicationDataPath("CFTemplate.xlsx")); sheet = workbook.Worksheets[0]; workbook.Version = ExcelVersion.Excel2016; OutputFileName = "ConditionalFormatting.xlsx"; } if (SaveOption != "Xls") { #region Databar //Add condition for the range IConditionalFormats formats = sheet.Range["C7:C46"].ConditionalFormats; IConditionalFormat format = formats.AddCondition(); //Set Data bar and icon set for the same cell //Set the format type format.FormatType = ExcelCFType.DataBar; IDataBar dataBar = format.DataBar; //Set the constraint dataBar.MinPoint.Type = ConditionValueType.LowestValue; dataBar.MinPoint.Value = "0"; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.MaxPoint.Value = "0"; //Set color for Bar dataBar.BarColor = Color.FromArgb(156, 208, 243); //Hide the value in data bar dataBar.ShowValue = false; #endregion #region Iconset //Add another condition in the same range format = formats.AddCondition(); //Set Icon format type format.FormatType = ExcelCFType.IconSet; IIconSet iconSet = format.IconSet; iconSet.IconSet = ExcelIconSetType.FourRating; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; //Sets Icon sets for another range formats = sheet.Range["E7:E46"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.IconSet; iconSet = format.IconSet; iconSet.IconSet = ExcelIconSetType.ThreeSymbols; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; #endregion #region Databar Negative value settings //Add condition for the range IConditionalFormats conditionalFormats1 = sheet.Range["E7:E46"].ConditionalFormats; IConditionalFormat conditionalFormat1 = conditionalFormats1.AddCondition(); //Set Data bar and icon set for the same cell //Set the conditionalFormat type conditionalFormat1.FormatType = ExcelCFType.DataBar; IDataBar dataBar1 = conditionalFormat1.DataBar; //Set the constraint dataBar1.BarColor = Color.YellowGreen; dataBar1.NegativeFillColor = Color.Pink; dataBar1.NegativeBorderColor = Color.WhiteSmoke; dataBar1.BarAxisColor = Color.Yellow; dataBar1.BorderColor = Color.WhiteSmoke; dataBar1.DataBarDirection = DataBarDirection.context; dataBar1.DataBarAxisPosition = DataBarAxisPosition.middle; dataBar1.HasGradientFill = true; //Hide the value in data bar dataBar1.ShowValue = false; #endregion #region Color Scale formats = sheet.Range["D7:D46"].ConditionalFormats; format = formats.AddCondition(); format.FormatType = ExcelCFType.ColorScale; IColorScale colorScale = format.ColorScale; //Sets 3 - color scale. colorScale.SetConditionCount(3); colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218); colorScale.Criteria[0].Type = ConditionValueType.LowestValue; colorScale.Criteria[0].Value = "0"; colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178); colorScale.Criteria[1].Type = ConditionValueType.Percentile; colorScale.Criteria[1].Value = "50"; colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171); colorScale.Criteria[2].Type = ConditionValueType.HighestValue; colorScale.Criteria[2].Value = "0"; #endregion } else { sheet.IsGridLinesVisible = false; sheet.Range["D2"].Text = "Conditional Formatting"; sheet.Range["D2:E2"].Merge(); sheet.Range["D2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter; sheet.Range["D2"].CellStyle.Font.Bold = true; sheet.Range["D2"].CellStyle.Font.Size = 14; //Applying conditional formatting to "E5" for format type as CellValue( Between) IConditionalFormats condition = sheet.Range["E5"].ConditionalFormats; sheet.Range["E5"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E5"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E5"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E5"].AddComment().Text = "Entering a Number between 10 to 20 will set the backcolor for the cell"; //Adding formats to IConditionalFormats collection IConditionalFormat condition1 = condition.AddCondition(); sheet.Range["D5"].Text = "Enter a Number between 10 to 20"; condition1.FirstFormula = "10"; condition1.SecondFormula = "20"; //Setting format properties. condition1.Operator = ExcelComparisonOperator.Between; condition1.FormatType = ExcelCFType.CellValue; condition1.BackColorRGB = Color.FromArgb(238, 122, 3); condition1.IsBold = true; condition1.IsItalic = true; //Applying conditional formatting to "E8" for format type as CellValue( Equal) IConditionalFormats condition2 = sheet.Range["E8"].ConditionalFormats; sheet.Range["E8"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E8"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E8"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E8"].AddComment().Text = "Entering a Number as 1000 will set the highlight the number with Red color"; //Adding formats to IConditionalFormats collection IConditionalFormat condition3 = condition2.AddCondition(); sheet.Range["D8"].Text = "Enter the Number as 1000"; //Setting format properties. condition3.FormatType = ExcelCFType.CellValue; condition3.Operator = ExcelComparisonOperator.Equal; condition3.FirstFormula = "1000"; condition3.FontColorRGB = Color.FromArgb(0xde, 0x64, 0x13); //Applying conditional formatting to "E11" for format type as CellValue( Not between) IConditionalFormats condition4 = sheet.Range["E11"].ConditionalFormats; sheet.Range["E11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E11"].AddComment().Text = "Entering a Number which is not between 100 to 200 will retain the pattern"; //Adding formats to IConditionalFormats collection IConditionalFormat condition5 = condition4.AddCondition(); sheet.Range["D11"].Text = "Enter a Number not between 100 to 200"; //Setting format properties condition5.FormatType = ExcelCFType.CellValue; condition5.Operator = ExcelComparisonOperator.NotBetween; condition5.FirstFormula = "100"; condition5.SecondFormula = "200"; condition5.FillPattern = ExcelPattern.DarkVertical; //Applying conditional formatting to "E14" for format type as CellValue( LessOrEqual) IConditionalFormats condition6 = sheet.Range["E14"].ConditionalFormats; sheet.Range["E14"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E14"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E14"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E14"].AddComment().Text = "Entering a Number which is less than or equal to 1000 will retain the pattern"; //Adding formats to IConditionalFormats collection IConditionalFormat condition7 = condition6.AddCondition(); sheet.Range["D14"].Text = "Enter a Number which is less than or equal to 1000"; //Setting format properties. condition7.FormatType = ExcelCFType.CellValue; condition7.Operator = ExcelComparisonOperator.LessOrEqual; condition7.FirstFormula = "1000"; condition7.BackColorRGB = Color.FromArgb(204, 212, 230); //Applying conditional formatting to "E17" for format type as CellValue( NotEqual) IConditionalFormats condition8 = sheet.Range["E17"].ConditionalFormats; sheet.Range["E17"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium; sheet.Range["E17"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false; sheet.Range["E17"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false; sheet.Range["E17"].AddComment().Text = "Entering a Number which is not equal to 1000 will retain the pattern"; //Adding formats to IConditionalFormats collection IConditionalFormat condition9 = condition8.AddCondition(); sheet.Range["D17"].Text = "Enter a Number which is not equal to 1000"; //Setting format properties. condition9.FormatType = ExcelCFType.CellValue; condition9.Operator = ExcelComparisonOperator.NotEqual; condition9.FirstFormula = "1000"; condition9.BackColorRGB = Color.ForestGreen; sheet.UsedRange.AutofitColumns(); } try { if (SaveOption == "Xls") { return(excelEngine.SaveAsActionResult(workbook, OutputFileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97)); } else { return(excelEngine.SaveAsActionResult(workbook, OutputFileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016)); } } catch (Exception) { } workbook.Close(); excelEngine.Dispose(); return(View()); }
/// <summary> /// Apply the conditonal format using workbook /// </summary> /// <param name="worksheet">worksheet used to get the range and set the conditional formats</param> private void ApplyConditionFormatting(IWorksheet worksheet) { IConditionalFormats statusCondition = worksheet["H2:AL31"].ConditionalFormats; IConditionalFormat leaveCondition = statusCondition.AddCondition(); leaveCondition.FormatType = ExcelCFType.CellValue; leaveCondition.Operator = ExcelComparisonOperator.Equal; leaveCondition.FirstFormula = "\"L\""; leaveCondition.BackColor = ExcelKnownColors.Orange; IConditionalFormat absentCondition = statusCondition.AddCondition(); absentCondition.FormatType = ExcelCFType.CellValue; absentCondition.Operator = ExcelComparisonOperator.Equal; absentCondition.FirstFormula = "\"A\""; absentCondition.BackColor = ExcelKnownColors.Red; IConditionalFormat presentCondition = statusCondition.AddCondition(); presentCondition.FormatType = ExcelCFType.CellValue; presentCondition.Operator = ExcelComparisonOperator.Equal; presentCondition.FirstFormula = "\"P\""; presentCondition.BackColor = ExcelKnownColors.Green; IConditionalFormat weekendCondition = statusCondition.AddCondition(); weekendCondition.FormatType = ExcelCFType.CellValue; weekendCondition.Operator = ExcelComparisonOperator.Equal; weekendCondition.FirstFormula = "\"WE\""; weekendCondition.BackColor = ExcelKnownColors.Brown; IConditionalFormats presentSummaryCF = worksheet["C2:C31"].ConditionalFormats; IConditionalFormat presentCountCF = presentSummaryCF.AddCondition(); presentCountCF.FormatType = ExcelCFType.DataBar; IDataBar dataBar = presentCountCF.DataBar; dataBar.BarColor = Color.Blue; IConditionalFormats leaveSummaryCF = worksheet["D2:D31"].ConditionalFormats; IConditionalFormat leaveCountCF = leaveSummaryCF.AddCondition(); leaveCountCF.FormatType = ExcelCFType.DataBar; dataBar = leaveCountCF.DataBar; dataBar.BarColor = Color.Orange; IConditionalFormats absentSummaryCF = worksheet["E2:E31"].ConditionalFormats; IConditionalFormat absentCountCF = absentSummaryCF.AddCondition(); absentCountCF.FormatType = ExcelCFType.DataBar; dataBar = absentCountCF.DataBar; dataBar.BarColor = Color.Red; IConditionalFormats unplannedSummaryCF = worksheet["F2:F31"].ConditionalFormats; IConditionalFormat unplannedCountCF = unplannedSummaryCF.AddCondition(); unplannedCountCF.FormatType = ExcelCFType.DataBar; dataBar = unplannedCountCF.DataBar; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.BarColor = Color.Red; IConditionalFormats plannedSummaryCF = worksheet["G2:G31"].ConditionalFormats; IConditionalFormat plannedCountCF = plannedSummaryCF.AddCondition(); plannedCountCF.FormatType = ExcelCFType.DataBar; dataBar = plannedCountCF.DataBar; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.BarColor = Color.BlueViolet; }
/// <summary> /// Apply the conditional formattings in the Excel document /// </summary> private void ApplyCondtionalFormatting() { Assembly assembly = typeof(App).GetTypeInfo().Assembly; Stream fileStream = null; fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.CFTemplate.xlsx"); MemoryStream stream = new MemoryStream(); //Creates a new instance for ExcelEngine. using (ExcelEngine excelEngine = new ExcelEngine()) { //Instantiate the Excel application object IApplication application = excelEngine.Excel; //Assigns default application version as Excel 2013 application.DefaultVersion = ExcelVersion.Excel2013; //Open an existing workbook IWorkbook workbook = excelEngine.Excel.Workbooks.Open(fileStream); //Access the first worksheet IWorksheet worksheet = workbook.Worksheets[0]; #region Databar //Create data bars for the data in specified range IConditionalFormats conditionalFormats = worksheet.Range["C7:C46"].ConditionalFormats; IConditionalFormat conditionalFormat = conditionalFormats.AddCondition(); conditionalFormat.FormatType = ExcelCFType.DataBar; IDataBar dataBar = conditionalFormat.DataBar; //Set the constraints dataBar.MinPoint.Type = ConditionValueType.LowestValue; dataBar.MinPoint.Value = "0"; dataBar.MaxPoint.Type = ConditionValueType.HighestValue; dataBar.MaxPoint.Value = "0"; //Set color for data bar dataBar.BarColor = COLOR.FromArgb(156, 208, 243); //Hide the value in data bar dataBar.ShowValue = false; #endregion #region Iconset //Create icon sets for the data in specified range conditionalFormat = conditionalFormats.AddCondition(); conditionalFormat.FormatType = ExcelCFType.IconSet; IIconSet iconSet = conditionalFormat.IconSet; //Apply four ratings icon and hide the data in the specified range iconSet.IconSet = ExcelIconSetType.FourRating; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; //Set icon set conditional format in specified range conditionalFormats = worksheet.Range["E7:E46"].ConditionalFormats; conditionalFormat = conditionalFormats.AddCondition(); conditionalFormat.FormatType = ExcelCFType.IconSet; iconSet = conditionalFormat.IconSet; //Apply three symbols icon and hide the data in the specified range iconSet.IconSet = ExcelIconSetType.ThreeSymbols; iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue; iconSet.IconCriteria[0].Value = "0"; iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue; iconSet.IconCriteria[1].Value = "0"; iconSet.ShowIconOnly = true; #endregion #region Databar Negative value settings //Create data bars for the data in specified range IConditionalFormats conditionalFormats1 = worksheet.Range["E7:E46"].ConditionalFormats; IConditionalFormat conditionalFormat1 = conditionalFormats1.AddCondition(); conditionalFormat1.FormatType = ExcelCFType.DataBar; IDataBar dataBar1 = conditionalFormat1.DataBar; //Set the constraints dataBar1.BarColor = COLOR.YellowGreen; dataBar1.NegativeFillColor = COLOR.Pink; dataBar1.NegativeBorderColor = COLOR.WhiteSmoke; dataBar1.BarAxisColor = COLOR.Yellow; dataBar1.BorderColor = COLOR.WhiteSmoke; dataBar1.DataBarDirection = DataBarDirection.context; dataBar1.DataBarAxisPosition = DataBarAxisPosition.middle; dataBar1.HasGradientFill = true; //Hide the value in data bar dataBar1.ShowValue = false; #endregion #region Color Scale //Create color scales for the data in specified range conditionalFormats = worksheet.Range["D7:D46"].ConditionalFormats; conditionalFormat = conditionalFormats.AddCondition(); conditionalFormat.FormatType = ExcelCFType.ColorScale; IColorScale colorScale = conditionalFormat.ColorScale; //Sets 3 - color scale. colorScale.SetConditionCount(3); colorScale.Criteria[0].FormatColorRGB = COLOR.FromArgb(230, 197, 218); colorScale.Criteria[0].Type = ConditionValueType.LowestValue; colorScale.Criteria[0].Value = "0"; colorScale.Criteria[1].FormatColorRGB = COLOR.FromArgb(244, 210, 178); colorScale.Criteria[1].Type = ConditionValueType.Percentile; colorScale.Criteria[1].Value = "50"; colorScale.Criteria[2].FormatColorRGB = COLOR.FromArgb(245, 247, 171); colorScale.Criteria[2].Type = ConditionValueType.HighestValue; colorScale.Criteria[2].Value = "0"; #endregion //Set the version of the workbook. workbook.Version = ExcelVersion.Excel2013; // Saving the workbook in xlsx format workbook.SaveAs(stream); } if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows) { Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("AdvancedCF.xlsx", "application/msexcel", stream); } else { Xamarin.Forms.DependencyService.Get <ISave>().Save("AdvancedCF.xlsx", "application/msexcel", stream); } }
public async Task ExportToExcel(string GridModel, string gridId, int id, string process, int?userId) { ExcelExport exp = new ExcelExport(); GridProperties obj = (GridProperties)Syncfusion.JavaScript.Utils.DeserializeToModel(typeof(GridProperties), GridModel); //Clear if there are any filter columns //syncfusion bug in exporting while in filter mode obj.FilterSettings.FilteredColumns.Clear(); grid = gridId; count = 0; if (gridId == "Audits") { var audits = await _prepareService.GetAudits(); if (userId.HasValue) { audits = audits.Where(a => a.AuditorId == userId.Value || a.Inspection.InspectionSteps.Any(s => s.InspectorId == userId.Value)); } var auditManage = AuditMapper.ToAuditManageViewModel(audits); //var dataSource = auditManage.Audits.ToList(); //auditViewModel = dataSource; //var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-"); //obj.ServerExcelQueryCellInfo = QueryCellInfo; //exp.Export(obj, dataSource, "Audits " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron"); using (ExcelEngine excelEngine = new ExcelEngine()) { // Set the default application version as Excel 2016. excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2016; //var group1 = auditManage.Audits.ToList(); var allAudits = auditManage.Audits.ToList(); int i = 0; //Create a workbook with a worksheet. IWorkbook workbook = excelEngine.Excel.Workbooks.Create(audits.Select(a => a.Survey).Distinct().Count()); foreach (var survey in audits.Select(a => a.Survey).Distinct()) { IWorksheet worksheet = workbook.Worksheets[i]; DataTable tbl = new DataTable(); //fill columns tbl.Columns.Add(LocalizedStrings.GetString("AuditDate"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Schedule"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Team"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Auditor"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("Auditee"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("AuditStatus"), typeof(string)); tbl.Columns.Add(LocalizedStrings.GetString("StandardAuditee"), typeof(string)); foreach (var surveyItem in survey.SurveyItems) { tbl.Columns.Add(surveyItem.Query, typeof(int)); } tbl.Columns.Add("Score", typeof(int)); tbl.Columns.Add(LocalizedStrings.GetString("Comment"), typeof(string)); //fill rows foreach (var audit in auditManage.Audits.Where(a => a.SurveyId == survey.Id)) { DataRow dr = null; dr = tbl.NewRow(); dr[LocalizedStrings.GetString("AuditDate")] = audit.EndDate.Value.ToShortDateString(); dr[LocalizedStrings.GetString("Schedule")] = audit.EndDate.Value.ToShortTimeString().Replace(":", "H"); dr[LocalizedStrings.GetString("Team")] = string.Join(",", audit.AuditorTeams); dr[LocalizedStrings.GetString("Auditor")] = audit.AuditorName; dr[LocalizedStrings.GetString("Auditee")] = audit.AuditeeName; dr[LocalizedStrings.GetString("AuditStatus")] = audits.Select(a => a.Auditor).FirstOrDefault(u => u.UserId == audit.AuditorId).Tenured.HasValue ? audits.Select(a => a.Auditor).FirstOrDefault(u => u.UserId == audit.AuditorId).Tenured.Value ? "Titulaire" : "Intérimaire" : ""; dr[LocalizedStrings.GetString("StandardAuditee")] = audit.ProcessName; int scoreTemp = 0; foreach (var surveyItem in survey.SurveyItems) { dr[surveyItem.Query] = audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.HasValue ? audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.Value ? 1 : 0 : -1; if (audit.AuditItems.FirstOrDefault(item => item.Number == surveyItem.Number).IsOK.Value == true) { scoreTemp++; } } dr["Score"] = scoreTemp; tbl.Rows.Add(dr); } worksheet.ImportDataTable(tbl, true, 2, 1); worksheet.Name = survey.Name; worksheet.UsedRange.WrapText = true; worksheet.Columns[0].AutofitColumns(); worksheet.SetColumnWidth(1, 10); IListObject table = worksheet.ListObjects.Create("tbl_" + survey.Name.Replace(" ", "_"), worksheet.UsedRange); table.BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9; foreach (var surveyItem in survey.SurveyItems) { //Apply conditional formats for IsOK questionnaire items IConditionalFormats condition = worksheet.Columns[table.Columns.IndexOf(s => s.Name == surveyItem.Query)].ConditionalFormats; IConditionalFormat condition1 = condition.AddCondition(); condition1.FormatType = ExcelCFType.CellValue; condition1.Operator = ExcelComparisonOperator.Equal; condition1.FirstFormula = "1"; condition1.BackColor = ExcelKnownColors.Green; IConditionalFormat condition2 = condition.AddCondition(); condition2.FormatType = ExcelCFType.CellValue; condition2.Operator = ExcelComparisonOperator.Equal; condition2.FirstFormula = "0"; condition2.BackColor = ExcelKnownColors.Red; worksheet.Columns[table.Columns.IndexOf(s => s.Name == surveyItem.Query)].RowHeight = 80; worksheet.AutofitColumn(table.Columns.IndexOf(s => s.Name == surveyItem.Query)); } i++; } //worksheet.ImportData(group1, 2, 1, true); var path = Server.MapPath("~/App_Data/" + LocalizedStrings.GetString("Audits") + ".xlsx"); workbook.SaveAs(path, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2010); workbook.Close(); excelEngine.Dispose(); } } if (gridId == "AuditItems") { var audits = await _prepareService.GetAudits(id); var audit = AuditMapper.ToAuditViewModel(audits.FirstOrDefault()); var dataSource = audit.AuditItems.ToList(); auditItemViewModel = dataSource; var currentDate = DateTime.Today.ToShortDateString().Replace("/", "-"); obj.ServerExcelQueryCellInfo = QueryCellInfo; exp.Export(obj, dataSource, LocalizedStrings.GetString("Audit") + " " + process + " " + currentDate + ".xlsx", ExcelVersion.Excel2013, false, false, "flat-saffron"); } }