SortAndFilter.aspx.cs
using System;
using System.Data;
using System.IO;
using System.Web.UI;
using ExpertXls.ExcelLib;
using ExcelLibraryDemos;
/// <summary>
/// Sort and Filter: the workbook code is in App_Code\SortAndFilterDemo.cs.
/// </summary>
public partial class SortAndFilterPage : Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void buttonCreate_Click(object sender, EventArgs e)
{
ExcelWorkbookFormat format = radioXlsx.Checked ? ExcelWorkbookFormat.Xlsx_2007 : ExcelWorkbookFormat.Xls_2003;
byte[] workbookBytes;
try
{
// create the workbook in memory; the input files are in the folders of the web site
using (MemoryStream stream = new MemoryStream())
{
SortAndFilterDemo.CreateWorkbook(format, Server.MapPath("~"), stream);
workbookBytes = stream.ToArray();
}
}
catch (Exception ex)
{
labelError.Text = "The workbook could not be created: " + Server.HtmlEncode(ex.Message);
return;
}
WorkbookDownload.Send(Response, workbookBytes, SortAndFilterDemo.OutputName + DemoLayout.GetExtension(format), format);
}
}
App_Code/SortAndFilterDemo.cs
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <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 static class SortAndFilterDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "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 static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
ExcelWorkbook workbook = new ExcelWorkbook(format);
// workbook.LicenseKey = "your license key";
workbook.DocumentProperties.Subject = "Sort and filter";
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";
Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
try
{
ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);
// the first worksheet: the table sorted by region, then by total from the largest
ExcelWorksheet sorted = 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);
ExcelRange sortedTable = 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
ExcelWorksheet filtered = 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);
ExcelRange filteredTable = DemoLayout.WriteSalesTable(filtered, 6, 1);
// the filter on the Total column compares the formula results
workbook.UpdateFormulaResult();
ExcelAutoFilter autoFilter = 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
ExcelWorksheet textFilter = 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);
ExcelRange textTable = DemoLayout.WriteSalesTable(textFilter, 6, 1);
textTable.AddAutoFilter().FilterByConditions(1, ExcelAutoFilterOperator.BeginsWith, "PDF", true,
ExcelAutoFilterOperator.DoesNotContain, "Split");
workbook.Save(output);
}
finally
{
workbook.Close();
logo.Dispose();
}
}
}
}