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

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

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.

Take a moment to peruse the documentation where you can find basic Excel document processing options along with the features like import and export data, chart, formulas, conditional formatting, data validation, tables, pivot tables and, protect the Excel documents, and most importantly, the PDF, CSV and Image conversions with code examples.

 

Conclusion

I hope you enjoyed learning about How to create auto filters in Excel using Flutter Excel library.

You can refer to our XIsIO’s feature tour page to learn about its other groundbreaking features. Explore our UG documentation and online demos to understand how to manipulate data in Excel documents.

If you are an existing user, you can access our latest components from the License and Downloads page. For new users, you can try our 30-day free trial to check out XlsIO and other Syncfusion® components.

If you have any queries or require clarification, please let us know in the comments below or contact us through our support forums, Support Tickets, or feedback portal. We are always happy to assist you!
Attachments
Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Access denied
Access denied