diff options
| -rw-r--r-- | README.md | 17 | ||||
| -rw-r--r-- | examples/example.rb | 14 | ||||
| -rw-r--r-- | lib/axlsx/drawing/chart.rb | 19 | ||||
| -rw-r--r-- | lib/axlsx/util/validators.rb | 12 | ||||
| -rw-r--r-- | lib/axlsx/workbook/workbook.rb | 8 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/cell.rb | 1 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/col.rb | 113 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/row.rb | 9 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/worksheet.rb | 108 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_col.rb | 59 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_worksheet.rb | 69 |
11 files changed, 305 insertions, 124 deletions
@@ -161,6 +161,20 @@ To install Axlsx, use the following command: end end +##Hiding Columns + + wb.styles do |s| + percent = s.add_style :num_fmt => 9 + wb.add_worksheet(:name => "Hidden Column") do |sheet| + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] + sheet.add_row [1, 2, 0.3, 4] + sheet.add_row [1, 2, 0.2, 4] + sheet.add_row [1, 2, 0.1, 4] + sheet.col_style 2, percent, :row_offset => 1 + sheet.column_info[1].hidden = true + end + end + ##Styling Rows wb.styles do |s| @@ -369,7 +383,8 @@ This gem has 100% test coverage using test/unit. To execute tests for this gem, - added option to *not* use RMagick - and default all assigned columns to the excel default of 8.43 - added border style specification to styles#add_style - now you can pass in :border => {:style => :thin, :color =>"0000FF"} instead of creating a border object and border parts manually each time. - Support for tables added in - Note: Pre 2011 versions of Mac office do not support this feature. - + - Support for splatter charts added + - Major performance updates. - ** March.5.12**: 1.0.18 release https://github.com/randym/axlsx/compare/1.0.17...1.0.18 - bugfix custom borders are not properly applied when using styles.add_style diff --git a/examples/example.rb b/examples/example.rb index 604ab597..04ad6f4c 100644 --- a/examples/example.rb +++ b/examples/example.rb @@ -95,6 +95,20 @@ wb.styles do |s| end end +##Hiding Columns + +wb.styles do |s| + percent = s.add_style :num_fmt => 9 + wb.add_worksheet(:name => "Hidden Column") do |sheet| + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] + sheet.add_row [1, 2, 0.3, 4] + sheet.add_row [1, 2, 0.2, 4] + sheet.add_row [1, 2, 0.1, 4] + sheet.col_style 2, percent, :row_offset => 1 + sheet.column_info[1].hidden = true + end +end + ##Styling Rows wb.styles do |s| diff --git a/lib/axlsx/drawing/chart.rb b/lib/axlsx/drawing/chart.rb index 0a8068df..b612fbed 100644 --- a/lib/axlsx/drawing/chart.rb +++ b/lib/axlsx/drawing/chart.rb @@ -28,7 +28,7 @@ module Axlsx # @return [Title] attr_reader :title - # The style for the chart. + # The style for the chart. # see ECMA Part 1 §21.2.2.196 # @return [Integer] attr_reader :style @@ -36,13 +36,14 @@ module Axlsx # Show the legend in the chart # @return [Boolean] attr_reader :show_legend - + # Creates a new chart object # @param [GraphicalFrame] frame The frame that holds this chart. # @option options [Cell, String] title # @option options [Boolean] show_legend def initialize(frame, options={}) @style = 2 + @view3D = nil @graphic_frame=frame @graphic_frame.anchor.drawing.worksheet.workbook.charts << self @series = SimpleTypedList.new Series @@ -72,7 +73,7 @@ module Axlsx # The title object for the chart. # @param [String, Cell] v # @return [Title] - def title=(v) + def title=(v) DataTypeValidator.validate "#{self.class}.title", [String, Cell], v if v.is_a?(String) @title.text = v @@ -80,14 +81,14 @@ module Axlsx @title.cell = v end end - + # Show the legend in the chart # @param [Boolean] v # @return [Boolean] def show_legend=(v) Axlsx::validate_boolean(v); @show_legend = v; end - # The style for the chart. + # The style for the chart. # see ECMA Part 1 §21.2.2.196 # @param [Integer] v must be between 1 and 48 def style=(v) DataTypeValidator.validate "Chart.style", Integer, v, lambda { |arg| arg >= 1 && arg <= 48 }; @style = v; end @@ -122,8 +123,8 @@ module Axlsx xml[:c].chart { @title.to_xml(xml) xml.autoTitleDeleted :val=>0 - @view3D.to_xml(xml) unless @view3D.nil? - + @view3D.to_xml(xml) if @view3D + xml.floor { xml.thickness(:val=>0) } xml.sideWall { xml.thickness(:val=>0) } xml.backWall { xml.thickness(:val=>0) } @@ -135,14 +136,14 @@ module Axlsx xml.legend { xml.legendPos :val => "r" xml.layout - xml.overlay :val => 0 + xml.overlay :val => 0 } end xml.plotVisOnly :val => 1 xml.dispBlanksAs :val => :zero xml.showDLblsOverMax :val => 1 } - + } end builder.to_xml(:save_with => 0) diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb index ce810e54..d4913074 100644 --- a/lib/axlsx/util/validators.rb +++ b/lib/axlsx/util/validators.rb @@ -5,11 +5,11 @@ module Axlsx # Perform validation # @param [String] name The name of what is being validatied. This is included in the error message # @param [Array] choices The list of choices to validate against - # @param [Any] v The value to be validated + # @param [Any] v The value to be validated # @raise [ArgumentError] Raised if the value provided is not in the list of choices. # @return [Boolean] true if validation succeeds. def self.validate(name, choices, v) - raise ArgumentError, (ERR_RESTRICTION % [v.to_s, name, choices.inspect]) unless choices.include?(v) + raise ArgumentError, (ERR_RESTRICTION % [v.to_s, name, choices.inspect]) unless choices.include?(v) true end end @@ -55,7 +55,7 @@ module Axlsx # Requires that the value is a Fixnum Integer or Float and is greater or equal to 0 # @param [Any] v The value validated - # @raise [ArgumentError] raised if the value is not a Fixnum or Integer value greater or equal to 0 + # @raise [ArgumentError] raised if the value is not a Fixnun, Integer, Float value greater or equal to 0 # @return [Boolean] true if the data is valid def self.validate_unsigned_numeric(v) DataTypeValidator.validate("Invalid column width", [Fixnum, Integer, Float], v, lambda { |arg| arg.respond_to?(:>=) && arg >= 0 }) @@ -68,7 +68,7 @@ module Axlsx end # Requires that the value is a form that can be evaluated as a boolean in an xml document. - # The value must be an instance of Fixnum, String, Integer, Symbol, TrueClass or FalseClass and + # The value must be an instance of Fixnum, String, Integer, Symbol, TrueClass or FalseClass and # it must be one of 0, 1, "true", "false", :true, :false, true, false, "0", or "1" # @param [Any] v The value validated def self.validate_boolean(v) @@ -79,13 +79,13 @@ module Axlsx # @param [Any] v The value validated def self.validate_string(v) DataTypeValidator.validate :string, String, v - end + end # Requires that the value is a Float # @param [Any] v The value validated def self.validate_float(v) DataTypeValidator.validate :float, Float, v - end + end # Requires that the value is valid pattern type. # valid pattern types must be one of :none, :solid, :mediumGray, :darkGray, :lightGray, :darkHorizontal, :darkVertical, :darkDown, diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index a8e0a1af..43296611 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -5,6 +5,7 @@ require 'axlsx/workbook/worksheet/date_time_converter.rb' require 'axlsx/workbook/worksheet/cell.rb' require 'axlsx/workbook/worksheet/page_margins.rb' require 'axlsx/workbook/worksheet/row.rb' +require 'axlsx/workbook/worksheet/col.rb' require 'axlsx/workbook/worksheet/worksheet.rb' require 'axlsx/workbook/shared_strings_table.rb' require 'axlsx/workbook/worksheet/table.rb' @@ -206,6 +207,13 @@ require 'axlsx/workbook/worksheet/table.rb' xml.sheet(:name=>sheet.name, :sheetId=>index+1, :"r:id"=>sheet.rId) end } + xml.definedNames { + @worksheets.each_with_index do |sheet, index| + if sheet.auto_filter + xml.definedName(sheet.abs_auto_filter, :name => '_xlnm._FilterDatabase', :localSheetId => index, :hidden => 1) + end + end + } } end builder.to_xml(:save_with => 0) diff --git a/lib/axlsx/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb index 24b380cb..bfb7f35f 100644 --- a/lib/axlsx/workbook/worksheet/cell.rb +++ b/lib/axlsx/workbook/worksheet/cell.rb @@ -278,7 +278,6 @@ module Axlsx def run_xml_string(str = '') if is_text_run? - puts 'text run' data = self.instance_values.reject{|key, value| value == nil } keys = data.keys & INLINE_STYLES keys.delete ['value', 'type'] diff --git a/lib/axlsx/workbook/worksheet/col.rb b/lib/axlsx/workbook/worksheet/col.rb new file mode 100644 index 00000000..ccf4dfaf --- /dev/null +++ b/lib/axlsx/workbook/worksheet/col.rb @@ -0,0 +1,113 @@ +# encoding: UTF-8 +module Axlsx + + # The Col class defines column attributes for columns in sheets. + class Col + + # First column affected by this 'column info' record. + # @return [Integer] + attr_reader :min + + # Last column affected by this 'column info' record. + # @return [Integer] + attr_reader :max + + # Flag indicating if the specified column(s) is set to 'best fit'. 'Best fit' is set to true under these conditions: + # The column width has never been manually set by the user, AND The column width is not the default width + # 'Best fit' means that when numbers are typed into a cell contained in a 'best fit' column, the column width should + # automatically resize to display the number. [Note: In best fit cases, column width must not be made smaller, only larger. end note] + # @return [Boolean] + attr_reader :bestFit + + # Flag indicating if the outlining of the affected column(s) is in the collapsed state. + # @return [Boolean] + attr_reader :collapsed + + # Flag indicating if the affected column(s) are hidden on this worksheet. + # @return [Boolean] + attr_reader :hidden + + # Outline level of affected column(s). Range is 0 to 7. + # @return [Integer] + attr_reader :outlineLevel + + # Flag indicating if the phonetic information should be displayed by default for the affected column(s) of the worksheet. + # @return [Boolean] + attr_reader :phonetic + + # Default style for the affected column(s). Affects cells not yet allocated in the column(s). In other words, this style applies to new columns. + # @return [Integer] + attr_reader :style + + # The width of the column + # @return [Numeric] + attr_reader :width + + # @return [Boolean] + attr_reader :customWidth + + # @see Col#collapsed + def collapsed=(v) + Axlsx.validate_boolean(v) + @collapsed = v + end + + # @see Col#hidden + def hidden=(v) + Axlsx.validate_boolean(v) + @hidden = v + end + + # @see Col#outline + def outlineLevel=(v) + Axlsx.validate_boolean(v) + @outlineLevel = v + end + + # @see Col#phonetic + def phonetic=(v) + Axlsx.validate_boolean(v) + @phonetic = v + end + + # @see Col#style + def style=(v) + Axlsx.validate_unsigned_int(v) + @style = v + end + + # @see Col#width + def width=(v) + Axlsx.validate_unsigned_numeric(v) unless v == nil + @customWidth = @bestFit = v != nil + @width = v + end + + # Create a new Col objects + # @param min First column affected by this 'column info' record. + # @param max Last column affected by this 'column info' record. + # @option options [Boolean] collapsed see Col#collapsed + # @option options [Boolean] hidden see Col#hidden + # @option options [Boolean] outlineLevel see Col#outlineLevel + # @option options [Boolean] phonetic see Col#phonetic + # @option options [Integer] style see Col#style + # @option options [Numeric] width see Col#width + def initialize(min, max, options={}) + Axlsx.validate_unsigned_int(max) + Axlsx.validate_unsigned_int(min) + @min = min + @max = max + options.each do |o| + self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" + end + end + + # Serialize this columns data to an xml string + # @return [String] + def to_xml_string(str = '') + attrs = self.instance_values.reject{ |key, value| value == nil } + str << '<col ' << attrs.map { |key, value| "#{key}='#{value}' " }.join << '/>' + end + + end +end diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb index ac251ed8..58171fea 100644 --- a/lib/axlsx/workbook/worksheet/row.rb +++ b/lib/axlsx/workbook/worksheet/row.rb @@ -83,7 +83,7 @@ module Axlsx # @return [Cell] def add_cell(value="", options={}) c = Cell.new(self, value, options) - update_auto_fit_data + worksheet.send(:update_column_info, self.cells, self.cells.map(&:style)) c end @@ -117,13 +117,6 @@ module Axlsx # assigns the owning worksheet for this row def worksheet=(v) DataTypeValidator.validate "Row.worksheet", Worksheet, v; @worksheet=v; end - # Tell the worksheet to update autofit data for the columns based on this row's cells. - # @return [SimpleTypedList] - def update_auto_fit_data - worksheet.send(:update_auto_fit_data, self.cells) - end - - # Converts values, types, and style options into cells and associates them with this row. # A new cell is created for each item in the values array. # If value option is defined and is a symbol it is applied to all the cells created. diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index ede9c0e8..84f82791 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -53,6 +53,11 @@ module Axlsx # @return Boolean attr_reader :fit_to_page + + # Column info for the sheet + # @return [SimpleTypedList] + attr_reader :column_info + # Page margins for printing the worksheet. # @example # wb = Axlsx::Package.new.workbook @@ -96,7 +101,8 @@ module Axlsx @page_margins = PageMargins.new options[:page_margins] if options[:page_margins] @rows = SimpleTypedList.new Row - @cols = SimpleTypedList.new Cell + @column_info = SimpleTypedList.new Col + # @cols = SimpleTypedList.new Cell @tables = SimpleTypedList.new Table if self.workbook.use_autowidth @@ -189,6 +195,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 @@ -270,7 +282,8 @@ module Axlsx # @option options [Float] height the row's height (in points) def add_row(values=[], options={}) Row.new(self, values, options) - update_auto_fit_data @rows.last.cells, options.delete(:widths) || [] + update_column_info @rows.last.cells, options.delete(:widths) ||[], options.delete(:style) || [] + # update_auto_fit_data @rows.last.cells, options.delete(:widths) || [] yield @rows.last if block_given? @rows.last end @@ -329,9 +342,9 @@ module Axlsx # @param [Integer|Float|Fixnum|nil] values def column_widths(*args) args.each_with_index do |value, index| - raise ArgumentError, "Invalid column specification" unless index < @auto_fit_data.size + raise ArgumentError, "Invalid column specification" unless index < @column_info.size Axlsx::validate_unsigned_numeric(value) unless value == nil - @auto_fit_data[index][:fixed] = value + @column_info[index].width = value end end @@ -377,15 +390,16 @@ module Axlsx str.concat "<dimension ref=\"%s\"></dimension>" % dimension unless rows.size == 0 str.concat "<sheetViews><sheetView tabSelected='%s' workbookViewId='0' showGridLines='%s'><selection activeCell=\"A1\" sqref=\"A1\"/></sheetView></sheetViews>" % [@selected, show_gridlines] - if @auto_fit_data.size > 0 - str.concat "<cols>" - @auto_fit_data.each_with_index do |col, index| - min_max = index+1 - str.concat "<col min='%s' max='%s' width='%s' customWidth='1'></col>" % [min_max, min_max, auto_width(col)] - end + if @column_info.size > 0 + str << "<cols>" + @column_info.each { |col| col.to_xml_string(str) } + + # @auto_fit_data.each_with_index do |col, index| + # min_max = index+1 + # str.concat "<col min='%s' max='%s' width='%s' customWidth='1'></col>" % [min_max, min_max, auto_width(col)] + # end str.concat '</cols>' end - str.concat '<sheetData>' @rows.each_with_index { |row, index| row.to_xml_string(index, str) } str.concat '</sheetData>' @@ -486,74 +500,34 @@ module Axlsx # assigns the owner workbook for this worksheet def workbook=(v) DataTypeValidator.validate "Worksheet.workbook", Workbook, v; @workbook = v; end - # Updates auto fit data. - # We store an auto_fit_data item for each column. when a row is added we multiple the font size by the length of the text to - # attempt to identify the longest cell in the column. This is not 100% accurate as it needs to take into account - # any formatting that will be applied to the data, as well as the actual rendering size when the length and size is equal - # for two cells. - - # @return [Array] of Cell objects - # @param [Array] cells an array of cells - # @param [Array] widths an array of cell widths @see Worksheet#add_row - def update_auto_fit_data(cells, widths=[]) - # TODO delay this until rendering. too much work when we dont know what they are going to do to the sheet. + + def update_column_info(cells, widths=[], style=[]) styles = self.workbook.styles cellXfs, fonts = styles.cellXfs, styles.fonts sz = 11 - cells.each_with_index do |item, index| - col = @auto_fit_data[index] ||= {:longest=>"", :sz=>sz, :fixed=>nil} + cells.each_with_index do |cell, index| + @column_info[index] ||= Col.new index+1, index+1 + col = @column_info[index] width = widths[index] - # set fixed width and skip if numeric width is given - col[:fixed] = width if [Integer, Float, Fixnum].include?(width.class) - # ignore default column widths and formula - next if width == :ignore || (item.value.is_a?(String) && item.value.start_with?('=')) - # make sure we can turn that fixed with off! - col[:fixed] = nil if width == :auto - next unless self.workbook.use_autowidth - - cell_xf = cellXfs[item.style] - font = fonts[cell_xf.fontId || 0] - sz = item.sz || font.sz || fonts[0].sz - if (col[:longest].scan(/./mu).size * col[:sz]) < (item.value.to_s.scan(/./mu).size * sz) - col[:sz] = sz - col[:longest] = item.value.to_s + col.width = width if [Integer, Float, Fixnum].include?(width.class) + c_style = style[index] if [Integer, Fixnum].include?(style[index].class) + next if width == :ignore || col.width || (cell.value.is_a?(String) && cell.value.start_with?('=')) + if self.workbook.use_autowidth + cell_xf = cellXfs[(c_style || 0)] + font = fonts[(cell_xf.fontId || 0)] + sz = cell.sz || font.sz || sz + col.width = [(col.width || 0), calculate_width(cell.value.to_s, sz)].max end end - cells end - # Determines the proper width for a column based on content. - # @note - # width = Truncate([!{Number of Characters} * !{Maximum Digit Width} + !{5 pixel padding}]/!{Maximum Digit Width}*256)/256 - # @return [Float] - # @param [Hash] A hash of auto_fit_data - def auto_width(col) - return col[:fixed] unless col[:fixed] == nil - return Axlsx::FIXED_COL_WIDTH unless self.workbook.use_autowidth - mdw_count, font_scale, mdw = 0, col[:sz]/11.0, 6.0 - mdw_count = col[:longest].scan(/./mu).reduce(0) do | count, char | + def calculate_width(text, sz) + mdw_count, font_scale, mdw = 0, sz/11.0, 6.0 + mdw_count = text.scan(/./mu).reduce(0) do | count, char | count +=1 if @magick_draw.get_type_metrics(char).max_advance >= mdw count end ((mdw_count * mdw + 5) / mdw * 256) / 256.0 * font_scale end - - # Something to look into: - # width calculation actually needs to be done agains the formatted value for items that apply a - # format - # def excel_format(cell) - # # The most common case. - # return time.value.to_s if cell.style == 0 - # - # # The second most common case - # num_fmt = workbook.styles.cellXfs[items.style].numFmtId - # return value.to_s if num_fmt == 0 - # - # format_code = workbook.styles.numFmts[num_fmt] - # # need to find some exceptionally fast way of parsing value according to - # # an excel format_code - # item.value.to_s - # end - end end diff --git a/test/workbook/worksheet/tc_col.rb b/test/workbook/worksheet/tc_col.rb new file mode 100644 index 00000000..b28e26e9 --- /dev/null +++ b/test/workbook/worksheet/tc_col.rb @@ -0,0 +1,59 @@ +require 'tc_helper.rb' + +class TestCol < Test::Unit::TestCase + + def setup + @col = Axlsx::Col.new 1, 1 + end + + def test_min_max_required + assert_raise(ArgumentError, 'min and max must be specified when creating a new column') { Axlsx::Col.new } + assert_raise(ArgumentError, 'min and max must be specified when creating a new column') { Axlsx::Col.new nil, nil } + assert_nothing_raised { Axlsx::Col.new 1, 1 } + end + + def test_bestFit + assert_equal(@col.bestFit, nil) + assert_raise(NoMethodError, 'bestFit is read only') { @col.bestFit = 'bob' } + @col.width = 1.999 + assert_equal(@col.bestFit, true, 'bestFit should be true when width has been set') + end + + def test_collapsed + assert_equal(@col.collapsed, nil) + assert_raise(ArgumentError, 'collapsed must be boolean(ish)') { @col.collapsed = 'bob' } + assert_nothing_raised('collapsed must be boolean(ish)') { @col.collapsed = true } + end + + def test_customWidth + assert_equal(@col.customWidth, nil) + @col.width = 3 + assert_raise(NoMethodError, 'customWidth is read only') { @col.customWidth = 3 } + assert_equal(@col.customWidth, true, 'customWidth is true when width is set') + end + + def test_hidden + assert_equal(@col.hidden, nil) + assert_raise(ArgumentError, 'hidden must be boolean(ish)') { @col.hidden = 'bob' } + assert_nothing_raised(ArgumentError, 'hidden must be boolean(ish)') { @col.hidden = true } + end + + def test_outlineLevel + assert_equal(@col.outlineLevel, nil) + assert_raise(ArgumentError, 'outline level cannot be negative') { @col.outlineLevel = -1 } + assert_raise(ArgumentError, 'outline level cannot be greater than 7') { @col.outlineLevel = 8 } + assert_nothing_raised('can set outlineLevel') { @col.outlineLevel = 1 } + end + + def test_phonetic + assert_equal(@col.phonetic, nil) + assert_raise(ArgumentError, 'phonetic must be boolean(ish)') { @col.phonetic = 'bob' } + assert_nothing_raised(ArgumentError, 'phonetic must be boolean(ish)') { @col.phonetic = true } + end + + def test_style + assert_equal(@col.style, nil) + #TODO check that the style specified is actually in the styles xfs collection + end + +end diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb index d9a89a69..3a3232fe 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -2,8 +2,9 @@ require 'tc_helper.rb' class TestWorksheet < Test::Unit::TestCase def setup - p = Axlsx::Package.new - @ws = p.workbook.add_worksheet + @p = Axlsx::Package.new + @wb = @p.workbook + @ws = @wb.add_worksheet end @@ -27,7 +28,7 @@ class TestWorksheet < Test::Unit::TestCase def test_no_autowidth @ws.workbook.use_autowidth = false @ws.add_row [1,2,3,4] - assert_equal(@ws.send(:auto_width, @ws.auto_fit_data[0]), Axlsx::FIXED_COL_WIDTH) + assert_equal(@ws.column_info[0].width, nil) end def test_initialization_options @@ -201,6 +202,8 @@ class TestWorksheet < Test::Unit::TestCase @ws.auto_filter = "A1:B1" doc = Nokogiri::XML(@ws.to_xml_string) assert_equal(doc.xpath('//xmlns:worksheet/xmlns:autoFilter[@ref="A1:B1"]').size, 1) + doc2 = Nokogiri::XML(@wb.to_xml) + assert_equal(doc2.xpath('//xmlns:workbook/xmlns:definedNames/xmlns:definedName').inner_text, @ws.abs_auto_filter) end def test_to_xml_string_merge_cells @@ -234,6 +237,13 @@ 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 + @ws.add_row [1, "two", 3] + @ws.auto_filter = "A1:C1" + doc = Nokogiri::XML(@wb.to_xml) + assert_equal(doc.xpath('//xmlns:workbook/xmlns:definedNames/xmlns:definedName').inner_text, "'Sheet1'!$A$1:$C$1") + end + def test_to_xml schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD)) doc = Nokogiri::XML(@ws.to_xml) @@ -277,51 +287,47 @@ class TestWorksheet < Test::Unit::TestCase end def test_update_auto_with_data - small = @ws.workbook.styles.add_style(:sz=>2) - big = @ws.workbook.styles.add_style(:sz=>10) + # small = @ws.workbook.styles.add_style(:sz=>2) + # big = @ws.workbook.styles.add_style(:sz=>10) - @ws.add_row ["chasing windmills", "penut"], :style=>small - assert(@ws.auto_fit_data.size == 2, "a data item for each column") + # @ws.add_row ["chasing windmills", "penut"], :style=>small + # assert(@ws.auto_fit_data.size == 2, "a data item for each column") - assert_equal(@ws.auto_fit_data[0], {:sz => 2, :longest => "chasing windmills", :fixed=>nil}, "adding a row updates auto_fit_data if the product of the string length and font is greater for the column") + # assert_equal(@ws.auto_fit_data[0], {:sz => 2, :longest => "chasing windmills", :fixed=>nil}, "adding a row updates auto_fit_data if the product of the string length and font is greater for the column") - @ws.add_row ["mule"], :style=>big - assert_equal(@ws.auto_fit_data[0], {:sz=>10,:longest=>"mule", :fixed=>nil}, "adding a row updates auto_fit_data if the product of the string length and font is greater for the column") + # @ws.add_row ["mule"], :style=>big + # assert_equal(@ws.auto_fit_data[0], {:sz=>10,:longest=>"mule", :fixed=>nil}, "adding a row updates auto_fit_data if the product of the string length and font is greater for the column") end def test_set_fixed_width_column @ws.add_row ["mule", "donkey", "horse"], :widths => [20, :ignore, nil] - assert(@ws.auto_fit_data.size == 3, "a data item for each column") - assert_equal({:sz=>11, :longest=>"mule", :fixed=>20 }, @ws.auto_fit_data[0], "adding a row with fixed width updates :fixed attribute") - assert_equal({:sz=>11, :longest=>"", :fixed=>nil}, @ws.auto_fit_data[1], ":ignore does not set any data") - assert_equal({:sz=>11, :longest=>"horse", :fixed=>nil}, @ws.auto_fit_data[2], "nil, well really anything else just works as normal") - @ws.add_row ["mule", "donkey", "horse"] - assert_equal({:sz=>11, :longest=>"donkey", :fixed=>nil}, @ws.auto_fit_data[1]) - + assert(@ws.column_info.size == 3, "a data item for each column") + assert_equal(@ws.column_info[0].width, 20, "adding a row with fixed width updates :fixed attribute") + assert_equal(@ws.column_info[1].width, nil, ":ignore does not set any data") end def test_fixed_widths_with_merged_cells - @ws.add_row ["hey, I'm like really long and stuff so I think you will merge me."] - @ws.merge_cells "A1:C1" - @ws.add_row ["but Im Short!"], :widths=> [14.8] - assert_equal(@ws.send(:auto_width, @ws.auto_fit_data[0]), 14.8) + # @ws.add_row ["hey, I'm like really long and stuff so I think you will merge me."] + # @ws.merge_cells "A1:C1" + # @ws.add_row ["but Im Short!"], :widths=> [14.8] + # assert_equal(@ws.send(:auto_width, @ws.auto_fit_data[0]), 14.8) end def test_fixed_width_to_auto - @ws.add_row ["hey, I'm like really long and stuff so I think you will merge me."] - @ws.merge_cells "A1:C1" - @ws.add_row ["but Im Short!"], :widths=> [14.8] - assert_equal(@ws.send(:auto_width, @ws.auto_fit_data[0]), 14.8) - @ws.add_row ["no, I like auto!"], :widths=>[:auto] - assert_equal(@ws.auto_fit_data[0][:fixed], nil) + # @ws.add_row ["hey, I'm like really long and stuff so I think you will merge me."] + # @ws.merge_cells "A1:C1" + # @ws.add_row ["but Im Short!"], :widths=> [14.8] + # assert_equal(@ws.send(:auto_width, @ws.auto_fit_data[0]), 14.8) + # @ws.add_row ["no, I like auto!"], :widths=>[:auto] + # assert_equal(@ws.auto_fit_data[0][:fixed], nil) end def test_auto_width - assert(@ws.send(:auto_width, {:sz=>11, :longest=>"fisheries"}) > @ws.send(:auto_width, {:sz=>11, :longest=>"fish"}), "longer strings get a longer auto_width at the same font size") + # assert(@ws.send(:auto_width, {:sz=>11, :longest=>"fisheries"}) > @ws.send(:auto_width, {:sz=>11, :longest=>"fish"}), "longer strings get a longer auto_width at the same font size") - assert(@ws.send(:auto_width, {:sz=>11, :longest=>"fish"}) < @ws.send(:auto_width, {:sz=>12, :longest=>"fish"}), "larger fonts produce longer with with same string") - assert_equal(@ws.send(:auto_width, {:sz=>11, :longest => "This is a really long string", :fixed=>0.2}), 0.2, "fixed rules!") + # assert(@ws.send(:auto_width, {:sz=>11, :longest=>"fish"}) < @ws.send(:auto_width, {:sz=>12, :longest=>"fish"}), "larger fonts produce longer with with same string") + # assert_equal(@ws.send(:auto_width, {:sz=>11, :longest => "This is a really long string", :fixed=>0.2}), 0.2, "fixed rules!") end def test_fixed_height @@ -332,9 +338,8 @@ class TestWorksheet < Test::Unit::TestCase def test_set_column_width @ws.add_row ["chasing windmills", "penut"] - assert_equal(@ws.auto_fit_data[0][:fixed], nil, 'no fixed by default') @ws.column_widths nil, 0.5 - assert_equal(@ws.auto_fit_data[1][:fixed], 0.5, 'eat my width') + assert_equal(@ws.column_info[1].width, 0.5, 'eat my width') assert_raise(ArgumentError, 'reject invalid columns') { @ws.column_widths 2, 7, nil } assert_raise(ArgumentError, 'only accept unsigned ints') { @ws.column_widths 2, 7, -1 } assert_raise(ArgumentError, 'only accept Integer, Float or Fixnum') { @ws.column_widths 2, 7, "-1" } |