diff options
27 files changed, 500 insertions, 199 deletions
diff --git a/lib/axlsx/drawing/drawing.rb b/lib/axlsx/drawing/drawing.rb index 6dd5580e..c1b41503 100644 --- a/lib/axlsx/drawing/drawing.rb +++ b/lib/axlsx/drawing/drawing.rb @@ -69,6 +69,7 @@ module Axlsx @anchors = SimpleTypedList.new [TwoCellAnchor, OneCellAnchor] end + # Adds an image to the chart If th end_at option is specified we create a two cell anchor. By default we use a one cell anchor. # @note The recommended way to manage images is to use Worksheet.add_image. Please refer to that method for documentation. # @see Worksheet#add_image diff --git a/lib/axlsx/util/simple_typed_list.rb b/lib/axlsx/util/simple_typed_list.rb index 711b4dae..a7622890 100644 --- a/lib/axlsx/util/simple_typed_list.rb +++ b/lib/axlsx/util/simple_typed_list.rb @@ -50,6 +50,18 @@ module Axlsx @locked_at = nil self end + + # join operator + # @param [Array] v the array to join + # @raise [ArgumentError] if any of the values being joined are not + # one of the allowed types + # @return [SimpleTypedList] + def +(v) + v.each do |item| + DataTypeValidator.validate "SimpleTypedList.+", @allowed_types, item + @list << item + end + end # Concat operator # @param [Any] v the data to be added @@ -60,12 +72,7 @@ module Axlsx @list << v @list.size - 1 end - - # alternate of << method - # @see << - def push(v) - self.<< v - end + alias :push :<< # delete the item from the list # @param [Any] v The item to be deleted. diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index b9a46c06..c169d630 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -1,8 +1,9 @@ # -*- coding: utf-8 -*- module Axlsx - +require 'axlsx/workbook/worksheet/auto_filter.rb' require 'axlsx/workbook/worksheet/date_time_converter.rb' require 'axlsx/workbook/worksheet/protected_range.rb' +require 'axlsx/workbook/worksheet/protected_ranges.rb' require 'axlsx/workbook/worksheet/cell.rb' require 'axlsx/workbook/worksheet/page_margins.rb' require 'axlsx/workbook/worksheet/page_setup.rb' @@ -13,15 +14,25 @@ require 'axlsx/workbook/worksheet/data_bar.rb' require 'axlsx/workbook/worksheet/icon_set.rb' require 'axlsx/workbook/worksheet/conditional_formatting.rb' require 'axlsx/workbook/worksheet/conditional_formatting_rule.rb' +require 'axlsx/workbook/worksheet/conditional_formattings.rb' require 'axlsx/workbook/worksheet/row.rb' require 'axlsx/workbook/worksheet/col.rb' +require 'axlsx/workbook/worksheet/cols.rb' require 'axlsx/workbook/worksheet/comments.rb' require 'axlsx/workbook/worksheet/comment.rb' +require 'axlsx/workbook/worksheet/merged_cells.rb' require 'axlsx/workbook/worksheet/sheet_protection.rb' +require 'axlsx/workbook/worksheet/sheet_pr.rb' +require 'axlsx/workbook/worksheet/dimension.rb' +require 'axlsx/workbook/worksheet/sheet_data.rb' +require 'axlsx/workbook/worksheet/worksheet_drawing.rb' +require 'axlsx/workbook/worksheet/worksheet_comments.rb' require 'axlsx/workbook/worksheet/worksheet.rb' require 'axlsx/workbook/shared_strings_table.rb' require 'axlsx/workbook/worksheet/table.rb' +require 'axlsx/workbook/worksheet/tables.rb' require 'axlsx/workbook/worksheet/data_validation.rb' +require 'axlsx/workbook/worksheet/data_validations.rb' require 'axlsx/workbook/worksheet/sheet_view.rb' require 'axlsx/workbook/worksheet/pane.rb' require 'axlsx/workbook/worksheet/selection.rb' @@ -236,9 +247,9 @@ require 'axlsx/workbook/worksheet/selection.rb' str << '</sheets>' str << '<definedNames>' @worksheets.each_with_index do |sheet, index| - if sheet.auto_filter + if sheet.auto_filter.defined_name str << '<definedName name="_xlnm._FilterDatabase" localSheetId="' << index.to_s << '" hidden="1">' - str << sheet.abs_auto_filter << '</definedName>' + str << sheet.auto_filter.defined_name << '</definedName>' end end str << '</definedNames>' diff --git a/lib/axlsx/workbook/worksheet/auto_filter.rb b/lib/axlsx/workbook/worksheet/auto_filter.rb new file mode 100644 index 00000000..ff0c93d1 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/auto_filter.rb @@ -0,0 +1,23 @@ +module Axlsx + + #This class represents an auto filter range in a worksheet + class AutoFilter + def initialize(worksheet) + raise ArgumentError, 'you must provide a worksheet' unless worksheet.is_a?(Worksheet) + @worksheet = worksheet + end + + attr_reader :worksheet + attr_accessor :range + + def defined_name + return unless range + Axlsx.cell_range(range.split(':').collect { |name| worksheet.name_to_cell(name)}) + end + + def to_xml_string(str='') + str << "<autoFilter ref='#{range}'></autoFilter>" + end + + end +end diff --git a/lib/axlsx/workbook/worksheet/cols.rb b/lib/axlsx/workbook/worksheet/cols.rb new file mode 100644 index 00000000..d4595f29 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/cols.rb @@ -0,0 +1,20 @@ +module Axlsx + + # The cols class manages the col object used to manage column widths. + # This is where the magic happens with autowidth + class Cols < SimpleTypedList + + def initialize(worksheet) + raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet) + super Col + @worksheet = worksheet + end + + def to_xml_string(str = '') + return if empty? + str << '<cols>' + each { |item| item.to_xml_string(str) } + str << '</cols>' + end + end +end diff --git a/lib/axlsx/workbook/worksheet/comments.rb b/lib/axlsx/workbook/worksheet/comments.rb index 0bf703f5..25da9de2 100644 --- a/lib/axlsx/workbook/worksheet/comments.rb +++ b/lib/axlsx/workbook/worksheet/comments.rb @@ -53,6 +53,14 @@ module Axlsx @list.map { |comment| comment.author.to_s }.uniq.sort end + # The relationships required by this object + # @return [Array] + def relationships + [Relationship.new(VML_DRAWING_R, "../#{vml_drawing.pn}"), + Relationship.new(COMMENT_R, "../#{pn}"), + Relationship.new(COMMENT_R_NULL, "NULL")] + end + # serialize the object # @param [String] str # @return [String] diff --git a/lib/axlsx/workbook/worksheet/conditional_formattings.rb b/lib/axlsx/workbook/worksheet/conditional_formattings.rb new file mode 100644 index 00000000..2fe9f2e8 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/conditional_formattings.rb @@ -0,0 +1,25 @@ +module Axlsx + + # A simple, self serializing class for storing conditional formattings + class ConditionalFormattings < SimpleTypedList + + # creates a new Tables object + def initialize(worksheet) + raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet) + super ConditionalFormatting + @worksheet = worksheet + end + + # The worksheet that owns this collection of tables + # @return [Worksheet] + attr_reader :worksheet + + # serialize the conditional formattings + def to_xml_string(str = "") + return if empty? + each { |item| item.to_xml_string(str) } + end + end + +end + diff --git a/lib/axlsx/workbook/worksheet/data_validations.rb b/lib/axlsx/workbook/worksheet/data_validations.rb new file mode 100644 index 00000000..cdfcf375 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/data_validations.rb @@ -0,0 +1,28 @@ +module Axlsx + + # A simple, self serializing class for storing conditional formattings + class DataValidations < SimpleTypedList + + # creates a new Tables object + def initialize(worksheet) + raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet) + super DataValidation + @worksheet = worksheet + end + + # The worksheet that owns this collection of tables + # @return [Worksheet] + attr_reader :worksheet + + # serialize the conditional formattings + def to_xml_string(str = "") + return if empty? + str << "<dataValidations count='#{size}'>" + each { |item| item.to_xml_string(str) } + str << '</dataValidations>' + end + end + +end + + diff --git a/lib/axlsx/workbook/worksheet/dimension.rb b/lib/axlsx/workbook/worksheet/dimension.rb new file mode 100644 index 00000000..838b4ffa --- /dev/null +++ b/lib/axlsx/workbook/worksheet/dimension.rb @@ -0,0 +1,49 @@ +module Axlsx + + # This class manages the dimensions for a worksheet. + # While this node is optional in the specification some readers like + # LibraOffice require this node to render the sheet + class Dimension + + + def self.default_first + @@default_first ||= 'A1' + end + + def self.default_last + @@default_last ||= 'AA200' + end + + + def initialize(worksheet) + raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet) + @worksheet = worksheet + end + + attr_reader :worksheet + + def sqref + "#{first_cell_reference}:#{last_cell_reference}" + end + + def to_xml_string(str = '') + return if worksheet.rows.empty? + str << "<dimension ref=\"%s\"></dimension>" % sqref + end + + def first_cell_reference + dimension_reference(worksheet.rows.first.cells.first, Dimension.default_first) + end + + def last_cell_reference + dimension_reference(worksheet.rows.last.cells.last, Dimension.default_last) + end + + private + + def dimension_reference(cell, default) + return default unless cell.respond_to?(:r) + cell.r + end + end +end diff --git a/lib/axlsx/workbook/worksheet/merged_cells.rb b/lib/axlsx/workbook/worksheet/merged_cells.rb new file mode 100644 index 00000000..943cab04 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/merged_cells.rb @@ -0,0 +1,25 @@ +module Axlsx + + class MergedCells < SimpleTypedList + + def initialize(worksheet) + raise ArgumentError, 'you must provide a worksheet' unless worksheet.is_a?(Worksheet) + super String + end + + def add(cells) + @list << if cells.is_a?(String) + cells + elsif cells.is_a?(Array) + Axlsx::cell_range(cells, false) + end + end + + def to_xml_string(str = '') + return if @list.empty? + str << "<mergeCells count='#{size}'>" + each { |merged_cell| str << "<mergeCell ref='#{merged_cell}'></mergeCell>" } + str << '</mergeCells>' + end + end +end diff --git a/lib/axlsx/workbook/worksheet/protected_ranges.rb b/lib/axlsx/workbook/worksheet/protected_ranges.rb new file mode 100644 index 00000000..e55d35fa --- /dev/null +++ b/lib/axlsx/workbook/worksheet/protected_ranges.rb @@ -0,0 +1,32 @@ +module Axlsx + + # A self serializing collection of ranges that should be protected in + # the worksheet + class ProtectedRanges < SimpleTypedList + + attr_reader :worksheet + + def initialize(worksheet) + raise ArgumentError, 'You must provide a worksheet' unless worksheet.is_a?(Worksheet) + super ProtectedRange + @worksheet = worksheet + end + + def add_range(cells) + sqref = if cells.is_a?(String) + cells + elsif cells.is_a?(SimpleTypedList) || cells.is_a?(Array) + Axlsx::cell_range(cells, false) + end + @list << ProtectedRange.new(:sqref => sqref, :name => "Range#{size}") + last + end + + def to_xml_string(str = '') + return if empty? + str << '<protectedRanges>' + each { |range| range.to_xml_string(str) } + str << '</protectedRanges>' + end + end +end diff --git a/lib/axlsx/workbook/worksheet/sheet_data.rb b/lib/axlsx/workbook/worksheet/sheet_data.rb new file mode 100644 index 00000000..98354b29 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/sheet_data.rb @@ -0,0 +1,20 @@ +module Axlsx + + # This class manages the serialization of rows for worksheets + class SheetData + + def initialize(worksheet) + raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet) + @worksheet = worksheet + end + + attr_reader :worksheet + + def to_xml_string(str = '') + str << '<sheetData>' + worksheet.rows.each_with_index{ |row, index| row.to_xml_string(index, str) } + str << '</sheetData>' + end + + end +end diff --git a/lib/axlsx/workbook/worksheet/sheet_pr.rb b/lib/axlsx/workbook/worksheet/sheet_pr.rb new file mode 100644 index 00000000..a7f54f28 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/sheet_pr.rb @@ -0,0 +1,16 @@ +module Axlsx + class SheetPr + + def initialize(worksheet) + raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet) + @worksheet = worksheet + end + + attr_reader :worksheet + + def to_xml_string(str = '') + return unless worksheet.fit_to_page? + str << "<sheetPr><pageSetUpPr fitToPage=\"%s\"></pageSetUpPr></sheetPr>" % worksheet.fit_to_page? + end + end +end diff --git a/lib/axlsx/workbook/worksheet/tables.rb b/lib/axlsx/workbook/worksheet/tables.rb new file mode 100644 index 00000000..2d9a1f3c --- /dev/null +++ b/lib/axlsx/workbook/worksheet/tables.rb @@ -0,0 +1,31 @@ +module Axlsx + + # A simple, self serializing class for storing tables + class Tables < SimpleTypedList + + # creates a new Tables object + def initialize(worksheet) + raise ArgumentError, "you must provide a worksheet" unless worksheet.is_a?(Worksheet) + super Table + @worksheet = worksheet + end + + # The worksheet that owns this collection of tables + # @return [Worksheet] + attr_reader :worksheet + + # returns the relationships required by this collection + def relationships + return [] if empty? + map{ |table| Relationship.new(TABLE_R, "../#{table.pn}") } + end + + def to_xml_string(str = "") + return if empty? + str << "<tableParts count='#{size}'>" + @list.each { |table| str << "<tablePart r:id='#{table.rId}'/>" } + str << '</tableParts>' + end + end + +end diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index bf48bf67..fd135726 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -21,24 +21,20 @@ module Axlsx def initialize(wb, options={}) self.workbook = wb @workbook.worksheets << self - initialize_optional_instance_variables + @sheet_protection = nil initialize_page_options(options) options.each do |o| self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" end end - - def initialize_optional_instance_variables - @page_setup = @drawing = @sheet_protection = @auto_filter = nil - end - + def initialize_page_options(options) @page_margins = PageMargins.new options[:page_margins] if options[:page_margins] @page_setup = PageSetup.new options[:page_setup] if options[:page_setup] @print_options = PrintOptions.new options[:print_options] if options[:print_options] end - # The name of the worksheet + # The name of the worksheet # @return [String] def name @name ||= "Sheet" + (index+1).to_s @@ -52,7 +48,6 @@ module Axlsx @sheet_protection end - # The sheet view object for this worksheet # @return [SheetView] # @see [SheetView] @@ -69,14 +64,19 @@ module Axlsx # The tables in this worksheet # @return [Array] of Table def tables - @tables ||= SimpleTypedList.new Table + @tables ||= Tables.new self end # The comments associated with this worksheet # @return [SimpleTypedList] + def worksheet_comments + @worksheet_comments ||= WorksheetComments.new self + end + def comments - @comments ||= Comments.new(self) + worksheet_comments.comments if worksheet_comments.has_comments? end + # The rows in this worksheet # @note The recommended way to manage rows is Worksheet#add_row # @return [SimpleTypedList] @@ -85,11 +85,13 @@ module Axlsx @rows ||= SimpleTypedList.new Row end - # An range that excel will apply an autfilter to "A1:B3" + # An range that excel will apply an autfilter 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 considerd to be data. # @return String - attr_reader :auto_filter + def auto_filter + @auto_filter ||= AutoFilter.new self + end # Indicates if the worksheet should show gridlines or not # @return Boolean @@ -115,7 +117,7 @@ module Axlsx # @return Boolean # @see #page_setup def fit_to_page? - return false unless @page_setup + return false unless self.instance_values.keys.include?('page_setup') page_setup.fit_to_page? end @@ -123,7 +125,7 @@ module Axlsx # Column info for the sheet # @return [SimpleTypedList] def column_info - @column_info ||= SimpleTypedList.new Col + @column_info ||= Cols.new self end # Page margins for printing the worksheet. @@ -213,6 +215,7 @@ module Axlsx cf = ConditionalFormatting.new( :sqref => cells ) cf.add_rules rules conditional_formattings << cf + conditional_formattings end # Add data validation to this worksheet. @@ -236,34 +239,30 @@ module Axlsx # worksheet["C1"].merge worksheet["E1"] # @param [Array, string] cells def merge_cells(cells) - merged_cells << if cells.is_a?(String) - cells - elsif cells.is_a?(Array) - Axlsx::cell_range(cells, false) - end + merged_cells.add cells 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] cells The string reference for the cells to protect or an array of cells. # @return [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) - Axlsx::cell_range(cells, false) - end - protected_ranges << ProtectedRange.new(:sqref => sqref, :name => "Range#{protected_ranges.size}") - protected_ranges.last + protected_ranges.add_range(cells) end - # The demensions of a worksheet. This is not actually a required element by the spec, + # The dimensions 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 + # @return [Dimension] def dimension - "#{dimension_reference(rows.first.cells.first, 'A1')}:#{dimension_reference(rows.last.cells.last, 'AA200')}" + @dimension ||= Dimension.new self + end + + # The sheet properties for this workbook. + # Currently only pageSetUpPr -> fitToPage is implemented + # @return [SheetPr] + def sheet_pr + @sheet_pr ||= SheetPr.new self end - # # Indicates if gridlines should be shown in the sheet. # This is true by default. # @return [Boolean] @@ -314,18 +313,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 def auto_filter=(v) DataTypeValidator.validate "Worksheet.auto_filter", String, v - @auto_filter = v + auto_filter.range = v end # The part name of this worksheet @@ -357,7 +350,7 @@ module Axlsx # @return [Drawing] # @see Worksheet#add_chart def drawing - @drawing ||= Axlsx::Drawing.new(self) + worksheet_drawing.drawing end # Adds a row to the worksheet and updates auto fit data. @@ -483,29 +476,28 @@ module Axlsx # @see Line3DChart # @see README for examples def add_chart(chart_type, options={}) - chart = drawing.add_chart(chart_type, options) + chart = worksheet_drawing.add_chart(chart_type, options) yield chart if block_given? chart end # needs documentation def add_table(ref, options={}) - table = Table.new(ref, self, options) - tables << table - yield table if block_given? - table + tables << Table.new(ref, self, options) + yield tables.last if block_given? + tables.last end - # Shortcut to comments#add_comment + # Shortcut to worsksheet_comments#add_comment def add_comment(options={}) - comments.add_comment(options) + worksheet_comments.add_comment(options) end # Adds a media item to the worksheets drawing # @option [Hash] options options passed to drawing.add_image def add_image(options={}) - image = drawing.add_image(options) + image = worksheet_drawing.add_image(options) yield image if block_given? image end @@ -516,58 +508,34 @@ module Axlsx def to_xml_string str = '<?xml version="1.0" encoding="UTF-8"?>' str << worksheet_node - str << sheet_pr_node - str << dimension_node - self_serializing_node(sheet_view, str) - collection_node('cols', column_info, str) - collection_node('sheetData', rows, str, :with_index => true, :required => true) - str << auto_filter_node - self_serializing_node(@sheet_protection, str) - collection_node('protectedRanges', protected_ranges, str) - str << merged_cells_node - self_serializing_node(print_options, str) - self_serializing_node(page_margins, str) - self_serializing_node(page_setup, str) - str << drawing_nodes - str << table_parts_node - str << conditional_formattings_node - collection_node('dataValidations', data_validations, str, :count => true) + [sheet_pr, dimension, + sheet_view, column_info, + sheet_data, + @sheet_protection, + protected_ranges, + auto_filter, + merged_cells, + print_options, + page_margins, + page_setup, + worksheet_drawing, + worksheet_comments, + tables, + conditional_formattings, + data_validations].each do |item| + item.to_xml_string(str) if item + end str << '</worksheet>' str.gsub(/[[:cntrl:]]/,'') end - def self_serializing_node(item, str) - item.to_xml_string(str) if item - end - - def collection_node(node_name, collection, str="", options={}) - return '' if collection && collection.empty? && !options[:required] - str << "<#{node_name}" - str << " count='#{collection.size}'" if options[:count] - str << '>' - if options[:with_index] - collection.each_with_index { |item, index| item.to_xml_string(index, str) } - else - collection.each { |item| item.to_xml_string(str) } - end - str << "</#{node_name}>" - end - # The worksheet relationships. This is managed automatically by the worksheet # @return [Relationships] def relationships r = Relationships.new - tables.each do |table| - r << Relationship.new(TABLE_R, "../#{table.pn}") - end - - if comments.size > 0 - r << Relationship.new(VML_DRAWING_R, "../#{comments.vml_drawing.pn}") - r << Relationship.new(COMMENT_R, "../#{comments.pn}") - r << Relationship.new(COMMENT_R_NULL, "NULL") - end - - r << Relationship.new(DRAWING_R, "../#{@drawing.pn}") if @drawing + r + [tables.relationships, + worksheet_comments.relationships, + worksheet_drawing.relationship].flatten.compact r end @@ -587,82 +555,61 @@ module Axlsx private def range(*cell_def) - first, last = cell_def - cells = [] - rows[(first.row.index..last.row.index)].each do |r| - r.cells[(first.index..last.index)].each do |c| - cells << c - end - end - cells - end - + first, last = cell_def + cells = [] + rows[(first.row.index..last.row.index)].each do |r| + r.cells[(first.index..last.index)].each do |c| + cells << c + end + end + cells + 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 def protected_ranges - @protected_ranges ||= SimpleTypedList.new ProtectedRange + @protected_ranges ||= ProtectedRanges.new self + # SimpleTypedList.new ProtectedRange end + # conditional formattings + # @return [Array] def conditional_formattings - @conditional_formattings ||= [] + @conditional_formattings ||= ConditionalFormattings.new self end - + + # data validations array + # @return [Array] def data_validations - @data_validations ||= [] + @data_validations ||= DataValidations.new self end - # An array of merged cell ranges e.d "A1:B3" - # Content and formatting is read from the first cell. - # @return Array + # merged cells array + # @return [Array] def merged_cells - @merged_cells ||= [] + @merged_cells ||= MergedCells.new self end - + # Helper method for parsingout the root node for worksheet # @return [String] def worksheet_node "<worksheet xmlns=\"%s\" xmlns:r=\"%s\">" % [XML_NS, XML_NS_R] end - # Helper method fo parsing out the sheetPr node - # @return [String] - def sheet_pr_node - return '' unless fit_to_page? - "<sheetPr><pageSetUpPr fitToPage=\"%s\"></pageSetUpPr></sheetPr>" % fit_to_page? - end - - # Helper method for parsing out the demension node - # @return [String] - def dimension_node - return '' if rows.size == 0 - "<dimension ref=\"%s\"></dimension>" % dimension - end - - # Helper method for parsing out the autoFilter node - # @return [String] - def auto_filter_node - return '' unless @auto_filter - "<autoFilter ref='%s'></autoFilter>" % @auto_filter + def sheet_data + @sheet_data ||= SheetData.new self end - # Helper method for parsing out the mergedCells node - # @return [String] - def merged_cells_node - return '' if merged_cells.empty? - str = "<mergeCells count='#{merged_cells.size}'>" - merged_cells.each { |merged_cell| str << "<mergeCell ref='#{merged_cell}'></mergeCell>" } - str << '</mergeCells>' + def worksheet_drawing + @worksheet_drawing ||= WorksheetDrawing.new self end def drawing_nodes str = "" - if @drawing - str << relation_node('drawing', DRAWING_R) - end - unless comments.empty? + if @comments str << relation_node('legacyDrawing', VML_DRAWING_R) end str @@ -672,24 +619,6 @@ module Axlsx "<#{node_name} r:id='rId" << (relationships.index{ |r| r.Type == relation_type } + 1).to_s << "'/>" end - # Helper method for parsing out the tableParts node - # @return [String] - def table_parts_node - return '' if tables.empty? - str = "<tableParts count='#{tables.size}'>" - tables.each { |table| str << "<tablePart r:id='#{table.rId}'/>" } - str << '</tableParts>' - end - - # Helper method for parsing out the conditional formattings - # @return [String] - def conditional_formattings_node - return '' if conditional_formattings.size == 0 - str = '' - conditional_formattings.each { |conditional_formatting| str << conditional_formatting.to_xml_string } - str - end - # Helper method for parsing out the dataValidations node # @return [String] def data_validations_node @@ -715,14 +644,8 @@ module Axlsx end def find_or_create_column_info(index, fixed_width=nil) - col = column_info[index] || Col.new(index + 1, index + 1) - column_info[index] = col if index == column_info.size - col + column_info[index] ||= Col.new(index + 1, index + 1) end - def dimension_reference(cell, default) - return default unless cell.respond_to?(:r) - cell.r - end end end diff --git a/lib/axlsx/workbook/worksheet/worksheet_comments.rb b/lib/axlsx/workbook/worksheet/worksheet_comments.rb new file mode 100644 index 00000000..25ddb024 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/worksheet_comments.rb @@ -0,0 +1,38 @@ +module Axlsx + + #A wraper class for comments that defines its on worksheet + #serailization + class WorksheetComments + + def initialize(worksheet) + raise ArugumentError, 'You must provide a worksheet' unless worksheet.is_a?(Worksheet) + @worksheet = worksheet + end + attr_reader :worksheet + + def comments + @comments ||= Comments.new(worksheet) + end + + def add_comment(options={}) + comments.add_comment(options) + end + + def relationships + return [] unless has_comments? + comments.relationships + end + + def has_comments? + !comments.empty? + end + + def index + worksheet.relationships.index { |r| r.Type == VML_DRAWING_R } + 1 + end + def to_xml_string(str = '') + return unless has_comments? + str << "<legacyDrawing r:id='rId#{index}' />" + end + end +end diff --git a/lib/axlsx/workbook/worksheet/worksheet_drawing.rb b/lib/axlsx/workbook/worksheet/worksheet_drawing.rb new file mode 100644 index 00000000..56131540 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/worksheet_drawing.rb @@ -0,0 +1,48 @@ +module Axlsx + + # This is a utility class for serialing the drawing node in a + # worksheet. Drawing objects have their own serialization that exports + # a drawing document. This is only for the single node in the + # worksheet + class WorksheetDrawing + + def initialize(worksheet) + raise ArgumentError, 'you must provide a worksheet' unless worksheet.is_a?(Worksheet) + @worksheet = worksheet + @drawing = nil + end + + attr_reader :worksheet + attr_reader :drawing + + def add_chart(chart_type, options) + @drawing ||= Drawing.new worksheet + drawing.add_chart(chart_type, options) + end + + def add_image(options) + @drawing ||= Drawing.new worksheet + drawing.add_image(options) + end + + def has_drawing? + @drawing.is_a? Drawing + end + + # The relationship required by this object + # @return [Relationship] + def relationship + return unless has_drawing? + Relationship.new(DRAWING_R, "../#{drawing.pn}") + end + + def index + worksheet.relationships.index{ |r| r.Type == DRAWING_R } +1 + end + + def to_xml_string(str = '') + return unless has_drawing? + str << "<drawing r:id='rId#{index}'/>" + end + end +end diff --git a/test/drawing/tc_bar_series.rb b/test/drawing/tc_bar_series.rb index 6b173155..483e561d 100644 --- a/test/drawing/tc_bar_series.rb +++ b/test/drawing/tc_bar_series.rb @@ -5,7 +5,7 @@ class TestBarSeries < Test::Unit::TestCase def setup p = Axlsx::Package.new @ws = p.workbook.add_worksheet :name=>"hmmm" - @chart = @ws.drawing.add_chart Axlsx::Bar3DChart, :title => "fishery" + @chart = @ws.add_chart Axlsx::Bar3DChart, :title => "fishery" @series = @chart.add_series :data=>[0,1,2], :labels=>["zero", "one", "two"], :title=>"bob", :colors => ['FF0000', '00FF00', '0000FF'], :shape => :cone end diff --git a/test/drawing/tc_drawing.rb b/test/drawing/tc_drawing.rb index 4d213d56..f347001f 100644 --- a/test/drawing/tc_drawing.rb +++ b/test/drawing/tc_drawing.rb @@ -9,8 +9,6 @@ class TestDrawing < Test::Unit::TestCase def test_initialization assert(@ws.workbook.drawings.empty?) - assert_equal(@ws.drawing, @ws.workbook.drawings.last, "drawing is added to workbook") - assert(@ws.drawing.anchors.is_a?(Axlsx::SimpleTypedList) && @ws.drawing.anchors.empty?, "anchor list is created and empty") end def test_add_chart @@ -39,7 +37,6 @@ class TestDrawing < Test::Unit::TestCase assert(image.is_a?(Axlsx::Pic)) end def test_charts - assert(@ws.drawing.charts.empty?) chart = @ws.add_chart(Axlsx::Pie3DChart, :title=>"bob", :start_at=>[0,0], :end_at=>[1,1]) assert_equal(@ws.drawing.charts.last, chart, "add chart is returned") chart = @ws.add_chart(Axlsx::Pie3DChart, :title=>"nancy", :start_at=>[1,5], :end_at=>[5,10]) @@ -47,23 +44,26 @@ class TestDrawing < Test::Unit::TestCase end def test_pn + @ws.add_chart(Axlsx::Pie3DChart) assert_equal(@ws.drawing.pn, "drawings/drawing1.xml") end def test_rels_pn + @ws.add_chart(Axlsx::Pie3DChart) assert_equal(@ws.drawing.rels_pn, "drawings/_rels/drawing1.xml.rels") end def test_rId + @ws.add_chart(Axlsx::Pie3DChart) assert_equal(@ws.drawing.rId, "rId1") end def test_index + @ws.add_chart(Axlsx::Pie3DChart) assert_equal(@ws.drawing.index, @ws.workbook.drawings.index(@ws.drawing)) end def test_relationships - assert(@ws.drawing.relationships.empty?) chart = @ws.add_chart(Axlsx::Pie3DChart, :title=>"bob", :start_at=>[0,0], :end_at=>[1,1]) assert_equal(@ws.drawing.relationships.size, 1, "adding a chart adds a relationship") chart = @ws.add_chart(Axlsx::Pie3DChart, :title=>"nancy", :start_at=>[1,5], :end_at=>[5,10]) @@ -72,6 +72,7 @@ class TestDrawing < Test::Unit::TestCase def test_to_xml schema = Nokogiri::XML::Schema(File.open(Axlsx::DRAWING_XSD)) + @ws.add_chart(Axlsx::Pie3DChart) doc = Nokogiri::XML(@ws.drawing.to_xml_string) errors = [] schema.validate(doc).each do |error| diff --git a/test/drawing/tc_line_series.rb b/test/drawing/tc_line_series.rb index 557ba251..cc62005b 100644 --- a/test/drawing/tc_line_series.rb +++ b/test/drawing/tc_line_series.rb @@ -5,7 +5,7 @@ class TestLineSeries < Test::Unit::TestCase def setup p = Axlsx::Package.new @ws = p.workbook.add_worksheet :name=>"hmmm" - chart = @ws.drawing.add_chart Axlsx::Line3DChart, :title => "fishery" + chart = @ws.add_chart Axlsx::Line3DChart, :title => "fishery" @series = chart.add_series :data=>[0,1,2], :labels=>["zero", "one", "two"], :title=>"bob", :color => "#FF0000" end diff --git a/test/drawing/tc_pie_3D_chart.rb b/test/drawing/tc_pie_3D_chart.rb index 5e671989..a941eacd 100644 --- a/test/drawing/tc_pie_3D_chart.rb +++ b/test/drawing/tc_pie_3D_chart.rb @@ -6,7 +6,7 @@ class TestPie3DChart < Test::Unit::TestCase p = Axlsx::Package.new ws = p.workbook.add_worksheet @row = ws.add_row ["one", 1, Time.now] - @chart = ws.drawing.add_chart Axlsx::Pie3DChart, :title => "fishery" + @chart = ws.add_chart Axlsx::Pie3DChart, :title => "fishery" end def teardown diff --git a/test/drawing/tc_pie_series.rb b/test/drawing/tc_pie_series.rb index 2c8859c7..70abb602 100644 --- a/test/drawing/tc_pie_series.rb +++ b/test/drawing/tc_pie_series.rb @@ -5,7 +5,7 @@ class TestPieSeries < Test::Unit::TestCase def setup p = Axlsx::Package.new @ws = p.workbook.add_worksheet :name=>"hmmm" - chart = @ws.drawing.add_chart Axlsx::Pie3DChart, :title => "fishery" + chart = @ws.add_chart Axlsx::Pie3DChart, :title => "fishery" @series = chart.add_series :data=>[0,1,2], :labels=>["zero", "one", "two"], :title=>"bob", :colors => ["FF0000", "00FF00", "0000FF"] end diff --git a/test/drawing/tc_scatter_series.rb b/test/drawing/tc_scatter_series.rb index e97abca7..b43ed171 100644 --- a/test/drawing/tc_scatter_series.rb +++ b/test/drawing/tc_scatter_series.rb @@ -5,7 +5,7 @@ class TestScatterSeries < Test::Unit::TestCase def setup p = Axlsx::Package.new @ws = p.workbook.add_worksheet :name=>"hmmm" - @chart = @ws.drawing.add_chart Axlsx::ScatterChart, :title => "Scatter Chart" + @chart = @ws.add_chart Axlsx::ScatterChart, :title => "Scatter Chart" @series = @chart.add_series :xData=>[1,2,4], :yData=>[1,3,9], :title=>"exponents", :color => 'FF0000' end diff --git a/test/drawing/tc_series.rb b/test/drawing/tc_series.rb index 6b83015a..b32595a7 100644 --- a/test/drawing/tc_series.rb +++ b/test/drawing/tc_series.rb @@ -5,7 +5,7 @@ class TestSeries < Test::Unit::TestCase def setup p = Axlsx::Package.new @ws = p.workbook.add_worksheet :name=>"hmmm" - chart = @ws.drawing.add_chart Axlsx::Chart, :title => "fishery" + chart = @ws.add_chart Axlsx::Chart, :title => "fishery" @series = chart.add_series :title=>"bob" end diff --git a/test/workbook/tc_workbook.rb b/test/workbook/tc_workbook.rb index e6bb48a0..f6f38778 100644 --- a/test/workbook/tc_workbook.rb +++ b/test/workbook/tc_workbook.rb @@ -79,7 +79,7 @@ class TestWorkbook < Test::Unit::TestCase sheet.auto_filter = "A1:B1" end doc = Nokogiri::XML(@wb.to_xml_string) - assert_equal(doc.xpath('//xmlns:workbook/xmlns:definedNames/xmlns:definedName').inner_text, @wb.worksheets[0].abs_auto_filter) + assert_equal(doc.xpath('//xmlns:workbook/xmlns:definedNames/xmlns:definedName').inner_text, @wb.worksheets[0].auto_filter.defined_name) end diff --git a/test/workbook/worksheet/table/tc_table.rb b/test/workbook/worksheet/table/tc_table.rb index c8e56fc3..7c87c69e 100644 --- a/test/workbook/worksheet/table/tc_table.rb +++ b/test/workbook/worksheet/table/tc_table.rb @@ -25,14 +25,6 @@ class TestTable < Test::Unit::TestCase end - def test_charts - assert(@ws.drawing.charts.empty?) - chart = @ws.add_chart(Axlsx::Pie3DChart, :title=>"bob", :start_at=>[0,0], :end_at=>[1,1]) - assert_equal(@ws.drawing.charts.last, chart, "add chart is returned") - chart = @ws.add_chart(Axlsx::Pie3DChart, :title=>"nancy", :start_at=>[1,5], :end_at=>[5,10]) - assert_equal(@ws.drawing.charts.last, chart, "add chart is returned") - end - def test_pn @ws.add_table("A1:D5") assert_equal(@ws.tables.first.pn, "tables/table1.xml") diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb index df808025..3bb9908f 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -113,12 +113,12 @@ class TestWorksheet < Test::Unit::TestCase def test_dimension @ws.add_row [1, 2, 3] @ws.add_row [4, 5, 6] - assert_equal @ws.dimension, "A1:C2" + assert_equal @ws.dimension.sqref, "A1:C2" end def test_dimension_with_empty_row @ws.add_row - assert_equal "A1:AA200", @ws.dimension + assert_equal "A1:AA200", @ws.dimension.sqref end def test_referencing @@ -148,7 +148,9 @@ class TestWorksheet < Test::Unit::TestCase end def test_drawing - assert @ws.drawing.is_a? Axlsx::Drawing + assert @ws.drawing == nil + @ws.add_chart(Axlsx::Pie3DChart) + assert @ws.drawing.is_a?(Axlsx::Drawing) end def test_col_style @@ -306,10 +308,11 @@ 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 + def test_auto_filter @ws.add_row [1, "two", 3] @ws.auto_filter = "A1:C1" - assert_equal(@ws.abs_auto_filter, "'Sheet1'!$A$1:$C$1") + assert_equal("A1:C1", @ws.auto_filter.range) + assert_equal(@ws.auto_filter.defined_name, "'Sheet1'!$A$1:$C$1") end def test_to_xml_string @@ -415,9 +418,9 @@ class TestWorksheet < Test::Unit::TestCase end def test_auto_filter - assert(@ws.auto_filter.nil?) + assert(@ws.auto_filter.range.nil?) assert_raise(ArgumentError) { @ws.auto_filter = 123 } @ws.auto_filter = "A1:D9" - assert_equal(@ws.auto_filter, "A1:D9") + assert_equal(@ws.auto_filter.range, "A1:D9") end end |