Merge pull request #70 from jurriaan/auto_filter-fix

Auto filter fix

3 files changed, 25 insertions, 2 deletions

diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index 7f557ef7..43296611 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -207,6 +207,13 @@ require 'axlsx/workbook/worksheet/table.rb'
               xml.sheet(:name=>sheet.name, :sheetId=>index+1, :"r:id"=>sheet.rId)
             end
           }
+          xml.definedNames {
+            @worksheets.each_with_index do |sheet, index|
+              if sheet.auto_filter
+                xml.definedName(sheet.abs_auto_filter, :name => '_xlnm._FilterDatabase', :localSheetId => index, :hidden => 1)
+              end
+            end
+          }
         }
       end
       builder.to_xml(:save_with => 0)
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 7678f6f9..ee455195 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -195,6 +195,12 @@ module Axlsx
       @name=v
     end
 
+    # The absolute auto filter range
+    # @see auto_filter
+    def abs_auto_filter
+      Axlsx.cell_range(@auto_filter.split(':').collect { |name| name_to_cell(name)}) if @auto_filter
+    end
+
     # The auto filter range for the worksheet
     # @param [String] v
     # @see auto_filter
diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb
index 603b5314..3a3232fe 100644
--- a/test/workbook/worksheet/tc_worksheet.rb
+++ b/test/workbook/worksheet/tc_worksheet.rb
@@ -2,8 +2,9 @@ require 'tc_helper.rb'
 
 class TestWorksheet < Test::Unit::TestCase
   def setup
-    p = Axlsx::Package.new
-    @ws = p.workbook.add_worksheet
+    @p = Axlsx::Package.new
+    @wb = @p.workbook
+    @ws = @wb.add_worksheet
   end
 
 
@@ -201,6 +202,8 @@ class TestWorksheet < Test::Unit::TestCase
     @ws.auto_filter = "A1:B1"
     doc = Nokogiri::XML(@ws.to_xml_string)
     assert_equal(doc.xpath('//xmlns:worksheet/xmlns:autoFilter[@ref="A1:B1"]').size, 1)
+    doc2 = Nokogiri::XML(@wb.to_xml)    
+    assert_equal(doc2.xpath('//xmlns:workbook/xmlns:definedNames/xmlns:definedName').inner_text, @ws.abs_auto_filter)
   end
 
   def test_to_xml_string_merge_cells
@@ -234,6 +237,13 @@ class TestWorksheet < Test::Unit::TestCase
     assert_equal(doc.xpath('//xmlns:worksheet/xmlns:tableParts/xmlns:tablePart[@r:id="rId1"]').size, 1)
   end
 
+  def test_abs_auto_filter
+    @ws.add_row [1, "two", 3]
+    @ws.auto_filter = "A1:C1"
+    doc = Nokogiri::XML(@wb.to_xml)    
+    assert_equal(doc.xpath('//xmlns:workbook/xmlns:definedNames/xmlns:definedName').inner_text, "'Sheet1'!$A$1:$C$1")
+  end
+
   def test_to_xml
     schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD))
     doc = Nokogiri::XML(@ws.to_xml)