public ActionResult AutoFilter(string id, string FilterType, string button, string colorsList, string rdb1, string rdb3, string iconText, string iconSetTypeList, string field, string checkbox)
{
if (FilterType == null)
{
ViewBag.datasource = AutoFilterIconList.GetSymbols();
ViewBag.datasource2 = AutoFilterIconList.GetRating();
ViewBag.datasource3 = AutoFilterIconList.GetArrows();
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;
IWorkbook workbook;
if (FilterType == "Advanced Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"AdvancedFilterData.xlsx"), ExcelOpenType.Automatic);
}
else if (FilterType == "Icon Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"IconFilterData.xlsx"), ExcelOpenType.Automatic);
}
else if (FilterType == "Color Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"FilterData_Color.xlsx"), ExcelOpenType.Automatic);
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"FilterData.xlsx"), ExcelOpenType.Automatic);
}
return(excelEngine.SaveAsActionResult(workbook, "InputTempalte.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel97));
}
else
{
string fileName = null;
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (FilterType == "Advanced Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"AdvancedFilterData.xlsx"), ExcelOpenType.Automatic);
}
else if (FilterType == "Icon Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"IconFilterData.xlsx"), ExcelOpenType.Automatic);
}
else if (FilterType == "Color Filter")
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"FilterData_Color.xlsx"), ExcelOpenType.Automatic);
}
else
{
workbook = application.Workbooks.Open(ResolveApplicationDataPath(@"FilterData.xlsx"), ExcelOpenType.Automatic);
}
IWorksheet sheet = workbook.Worksheets[0];
if (FilterType != "Advanced Filter")
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (FilterType)
{
case "Custom Filter":
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 "Text Filter":
fileName = "TextFilter.xlsx";
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case "DateTime Filter":
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 "Dynamic Filter":
fileName = "DynamicFilter.xlsx";
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case "Color Filter":
fileName = "ColorFilter.xlsx";
#region ColorFilter
System.Drawing.Color color = System.Drawing.Color.Empty;
switch (colorsList.ToLower())
{
case "red":
color = System.Drawing.Color.Red;
break;
case "blue":
color = System.Drawing.Color.Blue;
break;
case "green":
color = System.Drawing.Color.Green;
break;
case "yellow":
color = System.Drawing.Color.Yellow;
break;
case "empty":
color = System.Drawing.Color.Empty;
break;
}
if (rdb3 == "FontColor")
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
#endregion
break;
case "Icon Filter":
fileName = "IconFilter.xlsx";
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
int filterIndex = 0;
ExcelIconSetType iconset = ExcelIconSetType.FiveArrows;
int iconId = 0;
switch (iconSetTypeList)
{
case "ThreeSymbols":
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case "FourRating":
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case "FiveArrows":
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (iconText)
{
case "0":
//Do nothing
break;
case "1":
iconId = 1;
break;
case "2":
iconId = 2;
break;
case "3":
if (iconSetTypeList.Equals("ThreeSymbols"))
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case "4":
if (iconSetTypeList.Equals("FourRating"))
{
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 "Advanced Filter":
fileName = "AdvancedFilter.xlsx";
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdb1 == "FilterIN")
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, checkbox == "Unique");
}
else if (rdb1 == "FilterCopy")
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = System.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, checkbox == "Unique");
}
break;
#endregion
}
workbook.Version = ExcelVersion.Excel2016;
return(excelEngine.SaveAsActionResult(workbook, fileName, HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2016));
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
adapter.Dispose();
connection.Close();
return(View());
}
}
public ActionResult AutoFilter(string id, string FilterType, string button, string colorsList, string rdb1, string rdb3, string iconText, string iconSetTypeList, string field, string checkbox)
{
string basePath = _hostingEnvironment.WebRootPath;
if (FilterType == null)
{
ViewBag.datasource = icons.GetSymbols();
ViewBag.datasource2 = icons.GetRating();
ViewBag.datasource3 = icons.GetArrows();
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;
IWorkbook workbook;
if (FilterType == "Advanced Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/AdvancedFilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else if (FilterType == "Color Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/FilterData_Color.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else if (FilterType == "Icon Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/IconFilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/FilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/msexcel", "InputTemplate.xlsx"));
}
else
{
string fileName = null;
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
IWorkbook workbook;
if (FilterType == "Advanced Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/AdvancedFilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else if (FilterType == "Color Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/FilterData_Color.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else if (FilterType == "Icon Filter")
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/IconFilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
else
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/FilterData.xlsx", FileMode.Open, FileAccess.Read);
workbook = application.Workbooks.Open(inputStream);
}
IWorksheet sheet = workbook.Worksheets[0];
if (FilterType != "Advanced Filter")
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (FilterType)
{
case "Custom Filter":
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 "Text Filter":
fileName = "TextFilter.xlsx";
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case "DateTime Filter":
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 "Dynamic Filter":
fileName = "DynamicFilter.xlsx";
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case "Color Filter":
fileName = "ColorFilter.xlsx";
#region ColorFilter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Syncfusion.Drawing.Color color = Syncfusion.Drawing.Color.Empty;
switch (colorsList.ToLower())
{
case "red":
color = Color.Red;
break;
case "blue":
color = Color.Blue;
break;
case "green":
color = Color.Green;
break;
case "yellow":
color = Color.Yellow;
break;
case "empty":
color = Color.Empty;
break;
}
if (rdb3 == "FontColor")
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
#endregion
break;
case "Icon Filter":
fileName = "IconFilter.xlsx";
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
int filterIndex = 0;
ExcelIconSetType iconset = ExcelIconSetType.FiveArrows;
int iconId = 0;
switch (iconSetTypeList)
{
case "ThreeSymbols":
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case "FourRating":
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case "FiveArrows":
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (iconText)
{
case "0":
//Do nothing
break;
case "1":
iconId = 1;
break;
case "2":
iconId = 2;
break;
case "3":
if (iconSetTypeList.Equals("ThreeSymbols"))
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case "4":
if (iconSetTypeList.Equals("FourRating"))
{
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 "Advanced Filter":
fileName = "AdvancedFilter.xlsx";
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdb1 == "FilterIN")
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, checkbox == "Unique");
}
else if (rdb1 == "FilterCopy")
{
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, checkbox == "Unique");
}
break;
#endregion
}
workbook.Version = ExcelVersion.Excel2016;
try
{
MemoryStream result = new MemoryStream();
workbook.SaveAs(result);
result.Position = 0;
return(File(result, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
catch (Exception)
{
}
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
return(View());
}
}
private void FilterData(string outFileName)
{
IWorkbook book;
if (cmbAlgorithm.SelectedIndex == 6)
{
book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\AdvancedFilterData.xlsx", ExcelOpenType.Automatic);
}
else if (cmbAlgorithm.SelectedIndex == 5)
{
book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\IconFilterData.xlsx", ExcelOpenType.Automatic);
}
else if (cmbAlgorithm.SelectedIndex == 4)
{
book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\FilterData_Color.xlsx", ExcelOpenType.Automatic);
}
else
{
book = excelEngine.Excel.Workbooks.Open(@"..\..\..\..\..\..\..\Common\Data\XlsIO\FilterData.xlsx", ExcelOpenType.Automatic);
}
IWorksheet sheet = book.Worksheets[0];
if (cmbAlgorithm.SelectedIndex != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
string sortOn = cmbAlgorithm.SelectedValue as string;
int columnIndex = GetSelectedIndex(sortOn);
switch (columnIndex)
{
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:
#region Color Filter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
System.Drawing.Color color = System.Drawing.Color.Empty;
switch (comboBox.SelectedIndex)
{
case 0:
color = System.Drawing.Color.Red;
break;
case 1:
color = System.Drawing.Color.Blue;
break;
case 2:
color = System.Drawing.Color.Green;
break;
case 3:
color = System.Drawing.Color.Yellow;
break;
case 4:
//Do nothing.
break;
}
if (rdb3.IsChecked == true)
{
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:
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconset = ExcelIconSetType.ThreeSymbols;
int iconId = 0;
int filterIndex = 0;
switch (iconSetType.SelectedIndex)
{
case 0:
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case 1:
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case 2:
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (iconIdComboBox.SelectedIndex)
{
case 0:
//Do nothing
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (iconSetType.SelectedIndex == 0)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (iconSetType.SelectedIndex == 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
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdb1.IsChecked == true)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, checkBox.IsChecked == true);
}
else if (rdb2.IsChecked == true)
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = System.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, checkBox.IsChecked == true);
}
#endregion
break;
}
book.SaveAs(outFileName);
book.Close();
}
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);
}
}
private async void btnGenerateExcel_Click_2(object sender, RoutedEventArgs e)
{
#region Workbook initialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(Filters).GetTypeInfo().Assembly;
string resourcePath;
if (comboBox1.SelectedIndex == 6)
{
resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.AdvancedFilterTemplate.xlsx";
}
else if (comboBox1.SelectedIndex == 5)
{
resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.IconFilterData.xlsx";
}
else if (comboBox1.SelectedIndex == 4)
{
resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.FilterData_Color.xlsx";
}
else
{
resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.FilterData.xlsx";
}
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
#endregion
#region sorting the data
workbook.Version = ExcelVersion.Excel2007;
IWorksheet sheet = workbook.Worksheets[0];
if (comboBox1.SelectedIndex != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
string sortOn = comboBox1.SelectedValue as string;
int columnIndex = GetSelectedIndex(sortOn);
string fileName = "DataSortSample";
switch (columnIndex)
{
case 0:
fileName = "CustomFilter";
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";
IAutoFilter filter2 = sheet.AutoFilters[0];
filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" });
break;
case 2:
fileName = "DateTimeFilter";
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";
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
case 4:
fileName = "ColorFilter";
#region ColorFilter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Windows.UI.Color color = new Windows.UI.Color();
switch (comboBox2.SelectedIndex)
{
case 0:
color = Windows.UI.Color.FromArgb(255, 255, 0, 0);
break;
case 1:
color = Windows.UI.Color.FromArgb(255, 0, 0, 255);
break;
case 2:
color = Windows.UI.Color.FromArgb(255, 0, 128, 0);
break;
case 3:
color = Windows.UI.Color.FromArgb(255, 255, 255, 0);
break;
case 4:
//do nothing
break;
}
if (rdb3.IsChecked == true)
{
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";
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconSet = ExcelIconSetType.FiveArrows;
int icon = 0;
int filterIndex = 0;
switch (iconSetType.SelectedIndex)
{
case 0:
iconSet = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case 1:
iconSet = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case 2:
iconSet = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (iconId.SelectedIndex)
{
case 0:
//Do nothing
break;
case 1:
icon = 1;
break;
case 2:
icon = 2;
break;
case 3:
if (iconSetType.SelectedIndex == 0)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
icon = 3;
}
break;
case 4:
if (iconSetType.SelectedIndex == 1)
{
iconSet = (ExcelIconSetType)(-1);
}
else
{
icon = 4;
}
break;
case 5:
iconSet = (ExcelIconSetType)(-1);
break;
}
IAutoFilter filter5 = sheet.AutoFilters[filterIndex];
filter5.AddIconFilter(iconSet, icon);
#endregion
break;
case 6:
fileName = "AdvancedFilter";
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdb1.IsChecked == true)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, checkbox1.IsChecked == true);
}
else if (rdb2.IsChecked == true)
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = Windows.UI.Color.FromArgb(0, 0, 112, 192);
range.HorizontalAlignment = ExcelHAlign.HAlignCenter;
range.CellStyle.Font.Bold = true;
IRange copyRange = sheet.Range["I8"];
sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, checkbox1.IsChecked == true);
}
break;
#endregion
}
#endregion
#region Save the Workbook
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = fileName;
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(fileName + ".xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile != null)
{
//Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been saved 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);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
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);
}
}
private void FilterData(string outFileName)
{
#region Workbook Initialize
//Get the path of the input file
string inputPath = GetFullTemplatePath(outFileName);
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath, ExcelOpenType.Automatic);
IWorksheet sheet = workbook.Worksheets[0];
#endregion
int columnIndex = cmbFirst.SelectedIndex;
if (columnIndex != 6)
{
sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3];
}
switch (columnIndex)
{
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 4:
#region Color Filter
sheet.AutoFilters.FilterRange = sheet["A1:C49"];
Color color = Color.Empty;
switch (comboBox1.SelectedIndex)
{
case 0:
color = Color.Red;
break;
case 1:
color = Color.Blue;
break;
case 2:
color = Color.Green;
break;
case 3:
color = Color.Yellow;
break;
case 4:
//Do nothing.
break;
}
if (radioButton3.Checked)
{
IAutoFilter filter = sheet.AutoFilters[0];
filter.AddColorFilter(color, ExcelColorFilterType.CellColor);
}
else
{
IAutoFilter filter = sheet.AutoFilters[2];
filter.AddColorFilter(color, ExcelColorFilterType.FontColor);
}
#endregion
break;
case 5:
#region IconFilter
sheet.AutoFilters.FilterRange = sheet["A4:D44"];
ExcelIconSetType iconset = ExcelIconSetType.ThreeSymbols;
int iconId = 0;
int filterIndex = 0;
bool IdNotSelected = false;
switch (this.comboBox3.SelectedIndex)
{
case 0:
iconset = ExcelIconSetType.ThreeSymbols;
filterIndex = 3;
break;
case 1:
iconset = ExcelIconSetType.FourRating;
filterIndex = 1;
break;
case 2:
iconset = ExcelIconSetType.FiveArrows;
filterIndex = 2;
break;
}
switch (this.comboBoxAdv1.SelectedIndex)
{
case 0:
//Do nothing
break;
case 1:
iconId = 1;
break;
case 2:
iconId = 2;
break;
case 3:
if (comboBox3.SelectedIndex == 0)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 3;
}
break;
case 4:
if (comboBox3.SelectedIndex == 1)
{
iconset = (ExcelIconSetType)(-1);
}
else
{
iconId = 4;
}
break;
case 5:
iconset = (ExcelIconSetType)(-1);
break;
default:
MessageBox.Show("Please Select an Icon!");
IdNotSelected = true;
break;
}
if (!IdNotSelected)
{
IAutoFilter filter = sheet.AutoFilters[filterIndex];
filter.AddIconFilter(iconset, iconId);
}
#endregion
break;
case 6:
#region AdvancedFilter
IRange filterRange = sheet.Range["A8:G51"];
IRange criteriaRange = sheet.Range["A2:B5"];
if (rdbFilterInPlace.Checked)
{
sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, cbisUnique.Checked);
}
else if (rdbFilterCopy.Checked)
{
IRange range = sheet.Range["I7:O7"];
range.Merge();
range.Text = "FilterCopy";
range.CellStyle.Font.RGBColor = 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, cbisUnique.Checked);
}
#endregion
break;
default:
case 3:
IAutoFilter filter4 = sheet.AutoFilters[1];
filter4.AddDynamicFilter(DynamicFilterType.Quarter1);
break;
}
#region Workbook Save and Close
workbook.SaveAs(outFileName);
workbook.Close();
#endregion
}
