diff options
86 files changed, 1145 insertions, 624 deletions
@@ -13,3 +13,7 @@ tmp *.pem *.pfx examples/sprk2012 +.ruby-version +.bundle/config +.~lock* +*.qcachegrind diff --git a/.travis.yml b/.travis.yml index bf13d293..1dd9af85 100644 --- a/.travis.yml +++ b/.travis.yml @@ -1,4 +1,5 @@ language: ruby +bundler_args: --without profile notifications: irc: "irc.freenode.org#axlsx" email: @@ -9,6 +10,7 @@ rvm: - 1.8.7 - 1.9.2 - 1.9.3 + - 2.0.0 - jruby-18mode - rbx-18mode - rbx-19mode @@ -19,5 +21,6 @@ matrix: allow_failures: - rvm: ruby-head - rvm: jruby-head + - rvm: 1.8.7 before_install: - gem install nokogiri -- --with-cflags='--std=gnu99' diff --git a/CHANGELOG.md b/CHANGELOG.md index a1e392ff..6fabb87e 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,6 +1,13 @@ CHANGELOG --------- - +- **November.5.12**:1.3.2 + - MASSIVE REFACTORING + - Patch for apostrophes in worksheet names + - added sheet_by_name for workbook so you can now find your worksheets + by name + - added insert_worksheet so you can now add a worksheet to an + arbitrary position in the worksheets list. + - reduced memory consumption for package parts post serialization - **September.30.12**: 1.3.1 - Improved control character handling - Added stored auto filter values and date grouping items @@ -221,3 +228,4 @@ in value caches - Updated documentation ##October.20.11: 0.1.0 release + @@ -1,7 +1,12 @@ -source :rubygems +source "https://rubygems.org" gemspec group :test do - gem 'rake', '0.8.7' if RUBY_VERSION == "1.9.2" - gem 'rake', '>= 0.8.7' unless RUBY_VERSION == "1.9.2" + gem "rake", "0.8.7" if RUBY_VERSION == "1.9.2" + gem "rake", ">= 0.8.7" unless RUBY_VERSION == "1.9.2" + gem "simplecov" +end + +group :profile do + gem 'ruby-prof' end @@ -1,6 +1,6 @@ Axlsx: Office Open XML Spreadsheet Generation ==================================== -[](http://travis-ci.org/randym/axlsx/) +[](http://travis-ci.org/randym/axlsx/) If you are using axlsx for comercial purposes, or just want to show your appreciation for the gem, please don't hesitate to make a donation. @@ -17,27 +17,28 @@ appreciation for the gem, please don't hesitate to make a donation. **Author**: Randy Morgan -**Copyright**: 2011 - 2012 +**Copyright**: 2011 - 2013 **License**: MIT License -**Latest Version**: 1.3.5 +**Latest Version**: 1.3.7 -**Ruby Version**: 1.8.7, 1.9.2, 1.9.3 +**Ruby Version**: 1.8.7 (soon to be depreciated!!!), 1.9.2, 1.9.3, 2.0.0 **JRuby Version**: 1.6.7 1.8 and 1.9 modes **Rubinius Version**: rubinius 2.0.0dev * lower versions may run, this gem always tests against head. -**Release Date**: February 4th 2013 +**Release Date**: June ? 2013 If you are working in rails, or with active record see: -* http://github.com/randym/acts_as_xlsx +[acts_as_xlsx](http://github.com/randym/acts_as_xlsx) + acts_as_xlsx is a simple ActiveRecord mixin that lets you generate a workbook with: - ```ruby - Posts.where(created_at > Time.now-30.days).to_xlsx - ``` +```ruby +Posts.where(created_at > Time.now-30.days).to_xlsx +``` ** and ** @@ -47,6 +48,12 @@ Axlsx_Rails provides an Axlsx renderer so you can move all your spreadsheet code There are guides for using axlsx and acts_as_xlsx here: [http://axlsx.blog.randym.net](http://axlsx.blog.randym.net) +If you are working with ActiveAdmin see: + +[activeadmin_axlsx](http://github.com/randym/activeadmin_axlsx) + +It provies a plugin and dsl for generating downloadable reports. + The examples directory contains a number of more specific examples as well. @@ -63,45 +70,45 @@ With Axlsx you can create excel worksheets with charts, images (with links), aut Feature List ------------ -**1. Author xlsx documents: Axlsx is made to let you easily and quickly generate professional xlsx based reports that can be validated before serialization. +1. Author xlsx documents: Axlsx is made to let you easily and quickly generate professional xlsx based reports that can be validated before serialization. -**2. Generate 3D Pie, Line, Scatter and Bar Charts: With Axlsx chart generation and management is as easy as a few lines of code. You can build charts based off data in your worksheet or generate charts without any data in your sheet at all. Customize gridlines, label rotation and series colors as well. +2. Generate 3D Pie, Line, Scatter and Bar Charts: With Axlsx chart generation and management is as easy as a few lines of code. You can build charts based off data in your worksheet or generate charts without any data in your sheet at all. Customize gridlines, label rotation and series colors as well. -**3. Custom Styles: With guaranteed document validity, you can style borders, alignment, fills, fonts, and number formats in a single line of code. Those styles can be applied to an entire row, or a single cell anywhere in your workbook. +3. Custom Styles: With guaranteed document validity, you can style borders, alignment, fills, fonts, and number formats in a single line of code. Those styles can be applied to an entire row, or a single cell anywhere in your workbook. -**4. Automatic type support: Axlsx will automatically determine the type of data you are generating. In this release Float, Integer, String, Date, Time and Boolean types are automatically identified and serialized to your spreadsheet. +4. Automatic type support: Axlsx will automatically determine the type of data you are generating. In this release Float, Integer, String, Date, Time and Boolean types are automatically identified and serialized to your spreadsheet. -**5. Automatic and fixed column widths: Axlsx will automatically determine the appropriate width for your columns based on the content in the worksheet, or use any value you specify for the really funky stuff. +5. Automatic and fixed column widths: Axlsx will automatically determine the appropriate width for your columns based on the content in the worksheet, or use any value you specify for the really funky stuff. -**6. Support for automatically formatted 1904 and 1900 epochs configurable in the workbook. +6. Support for automatically formatted 1904 and 1900 epochs configurable in the workbook. -**7. Add jpg, gif and png images to worksheets with hyperlinks +7. Add jpg, gif and png images to worksheets with hyperlinks -**8. Reference cells in your worksheet with "A1" and "A1:D4" style references or from the workbook using "Sheet1!A3:B4" style references +8. Reference cells in your worksheet with "A1" and "A1:D4" style references or from the workbook using "Sheet1!A3:B4" style references -**9. Cell level style overrides for default and customized style objects +9. Cell level style overrides for default and customized style objects -**10. Support for formulas, merging, row and column outlining as well as +10. Support for formulas, merging, row and column outlining as well as cell level input data validation. -**12. Auto filtering tables with worksheet.auto_filter as well as support for Tables +12. Auto filtering tables with worksheet.auto_filter as well as support for Tables -**13. Export using shared strings or inline strings so we can inter-op with iWork Numbers (sans charts for now). +13. Export using shared strings or inline strings so we can inter-op with iWork Numbers (sans charts for now). -**14. Output to file or StringIO +14. Output to file or StringIO -**15. Support for page margins and print options +15. Support for page margins and print options -**16. Support for password and non password based sheet protection. +16. Support for password and non password based sheet protection. -**17. First stage interoperability support for GoogleDocs, LibreOffice, +17. First stage interoperability support for GoogleDocs, LibreOffice, and Numbers -**18. Support for defined names, which gives you repeated header rows for printing. +18. Support for defined names, which gives you repeated header rows for printing. -**19. Data labels for charts as well as series color customization. +19. Data labels for charts as well as series color customization. -**20. Support for sheet headers and footers +20. Support for sheet headers and footers Installing @@ -152,6 +159,19 @@ This gem has 100% test coverage using test/unit. To execute tests for this gem, #Change log --------- +- **June.?.13**:1.3.7 + - Bugfix: transposition of cells for Worksheet#cols now supports + incongruent column counts.counts + - Added space preservation for cell text. This will allow whitespace + in cell text both when using shared strings and when serializing + directly to the cell. +- **April.24.13**:1.3.6 + - Fixed LibreOffice/OpenOffice issue to properly apply colors to lines + in charts. + - Added support for specifying between/notBetween formula in an array. + *thanks* straydogstudio! + - Added standard line chart support. *thanks* scambra + - Fixed straydogstudio's link in the README. *thanks* nogara! - **February.4.13**:1.3.5 - converted vary_colors for chart data to instance variable with appropriate defulats for the various charts. - Added trust_input method on Axlsx to instruct the serializer to skip HTML escaping. This will give you a tremendous performance boost, @@ -172,14 +192,6 @@ This gem has 100% test coverage using test/unit. To execute tests for this gem, - Improvements in autowidth calculation. - **November.8.12**:1.3.3 - Patched cell run styles for u and validation for family -- **November.5.12**:1.3.2 - - MASSIVE REFACTORING - - Patch for apostrophes in worksheet names - - added sheet_by_name for workbook so you can now find your worksheets - by name - - added insert_worksheet so you can now add a worksheet to an - arbitrary position in the worksheets list. - - reduced memory consumption for package parts post serialization Please see the {file:CHANGELOG.md} document for past release information. @@ -247,7 +259,7 @@ done without the help of the people below. [rfc2616](https://github.com/rfc2616) - for FINALLY working out the interop issues with google docs. -[straydogstudio](https://github.com/straydocstudio) - For making an AWESOME axlsx templating gem for rails. +[straydogstudio](https://github.com/straydogstudio) - For making an AWESOME axlsx templating gem for rails. [MitchellAJ](https://github.com/MitchellAJ) - For catching a bug in font_size calculations, finding some old code in an example and above all for reporting all of that brilliantly @@ -262,11 +274,15 @@ air and our feet on the ground. [ball-hayden](https://github.com/ball-hayden) - For making sure we only get the right characters in our sheet names. -[nibus](https://github.com/nibus) - For patching sheet name unequeness. +[nibus](https://github.com/nibus) - For patching sheet name uniqueness. + +[scambra](https://github.com/scambra) - For keeping our lines in line! + +[agardiner](https://github.com/agardiner) - For the preservation of space. #Copyright and License ---------- -Axlsx © 2011-2012 by [Randy Morgan](mailto:[email protected]). +Axlsx © 2011-2013 by [Randy Morgan](mailto:[email protected]). Axlsx is licensed under the MIT license. Please see the LICENSE document for more information. diff --git a/axlsx.gemspec b/axlsx.gemspec index 511b1c16..77d89ccc 100644 --- a/axlsx.gemspec +++ b/axlsx.gemspec @@ -17,13 +17,12 @@ Gem::Specification.new do |s| s.test_files = Dir.glob("{test/**/*}") s.add_runtime_dependency 'nokogiri', '>= 1.4.1' - s.add_runtime_dependency 'rubyzip', '>= 0.9.5' + s.add_runtime_dependency 'rubyzip', '>= 0.9.9' s.add_runtime_dependency "htmlentities", "~> 4.3.1" -# This has been removed until JRuby can support the native extensions for redcarpet or yard removes the dependency s.add_development_dependency 'yard' s.add_development_dependency 'kramdown' - s.add_development_dependency 'simplecov' + s.add_development_dependency 'timecop', "~> 0.6.1" s.required_ruby_version = '>= 1.8.7' s.require_path = 'lib' end @@ -0,0 +1 @@ +/opt/boxen/rbenv/versions/2.0.0-p0/lib/ruby/gems/2.0.0/gems/axlsx-1.3.6/lib/axlsx.rb diff --git a/examples/2010_comments.rb b/examples/2010_comments.rb new file mode 100644 index 00000000..6a7bedf8 --- /dev/null +++ b/examples/2010_comments.rb @@ -0,0 +1,17 @@ +#!/usr/bin/env ruby -w -s +# -*- coding: utf-8 -*- +$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" + +#```ruby +require 'axlsx' +p = Axlsx::Package.new +p.workbook.add_worksheet(:name => 'Excel 2010 comments') do |sheet| + sheet.add_row ['Cell with visible comment'] + sheet.add_row + sheet.add_row + sheet.add_row ['Cell with hidden comment'] + + sheet.add_comment :ref => 'A1', :author => 'XXX', :text => 'Visibile' + sheet.add_comment :ref => 'A4', :author => 'XXX', :text => 'Hidden', :visible => false +end +p.serialize('excel_2010_comment_test.xlsx') diff --git a/examples/conditional_formatting/example_conditional_formatting.rb b/examples/conditional_formatting/example_conditional_formatting.rb index ab49d238..f5823ab4 100644 --- a/examples/conditional_formatting/example_conditional_formatting.rb +++ b/examples/conditional_formatting/example_conditional_formatting.rb @@ -11,8 +11,8 @@ percent = book.styles.add_style(:format_code => "0.00%", :border => Axlsx::STYLE money = book.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_THIN_BORDER) # define the style for conditional formatting -profitable = book.styles.add_style( :fg_color=>"FF428751", - :type => :dxf) +profitable = book.styles.add_style( :fg_color => "FF428751", :type => :dxf ) +unprofitable = wb.styles.add_style( :fg_color => "FF0000", :type => :dxf ) book.add_worksheet(:name => "Cell Is") do |ws| @@ -27,6 +27,8 @@ book.add_worksheet(:name => "Cell Is") do |ws| # Apply conditional formatting to range B3:B100 in the worksheet ws.add_conditional_formatting("B3:B100", { :type => :cellIs, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1 }) +# Apply conditional using the between operator; NOTE: supply an array to :formula for between/notBetween + sheet.add_conditional_formatting("C3:C100", { :type => :cellIs, :operator => :between, :formula => ["0.00%","100.00%"], :dxfId => unprofitable, :priority => 1 }) end book.add_worksheet(:name => "Color Scale") do |ws| diff --git a/examples/example.rb b/examples/example.rb index c9b5564e..218237a6 100755 --- a/examples/example.rb +++ b/examples/example.rb @@ -57,6 +57,7 @@ if examples.include? :basic wb.add_worksheet(:name => "Basic Worksheet") do |sheet| sheet.add_row ["First Column", "Second", "Third"] sheet.add_row [1, 2, 3] + sheet.add_row [' preserving whitespace'] end end #``` @@ -277,11 +278,12 @@ if examples.include? :images img = File.expand_path('../image1.jpeg', __FILE__) # specifying the :hyperlink option will add a hyper link to your image. # @note - Numbers does not support this part of the specification. - sheet.add_image(:image_src => img, :noSelect => true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") do |image| + sheet.add_image(:image_src => img, :noSelect => true, end_at: true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") do |image| image.width=720 image.height=666 image.hyperlink.tooltip = "Labeled Link" image.start_at 2, 2 + image.end_at 200, 200 end end end @@ -309,6 +311,7 @@ end #```ruby if examples.include? :mbcs + wb.styles.fonts.first.name = 'Arial Unicode MS' wb.add_worksheet(:name => "日本語でのシート名") do |sheet| sheet.add_row ["日本語"] sheet.add_row ["华语/華語"] @@ -408,7 +411,7 @@ if examples.include? :bar_chart sheet.add_row ["A Simple Bar Chart"] %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] } sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A6", :end_at => "F20") do |chart| - chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"], :title => sheet["A1"] + chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"], :title => sheet["A1"], :colors => ["00FF00", "0000FF"] end end end @@ -423,7 +426,7 @@ if examples.include? :chart_gridlines sheet.add_row ["Bar Chart without gridlines"] %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] } sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A6", :end_at => "F20") do |chart| - chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"] + chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"], :colors => ["00FF00", "FF0000"] chart.valAxis.gridlines = false chart.catAxis.gridlines = false end @@ -455,14 +458,23 @@ if examples.include? :line_chart 4.times do sheet.add_row [ rand(24)+1, rand(24)+1] end - sheet.add_chart(Axlsx::Line3DChart, :title => "Simple Line Chart", :rotX => 30, :rotY => 20) do |chart| + sheet.add_chart(Axlsx::Line3DChart, :title => "Simple 3D Line Chart", :rotX => 30, :rotY => 20) do |chart| chart.start_at 0, 5 chart.end_at 10, 20 - chart.add_series :data => sheet["A3:A6"], :title => sheet["A2"] - chart.add_series :data => sheet["B3:B6"], :title => sheet["B2"] + chart.add_series :data => sheet["A3:A6"], :title => sheet["A2"], :color => "0000FF" + chart.add_series :data => sheet["B3:B6"], :title => sheet["B2"], :color => "FF0000" chart.catAxis.title = 'X Axis' chart.valAxis.title = 'Y Axis' end + sheet.add_chart(Axlsx::LineChart, :title => "Simple Line Chart", :rotX => 30, :rotY => 20) do |chart| + chart.start_at 0, 21 + chart.end_at 10, 41 + chart.add_series :data => sheet["A3:A6"], :title => sheet["A2"], :color => "FF0000" + chart.add_series :data => sheet["B3:B6"], :title => sheet["B2"], :color => "00FF00" + chart.catAxis.title = 'X Axis' + chart.valAxis.title = 'Y Axis' + end + end end #``` @@ -479,8 +491,8 @@ if examples.include? :scatter_chart sheet.add_chart(Axlsx::ScatterChart, :title => "example 7: Scatter Chart") do |chart| chart.start_at 0, 4 chart.end_at 10, 19 - chart.add_series :xData => sheet["B1:E1"], :yData => sheet["B2:E2"], :title => sheet["A1"] - chart.add_series :xData => sheet["B3:E3"], :yData => sheet["B4:E4"], :title => sheet["A3"] + chart.add_series :xData => sheet["B1:E1"], :yData => sheet["B2:E2"], :title => sheet["A1"], :color => "FF0000" + chart.add_series :xData => sheet["B3:E3"], :yData => sheet["B4:E4"], :title => sheet["A3"], :color => "00FF00" end end end @@ -583,6 +595,8 @@ if examples.include? :comments wb.add_worksheet(:name => 'comments') do |sheet| sheet.add_row ['Can we build it?'] sheet.add_comment :ref => 'A1', :author => 'Bob', :text => 'Yes We Can!' + sheet.add_comment :ref => 'A2', :author => 'Bob', :text => 'Yes We Can! - but I dont think you need to know about it!', :visible => false + end end @@ -636,8 +650,8 @@ if examples.include? :conditional_formatting money = wb.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_THIN_BORDER) # define the style for conditional formatting - profitable = wb.styles.add_style( :fg_color=>"FF428751", - :type => :dxf) + profitable = wb.styles.add_style( :fg_color => "FF428751", :type => :dxf ) + unprofitable = wb.styles.add_style( :fg_color => "FF0000", :type => :dxf ) wb.add_worksheet(:name => "Conditional Cell Is") do |sheet| @@ -652,6 +666,8 @@ if examples.include? :conditional_formatting # Apply conditional formatting to range B3:B100 in the worksheet sheet.add_conditional_formatting("B3:B100", { :type => :cellIs, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1 }) + # Apply conditional using the between operator; NOTE: supply an array to :formula for between/notBetween + sheet.add_conditional_formatting("C3:C100", { :type => :cellIs, :operator => :between, :formula => ["0.00%","100.00%"], :dxfId => unprofitable, :priority => 1 }) end wb.add_worksheet(:name => "Conditional Color Scale") do |sheet| diff --git a/examples/underline.rb b/examples/underline.rb new file mode 100644 index 00000000..ccd1b394 --- /dev/null +++ b/examples/underline.rb @@ -0,0 +1,13 @@ +$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" +require 'axlsx' +p = Axlsx::Package.new +p.workbook do |wb| + wb.styles do |s| + no_underline = s.add_style :sz => 10, :b => true, :u => false, :alignment => { :horizontal=> :right } + wb.add_worksheet(:name => 'wunderlinen') do |sheet| + sheet.add_row %w{a b c really?}, :style => no_underline + end + end +end +p.serialize 'no_underline.xlsx' + diff --git a/exclusive.rb b/exclusive.rb new file mode 100644 index 00000000..71339146 --- /dev/null +++ b/exclusive.rb @@ -0,0 +1,32 @@ + +def exclusively_true(hash, key) + #clone = hash.clone + return false unless hash.delete(key) == true + !hash.has_value? true +end + +require 'test/unit' +class TestExclusive < Test::Unit::TestCase + def setup + @test_hash = {foo: true, bar: false, hoge: false} + end + def test_exclusive + assert_equal(true, exclusively_true(@test_hash, :foo)) + end + def test_inexclusive + @test_hash[:bar] = true + assert_equal(false, exclusively_true(@test_hash, :foo)) + end +end + +require 'benchmark' + +h = {foo: true} +999.times {|i| h["a#{i}"] = false} +Benchmark.bmbm(30) do |x| + x.report('exclusively_true') do + 1000.times do + exclusively_true(h, :foo) + end + end +end diff --git a/lib/axlsx/doc_props/core.rb b/lib/axlsx/doc_props/core.rb index d71300e0..a74031f2 100644 --- a/lib/axlsx/doc_props/core.rb +++ b/lib/axlsx/doc_props/core.rb @@ -8,14 +8,19 @@ module Axlsx # Creates a new Core object. # @option options [String] creator + # @option options [Time] created def initialize(options={}) @creator = options[:creator] || 'axlsx' + @created = options[:created] end # The author of the document. By default this is 'axlsx' # @return [String] attr_accessor :creator + # Creation time of the document. If nil, the current time will be used. + attr_accessor :created + # serializes the core.xml document # @return [String] def to_xml_string(str = '') @@ -24,7 +29,7 @@ module Axlsx str << 'xmlns:dcmitype="' << CORE_NS_DCMIT << '" xmlns:dcterms="' << CORE_NS_DCT << '" ' str << 'xmlns:xsi="' << CORE_NS_XSI << '">' str << '<dc:creator>' << self.creator << '</dc:creator>' - str << '<dcterms:created xsi:type="dcterms:W3CDTF">' << Time.now.strftime('%Y-%m-%dT%H:%M:%S') << 'Z</dcterms:created>' + str << '<dcterms:created xsi:type="dcterms:W3CDTF">' << (created || Time.now).strftime('%Y-%m-%dT%H:%M:%S') << 'Z</dcterms:created>' str << '<cp:revision>0</cp:revision>' str << '</cp:coreProperties>' end diff --git a/lib/axlsx/drawing/axes.rb b/lib/axlsx/drawing/axes.rb new file mode 100644 index 00000000..bc40e532 --- /dev/null +++ b/lib/axlsx/drawing/axes.rb @@ -0,0 +1,61 @@ +module Axlsx + + # The Axes class creates and manages axis information and + # serialization for charts. + class Axes + + # @param [Hash] options options used to generate axis each key + # should be an axis name like :val_axis and its value should be the + # class of the axis type to construct. The :cat_axis, if there is one, + # must come first (we assume a Ruby 1.9+ Hash or an OrderedHash). + def initialize(options={}) + raise(ArgumentError, "CatAxis must come first") if options.keys.include?(:cat_axis) && options.keys.first != :cat_axis + options.each do |name, axis_class| + add_axis(name, axis_class) + end + end + + # [] provides assiciative access to a specic axis store in an axes + # instance. + # @return [Axis] + def [](name) + axes.assoc(name)[1] + end + + # Serializes the object + # @param [String] str + # @param [Hash] options + # @option options ids + # If the ids option is specified only the axis identifier is + # serialized. Otherwise, each axis is serialized in full. + def to_xml_string(str = '', options = {}) + if options[:ids] + # CatAxis must come first in the XML (for Microsoft Excel at least) + sorted = axes.sort_by { |name, axis| axis.kind_of?(CatAxis) ? 0 : 1 } + sorted.inject(str) { |string, axis| string << '<c:axId val="' << axis[1].id.to_s << '"/>' } + else + axes.each { |axis| axis[1].to_xml_string(str) } + end + end + + # Adds an axis to the collection + # @param [Symbol] name The name of the axis + # @param [Axis] axis_class The axis class to generate + def add_axis(name, axis_class) + axis = axis_class.new + set_cross_axis(axis) + axes << [name, axis] + end + + private + + def axes + @axes ||= [] + end + + def set_cross_axis(axis) + axes.first[1].cross_axis = axis if axes.size == 1 + axis.cross_axis = axes.first[1] unless axes.empty? + end + end +end diff --git a/lib/axlsx/drawing/axis.rb b/lib/axlsx/drawing/axis.rb index 1b55bece..32e40373 100644 --- a/lib/axlsx/drawing/axis.rb +++ b/lib/axlsx/drawing/axis.rb @@ -7,17 +7,13 @@ module Axlsx include Axlsx::OptionsParser # Creates an Axis object - # @param [Integer] ax_id the id of this axis - # @param [Integer] cross_ax the id of the perpendicular axis + # @option options [Axis] cross_axis the perpendicular axis # @option options [Symbol] ax_pos # @option options [Symbol] crosses # @option options [Symbol] tick_lbl_pos # @raise [ArgumentError] If axi_id or cross_ax are not unsigned integers - def initialize(ax_id, cross_ax, options={}) - Axlsx::validate_unsigned_int(ax_id) - Axlsx::validate_unsigned_int(cross_ax) - @ax_id = ax_id - @cross_ax = cross_ax + def initialize(options={}) + @id = rand(8 ** 8) @format_code = "General" @delete = @label_rotation = 0 @scaling = Scaling.new(:orientation=>:minMax) @@ -37,13 +33,13 @@ module Axlsx # the id of the axis. # @return [Integer] - attr_reader :ax_id - alias :axID :ax_id + attr_reader :id + alias :axID :id # The perpendicular axis # @return [Integer] - attr_reader :cross_ax - alias :crossAx :cross_ax + attr_reader :cross_axis + alias :crossAx :cross_axis # The scaling of the axis # @see Scaling @@ -94,6 +90,13 @@ module Axlsx def color=(color_rgb) @color = color_rgb end + + # The crossing axis for this axis + # @param [Axis] axis + def cross_axis=(axis) + DataTypeValidator.validate "#{self.class}.cross_axis", [Axis], axis + @cross_axis = axis + end # The position of the axis # must be one of [:l, :r, :t, :b] @@ -147,7 +150,7 @@ module Axlsx # @param [String] str # @return [String] def to_xml_string(str = '') - str << '<c:axId val="' << @ax_id.to_s << '"/>' + str << '<c:axId val="' << @id.to_s << '"/>' @scaling.to_xml_string str str << '<c:delete val="'<< @delete.to_s << '"/>' str << '<c:axPos val="' << @ax_pos.to_s << '"/>' @@ -175,7 +178,7 @@ module Axlsx end # some potential value in implementing this in full. Very detailed! str << '<c:txPr><a:bodyPr rot="' << @label_rotation.to_s << '"/><a:lstStyle/><a:p><a:pPr><a:defRPr/></a:pPr><a:endParaRPr/></a:p></c:txPr>' - str << '<c:crossAx val="' << @cross_ax.to_s << '"/>' + str << '<c:crossAx val="' << @cross_axis.id.to_s << '"/>' str << '<c:crosses val="' << @crosses.to_s << '"/>' end diff --git a/lib/axlsx/drawing/bar_3D_chart.rb b/lib/axlsx/drawing/bar_3D_chart.rb index 6f69f703..755f334c 100644 --- a/lib/axlsx/drawing/bar_3D_chart.rb +++ b/lib/axlsx/drawing/bar_3D_chart.rb @@ -10,12 +10,16 @@ module Axlsx # the category axis # @return [CatAxis] - attr_reader :cat_axis + def cat_axis + axes[:cat_axis] + end alias :catAxis :cat_axis # the value axis # @return [ValAxis] - attr_reader :val_axis + def val_axis + axes[:val_axis] + end alias :valAxis :val_axis # The direction of the bars in the chart @@ -75,10 +79,6 @@ module Axlsx def initialize(frame, options={}) @vary_colors = true @gap_width, @gap_depth, @shape = nil, nil, nil - @cat_ax_id = rand(8 ** 8) - @val_ax_id = rand(8 ** 8) - @cat_axis = CatAxis.new(@cat_ax_id, @val_ax_id) - @val_axis = ValAxis.new(@val_ax_id, @cat_ax_id, :tick_lbl_pos => :low, :ax_pos => :l) super(frame, options) @series_type = BarSeries @view_3D = View3D.new({:r_ang_ax=>1}.merge(options)) @@ -131,17 +131,21 @@ module Axlsx str_inner << '<c:grouping val="' << grouping.to_s << '"/>' str_inner << '<c:varyColors val="' << vary_colors.to_s << '"/>' @series.each { |ser| ser.to_xml_string(str_inner) } - @d_lbls.to_xml_string(str) if @d_lbls + @d_lbls.to_xml_string(str_inner) if @d_lbls str_inner << '<c:gapWidth val="' << @gap_width.to_s << '"/>' unless @gap_width.nil? str_inner << '<c:gapDepth val="' << @gap_depth.to_s << '"/>' unless @gap_depth.nil? str_inner << '<c:shape val="' << @shape.to_s << '"/>' unless @shape.nil? - str_inner << '<c:axId val="' << @cat_ax_id.to_s << '"/>' - str_inner << '<c:axId val="' << @val_ax_id.to_s << '"/>' - str_inner << '<c:axId val="0"/>' + axes.to_xml_string(str_inner, :ids => true) str_inner << '</c:bar3DChart>' - @cat_axis.to_xml_string str_inner - @val_axis.to_xml_string str_inner + axes.to_xml_string(str_inner) end end + + # A hash of axes used by this chart. Bar charts have a value and + # category axes specified via axes[:val_axes] and axes[:cat_axis] + # @return [Axes] + def axes + @axes ||= Axes.new(:cat_axis => CatAxis, :val_axis => ValAxis) + end end end diff --git a/lib/axlsx/drawing/cat_axis.rb b/lib/axlsx/drawing/cat_axis.rb index ba392ce6..f32c23c9 100644 --- a/lib/axlsx/drawing/cat_axis.rb +++ b/lib/axlsx/drawing/cat_axis.rb @@ -4,23 +4,15 @@ module Axlsx class CatAxis < Axis # Creates a new CatAxis object - # @param [Integer] ax_id the id of this axis. Inherited - # @param [Integer] cross_ax the id of the perpendicular axis. Inherited - # @option options [Symbol] ax_pos. Inherited - # @option options [Symbol] tick_lbl_pos. Inherited - # @option options [Symbol] crosses. Inherited - # @option options [Boolean] auto - # @option options [Symbol] lbl_algn - # @option options [Integer] lbl_offset # @option options [Integer] tick_lbl_skip # @option options [Integer] tick_mark_skip - def initialize(ax_id, cross_ax, options={}) + def initialize(options={}) @tick_lbl_skip = 1 @tick_mark_skip = 1 self.auto = 1 self.lbl_algn = :ctr self.lbl_offset = "100" - super(ax_id, cross_ax, options) + super(options) end # From the docs: This element specifies that this axis is a date or text axis based on the data that is used for the axis labels, not a specific choice. diff --git a/lib/axlsx/drawing/chart.rb b/lib/axlsx/drawing/chart.rb index 79019a0a..c1d408e6 100644 --- a/lib/axlsx/drawing/chart.rb +++ b/lib/axlsx/drawing/chart.rb @@ -20,6 +20,7 @@ module Axlsx @graphic_frame.anchor.drawing.worksheet.workbook.charts << self @series = SimpleTypedList.new Series @show_legend = true + @display_blanks_as = :gap @series_type = Series @title = Title.new parse_options options @@ -70,10 +71,19 @@ module Axlsx # @return [Boolean] attr_reader :show_legend - # returns a relationship object for the chart - # @return [Axlsx::Relationship] + # How to display blank values + # Options are + # * gap: Display nothing + # * span: Not sure what this does + # * zero: Display as if the value were zero, not blank + # @return [Symbol] + # Default :gap (although this really should vary by chart type and grouping) + attr_reader :display_blanks_as + + # The relationship object for this chart. + # @return [Relationship] def relationship - Relationship.new(CHART_R, "../#{pn}") + Relationship.new(self, CHART_R, "../#{pn}") end # The index of this chart in the workbooks charts collection @@ -105,6 +115,12 @@ module Axlsx # @return [Boolean] def show_legend=(v) Axlsx::validate_boolean(v); @show_legend = v; end + # How to display blank values + # @see display_blanks_as + # @param [Symbol] v + # @return [Symbol] + def display_blanks_as=(v) Axlsx::validate_display_blanks_as(v); @display_blanks_as = v; end + # The style for the chart. # see ECMA Part 1 §21.2.2.196 # @param [Integer] v must be between 1 and 48 @@ -157,7 +173,7 @@ module Axlsx str << '</c:legend>' end str << '<c:plotVisOnly val="1"/>' - str << '<c:dispBlanksAs val="zero"/>' + str << '<c:dispBlanksAs val="' << display_blanks_as.to_s << '"/>' str << '<c:showDLblsOverMax val="1"/>' str << '</c:chart>' str << '<c:printSettings>' diff --git a/lib/axlsx/drawing/d_lbls.rb b/lib/axlsx/drawing/d_lbls.rb index 3685e7d2..74c01350 100644 --- a/lib/axlsx/drawing/d_lbls.rb +++ b/lib/axlsx/drawing/d_lbls.rb @@ -10,7 +10,7 @@ module Axlsx include Axlsx::OptionsParser # creates a new DLbls object def initialize(chart_type, options={}) - raise ArgumentError, 'chart_type must inherit from Chart' unless chart_type.superclass == Chart + raise ArgumentError, 'chart_type must inherit from Chart' unless [Chart, LineChart].include?(chart_type.superclass) @chart_type = chart_type initialize_defaults parse_options options diff --git a/lib/axlsx/drawing/drawing.rb b/lib/axlsx/drawing/drawing.rb index 46d5a88b..1748e9f4 100644 --- a/lib/axlsx/drawing/drawing.rb +++ b/lib/axlsx/drawing/drawing.rb @@ -22,6 +22,7 @@ module Axlsx require 'axlsx/drawing/ser_axis.rb' require 'axlsx/drawing/cat_axis.rb' require 'axlsx/drawing/val_axis.rb' + require 'axlsx/drawing/axes.rb' require 'axlsx/drawing/marker.rb' @@ -33,6 +34,7 @@ module Axlsx require 'axlsx/drawing/chart.rb' require 'axlsx/drawing/pie_3D_chart.rb' require 'axlsx/drawing/bar_3D_chart.rb' + require 'axlsx/drawing/line_chart.rb' require 'axlsx/drawing/line_3D_chart.rb' require 'axlsx/drawing/scatter_chart.rb' @@ -119,12 +121,6 @@ module Axlsx @worksheet.workbook.drawings.index(self) end - # The relation reference id for this drawing - # @return [String] - def rId - "rId#{index+1}" - end - # The part name for this drawing # @return [String] def pn @@ -138,15 +134,7 @@ module Axlsx "#{DRAWING_RELS_PN % (index+1)}" end - # The index of a chart, image or hyperlink object this drawing contains - def index_of(object) - child_objects.index(object) - end - - - # An ordered list of objects this drawing holds - # It is important that the objects are returned in the same order each time for - # releationship indexing in the package + # A list of objects this drawing holds. # @return [Array] def child_objects charts + images + hyperlinks diff --git a/lib/axlsx/drawing/graphic_frame.rb b/lib/axlsx/drawing/graphic_frame.rb index 943caeae..9b921275 100644 --- a/lib/axlsx/drawing/graphic_frame.rb +++ b/lib/axlsx/drawing/graphic_frame.rb @@ -22,15 +22,10 @@ module Axlsx @chart = chart_type.new(self, options) end - # The relationship id for this graphic + # The relationship id for this graphic frame. # @return [String] - # - # NOTE: Discontinued. This should not be part of GraphicFrame. - # The drawing object maintains relationships and needs to be queried to determine the relationship id of any given graphic data child object. - # def rId - warn('axlsx::DEPRECIATED: GraphicFrame#rId has been depreciated. relationship id is determed by the drawing object') - "rId#{@anchor.index+1}" + @anchor.drawing.relationships.for(chart).Id end # Serializes the object @@ -49,7 +44,7 @@ module Axlsx str << '</xdr:xfrm>' str << '<a:graphic>' str << '<a:graphicData uri="' << XML_NS_C << '">' - str << '<c:chart xmlns:c="' << XML_NS_C << '" xmlns:r="' << XML_NS_R << '" r:id="rId' << (@anchor.drawing.index_of(@chart)+1).to_s << '"/>' + str << '<c:chart xmlns:c="' << XML_NS_C << '" xmlns:r="' << XML_NS_R << '" r:id="' << rId << '"/>' str << '</a:graphicData>' str << '</a:graphic>' str << '</xdr:graphicFrame>' diff --git a/lib/axlsx/drawing/hyperlink.rb b/lib/axlsx/drawing/hyperlink.rb index e886f766..850baeef 100644 --- a/lib/axlsx/drawing/hyperlink.rb +++ b/lib/axlsx/drawing/hyperlink.rb @@ -83,27 +83,20 @@ module Axlsx # @return [String] attr_accessor :tooltip - # Returns a relationship object for this hyperlink - # @return [Axlsx::Relationship] + # The relationship object for this hyperlink. + # @return [Relationship] def relationship - Relationship.new(HYPERLINK_R, href, :target_mode => :External) + Relationship.new(self, HYPERLINK_R, href, :target_mode => :External) end + # Serializes the object # @param [String] str # @return [String] def to_xml_string(str = '') str << '<a:hlinkClick ' - serialized_attributes str, {:'r:id' => "rId#{id}", :'xmlns:r' => XML_NS_R } + serialized_attributes str, {:'r:id' => relationship.Id, :'xmlns:r' => XML_NS_R } str << '/>' end - private - - # The relational ID for this hyperlink - # @return [Integer] - def id - @parent.anchor.drawing.index_of(self)+1 - end - end end diff --git a/lib/axlsx/drawing/line_3D_chart.rb b/lib/axlsx/drawing/line_3D_chart.rb index b843f48e..183f3250 100644 --- a/lib/axlsx/drawing/line_3D_chart.rb +++ b/lib/axlsx/drawing/line_3D_chart.rb @@ -11,7 +11,7 @@ module Axlsx # ws = p.workbook.add_worksheet # ws.add_row ["This is a chart with no data in the sheet"] # - # chart = ws.add_chart(Axlsx::Line3DChart, :start_at=> [0,1], :end_at=>[0,6], :title=>"Most Popular Pets") + # chart = ws.add_chart(Axlsx::Line3DChart, :start_at=> [0,1], :end_at=>[0,6], :t#itle=>"Most Popular Pets") # chart.add_series :data => [1, 9, 10], :labels => ["Slimy Reptiles", "Fuzzy Bunnies", "Rottweiler"] # # @see Worksheet#add_chart @@ -19,93 +19,50 @@ module Axlsx # @see Chart#add_series # @see Series # @see Package#serialize - class Line3DChart < Chart - - # the category axis - # @return [CatAxis] - attr_reader :catAxis - - # the category axis - # @return [ValAxis] - attr_reader :valAxis - - # the category axis - # @return [Axis] - attr_reader :serAxis + class Line3DChart < Axlsx::LineChart # space between bar or column clusters, as a percentage of the bar or column width. # @return [String] - attr_reader :gapDepth - - #grouping for a column, line, or area chart. - # must be one of [:percentStacked, :clustered, :standard, :stacked] - # @return [Symbol] - attr_reader :grouping + attr_reader :gap_depth + alias :gapDepth :gap_depth # validation regex for gap amount percent GAP_AMOUNT_PERCENT = /0*(([0-9])|([1-9][0-9])|([1-4][0-9][0-9])|500)%/ + # the category axis + # @return [Axis] + def ser_axis + axes[:ser_axis] + end + alias :serAxis :ser_axis + # Creates a new line chart object - # @param [GraphicFrame] frame The workbook that owns this chart. - # @option options [Cell, String] title - # @option options [Boolean] show_legend - # @option options [Symbol] grouping - # @option options [String] gapDepth - # @option options [Integer] rotX - # @option options [String] hPercent - # @option options [Integer] rotY - # @option options [String] depthPercent - # @option options [Boolean] rAngAx - # @option options [Integer] perspective + # @option options [String] gap_depth # @see Chart + # @see lineChart # @see View3D def initialize(frame, options={}) - @vary_colors = false - @gapDepth = nil - @grouping = :standard - @catAxId = rand(8 ** 8) - @valAxId = rand(8 ** 8) - @serAxId = rand(8 ** 8) - @catAxis = CatAxis.new(@catAxId, @valAxId) - @valAxis = ValAxis.new(@valAxId, @catAxId) - @serAxis = SerAxis.new(@serAxId, @valAxId) + @gap_depth = nil + @view_3D = View3D.new({:r_ang_ax=>1}.merge(options)) super(frame, options) - @series_type = LineSeries - @view_3D = View3D.new({:perspective=>30}.merge(options)) - @d_lbls = nil + axes.add_axis :ser_axis, SerAxis end - # @see grouping - def grouping=(v) - RestrictionValidator.validate "Bar3DChart.grouping", [:percentStacked, :standard, :stacked], v - @grouping = v - end # @see gapDepth - def gapDepth=(v) - RegexValidator.validate "Bar3DChart.gapWidth", GAP_AMOUNT_PERCENT, v - @gapDepth=(v) + def gap_depth=(v) + RegexValidator.validate "Line3DChart.gapWidth", GAP_AMOUNT_PERCENT, v + @gap_depth=(v) end + alias :gapDepth= :gap_depth= - # Serializes the object - # @param [String] str - # @return [String] - def to_xml_string(str = '') - super(str) do |str_inner| - str_inner << '<c:line3DChart>' - str_inner << '<c:grouping val="' << grouping.to_s << '"/>' - str_inner << '<c:varyColors val="' << vary_colors.to_s << '"/>' - @series.each { |ser| ser.to_xml_string(str_inner) } - @d_lbls.to_xml_string(str) if @d_lbls - str_inner << '<c:gapDepth val="' << @gapDepth.to_s << '"/>' unless @gapDepth.nil? - str_inner << '<c:axId val="' << @catAxId.to_s << '"/>' - str_inner << '<c:axId val="' << @valAxId.to_s << '"/>' - str_inner << '<c:axId val="' << @serAxId.to_s << '"/>' - str_inner << '</c:line3DChart>' - @catAxis.to_xml_string str_inner - @valAxis.to_xml_string str_inner - @serAxis.to_xml_string str_inner + # Serializes the object + # @param [String] str + # @return [String] + def to_xml_string(str = '') + super(str) do |str_inner| + str_inner << '<c:gapDepth val="' << @gap_depth.to_s << '"/>' unless @gap_depth.nil? + end end - end end end diff --git a/lib/axlsx/drawing/line_chart.rb b/lib/axlsx/drawing/line_chart.rb new file mode 100644 index 00000000..699050dc --- /dev/null +++ b/lib/axlsx/drawing/line_chart.rb @@ -0,0 +1,99 @@ +# encoding: UTF-8 +module Axlsx + + # The LineChart is a two dimentional line chart (who would have guessed?) that you can add to your worksheet. + # @example Creating a chart + # # This example creates a line in a single sheet. + # require "rubygems" # if that is your preferred way to manage gems! + # require "axlsx" + # + # p = Axlsx::Package.new + # ws = p.workbook.add_worksheet + # ws.add_row ["This is a chart with no data in the sheet"] + # + # chart = ws.add_chart(Axlsx::LineChart, :start_at=> [0,1], :end_at=>[0,6], :title=>"Most Popular Pets") + # chart.add_series :data => [1, 9, 10], :labels => ["Slimy Reptiles", "Fuzzy Bunnies", "Rottweiler"] + # + # @see Worksheet#add_chart + # @see Worksheet#add_row + # @see Chart#add_series + # @see Series + # @see Package#serialize + class LineChart < Chart + + # the category axis + # @return [CatAxis] + def cat_axis + axes[:cat_axis] + end + alias :catAxis :cat_axis + + # the category axis + # @return [ValAxis] + def val_axis + axes[:val_axis] + end + alias :valAxis :val_axis + + # must be one of [:percentStacked, :clustered, :standard, :stacked] + # @return [Symbol] + attr_reader :grouping + + # Creates a new line chart object + # @param [GraphicFrame] frame The workbook that owns this chart. + # @option options [Cell, String] title + # @option options [Boolean] show_legend + # @option options [Symbol] grouping + # @see Chart + def initialize(frame, options={}) + @vary_colors = false + @grouping = :standard + super(frame, options) + @series_type = LineSeries + @d_lbls = nil + end + + # @see grouping + def grouping=(v) + RestrictionValidator.validate "LineChart.grouping", [:percentStacked, :standard, :stacked], v + @grouping = v + end + + # The node name to use in serialization. As LineChart is used as the + # base class for Liine3DChart we need to be sure to serialize the + # chart based on the actual class type and not a fixed node name. + # @return [String] + def node_name + path = self.class.to_s + if i = path.rindex('::') + path = path[(i+2)..-1] + end + path[0] = path[0].chr.downcase + path + end + + # Serializes the object + # @param [String] str + # @return [String] + def to_xml_string(str = '') + super(str) do |str_inner| + str_inner << "<c:" << node_name << ">" + str_inner << '<c:grouping val="' << grouping.to_s << '"/>' + str_inner << '<c:varyColors val="' << vary_colors.to_s << '"/>' + @series.each { |ser| ser.to_xml_string(str_inner) } + @d_lbls.to_xml_string(str_inner) if @d_lbls + yield str_inner if block_given? + axes.to_xml_string(str_inner, :ids => true) + str_inner << "</c:" << node_name << ">" + axes.to_xml_string(str_inner) + end + end + + # The axes for this chart. LineCharts have a category and value + # axis. + # @return [Axes] + def axes + @axes ||= Axes.new(:cat_axis => CatAxis, :val_axis => ValAxis) + end + end +end diff --git a/lib/axlsx/drawing/line_series.rb b/lib/axlsx/drawing/line_series.rb index 017822ed..b011e0b6 100644 --- a/lib/axlsx/drawing/line_series.rb +++ b/lib/axlsx/drawing/line_series.rb @@ -19,11 +19,16 @@ module Axlsx # @return [String] attr_reader :color + # show markers on values + # @return [Boolean] + attr_reader :show_marker + # Creates a new series # @option options [Array, SimpleTypedList] data # @option options [Array, SimpleTypedList] labels # @param [Chart] chart def initialize(chart, options={}) + @show_marker = false @labels, @data = nil, nil super(chart, options) @labels = AxDataSource.new(:data => options[:labels]) unless options[:labels].nil? @@ -35,6 +40,12 @@ module Axlsx @color = v end + # @see show_marker + def show_marker=(v) + Axlsx::validate_boolean(v) + @show_marker = v + end + # Serializes the object # @param [String] str # @return [String] @@ -43,9 +54,16 @@ module Axlsx if color str << '<c:spPr><a:solidFill>' str << '<a:srgbClr val="' << color << '"/>' - str << '</a:solidFill></c:spPr>' + str << '</a:solidFill>' + str << '<a:ln w="28800">' + str << '<a:solidFill>' + str << '<a:srgbClr val="' << color << '"/>' + str << '</a:solidFill>' + str << '</a:ln>' + str << '<a:round/>' + str << '</c:spPr>' end - + str << '<c:marker><c:symbol val="none"/></c:marker>' unless @show_marker @labels.to_xml_string(str) unless @labels.nil? @data.to_xml_string(str) unless @data.nil? end diff --git a/lib/axlsx/drawing/pic.rb b/lib/axlsx/drawing/pic.rb index 8ed8d042..89f4c1ea 100644 --- a/lib/axlsx/drawing/pic.rb +++ b/lib/axlsx/drawing/pic.rb @@ -102,15 +102,10 @@ module Axlsx "#{IMAGE_PN % [(index+1), extname]}" end - # The relational id withing the drawing's relationships - def id - @anchor.drawing.charts.size + @anchor.drawing.images.index(self) + 1 - end - - # Returns a relationship object for this object - # @return Axlsx::Relationship + # The relationship object for this pic. + # @return [Relationship] def relationship - Relationship.new(IMAGE_R, "../#{pn}") + Relationship.new(self, IMAGE_R, "../#{pn}") end # providing access to the anchor's width attribute @@ -177,7 +172,7 @@ module Axlsx picture_locking.to_xml_string(str) str << '</xdr:cNvPicPr></xdr:nvPicPr>' str << '<xdr:blipFill>' - str << '<a:blip xmlns:r ="' << XML_NS_R << '" r:embed="rId' << id.to_s << '"/>' + str << '<a:blip xmlns:r ="' << XML_NS_R << '" r:embed="' << relationship.Id << '"/>' str << '<a:stretch><a:fillRect/></a:stretch></xdr:blipFill><xdr:spPr>' str << '<a:xfrm><a:off x="0" y="0"/><a:ext cx="2336800" cy="2161540"/></a:xfrm>' str << '<a:prstGeom prst="rect"><a:avLst/></a:prstGeom></xdr:spPr></xdr:pic>' diff --git a/lib/axlsx/drawing/scatter_chart.rb b/lib/axlsx/drawing/scatter_chart.rb index 2b801642..526bd6d5 100644 --- a/lib/axlsx/drawing/scatter_chart.rb +++ b/lib/axlsx/drawing/scatter_chart.rb @@ -12,35 +12,40 @@ module Axlsx # The Style for the scatter chart # must be one of :none | :line | :lineMarker | :marker | :smooth | :smoothMarker # return [Symbol] - attr_reader :scatterStyle + attr_reader :scatter_style + alias :scatterStyle :scatter_style # the x value axis # @return [ValAxis] - attr_reader :xValAxis + def x_val_axis + axes[:x_val_axis] + end + alias :xValAxis :x_val_axis # the y value axis # @return [ValAxis] - attr_reader :yValAxis + def y_val_axis + axes[:x_val_axis] + end + alias :yValAxis :y_val_axis # Creates a new scatter chart def initialize(frame, options={}) @vary_colors = 0 - @scatterStyle = :lineMarker - @xValAxId = rand(8 ** 8) - @yValAxId = rand(8 ** 8) - @xValAxis = ValAxis.new(@xValAxId, @yValAxId) - @yValAxis = ValAxis.new(@yValAxId, @xValAxId) - super(frame, options) + @scatter_style = :lineMarker + + super(frame, options) @series_type = ScatterSeries @d_lbls = nil parse_options options end # see #scatterStyle - def scatterStyle=(v) + def scatter_style=(v) Axlsx.validate_scatter_style(v) - @scatterStyle = v + @scatter_style = v end + alias :scatterStyle= :scatter_style= # Serializes the object # @param [String] str @@ -48,17 +53,22 @@ module Axlsx def to_xml_string(str = '') super(str) do |str_inner| str_inner << '<c:scatterChart>' - str_inner << '<c:scatterStyle val="' << scatterStyle.to_s << '"/>' + str_inner << '<c:scatterStyle val="' << scatter_style.to_s << '"/>' str_inner << '<c:varyColors val="' << vary_colors.to_s << '"/>' @series.each { |ser| ser.to_xml_string(str_inner) } - d_lbls.to_xml_string(str) if @d_lbls - str_inner << '<c:axId val="' << @xValAxId.to_s << '"/>' - str_inner << '<c:axId val="' << @yValAxId.to_s << '"/>' + d_lbls.to_xml_string(str_inner) if @d_lbls + axes.to_xml_string(str_inner, :ids => true) str_inner << '</c:scatterChart>' - @xValAxis.to_xml_string str_inner - @yValAxis.to_xml_string str_inner + axes.to_xml_string(str_inner) end str end + + # The axes for the scatter chart. ScatterChart has an x_val_axis and + # a y_val_axis + # @return [Axes] + def axes + @axes ||= Axes.new(:x_val_axis => ValAxis, :y_val_axis => ValAxis) + end end end diff --git a/lib/axlsx/drawing/ser_axis.rb b/lib/axlsx/drawing/ser_axis.rb index 00b04989..54e2c60e 100644 --- a/lib/axlsx/drawing/ser_axis.rb +++ b/lib/axlsx/drawing/ser_axis.rb @@ -5,30 +5,29 @@ module Axlsx # The number of tick lables to skip between labels # @return [Integer] - attr_reader :tickLblSkip + attr_reader :tick_lbl_skip + alias :tickLblSkip :tick_lbl_skip # The number of tickmarks to be skipped before the next one is rendered. # @return [Boolean] - attr_reader :tickMarkSkip + attr_reader :tick_mark_skip + alias :tickMarkSkip :tick_mark_skip # Creates a new SerAxis object - # @param [Integer] axId the id of this axis. Inherited - # @param [Integer] crossAx the id of the perpendicular axis. Inherited - # @option options [Symbol] axPos. Inherited - # @option options [Symbol] tickLblPos. Inherited - # @option options [Symbol] crosses. Inherited - # @option options [Integer] tickLblSkip - # @option options [Integer] tickMarkSkip - def initialize(axId, crossAx, options={}) - @tickLblSkip, @tickMarkSkip = 1, 1 - super(axId, crossAx, options) + # @option options [Integer] tick_lbl_skip + # @option options [Integer] tick_mark_skip + def initialize(options={}) + @tick_lbl_skip, @tick_mark_skip = 1, 1 + super(options) end # @see tickLblSkip - def tickLblSkip=(v) Axlsx::validate_unsigned_int(v); @tickLblSkip = v; end + def tick_lbl_skip=(v) Axlsx::validate_unsigned_int(v); @tick_lbl_skip = v; end + alias :tickLblSkip= :tick_lbl_skip= # @see tickMarkSkip - def tickMarkSkip=(v) Axlsx::validate_unsigned_int(v); @tickMarkSkip = v; end + def tick_mark_skip=(v) Axlsx::validate_unsigned_int(v); @tick_mark_skip = v; end + alias :tickMarkSkip= :tick_mark_skip= # Serializes the object # @param [String] str @@ -36,8 +35,8 @@ module Axlsx def to_xml_string(str = '') str << '<c:serAx>' super(str) - str << '<c:tickLblSkip val="' << @tickLblSkip.to_s << '"/>' unless @tickLblSkip.nil? - str << '<c:tickMarkSkip val="' << @tickMarkSkip.to_s << '"/>' unless @tickMarkSkip.nil? + str << '<c:tickLblSkip val="' << @tick_lbl_skip.to_s << '"/>' unless @tick_lbl_skip.nil? + str << '<c:tickMarkSkip val="' << @tick_mark_skip.to_s << '"/>' unless @tick_mark_skip.nil? str << '</c:serAx>' end end diff --git a/lib/axlsx/drawing/val_axis.rb b/lib/axlsx/drawing/val_axis.rb index 6e55c8ea..0e7a0800 100644 --- a/lib/axlsx/drawing/val_axis.rb +++ b/lib/axlsx/drawing/val_axis.rb @@ -6,21 +6,22 @@ module Axlsx # This element specifies how the value axis crosses the category axis. # must be one of [:between, :midCat] # @return [Symbol] - attr_reader :crossBetween + attr_reader :cross_between + alias :crossBetween :cross_between # Creates a new ValAxis object - # @param [Integer] axId the id of this axis - # @param [Integer] crossAx the id of the perpendicular axis - # @option options [Symbol] axPos - # @option options [Symbol] tickLblPos - # @option options [Symbol] crosses - # @option options [Symbol] crossesBetween - def initialize(axId, crossAx, options={}) - self.crossBetween = :between - super(axId, crossAx, options) + # @option options [Symbol] crosses_between + def initialize(options={}) + self.cross_between = :between + super(options) end - # @see crossBetween - def crossBetween=(v) RestrictionValidator.validate "ValAxis.crossBetween", [:between, :midCat], v; @crossBetween = v; end + + # @see cross_between + def cross_between=(v) + RestrictionValidator.validate "ValAxis.cross_between", [:between, :midCat], v + @cross_between = v + end + alias :crossBetween= :cross_between= # Serializes the object # @param [String] str @@ -28,7 +29,7 @@ module Axlsx def to_xml_string(str = '') str << '<c:valAx>' super(str) - str << '<c:crossBetween val="' << @crossBetween.to_s << '"/>' + str << '<c:crossBetween val="' << @cross_between.to_s << '"/>' str << '</c:valAx>' end diff --git a/lib/axlsx/drawing/vml_shape.rb b/lib/axlsx/drawing/vml_shape.rb index 701456f3..21cac911 100644 --- a/lib/axlsx/drawing/vml_shape.rb +++ b/lib/axlsx/drawing/vml_shape.rb @@ -4,95 +4,35 @@ module Axlsx class VmlShape include Axlsx::OptionsParser + include Axlsx::Accessors # Creates a new VmlShape - # @option options [Integer|String] left_column - # @option options [Integer|String] left_offset - # @option options [Integer|String] top_row - # @option options [Integer|String] top_offset - # @option options [Integer|String] right_column - # @option options [Integer|String] right_offset - # @option options [Integer|String] bottom_row - # @option options [Integer|String] bottom_offset + # @option options [Integer] row + # @option options [Integer] column + # @option options [Integer] left_column + # @option options [Integer] left_offset + # @option options [Integer] top_row + # @option options [Integer] top_offset + # @option options [Integer] right_column + # @option options [Integer] right_offset + # @option options [Integer] bottom_row + # @option options [Integer] bottom_offset def initialize(options={}) @row = @column = @left_column = @top_row = @right_column = @bottom_row = 0 @left_offset = 15 @top_offset = 2 @right_offset = 50 @bottom_offset = 5 + @visible = true @id = (0...8).map{65.+(rand(25)).chr}.join parse_options options yield self if block_given? end - # The row anchor position for this shape determined by the comment's ref value - # @return [Integer] - attr_reader :row + unsigned_int_attr_accessor :row, :column, :left_column, :left_offset, :top_row, :top_offset, + :right_column, :right_offset, :bottom_row, :bottom_offset - # The column anchor position for this shape determined by the comment's ref value - # @return [Integer] - attr_reader :column - - # The left column for this shape - # @return [Integer] - attr_reader :left_column - - # The left offset for this shape - # @return [Integer] - attr_reader :left_offset - - # The top row for this shape - # @return [Integer] - attr_reader :top_row - - # The top offset for this shape - # @return [Integer] - attr_reader :top_offset - - # The right column for this shape - # @return [Integer] - attr_reader :right_column - - # The right offset for this shape - # @return [Integer] - attr_reader :right_offset - - # The botttom row for this shape - # @return [Integer] - attr_reader :bottom_row - - # The bottom offset for this shape - # @return [Integer] - attr_reader :bottom_offset - - # @see column - def column=(v); Axlsx::validate_integerish(v); @column = v.to_i end - - # @see row - def row=(v); Axlsx::validate_integerish(v); @row = v.to_i end - # @see left_column - def left_column=(v); Axlsx::validate_integerish(v); @left_column = v.to_i end - - # @see left_offset - def left_offset=(v); Axlsx::validate_integerish(v); @left_offset = v.to_i end - - # @see top_row - def top_row=(v); Axlsx::validate_integerish(v); @top_row = v.to_i end - - # @see top_offset - def top_offset=(v); Axlsx::validate_integerish(v); @top_offset = v.to_i end - - # @see right_column - def right_column=(v); Axlsx::validate_integerish(v); @right_column = v.to_i end - - # @see right_offset - def right_offset=(v); Axlsx::validate_integerish(v); @right_offset = v.to_i end - - # @see bottom_row - def bottom_row=(v); Axlsx::validate_integerish(v); @bottom_row = v.to_i end - - # @see bottom_offset - def bottom_offset=(v); Axlsx::validate_integerish(v); @bottom_offset = v.to_i end + boolean_attr_accessor :visible # serialize the shape to a string # @param [String] str @@ -100,7 +40,8 @@ module Axlsx def to_xml_string(str ='') str << <<SHAME_ON_YOU -<v:shape id="#{@id}" type="#_x0000_t202" fillcolor="#ffffa1 [80]" o:insetmode="auto"> +<v:shape id="#{@id}" type="#_x0000_t202" fillcolor="#ffffa1 [80]" o:insetmode="auto" + style="visibility:#{@visible ? 'visible' : 'hidden'}"> <v:fill color2="#ffffa1 [80]"/> <v:shadow on="t" obscured="t"/> <v:path o:connecttype="none"/> @@ -115,7 +56,7 @@ str << <<SHAME_ON_YOU <x:AutoFill>False</x:AutoFill> <x:Row>#{row}</x:Row> <x:Column>#{column}</x:Column> - <x:Visible/> + #{@visible ? '<x:Visible/>' : ''} </x:ClientData> </v:shape> SHAME_ON_YOU diff --git a/lib/axlsx/package.rb b/lib/axlsx/package.rb index df87ed12..0a1bceaa 100644 --- a/lib/axlsx/package.rb +++ b/lib/axlsx/package.rb @@ -17,12 +17,14 @@ module Axlsx # # @param [Hash] options A hash that you can use to specify the author and workbook for this package. # @option options [String] :author The author of the document + # @option options [Time] :created_at Timestamp in the document properties (defaults to current time). # @option options [Boolean] :use_shared_strings This is passed to the workbook to specify that shared strings should be used when serializing the package. # @example Package.new :author => 'you!', :workbook => Workbook.new def initialize(options={}) @workbook = nil @core, @app = Core.new, App.new @core.creator = options[:author] || @core.creator + @core.created = options[:created_at] parse_options options yield self if block_given? end @@ -35,12 +37,6 @@ module Axlsx end - # Shortcut to specify that the workbook should use shared strings - # @see Workbook#use_shared_strings - def use_shared_strings=(v) - Axlsx::validate_boolean(v); - workbook.use_shared_strings = v - end # Shortcut to determine if the workbook is configured to use shared strings # @see Workbook#use_shared_strings @@ -48,6 +44,12 @@ module Axlsx workbook.use_shared_strings end + # Shortcut to specify that the workbook should use shared strings + # @see Workbook#use_shared_strings + def use_shared_strings=(v) + Axlsx::validate_boolean(v); + workbook.use_shared_strings = v + end # The workbook this package will serialize or validate. # @return [Workbook] If no workbook instance has been assigned with this package a new Workbook instance is returned. # @raise ArgumentError if workbook parameter is not a Workbook instance. @@ -98,6 +100,7 @@ module Axlsx # File.open('example_streamed.xlsx', 'w') { |f| f.write(s.read) } def serialize(output, confirm_valid=false) return false unless !confirm_valid || self.validate.empty? + Relationship.clear_cached_instances Zip::ZipOutputStream.open(output) do |zip| write_parts(zip) end @@ -110,6 +113,7 @@ module Axlsx # @return [StringIO|Boolean] False if confirm_valid and validation errors exist. rewound string IO if not. def to_stream(confirm_valid=false) return false unless !confirm_valid || self.validate.empty? + Relationship.clear_cached_instances zip = write_parts(Zip::ZipOutputStream.new("streamed", true)) stream = zip.close_buffer stream.rewind @@ -156,12 +160,12 @@ module Axlsx p = parts p.each do |part| unless part[:doc].nil? - zip.put_next_entry(part[:entry]) + zip.put_next_entry(zip_entry_for_part(part)) entry = ['1.9.2', '1.9.3'].include?(RUBY_VERSION) ? part[:doc].force_encoding('BINARY') : part[:doc] zip.puts(entry) end unless part[:path].nil? - zip.put_next_entry(part[:entry]); + zip.put_next_entry(zip_entry_for_part(part)) # binread for 1.9.3 zip.write IO.respond_to?(:binread) ? IO.binread(part[:path]) : IO.read(part[:path]) end @@ -169,6 +173,22 @@ module Axlsx zip end + # Generate a ZipEntry for the given package part. + # The important part here is to explicitly set the timestamp for the zip entry: Serializing axlsx packages + # with identical contents should result in identical zip files – however, the timestamp of a zip entry + # defaults to the time of serialization and therefore the zip file contents would be different every time + # the package is serialized. + # + # Note: {Core#created} also defaults to the current time – so to generate identical axlsx packages you have + # to set this explicitly, too (eg. with `Package.new(created_at: Time.local(2013, 1, 1))`). + # + # @param part A hash describing a part of this pacakge (see {#parts}) + # @return [Zip::ZipEntry] + def zip_entry_for_part(part) + timestamp = Zip::DOSTime.at(@core.created.to_i) + Zip::ZipEntry.new("", part[:entry], "", "", 0, 0, Zip::ZipEntry::DEFLATED, 0, timestamp) + end + # The parts of a package # @return [Array] An array of hashes that define the entry, document and schema for each part of the package. # @private @@ -321,9 +341,9 @@ module Axlsx # @private def relationships rels = Axlsx::Relationships.new - rels << Relationship.new(WORKBOOK_R, WORKBOOK_PN) - rels << Relationship.new(CORE_R, CORE_PN) - rels << Relationship.new(APP_R, APP_PN) + rels << Relationship.new(self, WORKBOOK_R, WORKBOOK_PN) + rels << Relationship.new(self, CORE_R, CORE_PN) + rels << Relationship.new(self, APP_R, APP_PN) rels.lock rels end diff --git a/lib/axlsx/rels/relationship.rb b/lib/axlsx/rels/relationship.rb index 385059f1..a6b7bdd2 100644 --- a/lib/axlsx/rels/relationship.rb +++ b/lib/axlsx/rels/relationship.rb @@ -3,7 +3,43 @@ module Axlsx # A relationship defines a reference between package parts. # @note Packages automatically manage relationships. class Relationship + + class << self + # Keeps track of all instances of this class. + # @return [Array] + def instances + @instances ||= [] + end + + # Clear cached instances. + # + # This should be called before serializing a package (see {Package#serialize} and + # {Package#to_stream}) to make sure that serialization is idempotent (i.e. + # Relationship instances are generated with the same IDs everytime the package + # is serialized). + # + # Also, calling this avoids memory leaks (cached instances lingering around + # forever). + def clear_cached_instances + @instances = [] + end + + # Generate and return a unique id (eg. `rId123`) Used for setting {#Id}. + # + # The generated id depends on the number of cached instances, so using + # {clear_cached_instances} will automatically reset the generated ids, too. + # @return [String] + def next_free_id + "rId#{@instances.size + 1}" + end + end + # The id of the relationship (eg. "rId123"). Most instances get their own unique id. + # However, some instances need to share the same id – see {#should_use_same_id_as?} + # for details. + # @return [String] + attr_reader :Id + # The location of the relationship target # @return [String] attr_reader :Target @@ -30,14 +66,26 @@ module Axlsx # Target mode must be :external for now. attr_reader :TargetMode - # creates a new relationship + # The source object the relations belongs to (e.g. a hyperlink, drawing, ...). Needed when + # looking up the relationship for a specific object (see {Relationships#for}). + attr_reader :source_obj + + # Initializes a new relationship. + # @param [Object] source_obj see {#source_obj} # @param [String] type The type of the relationship # @param [String] target The target for the relationship # @option [Symbol] :target_mode only accepts :external. - def initialize(type, target, options={}) + def initialize(source_obj, type, target, options={}) + @source_obj = source_obj self.Target=target self.Type=type - self.TargetMode = options.delete(:target_mode) if options[:target_mode] + self.TargetMode = options[:target_mode] if options[:target_mode] + @Id = if (existing = self.class.instances.find{ |i| should_use_same_id_as?(i) }) + existing.Id + else + self.class.next_free_id + end + self.class.instances << self end # @see Target @@ -50,15 +98,32 @@ module Axlsx # serialize relationship # @param [String] str - # @param [Integer] rId the id for this relationship # @return [String] - def to_xml_string(rId, str = '') - h = self.instance_values - h[:Id] = 'rId' << rId.to_s + def to_xml_string(str = '') + h = self.instance_values.reject{|k, _| k == "source_obj"} str << '<Relationship ' str << h.map { |key, value| '' << key.to_s << '="' << Axlsx::coder.encode(value.to_s) << '"'}.join(' ') str << '/>' end - + + # Whether this relationship should use the same id as `other`. + # + # Instances designating the same relationship need to use the same id. We can not simply + # compare the {#Target} attribute, though: `foo/bar.xml`, `../foo/bar.xml`, + # `../../foo/bar.xml` etc. are all different but probably mean the same file (this + # is especially an issue for relationships in the context of pivot tables). So lets + # just ignore this attribute for now (except when {#TargetMode} is set to `:External` – + # then {#Target} will be an absolute URL and thus can safely be compared). + # + # @todo Implement comparison of {#Target} based on normalized path names. + # @param other [Relationship] + def should_use_same_id_as?(other) + result = self.source_obj == other.source_obj && self.Type == other.Type && self.TargetMode == other.TargetMode + if self.TargetMode == :External + result &&= self.Target == other.Target + end + result + end + end end diff --git a/lib/axlsx/rels/relationships.rb b/lib/axlsx/rels/relationships.rb index 521d7689..a9c73f7d 100644 --- a/lib/axlsx/rels/relationships.rb +++ b/lib/axlsx/rels/relationships.rb @@ -11,10 +11,17 @@ require 'axlsx/rels/relationship.rb' super Relationship end + # The relationship instance for the given source object, or nil if none exists. + # @see Relationship#source_obj + # @return [Relationship] + def for(source_obj) + @list.find{ |rel| rel.source_obj == source_obj } + end + def to_xml_string(str = '') str << '<?xml version="1.0" encoding="UTF-8"?>' str << '<Relationships xmlns="' << RELS_R << '">' - each_with_index { |rel, index| rel.to_xml_string(index+1, str) } + each{ |rel| rel.to_xml_string(str) } str << '</Relationships>' end diff --git a/lib/axlsx/stylesheet/styles.rb b/lib/axlsx/stylesheet/styles.rb index 8cd2275c..44ccb752 100644 --- a/lib/axlsx/stylesheet/styles.rb +++ b/lib/axlsx/stylesheet/styles.rb @@ -296,9 +296,11 @@ module Axlsx def parse_fill_options(options={}) return unless options[:bg_color] color = Color.new(:rgb=>options[:bg_color]) - pattern = PatternFill.new(:patternType =>:solid, :fgColor=>color) + dxf = options[:type] == :dxf + color_key = dxf ? :bgColor : :fgColor + pattern = PatternFill.new(:patternType =>:solid, color_key=>color) fill = Fill.new(pattern) - options[:type] == :dxf ? fill : fills << fill + dxf ? fill : fills << fill end # parses Style#add_style options for borders. diff --git a/lib/axlsx/util/serialized_attributes.rb b/lib/axlsx/util/serialized_attributes.rb index 5519f843..e421984b 100644 --- a/lib/axlsx/util/serialized_attributes.rb +++ b/lib/axlsx/util/serialized_attributes.rb @@ -59,7 +59,6 @@ module Axlsx # break the xml and 1.8.7 does not support ordered hashes. # @param [String] str The string instance to which serialized data is appended # @param [Array] additional_attributes An array of additional attribute names. - # @param [Proc] block A which will be called with the value for each element. # @return [String] The serialized output. def serialized_element_attributes(str='', additional_attributes=[], &block) attrs = self.class.xml_element_attributes + additional_attributes diff --git a/lib/axlsx/util/simple_typed_list.rb b/lib/axlsx/util/simple_typed_list.rb index 4d188ffd..9a5f2e3d 100644 --- a/lib/axlsx/util/simple_typed_list.rb +++ b/lib/axlsx/util/simple_typed_list.rb @@ -1,21 +1,9 @@ # encoding: UTF-8 module Axlsx + # A SimpleTypedList is a type restrictive collection that allows some of the methods from Array and supports basic xml serialization. # @private class SimpleTypedList - # The class constants of allowed types - # @return [Array] - attr_reader :allowed_types - - # The index below which items cannot be removed - # @return [Integer] - attr_reader :locked_at - - # The tag name to use when serializing this object - # by default the parent node for all items in the list is the classname of the first allowed type with the first letter in lowercase. - # @return [String] - attr_reader :serialize_as - # Creats a new typed list # @param [Array, Class] type An array of Class objects or a single Class object # @param [String] serialize_as The tag name to use in serialization @@ -33,6 +21,38 @@ module Axlsx @serialize_as = serialize_as end + # The class constants of allowed types + # @return [Array] + attr_reader :allowed_types + + # The index below which items cannot be removed + # @return [Integer] + attr_reader :locked_at + + # The tag name to use when serializing this object + # by default the parent node for all items in the list is the classname of the first allowed type with the first letter in lowercase. + # @return [String] + attr_reader :serialize_as + + # Transposes the list (without blowing up like ruby does) + # any non populated cell in the matrix will be a nil value + def transpose + return @list.clone if @list.size == 0 + row_count = @list.size + max_column_count = @list.map{|row| row.cells.size}.max + result = Array.new(max_column_count) { Array.new(row_count) } + 0..row_count.times do |row_index| + 0..max_column_count.times do |column_index| + datum = if @list[row_index].cells.size >= max_column_count + @list[row_index].cells[column_index] + elsif block_given? + yield(column_index, row_index) + end + result[column_index][row_index] = datum + end + end + result + end # Lock this list at the current size # @return [self] def lock diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb index 739a4de2..a161f0d9 100644 --- a/lib/axlsx/util/validators.rb +++ b/lib/axlsx/util/validators.rb @@ -290,4 +290,11 @@ module Axlsx def self.validate_split_state_type(v) RestrictionValidator.validate :split_state_type, [:frozen, :frozen_split, :split], v end + + # Requires that the value is a valid "display blanks as" type. + # valid types must be one of gap, span, zero + # @param [Any] v The value validated + def self.validate_display_blanks_as(v) + RestrictionValidator.validate :display_blanks_as, [:gap, :span, :zero], v + end end diff --git a/lib/axlsx/version.rb b/lib/axlsx/version.rb index 6b0df7df..6c9907ab 100644 --- a/lib/axlsx/version.rb +++ b/lib/axlsx/version.rb @@ -1,5 +1,5 @@ module Axlsx # The current version - VERSION = "1.3.5" + VERSION = "1.3.7" end diff --git a/lib/axlsx/workbook/shared_strings_table.rb b/lib/axlsx/workbook/shared_strings_table.rb index 43e8a1a8..7c08205e 100644 --- a/lib/axlsx/workbook/shared_strings_table.rb +++ b/lib/axlsx/workbook/shared_strings_table.rb @@ -26,10 +26,16 @@ module Axlsx # @see Cell#sharable attr_reader :unique_cells + # The xml:space attribute + # @see Workbook#xml_space + attr_reader :xml_space + # Creates a new Shared Strings Table agains an array of cells # @param [Array] cells This is an array of all of the cells in the workbook - def initialize(cells) + # @param [Symbol] xml_space The xml:space behavior for the shared string table. + def initialize(cells, xml_space=:preserve) @index = 0 + @xml_space = xml_space @unique_cells = {} @shared_xml_string = "" shareable_cells = cells.flatten.select{ |cell| cell.plain_string? } @@ -40,8 +46,10 @@ module Axlsx # Serializes the object # @param [String] str # @return [String] - def to_xml_string - '<?xml version="1.0" encoding="UTF-8"?><sst xmlns="' << XML_NS << '" count="' << @count.to_s << '" uniqueCount="' << unique_count.to_s << '">' << @shared_xml_string << '</sst>' + def to_xml_string(str='') + str << '<?xml version="1.0" encoding="UTF-8"?><sst xmlns="' << XML_NS << '"' + str << ' count="' << @count.to_s << '" uniqueCount="' << unique_count.to_s << '"' + str << ' xml:space="' << xml_space.to_s << '">' << @shared_xml_string << '</sst>' end private diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index e329ae74..d122f253 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -270,14 +270,14 @@ require 'axlsx/workbook/worksheet/selection.rb' def relationships r = Relationships.new @worksheets.each do |sheet| - r << Relationship.new(WORKSHEET_R, WORKSHEET_PN % (r.size+1)) + r << Relationship.new(sheet, WORKSHEET_R, WORKSHEET_PN % (r.size+1)) end pivot_tables.each_with_index do |pivot_table, index| - r << Relationship.new(PIVOT_TABLE_CACHE_DEFINITION_R, PIVOT_TABLE_CACHE_DEFINITION_PN % (index+1)) + r << Relationship.new(pivot_table.cache_definition, PIVOT_TABLE_CACHE_DEFINITION_R, PIVOT_TABLE_CACHE_DEFINITION_PN % (index+1)) end - r << Relationship.new(STYLES_R, STYLES_PN) + r << Relationship.new(self, STYLES_R, STYLES_PN) if use_shared_strings - r << Relationship.new(SHARED_STRINGS_R, SHARED_STRINGS_PN) + r << Relationship.new(self, SHARED_STRINGS_R, SHARED_STRINGS_PN) end r end @@ -285,7 +285,25 @@ require 'axlsx/workbook/worksheet/selection.rb' # generates a shared string object against all cells in all worksheets. # @return [SharedStringTable] def shared_strings - SharedStringsTable.new(worksheets.collect { |ws| ws.cells }) + SharedStringsTable.new(worksheets.collect { |ws| ws.cells }, xml_space) + end + + # The xml:space attribute for the worksheet. + # This determines how whitespace is handled withing the document. + # The most relevant part being whitespace in the cell text. + # allowed values are :preserve and :default. Axlsx uses :preserve unless + # you explicily set this to :default. + # @return Symbol + def xml_space + @xml_space ||= :preserve + end + + # Sets the xml:space attribute for the worksheet + # @see Worksheet#xml_space + # @param [Symbol] space must be one of :preserve or :default + def xml_space=(space) + Axlsx::RestrictionValidator.validate(:xml_space, [:preserve, :default], space) + @xml_space = space; end # returns a range of cells in a worksheet @@ -318,9 +336,8 @@ require 'axlsx/workbook/worksheet/selection.rb' defined_names.to_xml_string(str) unless pivot_tables.empty? str << '<pivotCaches>' - pivot_tables.each_with_index do |pivot_table, index| - rId = "rId#{@worksheets.size + index + 1 }" - str << '<pivotCache cacheId="' << pivot_table.cache_definition.cache_id.to_s << '" r:id="' << rId << '"/>' + pivot_tables.each do |pivot_table| + str << '<pivotCache cacheId="' << pivot_table.cache_definition.cache_id.to_s << '" r:id="' << pivot_table.cache_definition.rId << '"/>' end str << '</pivotCaches>' end diff --git a/lib/axlsx/workbook/worksheet/comment.rb b/lib/axlsx/workbook/worksheet/comment.rb index 3e54d2b3..7035f4cf 100644 --- a/lib/axlsx/workbook/worksheet/comment.rb +++ b/lib/axlsx/workbook/worksheet/comment.rb @@ -4,35 +4,33 @@ module Axlsx class Comment include Axlsx::OptionsParser + include Axlsx::Accessors # Creates a new comment object - # @param [Comments] comments + # @param [Comments] comments The comment collection this comment belongs to # @param [Hash] options # @option [String] author the author of the comment # @option [String] text The text for the comment + # @option [String] ref The refence (e.g. 'A3' where this comment will be anchored. + # @option [Boolean] visible This controls the visiblity of the associated vml_shape. def initialize(comments, options={}) raise ArgumentError, "A comment needs a parent comments object" unless comments.is_a?(Comments) + @visible = true @comments = comments parse_options options yield self if block_given? end - # The text to render - # @return [String] - attr_reader :text - - # The author of this comment - # @see Comments - # @return [String] - attr_reader :author + string_attr_accessor :text, :author + boolean_attr_accessor :visible - # The owning Comments object + # The owning Comments object # @return [Comments] attr_reader :comments # The string based cell position reference (e.g. 'A1') that determines the positioning of this comment - # @return [String] + # @return [String|Cell] attr_reader :ref # TODO @@ -60,30 +58,20 @@ module Axlsx @ref = v.r if v.is_a?(Cell) end - # @see text - def text=(v) - Axlsx::validate_string(v) - @text = v - end - - # @see author - def author=(v) - @author = v - end - # serialize the object # @param [String] str # @return [String] def to_xml_string(str = "") author = @comments.authors[author_index] str << '<comment ref="' << ref << '" authorId="' << author_index.to_s << '">' - str << '<text><r>' - str << '<rPr> <b/><color indexed="81"/></rPr>' - str << '<t>' << author.to_s << ': -</t></r>' + str << '<text>' + unless author.to_s == "" + str << '<r><rPr><b/><color indexed="81"/></rPr>' + str << "<t>" << ::CGI.escapeHTML(author.to_s) << ":\n</t></r>" + end str << '<r>' str << '<rPr><color indexed="81"/></rPr>' - str << '<t>' << text << '</t></r></text>' + str << '<t>' << ::CGI.escapeHTML(text) << '</t></r></text>' str << '</comment>' end @@ -93,7 +81,7 @@ module Axlsx # by default, all columns are 5 columns wide and 5 rows high def initialize_vml_shape pos = Axlsx::name_to_indices(ref) - @vml_shape = VmlShape.new(:row => pos[1], :column => pos[0]) do |vml| + @vml_shape = VmlShape.new(:row => pos[1], :column => pos[0], :visible => @visible) do |vml| vml.left_column = vml.column vml.right_column = vml.column + 2 vml.top_row = vml.row diff --git a/lib/axlsx/workbook/worksheet/comments.rb b/lib/axlsx/workbook/worksheet/comments.rb index 25da9de2..03cce339 100644 --- a/lib/axlsx/workbook/worksheet/comments.rb +++ b/lib/axlsx/workbook/worksheet/comments.rb @@ -56,9 +56,9 @@ module Axlsx # 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")] + [Relationship.new(self, VML_DRAWING_R, "../#{vml_drawing.pn}"), + Relationship.new(self, COMMENT_R, "../#{pn}"), + Relationship.new(self, COMMENT_R_NULL, "NULL")] end # serialize the object diff --git a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb index a0ce6a41..916b31c2 100644 --- a/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb +++ b/lib/axlsx/workbook/worksheet/conditional_formatting_rule.rb @@ -25,7 +25,7 @@ module Axlsx # @option options [Integer] stdDev The number of standard deviations above or below the average to match # @option options [Boolean] stopIfTrue Stop evaluating rules after this rule matches # @option options [Symbol] timePeriod The time period in a date occuring... rule - # @option options [String] formula The formula to match against in i.e. an equal rule + # @option options [String] formula The formula to match against in i.e. an equal rule. Use a [minimum, maximum] array for cellIs between/notBetween conditionals. def initialize(options={}) @color_scale = @data_bar = @icon_set = @formula = nil parse_options options @@ -36,6 +36,8 @@ module Axlsx :stopIfTrue, :timePeriod # Formula + # The formula or value to match against (e.g. 5 with an operator of :greaterThan to specify cell_value > 5). + # If the operator is :between or :notBetween, use an array to specify [minimum, maximum] # @return [String] attr_reader :formula @@ -180,7 +182,7 @@ module Axlsx # @see timePeriod def timePeriod=(v); Axlsx::validate_time_period_type(v); @timePeriod = v end # @see formula - def formula=(v); Axlsx::validate_string(v); @formula = v end + def formula=(v); [*v].each {|x| Axlsx::validate_string(x) }; @formula = [*v].map { |form| ::CGI.escapeHTML(form) } end # @see color_scale def color_scale=(v) @@ -208,7 +210,7 @@ module Axlsx str << '<cfRule ' serialized_attributes str str << '>' - str << '<formula>' << self.formula << '</formula>' if @formula + str << '<formula>' << [*self.formula].join('</formula><formula>') << '</formula>' if @formula @color_scale.to_xml_string(str) if @color_scale && @type == :colorScale @data_bar.to_xml_string(str) if @data_bar && @type == :dataBar @icon_set.to_xml_string(str) if @icon_set && @type == :iconSet diff --git a/lib/axlsx/workbook/worksheet/pivot_table.rb b/lib/axlsx/workbook/worksheet/pivot_table.rb index 94edf80e..0de22f8f 100644 --- a/lib/axlsx/workbook/worksheet/pivot_table.rb +++ b/lib/axlsx/workbook/worksheet/pivot_table.rb @@ -95,10 +95,17 @@ module Axlsx # (see #data) def data=(v) DataTypeValidator.validate "#{self.class}.data", [Array], v - v.each do |ref| - DataTypeValidator.validate "#{self.class}.data[]", [String], ref + @data = [] + v.each do |data_field| + if data_field.is_a? String + data_field = {:ref => data_field} + end + data_field.values.each do |value| + DataTypeValidator.validate "#{self.class}.data[]", [String], value + end + @data << data_field end - @data = v + @data end # The pages @@ -138,20 +145,14 @@ module Axlsx @cache_definition ||= PivotTableCacheDefinition.new(self) end - # The worksheet relationships. This is managed automatically by the worksheet + # The relationships for this pivot table. # @return [Relationships] def relationships r = Relationships.new - r << Relationship.new(PIVOT_TABLE_CACHE_DEFINITION_R, "../#{cache_definition.pn}") + r << Relationship.new(cache_definition, PIVOT_TABLE_CACHE_DEFINITION_R, "../#{cache_definition.pn}") r end - # The relation reference id for this table - # @return [String] - def rId - "rId#{index+1}" - end - # Serializes the object # @param [String] str # @return [String] @@ -196,11 +197,11 @@ module Axlsx str << '</pageFields>' end unless data.empty? - str << '<dataFields count="' << data.size.to_s << '">' + str << "<dataFields count=\"#{data.size}\">" data.each do |datum_value| - str << '<dataField name="Sum of ' << datum_value << '" ' << - 'fld="' << header_index_of(datum_value).to_s << '" ' << - 'baseField="0" baseItem="0"/>' + str << "<dataField name='#{@subtotal} of #{datum_value[:ref]}' fld='#{header_index_of(datum_value[:ref])}' baseField='0' baseItem='0'" + str << " subtotal='#{datum_value[:subtotal]}' " if datum_value[:subtotal] + str << "/>" end str << '</dataFields>' end diff --git a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb index 37f46c51..665384f4 100644 --- a/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb +++ b/lib/axlsx/workbook/worksheet/pivot_table_cache_definition.rb @@ -35,10 +35,11 @@ module Axlsx index + 1 end - # The relation reference id for this table + # The relationship id for this pivot table cache definition. + # @see Relationship#Id # @return [String] def rId - "rId#{index + 1}" + pivot_table.relationships.for(self).Id end # Serializes the object diff --git a/lib/axlsx/workbook/worksheet/pivot_tables.rb b/lib/axlsx/workbook/worksheet/pivot_tables.rb index f5625fc0..912d9f41 100644 --- a/lib/axlsx/workbook/worksheet/pivot_tables.rb +++ b/lib/axlsx/workbook/worksheet/pivot_tables.rb @@ -17,7 +17,7 @@ module Axlsx # returns the relationships required by this collection def relationships return [] if empty? - map{ |pivot_table| Relationship.new(PIVOT_TABLE_R, "../#{pivot_table.pn}") } + map{ |pivot_table| Relationship.new(pivot_table, PIVOT_TABLE_R, "../#{pivot_table.pn}") } end end diff --git a/lib/axlsx/workbook/worksheet/table.rb b/lib/axlsx/workbook/worksheet/table.rb index dce1a40e..94474c57 100644 --- a/lib/axlsx/workbook/worksheet/table.rb +++ b/lib/axlsx/workbook/worksheet/table.rb @@ -47,10 +47,11 @@ module Axlsx "#{TABLE_PN % (index+1)}" end - # The relation reference id for this table + # The relationship id for this table. + # @see Relationship#Id # @return [String] def rId - "rId#{index+1}" + @sheet.relationships.for(self).Id end # The name of the Table. diff --git a/lib/axlsx/workbook/worksheet/tables.rb b/lib/axlsx/workbook/worksheet/tables.rb index 2d9a1f3c..814f995f 100644 --- a/lib/axlsx/workbook/worksheet/tables.rb +++ b/lib/axlsx/workbook/worksheet/tables.rb @@ -17,7 +17,7 @@ module Axlsx # returns the relationships required by this collection def relationships return [] if empty? - map{ |table| Relationship.new(TABLE_R, "../#{table.pn}") } + map{ |table| Relationship.new(table, TABLE_R, "../#{table.pn}") } end def to_xml_string(str = "") diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 5f650263..ecba9254 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -114,14 +114,19 @@ module Axlsx @rows ||= SimpleTypedList.new Row end - # returns the sheet data as columnw - def cols - @rows.transpose + # returns the sheet data as columns + # If you pass a block, it will be evaluated whenever a row does not have a + # cell at a specific index. The block will be called with the row and column + # index in the missing cell was found. + # @example + # cols { |row_index, column_index| p "warn - row #{row_index} is does not have a cell at #{column_index} + def cols(&block) + @rows.transpose(&block) end - # An range that excel will apply an autfilter to "A1:B3" + # An 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 considerd to be data. + # The first row is considered the header, while subsequent rows are considered to be data. # @return String def auto_filter @auto_filter ||= AutoFilter.new self @@ -327,6 +332,10 @@ module Axlsx auto_filter.range = v end + # Accessor for controlling whether leading and trailing spaces in cells are + # preserved or ignored. The default is to preserve spaces. + attr_accessor :preserve_spaces + # The part name of this worksheet # @return [String] def pn @@ -339,10 +348,11 @@ module Axlsx "#{WORKSHEET_RELS_PN % (index+1)}" end - # The relationship Id of thiw worksheet + # The relationship id of this worksheet. # @return [String] + # @see Relationship#Id def rId - "rId#{index+1}" + @workbook.relationships.for(self).Id end # The index of this worksheet in the owning Workbook's worksheets list. @@ -565,14 +575,6 @@ module Axlsx r end - # identifies the index of an object withing the collections used in generating relationships for the worksheet - # @param [Any] object the object to search for - # @return [Integer] The index of the object - def relationships_index_of(object) - objects = [tables.to_a, worksheet_comments.comments.to_a, hyperlinks.to_a, worksheet_drawing.drawing].flatten.compact || [] - objects.index(object) - end - # Returns the cell or cells defined using excel style A1:B3 references. # @param [String|Integer] cell_def the string defining the cell or range of cells, or the rownumber # @return [Cell, Array] @@ -629,6 +631,11 @@ module Axlsx end private + + def xml_space + workbook.xml_space + end + def outline(collection, range, level = 1, collapsed = true) range.each do |index| unless (item = collection[index]).nil? @@ -699,7 +706,7 @@ module Axlsx # 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] + "<worksheet xmlns=\"%s\" xmlns:r=\"%s\" xml:space=\"#{xml_space}\">" % [XML_NS, XML_NS_R] end def sheet_data diff --git a/lib/axlsx/workbook/worksheet/worksheet_comments.rb b/lib/axlsx/workbook/worksheet/worksheet_comments.rb index 8c700aa0..f9e4c8cd 100644 --- a/lib/axlsx/workbook/worksheet/worksheet_comments.rb +++ b/lib/axlsx/workbook/worksheet/worksheet_comments.rb @@ -40,10 +40,11 @@ module Axlsx !comments.empty? end - # The index in the worksheet's relationships for the VML drawing that will render the comments - # @return [Integer] - def index - worksheet.relationships.index { |r| r.Type == VML_DRAWING_R } + 1 + # The relationship id of the VML drawing that will render the comments. + # @see Relationship#Id + # @return [String] + def drawing_rId + comments.relationships.find{ |r| r.Type == VML_DRAWING_R }.Id end # Seraalize the object @@ -51,7 +52,7 @@ module Axlsx # @return [String] def to_xml_string(str = '') return unless has_comments? - str << "<legacyDrawing r:id='rId#{index}' />" + str << "<legacyDrawing r:id='#{drawing_rId}' />" end end end diff --git a/lib/axlsx/workbook/worksheet/worksheet_drawing.rb b/lib/axlsx/workbook/worksheet/worksheet_drawing.rb index 9deeeec3..08cad1f7 100644 --- a/lib/axlsx/workbook/worksheet/worksheet_drawing.rb +++ b/lib/axlsx/workbook/worksheet/worksheet_drawing.rb @@ -41,24 +41,18 @@ module Axlsx @drawing.is_a? Drawing end - # The relationship required by this object + # The relationship instance for this drawing. # @return [Relationship] def relationship return unless has_drawing? - Relationship.new(DRAWING_R, "../#{drawing.pn}") - end - - # returns the index of the worksheet releationship that defines this drawing. - # @return [Integer] - def index - worksheet.relationships.index{ |r| r.Type == DRAWING_R } +1 + Relationship.new(self, DRAWING_R, "../#{drawing.pn}") end # Serialize the drawing for the worksheet # @param [String] str def to_xml_string(str = '') return unless has_drawing? - str << "<drawing r:id='rId#{index}'/>" + str << "<drawing r:id='#{relationship.Id}'/>" end end end diff --git a/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb index d352ec90..2a241287 100644 --- a/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb +++ b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb @@ -45,18 +45,13 @@ module Axlsx @ref = cell_reference end - # The relationship required by this hyperlink when the taget is :external + # The relationship instance for this hyperlink. + # A relationship is only required if `@target` is `:external`. If not, this method will simply return `nil`. + # @see #target= # @return [Relationship] def relationship return unless @target == :external - Relationship.new HYPERLINK_R, location, :target_mode => :External - end - - # The id of the relationship for this object - # @return [String] - def id - return unless @target == :external - "rId#{(@worksheet.relationships_index_of(self)+1)}" + Relationship.new(self, HYPERLINK_R, location, :target_mode => :External) end # Seralize the object @@ -73,7 +68,7 @@ module Axlsx # r:id should only be specified for external targets. # @return [Hash] def location_or_id - @target == :external ? { :"r:id" => id } : { :location => Axlsx::coder.encode(location) } + @target == :external ? { :"r:id" => relationship.Id } : { :location => Axlsx::coder.encode(location) } end end end diff --git a/lib/schema/sml.xsd b/lib/schema/sml.xsd index fa396e80..3a67d0f2 100644 --- a/lib/schema/sml.xsd +++ b/lib/schema/sml.xsd @@ -13,6 +13,8 @@ <xsd:import
namespace="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
schemaLocation="dml-spreadsheetDrawing.xsd"/>
+ <xsd:import namespace="http://www.w3.org/XML/1998/namespace" schemaLocation="xml.xsd"/> + <xsd:complexType name="CT_AutoFilter">
<xsd:sequence>
<xsd:element name="filterColumn" minOccurs="0" maxOccurs="unbounded" type="CT_FilterColumn"/>
@@ -1797,6 +1799,7 @@ </xsd:sequence>
<xsd:attribute name="count" type="xsd:unsignedInt" use="optional"/>
<xsd:attribute name="uniqueCount" type="xsd:unsignedInt" use="optional"/>
+ <xsd:attribute ref="xml:space"/> </xsd:complexType>
<xsd:simpleType name="ST_PhoneticType">
<xsd:restriction base="xsd:string">
@@ -2213,6 +2216,7 @@ <xsd:element name="tableParts" type="CT_TableParts" minOccurs="0" maxOccurs="1"/>
<xsd:element name="extLst" type="CT_ExtensionList" minOccurs="0" maxOccurs="1"/>
</xsd:sequence>
+ <xsd:attribute ref="xml:space"/> </xsd:complexType>
<xsd:complexType name="CT_SheetData">
<xsd:sequence>
diff --git a/test/benchmark.rb b/test/benchmark.rb index 2ef82eaf..b1162591 100644 --- a/test/benchmark.rb +++ b/test/benchmark.rb @@ -1,5 +1,4 @@ #!/usr/bin/env ruby -s -# -*- coding: utf-8 -*- $:.unshift "#{File.dirname(__FILE__)}/../lib" require 'axlsx' require 'csv' diff --git a/test/doc_props/tc_core.rb b/test/doc_props/tc_core.rb index cfff4d59..0eddfed7 100644 --- a/test/doc_props/tc_core.rb +++ b/test/doc_props/tc_core.rb @@ -23,6 +23,13 @@ class TestCore < Test::Unit::TestCase assert_equal(@doc.xpath('//dcterms:created').text, @time, "dcterms:created incorrect") end + def test_created_as_option + time = Time.utc(2013, 1, 1, 12, 00) + c = Axlsx::Core.new :created => time + doc = Nokogiri::XML(c.to_xml_string) + assert_equal(doc.xpath('//dcterms:created').text, time.xmlschema, "dcterms:created incorrect") + end + def test_populates_default_name assert_equal(@doc.xpath('//dc:creator').text, "axlsx", "Default name not populated") end diff --git a/test/drawing/tc_axes.rb b/test/drawing/tc_axes.rb new file mode 100644 index 00000000..e3c26936 --- /dev/null +++ b/test/drawing/tc_axes.rb @@ -0,0 +1,8 @@ +require 'tc_helper.rb' + +class TestAxes < Test::Unit::TestCase + def test_constructor_requires_cat_axis_first + assert_raise(ArgumentError) { Axlsx::Axes.new(:val_axis => Axlsx::ValAxis, :cat_axis => Axlsx::CatAxis) } + assert_nothing_raised { Axlsx::Axes.new(:cat_axis => Axlsx::CatAxis, :val_axis => Axlsx::ValAxis) } + end +end
\ No newline at end of file diff --git a/test/drawing/tc_axis.rb b/test/drawing/tc_axis.rb index 807655d7..3546f786 100644 --- a/test/drawing/tc_axis.rb +++ b/test/drawing/tc_axis.rb @@ -2,25 +2,22 @@ require 'tc_helper.rb' class TestAxis < Test::Unit::TestCase def setup - - @axis = Axlsx::Axis.new 12345, 54321, :gridlines => false, :title => 'Foo' + @axis = Axlsx::Axis.new :gridlines => false, :title => 'Foo' end - def teardown - end def test_initialization - assert_equal(@axis.axPos, :b, "axis position default incorrect") - assert_equal(@axis.tickLblPos, :nextTo, "tick label position default incorrect") - assert_equal(@axis.tickLblPos, :nextTo, "tick label position default incorrect") + assert_equal(@axis.ax_pos, :b, "axis position default incorrect") + assert_equal(@axis.tick_lbl_pos, :nextTo, "tick label position default incorrect") + assert_equal(@axis.tick_lbl_pos, :nextTo, "tick label position default incorrect") assert_equal(@axis.crosses, :autoZero, "tick label position default incorrect") assert(@axis.scaling.is_a?(Axlsx::Scaling) && @axis.scaling.orientation == :minMax, "scaling default incorrect") - assert_raise(ArgumentError) { Axlsx::Axis.new( -1234, 'abcd') } assert_equal('Foo', @axis.title.text) end def test_color @axis.color = "00FF00" + @axis.cross_axis = Axlsx::CatAxis.new str = '<?xml version="1.0" encoding="UTF-8"?>' str << '<c:chartSpace xmlns:c="' << Axlsx::XML_NS_C << '" xmlns:a="' << Axlsx::XML_NS_A << '">' doc = Nokogiri::XML(@axis.to_xml_string(str)) @@ -35,15 +32,15 @@ class TestAxis < Test::Unit::TestCase sheet.add_row ['cat', 7, 9, 10] sheet.add_chart(Axlsx::Line3DChart) do |chart| chart.add_series :data => sheet['B2:D2'], :labels => sheet['B1'] - chart.valAxis.title = sheet['A1'] - assert_equal('battle victories', chart.valAxis.title.text) + chart.val_axis.title = sheet['A1'] + assert_equal('battle victories', chart.val_axis.title.text) end end end def test_axis_position - assert_raise(ArgumentError, "requires valid axis position") { @axis.axPos = :nowhere } - assert_nothing_raised("accepts valid axis position") { @axis.axPos = :r } + assert_raise(ArgumentError, "requires valid axis position") { @axis.ax_pos = :nowhere } + assert_nothing_raised("accepts valid axis position") { @axis.ax_pos = :r } end def test_label_rotation @@ -55,13 +52,13 @@ class TestAxis < Test::Unit::TestCase end def test_tick_label_position - assert_raise(ArgumentError, "requires valid tick label position") { @axis.tickLblPos = :nowhere } - assert_nothing_raised("accepts valid tick label position") { @axis.tickLblPos = :high } + assert_raise(ArgumentError, "requires valid tick label position") { @axis.tick_lbl_pos = :nowhere } + assert_nothing_raised("accepts valid tick label position") { @axis.tick_lbl_pos = :high } end def test_format_code - assert_raise(ArgumentError, "requires valid format code") { @axis.format_code = 1 } - assert_nothing_raised("accepts valid format code") { @axis.tickLblPos = :high } + assert_raise(ArgumentError, "requires valid format code") { @axis.format_code = :high } + assert_nothing_raised("accepts valid format code") { @axis.format_code = "00.##" } end def test_crosses @@ -75,12 +72,13 @@ class TestAxis < Test::Unit::TestCase end def test_to_xml_string + @axis.cross_axis = Axlsx::CatAxis.new str = '<?xml version="1.0" encoding="UTF-8"?>' str << '<c:chartSpace xmlns:c="' << Axlsx::XML_NS_C << '" xmlns:a="' << Axlsx::XML_NS_A << '">' doc = Nokogiri::XML(@axis.to_xml_string(str)) assert(doc.xpath('//a:noFill')) assert(doc.xpath("//c:crosses[@val='#{@axis.crosses.to_s}']")) - assert(doc.xpath("//c:crossAx[@val='#{@axis.crossAx.to_s}']")) + assert(doc.xpath("//c:crossAx[@val='#{@axis.cross_axis.to_s}']")) assert(doc.xpath("//a:bodyPr[@rot='#{@axis.label_rotation.to_s}']")) assert(doc.xpath("//a:t[text()='Foo']")) end diff --git a/test/drawing/tc_bar_3D_chart.rb b/test/drawing/tc_bar_3D_chart.rb index 3e1ff342..0cae7af6 100644 --- a/test/drawing/tc_bar_3D_chart.rb +++ b/test/drawing/tc_bar_3D_chart.rb @@ -62,4 +62,10 @@ class TestBar3DChart < Test::Unit::TestCase assert(errors.empty?, "error free validation") end + def test_to_xml_string_has_axes_in_correct_order + str = @chart.to_xml_string + cat_axis_position = str.index(@chart.axes[:cat_axis].id.to_s) + val_axis_position = str.index(@chart.axes[:val_axis].id.to_s) + assert(cat_axis_position < val_axis_position, "cat_axis must occur earlier than val_axis in the XML") + end end diff --git a/test/drawing/tc_cat_axis.rb b/test/drawing/tc_cat_axis.rb index 6529b45d..ac690336 100644 --- a/test/drawing/tc_cat_axis.rb +++ b/test/drawing/tc_cat_axis.rb @@ -2,15 +2,15 @@ require 'tc_helper.rb' class TestCatAxis < Test::Unit::TestCase def setup - @axis = Axlsx::CatAxis.new 12345, 54321 + @axis = Axlsx::CatAxis.new end def teardown end def test_initialization assert_equal(@axis.auto, 1, "axis auto default incorrect") - assert_equal(@axis.lblAlgn, :ctr, "label align default incorrect") - assert_equal(@axis.lblOffset, "100", "label offset default incorrect") + assert_equal(@axis.lbl_algn, :ctr, "label align default incorrect") + assert_equal(@axis.lbl_offset, "100", "label offset default incorrect") end def test_auto @@ -18,14 +18,14 @@ class TestCatAxis < Test::Unit::TestCase assert_nothing_raised("accepts valid auto") { @axis.auto = false } end - def test_lblAlgn - assert_raise(ArgumentError, "requires valid label alignment") { @axis.lblAlgn = :nowhere } - assert_nothing_raised("accepts valid label alignment") { @axis.lblAlgn = :r } + def test_lbl_algn + assert_raise(ArgumentError, "requires valid label alignment") { @axis.lbl_algn = :nowhere } + assert_nothing_raised("accepts valid label alignment") { @axis.lbl_algn = :r } end - def test_lblOffset - assert_raise(ArgumentError, "requires valid label offset") { @axis.lblOffset = 'foo' } - assert_nothing_raised("accepts valid label offset") { @axis.lblOffset = "20" } + def test_lbl_offset + assert_raise(ArgumentError, "requires valid label offset") { @axis.lbl_offset = 'foo' } + assert_nothing_raised("accepts valid label offset") { @axis.lbl_offset = "20" } end end diff --git a/test/drawing/tc_chart.rb b/test/drawing/tc_chart.rb index 03e4fd6f..751d2ae4 100644 --- a/test/drawing/tc_chart.rb +++ b/test/drawing/tc_chart.rb @@ -45,6 +45,14 @@ class TestChart < Test::Unit::TestCase assert_equal(false, @chart.vary_colors) end + def test_display_blanks_as + assert_equal(:gap, @chart.display_blanks_as, "default is not :gap") + assert_raise(ArgumentError, "did not validate possible values") { @chart.display_blanks_as = :hole } + assert_nothing_raised { @chart.display_blanks_as = :zero } + assert_nothing_raised { @chart.display_blanks_as = :span } + assert_equal(:span, @chart.display_blanks_as) + end + def test_start_at @chart.start_at 15, 25 assert_equal(@chart.graphic_frame.anchor.from.col, 15) @@ -94,4 +102,10 @@ class TestChart < Test::Unit::TestCase assert(errors.empty?, "error free validation") end + def test_to_xml_string_for_display_blanks_as + schema = Nokogiri::XML::Schema(File.open(Axlsx::DRAWING_XSD)) + @chart.display_blanks_as = :span + doc = Nokogiri::XML(@chart.to_xml_string) + assert_equal("span", doc.xpath("//c:dispBlanksAs").attr("val").value, "did not use the display_blanks_as configuration") + end end diff --git a/test/drawing/tc_drawing.rb b/test/drawing/tc_drawing.rb index f347001f..7014f1b2 100644 --- a/test/drawing/tc_drawing.rb +++ b/test/drawing/tc_drawing.rb @@ -53,11 +53,6 @@ class TestDrawing < Test::Unit::TestCase 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)) diff --git a/test/drawing/tc_graphic_frame.rb b/test/drawing/tc_graphic_frame.rb index ce4b8214..eb638489 100644 --- a/test/drawing/tc_graphic_frame.rb +++ b/test/drawing/tc_graphic_frame.rb @@ -17,14 +17,11 @@ class TestGraphicFrame < Test::Unit::TestCase end def test_rId - assert_equal(@frame.rId, "rId1") - chart = @ws.add_chart Axlsx::Chart - assert_equal(chart.graphic_frame.rId, "rId2") + assert_equal @ws.drawing.relationships.for(@chart).Id, @frame.rId end - def test_rId_with_image_and_chart - image = @ws.add_image :image_src => (File.dirname(__FILE__) + "/../../examples/image1.jpeg"), :start_at => [0,25], :width => 200, :height => 200 - assert_equal(2, image.id) - assert_equal(1, @chart.index+1) + def test_to_xml_has_correct_rId + doc = Nokogiri::XML(@frame.to_xml_string) + assert_equal @frame.rId, doc.xpath("//c:chart", doc.collect_namespaces).first["r:id"] end end diff --git a/test/drawing/tc_hyperlink.rb b/test/drawing/tc_hyperlink.rb index 292f1f96..b3f204b5 100644 --- a/test/drawing/tc_hyperlink.rb +++ b/test/drawing/tc_hyperlink.rb @@ -51,10 +51,6 @@ class TestHyperlink < Test::Unit::TestCase assert_equal(@hyperlink.highlightClick, false ) end - def test_id - assert_equal(@hyperlink.send(:id), 2) - end - def test_history assert_nothing_raised { @hyperlink.history = false } assert_raise(ArgumentError) {@hyperlink.history = "bob"} diff --git a/test/drawing/tc_line_chart.rb b/test/drawing/tc_line_chart.rb new file mode 100644 index 00000000..83ebef97 --- /dev/null +++ b/test/drawing/tc_line_chart.rb @@ -0,0 +1,39 @@ +require 'tc_helper.rb' + +class TestLineChart < Test::Unit::TestCase + + def setup + @p = Axlsx::Package.new + ws = @p.workbook.add_worksheet + @row = ws.add_row ["one", 1, Time.now] + @chart = ws.add_chart Axlsx::LineChart, :title => "fishery" + end + + def teardown + end + + def test_initialization + assert_equal(@chart.grouping, :standard, "grouping defualt incorrect") + assert_equal(@chart.series_type, Axlsx::LineSeries, "series type incorrect") + assert(@chart.cat_axis.is_a?(Axlsx::CatAxis), "category axis not created") + assert(@chart.val_axis.is_a?(Axlsx::ValAxis), "value access not created") + end + + def test_grouping + assert_raise(ArgumentError, "require valid grouping") { @chart.grouping = :inverted } + assert_nothing_raised("allow valid grouping") { @chart.grouping = :stacked } + assert(@chart.grouping == :stacked) + end + + def test_to_xml + schema = Nokogiri::XML::Schema(File.open(Axlsx::DRAWING_XSD)) + doc = Nokogiri::XML(@chart.to_xml_string) + errors = [] + schema.validate(doc).each do |error| + errors.push error + puts error.message + end + assert(errors.empty?, "error free validation") + end + +end diff --git a/test/drawing/tc_line_series.rb b/test/drawing/tc_line_series.rb index cc62005b..866553c3 100644 --- a/test/drawing/tc_line_series.rb +++ b/test/drawing/tc_line_series.rb @@ -6,7 +6,7 @@ class TestLineSeries < Test::Unit::TestCase p = Axlsx::Package.new @ws = p.workbook.add_worksheet :name=>"hmmm" chart = @ws.add_chart Axlsx::Line3DChart, :title => "fishery" - @series = chart.add_series :data=>[0,1,2], :labels=>["zero", "one", "two"], :title=>"bob", :color => "#FF0000" + @series = chart.add_series :data=>[0,1,2], :labels=>["zero", "one", "two"], :title=>"bob", :color => "#FF0000", :show_marker => true end def test_initialize @@ -15,10 +15,16 @@ class TestLineSeries < Test::Unit::TestCase assert_equal(@series.data.class, Axlsx::NumDataSource) end - + + def test_show_marker + assert_equal(true, @series.show_marker) + @series.show_marker = false + assert_equal(false, @series.show_marker) + end def test_to_xml_string doc = Nokogiri::XML(@series.to_xml_string) assert(doc.xpath("//srgbClr[@val='#{@series.color}']")) + assert(doc.xpath("//marker")) end #TODO serialization testing end diff --git a/test/drawing/tc_pic.rb b/test/drawing/tc_pic.rb index a892678d..79a3194d 100644 --- a/test/drawing/tc_pic.rb +++ b/test/drawing/tc_pic.rb @@ -93,4 +93,10 @@ class TestPic < Test::Unit::TestCase assert(errors.empty?, "error free validation") end + def test_to_xml_has_correct_r_id + r_id = @image.anchor.drawing.relationships.for(@image).Id + doc = Nokogiri::XML(@image.anchor.drawing.to_xml_string) + assert_equal r_id, doc.xpath("//a:blip").first["r:embed"] + end + end diff --git a/test/drawing/tc_ser_axis.rb b/test/drawing/tc_ser_axis.rb index 2f30ceb8..7febafce 100644 --- a/test/drawing/tc_ser_axis.rb +++ b/test/drawing/tc_ser_axis.rb @@ -2,29 +2,30 @@ require 'tc_helper.rb' class TestSerAxis < Test::Unit::TestCase def setup - @axis = Axlsx::SerAxis.new 12345, 54321 + @axis = Axlsx::SerAxis.new end + def teardown end def test_options - a = Axlsx::SerAxis.new 12345, 54321, :tickLblSkip => 9, :tickMarkSkip => 7 - assert_equal(a.tickLblSkip, 9) - assert_equal(a.tickMarkSkip, 7) + a = Axlsx::SerAxis.new(:tick_lbl_skip => 9, :tick_mark_skip => 7) + assert_equal(a.tick_lbl_skip, 9) + assert_equal(a.tick_mark_skip, 7) end - def test_tickLblSkip - assert_raise(ArgumentError, "requires valid tickLblSkip") { @axis.tickLblSkip = -1 } - assert_nothing_raised("accepts valid tickLblSkip") { @axis.tickLblSkip = 1 } - assert_equal(@axis.tickLblSkip, 1) + def test_tick_lbl_skip + assert_raise(ArgumentError, "requires valid tick_lbl_skip") { @axis.tick_lbl_skip = -1 } + assert_nothing_raised("accepts valid tick_lbl_skip") { @axis.tick_lbl_skip = 1 } + assert_equal(@axis.tick_lbl_skip, 1) end - def test_tickMarkSkip - assert_raise(ArgumentError, "requires valid tickMarkSkip") { @axis.tickMarkSkip = :my_eyes } - assert_nothing_raised("accepts valid tickMarkSkip") { @axis.tickMarkSkip = 2 } - assert_equal(@axis.tickMarkSkip, 2) + def test_tick_mark_skip + assert_raise(ArgumentError, "requires valid tick_mark_skip") { @axis.tick_mark_skip = :my_eyes } + assert_nothing_raised("accepts valid tick_mark_skip") { @axis.tick_mark_skip = 2 } + assert_equal(@axis.tick_mark_skip, 2) end end diff --git a/test/drawing/tc_val_axis.rb b/test/drawing/tc_val_axis.rb index f3f55421..aa1cb23a 100644 --- a/test/drawing/tc_val_axis.rb +++ b/test/drawing/tc_val_axis.rb @@ -2,23 +2,23 @@ require 'tc_helper.rb' class TestValAxis < Test::Unit::TestCase def setup - @axis = Axlsx::ValAxis.new 12345, 54321 + @axis = Axlsx::ValAxis.new end def teardown end def test_initialization - assert_equal(@axis.crossBetween, :between, "axis crossBetween default incorrect") + assert_equal(@axis.cross_between, :between, "axis crossBetween default incorrect") end def test_options - a = Axlsx::ValAxis.new 2345, 4321, :crossBetween => :midCat - assert_equal(a.crossBetween, :midCat) + a = Axlsx::ValAxis.new(:cross_between => :midCat) + assert_equal(:midCat, a.cross_between) end def test_crossBetween - assert_raise(ArgumentError, "requires valid crossBetween") { @axis.crossBetween = :my_eyes } - assert_nothing_raised("accepts valid crossBetween") { @axis.crossBetween = :midCat } + assert_raise(ArgumentError, "requires valid crossBetween") { @axis.cross_between = :my_eyes } + assert_nothing_raised("accepts valid crossBetween") { @axis.cross_between = :midCat } end end diff --git a/test/drawing/tc_vml_shape.rb b/test/drawing/tc_vml_shape.rb index 719beca7..94ad6e9f 100644 --- a/test/drawing/tc_vml_shape.rb +++ b/test/drawing/tc_vml_shape.rb @@ -6,8 +6,8 @@ class TestVmlShape < Test::Unit::TestCase p = Axlsx::Package.new wb = p.workbook @ws = wb.add_worksheet - @ws.add_comment :ref => 'A1', :text => 'penut machine', :author => 'crank' - @ws.add_comment :ref => 'C3', :text => 'rust bucket', :author => 'PO' + @ws.add_comment :ref => 'A1', :text => 'penut machine', :author => 'crank', :visible => true + @ws.add_comment :ref => 'C3', :text => 'rust bucket', :author => 'PO', :visible => false @comments = @ws.comments end @@ -84,11 +84,17 @@ class TestVmlShape < Test::Unit::TestCase assert(shape.top_row == 3) assert_raise(ArgumentError) { shape.top_row = [] } end - + def test_visible + shape = @comments.first.vml_shape + shape.visible = false + assert(shape.visible == false) + assert_raise(ArgumentError) { shape.visible = 'foo' } + end def test_to_xml_string str = @comments.vml_drawing.to_xml_string() doc = Nokogiri::XML(str) assert_equal(doc.xpath("//v:shape").size, 2) + assert_equal(1, doc.xpath("//x:Visible").size, 'ClientData/x:Visible element rendering') @comments.each do |comment| shape = comment.vml_shape assert(doc.xpath("//v:shape/x:ClientData/x:Row[text()='#{shape.row}']").size == 1) diff --git a/test/profile.rb b/test/profile.rb index 8e4218fd..ffe057e4 100644 --- a/test/profile.rb +++ b/test/profile.rb @@ -1,26 +1,20 @@ #!/usr/bin/env ruby -s -# Usage: -# > ruby test/profile.rb -# > pprof.rb --gif /tmp/axlsx > /tmp/axlsx.gif -# > open /tmp/axlsx_noautowidth.gif - $:.unshift "#{File.dirname(__FILE__)}/../lib" require 'axlsx' -require 'perftools' -Axlsx.trust_input = true +require 'ruby-prof' row = [] # Taking worst case scenario of all string data input = (32..126).to_a.pack('U*').chars.to_a 20.times { row << input.shuffle.join} -times = 3000 -PerfTools::CpuProfiler.start("/tmp/axlsx") do +profile = RubyProf.profile do p = Axlsx::Package.new p.workbook.add_worksheet do |sheet| - times.times do + 30.times do sheet << row end end - p.serialize("example.xlsx") end +printer = RubyProf::CallTreePrinter.new(profile) +printer.print(File.new('axlsx.qcachegrind', 'w')) diff --git a/test/rels/tc_relationship.rb b/test/rels/tc_relationship.rb index 32b9e4a1..add1654f 100644 --- a/test/rels/tc_relationship.rb +++ b/test/rels/tc_relationship.rb @@ -1,26 +1,44 @@ require 'tc_helper.rb' class TestRelationships < Test::Unit::TestCase - def setup + + def test_instances_with_different_attributes_have_unique_ids + rel_1 = Axlsx::Relationship.new(Object.new, Axlsx::WORKSHEET_R, 'target') + rel_2 = Axlsx::Relationship.new(Object.new, Axlsx::COMMENT_R, 'foobar') + assert_not_equal rel_1.Id, rel_2.Id end - - def teardown + + def test_instances_with_same_attributes_share_id + source_obj = Object.new + instance = Axlsx::Relationship.new(source_obj, Axlsx::WORKSHEET_R, 'target') + assert_equal instance.Id, Axlsx::Relationship.new(source_obj, Axlsx::WORKSHEET_R, 'target').Id end - + + def test_target_is_only_considered_for_same_attributes_check_if_target_mode_is_external + source_obj = Object.new + rel_1 = Axlsx::Relationship.new(source_obj, Axlsx::WORKSHEET_R, 'target') + rel_2 = Axlsx::Relationship.new(source_obj, Axlsx::WORKSHEET_R, '../target') + assert_equal rel_1.Id, rel_2.Id + + rel_3 = Axlsx::Relationship.new(source_obj, Axlsx::HYPERLINK_R, 'target', :target_mode => :External) + rel_4 = Axlsx::Relationship.new(source_obj, Axlsx::HYPERLINK_R, '../target', :target_mode => :External) + assert_not_equal rel_3.Id, rel_4.Id + end + def test_type - assert_raise(ArgumentError) { Axlsx::Relationship.new 'type', 'target' } - assert_nothing_raised { Axlsx::Relationship.new Axlsx::WORKSHEET_R, 'target' } - assert_nothing_raised { Axlsx::Relationship.new Axlsx::COMMENT_R, 'target' } + assert_raise(ArgumentError) { Axlsx::Relationship.new nil, 'type', 'target' } + assert_nothing_raised { Axlsx::Relationship.new nil, Axlsx::WORKSHEET_R, 'target' } + assert_nothing_raised { Axlsx::Relationship.new nil, Axlsx::COMMENT_R, 'target' } end def test_target_mode - assert_raise(ArgumentError) { Axlsx::Relationship.new 'type', 'target', :target_mode => "FISH" } - assert_nothing_raised { Axlsx::Relationship.new( Axlsx::WORKSHEET_R, 'target', :target_mode => :External) } + assert_raise(ArgumentError) { Axlsx::Relationship.new nil, 'type', 'target', :target_mode => "FISH" } + assert_nothing_raised { Axlsx::Relationship.new( nil, Axlsx::WORKSHEET_R, 'target', :target_mode => :External) } end def test_ampersand_escaping_in_target - r = Axlsx::Relationship.new(Axlsx::HYPERLINK_R, "http://example.com?foo=1&bar=2", :target_mod => :External) - doc = Nokogiri::XML(r.to_xml_string(1)) + r = Axlsx::Relationship.new(nil, Axlsx::HYPERLINK_R, "http://example.com?foo=1&bar=2", :target_mod => :External) + doc = Nokogiri::XML(r.to_xml_string) assert_equal(doc.xpath("//Relationship[@Target='http://example.com?foo=1&bar=2']").size, 1) end end diff --git a/test/rels/tc_relationships.rb b/test/rels/tc_relationships.rb index 356e4691..fe5a1ce5 100644 --- a/test/rels/tc_relationships.rb +++ b/test/rels/tc_relationships.rb @@ -2,6 +2,17 @@ require 'tc_helper.rb' class TestRelationships < Test::Unit::TestCase + def test_for + source_obj_1, source_obj_2 = Object.new, Object.new + rel_1 = Axlsx::Relationship.new(source_obj_1, Axlsx::WORKSHEET_R, "bar") + rel_2 = Axlsx::Relationship.new(source_obj_2, Axlsx::WORKSHEET_R, "bar") + rels = Axlsx::Relationships.new + rels << rel_1 + rels << rel_2 + assert_equal rel_1, rels.for(source_obj_1) + assert_equal rel_2, rels.for(source_obj_2) + end + def test_valid_document @rels = Axlsx::Relationships.new schema = Nokogiri::XML::Schema(File.open(Axlsx::RELS_XSD)) @@ -12,7 +23,7 @@ class TestRelationships < Test::Unit::TestCase errors << error end - @rels << Axlsx::Relationship.new(Axlsx::WORKSHEET_R, "bar") + @rels << Axlsx::Relationship.new(nil, Axlsx::WORKSHEET_R, "bar") doc = Nokogiri::XML(@rels.to_xml_string) errors = [] schema.validate(doc).each do |error| diff --git a/test/stylesheet/tc_styles.rb b/test/stylesheet/tc_styles.rb index b9d0b28a..98c8e3ef 100644 --- a/test/stylesheet/tc_styles.rb +++ b/test/stylesheet/tc_styles.rb @@ -150,7 +150,7 @@ class TestStyles < Test::Unit::TestCase assert_equal(@styles.parse_fill_options(:bg_color => "DE").class, Fixnum, "return index of fill if not :dxf type") assert_equal(@styles.parse_fill_options(:bg_color => "DE", :type => :dxf).class, Axlsx::Fill, "return fill object if :dxf type") f = @styles.parse_fill_options(:bg_color => "DE", :type => :dxf) - assert(f.fill_type.fgColor.rgb == "FFDEDEDE") + assert(f.fill_type.bgColor.rgb == "FFDEDEDE") end def test_parse_protection_options @@ -210,7 +210,7 @@ class TestStyles < Test::Unit::TestCase assert_equal(0, style, "returns the zero-based dxfId") dxf = @styles.dxfs.last - assert_equal(@styles.dxfs.last.fill.fill_type.fgColor.rgb, "FF000000", "fill created with color") + assert_equal(@styles.dxfs.last.fill.fill_type.bgColor.rgb, "FF000000", "fill created with color") assert_equal(font_count, (@styles.fonts.size), "font not created under styles") assert_equal(fill_count, (@styles.fills.size), "fill not created under styles") diff --git a/test/tc_helper.rb b/test/tc_helper.rb index 34f7f22d..af40a1e4 100644 --- a/test/tc_helper.rb +++ b/test/tc_helper.rb @@ -2,7 +2,9 @@ $LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" require 'simplecov' SimpleCov.start do add_filter "/test/" + add_filter "/vendor/" end require 'test/unit' +require "timecop" require "axlsx.rb" diff --git a/test/tc_package.rb b/test/tc_package.rb index 5fd411e0..833f36b1 100644 --- a/test/tc_package.rb +++ b/test/tc_package.rb @@ -105,6 +105,12 @@ class TestPackage < Test::Unit::TestCase assert(Axlsx::Package.new.workbook.worksheets.size == 0, 'Workbook should not have sheets by default') end + def test_created_at_is_propagated_to_core + time = Time.utc(2013, 1, 1, 12, 0) + p = Axlsx::Package.new :created_at => time + assert_equal(time, p.core.created) + end + def test_serialization assert_nothing_raised do begin @@ -117,6 +123,27 @@ class TestPackage < Test::Unit::TestCase end end end + + # See comment for Package#zip_entry_for_part + def test_serialization_creates_identical_files_at_any_time_if_created_at_is_set + @package.core.created = Time.now + zip_content_now = @package.to_stream.string + Timecop.travel(3600) do + zip_content_then = @package.to_stream.string + assert zip_content_then == zip_content_now, "zip files are not identical" + end + end + + def test_serialization_creates_identical_files_for_identical_packages + package_1, package_2 = 2.times.map do + Axlsx::Package.new(:created_at => Time.utc(2013, 1, 1)).tap do |p| + p.workbook.add_worksheet(:name => "Basic Worksheet") do |sheet| + sheet.add_row [1, 2, 3] + end + end + end + assert package_1.to_stream.string == package_2.to_stream.string, "zip files are not identical" + end def test_validation assert_equal(@package.validate.size, 0, @package.validate) diff --git a/test/util/tc_validators.rb b/test/util/tc_validators.rb index de896f3d..7a82f90d 100644 --- a/test/util/tc_validators.rb +++ b/test/util/tc_validators.rb @@ -158,7 +158,11 @@ class TestValidators < Test::Unit::TestCase assert_raise(ArgumentError) { Axlsx.validate_split_state_type 'frozen_split' } assert_raise(ArgumentError) { Axlsx.validate_split_state_type 0 } end - + + def test_validate_integerish + assert_raise(ArgumentError) { Axlsx.validate_integerish Axlsx } + [1, 1.4, "a"].each { |test_value| assert_nothing_raised { Axlsx.validate_integerish test_value } } + end def test_validate_family assert_raise(ArgumentError) { Axlsx.validate_family 0 } (1..5).each do |item| diff --git a/test/workbook/tc_shared_strings_table.rb b/test/workbook/tc_shared_strings_table.rb index e3c9bf7b..7a333f4f 100644 --- a/test/workbook/tc_shared_strings_table.rb +++ b/test/workbook/tc_shared_strings_table.rb @@ -24,6 +24,12 @@ class TestSharedStringsTable < Test::Unit::TestCase assert_equal(sst.unique_count, 4) end + def test_uses_workbook_xml_space + assert_equal(@p.workbook.xml_space, @p.workbook.shared_strings.xml_space) + @p.workbook.xml_space = :default + assert_equal(:default, @p.workbook.shared_strings.xml_space) + end + def test_valid_document schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD)) doc = Nokogiri::XML(@p.workbook.shared_strings.to_xml_string) diff --git a/test/workbook/tc_workbook.rb b/test/workbook/tc_workbook.rb index 1a9b9669..32b6935a 100644 --- a/test/workbook/tc_workbook.rb +++ b/test/workbook/tc_workbook.rb @@ -9,6 +9,23 @@ class TestWorkbook < Test::Unit::TestCase def teardown end + def test_worksheet_users_xml_space + sheet = @wb.add_worksheet(:name => 'foo') + ws_xml = Nokogiri::XML(sheet.to_xml_string) + assert(ws_xml.xpath("//xmlns:worksheet/@xml:space='preserve'")) + + @wb.xml_space = :default + ws_xml = Nokogiri::XML(sheet.to_xml_string) + assert(ws_xml.xpath("//xmlns:worksheet/@xml:space='default'")) + end + + def test_xml_space + assert_equal(:preserve, @wb.xml_space) + @wb.xml_space = :default + assert_equal(:default, @wb.xml_space) + assert_raise(ArgumentError) { @wb.xml_space = :none } + end + def test_no_autowidth assert_equal(@wb.use_autowidth, true) assert_raise(ArgumentError) {@wb.use_autowidth = 0.1} @@ -99,5 +116,10 @@ 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_uses_correct_rIds_for_pivotCache + ws = @wb.add_worksheet + pivot_table = ws.add_pivot_table('G5:G6', 'A1:D5') + doc = Nokogiri::XML(@wb.to_xml_string) + assert_equal pivot_table.cache_definition.rId, doc.xpath("//xmlns:pivotCache").first["r:id"] + end end diff --git a/test/workbook/worksheet/tc_comment.rb b/test/workbook/worksheet/tc_comment.rb index 0cb79bcc..e66abb9a 100644 --- a/test/workbook/worksheet/tc_comment.rb +++ b/test/workbook/worksheet/tc_comment.rb @@ -5,7 +5,7 @@ class TestComment < Test::Unit::TestCase p = Axlsx::Package.new wb = p.workbook @ws = wb.add_worksheet - @c1 = @ws.add_comment :ref => 'A1', :text => 'penut machine', :author => 'crank' + @c1 = @ws.add_comment :ref => 'A1', :text => 'text with special char <', :author => 'author with special char <', :visible => false @c2 = @ws.add_comment :ref => 'C3', :text => 'rust bucket', :author => 'PO' end @@ -14,12 +14,12 @@ class TestComment < Test::Unit::TestCase end def test_author - assert(@c1.author == 'crank') + assert(@c1.author == 'author with special char <') assert(@c2.author == 'PO') end def test_text - assert(@c1.text == 'penut machine') + assert(@c1.text == 'text with special char <') assert(@c2.text == 'rust bucket') end @@ -28,6 +28,10 @@ class TestComment < Test::Unit::TestCase assert_equal(@c2.author_index, 0) end + def test_visible + assert_equal(false, @c1.visible) + assert_equal(true, @c2.visible) + end def test_ref assert(@c1.ref == 'A1') assert(@c2.ref == 'C3') @@ -45,13 +49,24 @@ class TestComment < Test::Unit::TestCase assert(@c1.vml_shape.bottom_row == pos[1]+4) end - def to_xml_string + def test_to_xml_string doc = Nokogiri::XML(@c1.to_xml_string) assert_equal(doc.xpath("//comment[@ref='#{@c1.ref}']").size, 1) - assert_equal(doc.xpath("//comment[@authorId='#{@c1.author_index.to}']").size, 1) - assert_equal(doc.xpath("//t[text()='#{@c1.author}']").size, 1) + assert_equal(doc.xpath("//comment[@authorId='#{@c1.author_index.to_s}']").size, 1) + assert_equal(doc.xpath("//t[text()='#{@c1.author}:\n']").size, 1) assert_equal(doc.xpath("//t[text()='#{@c1.text}']").size, 1) end + def test_comment_text_contain_author_and_text + comment = @ws.add_comment :ref => 'C4', :text => 'some text', :author => 'Bob' + doc = Nokogiri::XML(comment.to_xml_string) + assert_equal("Bob:\nsome text", doc.xpath("//comment/text").text) + end + + def test_comment_text_does_not_contain_stray_colon_if_author_blank + comment = @ws.add_comment :ref => 'C5', :text => 'some text', :author => '' + doc = Nokogiri::XML(comment.to_xml_string) + assert_equal("some text", doc.xpath("//comment/text").text) + end end diff --git a/test/workbook/worksheet/tc_comments.rb b/test/workbook/worksheet/tc_comments.rb index 665f3598..acadf73d 100644 --- a/test/workbook/worksheet/tc_comments.rb +++ b/test/workbook/worksheet/tc_comments.rb @@ -25,9 +25,9 @@ class TestComments < Test::Unit::TestCase end def test_authors assert_equal(@ws.comments.authors.size, @ws.comments.size) - @ws.add_comment(:text => 'Yes We Can!', :author => :bob, :ref => 'F1') + @ws.add_comment(:text => 'Yes We Can!', :author => 'bob', :ref => 'F1') assert_equal(@ws.comments.authors.size, 3) - @ws.add_comment(:text => 'Yes We Can!', :author => :bob, :ref => 'F1') + @ws.add_comment(:text => 'Yes We Can!', :author => 'bob', :ref => 'F1') assert_equal(@ws.comments.authors.size, 3, 'only unique authors are returned') end def test_pn diff --git a/test/workbook/worksheet/tc_conditional_formatting.rb b/test/workbook/worksheet/tc_conditional_formatting.rb index 087fd40e..9e5e01cf 100644 --- a/test/workbook/worksheet/tc_conditional_formatting.rb +++ b/test/workbook/worksheet/tc_conditional_formatting.rb @@ -130,6 +130,15 @@ class TestConditionalFormatting < Test::Unit::TestCase assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='greaterThan']//xmlns:formula='0.5'") end + def test_multiple_formulas + @ws.add_conditional_formatting "B3:B3", { :type => :cellIs, :dxfId => 0, :priority => 1, :operator => :between, :formula => ["1 <> 2","5"] } + doc = Nokogiri::XML.parse(@ws.to_xml_string) + p doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='between']") + + assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='between']//xmlns:formula='1 <> 2'") + assert doc.xpath("//xmlns:worksheet/xmlns:conditionalFormatting//xmlns:cfRule[@type='cellIs'][@dxfId=0][@priority=1][@operator='between']//xmlns:formula='5'") + end + def test_sqref assert_raise(ArgumentError) { @cf.sqref = 10 } assert_nothing_raised { @cf.sqref = "A1:A1" } diff --git a/test/workbook/worksheet/tc_pivot_table.rb b/test/workbook/worksheet/tc_pivot_table.rb index 9adbaf93..ee90bec2 100644 --- a/test/workbook/worksheet/tc_pivot_table.rb +++ b/test/workbook/worksheet/tc_pivot_table.rb @@ -1,5 +1,17 @@ require 'tc_helper.rb' + +def shared_test_pivot_table_xml_validity(pivot_table) + schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD)) + doc = Nokogiri::XML(pivot_table.to_xml_string) + errors = [] + schema.validate(doc).each do |error| + errors.push error + puts error.message + end + assert(errors.empty?, "error free validation") +end + class TestPivotTable < Test::Unit::TestCase def setup p = Axlsx::Package.new @@ -33,10 +45,17 @@ class TestPivotTable < Test::Unit::TestCase end assert_equal(['Year', 'Month'], pivot_table.rows) assert_equal(['Type'], pivot_table.columns) - assert_equal(['Sales'], pivot_table.data) + assert_equal([{:ref=>"Sales"}], pivot_table.data) assert_equal(['Region'], pivot_table.pages) end + def test_add_pivot_table_with_options_on_data_field + pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5') do |pt| + pt.data = [{:ref=>"Sales", :subtotal => 'average'}] + end + assert_equal([{:ref=>"Sales", :subtotal => 'average'}], pivot_table.data) + end + def test_header_indices pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5') assert_equal(0, pivot_table.header_index_of('Year' )) @@ -53,11 +72,6 @@ class TestPivotTable < Test::Unit::TestCase assert_equal(@ws.pivot_tables.first.pn, "pivotTables/pivotTable1.xml") end - def test_rId - @ws.add_pivot_table('G5:G6', 'A1:D5') - assert_equal(@ws.pivot_tables.first.rId, "rId1") - end - def test_index @ws.add_pivot_table('G5:G6', 'A1:D5') assert_equal(@ws.pivot_tables.first.index, @ws.workbook.pivot_tables.index(@ws.pivot_tables.first)) @@ -73,14 +87,7 @@ class TestPivotTable < Test::Unit::TestCase def test_to_xml_string pivot_table = @ws.add_pivot_table('G5:G6', 'A1:D5') - schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD)) - doc = Nokogiri::XML(pivot_table.to_xml_string) - errors = [] - schema.validate(doc).each do |error| - errors.push error - puts error.message - end - assert(errors.empty?, "error free validation") + shared_test_pivot_table_xml_validity(pivot_table) end def test_to_xml_string_with_configuration @@ -90,13 +97,13 @@ class TestPivotTable < Test::Unit::TestCase pt.data = ['Sales'] pt.pages = ['Region'] end - schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD)) - doc = Nokogiri::XML(pivot_table.to_xml_string) - errors = [] - schema.validate(doc).each do |error| - errors.push error - puts error.message + shared_test_pivot_table_xml_validity(pivot_table) + end + + def test_to_xml_string_with_options_on_data_field + pivot_table = @ws.add_pivot_table('G5:G6', 'A1:E5') do |pt| + pt.data = [{:ref=>"Sales", :subtotal => 'average'}] end - assert(errors.empty?, "error free validation") + shared_test_pivot_table_xml_validity(pivot_table) end end diff --git a/test/workbook/worksheet/tc_pivot_table_cache_definition.rb b/test/workbook/worksheet/tc_pivot_table_cache_definition.rb index 2b4389b7..a38e808a 100644 --- a/test/workbook/worksheet/tc_pivot_table_cache_definition.rb +++ b/test/workbook/worksheet/tc_pivot_table_cache_definition.rb @@ -21,7 +21,7 @@ class TestPivotTableCacheDefinition < Test::Unit::TestCase end def test_rId - assert_equal('rId1', @cache_definition.rId) + assert_equal @pivot_table.relationships.for(@cache_definition).Id, @cache_definition.rId end def test_index diff --git a/test/workbook/worksheet/tc_table.rb b/test/workbook/worksheet/tc_table.rb index de86b886..6f39bb13 100644 --- a/test/workbook/worksheet/tc_table.rb +++ b/test/workbook/worksheet/tc_table.rb @@ -36,8 +36,8 @@ class TestTable < Test::Unit::TestCase end def test_rId - @ws.add_table("A1:D5") - assert_equal(@ws.tables.first.rId, "rId1") + table = @ws.add_table("A1:D5") + assert_equal @ws.relationships.for(table).Id, table.rId end def test_index diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb index ed8c7cab..00983fec 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -123,9 +123,7 @@ class TestWorksheet < Test::Unit::TestCase end def test_rId - assert_equal(@ws.rId, "rId1") - ws = @ws.workbook.add_worksheet - assert_equal(ws.rId, "rId2") + assert_equal @ws.workbook.relationships.for(@ws).Id, @ws.rId end def test_index @@ -205,13 +203,20 @@ class TestWorksheet < Test::Unit::TestCase def test_cols @ws.add_row [1,2,3,4] @ws.add_row [1,2,3,4] - @ws.add_row [1,2,3,4] + @ws.add_row [1,2,3] @ws.add_row [1,2,3,4] c = @ws.cols[1] assert_equal(c.size, 4) assert_equal(c[0].value, 2) end + def test_cols_with_block + @ws.add_row [1,2,3] + @ws.add_row [1] + cols = @ws.cols {|row, column| :foo } + assert_equal(:foo, cols[1][1]) + end + def test_row_style @ws.add_row [1,2,3,4] @ws.add_row [1,2,3,4] @@ -334,16 +339,16 @@ class TestWorksheet < Test::Unit::TestCase def test_to_xml_string_drawing @ws.add_chart Axlsx::Pie3DChart doc = Nokogiri::XML(@ws.to_xml_string) - assert_equal(doc.xpath('//xmlns:worksheet/xmlns:drawing[@r:id="rId1"]').size, 1) + assert_equal @ws.send(:worksheet_drawing).relationship.Id, doc.xpath('//xmlns:worksheet/xmlns:drawing').first["r:id"] end def test_to_xml_string_tables @ws.add_row ["one", "two"] @ws.add_row [1, 2] - @ws.add_table "A1:B2" + table = @ws.add_table "A1:B2" doc = Nokogiri::XML(@ws.to_xml_string) assert_equal(doc.xpath('//xmlns:worksheet/xmlns:tableParts[@count="1"]').size, 1) - assert_equal(doc.xpath('//xmlns:worksheet/xmlns:tableParts/xmlns:tablePart[@r:id="rId1"]').size, 1) + assert_equal table.rId, doc.xpath('//xmlns:worksheet/xmlns:tableParts/xmlns:tablePart').first["r:id"] end def test_to_xml_string diff --git a/test/workbook/worksheet/tc_worksheet_hyperlink.rb b/test/workbook/worksheet/tc_worksheet_hyperlink.rb index 278c5add..748082ac 100644 --- a/test/workbook/worksheet/tc_worksheet_hyperlink.rb +++ b/test/workbook/worksheet/tc_worksheet_hyperlink.rb @@ -32,22 +32,13 @@ class TestWorksheetHyperlink < Test::Unit::TestCase assert_equal(@options[:ref], @a.ref) end - def test_id - @a.target = :external - - assert_equal("rId1", @a.id) - @a.target = :internal - assert_equal(nil, @a.id) - end - - def test_to_xml_string_with_non_external doc = Nokogiri::XML(@ws.to_xml_string) assert_equal(doc.xpath("//xmlns:hyperlink[@ref='#{@a.ref}']").size, 1) assert_equal(doc.xpath("//xmlns:hyperlink[@tooltip='#{@a.tooltip}']").size, 1) assert_equal(doc.xpath("//xmlns:hyperlink[@location='#{@a.location}']").size, 1) assert_equal(doc.xpath("//xmlns:hyperlink[@display='#{@a.display}']").size, 1) - assert_equal(doc.xpath("//xmlns:hyperlink[@r:id='#{@a.id}']").size, 0) + assert_equal(doc.xpath("//xmlns:hyperlink[@r:id]").size, 0) end def test_to_xml_stirng_with_external @@ -57,7 +48,7 @@ class TestWorksheetHyperlink < Test::Unit::TestCase assert_equal(doc.xpath("//xmlns:hyperlink[@tooltip='#{@a.tooltip}']").size, 1) assert_equal(doc.xpath("//xmlns:hyperlink[@display='#{@a.display}']").size, 1) assert_equal(doc.xpath("//xmlns:hyperlink[@location='#{@a.location}']").size, 0) - assert_equal(doc.xpath("//xmlns:hyperlink[@r:id='#{@a.id}']").size, 1) + assert_equal(doc.xpath("//xmlns:hyperlink[@r:id='#{@a.relationship.Id}']").size, 1) end end |