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");
}
}
