internal void OnButtonClicked(object sender, EventArgs e)
{
// Workbook Initialize
ExcelEngine engine = new ExcelEngine();
IApplication application = engine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet sheet = workbook.Worksheets[0];
sheet.EnableSheetCalculations();
sheet["A40"].Activate();
// Worksheet Data
sheet.Name = "Inventory";
sheet.IsGridLinesVisible = false;
sheet["A1"].Text = "INVENTORY LIST";
sheet["A3"].Text = "TOTAL INVENTORY VALUE";
sheet["D3"].Text = "TOTAL ITEMS";
sheet["F3"].Text = "BIN COUNT";
sheet["A6"].Text = "ID";
sheet["B6"].Text = "Name";
sheet["C6"].Text = "Bin #";
sheet["D6"].Text = "Unit Price";
sheet["E6"].Text = "Quantity in Stock";
sheet["F6"].Text = "Inventory Value";
sheet["A7"].Text = "IN001";
sheet["B7"].Text = "Item 1";
sheet["C7"].Text = "T340";
sheet["D7"].Number = 51;
sheet["E7:E26"].Number = 25;
sheet["A8"].Text = "IN002";
sheet["B8"].Text = "Item 2";
sheet["C8"].Text = "T5780";
sheet["D8"].Number = 93;
sheet["A9"].Text = "IN003";
sheet["B9"].Text = "Item 3";
sheet["C9"].Text = "T340";
sheet["D9"].Number = 57;
sheet["A10"].Text = "IN004";
sheet["B10"].Text = "Item 4";
sheet["C10"].Text = "T908";
sheet["D10"].Number = 19;
sheet["A11"].Text = "IN005";
sheet["B11"].Text = "Item 5";
sheet["C11"].Text = "T9845";
sheet["D11"].Number = 75;
sheet["A12"].Text = "IN006";
sheet["B12"].Text = "Item 6";
sheet["C12"].Text = "T540";
sheet["D12"].Number = 11;
sheet["A13"].Text = "IN007";
sheet["B13"].Text = "Item 7";
sheet["C13"].Text = "T5780";
sheet["D13"].Number = 56;
sheet["A14"].Text = "IN008";
sheet["B14"].Text = "Item 8";
sheet["C14"].Text = "T340";
sheet["D14"].Number = 38;
sheet["A15"].Text = "IN009";
sheet["B15"].Text = "Item 9";
sheet["C15"].Text = "T908";
sheet["D15"].Number = 59;
sheet["A16"].Text = "IN010";
sheet["B16"].Text = "Item 10";
sheet["C16"].Text = "T9845";
sheet["D16"].Number = 50;
sheet["A17"].Text = "IN011";
sheet["B17"].Text = "Item 11";
sheet["C17"].Text = "T306";
sheet["D17"].Number = 59;
sheet["A18"].Text = "IN012";
sheet["B18"].Text = "Item 12";
sheet["C18"].Text = "T5780";
sheet["D18"].Number = 18;
sheet["A19"].Text = "IN013";
sheet["B19"].Text = "Item 13";
sheet["C19"].Text = "T306";
sheet["D19"].Number = 26;
sheet["A20"].Text = "IN014";
sheet["B20"].Text = "Item 14";
sheet["C20"].Text = "T908";
sheet["D20"].Number = 42;
sheet["A21"].Text = "IN015";
sheet["B21"].Text = "Item 15";
sheet["C21"].Text = "T9845";
sheet["D21"].Number = 32;
sheet["A22"].Text = "IN016";
sheet["B22"].Text = "Item 16";
sheet["C22"].Text = "T415";
sheet["D22"].Number = 90;
sheet["A23"].Text = "IN017";
sheet["B23"].Text = "Item 17";
sheet["C23"].Text = "T5780";
sheet["D23"].Number = 12;
sheet["A24"].Text = "IN018";
sheet["B24"].Text = "Item 18";
sheet["C24"].Text = "T340";
sheet["D24"].Number = 82;
sheet["A25"].Text = "IN019";
sheet["B25"].Text = "Item 19";
sheet["C25"].Text = "T908";
sheet["D25"].Number = 16;
sheet["A26"].Text = "IN020";
sheet["B26"].Text = "Item 20";
sheet["C26"].Text = "T9845";
sheet["D26"].Number = 11;
sheet["A27"].Text = "Total";
// Formatting
sheet.Range["A1:F1"].Merge();
sheet.Range["A4:B4"].Merge();
sheet["A1"].CellStyle.Font.Bold = true;
sheet["A1"].CellStyle.Font.FontName = "Calibri";
sheet["A1"].CellStyle.Font.Size = 18;
sheet.Range["A1:F1"].CellStyle.IncludeBorder = true;
sheet.Range["A1:F1"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].ColorRGB = COLOR.FromArgb(0, 204, 153);
sheet.Range["A1:F1"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Medium;
sheet["A3:F3"].CellStyle.Font.Bold = true;
sheet["A3:F3"].CellStyle.Font.FontName = "Calibri";
sheet["A3:F3"].CellStyle.Font.Size = 9;
sheet["A4:F4"].CellStyle.Font.Bold = true;
sheet["A4:F4"].CellStyle.Font.FontName = "Calibri";
sheet["A4:F4"].CellStyle.Font.Size = 14;
sheet["A4:F4"].CellStyle.Font.RGBColor = COLOR.FromArgb(0, 204, 153);
sheet["A4"].HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet["A4"].NumberFormat = "_($* #,##0.00_)";
sheet["A6:F6"].CellStyle.Font.FontName = "Calibri";
sheet["A6:F6"].CellStyle.Font.Size = 11;
sheet["A6:F6"].CellStyle.Font.Bold = true;
sheet["A6:F6"].CellStyle.Font.RGBColor = COLOR.FromArgb(255, 255, 255);
sheet["A6:F6"].WrapText = true;
sheet["A6:F6"].VerticalAlignment = ExcelVAlign.VAlignTop;
sheet.Range["A6:F6"].CellStyle.Color = COLOR.FromArgb(0, 204, 153);
sheet["A6:F27"].CellStyle.Font.FontName = "Calibri";
sheet["A6:F27"].CellStyle.Font.Size = 11;
sheet["D7:F27"].NumberFormat = "_($* #,##0.00_)";
sheet["D4:F4"].HorizontalAlignment = ExcelHAlign.HAlignLeft;
sheet["A27:F27"].CellStyle.Font.FontName = "Calibri";
sheet["A27:F27"].CellStyle.Font.Size = 11;
sheet["A27:F27"].CellStyle.Font.Bold = true;
sheet.Range["A7:27"].CellStyle.IncludeBorder = true;
sheet.Range["A7:F27"].CellStyle.Borders.ColorRGB = COLOR.FromArgb(112, 173, 71);
sheet.Range["A7:F27"].CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;
sheet.Range["A7:F27"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["A7:F27"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet["A26:F26"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Double;
sheet.Range["A1:F1"].RowHeight = 24;
sheet["A2"].RowHeight = 7.18;
sheet["A3"].RowHeight = 23.5;
sheet["A4"].RowHeight = 19.5;
sheet["A5"].RowHeight = 6.8;
sheet["A6"].RowHeight = 41.3;
sheet["A1:B1"].ColumnWidth = 7.18;
sheet["C1"].ColumnWidth = 6.91;
sheet["D1"].ColumnWidth = 9.55;
sheet["E1"].ColumnWidth = 8.91;
sheet["F1"].ColumnWidth = 11.18;
// Formula
sheet["D4"].Formula = "=COUNT(D7:D26)";
sheet["F4"].Formula = "=SUMPRODUCT(1/COUNTIF(C7:C26,C7:C26))";
for (int i = 7; i < 27; i++)
{
sheet["F" + i].Formula = "=D" + i + "*E" + i;
}
sheet["D27"].Formula = "SUM(D7:D26)";
sheet["E27"].Formula = "SUM(E7:E26)";
sheet["F27"].Formula = "SUM(F7:F26)";
sheet["A4"].Formula = "=F27";
// Save Workbook
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
engine.Dispose();
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Formulas.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Formulas.xlsx", "application/msexcel", stream);
}
}
void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
int index = spinner.SelectedItemPosition;
#region Initializing Workbook
string resourcePath = "";
if (index == 6)
{
resourcePath = "SampleBrowser.Samples.XlsIO.Template.AdvancedFilterData.xlsx";
}
else if (index == 5)
{
resourcePath = "SampleBrowser.Samples.XlsIO.Template.IconFilterData.xlsx";
}
else if (index == 4)
{
resourcePath = "SampleBrowser.Samples.XlsIO.Template.FilterData_Color.xlsx";
}
else
{
resourcePath = "SampleBrowser.Samples.XlsIO.Template.FilterData.xlsx";
}
Assembly assembly = Assembly.GetExecutingAssembly();
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet["A60"].Activate();
#endregion
if (index != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
string fileName = "";
switch (index)
{
case 0:
fileName = "CustomFilter.xlsx";
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
fileName = "TextFilter.xlsx";
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
fileName = "DateTimeFilter.xlsx";
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 3:
fileName = "DynamicFilter.xlsx";
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
fileName = "Color Filter.xlsx";
#region ColorFilter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Syncfusion.Drawing.Color color = Syncfusion.Drawing.Color.Empty;
switch (spinner3.SelectedItemPosition)
{
case 0:
color = Syncfusion.Drawing.Color.Red;
break;
case 1:
color = Syncfusion.Drawing.Color.Blue;
break;
case 2:
color = Syncfusion.Drawing.Color.Green;
break;
case 3:
color = Syncfusion.Drawing.Color.Yellow;
break;
case 4:
color = Syncfusion.Drawing.Color.Empty;
break;
}
if (spinner2.SelectedItemPosition == 0)
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
#endregion
break;
case 5:
fileName = "IconFilter.xlsx";
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconSet = ExcelIconSetType.FiveArrows;
int filterIndex = 0;
int iconId = 0;
switch (spinner4.SelectedItemPosition)
{
case 0:
filterIndex = 3;
iconSet = ExcelIconSetType.ThreeSymbols;
break;
case 1:
filterIndex = 1;
iconSet = ExcelIconSetType.FourRating;
break;
case 2:
filterIndex = 2;
iconSet = ExcelIconSetType.FiveArrows;
break;
}
switch (spinner5.SelectedItemPosition)
{
case 0:
iconId = 0;
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (spinner4.SelectedItemPosition == 0)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (spinner4.SelectedItemPosition == 1)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case 5:
iconSet = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconSet, iconId);
#endregion
break;
case 6:
#region AdvancedFilter
fileName = "AdvancedFilter.xlsx";
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (spinner1.SelectedItemPosition == 0)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, switch1.Checked);
}
else
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Syncfusion.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, switch1.Checked);
}
#endregion
break;
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (stream != null)
{
SaveAndroid androidSave = new SaveAndroid();
androidSave.Save(fileName, "application/msexcel", stream, m_context);
}
}
internal void OnButtonClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
// Initializing Workbook
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
int filterType = this.picker.SelectedIndex;
#if COMMONSB
if (filterType == 6)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.AdvancedFilterData.xlsx");
}
else if (filterType == 5)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.IconFilterData.xlsx");
}
else if (filterType == 4)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.FilterData_Color.xlsx");
}
else
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.FilterData.xlsx");
}
#else
if (filterType == 6)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.AdvancedFilterData.xlsx");
}
else if (filterType == 5)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.IconFilterData.xlsx");
}
else if (filterType == 4)
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.FilterData_Color.xlsx");
}
else
{
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.FilterData.xlsx");
}
#endif
IWorkbook workbook = application.Workbooks.Open(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
if (filterType != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (filterType)
{
case 0:
IAutoFilter filter1 = sheet.AutoFilters[0];
filter1.IsAnd = false;
filter1.FirstCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.FirstCondition.DataType = ExcelFilterDataType.String;
filter1.FirstCondition.String = "Owner";
filter1.SecondCondition.ConditionOperator = ExcelFilterCondition.Equal;
filter1.SecondCondition.DataType = ExcelFilterDataType.String;
filter1.SecondCondition.String = "Sales Representative";
break;
case 1:
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
IAutoFilter filter3 = sheet.AutoFilters[1];
filter3.AddDateFilter(new DateTime(2004, 9, 1, 1, 0, 0, 0), DateTimeGroupingType.month);
filter3.AddDateFilter(new DateTime(2011, 1, 1, 1, 0, 0, 0), DateTimeGroupingType.year);
break;
case 3:
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
// ColorFilter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Syncfusion.Drawing.Color color = Syncfusion.Drawing.Color.Empty;
switch (ColorsList.SelectedIndex)
{
case 0:
color = Syncfusion.Drawing.Color.Red;
break;
case 1:
color = Syncfusion.Drawing.Color.Blue;
break;
case 2:
color = Syncfusion.Drawing.Color.Green;
break;
case 3:
color = Syncfusion.Drawing.Color.Yellow;
break;
case 4:
color = Syncfusion.Drawing.Color.Empty;
break;
}
if (ColorFilterType.SelectedIndex == 0)
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
break;
case 5:
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconSet = ExcelIconSetType.FiveArrows;
int filterIndex = 0;
int iconId = 0;
switch (IconSetList.SelectedIndex)
{
case 0:
filterIndex = 3;
iconSet = ExcelIconSetType.ThreeSymbols;
break;
case 1:
filterIndex = 1;
iconSet = ExcelIconSetType.FourRating;
break;
case 2:
filterIndex = 2;
iconSet = ExcelIconSetType.FiveArrows;
break;
}
switch (IconIdList.SelectedIndex)
{
case 0:
iconId = 0;
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (IconSetList.SelectedIndex == 0)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (IconSetList.SelectedIndex == 1)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case 5:
iconSet = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconSet, iconId);
break;
case 6:
// AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (Advanced.SelectedIndex == 0)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, Switch1.IsToggled);
}
else
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Syncfusion.Drawing.Color.FromArgb(0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, Switch1.IsToggled);
}
break;
}
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Filters.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("Filters.xlsx", "application/msexcel", stream);
}
}
/// <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);
}
}
