diff options
| -rw-r--r-- | README.md | 6 | ||||
| -rw-r--r-- | examples/basic_charts.rb | 8 | ||||
| -rw-r--r-- | lib/axlsx.rb | 2 | ||||
| -rw-r--r-- | lib/axlsx/drawing/bar_3D_chart.rb | 10 | ||||
| -rw-r--r-- | lib/axlsx/drawing/chart.rb | 5 | ||||
| -rw-r--r-- | lib/axlsx/drawing/d_lbls.rb | 107 | ||||
| -rw-r--r-- | lib/axlsx/drawing/drawing.rb | 1 | ||||
| -rw-r--r-- | lib/axlsx/drawing/line_3D_chart.rb | 11 | ||||
| -rw-r--r-- | lib/axlsx/drawing/pie_3D_chart.rb | 2 | ||||
| -rw-r--r-- | lib/axlsx/drawing/scatter_chart.rb | 4 | ||||
| -rw-r--r-- | lib/axlsx/workbook/worksheet/worksheet.rb | 20 | ||||
| -rw-r--r-- | rubima.md | 74 | ||||
| -rw-r--r-- | test/drawing/tc_chart.rb | 8 | ||||
| -rw-r--r-- | test/drawing/tc_d_lbls.rb | 47 | ||||
| -rw-r--r-- | test/workbook/worksheet/tc_worksheet.rb | 11 |
15 files changed, 283 insertions, 33 deletions
@@ -116,6 +116,11 @@ This gem has 100% test coverage using test/unit. To execute tests for this gem, #Change log --------- +- **July.??.12**: 1.1.9 release + - lots of code clean up for worksheet + - added in data labels for pie charts, line charts and bar charts. + - bugfix chard with data in a sheet that has a space in the name are + now auto updating formula based values - **July.14.12**: 1.1.8 release - added html entity encoding for sheet names. This allows you to use characters like '<' and '&' in your sheet names. @@ -191,7 +196,6 @@ related to themes, which axlsx does not implement at this time. 2. Google Docs - Images are known to not work with google docs - border colors do not work - - Charts, for the most part, do not work. Google docs has some specific requirements about how the worksheet is set up to create a chart. 3. Numbers - you must set 'use_shared_strings' to true diff --git a/examples/basic_charts.rb b/examples/basic_charts.rb index 63cb715b..692dc192 100644 --- a/examples/basic_charts.rb +++ b/examples/basic_charts.rb @@ -9,6 +9,10 @@ wb.add_worksheet(:name => "Pie Chart") do |sheet| sheet.add_row [1, 2, 3, 4] sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,2], :end_at => [5, 15], :title=> 'dark corner here') do |chart| chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"] + chart.d_lbls.show_val = true + chart.d_lbls.show_percent = true + chart.d_lbls.d_lbl_pos = :outEnd + chart.d_lbls.show_leader_lines = true end end @@ -18,6 +22,8 @@ end sheet.add_row [1, 2, 3, '=sum(A2:C2)'] sheet.add_chart(Axlsx::Line3DChart, :start_at => [0,2], :end_at => [5, 15], :title => "Chart") do |chart| chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"], :title => 'bob' + chart.d_lbls.show_val = true + chart.d_lbls.show_cat_name = true end end @@ -30,6 +36,8 @@ end chart.add_series :data => sheet["A3:C3"], :labels => sheet["A2:C2"], :title => sheet["A1"] chart.valAxis.label_rotation = -45 chart.catAxis.label_rotation = 45 + chart.d_lbls.d_lbl_pos = :outEnd + chart.d_lbls.show_val = true end end diff --git a/lib/axlsx.rb b/lib/axlsx.rb index 2fb1fa36..ad14cd7a 100644 --- a/lib/axlsx.rb +++ b/lib/axlsx.rb @@ -45,7 +45,7 @@ module Axlsx return "" unless cells.first.is_a? Cell sort_cells(cells) reference = "#{cells.first.reference(absolute)}:#{cells.last.reference(absolute)}" - absolute ? "#{cells.first.row.worksheet.name}!#{reference}" : reference + absolute ? "'#{cells.first.row.worksheet.name}'!#{reference}" : reference end def self.sort_cells(cells) diff --git a/lib/axlsx/drawing/bar_3D_chart.rb b/lib/axlsx/drawing/bar_3D_chart.rb index 5db7c1e4..50dbecb2 100644 --- a/lib/axlsx/drawing/bar_3D_chart.rb +++ b/lib/axlsx/drawing/bar_3D_chart.rb @@ -77,6 +77,7 @@ module Axlsx super(frame, options) @series_type = BarSeries @view_3D = View3D.new({:r_ang_ax=>1}.merge(options)) + @d_lbls = nil end # The direction of the bars in the chart @@ -125,14 +126,7 @@ module Axlsx str_inner << '<c:grouping val="' << grouping.to_s << '"/>' str_inner << '<c:varyColors val="1"/>' @series.each { |ser| ser.to_xml_string(str_inner) } - str_inner << '<c:dLbls>' - str_inner << '<c:showLegendKey val="0"/>' - str_inner << '<c:showVal val="0"/>' - str_inner << '<c:showCatName val="0"/>' - str_inner << '<c:showSerName val="0"/>' - str_inner << '<c:showPercent val="0"/>' - str_inner << '<c:showBubbleSize val="0"/>' - str_inner << '</c:dLbls>' + @d_lbls.to_xml_string(str) 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? diff --git a/lib/axlsx/drawing/chart.rb b/lib/axlsx/drawing/chart.rb index 3047fec2..d0c3cd13 100644 --- a/lib/axlsx/drawing/chart.rb +++ b/lib/axlsx/drawing/chart.rb @@ -46,7 +46,10 @@ module Axlsx attr_reader :series_type #TODO data labels! - #attr_reader :dLabls + def d_lbls + @d_lbls ||= DLbls.new(self.class) + end + # The title object for the chart. # @return [Title] diff --git a/lib/axlsx/drawing/d_lbls.rb b/lib/axlsx/drawing/d_lbls.rb new file mode 100644 index 00000000..f7126a67 --- /dev/null +++ b/lib/axlsx/drawing/d_lbls.rb @@ -0,0 +1,107 @@ +module Axlsx + # There are more elements in the dLbls spec that allow for + # customizations and formatting. For now, I am just implementing the + # basics. + # + #<c:dLbls> + #<c:dLblPos val="outEnd"/> + #<c:showLegendKey val="0"/> + #<c:showVal val="0"/> + #<c:showCatName val="1"/> + #<c:showSerName val="0"/> + #<c:showPercent val="1"/> + #<c:showBubbleSize val="0"/> + #<c:showLeaderLines val="1"/> + #</c:dLbls> + + #The DLbls class manages serialization of data labels + class DLbls + + # These attributes are all boolean so I'm doing a bit of a hand + # waving magic show to set up the attriubte accessors + # @note + # not all charts support all methods! + # Bar3DChart and Line3DChart and ScatterChart do not support d_lbl_pos or show_leader_lines + # + BOOLEAN_ATTRIBUTES = [:show_legend_key, :show_val, :show_cat_name, :show_ser_name, :show_percent, :show_bubble_size, :show_leader_lines] + + # creates a new DLbls object + def initialize(chart_type, options={}) + raise ArgumentError, 'chart_type must inherit from Chart' unless chart_type.superclass == Chart + @chart_type = chart_type + initialize_defaults + options.each do |o| + self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" + end + end + + # Initialize all the values to false as Excel requires them to + # explicitly be disabled or all will show. + def initialize_defaults + BOOLEAN_ATTRIBUTES.each do |attr| + self.send("#{attr}=", false) + end + end + + # The chart type that is using this data lables instance. + # This affects the xml output as not all chart types support the + # same data label attributes. + attr_reader :chart_type + + # The position of the data labels in the chart + # @see d_lbl_pos= for a list of allowed values + # @return [Symbol] + def d_lbl_pos + return unless @chart_type == Pie3DChart + @d_lbl_pos ||= :bestFit + end + + # @see DLbls#d_lbl_pos + # Assigns the label postion for this data labels on this chart. + # Allowed positions are :bestFilt, :b, :ctr, :inBase, :inEnd, :l, + # :outEnd, :r and :t + # The default is :bestFit + # @param [Symbol] label_postion the postion you want to use. + def d_lbl_pos=(label_position) + return unless @chart_type == Pie3DChart + Axlsx::RestrictionValidator.validate 'DLbls#d_lbl_pos', [:bestFit, :b, :ctr, :inBase, :inEnd, :l, :outEnd, :r, :t], label_position + @d_lbl_pos = label_position + end + + # Dynamically create accessors for boolean attriubtes + BOOLEAN_ATTRIBUTES.each do |attr| + class_eval %{ + # The #{attr} attribute reader + # @return [Boolean] + attr_reader :#{attr} + + # The #{attr} writer + # @param [Boolean] value The value to assign to #{attr} + # @return [Boolean] + def #{attr}=(value) + Axlsx::validate_boolean(value) + @#{attr} = value + end + } + end + + + # serializes the data labels + # @return [String] + def to_xml_string(str = '') + validate_attributes_for_chart_type + str << '<c:dLbls>' + instance_values.each { |name, value| str << "<c:#{Axlsx::camel(name, false)} val='#{value}' />" } + str << '</c:dLbls>' + end + + # nills out d_lbl_pos and show_leader_lines as these attributes, while valid in the spec actually chrash excel for any chart type other than pie charts. + def validate_attributes_for_chart_type + return if @chart_type == Pie3DChart + @d_lbl_pos = nil + @show_leader_lines = nil + end + + + end +end diff --git a/lib/axlsx/drawing/drawing.rb b/lib/axlsx/drawing/drawing.rb index c1b41503..48b9320f 100644 --- a/lib/axlsx/drawing/drawing.rb +++ b/lib/axlsx/drawing/drawing.rb @@ -1,5 +1,6 @@ # encoding: UTF-8 module Axlsx + require 'axlsx/drawing/d_lbls.rb' require 'axlsx/drawing/title.rb' require 'axlsx/drawing/series_title.rb' require 'axlsx/drawing/series.rb' diff --git a/lib/axlsx/drawing/line_3D_chart.rb b/lib/axlsx/drawing/line_3D_chart.rb index 5fc3e7c2..b601c02b 100644 --- a/lib/axlsx/drawing/line_3D_chart.rb +++ b/lib/axlsx/drawing/line_3D_chart.rb @@ -71,6 +71,7 @@ module Axlsx super(frame, options) @series_type = LineSeries @view_3D = View3D.new({:perspective=>30}.merge(options)) + @d_lbls = nil end # @see grouping @@ -94,14 +95,7 @@ module Axlsx str_inner << '<c:grouping val="' << grouping.to_s << '"/>' str_inner << '<c:varyColors val="1"/>' @series.each { |ser| ser.to_xml_string(str_inner) } - str_inner << '<c:dLbls>' - str_inner << '<c:showLegendKey val="0"/>' - str_inner << '<c:showVal val="0"/>' - str_inner << '<c:showCatName val="0"/>' - str_inner << '<c:showSerName val="0"/>' - str_inner << '<c:showPercent val="0"/>' - str_inner << '<c:showBubbleSize val="0"/>' - str_inner << '</c:dLbls>' + @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 << '"/>' @@ -112,6 +106,5 @@ module Axlsx @serAxis.to_xml_string str_inner end end - end end diff --git a/lib/axlsx/drawing/pie_3D_chart.rb b/lib/axlsx/drawing/pie_3D_chart.rb index 20e8cd62..059d4148 100644 --- a/lib/axlsx/drawing/pie_3D_chart.rb +++ b/lib/axlsx/drawing/pie_3D_chart.rb @@ -26,6 +26,7 @@ module Axlsx super(frame, options) @series_type = PieSeries @view_3D = View3D.new({:rot_x =>30, :perspective=>30}.merge(options)) + @d_lbls = nil end # Serializes the object @@ -36,6 +37,7 @@ module Axlsx str_inner << '<c:pie3DChart>' str_inner << '<c:varyColors val="1"/>' @series.each { |ser| ser.to_xml_string(str_inner) } + @d_lbls.to_xml_string(str) if @d_lbls str_inner << '</c:pie3DChart>' end end diff --git a/lib/axlsx/drawing/scatter_chart.rb b/lib/axlsx/drawing/scatter_chart.rb index 7b9230d5..b3cd8c29 100644 --- a/lib/axlsx/drawing/scatter_chart.rb +++ b/lib/axlsx/drawing/scatter_chart.rb @@ -29,6 +29,7 @@ module Axlsx @yValAxis = ValAxis.new(@yValAxId, @xValAxId) super(frame, options) @series_type = ScatterSeries + @d_lbls = nil options.each do |o| self.send("#{o[0]}=", o[1]) if self.respond_to? "#{o[0]}=" end @@ -49,6 +50,9 @@ module Axlsx str_inner << '<c:scatterStyle val="' << scatterStyle.to_s << '"/>' str_inner << '<c:varyColors val="1"/>' @series.each { |ser| ser.to_xml_string(str_inner) } + if @d_lbls + @d_lbls.to_xml_string(str) + end str_inner << '<c:dLbls>' str_inner << '<c:showLegendKey val="0"/>' str_inner << '<c:showVal val="0"/>' diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index bc933f30..abb6d48b 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -488,7 +488,7 @@ module Axlsx r = Relationships.new r + [tables.relationships, worksheet_comments.relationships, - worksheet_drawing.relationship].flatten.compact + worksheet_drawing.relationship].flatten.compact || [] r end @@ -514,6 +514,20 @@ module Axlsx r.cells[col_index] if r end + # shortcut method to access styles direclty from the worksheet + # This lets us do stuff like: + # @example + # p = Axlsx::Package.new + # p.workbook.add_worksheet(:name => 'foo') do |sheet| + # my_style = sheet.styles.add_style { :bg_color => "FF0000" } + # sheet.add_row ['Oh No!'], :styles => my_style + # end + # p.serialize 'foo.xlsx' + def styles + @styles ||= self.workbook.styles + end + + private def validate_sheet_name(name) @@ -596,10 +610,6 @@ module Axlsx def workbook=(v) DataTypeValidator.validate "Worksheet.workbook", Workbook, v; @workbook = v; end - def styles - @styles ||= self.workbook.styles - end - def update_column_info(cells, widths=[]) cells.each_with_index do |cell, index| col = find_or_create_column_info(index) diff --git a/rubima.md b/rubima.md new file mode 100644 index 00000000..c28850fe --- /dev/null +++ b/rubima.md @@ -0,0 +1,74 @@ +Axlsx – The end of CSV as an excuse for client reporting. + +One of the things that keeps popping up for ruby and ruby on rails application developers is client reporting. +We have tools for creating beautiful screen based reports that make the end user say “WOW”, we can even - +with the help of a great designer - produce a well formatted PDF. But when it comes to data, we are still +using CSV, a technology from the 1960's + +That has changed. There is a new gem, released in November of last year and still under active development +that brings more to ruby and ruby on rails than was ever possible before for client reporting. + +The gem is axlsx (http://rubygems.org/gems/axlsx) + +Let's take a look at some of the things that it can do for you. + +1. The Basics +The basics of generating and serializing xlsx data are dead simple. + +1. Create a package +2. Setup your styles +3. add a worksheet to the workbook +3. add your data, charts, images, conditional formatting, data + validations, page setup, print options, and password locking, +comments, cell merges and panes. +4. serialize + +The code below illustrates a trivial example a few of these items + +'''ruby +require 'axlsx' + +package = Axlsx::Package.new +styles = package.workbook.styles + +header = styles.add_style :bg_color => '00', :fg_color => 'FF', :sz => 16, :alignment => { :horizontal => :center } + +quarter_label = styles.add_style :bg_color => 'FFDFDEDF', :alignment => { :indent => 1 }, :sz => 14 + +money = styles.add_style :num_fmt => 5, :zd => 14 + +package.workbook.add_worksheet do |worksheet| + worksheet.add_row ['Revenue by Quarter'], :style => header + worksheet.merge_cells 'A1:I1' + worksheet.add_row + data_row_style = [nil, quarter_label, money] + + worksheet.add_row [nil, 'Q1', 35221124], :style => data_row_style + worksheet.add_row [nil, 'Q2', 56742113], :style => data_row_style + worksheet.add_row [nil, 'Q3', 71165443], :style => data_row_style + worksheet.add_row [nil, 'Q4', 98761111], :style => data_row_style + + worksheet.add_chart(Axlsx::Bar3DChart, :bar_dir => :col) do |chart| + chart.start_at 4, 2 + chart.end_at 9, 15 + chart.title = worksheet['A1'] + chart.add_series :data => worksheet['C3:C6'], :labels => worksheet['B3:B6'] + end +end +package.serialize 'the_better_basics.xlsx' + +If you are using rails, there is a sister gem acts_as_xlsx (http://rubygems.org/gems/axlsx) +That is going to make you smile as well. + +Have a look at these two blog posts for a quick write up of how easy it +is to use. + +http://axlsx.blogspot.jp/2011/12/using-actsasxlsx-to-generate-excel-data.html +http://axlsx.blogspot.jp/2011/12/axlsx-making-excel-reports-with-ruby-on.html + + + + + + + diff --git a/test/drawing/tc_chart.rb b/test/drawing/tc_chart.rb index e6ead247..8dbebabc 100644 --- a/test/drawing/tc_chart.rb +++ b/test/drawing/tc_chart.rb @@ -73,7 +73,13 @@ class TestChart < Test::Unit::TestCase def test_pn assert_equal(@chart.pn, "charts/chart1.xml") end - + + def test_d_lbls + assert_equal(nil, @chart.instance_values[:d_lbls]) + @chart.d_lbls.d_lbl_pos = :t + assert(@chart.d_lbls.is_a?(Axlsx::DLbls), 'DLbls instantiated on access') + end + def test_to_xml_string schema = Nokogiri::XML::Schema(File.open(Axlsx::DRAWING_XSD)) doc = Nokogiri::XML(@chart.to_xml_string) diff --git a/test/drawing/tc_d_lbls.rb b/test/drawing/tc_d_lbls.rb new file mode 100644 index 00000000..5bd52a92 --- /dev/null +++ b/test/drawing/tc_d_lbls.rb @@ -0,0 +1,47 @@ +require 'tc_helper' + +class TestDLbls < Test::Unit::TestCase + + def setup + @d_lbls = Axlsx::DLbls.new(Axlsx::Pie3DChart) + end + + def test_initialization + assert_equal(:bestFit, @d_lbls.d_lbl_pos) + Axlsx::DLbls::BOOLEAN_ATTRIBUTES.each do |attr| + assert_equal(false, @d_lbls.send(attr)) + end + end + + def test_initialization_with_optoins + options_hash = Hash[*[Axlsx::DLbls::BOOLEAN_ATTRIBUTES.map { |name| [name, true] }] ] + d_lbls = Axlsx::DLbls.new(Axlsx::Pie3DChart, options_hash.merge( { :d_lbl_pos => :t })) + Axlsx::DLbls::BOOLEAN_ATTRIBUTES.each do |attr| + assert_equal(true, d_lbls.send(attr), "boolean attributes set by options") + end + assert_equal(:t, d_lbls.d_lbl_pos, "d_lbl_pos set by options") + end + def test_d_lbl_pos + assert_raise(ArgumentError, 'invlaid label positions are rejected') { @d_lbls.d_lbl_pos = :upside_down } + assert_nothing_raised('accepts valid label position') { @d_lbls.d_lbl_pos = :ctr } + end + + def test_boolean_attributes + Axlsx::DLbls::BOOLEAN_ATTRIBUTES.each do |attr| + assert_raise(ArgumentError, "rejects non boolean value for #{attr}") { @d_lbls.send("#{attr}=", :foo) } + assert_nothing_raised("accepts boolean value for #{attr}") { @d_lbls.send("#{attr}=", true) } + assert_nothing_raised("accepts boolean value for #{attr}") { @d_lbls.send("#{attr}=", false) } + end + end + + def test_to_xml_string + str = '<?xml version="1.0" encoding="UTF-8"?>' + str << '<c:chartSpace xmlns:c="' << Axlsx::XML_NS_C << '" xmlns:a="' << Axlsx::XML_NS_A << '" xmlns:r="' << Axlsx::XML_NS_R << '">' + @d_lbls.to_xml_string(str) + str << '</c:chartSpace>' + doc = Nokogiri::XML(str) + @d_lbls.instance_values.each do |name, value| + assert(doc.xpath("//c:#{Axlsx::camel(name, false)}[@val='#{value}']"), "#{name} is properly serialized") + end + end +end diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb index 3bb9908f..8147e18e 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -308,19 +308,16 @@ class TestWorksheet < Test::Unit::TestCase assert_equal(doc.xpath('//xmlns:worksheet/xmlns:tableParts/xmlns:tablePart[@r:id="rId1"]').size, 1) end - def test_auto_filter - @ws.add_row [1, "two", 3] - @ws.auto_filter = "A1:C1" - assert_equal("A1:C1", @ws.auto_filter.range) - assert_equal(@ws.auto_filter.defined_name, "'Sheet1'!$A$1:$C$1") - end - def test_to_xml_string schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD)) doc = Nokogiri::XML(@ws.to_xml_string) assert(schema.validate(doc).map{ |e| puts e.message; e }.empty?, "error free validation") end + def test_styles + assert(@ws.styles.is_a?(Axlsx::Styles), 'worksheet provides access to styles') + end + def test_to_xml_string_with_illegal_chars nasties = "\v\u2028\u0001\u0002\u0003\u0004\u0005\u0006\u0007\u0008\u001f" @ws.add_row [nasties] |