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(); }
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 }
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 }
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; MemoryStream result = new MemoryStream(); workbook.SaveAs(result); result.Position = 0; return(File(result, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName)); //Close the workbook. workbook.Close(); excelEngine.Dispose(); return(View()); } }
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); } }
/// <summary> /// Apply the different types of data filter /// </summary> /// <param name="FilterType">Filter Type index</param> /// <param name="AdvancedFilterIndex">Advanced Filter Type index</param> /// <param name="isUniqueRecords">Boolean value indicates whether it is unique or not.</param> private void ApplyFilter(int FilterType, int AdvancedFilterIndex, bool isUniqueRecords) { Assembly assembly = typeof(App).GetTypeInfo().Assembly; Stream fileStream = null; if (FilterType != 4) { fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.FilterData.xlsx"); } else { fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Template.AdvancedFilterData.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 = application.Workbooks.Open(fileStream); //Access the first worksheet IWorksheet sheet = workbook.Worksheets[0]; //Set the range to be filtered if (FilterType != 4) { sheet.AutoFilters.FilterRange = sheet.Range[1, 1, 49, 3]; } switch (FilterType) { case 0: //Applying custom filter 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: //Applying text filter IAutoFilter filter2 = sheet.AutoFilters[0]; filter2.AddTextFilter(new string[] { "Owner", "Sales Representative", "Sales Associate" }); break; case 2: //Applying datetime filter 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: //Applying datetime filter IAutoFilter filter4 = sheet.AutoFilters[1]; filter4.AddDynamicFilter(DynamicFilterType.Quarter1); break; case 4: //Accessing the range to be filtered to perform advanced filter IRange filterRange = sheet.Range["A8:G51"]; //Accessing the range to be used as criteria to perform advanced filter IRange criteriaRange = sheet.Range["A2:B5"]; if (AdvancedFilterIndex == 0) { //Apply advanced filter and leave the filtered data in place sheet.AdvancedFilter(ExcelFilterAction.FilterInPlace, filterRange, criteriaRange, null, isUniqueRecords); } 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"]; //Apply advanced filter and copy the filtered data to new place sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyRange, isUniqueRecords); } break; } //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("Filters.xlsx", "application/msexcel", stream); } else { Xamarin.Forms.DependencyService.Get <ISave>().Save("Filters.xlsx", "application/msexcel", stream); } }
void OnButtonClicked(object sender, EventArgs e) { ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; #region Initializing Workbook Assembly assembly = typeof(App).GetTypeInfo().Assembly; Stream fileStream = null; int FilterType = this.picker.SelectedIndex; string resourcePath = ""; #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]; #endregion 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: #region 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); } #endregion 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: #region 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); } #endregion break; } workbook.Version = ExcelVersion.Excel2013; MemoryStream stream = new MemoryStream(); workbook.SaveAs(stream); workbook.Close(); excelEngine.Dispose(); if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows) { Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("Filters.xlsx", "application/msexcel", stream); } else { Xamarin.Forms.DependencyService.Get <ISave>().Save("Filters.xlsx", "application/msexcel", stream); } }