exceljs · guyonroche · Apr 26, 2017 · Apr 21, 2017 · Apr 25, 2017 · Apr 26, 2017
diff --git a/README.md b/README.md
@@ -60,6 +60,7 @@ To be clear, all contributions added to this library will be included in the lib
           <li><a href="#split-views">Split Views</a></li>
         </ul>
       </li>
+      <li><a href="#auto-filters">Auto Filters</a></li>
       <li><a href="#columns">Columns</a></li>
       <li><a href="#rows">Rows</a></li>
       <li><a href="#handling-individual-cells">Handling Individual Cells</a></li>
@@ -365,6 +366,41 @@ worksheet.views = [
 ];
 ```
 
+## Auto filters
+
+It is possible to apply an auto filter to your worksheet.
+
+```javascript
+// Set an auto filter from A1 to C1
+worksheet.autoFilter = {
+    from: 'A1',
+    to: 'C1',
+}
+
+// Set an auto filter from the cell in row 3 and column 1
+// to the cell in row 5 and column 12
+worksheet.autoFilter = {
+    from: {
+        row: 3,
+        column: 1
+    },
+    to: {
+        row: 5,
+        column: 12
+    }
+}
+
+// Set an auto filter from D3 to the
+// cell in row 7 and column 5
+worksheet.autoFilter = {
+    from: 'D3',
+    to: {
+        row: 7,
+        column: 5
+    }
+}
+```
+
 ## Columns
 
 ```javascript

diff --git a/lib/doc/worksheet.js b/lib/doc/worksheet.js
@@ -83,6 +83,8 @@ var Worksheet = module.exports = function(options) {
 
   // for freezepanes, split, zoom, gridlines, etc
   this.views = options.views || [];
+
+  this.autoFilter = options.autoFilter || null;
 };
 
 Worksheet.prototype = {
@@ -509,7 +511,8 @@ Worksheet.prototype = {
       dataValidations: this.dataValidations.model,
       properties: this.properties,
       pageSetup: this.pageSetup,
-      views: this.views
+      views: this.views,
+      autoFilter: this.autoFilter
     };
 
     // =================================================

diff --git a/lib/stream/xlsx/workbook-writer.js b/lib/stream/xlsx/workbook-writer.js
@@ -163,7 +163,8 @@ WorkbookWriter.prototype = {
       useSharedStrings: useSharedStrings,
       properties: options.properties,
       pageSetup: options.pageSetup,
-      views: options.views
+      views: options.views,
+      autoFilter: options.autoFilter
     });
 
     this._worksheets[id] = worksheet;

diff --git a/lib/stream/xlsx/worksheet-writer.js b/lib/stream/xlsx/worksheet-writer.js
@@ -51,6 +51,7 @@ var HyperlinkXform = require('../../xlsx/xform/sheet/hyperlink-xform');
 var SheetViewXform = require('../../xlsx/xform/sheet/sheet-view-xform');
 var PageMarginsXform = require('../../xlsx/xform/sheet/page-margins-xform');
 var PageSetupXform = require('../../xlsx/xform/sheet/page-setup-xform');
+var AutoFilterXform = require('../../xlsx/xform/sheet/auto-filter-xform');
 
 // since prepare and render is functional, we can use singletons
 var xform = {
@@ -62,7 +63,8 @@ var xform = {
   hyperlinks: new ListXform({tag: 'hyperlinks', length: false, childXform: new HyperlinkXform()}),
   sheetViews: new ListXform({tag: 'sheetViews', length: false, childXform: new SheetViewXform()}),
   pageMargins: new PageMarginsXform(),
-  pageSeteup: new PageSetupXform()
+  pageSeteup: new PageSetupXform(),
+  autoFilter: new AutoFilterXform()
 };
 
 
@@ -148,6 +150,9 @@ var WorksheetWriter = module.exports = function(options) {
   // views
   this._views = options.views || [];
 
+  // auto filter
+  this.autoFilter = options.autoFilter || null;
+
   // start writing to stream now
   this._writeOpenWorksheet();
 
@@ -195,6 +200,7 @@ WorksheetWriter.prototype = {
         this._writeOpenSheetData();
     }
     this._writeCloseSheetData();
+    this._writeAutoFilter();
     this._writeMergeCells();
 
     this._writeHyperlinks();
@@ -491,6 +497,9 @@ WorksheetWriter.prototype = {
   _writePageSetup: function() {
     this.stream.write(xform.pageSeteup.toXml(this.pageSetup));
   },
+  _writeAutoFilter: function() {
+    this.stream.write(xform.autoFilter.toXml(this.autoFilter));
+  },
   _writeDimensions: function() {
     // for some reason, Excel can't handle dimensions at the bottom of the file
     // and we don't know the dimensions until the commit, so don't write them.
@@ -499,4 +508,4 @@ WorksheetWriter.prototype = {
   _writeCloseWorksheet: function() {
     this._write('</worksheet>');
   }
-};
+};
diff --git a/lib/xlsx/xform/sheet/auto-filter-xform.js b/lib/xlsx/xform/sheet/auto-filter-xform.js
@@ -0,0 +1,75 @@
+/**
+ * Copyright (c) 2015 Guyon Roche
+ *
+ * Permission is hereby granted, free of charge, to any person obtaining a copy
+ * of this software and associated documentation files (the "Software"), to deal
+ * in the Software without restriction, including without limitation the rights
+ * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+ * copies of the Software, and to permit persons to whom the Software is
+ * furnished to do so, subject to the following conditions:
+ *
+ * The above copyright notice and this permission notice shall be included in
+ * all copies or substantial portions of the Software.
+ *
+ * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+ * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+ * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.  IN NO EVENT SHALL THE
+ * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+ * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+ * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
+ * THE SOFTWARE.
+ *
+ */
+'use strict';
+
+var utils = require('../../../utils/utils');
+var colCache = require('../../../utils/col-cache');
+var BaseXform = require('../base-xform');
+
+var AutoFilterXform = module.exports = function() {
+};
+
+utils.inherits(AutoFilterXform, BaseXform, {
+
+  get tag() { return 'autoFilter'; },
+
+  render: function(xmlStream, model) {
+    function getAddress (autoFilterObject) {
+      if (autoFilterObject) {
+        if (typeof autoFilterObject === 'string') {
+            return colCache.getAddress(autoFilterObject);
+        } else if (autoFilterObject.row && autoFilterObject.column) {
+            return colCache.getAddress(autoFilterObject.row, autoFilterObject.column);
+        }
+      }
+    }
+    if (model) {
+      var firstAddress = getAddress(model.from);
+      var secondAddress = getAddress(model.to);
+      if (firstAddress && secondAddress) {
+        xmlStream.leafNode('autoFilter', {ref: firstAddress.address + ':' + secondAddress.address});
+      }
+    }
+  },
+
+  parseOpen: function(node) {
+    if (node.name === 'autoFilter') {
+      var cells = node.attributes.ref.split(':');
+      if (cells.length === 2) {
+        var firstAddress = colCache.getAddress(cells[0]);
+        var secondAddress = colCache.getAddress(cells[1]);
+        this.model = {
+          from: {
+            row: firstAddress.row,
+            column: firstAddress.col
+          },
+          to: {
+            row: secondAddress.row,
+            column: secondAddress.col
+          }
+        }
+      }
+    }
+  }
+
+});
diff --git a/lib/xlsx/xform/sheet/worksheet-xform.js b/lib/xlsx/xform/sheet/worksheet-xform.js
@@ -47,6 +47,7 @@ var SheetViewXform = require('./sheet-view-xform');
 var PageMarginsXform = require('./page-margins-xform');
 var PageSetupXform = require('./page-setup-xform');
 var PrintOptionsXform = require('./print-options-xform');
+var AutoFilterXform = require('./auto-filter-xform');
 
 var WorkSheetXform = module.exports = function() {
   this.map = {
@@ -56,6 +57,7 @@ var WorkSheetXform = module.exports = function() {
     sheetFormatPr: new SheetFormatPropertiesXform(),
     cols: new ListXform({tag: 'cols', count: false, childXform: new ColXform()}),
     sheetData: new ListXform({tag: 'sheetData', count: false, empty: true, childXform: new RowXform()}),
+    autoFilter: new AutoFilterXform(),
     mergeCells: new ListXform({tag: 'mergeCells', count: true, childXform: new MergeCellXform()}),
     hyperlinks: new ListXform({tag: 'hyperlinks', count: false, childXform: new HyperlinkXform()}),
     pageMargins: new PageMarginsXform(),
@@ -116,6 +118,7 @@ utils.inherits(WorkSheetXform, BaseXform, {
     this.map.sheetFormatPr.render(xmlStream, sheetFormatPropertiesModel);
     this.map.cols.render(xmlStream, model.cols);
     this.map.sheetData.render(xmlStream, model.rows);
+    this.map.autoFilter.render(xmlStream, model.autoFilter);
     this.map.mergeCells.render(xmlStream, model.mergeCells);
     this.map.dataValidations.render(xmlStream, model.dataValidations);
     // For some reason hyperlinks have to be after the data validations

diff --git a/spec/unit/xlsx/xform/sheet/auto-filter-xform.spec.js b/spec/unit/xlsx/xform/sheet/auto-filter-xform.spec.js
@@ -0,0 +1,27 @@
+'use strict';
+
+var AutoFilterXform = require('../../../../../lib/xlsx/xform/sheet/auto-filter-xform');
+var testXformHelper = require('./../test-xform-helper');
+
+var expectations = [
+  {
+    title: 'Row and Column Adress',
+    create: function() { return new AutoFilterXform(); },
+    preparedModel: {from: {row: 1, column: 1}, to: {row: 1, column: 3}},
+    xml: '<autoFilter ref="A1:C1"/>',
+    parsedModel: {from: {row: 1, column: 1}, to: {row: 1, column: 3}},
+    tests: ['render', 'renderIn', 'parse']
+  },
+  {
+    title: 'String address',
+    create: function() { return new AutoFilterXform(); },
+    preparedModel: {from: 'A1', to: 'C1'},
+    xml: '<autoFilter ref="A1:C1"/>',
+    parsedModel: {from: {row: 1, column: 1}, to: {row: 1, column: 3}},
+    tests: ['render', 'renderIn', 'parse']
+  }
+];
+
+describe('AutoFilterXform', function() {
+  testXformHelper(expectations);
+});