public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
object[,] data = new object[, ] {
{ "Name", "City", "Birthday", "Eye color", "Weight", "Height" },
{ "Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165 },
{ "Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134 },
{ "Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180 },
{ "Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163 },
{ "Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176 },
{ "Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145 }
};
worksheet.Range["A1:F7"].Value = data;
//three color scale.
IColorScale threeColorScaleRule = worksheet.Range["E2:E7"].FormatConditions.AddColorScale(ColorScaleType.ThreeColorScale);
threeColorScaleRule.ColorScaleCriteria[0].Type = ConditionValueTypes.LowestValue;
threeColorScaleRule.ColorScaleCriteria[0].FormatColor.Color = Color.Red;
threeColorScaleRule.ColorScaleCriteria[1].Type = ConditionValueTypes.Percent;
threeColorScaleRule.ColorScaleCriteria[1].Value = 30;
threeColorScaleRule.ColorScaleCriteria[1].FormatColor.Color = Color.Yellow;
threeColorScaleRule.ColorScaleCriteria[2].Type = ConditionValueTypes.HighestValue;
threeColorScaleRule.ColorScaleCriteria[2].FormatColor.Color = Color.Green;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet sheet = workbook.Worksheets[0];
//Conditional formatting on merge cell
sheet.Range["B2:C4"].Merge();
sheet.Range["B2:C4"].Value = 123;
var cf = (IFormatCondition)sheet.Range["B2:C4"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 0);
cf.Borders.ThemeColor = ThemeColor.Accent1;
cf.Borders.LineStyle = BorderLineStyle.Thin;
//Set cell values
int[] data = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
sheet.Range["B10:B19"].Value = data;
sheet.Range["C10:C19"].Value = data;
sheet.Range["D10:D19"].Value = data;
//Set conditional formattings
//Color scale
IColorScale cf1 = sheet.Range["B10:B19"].FormatConditions.AddColorScale(ColorScaleType.ThreeColorScale);
cf1.ColorScaleCriteria[0].Type = ConditionValueTypes.LowestValue;
cf1.ColorScaleCriteria[0].FormatColor.Color = Color.FromRGB(248, 105, 107);
cf1.ColorScaleCriteria[1].Type = ConditionValueTypes.Percentile;
cf1.ColorScaleCriteria[1].Value = 50;
cf1.ColorScaleCriteria[1].FormatColor.Color = Color.FromRGB(255, 235, 132);
cf1.ColorScaleCriteria[2].Type = ConditionValueTypes.HighestValue;
cf1.ColorScaleCriteria[2].FormatColor.Color = Color.FromRGB(99, 190, 123);
//Data bar
sheet.Range["C14"].Value = -5;
sheet.Range["C17"].Value = -8;
IDataBar cf2 = sheet.Range["C10:C19"].FormatConditions.AddDatabar();
cf2.MinPoint.Type = ConditionValueTypes.AutomaticMin;
cf2.MaxPoint.Type = ConditionValueTypes.AutomaticMax;
cf2.BarFillType = DataBarFillType.Gradient;
cf2.BarColor.Color = Color.FromRGB(0, 138, 239);
cf2.BarBorder.Color.Color = Color.FromRGB(0, 138, 239);
cf2.NegativeBarFormat.Color.Color = Color.FromRGB(255, 0, 0);
cf2.NegativeBarFormat.BorderColorType = DataBarNegativeColorType.Color;
cf2.NegativeBarFormat.BorderColor.Color = Color.FromRGB(255, 0, 0);
cf2.AxisColor.Color = Color.Black;
cf2.AxisPosition = DataBarAxisPosition.Automatic;
//Icon set
IIconSetCondition cf3 = sheet.Range["D10:D19"].FormatConditions.AddIconSetCondition();
cf3.IconSet = workbook.IconSets[IconSetType.Icon3Symbols];
}
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 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(255, 230, 197, 218);
colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
colorScale.Criteria[0].Value = "0";
colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(255, 244, 210, 178);
colorScale.Criteria[1].Type = ConditionValueType.Percentile;
colorScale.Criteria[1].Value = "50";
colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(255, 245, 247, 171);
colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
colorScale.Criteria[2].Value = "0";
#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(ResolveApplicationPath("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 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
{
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 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 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);
}
}