Merge pull request #250 from randym/bookview_sheetstate

Managing Sheet Visibility and Tab Selection

5 files changed, 150 insertions, 11 deletions

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]