Filters

Filters (2)
The sample demonstrates how to use Excel Filter function in Silverlight via Spire.XLS.
<Application xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" x:Class="ExcelFilter.App"> <Application.Resources> </Application.Resources> </Application>
using System; using System.Windows; namespace ExcelFilter { public partial class App : Application { public App() { this.Startup += this.Application_Startup; this.Exit += this.Application_Exit; this.UnhandledException += this.Application_UnhandledException; InitializeComponent(); } private void Application_Startup(object sender, StartupEventArgs e) { this.RootVisual = new MainPage(); } private void Application_Exit(object sender, EventArgs e) { } private void Application_UnhandledException(object sender, ApplicationUnhandledExceptionEventArgs e) { // If the app is running outside of the debugger then report the exception using // the browser's exception mechanism. On IE this will display it a yellow alert // icon in the status bar and Firefox will display a script error. if (!System.Diagnostics.Debugger.IsAttached) { // NOTE: This will allow the application to continue running after an exception has been thrown // but not handled. // For production applications this error handling should be replaced with something that will // report the error to the website and stop the application. e.Handled = true; Deployment.Current.Dispatcher.BeginInvoke(delegate { ReportErrorToDOM(e); }); } } private void ReportErrorToDOM(ApplicationUnhandledExceptionEventArgs e) { try { string errorMsg = e.ExceptionObject.Message + e.ExceptionObject.StackTrace; errorMsg = errorMsg.Replace('"', '\'').Replace("\r\n", @"\n"); System.Windows.Browser.HtmlPage.Window.Eval("throw new Error(\"Unhandled Error in Silverlight Application " + errorMsg + "\");"); } catch (Exception) { } } } }
Imports System.Windows Namespace ExcelFilter Partial Public Class App Inherits Application Public Sub New() AddHandler Me.Startup, AddressOf Application_Startup AddHandler Me.Exit, AddressOf Application_Exit AddHandler Me.UnhandledException, AddressOf Application_UnhandledException InitializeComponent() End Sub Private Sub Application_Startup(ByVal sender As Object, ByVal e As StartupEventArgs) Me.RootVisual = New MainPage() End Sub Private Sub Application_Exit(ByVal sender As Object, ByVal e As EventArgs) End Sub Private Sub Application_UnhandledException(ByVal sender As Object, ByVal e As ApplicationUnhandledExceptionEventArgs) ' If the app is running outside of the debugger then report the exception using ' the browser's exception mechanism. On IE this will display it a yellow alert ' icon in the status bar and Firefox will display a script error. If Not Debugger.IsAttached Then ' NOTE: This will allow the application to continue running after an exception has been thrown ' but not handled. ' For production applications this error handling should be replaced with something that will ' report the error to the website and stop the application. e.Handled = True Deployment.Current.Dispatcher.BeginInvoke(Sub() ReportErrorToDOM(e)) End If End Sub Private Sub ReportErrorToDOM(ByVal e As ApplicationUnhandledExceptionEventArgs) Try Dim errorMsg As String = e.ExceptionObject.Message + e.ExceptionObject.StackTrace errorMsg = errorMsg.Replace(""""c, "'"c).Replace(vbCrLf, vbLf) System.Windows.Browser.HtmlPage.Window.Eval("throw new Error(""Unhandled Error in Silverlight Application " & errorMsg & """);") Catch e1 As Exception End Try End Sub End Class End Namespace
<UserControl x:Class="ExcelFilter.MainPage" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="d" d:DesignHeight="328" d:DesignWidth="400"> <Grid x:Name="LayoutRoot" Background="#FFB4FFFF" Loaded="LayoutRoot_Loaded" Width="414" Height="331"> <Grid.ColumnDefinitions> <ColumnDefinition Width="176*" /> <ColumnDefinition Width="224*" /> </Grid.ColumnDefinitions> <Button Content="Run" Height="23" HorizontalAlignment="Left" Margin="70,234,0,0" Name="button1" VerticalAlignment="Top" Width="75" Grid.Column="1" Click="button1_Click" /> </Grid> </UserControl>
using System; using System.Windows; using System.Windows.Controls; using System.IO; using System.Reflection; using Spire.Xls; namespace ExcelFilter { public partial class MainPage : UserControl { private Workbook workbook = null; private SaveFileDialog saveFileDialog = null; public MainPage() { InitializeComponent(); this.workbook = new Workbook(); this.saveFileDialog = new SaveFileDialog(); this.saveFileDialog.Filter = "Excel Document(*.xls)|*.xls"; } private void LayoutRoot_Loaded(object sender, RoutedEventArgs e) { //load the workbook using a stream Assembly assembly = this.GetType().Assembly; foreach (String name in assembly.GetManifestResourceNames()) { if (name.EndsWith("parts.xls")) { using (Stream docStream = assembly.GetManifestResourceStream(name)) { this.workbook.LoadFromStream(docStream, ExcelVersion.Version97to2003); } } } } private void button1_Click(object sender, RoutedEventArgs e) { Worksheet sheet = this.workbook.Worksheets[0]; sheet.AutoFilters.Range = sheet.Range["A1:D1"]; //save the workbook bool? result = this.saveFileDialog.ShowDialog(); if (result.HasValue && result.Value) { using (Stream stream = this.saveFileDialog.OpenFile()) { this.workbook.SaveToStream(stream); } } } } }
Imports System.Windows Imports System.Windows.Controls Imports System.IO Imports System.Reflection Imports Spire.Xls Namespace ExcelFilter Partial Public Class MainPage Inherits UserControl Private workbook As Workbook = Nothing Private saveFileDialog As SaveFileDialog = Nothing Public Sub New() InitializeComponent() Me.workbook = New Workbook() Me.saveFileDialog = New SaveFileDialog() Me.saveFileDialog.Filter = "Excel Document(*.xls)|*.xls" End Sub Private Sub LayoutRoot_Loaded(ByVal sender As Object, ByVal e As RoutedEventArgs) 'load the workbook using a stream Dim [assembly] As System.Reflection.Assembly = Me.GetType().Assembly For Each name As String In [assembly].GetManifestResourceNames() If name.EndsWith("parts.xls") Then Using docStream As Stream = [assembly].GetManifestResourceStream(name) Me.workbook.LoadFromStream(docStream, ExcelVersion.Version97to2003) End Using End If Next name End Sub Private Sub button1_Click(ByVal sender As Object, ByVal e As RoutedEventArgs) Dim sheet As Worksheet = Me.workbook.Worksheets(0) sheet.AutoFilters.Range = sheet.Range("A1:D1") 'save the workbook Dim result? As Boolean = Me.saveFileDialog.ShowDialog() If result.HasValue AndAlso result.Value Then Using stream As Stream = Me.saveFileDialog.OpenFile() Me.workbook.SaveToStream(stream) End Using End If End Sub End Class End Namespace
Published in
Filters
Tagged under
The sample demonstrates how to create auto filters in an excel workbook.

private void btnRun_Click(object sender, System.EventArgs e) { Workbook workbook = new Workbook(); //Initialize worksheet workbook.CreateEmptySheets(1); Worksheet sheet = workbook.Worksheets[0]; sheet.GridLinesVisible = false; //Writes filter data CreateFilterData(sheet); sheet.AutoFilters.Range = sheet.Range["A1:C1"]; workbook.SaveToFile("Sample.xls"); ExcelDocViewer(workbook.FileName); } private void CreateFilterData(Worksheet sheet) { //Country sheet.Range["A1"].Value = "Country"; sheet.Range["A2"].Value = "Cuba"; sheet.Range["A3"].Value = "Mexico"; sheet.Range["A4"].Value = "France"; sheet.Range["A5"].Value = "German"; //Jun sheet.Range["B1"].Value = "Jun"; sheet.Range["B2"].NumberValue = 6000; sheet.Range["B3"].NumberValue = 8000; sheet.Range["B4"].NumberValue = 9000; sheet.Range["B5"].NumberValue = 8500; //Aug sheet.Range["C1"].Value = "Aug"; sheet.Range["C2"].NumberValue = 3000; sheet.Range["C3"].NumberValue = 2000; sheet.Range["C4"].NumberValue = 2300; sheet.Range["C5"].NumberValue = 4200; //Style sheet.Range["A1:C1"].Style.Font.IsBold = true; sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow; sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1; sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange; sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise; //Border sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin; sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin; sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin; sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin; sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0"; } private void ExcelDocViewer( string fileName ) { try { System.Diagnostics.Process.Start(fileName); } catch{} }
Private Sub btnRun_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRun.Click Dim workbook As Workbook = New Workbook() 'Initialize worksheet workbook.CreateEmptySheets(1) Dim sheet As Worksheet = workbook.Worksheets(0) sheet.GridLinesVisible = False 'Writes filter data CreateFilterData(sheet) sheet.AutoFilters.Range = sheet.Range("A1:C1") workbook.SaveToFile("Sample.xls") ExcelDocViewer(workbook.FileName) End Sub Private Sub CreateFilterData(ByVal sheet As Worksheet) 'Country sheet.Range("A1").Value = "Country" sheet.Range("A2").Value = "Cuba" sheet.Range("A3").Value = "Mexico" sheet.Range("A4").Value = "France" sheet.Range("A5").Value = "German" 'Jun sheet.Range("B1").Value = "Jun" sheet.Range("B2").NumberValue = 6000 sheet.Range("B3").NumberValue = 8000 sheet.Range("B4").NumberValue = 9000 sheet.Range("B5").NumberValue = 8500 'Aug sheet.Range("C1").Value = "Aug" sheet.Range("C2").NumberValue = 3000 sheet.Range("C3").NumberValue = 2000 sheet.Range("C4").NumberValue = 2300 sheet.Range("C5").NumberValue = 4200 'Style sheet.Range("A1:C1").Style.Font.IsBold = True sheet.Range("A2:C2").Style.KnownColor = ExcelColors.LightYellow sheet.Range("A3:C3").Style.KnownColor = ExcelColors.LightGreen1 sheet.Range("A4:C4").Style.KnownColor = ExcelColors.LightOrange sheet.Range("A5:C5").Style.KnownColor = ExcelColors.LightTurquoise 'Border sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128) sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128) sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128) sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128) sheet.Range("A1:C5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin sheet.Range("B2:C5").Style.NumberFormat = """$""#,##0" End Sub Private Sub ExcelDocViewer(ByVal fileName As String) Try System.Diagnostics.Process.Start(fileName) Catch End Try End Sub