Merge pull request #250 from randym/bookview_sheetstate

Managing Sheet Visibility and Tab Selection

10 files changed, 243 insertions, 12 deletions

diff --git a/examples/example.rb b/examples/example.rb
index 4960ce9f..8b11cb20 100755
--- a/examples/example.rb
+++ b/examples/example.rb
@@ -40,14 +40,15 @@ examples << :printing
 examples << :header_footer
 examples << :comments
 examples << :panes
+examples << :book_view
 examples << :sheet_view
+examples << :hiding_sheets
 examples << :conditional_formatting
 examples << :streaming
 examples << :shared_strings
 examples << :no_autowidth
 examples << :cached_formula
 examples << :page_breaks
-
 p = Axlsx::Package.new
 wb = p.workbook
 #```
@@ -644,7 +645,27 @@ if examples.include? :sheet_view
   end
 end
 
+## Book Views
+#
+## Book views let you specify which sheet the show as active when the user opens the work book as well as a bunch of other 
+## tuning values for the UI @see Axlsx::WorkbookView
+## ```ruby
+if examples.include? :book_view
+  # when you open example.xml the second sheet is selected, and the horizontal scroll bar is much smaller showing more sheets
+  wb.add_view tab_ratio: 800, active_tab: 1
+end
 
+## Hiding Sheets
+##
+## Sheets can be hidden with the state attribute
+if examples.include? :hiding_sheets
+  wb.add_worksheet name: 'hidden', state: :hidden do |sheet|
+    sheet.add_row ['you cant see me!']
+  end
+  wb.add_worksheet name: 'very hidden', state: :very_hidden do |sheet|
+    sheet.add_row ['you really cant see me!']
+  end
+end
 # conditional formatting
 #
 if examples.include? :conditional_formatting
diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb
index a161f0d9..bdea6a1d 100644
--- a/lib/axlsx/util/validators.rb
+++ b/lib/axlsx/util/validators.rb
@@ -297,4 +297,10 @@ module Axlsx
   def self.validate_display_blanks_as(v)
     RestrictionValidator.validate :display_blanks_as, [:gap, :span, :zero], v
   end
+
+  # Requires that the value is one of :visible, :hidden, :very_hidden
+  def self.validate_view_visibility(v)
+    RestrictionValidator.validate :visibility, [:visible, :hidden, :very_hidden], v
+  end
+
 end
diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb
index 0f70f37f..edd1d0d7 100644
--- a/lib/axlsx/workbook/workbook.rb
+++ b/lib/axlsx/workbook/workbook.rb
@@ -37,7 +37,8 @@ require 'axlsx/workbook/worksheet/worksheet_hyperlinks'
 require 'axlsx/workbook/worksheet/break'
 require 'axlsx/workbook/worksheet/row_breaks'
 require 'axlsx/workbook/worksheet/col_breaks'
-
+require 'axlsx/workbook/workbook_view'
+require 'axlsx/workbook/workbook_views'
 
 
 require 'axlsx/workbook/worksheet/worksheet.rb'
@@ -139,6 +140,10 @@ require 'axlsx/workbook/worksheet/selection.rb'
     # @return [SimpleTypedList]
     attr_reader :pivot_tables
 
+    # A collection of views for this workbook
+    def views
+      @views ||= WorkbookViews.new
+    end
 
     # A collection of defined names for this workbook
     # @note The recommended way to manage defined names is Workbook#add_defined_name
@@ -263,6 +268,10 @@ require 'axlsx/workbook/worksheet/selection.rb'
       worksheet
     end
 
+    def add_view(options={})
+      views << WorkbookView.new(options)
+    end
+
     # Adds a defined name to this workbook
     # @return [DefinedName]
     # @param [String] formula @see DefinedName
@@ -327,17 +336,13 @@ require 'axlsx/workbook/worksheet/selection.rb'
     # @param [String] str
     # @return [String]
     def to_xml_string(str='')
-      add_worksheet unless worksheets.size > 0
+      add_worksheet(name: 'Sheet1') unless worksheets.size > 0
       str << '<?xml version="1.0" encoding="UTF-8"?>'
       str << '<workbook xmlns="' << XML_NS << '" xmlns:r="' << XML_NS_R << '">'
       str << '<workbookPr date1904="' << @@date1904.to_s << '"/>'
+      views.to_xml_string(str)
       str << '<sheets>'
-      @worksheets.each_with_index do |sheet, index|
-        str << '<sheet name="' << sheet.name << '" sheetId="' << (index+1).to_s << '" r:id="' << sheet.rId << '"/>'
-        if defined_name = sheet.auto_filter.defined_name
-          add_defined_name defined_name, :name => '_xlnm._FilterDatabase', :local_sheet_id => index, :hidden => 1
-        end
-      end
+      worksheets.each { |sheet| sheet.to_sheet_node_xml_string(str) }
       str << '</sheets>'
       defined_names.to_xml_string(str)
       unless pivot_tables.empty?
diff --git a/lib/axlsx/workbook/workbook_view.rb b/lib/axlsx/workbook/workbook_view.rb
new file mode 100644
index 00000000..11eae571
--- /dev/null
+++ b/lib/axlsx/workbook/workbook_view.rb
@@ -0,0 +1,78 @@
+# <xsd:complexType name="CT_BookView">
+#     <xsd:sequence>
+#       <xsd:element name="extLst" type="CT_ExtensionList" minOccurs="0" maxOccurs="1"/>
+#     </xsd:sequence>
+#     <xsd:attribute name="visibility" type="ST_Visibility" use="optional" default="visible"/>
+#     <xsd:attribute name="minimized" type="xsd:boolean" use="optional" default="false"/>
+#     <xsd:attribute name="showHorizontalScroll" type="xsd:boolean" use="optional" default="true"/>
+#     <xsd:attribute name="showVerticalScroll" type="xsd:boolean" use="optional" default="true"/>
+#     <xsd:attribute name="showSheetTabs" type="xsd:boolean" use="optional" default="true"/>
+#     <xsd:attribute name="xWindow" type="xsd:int" use="optional"/>
+#     <xsd:attribute name="yWindow" type="xsd:int" use="optional"/>
+#     <xsd:attribute name="windowWidth" type="xsd:unsignedInt" use="optional"/>
+#     <xsd:attribute name="windowHeight" type="xsd:unsignedInt" use="optional"/>
+#     <xsd:attribute name="tabRatio" type="xsd:unsignedInt" use="optional" default="600"/>
+#     <xsd:attribute name="firstSheet" type="xsd:unsignedInt" use="optional" default="0"/>
+#     <xsd:attribute name="activeTab" type="xsd:unsignedInt" use="optional" default="0"/>
+#     <xsd:attribute name="autoFilterDateGrouping" type="xsd:boolean" use="optional"
+#     default="true"/>
+# </xsd:complexType>
+
+module Axlsx
+
+  # A BookView defines the display properties for a workbook.
+  # Units for window widths and other dimensions are expressed in twips.
+  # Twip measurements are portable between different display resolutions.
+  # The formula is (screen pixels) * (20 * 72) / (logical device dpi),
+  # where the logical device dpi can be different for x and y coordinates.
+  class WorkbookView
+
+    include Axlsx::SerializedAttributes
+    include Axlsx::OptionsParser
+    include Axlsx::Accessors
+
+
+    # Creates a new BookView object
+    # @params [Hash] options  A hash of key/value pairs that will be mapped to this instances attributes.
+    # @option [Symbol] visibility Specifies visible state of the workbook window. The default value for this attribute is :visible.
+    # @option [Boolean] minimized Specifies a boolean value that indicates whether the workbook window is minimized.
+    # @option [Boolean] show_horizontal_scroll Specifies a boolean value that indicates whether to display the horizontal scroll bar in the user interface.
+    # @option [Boolean] show_vertical_scroll Specifies a boolean value that indicates whether to display the vertical scroll bar.
+    # @option [Boolean] show_sheet_tabs Specifies a boolean value that indicates whether to display the sheet tabs in the user interface.
+    # @option [Integer] tab_ratio Specifies ratio between the workbook tabs bar and the horizontal scroll bar.
+    # @option [Integer] first_sheet Specifies the index to the first sheet in this book view.
+    # @option [Integer] active_tab Specifies an unsignedInt that contains the index to the active sheet in this book view. 
+    # @option [Integer] x_window Specifies the X coordinate for the upper left corner of the workbook window. The unit of measurement for this value is twips.
+    # @option [Integer] y_window Specifies the Y coordinate for the upper left corner of the workbook window. The unit of measurement for this value is twips.
+    # @option [Integer] window_width Specifies the width of the workbook window. The unit of measurement for this value is twips.
+    # @option [Integer] window_height Specifies the height of the workbook window. The unit of measurement for this value is twips.
+    # @option [Boolean] auto_filter_date_grouping Specifies a boolean value that indicates whether to group dates when presenting the user with filtering options in the user interface.
+    def initialize(options={})
+      parse_options options
+      yield self if block_given?
+    end
+
+
+    unsigned_int_attr_accessor :x_window, :y_window, :window_width, :window_height,
+                               :tab_ratio, :first_sheet, :active_tab
+
+    validated_attr_accessor  [:visibility], :validate_view_visibility
+
+    serializable_attributes :visibility, :minimized,
+                            :show_horizontal_scroll, :show_vertical_scroll,
+                            :show_sheet_tabs, :tab_ratio, :first_sheet, :active_tab,
+                            :x_window, :y_window, :window_width, :window_height,
+                            :auto_filter_date_grouping
+
+    boolean_attr_accessor :minimized, :show_horizontal_scroll, :show_vertical_scroll,
+                          :show_sheet_tabs, :auto_filter_date_grouping
+
+
+
+    def to_xml_string(str = '')
+       str << '<workbookView '
+       serialized_attributes str
+       str << '></workbookView>'
+    end
+  end
+end
diff --git a/lib/axlsx/workbook/workbook_views.rb b/lib/axlsx/workbook/workbook_views.rb
new file mode 100644
index 00000000..4e69f19e
--- /dev/null
+++ b/lib/axlsx/workbook/workbook_views.rb
@@ -0,0 +1,22 @@
+module Axlsx
+  # a simple types list of BookView objects
+  class WorkbookViews < SimpleTypedList
+
+    # creates the book views object
+    def initialize
+      super WorkbookView
+    end
+
+    # Serialize to xml
+    # @param [String] str
+    # @return [String]
+    def to_xml_string(str = '')
+      return if @list.empty?
+      str << "<bookViews>"
+      each { |view| view.to_xml_string(str) }
+      str << '</bookViews>'
+    end
+  end
+end
+
+
diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb
index 9937a3f4..2dd5e3af 100644
--- a/lib/axlsx/workbook/worksheet/worksheet.rb
+++ b/lib/axlsx/workbook/worksheet/worksheet.rb
@@ -4,7 +4,7 @@ module Axlsx
   # The Worksheet class represents a worksheet in the workbook.
   class Worksheet
     include Axlsx::OptionsParser
-
+    include Axlsx::SerializedAttributes
     # definition of characters which are less than the maximum width of 0-9 in the default font for use in String#count.
     # This is used for autowidth calculations
     # @return [String]
@@ -24,12 +24,15 @@ module Axlsx
     def initialize(wb, options={})
       self.workbook = wb
       @sheet_protection = nil
-
       initialize_page_options(options)
       parse_options options
       @workbook.worksheets << self
+      @sheet_id = index + 1
+      yield self if block_given?
     end
 
+    serializable_attributes :sheet_id, :name, :state
+
     # Initalizes page margin, setup and print options
     # @param [Hash] options Options passed in from the initializer
     def initialize_page_options(options)
@@ -47,6 +50,22 @@ module Axlsx
       @name ||=  "Sheet" + (index+1).to_s
     end
 
+    # Specifies the visible state of this sheet. Allowed states are
+    # :visible, :hidden or :very_hidden. The default value is :visible.
+    #
+    # Worksheets in the :hidden state can be shown using the sheet formatting properties in excel.
+    # :very_hidden sheets should be inaccessible to end users.
+    # @param [Symbol] sheet_state The visible state for this sheet.
+    def state=(sheet_state)
+      RestrictionValidator.validate "Worksheet#state", [:visible, :hidden, :very_hidden], sheet_state
+      @state = sheet_state
+    end
+
+    # The visibility of this sheet
+    def state
+      @state ||= :visible
+    end
+
     # The sheet calculation properties
     # @return [SheetCalcPr]
     def sheet_calc_pr
@@ -142,7 +161,7 @@ module Axlsx
       @rows.transpose(&block)
     end
 
-    # An range that excel will apply an auto-filter to "A1:B3"
+    # A range that excel will apply an auto-filter to "A1:B3"
     # This will turn filtering on for the cells in the range.
     # The first row is considered the header, while subsequent rows are considered to be data.
     # @return String
@@ -348,6 +367,7 @@ module Axlsx
     def auto_filter=(v)
       DataTypeValidator.validate "Worksheet.auto_filter", String, v
       auto_filter.range = v
+      workbook.add_defined_name auto_filter.defined_name, name: '_xlnm.FilterDatabase', local_sheet_id: index, hidden: 1
     end
 
     # Accessor for controlling whether leading and trailing spaces in cells are
@@ -577,6 +597,14 @@ module Axlsx
       cells.each { |cell| cell.style = style }
     end
 
+    # Returns a sheet node serialization for this sheet in the workbook.
+    def to_sheet_node_xml_string(str='')
+      str << '<sheet '
+      serialized_attributes str
+      str << "r:id='" << rId << "'"
+      str << '></sheet>'
+    end
+
     # Serializes the worksheet object to an xml string
     # This intentionally does not use nokogiri for performance reasons
     # @return [String]
diff --git a/test/tc_package.rb b/test/tc_package.rb
index 4df44ea4..8f0caa46 100644
--- a/test/tc_package.rb
+++ b/test/tc_package.rb
@@ -11,6 +11,7 @@ class TestPackage < Test::Unit::TestCase
     ws.outline_level_columns 0, 1
     ws.add_hyperlink :ref => ws.rows.first.cells.last, :location => 'https://github.com/randym'
     ws.workbook.add_defined_name("#{ws.name}!A1:C2", :name => '_xlnm.Print_Titles', :hidden => true)
+    ws.workbook.add_view active_tab: 1, first_sheet: 0
     ws.protect_range('A1:C1')
     ws.protect_range(ws.rows.last.cells)
     ws.add_comment :author => 'alice', :text => 'Hi Bob', :ref => 'A12'
diff --git a/test/workbook/tc_workbook.rb b/test/workbook/tc_workbook.rb
index 32b6935a..2a51e4bd 100644
--- a/test/workbook/tc_workbook.rb
+++ b/test/workbook/tc_workbook.rb
@@ -53,6 +53,11 @@ class TestWorkbook < Test::Unit::TestCase
     assert_equal(1, @wb.defined_names.size)
   end
 
+  def test_add_view
+    @wb.add_view visibility: :hidden, window_width: 800
+    assert_equal(1, @wb.views.size)
+  end
+
   def test_shared_strings
     assert_equal(@wb.use_shared_strings, nil)
     assert_raise(ArgumentError) {@wb.use_shared_strings = 'bpb'}
@@ -116,6 +121,15 @@ class TestWorkbook < Test::Unit::TestCase
     assert_equal(doc.xpath('//xmlns:workbook/xmlns:definedNames/xmlns:definedName').inner_text, @wb.worksheets[0].auto_filter.defined_name)
   end
 
+  def test_to_xml_string_book_views
+    @wb.add_worksheet do |sheet|
+      sheet.add_row [1, "two"]
+    end
+    @wb.add_view active_tab: 0, first_sheet: 0
+    doc = Nokogiri::XML(@wb.to_xml_string)
+    assert_equal(1, doc.xpath('//xmlns:workbook/xmlns:bookViews/xmlns:workbookView[@activeTab=0]').size)
+  end
+
   def test_to_xml_uses_correct_rIds_for_pivotCache
     ws = @wb.add_worksheet
     pivot_table = ws.add_pivot_table('G5:G6', 'A1:D5')
diff --git a/test/workbook/tc_workbook_view.rb b/test/workbook/tc_workbook_view.rb
new file mode 100644
index 00000000..73f39a9c
--- /dev/null
+++ b/test/workbook/tc_workbook_view.rb
@@ -0,0 +1,47 @@
+require 'tc_helper'
+
+class TestWorkbookView < Test::Unit::TestCase
+
+  def setup
+    @options = { visibility: :hidden, minimized: true, show_horizontal_scroll: true, show_vertical_scroll: true,
+                show_sheet_tabs: true, tab_ratio: 750, first_sheet: 0, active_tab: 1, x_window: 500, y_window: 400,
+                window_width: 800, window_height: 600, auto_filter_date_grouping: true }
+    @book_view = Axlsx::WorkbookView.new @options
+  end
+
+  def test_options_assignation
+    @options.each do |key, value|
+      assert_equal(value, @book_view.send(key))
+    end
+  end
+
+  def test_boolean_attribute_validation
+    %w(minimized show_horizontal_scroll show_vertical_scroll show_sheet_tabs auto_filter_date_grouping).each do |attr|
+      assert_raise(ArgumentError, 'only booleanish allowed in boolean attributes') { @book_view.send("#{attr}=", "banana") }
+      assert_nothing_raised { @book_view.send("#{attr}=", false )}
+    end
+  end
+
+  def test_integer_attribute_validation
+    %w(tab_ratio first_sheet active_tab x_window y_window window_width window_height).each do |attr|
+      assert_raise(ArgumentError, 'only integer allowed in integer attributes') { @book_view.send("#{attr}=", "b") }
+      assert_nothing_raised { @book_view.send("#{attr}=", 7 )}
+    end
+  end
+
+  def test_visibility_attribute_validation
+    assert_raise(ArgumentError) { @book_view.visibility = :foobar }
+    assert_nothing_raised { @book_view.visibility = :hidden }
+    assert_nothing_raised { @book_view.visibility = :very_hidden }
+    assert_nothing_raised { @book_view.visibility = :visible }
+  end
+
+  def test_to_xml_string
+    xml = @book_view.to_xml_string
+    doc = Nokogiri::XML(xml)
+    @options.each do |key, value|
+      path = "workbookView[@#{Axlsx.camel(key, false)}='#{value}']"
+      assert_equal(1, doc.xpath(path).size)
+    end
+  end
+end
diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb
index 3782b215..b83613b4 100644
--- a/test/workbook/worksheet/tc_worksheet.rb
+++ b/test/workbook/worksheet/tc_worksheet.rb
@@ -73,6 +73,15 @@ class TestWorksheet < Test::Unit::TestCase
     end
   end
 
+  def test_state
+    assert_equal(:visible, @ws.state)
+  end
+
+  def test_state_validation
+    assert_raise(ArgumentError) { @ws.state = :dead }
+    assert_nothing_raised { @ws.state = :very_hidden }
+  end
+
   def test_no_autowidth
     @ws.workbook.use_autowidth = false
     @ws.add_row [1,2,3,4]