diff options
| -rwxr-xr-x | examples/example.rb | 10 | ||||
| -rw-r--r-- | lib/axlsx/workbook/workbook.rb | 3 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/protected_range.rb | 40 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/worksheet.rb | 26 | ||||
| -rw-r--r-- | test/tc_package.rb | 2 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_protected_range.rb | 18 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_worksheet.rb | 14 |
7 files changed, 106 insertions, 7 deletions
diff --git a/examples/example.rb b/examples/example.rb index 4287dff9..5b5b11b9 100755 --- a/examples/example.rb +++ b/examples/example.rb @@ -334,6 +334,14 @@ end #``` +# Sheet Protection and excluding cells from locking. +unlocked = wb.styles.add_style :locked => false +wb.add_worksheet(:name => 'Sheet Protection') do |sheet| + sheet.sheet_protection.password = 'fish' + sheet.add_row [1, 2 ,3] # These cells will be locked + sheet.add_row [4, 5, 6], :style => unlocked # these cells will not! +end + ##Specify page margins and other options for printing #```ruby @@ -355,7 +363,7 @@ end ## Frozen/Split panes ## ``` ruby wb.add_worksheet(:name => 'fixed headers') do |sheet| - sheet.add_row(['', (0..99).map { |i| "column header #{i}" }].flatten ) + sheet.add_row(['', (0..99).map { |i| "column header #{i}" }].flatten ) 100.times.with_index { |index| sheet << ["row header", (0..index).to_a].flatten } sheet.sheet_view.pane do |pane| pane.top_left_cell = "B2" diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index 08cbbc20..b9a46c06 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -2,6 +2,7 @@ module Axlsx require 'axlsx/workbook/worksheet/date_time_converter.rb' +require 'axlsx/workbook/worksheet/protected_range.rb' require 'axlsx/workbook/worksheet/cell.rb' require 'axlsx/workbook/worksheet/page_margins.rb' require 'axlsx/workbook/worksheet/page_setup.rb' @@ -100,7 +101,6 @@ require 'axlsx/workbook/worksheet/selection.rb' # @return [SimpleTypedList] attr_reader :tables - # A colllection of comments associated with this workbook # @note The recommended way to manage comments is Worksheet#add_comment # @see Worksheet#add_comment @@ -143,7 +143,6 @@ require 'axlsx/workbook/worksheet/selection.rb' @drawings = SimpleTypedList.new Drawing @charts = SimpleTypedList.new Chart @images = SimpleTypedList.new Pic - # Are these even used????? Check package serialization parts @tables = SimpleTypedList.new Table @comments = SimpleTypedList.new Comments diff --git a/lib/axlsx/workbook/worksheet/protected_range.rb b/lib/axlsx/workbook/worksheet/protected_range.rb new file mode 100644 index 00000000..feb5eb48 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/protected_range.rb @@ -0,0 +1,40 @@ +module Axlsx + # The Protected Range class represents a set of cells in the worksheet + # @note the recommended way to manage protected ranges with via Worksheet#protect_range + # @see Worksheet#protect_range + class ProtectedRange + + # The reference for the protected range + # @return [String] + attr_reader :sqref + + # The name of the protected range + # @return [String] + attr_reader :name + + # Initializes a new protected range object + # @option [String] sqref The cell range reference to protect. This can be an absolute or a relateve range however, it only applies to the current sheet. + # @option [String] name An optional name for the protected name. + def initialize(options={}) + options.each do |o| + self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" + end + yield self if block_given? + end + + def sqref=(v) + Axlsx.validate_string(v) + @sqref = v + end + + def name=(v) + Axlsx.validate_string(v) + @name = v + end + + def to_xml_string(str="") + attrs = self.instance_values.reject{ |key, value| value == nil } + str << '<protectedRange ' << attrs.map { |key, value| '' << key << '="' << value.to_s << '"' }.join(' ') << '/>' + end + end +end diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 7245b8c2..513f0453 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -17,6 +17,12 @@ module Axlsx @sheet_protection end + # A collection of protected ranges in the worksheet + # @note The recommended way to manage protected ranges is with Worksheet#protect_range + # @see Worksheet#protect_range + # @return [SimpleTypedList] The protected ranges for this worksheet + attr_reader :protected_ranges + # The sheet view object for this worksheet # @return [SheetView] # @see [SheetView] @@ -188,7 +194,7 @@ module Axlsx @print_options = PrintOptions.new options[:print_options] if options[:print_options] @rows = SimpleTypedList.new Row @column_info = SimpleTypedList.new Col - # @cols = SimpleTypedList.new Cell + @protected_ranges = SimpleTypedList.new ProtectedRange @tables = SimpleTypedList.new Table options.each do |o| @@ -247,7 +253,21 @@ module Axlsx end end - + # Adds a new protected cell range to the worksheet. Note that protected ranges are only in effect when sheet protection is enabled. + # @param [String|Array] The string reference for the cells to protect or an array of cells. + # @retrun [ProtectedRange] + # @note When using an array of cells, a contiguous range is created from the minimum top left to the maximum top bottom of the cells provided. + def protect_range(cells) + sqref = if cells.is_a?(String) + cells + elsif cells.is_a?(SimpleTypedList) + cells = cells.sort { |x, y| [x.index, x.row.index] <=> [y.index, y.row.index] } + "#{cells.first.r}:#{cells.last.r}" + end + @protected_ranges << ProtectedRange.new(:sqref => sqref, :name => 'Range#{@protected_ranges.size}') + @protected_ranges.last + end + # The demensions of a worksheet. This is not actually a required element by the spec, # but at least a few other document readers expect this for conversion # @return [String] the A1:B2 style reference for the first and last row column intersection in the workbook @@ -550,7 +570,7 @@ module Axlsx # [#xAFFFE-#xAFFFF], [#xBFFFE-#xBFFFF], [#xCFFFE-#xCFFFF], # [#xDFFFE-#xDFFFF], [#xEFFFE-#xEFFFF], [#xFFFFE-#xFFFFF], # [#x10FFFE-#x10FFFF]. - # str.tr("", '') + str.tr("\u0001-\u0008", '') end # The worksheet relationships. This is managed automatically by the worksheet diff --git a/test/tc_package.rb b/test/tc_package.rb index eedd2375..3b5db288 100644 --- a/test/tc_package.rb +++ b/test/tc_package.rb @@ -7,6 +7,8 @@ class TestPackage < Test::Unit::TestCase ws = @package.workbook.add_worksheet ws.add_row ['Can', 'we', 'build it?'] ws.add_row ['Yes!', 'We', 'can!'] + ws.protect_range('A1:C1') + ws.protect_range(ws.rows.last.cells) ws.add_comment :author => 'alice', :text => 'Hi Bob', :ref => 'A12' ws.add_comment :author => 'bob', :text => 'Hi Alice', :ref => 'F19' ws.sheet_view do |vs| diff --git a/test/workbook/worksheet/tc_protected_range.rb b/test/workbook/worksheet/tc_protected_range.rb new file mode 100644 index 00000000..6a62f58c --- /dev/null +++ b/test/workbook/worksheet/tc_protected_range.rb @@ -0,0 +1,18 @@ +# encoding: UTF-8 +require 'tc_helper.rb' +class TestProtectedRange < Test::Unit::TestCase +def setup + @p = Axlsx::Package.new + @ws = @p.workbook.add_worksheet { |sheet| sheet.add_row [1,2,3,4,5,6,7,8,9] } +end + +def test_initialize_options + assert_nothing_raised {Axlsx::ProtectedRange.new(:sqref => 'A1:B1', :name => "only bob")} +end + +def test_range + assert_equal(0, @ws.protected_ranges.size) + r = @ws.protect_range('A1:B1') + assert_equal('A1:B1', r.sqref) +end +end diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb index 938bf2f6..7cc44a52 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -312,7 +312,6 @@ class TestWorksheet < Test::Unit::TestCase schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD)) doc = Nokogiri::XML(@ws.to_xml_string) - puts @ws.to_xml_string assert(schema.validate(doc).map { |e| puts e.message; e }.empty?) end # Make sure the XML for all optional elements (like pageMargins, autoFilter, ...) @@ -373,6 +372,19 @@ class TestWorksheet < Test::Unit::TestCase assert_raise(ArgumentError, 'only accept Integer, Float or Fixnum') { @ws.column_widths 2, 7, "-1" } end + def test_protect_range + assert(@ws.protected_ranges.is_a?(Axlsx::SimpleTypedList)) + assert_equal(0, @ws.protected_ranges.size) + @ws.protect_range('A1:A3') + assert_equal('A1:A3', @ws.protected_ranges.last.sqref) + end + + def test_protect_range_with_cells + @ws.add_row [1, 2, 3] + assert_nothing_raised {@ws.protect_range(@ws.rows.first.cells) } + assert_equal('A1:C1', @ws.protected_ranges.last.sqref) + + end def test_merge_cells assert(@ws.merged_cells.is_a?(Array)) assert_equal(@ws.merged_cells.size, 0) |