SortAndFilter.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' Sort and Filter: the workbook code is in App_Code\SortAndFilterDemo.vb.
''' </summary>
Partial Class SortAndFilterPage
Inherits Page
' AutoEventWireup="true" in the page directive connects Page_Load to the Load event
Protected Sub Page_Load(sender As Object, e As EventArgs)
End Sub
Protected Sub buttonCreate_Click(sender As Object, e As EventArgs)
Dim format As ExcelWorkbookFormat = If(radioXlsx.Checked, ExcelWorkbookFormat.Xlsx_2007, ExcelWorkbookFormat.Xls_2003)
Dim workbookBytes As Byte()
Try
' create the workbook in memory; the input files are in the folders of the web site
Using stream As New MemoryStream()
SortAndFilterDemo.CreateWorkbook(format, Server.MapPath("~"), stream)
workbookBytes = stream.ToArray()
End Using
Catch ex As Exception
labelError.Text = "The workbook could not be created: " & Server.HtmlEncode(ex.Message)
Return
End Try
WorkbookDownload.Send(Response, workbookBytes, SortAndFilterDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/SortAndFilterDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Sorts a sales table by two columns and adds an AutoFilter to a copy of it: the rows are sorted by region and
''' then by the total, from the largest; the AutoFilter shows only the products of two regions with a total of at
''' least 2,500. Excel shows the filter buttons in the header row, so the user can change the filters.
''' </summary>
Public Module SortAndFilterDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "SortAndFilter"
''' <summary>
''' Creates the workbook in the given format and saves it to the output stream.
''' </summary>
''' <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
''' <param name="inputFolder">The folder that contains the Images folder of the sample.</param>
''' <param name="output">The stream that receives the workbook.</param>
Public Sub CreateWorkbook(format As ExcelWorkbookFormat, inputFolder As String, output As Stream)
Dim workbook As New ExcelWorkbook(format)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Sort and filter"
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET"
Dim logo As Image = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"))
Try
Dim titleStyle As ExcelCellStyle = DemoLayout.AddTitleStyle(workbook)
Dim sectionStyle As ExcelCellStyle = DemoLayout.AddSectionStyle(workbook)
' the first worksheet: the table sorted by region, then by total from the largest
Dim sorted As ExcelWorksheet = workbook.Worksheets(0)
sorted.Name = "Sorted"
DemoLayout.SetupPage(sorted, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(sorted, "A2:G3", "Sorting a Range by Two Columns", titleStyle)
DemoLayout.WriteSection(sorted, "A5:G5", "Sorted by Region (A to Z), then by Total (largest first).", sectionStyle)
Dim sortedTable As ExcelRange = DemoLayout.WriteSalesTable(sorted, 6, 1)
' the Total column has formulas: calculate their results, which are the values that are sorted
workbook.UpdateFormulaResult()
' the column indexes are relative to the range: 2 is Region, 7 is Total;
' the first row of the range is the header row and stays in place
sortedTable.Sort(New ExcelSortKey() {
New ExcelSortKey(2, ExcelSortOrder.Ascending),
New ExcelSortKey(7, ExcelSortOrder.Descending)}, True)
' the second worksheet: an AutoFilter with a filter on two columns
Dim filtered As ExcelWorksheet = workbook.Worksheets.AddWorksheet("Filtered")
DemoLayout.SetupPage(filtered, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(filtered, "A2:G3", "Filtering a Range with an AutoFilter", titleStyle)
DemoLayout.WriteSection(filtered, "A5:G5", "Region North or South, and a Total of at least 2,500. Use the buttons to change the filters.", sectionStyle)
Dim filteredTable As ExcelRange = DemoLayout.WriteSalesTable(filtered, 6, 1)
' the filter on the Total column compares the formula results
workbook.UpdateFormulaResult()
Dim autoFilter As ExcelAutoFilter = filteredTable.AddAutoFilter()
' only the North and South regions
autoFilter.FilterByValues(2, New String() {"North", "South"})
' and a total greater than or equal to 2500; the hidden rows are the rows that do not meet both filters
autoFilter.FilterByCondition(7, ExcelAutoFilterOperator.GreaterThanOrEqualTo, 2500)
' the third worksheet: a filter with two text conditions
Dim textFilter As ExcelWorksheet = workbook.Worksheets.AddWorksheet("Text Filter")
DemoLayout.SetupPage(textFilter, logo, ExcelPageOrientation.Landscape)
DemoLayout.WriteTitle(textFilter, "A2:G3", "Filtering by Text Conditions", titleStyle)
DemoLayout.WriteSection(textFilter, "A5:G5", "Products that begin with ""PDF"" and do not contain ""Split"".", sectionStyle)
Dim textTable As ExcelRange = DemoLayout.WriteSalesTable(textFilter, 6, 1)
textTable.AddAutoFilter().FilterByConditions(1, ExcelAutoFilterOperator.BeginsWith, "PDF", True,
ExcelAutoFilterOperator.DoesNotContain, "Split")
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
End Module