My Profile
Sign Out
Tickets
Knowledge Base
Login
Articles in this section
Category / Section
  2. Flutter
  3. XlsIO

How to create auto filters in Excel using Flutter Excel library?

4 mins read

Our Syncfusion Flutter Excel Library (XlsIO) is a non-UI, reusable file-format library. It allows users to create Excel documents programmatically with text, numbers, date-time values, styles, number formats, formulas, images, charts, hyperlinks, imported data, conditional formatting, and workbook and worksheet protection. This library works in both mobile and web applications. In this article, we are going to see how to create auto filters in an Excel spreadsheet using the Flutter Excel Library.

Auto filters allow filtering data to display only rows that meet criteria specified by the user and hide rows that do not. Syncfusion Flutter Excel creation library supports different filter types such as,

  • Text filter
  • Custom filter
  • Date filter
  • Dynamic filter
  • Color filter

Let us discuss these filters in detail now.

Text Filter:

Text filter as the name says filters the rows that contain required text and is case sensitive.

final Workbook workbook = Workbook();
final Worksheet worksheet = workbook.worksheets[0];
worksheet.getRangeByName('A1').setText('Title');
worksheet.getRangeByName('A2').setText('Sales Representative');
worksheet.getRangeByName('A3').setText('Owner');
worksheet.getRangeByName('A4').setText('Owner');
worksheet.getRangeByName('A5').setText('Sales Representative');
worksheet.getRangeByName('A6').setText('Order Administrator');
worksheet.getRangeByName('A7').setText('Sales Representative');
worksheet.getRangeByName('A8').setText('Marketing Manager');
worksheet.getRangeByName('A9').setText('Owner');
worksheet.getRangeByName('A10').setText('Owner');
 
worksheet.getRangeByName('B1').setText('DOJ');
worksheet.getRangeByName('B2').dateTime = DateTime(2006, 9, 10);
worksheet.getRangeByName('B3').dateTime = DateTime(2000, 6, 10);
worksheet.getRangeByName('B4').dateTime = DateTime(2002, 9, 18);
worksheet.getRangeByName('B5').dateTime = DateTime(2009, 5, 23);
worksheet.getRangeByName('B6').dateTime = DateTime(2012, 1, 6);
worksheet.getRangeByName('B7').dateTime = DateTime(2007, 7, 19);
worksheet.getRangeByName('B8').dateTime = DateTime(2008, 6, 30);
worksheet.getRangeByName('B9').dateTime = DateTime(2002, 4, 16);
worksheet.getRangeByName('B10').dateTime = DateTime(2008, 11, 29);
 
worksheet.getRangeByName('C1').setText('City');
worksheet.getRangeByName('C2').setText('Berlin');
worksheet.getRangeByName('C3').setText('Mexico D.F.');
worksheet.getRangeByName('C4').setText('Mexico D.F.');
worksheet.getRangeByName('C5').setText('London');
worksheet.getRangeByName('C6').setText('Lulea');
worksheet.getRangeByName('C7').setText('Mannheim');
worksheet.getRangeByName('C8').setText('Strasbourg');
worksheet.getRangeByName('C9').setText('Madrid');
worksheet.getRangeByName('C10').setText('Marseille');
 
// Initialize filter range.
worksheet.autoFilters.filterRange = worksheet.getRangeByName('A1:C10');
 
// Add text filter.
final AutoFilter autofilter = worksheet.autoFilters[0];
autofilter.addTextFilter(<String>{'Owner'});
worksheet.getRangeByName('A1:C10').autoFitColumns();
 
// Save and dispose workbook.
final List<int> bytes = workbook.saveAsStream();
File('TextFilter.xlsx').writeAsBytes(bytes);
workbook.dispose();

 

Text filter

A complete working sample can be downloaded from TextFilter.zip.

Refer here for more information.

Custom Filter:

Custom filter helps to filter data that satisfies either of the given conditions.

final Workbook workbook = Workbook();
final Worksheet worksheet = workbook.worksheets[0];
worksheet.getRangeByName('A1').setText('Title');
worksheet.getRangeByName('A2').setText('Sales Representative');
worksheet.getRangeByName('A3').setText('Owner');
worksheet.getRangeByName('A4').setText('Owner');
worksheet.getRangeByName('A5').setText('Sales Representative');
worksheet.getRangeByName('A6').setText('Order Administrator');
worksheet.getRangeByName('A7').setText('Sales Representative');
worksheet.getRangeByName('A8').setText('Marketing Manager');
worksheet.getRangeByName('A9').setText('Owner');
worksheet.getRangeByName('A10').setText('Owner');
 
worksheet.getRangeByName('B1').setText('DOJ');
worksheet.getRangeByName('B2').dateTime = DateTime(2006, 9, 10);
worksheet.getRangeByName('B3').dateTime = DateTime(2000, 6, 10);
worksheet.getRangeByName('B4').dateTime = DateTime(2002, 9, 18);
worksheet.getRangeByName('B5').dateTime = DateTime(2009, 5, 23);
worksheet.getRangeByName('B6').dateTime = DateTime(2012, 1, 6);
worksheet.getRangeByName('B7').dateTime = DateTime(2007, 7, 19);
worksheet.getRangeByName('B8').dateTime = DateTime(2008, 6, 30);
worksheet.getRangeByName('B9').dateTime = DateTime(2002, 4, 16);
worksheet.getRangeByName('B10').dateTime = DateTime(2008, 11, 29);
 
worksheet.getRangeByName('C1').setText('City');
worksheet.getRangeByName('C2').setText('Berlin');
worksheet.getRangeByName('C3').setText('Mexico D.F.');
worksheet.getRangeByName('C4').setText('Mexico D.F.');
worksheet.getRangeByName('C5').setText('London');
worksheet.getRangeByName('C6').setText('Lulea');
worksheet.getRangeByName('C7').setText('Mannheim');
worksheet.getRangeByName('C8').setText('Strasbourg');
worksheet.getRangeByName('C9').setText('Madrid');
worksheet.getRangeByName('C10').setText('Marseille');
 
// Initialize filter range.
worksheet.autoFilters.filterRange = worksheet.getRangeByName('A1:C10');
final AutoFilter autofilter = worksheet.autoFilters[0];
 
// First condition.
final AutoFilterCondition firstCondition = autofilter.firstCondition;
firstCondition.conditionOperator = ExcelFilterCondition.greaterOrEqual;
firstCondition.numberValue = 10;
 
// Second condition.
final AutoFilterCondition secondCondition = autofilter.secondCondition;
secondCondition.conditionOperator = ExcelFilterCondition.less;
secondCondition.numberValue = 15;
 
// Save and dispose workbook.
final List<int> bytes = workbook.saveAsStream();
File('CustomFilter.xlsx').writeAsBytes(bytes);
workbook.dispose();

 

Custom filter in Flutter Excel Library

A complete working sample can be downloaded from CustomFilter.zip.

Refer here for more information.

Date Filter:

Date Filter filters the rows that contain specified dates.

final Workbook workbook = Workbook();
final Worksheet worksheet = workbook.worksheets[0];
worksheet.getRangeByName('A1').setText('Title');
worksheet.getRangeByName('A2').setText('Sales Representative');
worksheet.getRangeByName('A3').setText('Owner');
worksheet.getRangeByName('A4').setText('Owner');
worksheet.getRangeByName('A5').setText('Sales Representative');
worksheet.getRangeByName('A6').setText('Order Administrator');
worksheet.getRangeByName('A7').setText('Sales Representative');
worksheet.getRangeByName('A8').setText('Marketing Manager');
worksheet.getRangeByName('A9').setText('Owner');
worksheet.getRangeByName('A10').setText('Owner');
 
worksheet.getRangeByName('B1').setText('DOJ');
worksheet.getRangeByName('B2').dateTime = DateTime(2006, 9, 10);
worksheet.getRangeByName('B3').dateTime = DateTime(2000, 6, 10);
worksheet.getRangeByName('B4').dateTime = DateTime(2002, 9, 18);
worksheet.getRangeByName('B5').dateTime = DateTime(2009, 5, 23);
worksheet.getRangeByName('B6').dateTime = DateTime(2012, 1, 6);
worksheet.getRangeByName('B7').dateTime = DateTime(2007, 7, 19);
worksheet.getRangeByName('B8').dateTime = DateTime(2008, 6, 30);
worksheet.getRangeByName('B9').dateTime = DateTime(2002, 4, 16);
worksheet.getRangeByName('B10').dateTime = DateTime(2008, 11, 29);
 
worksheet.getRangeByName('C1').setText('City');
worksheet.getRangeByName('C2').setText('Berlin');
worksheet.getRangeByName('C3').setText('Mexico D.F.');
worksheet.getRangeByName('C4').setText('Mexico D.F.');
worksheet.getRangeByName('C5').setText('London');
worksheet.getRangeByName('C6').setText('Lulea');
worksheet.getRangeByName('C7').setText('Mannheim');
worksheet.getRangeByName('C8').setText('Strasbourg');
worksheet.getRangeByName('C9').setText('Madrid');
worksheet.getRangeByName('C10').setText('Marseille');
 
// Initialize filter range.
worksheet.autoFilters.filterRange = worksheet.getRangeByName('A1:C10');
 
// Add date filter.
final AutoFilter autofilter = worksheet.autoFilters[1];
autofilter.addDateFilter(DateTime(2002), DateTimeFilterType.year);
autofilter.addDateFilter(DateTime(2009, 5), DateTimeFilterType.year);
 
// Save and dispose workbook.
final List<int> bytes = workbook.saveAsStream();
File('DateFilter.xlsx').writeAsBytes(bytes);
workbook.dispose();

 

Date filter in Flutter Excel Library

A complete working sample can be downloaded from DateFilter.zip.

Refer here for more information.

Dynamic Filter:

Dynamic filter helps to filter data that satisfies the conditions based on calendar.

final Workbook workbook = Workbook();
final Worksheet worksheet = workbook.worksheets[0];
worksheet.getRangeByName('A1').setText('Title');
worksheet.getRangeByName('A2').setText('Sales Representative');
worksheet.getRangeByName('A3').setText('Owner');
worksheet.getRangeByName('A4').setText('Owner');
worksheet.getRangeByName('A5').setText('Sales Representative');
worksheet.getRangeByName('A6').setText('Order Administrator');
worksheet.getRangeByName('A7').setText('Sales Representative');
worksheet.getRangeByName('A8').setText('Marketing Manager');
worksheet.getRangeByName('A9').setText('Owner');
worksheet.getRangeByName('A10').setText('Owner');
 
worksheet.getRangeByName('B1').setText('DOJ');
worksheet.getRangeByName('B2').dateTime = DateTime(2006, 9, 10);
worksheet.getRangeByName('B3').dateTime = DateTime(2000, 6, 10);
worksheet.getRangeByName('B4').dateTime = DateTime(2002, 9, 18);
worksheet.getRangeByName('B5').dateTime = DateTime(2009, 5, 23);
worksheet.getRangeByName('B6').dateTime = DateTime(2012, 1, 6);
worksheet.getRangeByName('B7').dateTime = DateTime(2007, 7, 19);
worksheet.getRangeByName('B8').dateTime = DateTime(2008, 6, 30);
worksheet.getRangeByName('B9').dateTime = DateTime(2002, 4, 16);
worksheet.getRangeByName('B10').dateTime = DateTime(2008, 11, 29);
 
worksheet.getRangeByName('C1').setText('City');
worksheet.getRangeByName('C2').setText('Berlin');
worksheet.getRangeByName('C3').setText('Mexico D.F.');
worksheet.getRangeByName('C4').setText('Mexico D.F.');
worksheet.getRangeByName('C5').setText('London');
worksheet.getRangeByName('C6').setText('Lulea');
worksheet.getRangeByName('C7').setText('Mannheim');
worksheet.getRangeByName('C8').setText('Strasbourg');
worksheet.getRangeByName('C9').setText('Madrid');
worksheet.getRangeByName('C10').setText('Marseille');
 
// Initialize filter range.
worksheet.autoFilters.filterRange = worksheet.getRangeByName('A1:C10');
 
// Add dynamic filter.
final AutoFilter autofilter = worksheet.autoFilters[1];
autofilter.addDynamicFilter(DynamicFilterType.quarter2);
worksheet.getRangeByName('A1:C10').autoFitColumns();
 
// Save and dispose workbook.
final List<int> bytes = workbook.saveAsStream();
File('DynamicFilter.xlsx').writeAsBytes(bytes);
workbook.dispose();

 

Dynamic filter in Flutter Excel Library

A complete working sample can be downloaded from DynamicFilter.zip.

Refer here for more information.

Color Filter:

Color filter helps to filter the data based on color. There are two types of color filters that can be used namely font color filter and cell color filter.

Font Color Filter:

final Workbook workbook = Workbook();
final Worksheet worksheet = workbook.worksheets[0];
worksheet.getRangeByName('A1').setText('Title');
worksheet.getRangeByName('A2').setText('Sales Representative');
worksheet.getRangeByName('A3').setText('Owner');
worksheet.getRangeByName('A4').setText('Owner');
worksheet.getRangeByName('A5').setText('Sales Representative');
worksheet.getRangeByName('A6').setText('Order Administrator');
worksheet.getRangeByName('A7').setText('Sales Representative');
worksheet.getRangeByName('A8').setText('Marketing Manager');
worksheet.getRangeByName('A9').setText('Owner');
worksheet.getRangeByName('A10').setText('Owner');
 
worksheet.getRangeByName('B1').setText('DOJ');
worksheet.getRangeByName('B2').dateTime = DateTime(2006, 9, 10);
worksheet.getRangeByName('B3').dateTime = DateTime(2000, 6, 10);
worksheet.getRangeByName('B4').dateTime = DateTime(2002, 9, 18);
worksheet.getRangeByName('B5').dateTime = DateTime(2009, 5, 23);
worksheet.getRangeByName('B6').dateTime = DateTime(2012, 1, 6);
worksheet.getRangeByName('B7').dateTime = DateTime(2007, 7, 19);
worksheet.getRangeByName('B8').dateTime = DateTime(2008, 6, 30);
worksheet.getRangeByName('B9').dateTime = DateTime(2002, 4, 16);
worksheet.getRangeByName('B10').dateTime = DateTime(2008, 11, 29);
 
worksheet.getRangeByName('C1').setText('City');
worksheet.getRangeByName('C2').setText('Berlin');
worksheet.getRangeByName('C3').setText('Mexico D.F.');
worksheet.getRangeByName('C4').setText('Mexico D.F.');
worksheet.getRangeByName('C5').setText('London');
worksheet.getRangeByName('C6').setText('Lulea');
worksheet.getRangeByName('C7').setText('Mannheim');
worksheet.getRangeByName('C8').setText('Strasbourg');
worksheet.getRangeByName('C9').setText('Madrid');
worksheet.getRangeByName('C10').setText('Marseille');
 
worksheet.getRangeByName('A2').cellStyle.backColor = '#008000';
worksheet.getRangeByName('A3').cellStyle.backColor = '#0000FF';
worksheet.getRangeByName('A4').cellStyle.backColor = '#FF0000';
worksheet.getRangeByName('A5').cellStyle.backColor = '#FF0000';
worksheet.getRangeByName('A6').cellStyle.backColor = '#FFFFFF';
worksheet.getRangeByName('A7').cellStyle.backColor = '#FF0000';
worksheet.getRangeByName('A8').cellStyle.backColor = '#FFFFFF';
worksheet.getRangeByName('A9').cellStyle.backColor = '#0000FF';
worksheet.getRangeByName('A10').cellStyle.backColor = '#008000';
 
worksheet.getRangeByName('C2').cellStyle.fontColor = '#FF0000';
worksheet.getRangeByName('C3').cellStyle.fontColor = '#008000';
worksheet.getRangeByName('C4').cellStyle.fontColor = '#0000FF';
worksheet.getRangeByName('C5').cellStyle.fontColor = '#000000';
worksheet.getRangeByName('C6').cellStyle.fontColor = '#FF0000';
worksheet.getRangeByName('C7').cellStyle.fontColor = '#008000';
worksheet.getRangeByName('C8').cellStyle.fontColor = '#0000FF';
worksheet.getRangeByName('C9').cellStyle.fontColor = '#000000';
worksheet.getRangeByName('C10').cellStyle.fontColor = '#FF0000';
 
// Initialize filter range.
worksheet.autoFilters.filterRange = worksheet.getRangeByName('A1:C10');
 
// Add font color filter.
final AutoFilter autofilter = worksheet.autoFilters[2];
autofilter.addColorFilter('#0000FF', ExcelColorFilterType.fontColor);
 
// Save and dispose workbook.
final List<int> bytes = workbook.saveAsStream();
File('FontColorFilter.xlsx').writeAsBytes(bytes);
workbook.dispose();

 

Font color filter in Flutter Excel library

A complete working sample can be downloaded from FontColorFilter.zip.

Refer here for more information.

Cell Color Filter:

final Workbook workbook = Workbook();
final Worksheet worksheet = workbook.worksheets[0];
worksheet.getRangeByName('A1').setText('Title');
worksheet.getRangeByName('A2').setText('Sales Representative');
worksheet.getRangeByName('A3').setText('Owner');
worksheet.getRangeByName('A4').setText('Owner');
worksheet.getRangeByName('A5').setText('Sales Representative');
worksheet.getRangeByName('A6').setText('Order Administrator');
worksheet.getRangeByName('A7').setText('Sales Representative');
worksheet.getRangeByName('A8').setText('Marketing Manager');
worksheet.getRangeByName('A9').setText('Owner');
worksheet.getRangeByName('A10').setText('Owner');
 
worksheet.getRangeByName('B1').setText('DOJ');
worksheet.getRangeByName('B2').dateTime = DateTime(2006, 9, 10);
worksheet.getRangeByName('B3').dateTime = DateTime(2000, 6, 10);
worksheet.getRangeByName('B4').dateTime = DateTime(2002, 9, 18);
worksheet.getRangeByName('B5').dateTime = DateTime(2009, 5, 23);
worksheet.getRangeByName('B6').dateTime = DateTime(2012, 1, 6);
worksheet.getRangeByName('B7').dateTime = DateTime(2007, 7, 19);
worksheet.getRangeByName('B8').dateTime = DateTime(2008, 6, 30);
worksheet.getRangeByName('B9').dateTime = DateTime(2002, 4, 16);
worksheet.getRangeByName('B10').dateTime = DateTime(2008, 11, 29);
 
worksheet.getRangeByName('C1').setText('City');
worksheet.getRangeByName('C2').setText('Berlin');
worksheet.getRangeByName('C3').setText('Mexico D.F.');
worksheet.getRangeByName('C4').setText('Mexico D.F.');
worksheet.getRangeByName('C5').setText('London');
worksheet.getRangeByName('C6').setText('Lulea');
worksheet.getRangeByName('C7').setText('Mannheim');
worksheet.getRangeByName('C8').setText('Strasbourg');
worksheet.getRangeByName('C9').setText('Madrid');
worksheet.getRangeByName('C10').setText('Marseille');
 
worksheet.getRangeByName('A2').cellStyle.backColor = '#008000';
worksheet.getRangeByName('A3').cellStyle.backColor = '#0000FF';
worksheet.getRangeByName('A4').cellStyle.backColor = '#FF0000';
worksheet.getRangeByName('A5').cellStyle.backColor = '#FF0000';
worksheet.getRangeByName('A6').cellStyle.backColor = '#FFFFFF';
worksheet.getRangeByName('A7').cellStyle.backColor = '#FF0000';
worksheet.getRangeByName('A8').cellStyle.backColor = '#FFFFFF';
worksheet.getRangeByName('A9').cellStyle.backColor = '#0000FF';
worksheet.getRangeByName('A10').cellStyle.backColor = '#008000';
 
worksheet.getRangeByName('C2').cellStyle.fontColor = '#FF0000';
worksheet.getRangeByName('C3').cellStyle.fontColor = '#008000';
worksheet.getRangeByName('C4').cellStyle.fontColor = '#0000FF';
worksheet.getRangeByName('C5').cellStyle.fontColor = '#000000';
worksheet.getRangeByName('C6').cellStyle.fontColor = '#FF0000';
worksheet.getRangeByName('C7').cellStyle.fontColor = '#008000';
worksheet.getRangeByName('C8').cellStyle.fontColor = '#0000FF';
worksheet.getRangeByName('C9').cellStyle.fontColor = '#000000';
worksheet.getRangeByName('C10').cellStyle.fontColor = '#FF0000';
 
// Initialize filter range.
worksheet.autoFilters.filterRange = worksheet.getRangeByName('A1:C10');
 
// Add cell color filter.
final AutoFilter autofilter = worksheet.autoFilters[0];
autofilter.addColorFilter('#FF0000', ExcelColorFilterType.cellColor);
 
// Save and dispose workbook.
final List<int> bytes = workbook.saveAsStream();
File('CellColorFilter.xlsx').writeAsBytes(bytes);
workbook.dispose();

 

Cell color filter in Flutter Excel Library

A complete working sample can be downloaded from CellColorFilter.zip.

Refer here for more information.

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied