From 90fcca29df390de244e2b16ef0279fd373482981 Mon Sep 17 00:00:00 2001 From: Randy Morgan Date: Sun, 29 Sep 2013 13:13:39 +0900 Subject: add validation for Worksheet#state and WorkbookView#visibility --- lib/axlsx/util/validators.rb | 6 ++++++ 1 file changed, 6 insertions(+) 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 -- cgit v1.2.3 From 097b7da7eb6b29907da28c174e83ff22a6358176 Mon Sep 17 00:00:00 2001 From: Randy Morgan Date: Sun, 29 Sep 2013 13:14:53 +0900 Subject: Added workbook views collection and workbook view object --- lib/axlsx/workbook/workbook_view.rb | 78 ++++++++++++++++++++++++++++++++++++ lib/axlsx/workbook/workbook_views.rb | 22 ++++++++++ test/workbook/tc_workbook_view.rb | 47 ++++++++++++++++++++++ 3 files changed, 147 insertions(+) create mode 100644 lib/axlsx/workbook/workbook_view.rb create mode 100644 lib/axlsx/workbook/workbook_views.rb create mode 100644 test/workbook/tc_workbook_view.rb 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 @@ +# +# +# +# +# +# +# +# +# +# +# +# +# +# +# +# +# +# + +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 << '' + 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 << "" + each { |view| view.to_xml_string(str) } + str << '' + end + end +end + + 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 -- cgit v1.2.3 From 2d604bd4a7463d6f248e103f24246e53973c71aa Mon Sep 17 00:00:00 2001 From: Randy Morgan Date: Sun, 29 Sep 2013 13:16:22 +0900 Subject: Integrate workbook views and alter serialization This integrates workbook views and sheet state into serialization. I also noticed that we were populating defined names during serialization. While it is good to delay this as late as possible as there is always the chance that some conditional programming by the consumer adds, and then removes an autofilter, I am choosing to risk it at this point for cleaner code. --- lib/axlsx/workbook/workbook.rb | 21 +++++++++++-------- lib/axlsx/workbook/worksheet/worksheet.rb | 34 ++++++++++++++++++++++++++++--- test/tc_package.rb | 1 + test/workbook/tc_workbook.rb | 14 +++++++++++++ test/workbook/worksheet/tc_worksheet.rb | 9 ++++++++ 5 files changed, 68 insertions(+), 11 deletions(-) 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 << '' str << '' str << '' + views.to_xml_string(str) str << '' - @worksheets.each_with_index do |sheet, index| - str << '' - 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 << '' defined_names.to_xml_string(str) unless pivot_tables.empty? 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 << '' + 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/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] -- cgit v1.2.3 From 09c9170e50ed4550015e34fdb54b7433bee0ef40 Mon Sep 17 00:00:00 2001 From: Randy Morgan Date: Sun, 29 Sep 2013 13:18:23 +0900 Subject: Adds examples for hiding sheets and workbook view These examples show using hidden and very hidden to manage worksheet visibility as well as using the active_tab attribute of a workbook#view in order to specify which tab should be showing when the sheet is opened. --- examples/example.rb | 23 ++++++++++++++++++++++- 1 file changed, 22 insertions(+), 1 deletion(-) 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 -- cgit v1.2.3