diff options
65 files changed, 1398 insertions, 969 deletions
diff --git a/CHANGELOG.md b/CHANGELOG.md index 7f8a3571..a1e392ff 100644 --- a/CHANGELOG.md +++ b/CHANGELOG.md @@ -1,5 +1,19 @@ CHANGELOG --------- + +- **September.30.12**: 1.3.1 + - Improved control character handling + - Added stored auto filter values and date grouping items + - Improved support for autowidth when custom styles are applied + - Added support for table style info that lets you take advantage of + all the predefined table styles. + - Improved style management for fonts so they merge undefined values + from the initial master. +- **September.8.12**: 1.2.3 + - enhance exponential float/bigdecimal values rendering as strings intead + of 'numbers' in excel. + - added support for :none option on chart axis labels + - added support for paper_size option on worksheet.page_setup - **August.27.12**: 1.2.2 - minor patch for auto-filters - minor documentation improvements. @@ -15,13 +15,13 @@ appreciation for the gem, please don't hesitate to make a donation. **Google Group**: [https://groups.google.com/forum/?fromgroups#!forum/axlsx](https://groups.google.com/forum/?fromgroups#!forum/axlsx) -**Author**: Randy Morgan +**Author**: Randy Morgan -**Copyright**: 2011 - 2012 +**Copyright**: 2011 - 2012 **License**: MIT License -**Latest Version**: 1.3.1 +**Latest Version**: 1.3.4 **Ruby Version**: 1.8.7, 1.9.2, 1.9.3 @@ -29,7 +29,7 @@ appreciation for the gem, please don't hesitate to make a donation. **Rubinius Version**: rubinius 2.0.0dev * lower versions may run, this gem always tests against head. -**Release Date**: September 30th 2012 +**Release Date**: November ??th 2012 If you are working in rails, or with active record see: * http://github.com/randym/acts_as_xlsx @@ -101,6 +101,9 @@ and Numbers **19. Data labels for charts as well as series color customization. +**20. Support for sheet headers and footers + + Installing ---------- @@ -149,22 +152,21 @@ This gem has 100% test coverage using test/unit. To execute tests for this gem, #Change log --------- -- **October.??.12**: 1.3.2 - - Patched to handle sheet names with apostrophes - - refactored string and boolean attribute accessors -- **September.30.12**: 1.3.1 - - Improved control character handling - - Added stored auto filter values and date grouping items - - Improved support for autowidth when custom styles are applied - - Added support for table style info that lets you take advantage of - all the predefined table styles. - - Improved style management for fonts so they merge undefined values - from the initial master. -- **September.8.12**: 1.2.3 - - enhance exponential float/bigdecimal values rendering as strings intead - of 'numbers' in excel. - - added support for :none option on chart axis labels - - added support for paper_size option on worksheet.page_setup +- **November.25.12**:1.3.4 + - Support for headers and footers for worksheets + - bug fix: Properly escape hyperlink urls + - Improvements in color_scale generation for conditional formatting + - 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. @@ -173,7 +175,7 @@ As axslx implements the Office Open XML (ECMA-376 spec) much of the functionality is interoperable with other spreadsheet software. Below is a listing of some known issues. -1. Libra Office +1. Libre Office - You must specify colors for your series. see examples/chart_colors.rb for an example. - You must use data in your sheet for charts. You cannot use hard coded @@ -238,6 +240,9 @@ done without the help of the people below. [ebenoist](https://github.com/ebenoist) - For taking control of control characters and keeping what is between the lines, between the lines. +[adammathys](https://github.com/adammathys) - Fgr getting our head in the +air and our feet on the ground. + #Copyright and License ---------- @@ -11,7 +11,7 @@ end task :gendoc do #puts 'yard doc generation disabled until JRuby build native extensions for redcarpet or yard removes the dependency.' system "yardoc" - #system "yard stats --list-undoc" + system "yard stats --list-undoc" end task :test do diff --git a/axlsx.gemspec b/axlsx.gemspec index 81a3ce0a..be46cb6b 100644 --- a/axlsx.gemspec +++ b/axlsx.gemspec @@ -1,5 +1,3 @@ -# This will recursively require rake when using rake 0.9.2.2 and bundler -#require 'rake' require File.expand_path('../lib/axlsx/version', __FILE__) Gem::Specification.new do |s| @@ -15,7 +13,7 @@ Gem::Specification.new do |s| s.description = <<-eof xlsx generation with charts, images, automated column width, customizable styles and full schema validation. Axlsx excels at helping you generate beautiful Office Open XML Spreadsheet documents without having to understand the entire ECMA specification. Check out the README for some examples of how easy it is. Best of all, you can validate your xlsx file before serialization so you know for sure that anything generated is going to load on your client's machine. eof - s.files = Dir.glob("{lib/**/*,examples/**/*}") + %w{ LICENSE README.md Rakefile CHANGELOG.md .yardopts } + s.files = Dir.glob("{lib/**/*,examples/**/*.rb,examples/**/*.jpeg}") + %w{ LICENSE README.md Rakefile CHANGELOG.md .yardopts } s.test_files = Dir.glob("{test/**/*}") s.add_runtime_dependency 'nokogiri', '>= 1.4.1' @@ -25,7 +23,7 @@ Gem::Specification.new do |s| # 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 'cover_me' unless RUBY_VERSION == '1.8.7' + s.add_development_dependency 'simplecov' s.required_ruby_version = '>= 1.8.7' s.require_path = 'lib' end diff --git a/examples/example.rb b/examples/example.rb index 596da6b2..a7eace2a 100755 --- a/examples/example.rb +++ b/examples/example.rb @@ -1,20 +1,61 @@ #!/usr/bin/env ruby -w -s # -*- coding: utf-8 -*- -# $LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" +$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" #```ruby require 'axlsx' +examples = [] +examples << :basic +examples << :custom_styles +examples << :wrap_text +examples << :cell_style_override +examples << :custom_borders +examples << :surrounding_border +examples << :deep_custom_borders +examples << :row_column_style +examples << :fixed_column_width +examples << :merge_cells +examples << :images +examples << :format_dates +examples << :mbcs +examples << :formula +examples << :auto_filter +examples << :data_types +examples << :hyperlinks +examples << :number_currency_format +examples << :venezuela_currency +examples << :bar_chart +examples << :chart_gridlines +examples << :pie_chart +examples << :line_chart +examples << :scatter_chart +examples << :tables +examples << :fit_to_page +examples << :hide_gridlines +examples << :repeated_header +examples << :defined_name +examples << :printing +examples << :header_footer +examples << :comments +examples << :panes +examples << :sheet_view +examples << :conditional_formatting +examples << :streaming +examples << :shared_strings +examples << :no_autowidth p = Axlsx::Package.new wb = p.workbook #``` -#A Simple Workbook +## A Simple Workbook #```ruby -wb.add_worksheet(:name => "Basic Worksheet") do |sheet| - sheet.add_row ["First Column", "Second", "Third"] - sheet.add_row [1, 2, 3] +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] + end end #``` @@ -24,36 +65,64 @@ end # Each cell allows a single, predified style. # When using add_row, the value in the :style array at the same index as the cell's column will be applied to that cell. # Alternatively, you can apply a style to an entire row by using an integer value for :style. +if examples.include? :custom_styles + wb.styles do |s| + black_cell = s.add_style :bg_color => "00", :fg_color => "FF", :sz => 14, :alignment => { :horizontal=> :center } + blue_cell = s.add_style :bg_color => "0000FF", :fg_color => "FF", :sz => 20, :alignment => { :horizontal=> :center } + wb.add_worksheet(:name => "Custom Styles") do |sheet| + + # Applies the black_cell style to the first and third cell, and the blue_cell style to the second. + sheet.add_row ["Text Autowidth", "Second", "Third"], :style => [black_cell, blue_cell, black_cell] -wb.styles do |s| - black_cell = s.add_style :bg_color => "00", :fg_color => "FF", :sz => 14, :alignment => { :horizontal=> :center } - blue_cell = s.add_style :bg_color => "0000FF", :fg_color => "FF", :sz => 20, :alignment => { :horizontal=> :center } - wb.add_worksheet(:name => "Custom Styles") do |sheet| + # Applies the thin border to all three cells + sheet.add_row [1, 2, 3], :style => Axlsx::STYLE_THIN_BORDER + end + end +end - # Applies the black_cell style to the first and third cell, and the blue_cell style to the second. - sheet.add_row ["Text Autowidth", "Second", "Third"], :style => [black_cell, blue_cell, black_cell] - # Applies the thin border to all three cells - sheet.add_row [1, 2, 3], :style => Axlsx::STYLE_THIN_BORDER +#```ruby +# A simple example of wrapping text. Seems this may not be working in Libre Office so here is an example for me to play with. +if examples.include? :wrap_text + wb.styles do |s| + wrap_text = s.add_style :fg_color=> "FFFFFF", + :b => true, + :bg_color => "004586", + :sz => 12, + :border => { :style => :thin, :color => "00" }, + :alignment => { :horizontal => :center, + :vertical => :center , + :wrap_text => true} + wb.add_worksheet(:name => 'wrap text') do |sheet| + sheet.add_row ['Torp, White and Cronin'], :style=>wrap_text + sheet.column_info.first.width = 5 + end end end -#``` ##Styling Cell Overrides #```ruby #Some of the style attributes can also be set at the cell level. Cell level styles take precedence over Custom Styles shown in the previous example. - -wb.add_worksheet(:name => "Cell Level Style Overrides") do |sheet| - - # this will set the font size for each cell. - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'], :sz => 16 - - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] - - # You can also apply cell style overrides to a range of cells - sheet["A1:D1"].each { |c| c.color = "FF0000" } - sheet['A1:D2'].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } +if examples.include? :cell_style_override + wb.add_worksheet(:name => "Cell Level Style Overrides") do |sheet| + + # this will set the font size for each cell. + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'], :sz => 16 + + sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] + sheet.add_row %w(u shadow sz b i strike outline) + sheet.rows.last.cells[0].u = :double + sheet.rows.last.cells[1].shadow = true + sheet.rows.last.cells[2].sz = 20 + sheet.rows.last.cells[3].b = true + sheet.rows.last.cells[4].i = true + sheet.rows.last.cells[5].strike = true + sheet.rows.last.cells[6].outline = 1 + # You can also apply cell style overrides to a range of cells + sheet["A1:D1"].each { |c| c.color = "FF0000" } + sheet['A1:D2'].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } + end end ##``` @@ -61,65 +130,98 @@ end #```ruby #Axlsx defines a thin border style, but you can easily create and use your own. -wb.styles do |s| - red_border = s.add_style :border => { :style => :thick, :color =>"FFFF0000", :edges => [:left, :right] } - blue_border = s.add_style :border => { :style => :thick, :color =>"FF0000FF"} +if examples.include? :custom_borders + wb.styles do |s| + red_border = s.add_style :border => { :style => :thick, :color =>"FFFF0000", :edges => [:left, :right] } + blue_border = s.add_style :border => { :style => :thick, :color =>"FF0000FF"} + + wb.add_worksheet(:name => "Custom Borders") do |sheet| + sheet.add_row ["wrap", "me", "Up in Red"], :style => red_border + sheet.add_row [1, 2, 3], :style => blue_border + end + end +end + +#```ruby +# More Custom Borders +if examples.include? :surrounding_border + + # Stuff like this is why I LOVE RUBY + # If you dont know about hash default values + # LEARN IT! LIVE IT! LOVE IT! + defaults = { :style => :thick, :color => "000000" } + borders = Hash.new do |hash, key| + hash[key] = wb.styles.add_style :border => defaults.merge( { :edges => key.to_s.split('_').map(&:to_sym) } ) + end + top_row = [0, borders[:top_left], borders[:top], borders[:top], borders[:top_right]] + middle_row = [0, borders[:left], nil, nil, borders[:right]] + bottom_row = [0, borders[:bottom_left], borders[:bottom], borders[:bottom], borders[:bottom_right]] + + wb.add_worksheet(:name => "Surrounding Border") do |ws| + ws.add_row [] + ws.add_row ['', 1,2,3,4], :style => top_row + ws.add_row ['', 5,6,7,8], :style => middle_row + ws.add_row ['', 9, 10, 11, 12] + + #This works too! + ws.rows.last.style = bottom_row - wb.add_worksheet(:name => "Custom Borders") do |sheet| - sheet.add_row ["wrap", "me", "Up in Red"], :style => red_border - sheet.add_row [1, 2, 3], :style => blue_border end end -#```ruby +#```ruby # Hacking border styles - wb.styles do |s| - top_bottom = s.add_style :border => { :style => :thick, :color =>"FFFF0000", :edges => [:top, :bottom] } - border = s.borders[s.cellXfs[top_bottom].borderId] - # edit existing border parts - border.prs.each do |part| - case part.name - when :top - part.color = Axlsx::Color.new(:rgb => "FFFF0000") - when :bottom - part.color = Axlsx::Color.new(:rgb => "FF00FF00") - end +if examples.include? :deep_custom_borders + wb.styles do |s| + top_bottom = s.add_style :border => { :style => :thick, :color =>"FFFF0000", :edges => [:top, :bottom] } + border = s.borders[s.cellXfs[top_bottom].borderId] + # edit existing border parts + border.prs.each do |part| + case part.name + when :top + part.color = Axlsx::Color.new(:rgb => "FFFF0000") + when :bottom + part.color = Axlsx::Color.new(:rgb => "FF00FF00") end + end - border.prs << Axlsx::BorderPr.new(:name => :left, :color => Axlsx::Color.new(:rgb => '0000FF'), :style => :mediumDashed) - wb.add_worksheet(:name => 'hacked borders') do |sheet| - sheet.add_row [1,2,3], :style=>top_bottom - end + border.prs << Axlsx::BorderPr.new(:name => :left, :color => Axlsx::Color.new(:rgb => '0000FF'), :style => :mediumDashed) + wb.add_worksheet(:name => 'hacked borders') do |sheet| + sheet.add_row [1,2,3], :style=>top_bottom end + end +end ##``` ##Styling Rows and Columns #```ruby -wb.styles do |s| - head = s.add_style :bg_color => "00", :fg_color => "FF" - percent = s.add_style :num_fmt => 9 - wb.add_worksheet(:name => "Columns and Rows") do |sheet| - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4', 'col5'] - sheet.add_row [1, 2, 0.3, 4, 5.0] - sheet.add_row [1, 2, 0.2, 4, 5.0] - sheet.add_row [1, 2, 0.1, 4, 5.0] - - #apply the percent style to the column at index 2 skipping the first row. - sheet.col_style 2, percent, :row_offset => 1 - - # apply the head style to the first row. - sheet.row_style 0, head - - #Hide the 5th column - sheet.column_info[4].hidden = true - - #Set the second column outline level - sheet.column_info[1].outlineLevel = 2 - - sheet.rows[3].hidden = true - sheet.rows[1].outlineLevel = 2 +if examples.include? :row_column_style + wb.styles do |s| + head = s.add_style :bg_color => "00", :fg_color => "FF" + percent = s.add_style :num_fmt => 9 + wb.add_worksheet(:name => "Columns and Rows") do |sheet| + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4', 'col5'] + sheet.add_row [1, 2, 0.3, 4, 5.0] + sheet.add_row [1, 2, 0.2, 4, 5.0] + sheet.add_row [1, 2, 0.1, 4, 5.0] + + #apply the percent style to the column at index 2 skipping the first row. + sheet.col_style 2, percent, :row_offset => 1 + + # apply the head style to the first row. + sheet.row_style 0, head + + #Hide the 5th column + sheet.column_info[4].hidden = true + + #Set the second column outline level + sheet.column_info[1].outlineLevel = 2 + + sheet.rows[3].hidden = true + sheet.rows[1].outlineLevel = 2 + end end end ##``` @@ -128,40 +230,46 @@ end ##Specifying Column Widths #```ruby -wb.add_worksheet(:name => "custom column widths") do |sheet| - sheet.add_row ["I use autowidth and am very wide", "I use a custom width and am narrow"] - sheet.add_row ['abcdefg', 'This is a very long text and should flow into the right cell', nil, 'xxx' ] - sheet.column_widths nil, 3, 5, nil +if examples.include? :fixed_column_width + wb.add_worksheet(:name => "custom column widths") do |sheet| + sheet.add_row ["I use autowidth and am very wide", "I use a custom width and am narrow"] + sheet.add_row ['abcdefg', 'This is a very long text and should flow into the right cell', nil, 'xxx' ] + sheet.column_widths nil, 3, 5, nil + end end ##``` ##Merging Cells. #```ruby -wb.add_worksheet(:name => 'Merging Cells') do |sheet| - # cell level style overides when adding cells - sheet.add_row ["col 1", "col 2", "col 3", "col 4"], :sz => 16 - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] - sheet.add_row [2, 3, 4, "=SUM(A3:C3)"] - sheet.add_row ["total", "", "", "=SUM(D2:D3)"] - sheet.merge_cells("A4:C4") - sheet["A1:D1"].each { |c| c.color = "FF0000"} - sheet["A1:D4"].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } +if examples.include? :merge_cells + wb.add_worksheet(:name => 'Merging Cells') do |sheet| + # cell level style overides when adding cells + sheet.add_row ["col 1", "col 2", "col 3", "col 4"], :sz => 16 + sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] + sheet.add_row [2, 3, 4, "=SUM(A3:C3)"] + sheet.add_row ["total", "", "", "=SUM(D2:D3)"] + sheet.merge_cells("A4:C4") + sheet["A1:D1"].each { |c| c.color = "FF0000"} + sheet["A1:D4"].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } + end end ##``` ##Add an Image with a hyperlink #```ruby -wb.add_worksheet(:name => "Image with Hyperlink") do |sheet| - 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| - image.width=720 - image.height=666 - image.hyperlink.tooltip = "Labeled Link" - image.start_at 2, 2 +if examples.include? :images + wb.add_worksheet(:name => "Image with Hyperlink") do |sheet| + 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| + image.width=720 + image.height=666 + image.hyperlink.tooltip = "Labeled Link" + image.start_at 2, 2 + end end end #``` @@ -169,15 +277,17 @@ end ##Using Custom Formatting and date1904 #```ruby -require 'date' -wb.styles do |s| - date = s.add_style(:format_code => "yyyy-mm-dd", :border => Axlsx::STYLE_THIN_BORDER) - padded = s.add_style(:format_code => "00#", :border => Axlsx::STYLE_THIN_BORDER) - percent = s.add_style(:format_code => "0000%", :border => Axlsx::STYLE_THIN_BORDER) - # wb.date1904 = true # Use the 1904 date system (Used by Excel for Mac < 2011) - wb.add_worksheet(:name => "Formatting Data") do |sheet| - sheet.add_row ["Custom Formatted Date", "Percent Formatted Float", "Padded Numbers"], :style => Axlsx::STYLE_THIN_BORDER - sheet.add_row [Date::strptime('2012-01-19','%Y-%m-%d'), 0.2, 32], :style => [date, percent, padded] +if examples.include? :format_dates + require 'date' + wb.styles do |s| + date = s.add_style(:format_code => "yyyy-mm-dd", :border => Axlsx::STYLE_THIN_BORDER) + padded = s.add_style(:format_code => "00#", :border => Axlsx::STYLE_THIN_BORDER) + percent = s.add_style(:format_code => "0000%", :border => Axlsx::STYLE_THIN_BORDER) + # wb.date1904 = true # Use the 1904 date system (Used by Excel for Mac < 2011) + wb.add_worksheet(:name => "Formatting Data") do |sheet| + sheet.add_row ["Custom Formatted Date", "Percent Formatted Float", "Padded Numbers"], :style => Axlsx::STYLE_THIN_BORDER + sheet.add_row [Date::strptime('2012-01-19','%Y-%m-%d'), 0.2, 32], :style => [date, percent, padded] + end end end #``` @@ -185,33 +295,39 @@ end ##Asian Language Support #```ruby -wb.add_worksheet(:name => "日本語でのシート名") do |sheet| - sheet.add_row ["日本語"] - sheet.add_row ["华语/華語"] - sheet.add_row ["한국어/조선말"] +if examples.include? :mbcs + wb.add_worksheet(:name => "日本語でのシート名") do |sheet| + sheet.add_row ["日本語"] + sheet.add_row ["华语/華語"] + sheet.add_row ["한국어/조선말"] + end end ##``` ##Using formula #```ruby -wb.add_worksheet(:name => "Using Formulas") do |sheet| - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] +if examples.include? :formula + wb.add_worksheet(:name => "Using Formulas") do |sheet| + sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] + sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] + end end ##``` ##Auto Filter #```ruby -wb.add_worksheet(:name => "Auto Filter") do |sheet| - sheet.add_row ["Build Matrix"] - sheet.add_row ["Build", "Duration", "Finished", "Rvm"] - sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] - sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] - sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] - sheet.auto_filter = "A2:D5" - sheet.auto_filter.add_column 3, :filters, :filter_items => ['1.9.2', '1.8.7'] +if examples.include? :auto_filter + wb.add_worksheet(:name => "Auto Filter") do |sheet| + sheet.add_row ["Build Matrix"] + sheet.add_row ["Build", "Duration", "Finished", "Rvm"] + sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] + sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] + sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] + sheet.auto_filter = "A2:D5" + sheet.auto_filter.add_column 3, :filters, :filter_items => ['1.9.2', '1.8.7'] + end end #``` @@ -219,61 +335,79 @@ end #```ruby -wb.add_worksheet(:name => "Automatic cell types") do |sheet| - date_format = wb.styles.add_style :format_code => 'YYYY-MM-DD' - time_format = wb.styles.add_style :format_code => 'hh:mm:ss' - sheet.add_row ["Date", "Time", "String", "Boolean", "Float", "Integer"] - sheet.add_row [Date.today, Time.now, "value", true, 0.1, 1], :style => [date_format, time_format] +if examples.include? :data_types + wb.add_worksheet(:name => "Automatic cell types") do |sheet| + date_format = wb.styles.add_style :format_code => 'YYYY-MM-DD' + time_format = wb.styles.add_style :format_code => 'hh:mm:ss' + sheet.add_row ["Date", "Time", "String", "Boolean", "Float", "Integer"] + sheet.add_row [Date.today, Time.now, "value", true, 0.1, 1], :style => [date_format, time_format] + end end - # Hyperlinks in worksheet -wb.add_worksheet(:name => 'hyperlinks') do |sheet| - # external references - sheet.add_row ['axlsx'] - sheet.add_hyperlink :location => 'https://github.com/randym/axlsx', :ref => sheet.rows.first.cells.first - # internal references - sheet.add_hyperlink :location => "'Next Sheet'!A1", :ref => 'A2', :target => :sheet - sheet.add_row ['next sheet'] -end +if examples.include? :hyperlinks + wb.add_worksheet(:name => 'hyperlinks') do |sheet| + # external references + sheet.add_row ['axlsx'] + sheet.add_hyperlink :location => 'https://github.com/randym/axlsx', :ref => sheet.rows.first.cells.first + # internal references + sheet.add_hyperlink :location => "'Next Sheet'!A1", :ref => 'A2', :target => :sheet + sheet.add_row ['next sheet'] + end -wb.add_worksheet(:name => 'Next Sheet') do |sheet| - sheet.add_row ['hello!'] + wb.add_worksheet(:name => 'Next Sheet') do |sheet| + sheet.add_row ['hello!'] + end end ###``` ##Number formatting and currency -wb.add_worksheet(:name => "Formats and Currency") do |sheet| - currency = wb.styles.add_style :num_fmt => 5 - red_negative = wb.styles.add_style :num_fmt => 8 - comma = wb.styles.add_style :num_fmt => 3 - super_funk = wb.styles.add_style :format_code => '[Green]#' - sheet.add_row %w(Currency RedNegative Comma Custom) - sheet.add_row [1500, -122.34, 123456789, 594829], :style=> [currency, red_negative, comma, super_funk] +if examples.include? :number_currency_format + wb.add_worksheet(:name => "Formats and Currency") do |sheet| + currency = wb.styles.add_style :num_fmt => 5 + red_negative = wb.styles.add_style :num_fmt => 8 + comma = wb.styles.add_style :num_fmt => 3 + super_funk = wb.styles.add_style :format_code => '[Green]#' + sheet.add_row %w(Currency RedNegative Comma Custom) + sheet.add_row [1500, -122.34, 123456789, 594829], :style=> [currency, red_negative, comma, super_funk] + end +end + +## Venezuala currency +if examples.include? :venezuela_currency + wb.add_worksheet(:name => 'Venezuala_currency') do |sheet| + number = wb.styles.add_style :format_code => '#.##0\,00' + sheet.add_row [2.5] , :style => [number] + end end ##Generating A Bar Chart #```ruby -wb.add_worksheet(:name => "Bar Chart") do |sheet| - 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"] +if examples.include? :bar_chart + wb.add_worksheet(:name => "Bar Chart") do |sheet| + 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"] + end end end + ##``` ##Hide Gridlines in chart #```ruby -wb.add_worksheet(:name => "Chart With No Gridlines") do |sheet| - 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.valAxis.gridlines = false - chart.catAxis.gridlines = false +if examples.include? :chart_gridlines + wb.add_worksheet(:name => "Chart With No Gridlines") do |sheet| + 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.valAxis.gridlines = false + chart.catAxis.gridlines = false + end end end #``` @@ -281,11 +415,13 @@ end ##Generating A Pie Chart #```ruby -wb.add_worksheet(:name => "Pie Chart") do |sheet| - sheet.add_row ["Simple Pie Chart"] - %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] } - sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,5], :end_at => [10, 20], :title => "example 3: Pie Chart") do |chart| - chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"], :colors => ['FF0000', '00FF00', '0000FF'] +if examples.include? :pie_chart + wb.add_worksheet(:name => "Pie Chart") do |sheet| + sheet.add_row ["Simple Pie Chart"] + %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] } + sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,5], :end_at => [10, 20], :title => "example 3: Pie Chart") do |chart| + chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"], :colors => ['FF0000', '00FF00', '0000FF'] + end end end #``` @@ -293,19 +429,21 @@ end ##Generating A Line Chart #```ruby -wb.add_worksheet(:name => "Line Chart") do |sheet| - sheet.add_row ["Simple Line Chart"] - sheet.add_row %w(first second) - 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| - 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.catAxis.title = 'X Axis' - chart.valAxis.title = 'Y Axis' +if examples.include? :line_chart + wb.add_worksheet(:name => "Line Chart") do |sheet| + sheet.add_row ["Simple Line Chart"] + sheet.add_row %w(first second) + 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| + 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.catAxis.title = 'X Axis' + chart.valAxis.title = 'Y Axis' + end end end #``` @@ -313,16 +451,18 @@ end ##Generating A Scatter Chart #```ruby -wb.add_worksheet(:name => "Scatter Chart") do |sheet| - sheet.add_row ["First", 1, 5, 7, 9] - sheet.add_row ["", 1, 25, 49, 81] - sheet.add_row ["Second", 5, 2, 14, 9] - sheet.add_row ["", 5, 10, 15, 20] - 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"] +if examples.include? :scatter_chart + wb.add_worksheet(:name => "Scatter Chart") do |sheet| + sheet.add_row ["First", 1, 5, 7, 9] + sheet.add_row ["", 1, 25, 49, 81] + sheet.add_row ["Second", 5, 2, 14, 9] + sheet.add_row ["", 5, 10, 15, 20] + 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"] + end end end #``` @@ -331,13 +471,15 @@ end ##Tables #```ruby -wb.add_worksheet(:name => "Table") do |sheet| - sheet.add_row ["Build Matrix"] - sheet.add_row ["Build", "Duration", "Finished", "Rvm"] - sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] - sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] - sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] - sheet.add_table "A2:D5", :name => 'Build Matrix', :style_info => { :name => "TableStyleMedium23" } +if examples.include? :tables + wb.add_worksheet(:name => "Table") do |sheet| + sheet.add_row ["Build Matrix"] + sheet.add_row ["Build", "Duration", "Finished", "Rvm"] + sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] + sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] + sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] + sheet.add_table "A2:D5", :name => 'Build Matrix', :style_info => { :name => "TableStyleMedium23" } + end end #``` @@ -345,9 +487,11 @@ end ##Fit to page printing #```ruby -wb.add_worksheet(:name => "fit to page") do |sheet| - sheet.add_row ['this all goes on one page'] - sheet.fit_to_page = true +if examples.include? :fit_to_page + wb.add_worksheet(:name => "fit to page") do |sheet| + sheet.add_row ['this all goes on one page'] + sheet.fit_to_page = true + end end ##``` @@ -355,159 +499,227 @@ end ##Hide Gridlines in worksheet #```ruby -wb.add_worksheet(:name => "No Gridlines") do |sheet| - sheet.add_row ["This", "Sheet", "Hides", "Gridlines"] - sheet.show_gridlines = false +if examples.include? :hide_gridlines + wb.add_worksheet(:name => "No Gridlines") do |sheet| + sheet.add_row ["This", "Sheet", "Hides", "Gridlines"] + sheet.show_gridlines = false + end end ##``` +# Repeat printing of header rows. #```ruby -wb.add_worksheet(:name => "repeated header") do |sheet| - sheet.add_row %w(These Column Header Will Render On Every Printed Sheet) - 200.times { sheet.add_row %w(1 2 3 4 5 6 7 8) } - wb.add_defined_name("'repeated header'!$1:$1", :local_sheet_id => sheet.index, :name => '_xlnm.Print_Titles') +if examples.include? :repeated_header + wb.add_worksheet(:name => "repeated header") do |sheet| + sheet.add_row %w(These Column Header Will Render On Every Printed Sheet) + 200.times { sheet.add_row %w(1 2 3 4 5 6 7 8) } + wb.add_defined_name("'repeated header'!$1:$1", :local_sheet_id => sheet.index, :name => '_xlnm.Print_Titles') + end end -# Sheet Protection and excluding cells from locking. -unlocked = wb.styles.add_style :locked => false -wb.add_worksheet(:name => 'Sheet Protection') do |sheet| - sheet.sheet_protection.password = 'fish' - sheet.add_row [1, 2 ,3] # These cells will be locked - sheet.add_row [4, 5, 6], :style => unlocked # these cells will not! +# Defined Names in formula +if examples.include? :defined_name + wb.add_worksheet(:name => 'defined name') do |sheet| + sheet.add_row [1, 2, 17, '=FOOBAR'] + wb.add_defined_name("'defined name'!$C1", :local_sheet_id => sheet.index, :name => 'FOOBAR') + end end +# Sheet Protection and excluding cells from locking. +if examples.include? :sheet_protection + unlocked = wb.styles.add_style :locked => false + wb.add_worksheet(:name => 'Sheet Protection') do |sheet| + sheet.sheet_protection.password = 'fish' + sheet.add_row [1, 2 ,3] # These cells will be locked + sheet.add_row [4, 5, 6], :style => unlocked # these cells will not! + end +end ##Specify page margins and other options for printing #```ruby -margins = {:left => 3, :right => 3, :top => 1.2, :bottom => 1.2, :header => 0.7, :footer => 0.7} -setup = {:fit_to_width => 1, :orientation => :landscape, :paper_width => "297mm", :paper_height => "210mm"} -options = {:grid_lines => true, :headings => true, :horizontal_centered => true} -wb.add_worksheet(:name => "print margins", :page_margins => margins, :page_setup => setup, :print_options => options) do |sheet| - sheet.add_row ["this sheet uses customized print settings"] +if examples.include? :printing + margins = {:left => 3, :right => 3, :top => 1.2, :bottom => 1.2, :header => 0.7, :footer => 0.7} + setup = {:fit_to_width => 1, :orientation => :landscape, :paper_width => "297mm", :paper_height => "210mm"} + options = {:grid_lines => true, :headings => true, :horizontal_centered => true} + wb.add_worksheet(:name => "print margins", :page_margins => margins, :page_setup => setup, :print_options => options) do |sheet| + sheet.add_row ["this sheet uses customized print settings"] + end end #``` -## Add Comments to your spreadsheet +## Add headers and footers to a worksheet #``` ruby -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!' +if examples.include? :header_footer + header_footer = {:different_first => false, :odd_header => '&L&F : &A&R&D &T', :odd_footer => '&C&Pof&N'} + wb.add_worksheet(:name => "header footer", :header_footer => header_footer) do |sheet| + sheet.add_row ["this sheet has a header and a footer"] + end +end +#``` + +## Add Comments to your spreadsheet +#``` ruby +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!' + end end ## Frozen/Split panes ## ``` ruby -wb.add_worksheet(:name => 'fixed headers') do |sheet| - sheet.add_row(['', (0..99).map { |i| "column header #{i}" }].flatten ) - 100.times.with_index { |index| sheet << ["row header", (0..index).to_a].flatten } - sheet.sheet_view.pane do |pane| - pane.top_left_cell = "B2" - pane.state = :frozen_split - pane.y_split = 1 - pane.x_split = 1 - pane.active_pane = :bottom_right +if examples.include? :panes + wb.add_worksheet(:name => 'panes') do |sheet| + sheet.add_row(['', (0..99).map { |i| "column header #{i}" }].flatten ) + 100.times.with_index { |index| sheet << ["row header", (0..index).to_a].flatten } + sheet.sheet_view.pane do |pane| + pane.top_left_cell = "B2" + pane.state = :frozen_split + pane.y_split = 1 + pane.x_split = 1 + pane.active_pane = :bottom_right + end end end -# conditional formatting -# -percent = wb.styles.add_style(:format_code => "0.00%", :border => Axlsx::STYLE_THIN_BORDER) -money = wb.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_THIN_BORDER) +if examples.include? :sheet_view + ws = wb.add_worksheet(:name => 'SheetView - Split') + ws.sheet_view do |vs| + vs.pane do |pane| + pane.active_pane = :top_right + pane.state = :split + pane.x_split = 11080 + pane.y_split = 5000 + pane.top_left_cell = 'C44' + end + + vs.add_selection(:top_left, { :active_cell => 'A2', :sqref => 'A2' }) + vs.add_selection(:top_right, { :active_cell => 'I10', :sqref => 'I10' }) + vs.add_selection(:bottom_left, { :active_cell => 'E55', :sqref => 'E55' }) + vs.add_selection(:bottom_right, { :active_cell => 'I57', :sqref => 'I57' }) + end -# define the style for conditional formatting -profitable = wb.styles.add_style( :fg_color=>"FF428751", - :type => :dxf) + ws = wb.add_worksheet :name => "Sheetview - Frozen" + ws.sheet_view do |vs| + vs.pane do |pane| + pane.state = :frozen + pane.x_split = 3 + pane.y_split = 4 + end + end +end -wb.add_worksheet(:name => "Conditional Cell Is") do |ws| +# conditional formatting +# +if examples.include? :conditional_formatting + percent = wb.styles.add_style(:format_code => "0.00%", :border => Axlsx::STYLE_THIN_BORDER) + 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) + + wb.add_worksheet(:name => "Conditional Cell Is") do |sheet| + + # Generate 20 rosheet of data + sheet.add_row ["Previous Year Quarterly Profits (JPY)"] + sheet.add_row ["Quarter", "Profit", "% of Total"] + offset = 3 + rosheet = 20 + offset.upto(rosheet + offset) do |i| + sheet.add_row ["Q#{i}", 10000*((rosheet/2-i) * (rosheet/2-i)), "=100*B#{i}/SUM(B3:B#{rosheet+offset})"], :style=>[nil, money, percent] + end - # Generate 20 rows of data - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + # 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 }) end -# 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 }) -end + wb.add_worksheet(:name => "Conditional Color Scale") do |sheet| + sheet.add_row ["Previous Year Quarterly Profits (JPY)"] + sheet.add_row ["Quarter", "Profit", "% of Total"] + offset = 3 + rosheet = 20 + offset.upto(rosheet + offset) do |i| + sheet.add_row ["Q#{i}", 10000*((rosheet/2-i) * (rosheet/2-i)), "=100*B#{i}/SUM(B3:B#{rosheet+offset})"], :style=>[nil, money, percent] + end + # color scale has two_tone and three_tone class methods to setup the excel defaults (2011) + # alternatively, you can pass in {:type => [:min, :max, :percent], :val => [whatever], :color =>[Some RGB String] to create a customized color scale object -wb.add_worksheet(:name => "Conditional Color Scale") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + color_scale = Axlsx::ColorScale.three_tone + sheet.add_conditional_formatting("B3:B100", { :type => :colorScale, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1, :color_scale => color_scale }) end -# Apply conditional formatting to range B3:B100 in the worksheet - color_scale = Axlsx::ColorScale.new - ws.add_conditional_formatting("B3:B100", { :type => :colorScale, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1, :color_scale => color_scale }) -end -wb.add_worksheet(:name => "Conditional Data Bar") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + wb.add_worksheet(:name => "Conditional Data Bar") do |sheet| + sheet.add_row ["Previous Year Quarterly Profits (JPY)"] + sheet.add_row ["Quarter", "Profit", "% of Total"] + offset = 3 + rows = 20 + offset.upto(rows + offset) do |i| + sheet.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + end + # Apply conditional formatting to range B3:B100 in the worksheet + data_bar = Axlsx::DataBar.new + sheet.add_conditional_formatting("B3:B100", { :type => :dataBar, :dxfId => profitable, :priority => 1, :data_bar => data_bar }) end -# Apply conditional formatting to range B3:B100 in the worksheet - data_bar = Axlsx::DataBar.new - ws.add_conditional_formatting("B3:B100", { :type => :dataBar, :dxfId => profitable, :priority => 1, :data_bar => data_bar }) -end -wb.add_worksheet(:name => "Conditional Format Icon Set") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + wb.add_worksheet(:name => "Conditional Format Icon Set") do |sheet| + sheet.add_row ["Previous Year Quarterly Profits (JPY)"] + sheet.add_row ["Quarter", "Profit", "% of Total"] + offset = 3 + rows = 20 + offset.upto(rows + offset) do |i| + sheet.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + end + # Apply conditional formatting to range B3:B100 in the worksheet + icon_set = Axlsx::IconSet.new + sheet.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set }) end -# Apply conditional formatting to range B3:B100 in the worksheet - icon_set = Axlsx::IconSet.new - ws.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set }) end ##Validate and Serialize #```ruby # Serialize directly to file + p.serialize("example.xlsx") # or #Serialize to a stream -s = p.to_stream() -File.open('example_streamed.xlsx', 'w') { |f| f.write(s.read) } +if examples.include? :streaming + s = p.to_stream() + File.open('example_streamed.xlsx', 'w') { |f| f.write(s.read) } +end #``` ##Using Shared Strings #```ruby # This is required by Numbers -p.use_shared_strings = true -p.serialize("shared_strings_example.xlsx") +if examples.include? :shared_strings + p.use_shared_strings = true + p.serialize("shared_strings_example.xlsx") +end #``` #p.validate do |er| - #puts er.inspect +#puts er.inspect #end ##Disabling Autowidth #```ruby -p = Axlsx::Package.new -p.use_autowidth = false -wb = p.workbook -wb.add_worksheet(:name => "Manual Widths") do | sheet | - sheet.add_row ['oh look! no autowidth'] +if examples.include? :no_autowidth + p = Axlsx::Package.new + p.use_autowidth = false + wb = p.workbook + wb.add_worksheet(:name => "Manual Widths") do | sheet | + sheet.add_row ['oh look! no autowidth'] + end + p.validate.each { |e| puts e.message } + p.serialize("no-use_autowidth.xlsx") end -p.validate.each { |e| puts e.message } -p.serialize("no-use_autowidth.xlsx") #``` diff --git a/examples/extractive.pdf b/examples/extractive.pdf Binary files differdeleted file mode 100644 index e430df96..00000000 --- a/examples/extractive.pdf +++ /dev/null diff --git a/examples/finance.rb b/examples/finance.rb deleted file mode 100644 index 623773fd..00000000 --- a/examples/finance.rb +++ /dev/null @@ -1,82 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" - - -require 'axlsx' - -# First thing to do is setup our styles. OOXML Style management is unfortunately very different from CSS so we want to use the -# add_style helper method on the workbook styles object so we dont go insane. - -# I find it easier to declare a hash and then feed that in later - -class FinancialReport - - def initialize(data) - create_styles - prepare - insert_data data - finalize - package - end - - def style_hash - - sienna = 'A0522D' - { - :search_results => { :sz => 10, :b => true }, - :bold_header => { :b => true }, - :grey_bg => { :bg_color => "DEDEDE" }, - :transaction__header => { :fg_color => sienna, :b => true }, - :transaction_currency => { :fb_color => sienna, :num_fmt => 5 }, - :transactin_date => { :fg_color => sienna, :format_code => 'yyyy-mm-dd' } - } - end - - def styles - @styles ||= {} - end - - def package - @package ||= Axlsx::Package.new - end - - # Just a place to put some defualt data for the exercise - def self.data - @data = ['baked', - "American Medical Systems Holdings, Inc.", - "Endo Pharmaceuticals Holdings, Inc.", - "4371", - Date.new, - 2757001160, - 2519495160, - 116, - 3842, - "Medical devices for urology disorders"] - end - - # Populates an array of ['style_name'] = style_index - def create_styles - package.workbook.styles do |style| - style_hash.each do |key, value| - styles[key] = style.add_style(value) - end - end - end - def prepare - package.workbook.add_worksheet(:name => 'All Information') do |sheet| - sheet.add_row [nil, 'Search Results'], :style => [nil, styles['search_results']] - end - end - - - def insert_data(data) - package.workbook.worksheets.first do |sheet| - sheet.add_row data, style=> [styles[: - end - end - - def finalize - # package.serialize 'financial.xlsx' - end -end -f = FinancialReport.new(FinancialReport.data) -f.package.serialize 'finance.xlsx' diff --git a/examples/hyperlinks.rb b/examples/hyperlinks.rb deleted file mode 100644 index 9519f154..00000000 --- a/examples/hyperlinks.rb +++ /dev/null @@ -1,23 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" - -#```ruby -require 'axlsx' - -p = Axlsx::Package.new -wb = p.workbook -wb.add_worksheet(:name => 'hyperlinks') do |sheet| - # external references - sheet.add_row ['axlsx'] - sheet.add_hyperlink :location => 'https://github.com/randym/axlsx', :ref => sheet.rows.first.cells.first - # internal references - sheet.add_row ['next sheet'] - sheet.add_hyperlink :location => "'Next Sheet'!A1", :target => :sheet, :ref => 'A2' -end - -wb.add_worksheet(:name => 'Next Sheet') do |sheet| - sheet.add_row ['hello!'] -end - -p.serialize 'hyperlinks.xlsx' diff --git a/examples/scraping_html.rb b/examples/scraping_html.rb deleted file mode 100644 index 7df27c25..00000000 --- a/examples/scraping_html.rb +++ /dev/null @@ -1,91 +0,0 @@ - require 'rubygems' - require 'nokogiri' - require 'open-uri' - require 'axlsx' - - class Scraper - - def initialize(url, selector) - @url = url - @selector = selector - end - - def hooks - @hooks ||= {} - end - - def add_hook(clue, p_roc) - hooks[clue] = p_roc - end - - def export(file_name) - Scraper.clues.each do |clue| - if detail = parse_clue(clue) - output << [clue, detail.pop] - detail.each { |datum| output << ['', datum] } - end - end - serialize(file_name) - end - - private - - def self.clues - @clues ||= ['Operating system', 'Processors', 'Chipset', 'Memory type', 'Hard drive', 'Graphics', - 'Ports', 'Webcam', 'Pointing device', 'Keyboard', 'Network interface', 'Chipset', 'Wireless', - 'Power supply type', 'Energy efficiency', 'Weight', 'Minimum dimensions (W x D x H)', - 'Warranty', 'Software included', 'Product color'] - end - - def doc - @doc ||= begin - Nokogiri::HTML(open(@url)) - rescue - raise ArgumentError, 'Invalid URL - Nothing to parse' - end - end - - def output - @output ||= [] - end - - def selector_for_clue(clue) - @selector % clue - end - - def parse_clue(clue) - if element = doc.at(selector_for_clue(clue)) - call_hook(clue, element) || element.inner_html.split('<br>').each(&:strip) - end - end - - def call_hook(clue, element) - if hooks[clue].is_a? Proc - value = hooks[clue].call(element) - value.is_a?(Array) ? value : [value] - end - end - - def package - @package ||= Axlsx::Package.new - end - - def serialize(file_name) - package.workbook.add_worksheet do |sheet| - output.each { |datum| sheet.add_row datum } - end - package.serialize(file_name) - end - end - - scraper = Scraper.new("http://h10010.www1.hp.com/wwpc/ie/en/ho/WF06b/321957-321957-3329742-89318-89318-5186820-5231694.html?dnr=1", "//td[text()='%s']/following-sibling::td") - - # define a custom action to take against any elements found. - os_parse = Proc.new do |element| - element.inner_html.split('<br>').each(&:strip!).each(&:upcase!) - end - - scraper.add_hook('Operating system', os_parse) - - scraper.export('foo.xlsx') - diff --git a/examples/sheet_view.rb b/examples/sheet_view.rb deleted file mode 100644 index 2cbe680a..00000000 --- a/examples/sheet_view.rb +++ /dev/null @@ -1,34 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' - -p = Axlsx::Package.new -ws = p.workbook.add_worksheet :name => "Sheetview - Split" -ws.sheet_view do |vs| - vs.pane do |p| - p.active_pane = :top_right - p.state = :split - p.x_split = 11080 - p.y_split = 5000 - p.top_left_cell = 'C44' - end - - vs.add_selection(:top_left, { :active_cell => 'A2', :sqref => 'A2' }) - vs.add_selection(:top_right, { :active_cell => 'I10', :sqref => 'I10' }) - vs.add_selection(:bottom_left, { :active_cell => 'E55', :sqref => 'E55' }) - vs.add_selection(:bottom_right, { :active_cell => 'I57', :sqref => 'I57' }) -end - - -ws = p.workbook.add_worksheet :name => "Sheetview - Frozen" -ws.sheet_view do |vs| - vs.pane do |p| - p.state = :frozen - p.x_split = 3 - p.y_split = 4 - end -end - - -p.serialize 'sheet_view.xlsx'
\ No newline at end of file diff --git a/examples/skydrive/axlsx.csv b/examples/skydrive/axlsx.csv deleted file mode 100644 index b3cea7c1..00000000 --- a/examples/skydrive/axlsx.csv +++ /dev/null @@ -1 +0,0 @@ -,,,,,
,College Budget,,,,
,,,,,
,What's coming in this month.,,,How am I doing,
,Item,Amount,,Item,Amount
,Estimated monthly net income,\500,,Monthly income,"\20,600"
,Financial aid,\100,,Monthly expenses,"\2,255"
,Allowance from mom & dad,"\20,000",,Semester expenses,\440
,Total,"\20,600",,Difference,"\17,905"
,,,,,
,What's going out this month.,,,Semester Costs,
,Item,Amount,,Item,Amount
,Rent,\650,,Tuition,\200
,Utilities,\120,,Lab fees,\50
,Cell phone,\100,,Other fees,\10
,Groceries,\75,,Books,\150
,Auto expenses,\0,,Deposits,\0
,Student loans,\0,,Transportation,\30
,Other loans,\350,,Total,\440
,Credit cards,\450,,,
,Insurance,\0,,,
,Laundry,\10,,,
,Haircuts,\0,,,
,Medical expenses,\0,,,
,Entertainment,\500,,,
,Miscellaneous,\0,,,
,Total,"\2,255",,,
\ No newline at end of file diff --git a/examples/sprk2012/basics.rb b/examples/sprk2012/basics.rb deleted file mode 100644 index 82c56a4e..00000000 --- a/examples/sprk2012/basics.rb +++ /dev/null @@ -1,11 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../../lib" -require 'axlsx' - -package = Axlsx::Package.new -package.workbook.add_worksheet(:name => "Basic Worksheet") do |sheet| - sheet.add_row ["First Column", "Second", "Third"] - sheet.add_row [1, 2, 3] -end -package.serialize 'basics.xlsx' - - diff --git a/examples/sprk2012/images.rb b/examples/sprk2012/images.rb deleted file mode 100644 index 6e0b306d..00000000 --- a/examples/sprk2012/images.rb +++ /dev/null @@ -1,9 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../../lib" - -require 'axlsx' -package = Axlsx::Package.new do |package| - package.workbook.add_worksheet(:name => "imagex") do |sheet| - img_path = File.expand_path('../../image1.jpeg', __FILE__) - sheet.add_image(:image_src => img_path, :width => 720, :height => 666, :start_at => [2,2]) - end -end.serialize 'images.xlsx' diff --git a/examples/sprk2012/styles.rb b/examples/sprk2012/styles.rb deleted file mode 100644 index ce1bf2a0..00000000 --- a/examples/sprk2012/styles.rb +++ /dev/null @@ -1,20 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../../lib" - -require 'axlsx' -package = Axlsx::Package.new -package.workbook do |workbook| - workbook.styles do |s| - black_cell = s.add_style :bg_color => "00", :fg_color => "FF", :sz => 14, :alignment => { :horizontal=> :center } - blue_cell = s.add_style :bg_color => "0000FF", :fg_color => "FF", :sz => 20, :alignment => { :horizontal=> :center } - - workbook.add_worksheet(:name => "Styles") do |sheet| - # Applies the black_cell style to the first and third cell, and the blue_cell style to the second. - sheet.add_row ["Text Autowidth", "Second", "Third"], :style => [black_cell, blue_cell, black_cell] - - # Applies the thin border to all three cells - sheet.add_row [1, 2, 3], :style => Axlsx::STYLE_THIN_BORDER - end - end -end -package.serialize 'styles.xlsx' - diff --git a/examples/two_cell_anchor_image.rb b/examples/two_cell_anchor_image.rb deleted file mode 100644 index 4fe4b566..00000000 --- a/examples/two_cell_anchor_image.rb +++ /dev/null @@ -1,11 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' - -p = Axlsx::Package.new -src = "#{File.dirname(__FILE__)}/image1.png" -p.workbook.add_worksheet(:name => 'double_anchor') do |ws| - ws.add_image(:image_src => src, :start_at => [0,0], :end_at => [2,4]) -end -p.serialize('two_cell_anchor_image.xlsx') diff --git a/examples/wrap_text.rb b/examples/wrap_text.rb new file mode 100644 index 00000000..c2e1aeb5 --- /dev/null +++ b/examples/wrap_text.rb @@ -0,0 +1,21 @@ +$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" +require 'axlsx' +p = Axlsx::Package.new +p.workbook do |wb| + wb.styles do |s| + wrap_text = s.add_style :fg_color=> "FFFFFF", + :b => true, + :bg_color => "004586", + :sz => 12, + :border => { :style => :thin, :color => "00" }, + :alignment => { :horizontal => :center, + :vertical => :center , + :wrap_text => true} + wb.add_worksheet(:name => 'wrap text') do |sheet| + sheet.add_row ['Torp, White and Cronin'], :style => wrap_text + # Forcing the column to be a bit narrow so we can see if the text wrap. + sheet.column_info.first.width = 5 + end + end +end +p.serialize 'wrap_text.xlsx' diff --git a/lib/axlsx/content_type/abstract_content_type.rb b/lib/axlsx/content_type/abstract_content_type.rb new file mode 100644 index 00000000..0afaa53f --- /dev/null +++ b/lib/axlsx/content_type/abstract_content_type.rb @@ -0,0 +1,32 @@ +module Axlsx + + # This class extracts the common parts from Default and Override + class AbstractContentType + + include Axlsx::OptionsParser + + # Initializes an abstract content type + # @see Default, Override + def initialize(options={}) + parse_options options + end + + # The type of content. + # @return [String] + attr_reader :content_type + alias :ContentType :content_type + + # The content type. + # @see Axlsx#validate_content_type + def content_type=(v) Axlsx::validate_content_type v; @content_type = v end + alias :ContentType= :content_type= + + # Serialize the contenty type to xml + def to_xml_string(node_name = '', str = '') + str << "<#{node_name} " + str << instance_values.map { |key, value| '' << Axlsx::camel(key) << '="' << value.to_s << '"' }.join(' ') + str << '/>' + end + + end +end diff --git a/lib/axlsx/content_type/content_type.rb b/lib/axlsx/content_type/content_type.rb index 1d528097..94d65296 100644 --- a/lib/axlsx/content_type/content_type.rb +++ b/lib/axlsx/content_type/content_type.rb @@ -1,6 +1,6 @@ # encoding: UTF-8 module Axlsx - + require 'axlsx/content_type/abstract_content_type.rb' require 'axlsx/content_type/default.rb' require 'axlsx/content_type/override.rb' diff --git a/lib/axlsx/content_type/default.rb b/lib/axlsx/content_type/default.rb index 2b784ebf..3fe15609 100644 --- a/lib/axlsx/content_type/default.rb +++ b/lib/axlsx/content_type/default.rb @@ -2,55 +2,24 @@ module Axlsx # An default content part. These parts are automatically created by for you based on the content of your package. - class Default + class Default < AbstractContentType - include Axlsx::OptionsParser - - #Creates a new Default object - # @option options [String] extension - # @option options [String] content_type - # @raise [ArgumentError] An argument error is raised if both extension and content_type are not specified. - def initialize(options={}) - raise ArgumentError, INVALID_ARGUMENTS unless validate_options(options) - parse_options options - end - - # Error string for option validation - INVALID_ARGUMENTS = "extension and content_type are required" + # The serialization node name for this class + NODE_NAME = 'Default' # The extension of the content type. # @return [String] attr_reader :extension alias :Extension :extension - # The type of content. - # @return [String] - attr_reader :content_type - alias :ContentType :content_type - # Sets the file extension for this content type. def extension=(v) Axlsx::validate_string v; @extension = v end alias :Extension= :extension= - # Sets the content type - # @see Axlsx#validate_content_type - def content_type=(v) Axlsx::validate_content_type v; @content_type = v end - alias :ContentType= :content_type= - - # Serializes the object - # @param [String] str - # @return [String] - def to_xml_string(str = '') - str << '<Default ' - str << instance_values.map { |key, value| '' << Axlsx::camel(key) << '="' << value.to_s << '"' }.join(' ') - str << '/>' + # Serializes this object to xml + def to_xml_string(str ='') + super(NODE_NAME, str) end - - private - def validate_options(options) - (options[:Extension] || options[:extension]) && (options[:content_type] || options[:ContentType]) - end - end end diff --git a/lib/axlsx/content_type/override.rb b/lib/axlsx/content_type/override.rb index a3947d99..7a8e33fa 100644 --- a/lib/axlsx/content_type/override.rb +++ b/lib/axlsx/content_type/override.rb @@ -1,27 +1,12 @@ + # encoding: UTF-8 module Axlsx # An override content part. These parts are automatically created by for you based on the content of your package. - class Override - - include Axlsx::OptionsParser - - #Creates a new Override object - # @option options [String] PartName - # @option options [String] ContentType - # @raise [ArgumentError] An argument error is raised if both PartName and ContentType are not specified. - def initialize(options={}) - raise ArgumentError, INVALID_ARGUMENTS unless validate_options(options) - parse_options options - end + class Override < AbstractContentType - # Error message for invalid options - INVALID_ARGUMENTS = 'part_name and content_type are required' - - # The type of content. - # @return [String] - attr_reader :content_type - alias :ContentType :content_type + # Serialization node name for this object + NODE_NAME = 'Override' # The name and location of the part. # @return [String] @@ -32,26 +17,9 @@ module Axlsx def part_name=(v) Axlsx::validate_string v; @part_name = v end alias :PartName= :part_name= - # The content type. - # @see Axlsx#validate_content_type - def content_type=(v) Axlsx::validate_content_type v; @content_type = v end - alias :ContentType= :content_type= - - # Serializes the object - # @param [String] str - # @return [String] + # Serializes this object to xml def to_xml_string(str = '') - str << '<Override ' - str << instance_values.map { |key, value| '' << Axlsx::camel(key) << '="' << value.to_s << '"' }.join(' ') - str << '/>' - end - - private - - def validate_options(options) - (options[:PartName] || options[:part_name]) && (options[:ContentType] || options[:content_type]) + super(NODE_NAME, str) end - end - end diff --git a/lib/axlsx/drawing/chart.rb b/lib/axlsx/drawing/chart.rb index 35e2089a..1006f25e 100644 --- a/lib/axlsx/drawing/chart.rb +++ b/lib/axlsx/drawing/chart.rb @@ -14,7 +14,7 @@ module Axlsx # @option options [Array|String|Cell] start_at The X, Y coordinates defining the top left corner of the chart. # @option options [Array|String|Cell] end_at The X, Y coordinates defining the bottom right corner of the chart. def initialize(frame, options={}) - @style = 18 + @style = 18 @view_3D = nil @graphic_frame=frame @graphic_frame.anchor.drawing.worksheet.workbook.charts << self diff --git a/lib/axlsx/drawing/drawing.rb b/lib/axlsx/drawing/drawing.rb index 48b9320f..769d67c5 100644 --- a/lib/axlsx/drawing/drawing.rb +++ b/lib/axlsx/drawing/drawing.rb @@ -137,6 +137,12 @@ module Axlsx "#{DRAWING_RELS_PN % (index+1)}" end + # The index of a chart, image or hyperlink object this drawing contains + def index_of(object) + objects = charts + images + hyperlinks + objects.index(object) + end + # The drawing's relationships. # @return [Relationships] def relationships diff --git a/lib/axlsx/drawing/hyperlink.rb b/lib/axlsx/drawing/hyperlink.rb index 3f4a2e80..aa763e1f 100644 --- a/lib/axlsx/drawing/hyperlink.rb +++ b/lib/axlsx/drawing/hyperlink.rb @@ -93,10 +93,11 @@ module Axlsx end private + # The relational ID for this hyperlink # @return [Integer] def id - @parent.anchor.drawing.charts.size + @parent.anchor.drawing.images.size + @parent.anchor.drawing.hyperlinks.index(self) + 1 + @parent.anchor.drawing.index_of(self)+1 end end diff --git a/lib/axlsx/drawing/view_3D.rb b/lib/axlsx/drawing/view_3D.rb index 0acba62f..9c320b4a 100644 --- a/lib/axlsx/drawing/view_3D.rb +++ b/lib/axlsx/drawing/view_3D.rb @@ -40,7 +40,7 @@ module Axlsx # @return [Integer] attr_reader :rot_y alias :rotY :rot_y - + # depth or chart as % of chart width # must be between 20% and 2000% # @return [String] @@ -56,44 +56,60 @@ module Axlsx # @return [Integer] attr_reader :perspective - # @see rot_x - def rot_x=(v) DataTypeValidator.validate "#{self.class}.rot_x", [Integer, Fixnum], v, lambda {|arg| arg >= -90 && arg <= 90 }; @rot_x = v; end + # @see rot_x + def rot_x=(v) + RangeValidator.validate "View3D.rot_x", -90, 90, v + @rot_x = v + end alias :rotX= :rot_x= - # @see h_percent - def h_percent=(v) - RegexValidator.validate "#{self.class}.h_percent", H_PERCENT_REGEX, v - @h_percent = v - end + # @see h_percent + def h_percent=(v) + RegexValidator.validate "#{self.class}.h_percent", H_PERCENT_REGEX, v + @h_percent = v + end alias :hPercent= :h_percent= - # @see rot_y - def rot_y=(v) DataTypeValidator.validate "#{self.class}.rot_y", [Integer, Fixnum], v, lambda {|arg| arg >= 0 && arg <= 360 }; @rot_y = v; end + # @see rot_y + def rot_y=(v) + RangeValidator.validate "View3D.rot_y", 0, 360, v + @rot_y = v + end alias :rotY= :rot_y= - # @see depth_percent - def depth_percent=(v) RegexValidator.validate "#{self.class}.depth_percent", DEPTH_PERCENT_REGEX, v; @depth_percent = v; end + # @see depth_percent + def depth_percent=(v) RegexValidator.validate "#{self.class}.depth_percent", DEPTH_PERCENT_REGEX, v; @depth_percent = v; end alias :depthPercent= :depth_percent= - # @see r_ang_ax - def r_ang_ax=(v) Axlsx::validate_boolean(v); @r_ang_ax = v; end + # @see r_ang_ax + def r_ang_ax=(v) Axlsx::validate_boolean(v); @r_ang_ax = v; end alias :rAngAx= :r_ang_ax= - # @see perspective - def perspective=(v) DataTypeValidator.validate "#{self.class}.perspective", [Integer, Fixnum], v, lambda {|arg| arg >= 0 && arg <= 240 }; @perspective = v; end + # @see perspective + def perspective=(v) + RangeValidator.validate "View3D.perspective", 0, 240, v + @perspective = v + end + + # DataTypeValidator.validate "#{self.class}.perspective", [Integer, Fixnum], v, lambda {|arg| arg >= 0 && arg <= 240 }; @perspective = v; end # Serializes the object # @param [String] str # @return [String] def to_xml_string(str = '') str << '<c:view3D>' - str << '<c:rotX val="' << @rot_x.to_s << '"/>' unless @rot_x.nil? - str << '<c:hPercent val="' << @h_percent.to_s << '"/>' unless @h_percent.nil? - str << '<c:rotY val="' << @rot_y.to_s << '"/>' unless @rot_y.nil? - str << '<c:depthPercent val="' << @depth_percent.to_s << '"/>' unless @depth_percent.nil? - str << '<c:rAngAx val="' << @r_ang_ax.to_s << '"/>' unless @r_ang_ax.nil? - str << '<c:perspective val="' << @perspective.to_s << '"/>' unless @perspective.nil? + %w(rot_x h_percent rot_y depth_percent r_ang_ax perspective).each do |key| + str << element_for_attribute(key, 'c') + end str << '</c:view3D>' end + + private + # Note: move this to Axlsx module if we find the smae pattern elsewhere. + def element_for_attribute(name, namespace='') + val = instance_values[name] + return "" if val == nil + "<%s:%s val='%s'/>" % [namespace, Axlsx::camel(name, false), val] + end end end diff --git a/lib/axlsx/package.rb b/lib/axlsx/package.rb index 829eb199..e3981d62 100644 --- a/lib/axlsx/package.rb +++ b/lib/axlsx/package.rb @@ -173,7 +173,7 @@ module Axlsx # @return [Array] An array of hashes that define the entry, document and schema for each part of the package. # @private def parts - @parts = [ + parts = [ {:entry => RELS_PN, :doc => relationships.to_xml_string, :schema => RELS_XSD}, {:entry => "xl/#{STYLES_PN}", :doc => workbook.styles.to_xml_string, :schema => SML_XSD}, {:entry => CORE_PN, :doc => @core.to_xml_string, :schema => CORE_XSD}, @@ -184,39 +184,39 @@ module Axlsx ] workbook.drawings.each do |drawing| - @parts << {:entry => "xl/#{drawing.rels_pn}", :doc => drawing.relationships.to_xml_string, :schema => RELS_XSD} - @parts << {:entry => "xl/#{drawing.pn}", :doc => drawing.to_xml_string, :schema => DRAWING_XSD} + parts << {:entry => "xl/#{drawing.rels_pn}", :doc => drawing.relationships.to_xml_string, :schema => RELS_XSD} + parts << {:entry => "xl/#{drawing.pn}", :doc => drawing.to_xml_string, :schema => DRAWING_XSD} end workbook.tables.each do |table| - @parts << {:entry => "xl/#{table.pn}", :doc => table.to_xml_string, :schema => SML_XSD} + parts << {:entry => "xl/#{table.pn}", :doc => table.to_xml_string, :schema => SML_XSD} end workbook.comments.each do|comment| if comment.size > 0 - @parts << { :entry => "xl/#{comment.pn}", :doc => comment.to_xml_string, :schema => SML_XSD } - @parts << { :entry => "xl/#{comment.vml_drawing.pn}", :doc => comment.vml_drawing.to_xml_string, :schema => nil } + parts << { :entry => "xl/#{comment.pn}", :doc => comment.to_xml_string, :schema => SML_XSD } + parts << { :entry => "xl/#{comment.vml_drawing.pn}", :doc => comment.vml_drawing.to_xml_string, :schema => nil } end end workbook.charts.each do |chart| - @parts << {:entry => "xl/#{chart.pn}", :doc => chart.to_xml_string, :schema => DRAWING_XSD} + parts << {:entry => "xl/#{chart.pn}", :doc => chart.to_xml_string, :schema => DRAWING_XSD} end workbook.images.each do |image| - @parts << {:entry => "xl/#{image.pn}", :path => image.image_src} + parts << {:entry => "xl/#{image.pn}", :path => image.image_src} end if use_shared_strings - @parts << {:entry => "xl/#{SHARED_STRINGS_PN}", :doc => workbook.shared_strings.to_xml_string, :schema => SML_XSD} + parts << {:entry => "xl/#{SHARED_STRINGS_PN}", :doc => workbook.shared_strings.to_xml_string, :schema => SML_XSD} end workbook.worksheets.each do |sheet| - @parts << {:entry => "xl/#{sheet.rels_pn}", :doc => sheet.relationships.to_xml_string, :schema => RELS_XSD} - @parts << {:entry => "xl/#{sheet.pn}", :doc => sheet.to_xml_string, :schema => SML_XSD} + parts << {:entry => "xl/#{sheet.rels_pn}", :doc => sheet.relationships.to_xml_string, :schema => RELS_XSD} + parts << {:entry => "xl/#{sheet.pn}", :doc => sheet.to_xml_string, :schema => SML_XSD} end - @parts + parts end # Performs xsd validation for a signle document diff --git a/lib/axlsx/rels/relationship.rb b/lib/axlsx/rels/relationship.rb index 99c8e112..04911904 100644 --- a/lib/axlsx/rels/relationship.rb +++ b/lib/axlsx/rels/relationship.rb @@ -55,7 +55,7 @@ module Axlsx h = self.instance_values h[:Id] = 'rId' << rId.to_s str << '<Relationship ' - str << h.map { |key, value| '' << key.to_s << '="' << value.to_s << '"'}.join(' ') + str << h.map { |key, value| '' << key.to_s << '="' << Axlsx::coder.encode(value.to_s) << '"'}.join(' ') str << '/>' end diff --git a/lib/axlsx/stylesheet/color.rb b/lib/axlsx/stylesheet/color.rb index b5537ac1..744e63d5 100644 --- a/lib/axlsx/stylesheet/color.rb +++ b/lib/axlsx/stylesheet/color.rb @@ -69,8 +69,8 @@ module Axlsx # Serializes the object # @param [String] str # @return [String] - def to_xml_string(str = '') - str << "<color " + def to_xml_string(str = '', tag_name = 'color') + str << "<" << tag_name << " " serialized_attributes str str << "/>" end diff --git a/lib/axlsx/stylesheet/gradient_fill.rb b/lib/axlsx/stylesheet/gradient_fill.rb index cfd51e64..b077d497 100644 --- a/lib/axlsx/stylesheet/gradient_fill.rb +++ b/lib/axlsx/stylesheet/gradient_fill.rb @@ -84,6 +84,7 @@ module Axlsx @bottom = v end + # validates that the value provided is between 0.0 and 1.0 def validate_format_percentage(name, value) DataTypeValidator.validate name, Float, value, lambda { |arg| arg >= 0.0 && arg <= 1.0} end diff --git a/lib/axlsx/stylesheet/pattern_fill.rb b/lib/axlsx/stylesheet/pattern_fill.rb index c06c7f16..504624e6 100644 --- a/lib/axlsx/stylesheet/pattern_fill.rb +++ b/lib/axlsx/stylesheet/pattern_fill.rb @@ -6,7 +6,6 @@ module Axlsx class PatternFill include Axlsx::OptionsParser - # Creates a new PatternFill Object # @option options [Symbol] patternType # @option options [Color] fgColor @@ -62,19 +61,11 @@ module Axlsx def to_xml_string(str = '') str << '<patternFill patternType="' << patternType.to_s << '">' if fgColor.is_a?(Color) - str << "<fgColor " - fgColor.instance_values.each do |key, value| - str << key.to_s << '="' << value.to_s << '" ' - end - str << "/>" + fgColor.to_xml_string str, "fgColor" end if bgColor.is_a?(Color) - str << "<bgColor " - bgColor.instance_values.each do |key, value| - str << key.to_s << '="' << value.to_s << '" ' - end - str << "/>" + bgColor.to_xml_string str, "bgColor" end str << '</patternFill>' end diff --git a/lib/axlsx/util/accessors.rb b/lib/axlsx/util/accessors.rb index 3096d9c7..f578be26 100644 --- a/lib/axlsx/util/accessors.rb +++ b/lib/axlsx/util/accessors.rb @@ -1,19 +1,42 @@ module Axlsx + # This module defines some of the more common validating attribute + # accessors that we use in Axlsx + # + # When this module is included in your class you can simply call + # + # string_attr_access :foo + # + # To generate a new, validating set of accessors for foo. module Accessors def self.included(base) base.send :extend, ClassMethods end + # Defines the class level xxx_attr_accessor methods module ClassMethods + + # Creates one or more string validated attr_accessors + # @param [Array] symbols An array of symbols representing the + # names of the attributes you will add to your class. def string_attr_accessor(*symbols) validated_attr_accessor(symbols, 'validate_string') end + # Creates on or more boolean validated attr_accessors + # @param [Array] symbols An array of symbols representing the + # names of the attributes you will add to your class. def boolean_attr_accessor(*symbols) validated_attr_accessor(symbols, 'validate_boolean') end + # Template for defining validated write accessors SETTER = "def %s=(value) Axlsx::%s(value); @%s = value; end" + + # Creates the reader and writer access methods + # @param [Array] symbols The names of the attributes to create + # @param [String] validator The axlsx validation method to use when + # validating assignation. + # @see lib/axlsx/util/validators.rb def validated_attr_accessor(symbols, validator) symbols.each do |symbol| attr_reader symbol diff --git a/lib/axlsx/util/options_parser.rb b/lib/axlsx/util/options_parser.rb index b6332275..07dd128b 100644 --- a/lib/axlsx/util/options_parser.rb +++ b/lib/axlsx/util/options_parser.rb @@ -1,5 +1,11 @@ module Axlsx + # This module defines a single method for parsing options in class + # initializers. module OptionsParser + + # Parses an options hash by calling any defined method by the same + # name of the key postfixed with an '=' + # @param [Hash] options Options to parse. def parse_options(options={}) options.each do |key, value| self.send("#{key}=", value) if self.respond_to?("#{key}=") && value != nil diff --git a/lib/axlsx/util/serialized_attributes.rb b/lib/axlsx/util/serialized_attributes.rb index 59efbc9f..5519f843 100644 --- a/lib/axlsx/util/serialized_attributes.rb +++ b/lib/axlsx/util/serialized_attributes.rb @@ -1,20 +1,44 @@ module Axlsx + # This module allows us to define a list of symbols defining which + # attributes will be serialized for a class. module SerializedAttributes + # Extend with class methods def self.included(base) base.send :extend, ClassMethods end + # class methods applied to all includers module ClassMethods + # This is the method to be used in inheriting classes to specify + # which of the instance values are serializable def serializable_attributes(*symbols) @xml_attributes = symbols end + + # a reader for those attributes def xml_attributes @xml_attributes end + + # This helper registers the attributes that will be formatted as elements. + def serializable_element_attributes(*symbols) + @xml_element_attributes = symbols + end + + # attr reader for element attributes + def xml_element_attributes + @xml_element_attributes + end end + # serializes the instance values of the defining object based on the + # list of serializable attributes. + # @param [String] str The string instance to append this + # serialization to. + # @param [Hash] additional_attributes An option key value hash for + # defining values that are not serializable attributes list. def serialized_attributes(str = '', additional_attributes = {}) key_value_pairs = instance_values key_value_pairs.each do |key, value| @@ -22,9 +46,30 @@ module Axlsx key_value_pairs.delete(key) unless self.class.xml_attributes.include?(key.to_sym) end key_value_pairs.merge! additional_attributes - key_value_pairs.each do |key, value| - str << "#{Axlsx.camel(key, false)}='#{value}' " + str << "#{Axlsx.camel(key, false)}=\"#{value}\" " + end + str + end + + + # serialized instance values at text nodes on a camelized element of the + # attribute name. You may pass in a block for evaluation against non nil + # values. We use an array for element attributes becuase misordering will + # 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 + values = instance_values + attrs.each do |attribute_name| + value = values[attribute_name.to_s] + next if value.nil? + value = yield value if block_given? + element_name = Axlsx.camel(attribute_name, false) + str << "<#{element_name}>#{value}</#{element_name}>" end str end diff --git a/lib/axlsx/util/simple_typed_list.rb b/lib/axlsx/util/simple_typed_list.rb index a6c11a5d..4d188ffd 100644 --- a/lib/axlsx/util/simple_typed_list.rb +++ b/lib/axlsx/util/simple_typed_list.rb @@ -43,16 +43,16 @@ module Axlsx def to_ary @list end - + alias :to_a :to_ary - + # Unlock the list # @return [self] def unlock @locked_at = nil self end - + # join operator # @param [Array] v the array to join # @raise [ArgumentError] if any of the values being joined are not @@ -62,7 +62,7 @@ module Axlsx v.each do |item| DataTypeValidator.validate "SimpleTypedList.+", @allowed_types, item @list << item - end + end end # Concat operator @@ -107,6 +107,18 @@ module Axlsx v end + # inserts an item at the index specfied + # @param [Integer] index + # @param [Any] v + # @raise [ArgumentError] if the index is protected by locking + # @raise [ArgumentError] if the index is not one of the allowed types + def insert(index, v) + DataTypeValidator.validate "SimpleTypedList.<<", @allowed_types, v + raise ArgumentError, "Item is protected and cannot be changed" if protected? index + @list.insert(index, v) + v + end + # determines if the index is protected # @param [Integer] index def protected? index diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb index 9c1ec741..cc8f0a92 100644 --- a/lib/axlsx/util/validators.rb +++ b/lib/axlsx/util/validators.rb @@ -140,7 +140,15 @@ module Axlsx def self.validate_page_orientation(v) RestrictionValidator.validate "page_orientation", [:default, :landscape, :portrait], v end + # Requires that the value is one of :none, :single, :double, :singleAccounting, :doubleAccounting + def self.validate_cell_u(v) + RestrictionValidator.validate "cell run style u", [:none, :single, :double, :singleAccounting, :doubleAccounting], v + end + # validates cell style family which must be between 1 and 5 + def self.validate_family(v) + RestrictionValidator.validate "cell run style family", 1..5, v + end # Requires that the value is valid pattern type. # valid pattern types must be one of :none, :solid, :mediumGray, :darkGray, :lightGray, :darkHorizontal, :darkVertical, :darkDown, # :darkUp, :darkGrid, :darkTrellis, :lightHorizontal, :lightVertical, :lightDown, :lightUp, :lightGrid, :lightTrellis, :gray125, or :gray0625. diff --git a/lib/axlsx/version.rb b/lib/axlsx/version.rb index 9a40f3f1..f3f0c5f6 100644 --- a/lib/axlsx/version.rb +++ b/lib/axlsx/version.rb @@ -1,5 +1,5 @@ module Axlsx # The current version - VERSION = "1.3.1" + VERSION = "1.3.4" end diff --git a/lib/axlsx/workbook/defined_name.rb b/lib/axlsx/workbook/defined_name.rb index 833093fd..b55856de 100644 --- a/lib/axlsx/workbook/defined_name.rb +++ b/lib/axlsx/workbook/defined_name.rb @@ -55,9 +55,9 @@ module Axlsx # creates a new DefinedName. # @param [String] formula - the formula the defined name references # @param [Hash] options - A hash of key/value pairs that will be mapped to this instances attributes. - # + # # @option [String] name - Specifies the name that appears in the user interface for the defined name. - # This attribute is required. + # This attribute is required. # The following built-in names are defined in this SpreadsheetML specification: # Print # _xlnm.Print_Area: this defined name specifies the workbook's print area. @@ -111,25 +111,13 @@ module Axlsx @local_sheet_id = value end - string_attr_accessor :short_cut_key, :status_bar, :help, :description, :custom_menu, :comment + string_attr_accessor :short_cut_key, :status_bar, :help, :description, :custom_menu, :comment, :name, :formula - # boolean attributes that will be added when this class is evaluated boolean_attr_accessor :workbook_parameter, :publish_to_server, :xlm, :vb_proceedure, :function, :hidden serializable_attributes :short_cut_key, :status_bar, :help, :description, :custom_menu, :comment, :workbook_parameter, :publish_to_server, :xlm, :vb_proceedure, :function, :hidden, :name, :local_sheet_id - - attr_reader :name - # The name of this defined name. Please refer to the class documentation for more information - def name=(value) - Axlsx::RestrictionValidator.validate 'DefinedName.name', %w(_xlnm.Print_Area _xlnm.Print_Titles _xlnm.Criteria _xlnm._FilterDatabase _xlnm.Extract _xlnm.Consolidate_Area _xlnm.Database _xlnm.Sheet_Title), value - @name = value - end - - # The formula this defined name references - attr_reader :formula - def to_xml_string(str='') raise ArgumentError, 'you must specify the name for this defined name. Please read the documentation for Axlsx::DefinedName for more details' unless name str << '<definedName ' diff --git a/lib/axlsx/workbook/workbook.rb b/lib/axlsx/workbook/workbook.rb index a1964883..6c99d6de 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -9,8 +9,10 @@ require 'axlsx/workbook/worksheet/cell.rb' require 'axlsx/workbook/worksheet/page_margins.rb' require 'axlsx/workbook/worksheet/page_set_up_pr.rb' require 'axlsx/workbook/worksheet/page_setup.rb' +require 'axlsx/workbook/worksheet/header_footer.rb' require 'axlsx/workbook/worksheet/print_options.rb' require 'axlsx/workbook/worksheet/cfvo.rb' +require 'axlsx/workbook/worksheet/cfvos.rb' require 'axlsx/workbook/worksheet/color_scale.rb' require 'axlsx/workbook/worksheet/data_bar.rb' require 'axlsx/workbook/worksheet/icon_set.rb' @@ -214,6 +216,23 @@ require 'axlsx/workbook/worksheet/selection.rb' # see @use_autowidth def use_autowidth=(v=true) Axlsx::validate_boolean v; @use_autowidth = v; end + # inserts a worksheet into this workbook at the position specified. + # It the index specified is out of range, the worksheet will be added to the end of the + # worksheets collection + # @return [Worksheet] + # @param index The zero based position to insert the newly created worksheet + # @param [Hash] options Options to pass into the worksheed during initialization. + # @option options [String] name The name of the worksheet + # @option options [Hash] page_margins The page margins for the worksheet + def insert_worksheet(index=0, options={}) + worksheet = Worksheet.new(self, options) + @worksheets.delete_at(@worksheets.size - 1) + @worksheets.insert(index, worksheet) + yield worksheet if block_given? + worksheet + end + + # # Adds a worksheet to this workbook # @return [Worksheet] # @option options [String] name The name of the worksheet. diff --git a/lib/axlsx/workbook/worksheet/cell.rb b/lib/axlsx/workbook/worksheet/cell.rb index 86e61d9d..cfcb6118 100644 --- a/lib/axlsx/workbook/worksheet/cell.rb +++ b/lib/axlsx/workbook/worksheet/cell.rb @@ -112,16 +112,43 @@ module Axlsx def font_name=(v) set_run_style :validate_string, :font_name, v; end # The inline charset property for the cell + # As far as I can tell, this is pretty much ignored. However, based on the spec it should be one of the following: + # 0  ANSI_CHARSET + # 1 DEFAULT_CHARSET + # 2 SYMBOL_CHARSET + # 77 MAC_CHARSET + # 128 SHIFTJIS_CHARSET + # 129  HANGUL_CHARSET + # 130  JOHAB_CHARSET + # 134  GB2312_CHARSET + # 136  CHINESEBIG5_CHARSET + # 161  GREEK_CHARSET + # 162  TURKISH_CHARSET + # 163  VIETNAMESE_CHARSET + # 177  HEBREW_CHARSET + # 178  ARABIC_CHARSET + # 186  BALTIC_CHARSET + # 204  RUSSIAN_CHARSET + # 222  THAI_CHARSET + # 238  EASTEUROPE_CHARSET + # 255  OEM_CHARSET # @return [String] attr_reader :charset # @see charset def charset=(v) set_run_style :validate_unsigned_int, :charset, v; end # The inline family property for the cell - # @return [String] + # @return [Integer] + # 1 Roman + # 2 Swiss + # 3 Modern + # 4 Script + # 5 Decorative attr_reader :family # @see family - def family=(v) set_run_style :validate_string, :family, v; end + def family=(v) + set_run_style :validate_family, :family, v.to_i + end # The inline bold property for the cell # @return [Boolean] @@ -165,11 +192,17 @@ module Axlsx # @see extend def extend=(v) set_run_style :validate_boolean, :extend, v; end - # The inline underline property for the cell + # The inline underline property for the cell. + # It must be one of :none, :single, :double, :singleAccounting, :doubleAccounting, true # @return [Boolean] + # @return [String] + # @note true is for backwards compatability and is reassigned to :single attr_reader :u # @see u - def u=(v) set_run_style :validate_boolean, :u, v; end + def u=(v) + v = :single if (v == true || v == 1 || v == :true || v == 'true') + set_run_style :validate_cell_u, :u, v + end # The inline color property for the cell # @return [Color] @@ -365,8 +398,8 @@ module Axlsx end # assigns the owning row for this cell. - def row=(v) DataTypeValidator.validate "Cell.row", Row, v; @row=v end - + def row=(v) @row=v end + # Determines the cell type based on the cell value. # @note This is only used when a cell is created but no :type option is specified, the following rules apply: # 1. If the value is an instance of Date, the type is set to :date @@ -411,6 +444,9 @@ module Axlsx v ? 1 : 0 else @type = :string + v.to_s + # TODO find a better way to do this as it accounts for 30% of + # processing time in benchmarking... ::CGI.escapeHTML(v.to_s) end end diff --git a/lib/axlsx/workbook/worksheet/cfvos.rb b/lib/axlsx/workbook/worksheet/cfvos.rb new file mode 100644 index 00000000..d642bfc6 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/cfvos.rb @@ -0,0 +1,15 @@ +module Axlsx + + #A collection of Cfvo objects that initializes with the required + #first two items + class Cfvos < SimpleTypedList + + def initialize + super(Cfvo) + end + + def to_xml_string(str='') + @list.each { |cfvo| cfvo.to_xml_string(str) } + end + end +end diff --git a/lib/axlsx/workbook/worksheet/col.rb b/lib/axlsx/workbook/worksheet/col.rb index 596531ab..a2658724 100644 --- a/lib/axlsx/workbook/worksheet/col.rb +++ b/lib/axlsx/workbook/worksheet/col.rb @@ -23,7 +23,7 @@ module Axlsx parse_options options end - serializable_attributes :collapsed, :hidden, :outline_level, :phonetic, :style, :width, :min, :max + serializable_attributes :collapsed, :hidden, :outline_level, :phonetic, :style, :width, :min, :max, :best_fit, :custom_width # First column affected by this 'column info' record. # @return [Integer] @@ -38,7 +38,8 @@ module Axlsx # 'Best fit' means that when numbers are typed into a cell contained in a 'best fit' column, the column width should # automatically resize to display the number. [Note: In best fit cases, column width must not be made smaller, only larger. end note] # @return [Boolean] - attr_reader :bestFit + attr_reader :best_fit + alias :bestFit :best_fit # Flag indicating if the outlining of the affected column(s) is in the collapsed state. # @return [Boolean] @@ -66,7 +67,8 @@ module Axlsx attr_reader :width # @return [Boolean] - attr_reader :customWidth + attr_reader :custom_width + alias :customWidth :custom_width # @see Col#collapsed def collapsed=(v) @@ -103,7 +105,7 @@ module Axlsx # @see Col#width def width=(v) Axlsx.validate_unsigned_numeric(v) unless v == nil - @customWidth = @bestFit = v != nil + @custom_width = @best_fit = v != nil @width = v end diff --git a/lib/axlsx/workbook/worksheet/color_scale.rb b/lib/axlsx/workbook/worksheet/color_scale.rb index 11f97e21..287e6869 100644 --- a/lib/axlsx/workbook/worksheet/color_scale.rb +++ b/lib/axlsx/workbook/worksheet/color_scale.rb @@ -7,34 +7,70 @@ module Axlsx # @see ConditionalFormattingRule#initialize class ColorScale + class << self + + # These are the default conditional formatting value objects + # that define a two tone color gradient. + def default_cfvos + [{:type => :min, :val => 0, :color => 'FFFF7128'}, + {:type => :max, :val => 0, :color => 'FFFFEF9C'}] + end + + # A builder for two tone color gradient + # @example + # # this creates a two tone color scale + # color_scale = Axlsx::ColorScale.two_tone + # @see examples/example.rb conditional formatting examples. + def two_tone + self.new + end + + # A builder for three tone color gradient + # @example + # #this creates a three tone color scale + # color_scale = Axlsx::ColorScale.three_tone + # @see examples/example.rb conditional formatting examples. + def three_tone + self.new({:type => :min, :val => 0, :color => 'FFF8696B'}, + {:type => :percent, :val => '50', :color => 'FFFFEB84'}, + {:type => :max, :val => 0, :color => 'FF63BE7B'}) + end + end # A simple typed list of cfvos # @return [SimpleTypedList] # @see Cfvo - attr_reader :value_objects + def value_objects + @value_objects ||= Cfvos.new + end # A simple types list of colors # @return [SimpleTypedList] # @see Color - attr_reader :colors + def colors + @colors ||= SimpleTypedList.new Color + end # creates a new ColorScale object. - # This method will yield it self so you can alter the properites of the defauls conditional formating value object (cfvo and colors - # Two value objects and two colors are created on initialization and cannot be deleted. # @see Cfvo # @see Color - def initialize - initialize_value_objects - initialize_colors + # @example + # color_scale = Axlsx::ColorScale.new({:type => :num, :val => 0.55, :color => 'fff7696c'}) + def initialize(*cfvos) + initialize_default_cfvos(cfvos) yield self if block_given? end # adds a new cfvo / color pair to the color scale and returns a hash containing # a reference to the newly created cfvo and color objects so you can alter the default properties. # @return [Hash] a hash with :cfvo and :color keys referencing the newly added objects. + # @param [Hash] options options for the new cfvo and color objects + # @option [Symbol] type The type of cfvo you to add + # @option [Any] val The value of the cfvo to add + # @option [String] The rgb color for the cfvo def add(options={}) - @value_objects << Cfvo.new(:type => options[:type] || :min, :val => options[:val] || 0) - @colors << Color.new(:rgb => options[:color] || "FF000000") - {:cfvo => @value_objects.last, :color => @colors.last} + value_objects << Cfvo.new(:type => options[:type] || :min, :val => options[:val] || 0) + colors << Color.new(:rgb => options[:color] || "FF000000") + {:cfvo => value_objects.last, :color => colors.last} end @@ -42,8 +78,8 @@ module Axlsx # @param [Integer] index The index of the cfvo and color object to delete # @note you cannot remove the first two cfvo and color pairs def delete_at(index=2) - @value_objects.delete_at index - @colors.delete_at index + value_objects.delete_at index + colors.delete_at index end # Serialize this color_scale object data to an xml string @@ -51,26 +87,24 @@ module Axlsx # @return [String] def to_xml_string(str = '') str << '<colorScale>' - @value_objects.each { |cfvo| cfvo.to_xml_string(str) } - @colors.each { |color| color.to_xml_string(str) } + value_objects.to_xml_string(str) + colors.each { |color| color.to_xml_string(str) } str << '</colorScale>' end private - - # creates the initial cfvo objects - def initialize_value_objects - @value_objects = SimpleTypedList.new Cfvo - @value_objects.concat [Cfvo.new(:type => :min, :val => 0), Cfvo.new(:type => :max, :val => 0)] - @value_objects.lock + # There has got to be cleaner way of merging these arrays. + def initialize_default_cfvos(user_cfvos) + defaults = self.class.default_cfvos + user_cfvos.each_with_index do |cfvo, index| + if index < defaults.size + cfvo = defaults[index].merge(cfvo) + end + add cfvo + end + while colors.size < defaults.size + add defaults[colors.size - 1] + end end - - # creates the initial color objects - def initialize_colors - @colors = SimpleTypedList.new Color - @colors.concat [Color.new(:rgb => "FFFF0000"), Color.new(:rgb => "FF0000FF")] - @colors.lock - end - end end diff --git a/lib/axlsx/workbook/worksheet/data_bar.rb b/lib/axlsx/workbook/worksheet/data_bar.rb index bca24bf7..e67f76a4 100644 --- a/lib/axlsx/workbook/worksheet/data_bar.rb +++ b/lib/axlsx/workbook/worksheet/data_bar.rb @@ -10,21 +10,31 @@ module Axlsx include Axlsx::OptionsParser include Axlsx::SerializedAttributes + class << self + # This differs from ColorScale. There must be exactly two cfvos one color + def default_cfvos + [{:type => :min, :val => "0"}, + {:type => :max, :val => "0"}] + end + end + # Creates a new data bar conditional formatting object + # @param [Hash] options # @option options [Integer] minLength # @option options [Integer] maxLength # @option options [Boolean] showValue # @option options [String] color - the rbg value used to color the bars - def initialize(options = {}) - @minLength = 10 - @maxLength = 90 - @showValue = true - initialize_value_objects + # @param [Array] cfvos hashes defining the gradient interpolation points for this formatting. + def initialize(options = {}, *cfvos) + @min_length = 10 + @max_length = 90 + @show_value = true parse_options options + initialize_cfvos(cfvos) yield self if block_given? end - serializable_attributes :minLength, :maxLength, :showValue + serializable_attributes :min_length, :max_length, :show_value # instance values that must be serialized as their own elements - e.g. not attributes. CHILD_ELEMENTS = [:value_objects, :color] @@ -33,24 +43,29 @@ module Axlsx # The minimum length of the data bar, as a percentage of the cell width. # The default value is 10 # @return [Integer] - attr_reader :minLength + attr_reader :min_length + alias :minLength :min_length # maxLength attribute # The maximum length of the data bar, as a percentage of the cell width. # The default value is 90 # @return [Integer] - attr_reader :maxLength + attr_reader :max_length + alias :maxLength :max_length # maxLength attribute # Indicates whether to show the values of the cells on which this data bar is applied. # The default value is true # @return [Boolean] - attr_reader :showValue + attr_reader :show_value + alias :showValue :show_value # A simple typed list of cfvos # @return [SimpleTypedList] # @see Cfvo - attr_reader :value_objects + def value_objects + @value_objects ||= Cfvos.new + end # color # the color object used in the data bar formatting @@ -60,20 +75,33 @@ module Axlsx end # @see minLength - def minLength=(v); Axlsx.validate_unsigned_int(v); @minLength = v end - # @see maxLength - def maxLength=(v); Axlsx.validate_unsigned_int(v); @maxLength = v end - - # @see showValue - def showValue=(v); Axlsx.validate_boolean(v); @showValue = v end - - # Sets the color for the data bars. - # @param [Color|String] v The color object, or rgb string value to apply - def color=(v) - @color = v if v.is_a? Color - self.color.rgb = v if v.is_a? String - @color + def min_length=(v) + Axlsx.validate_unsigned_int(v) + @min_length = v end + alias :minLength= :min_length= + + # @see maxLength + def max_length=(v) + Axlsx.validate_unsigned_int(v) + @max_length = v + end + alias :maxLength= :max_length= + + # @see showValue + def show_value=(v) + Axlsx.validate_boolean(v) + @show_value = v + end + alias :showValue= :show_value= + + # Sets the color for the data bars. + # @param [Color|String] v The color object, or rgb string value to apply + def color=(v) + @color = v if v.is_a? Color + self.color.rgb = v if v.is_a? String + @color + end # Serialize this object to an xml string # @param [String] str @@ -82,19 +110,22 @@ module Axlsx str << '<dataBar ' serialized_attributes str str << '>' - @value_objects.each { |cfvo| cfvo.to_xml_string(str) } + value_objects.to_xml_string(str) self.color.to_xml_string(str) str << '</dataBar>' end private - # Initalize the simple typed list of value objects - # I am keeping this private for now as I am not sure what impact changes to the required two cfvo objects will do. - def initialize_value_objects - @value_objects = SimpleTypedList.new Cfvo - @value_objects.concat [Cfvo.new(:type => :min, :val => 0), Cfvo.new(:type => :max, :val => 0)] - @value_objects.lock + def initialize_cfvos(cfvos) + self.class.default_cfvos.each_with_index.map do |default, index| + if index < cfvos.size + value_objects << Cfvo.new(default.merge(cfvos[index])) + else + value_objects << Cfvo.new(default) + end + end end + end end diff --git a/lib/axlsx/workbook/worksheet/header_footer.rb b/lib/axlsx/workbook/worksheet/header_footer.rb new file mode 100644 index 00000000..a40ad853 --- /dev/null +++ b/lib/axlsx/workbook/worksheet/header_footer.rb @@ -0,0 +1,54 @@ +module Axlsx + # Header/Footer options for printing a worksheet. All settings are optional. + # + # Headers and footers are generated using a string which is a combination + # of plain text and control characters. A fairly comprehensive list of control + # characters can be found here: + # https://github.com/randym/axlsx/blob/master/notes_on_header_footer.md + # + # @note The recommended way of managing header/footers is via Worksheet#header_footer + # @see Worksheet#initialize + class HeaderFooter + + include Axlsx::OptionsParser + include Axlsx::SerializedAttributes + include Axlsx::Accessors + + # Creates a new HeaderFooter object + # @option options [String] odd_header The content for headers on odd numbered pages. + # @option options [String] odd_footer The content for footers on odd numbered pages. + # @option options [String] even_header The content for headers on even numbered pages. + # @option options [String] even_footer The content for footers on even numbered pages. + # @option options [String] first_header The content for headers on even numbered pages. + # @option options [String] first_footer The content for footers on even numbered pages. + # @option options [Boolean] different_odd_even Setting this to true will show different headers/footers on odd and even pages. When false, the odd headers/footers are used on each page. (Default: false) + # @option options [Boolean] different_first If true, will use the first header/footer on page 1. Otherwise, the odd header/footer is used. + def initialize(options = {}) + parse_options options + end + + serializable_attributes :different_odd_even, :different_first + serializable_element_attributes :odd_header, :odd_footer, :even_header, :even_footer, :first_header, :first_footer + string_attr_accessor :odd_header, :odd_footer, :even_header, :even_footer, :first_header, :first_footer + boolean_attr_accessor :different_odd_even, :different_first + + # Set some or all header/footers at once. + # @param [Hash] options The header/footer options to set (possible keys are :odd_header, :odd_footer, :even_header, :even_footer, :first_header, :first_footer, :different_odd_even, and :different_first). + def set(options) + parse_options options + end + + # Serializes the header/footer object. + # @param [String] str + # @return [String] + def to_xml_string(str = '') + str << "<headerFooter " + serialized_attributes str + str << ">" + serialized_element_attributes(str) do |value| + value = ::CGI.escapeHTML(value) + end + str << "</headerFooter>" + end + end +end diff --git a/lib/axlsx/workbook/worksheet/pane.rb b/lib/axlsx/workbook/worksheet/pane.rb index 6ac87f13..a34b3140 100644 --- a/lib/axlsx/workbook/worksheet/pane.rb +++ b/lib/axlsx/workbook/worksheet/pane.rb @@ -1,4 +1,3 @@ -# encoding: UTF-8 module Axlsx # Pane options for a worksheet. # @@ -132,7 +131,7 @@ module Axlsx private def finalize - if @state == 'frozen' && @top_left_cell.nil? + if @state == 'frozen' && @top_left_cell.nil? row = @y_split || 0 column = @x_split || 0 @top_left_cell = "#{('A'..'ZZ').to_a[column]}#{row+1}" diff --git a/lib/axlsx/workbook/worksheet/protected_range.rb b/lib/axlsx/workbook/worksheet/protected_range.rb index c58b895d..15904292 100644 --- a/lib/axlsx/workbook/worksheet/protected_range.rb +++ b/lib/axlsx/workbook/worksheet/protected_range.rb @@ -41,7 +41,6 @@ module Axlsx # our output to that object. Use this - it helps limit the number of # objects created during serialization def to_xml_string(str="") - attrs = self.instance_values.reject{ |key, value| value == nil } str << '<protectedRange ' serialized_attributes str str << '/>' diff --git a/lib/axlsx/workbook/worksheet/row.rb b/lib/axlsx/workbook/worksheet/row.rb index b10d8403..1b64b412 100644 --- a/lib/axlsx/workbook/worksheet/row.rb +++ b/lib/axlsx/workbook/worksheet/row.rb @@ -5,59 +5,14 @@ module Axlsx # @see Worksheet#add_row class Row + include SerializedAttributes + include Accessors # No support is provided for the following attributes # spans # thickTop # thickBottom - - # A list of serilizable attributes. - # @note height(ht) and customHeight are manages separately for now. Have a look at Row#height - SERIALIZABLE_ATTRIBUTES = [:hidden, :outlineLevel, :collapsed, :s, :customFormat, :ph] - - # The worksheet this row belongs to - # @return [Worksheet] - attr_reader :worksheet - - # The cells this row holds - # @return [SimpleTypedList] - attr_reader :cells - - # Row height measured in point size. There is no margin padding on row height. - # @return [Float] - attr_reader :height - - # Flag indicating if the outlining of row. - # @return [Boolean] - attr_reader :collapsed - - # Flag indicating if the the row is hidden. - # @return [Boolean] - attr_reader :hidden - - # Outlining level of the row, when outlining is on - # @return [Integer] - attr_reader :outlineLevel - - # The style applied ot the row. This affects the entire row. - # @return [Integer] - attr_reader :s - - # indicates that a style has been applied directly to the row via Row#s - # @return [Boolean] - attr_reader :customFormat - - # indicates if the row should show phonetic - # @return [Boolean] - attr_reader :ph - - # NOTE removing this from the api as it is actually incorrect. - # having a method to style a row's cells is fine, but it is not an attribute on the row. - # The proper attribute is ':s' - # attr_reader style - # - - # Creates a new row. New Cell objects are created based on the values, types and style options. + # Creates a new row. New Cell objects are created based on the values, types and style options. # A new cell is created for each item in the values array. style and types options are applied as follows: # If the types option is defined and is a symbol it is applied to all the cells created. # If the types option is an array, cell types are applied by index for each cell @@ -73,7 +28,7 @@ module Axlsx # @see Row#array_to_cells # @see Cell def initialize(worksheet, values=[], options={}) - @height = nil + @ht = nil self.worksheet = worksheet @cells = SimpleTypedList.new Cell @worksheet.rows << self @@ -81,29 +36,48 @@ module Axlsx array_to_cells(values, options) end - # @see Row#collapsed - def collapsed=(v) - Axlsx.validate_boolean(v) - @collapsed = v - end + # A list of serializable attributes. + serializable_attributes :hidden, :outline_level, :collapsed, :custom_format, :s, :ph, :custom_height, :ht + + # Boolean row attribute accessors + boolean_attr_accessor :hidden, :collapsed, :custom_format, :ph, :custom_height - # @see Row#hidden - def hidden=(v) - Axlsx.validate_boolean(v) - @hidden = v + # The worksheet this row belongs to + # @return [Worksheet] + attr_reader :worksheet + + # The cells this row holds + # @return [SimpleTypedList] + attr_reader :cells + + # Row height measured in point size. There is no margin padding on row height. + # @return [Float] + def height + @ht end - - # @see Row#ph - def ph=(v) Axlsx.validate_boolean(v); @ph = v end + + # Outlining level of the row, when outlining is on + # @return [Integer] + attr_reader :outline_level + alias :outlineLevel :outline_level + + # The style applied ot the row. This affects the entire row. + # @return [Integer] + attr_reader :s # @see Row#s - def s=(v) Axlsx.validate_unsigned_numeric(v); @s = v; @customFormat = true end + def s=(v) + Axlsx.validate_unsigned_numeric(v) + @custom_format = true + @s = v + end # @see Row#outline - def outlineLevel=(v) + def outline_level=(v) Axlsx.validate_unsigned_numeric(v) - @outlineLevel = v + @outline_level = v end + alias :outlineLevel= :outline_level= # The index of this row in the worksheet # @return [Integer] @@ -116,18 +90,11 @@ module Axlsx # @param [String] str The string this rows xml will be appended to. # @return [String] def to_xml_string(r_index, str = '') - str << '<row r="' << (r_index + 1 ).to_s << '" ' - instance_values.select { |key, value| SERIALIZABLE_ATTRIBUTES.include? key.to_sym }.each do |key, value| - str << key << '="' << value.to_s << '" ' - end - if custom_height? - str << 'customHeight="1" ht="' << height.to_s << '">' - else - str << '>' - end + str << '<row ' + serialized_attributes(str, { :r => r_index + 1 }) + str << '>' @cells.each_with_index { |cell, c_index| cell.to_xml_string(r_index, c_index, str) } str << '</row>' - str end # Adds a singel sell to the row based on the data provided and updates the worksheet's autofit data. @@ -154,13 +121,13 @@ module Axlsx end # @see height - def height=(v); Axlsx::validate_unsigned_numeric(v) unless v.nil?; @height = v end - - # true if the row height has been manually set - # @return [Boolean] - # @see #height - def custom_height? - @height != nil + def height=(v) + Axlsx::validate_unsigned_numeric(v) + unless v.nil? + @ht = v + @custom_height = true + end + @ht end private diff --git a/lib/axlsx/workbook/worksheet/sheet_protection.rb b/lib/axlsx/workbook/worksheet/sheet_protection.rb index a472ec55..5ff888ea 100644 --- a/lib/axlsx/workbook/worksheet/sheet_protection.rb +++ b/lib/axlsx/workbook/worksheet/sheet_protection.rb @@ -45,12 +45,6 @@ module Axlsx # @return [String] attr_reader :salt_value - # If 1 or true then sorting should not be allowed when the sheet is protected. - # If 0 or false then sorting should be allowed when the sheet is protected. - # @return [Boolean] - # default true - attr_reader :sort - # Password hash # @return [String] # default nil diff --git a/lib/axlsx/workbook/worksheet/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index 6a32e881..8a595b2d 100644 --- a/lib/axlsx/workbook/worksheet/worksheet.rb +++ b/lib/axlsx/workbook/worksheet/worksheet.rb @@ -9,7 +9,8 @@ module Axlsx # This is used for autowidth calculations # @return [String] def self.thin_chars - @thin_chars ||= "^.acefijklrstxyzFIJL()-" + # removed 'e' and 'y' from this list - as a GUESS + @thin_chars ||= "^.acfijklrstxzFIJL()-" end # Creates a new worksheet. @@ -18,6 +19,7 @@ module Axlsx # @option options [String] name The name of this worksheet. # @option options [Hash] page_margins A hash containing page margins for this worksheet. @see PageMargins # @option options [Hash] print_options A hash containing print options for this worksheet. @see PrintOptions + # @option options [Hash] header_footer A hash containing header/footer options for this worksheet. @see HeaderFooter # @option options [Boolean] show_gridlines indicates if gridlines should be shown for this sheet. def initialize(wb, options={}) self.workbook = wb @@ -34,6 +36,7 @@ module Axlsx @page_margins = PageMargins.new options[:page_margins] if options[:page_margins] @page_setup = PageSetup.new options[:page_setup] if options[:page_setup] @print_options = PrintOptions.new options[:print_options] if options[:print_options] + @header_footer = HeaderFooter.new options[:header_footer] if options[:header_footer] end # The name of the worksheet @@ -41,7 +44,7 @@ module Axlsx def name @name ||= "Sheet" + (index+1).to_s end - + # The sheet calculation properties # @return [SheetCalcPr] def sheet_calc_pr @@ -104,7 +107,7 @@ module Axlsx # An range that excel will apply an autfilter to "A1:B3" # This will turn filtering on for the cells in the range. # The first row is considered the header, while subsequent rows are considerd to be data. - # @return String + # @return String def auto_filter @auto_filter ||= AutoFilter.new self end @@ -193,6 +196,21 @@ module Axlsx @print_options end + # Options for headers and footers. + # @example + # wb = Axlsx::Package.new.workbook + # # would generate something like: "file.xlsx : sheet_name 2 of 7 date with timestamp" + # header = {:different_odd_ => false, :odd_header => "&L&F : &A&C&Pof%N%R%D %T"} + # ws = wb.add_worksheet :header_footer => header + # + # @see HeaderFooter#initialize + # @return [HeaderFooter] + def header_footer + @header_footer ||= HeaderFooter.new + yield @header_footer if block_given? + @header_footer + end + # convinience method to access all cells in this worksheet # @return [Array] cells def cells @@ -237,7 +255,7 @@ module Axlsx # Indicates if gridlines should be shown in the sheet. # This is true by default. # @return [Boolean] - # @deprecated Use {SheetView#show_grid_lines=} instead. + # @deprecated Use SheetView#show_grid_lines= instead. def show_gridlines=(v) warn('axlsx::DEPRECIATED: Worksheet#show_gridlines= has been depreciated. This value can be set over SheetView#show_grid_lines=.') Axlsx::validate_boolean v @@ -246,7 +264,7 @@ module Axlsx # @see selected # @return [Boolean] - # @deprecated Use {SheetView#tab_selected=} instead. + # @deprecated Use SheetView#tab_selected= instead. def selected=(v) warn('axlsx::DEPRECIATED: Worksheet#selected= has been depreciated. This value can be set over SheetView#tab_selected=.') Axlsx::validate_boolean v @@ -255,7 +273,7 @@ module Axlsx # Indicates if the worksheet should show gridlines or not # @return Boolean - # @deprecated Use {SheetView#show_grid_lines} instead. + # @deprecated Use SheetView#show_grid_lines instead. def show_gridlines warn('axlsx::DEPRECIATED: Worksheet#show_gridlines has been depreciated. This value can get over SheetView#show_grid_lines.') sheet_view.show_grid_lines @@ -265,7 +283,7 @@ module Axlsx # It is possible to have more than one worksheet selected, however it might cause issues # in some older versions of excel when using copy and paste. # @return Boolean - # @deprecated Use {SheetView#tab_selected} instead. + # @deprecated Use SheetView#tab_selected instead. def selected warn('axlsx::DEPRECIATED: Worksheet#selected has been depreciated. This value can get over SheetView#tab_selected.') sheet_view.tab_selected @@ -280,7 +298,7 @@ module Axlsx # The name of the worksheet # The name of a worksheet must be unique in the workbook, and must not exceed 31 characters - # @param [String] name + # @param [String] name def name=(name) validate_sheet_name name @name=Axlsx::coder.encode(name) @@ -388,7 +406,7 @@ module Axlsx cf = ConditionalFormatting.new( :sqref => cells ) cf.add_rules rules conditional_formattings << cf - conditional_formattings + conditional_formattings end # Add data validation to this worksheet. @@ -513,7 +531,7 @@ module Axlsx def sanitize(str) str.gsub(CONTROL_CHAR_REGEX, '') end - + # The worksheet relationships. This is managed automatically by the worksheet # @return [Relationships] def relationships @@ -570,12 +588,12 @@ module Axlsx private - + def validate_sheet_name(name) DataTypeValidator.validate "Worksheet.name", String, name raise ArgumentError, (ERR_SHEET_NAME_TOO_LONG % name) if name.size > 31 raise ArgumentError, (ERR_SHEET_NAME_COLON_FORBIDDEN % name) if name.include? ':' - name = Axlsx::coder.encode(name) + name = Axlsx::coder.encode(name) sheet_names = @workbook.worksheets.map { |s| s.name } raise ArgumentError, (ERR_DUPLICATE_SHEET_NAME % name) if sheet_names.include?(name) end @@ -586,7 +604,7 @@ module Axlsx sheet_data, sheet_calc_pr, @sheet_protection, protected_ranges, auto_filter, merged_cells, conditional_formattings, data_validations, hyperlinks, print_options, page_margins, - page_setup, worksheet_drawing, worksheet_comments, + page_setup, header_footer, worksheet_drawing, worksheet_comments, tables] end @@ -606,7 +624,7 @@ module Axlsx # @see Worksheet#protect_range # @return [SimpleTypedList] The protected ranges for this worksheet def protected_ranges - @protected_ranges ||= ProtectedRanges.new self + @protected_ranges ||= ProtectedRanges.new self # SimpleTypedList.new ProtectedRange end @@ -619,7 +637,7 @@ module Axlsx # data validations array # @return [Array] def data_validations - @data_validations ||= DataValidations.new self + @data_validations ||= DataValidations.new self end # merged cells array diff --git a/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb index 830698cc..d352ec90 100644 --- a/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb +++ b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb @@ -73,7 +73,7 @@ module Axlsx # r:id should only be specified for external targets. # @return [Hash] def location_or_id - @target == :external ? { :"r:id" => id } : { :"location" => location } + @target == :external ? { :"r:id" => id } : { :location => Axlsx::coder.encode(location) } end end end diff --git a/test/benchmark.rb b/test/benchmark.rb index d61aa7b2..33bf7d2c 100644 --- a/test/benchmark.rb +++ b/test/benchmark.rb @@ -4,11 +4,10 @@ $:.unshift "#{File.dirname(__FILE__)}/../lib" require 'axlsx' require 'csv' require 'benchmark' - row = [] input = (32..126).to_a.pack('U*').chars.to_a 20.times { row << input.shuffle.join} -times = 2000 +times = 3000 Benchmark.bmbm(30) do |x| x.report('axlsx_noautowidth') { diff --git a/test/content_type/tc_default.rb b/test/content_type/tc_default.rb index 2fe0d965..d765a904 100644 --- a/test/content_type/tc_default.rb +++ b/test/content_type/tc_default.rb @@ -3,14 +3,6 @@ require 'tc_helper.rb' class TestDefault < Test::Unit::TestCase - def test_initialization_requires_Extension_and_ContentType - assert_raise(ArgumentError, "raises argument error if Extension and/or ContentType are not specified") { Axlsx::Default.new } - assert_raise(ArgumentError, "raises argument error if Extension and/or ContentType are not specified") { Axlsx::Default.new :Extension=>"xml" } - assert_raise(ArgumentError, "raises argument error if Extension and/or ContentType are not specified") { Axlsx::Default.new :ContentType=>"asdf" } - - assert_nothing_raised {Axlsx::Default.new :Extension=>"foo", :ContentType=>Axlsx::XML_CT} - - end def test_content_type_restriction assert_raise(ArgumentError, "raises argument error if invlalid ContentType is") { Axlsx::Default.new :ContentType=>"asdf" } end @@ -20,8 +12,5 @@ class TestDefault < Test::Unit::TestCase doc = Nokogiri::XML(type.to_xml_string) assert_equal(doc.xpath("Default[@ContentType='#{Axlsx::XML_CT}']").size, 1) assert_equal(doc.xpath("Default[@Extension='xml']").size, 1) - end - - end diff --git a/test/content_type/tc_override.rb b/test/content_type/tc_override.rb index 920f1667..d9bec90c 100644 --- a/test/content_type/tc_override.rb +++ b/test/content_type/tc_override.rb @@ -1,16 +1,6 @@ -# -*- coding: utf-8 -*- require 'tc_helper.rb' - class TestOverride < Test::Unit::TestCase - def test_initialization_requires_Extension_and_ContentType - err = "requires PartName and ContentType options" - assert_raise(ArgumentError, err) { Axlsx::Override.new } - assert_raise(ArgumentError, err) { Axlsx::Override.new :PartName=>"xml" } - assert_raise(ArgumentError, err) { Axlsx::Override.new :ContentType=>"asdf" } - assert_nothing_raised {Axlsx::Override.new :PartName=>"foo", :ContentType=>Axlsx::CHART_CT} - end - def test_content_type_restriction assert_raise(ArgumentError, "requires known content type") { Axlsx::Override.new :ContentType=>"asdf" } end @@ -20,8 +10,5 @@ class TestOverride < Test::Unit::TestCase doc = Nokogiri::XML(type.to_xml_string) assert_equal(doc.xpath("Override[@ContentType='#{Axlsx::CHART_CT}']").size, 1) assert_equal(doc.xpath("Override[@PartName='somechart.xml']").size, 1) - end - - end diff --git a/test/profile.rb b/test/profile.rb index fa81577b..0190dc16 100644 --- a/test/profile.rb +++ b/test/profile.rb @@ -1,31 +1,22 @@ #!/usr/bin/env ruby -s -# -*- coding: utf-8 -*- # Usage: # > ruby test/profile.rb -# > pprof.rb --gif /tmp/axlsx_noautowidth > /tmp/axlsx_noautowidth.gif +# > pprof.rb --gif /tmp/axlsx > /tmp/axlsx.gif # > open /tmp/axlsx_noautowidth.gif $:.unshift "#{File.dirname(__FILE__)}/../lib" require 'axlsx' -require 'csv' - -# require 'benchmark' require 'perftools' 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_noautowidth") do +PerfTools::CpuProfiler.start("/tmp/axlsx") do p = Axlsx::Package.new - p.use_autowidth = false - p.use_shared_strings = true - wb = p.workbook - - #A Simple Workbook - - wb.add_worksheet do |sheet| + p.workbook.add_worksheet do |sheet| times.times do sheet << row end diff --git a/test/rels/tc_relationship.rb b/test/rels/tc_relationship.rb index ef995449..32b9e4a1 100644 --- a/test/rels/tc_relationship.rb +++ b/test/rels/tc_relationship.rb @@ -18,4 +18,9 @@ class TestRelationships < Test::Unit::TestCase assert_nothing_raised { Axlsx::Relationship.new( 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)) + assert_equal(doc.xpath("//Relationship[@Target='http://example.com?foo=1&bar=2']").size, 1) + end end diff --git a/test/tc_helper.rb b/test/tc_helper.rb index 84e834c4..34f7f22d 100644 --- a/test/tc_helper.rb +++ b/test/tc_helper.rb @@ -1,4 +1,8 @@ $LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'cover_me' unless RUBY_VERSION == '1.8.7' +require 'simplecov' +SimpleCov.start do + add_filter "/test/" +end + require 'test/unit' require "axlsx.rb" diff --git a/test/util/tc_validators.rb b/test/util/tc_validators.rb index 1c3e173c..de896f3d 100644 --- a/test/util/tc_validators.rb +++ b/test/util/tc_validators.rb @@ -159,6 +159,20 @@ class TestValidators < Test::Unit::TestCase assert_raise(ArgumentError) { Axlsx.validate_split_state_type 0 } end + def test_validate_family + assert_raise(ArgumentError) { Axlsx.validate_family 0 } + (1..5).each do |item| + assert_nothing_raised { Axlsx.validate_family item } + end + end + + def test_validate_u + assert_raise(ArgumentError) { Axlsx.validate_cell_u :hoge } + [:none, :single, :double, :singleAccounting, :doubleAccounting].each do |sym| + assert_nothing_raised { Axlsx.validate_cell_u sym } + end + end + def test_range_validation # exclusive assert_raise(ArgumentError) { Axlsx::RangeValidator.validate('foo', 1, 10, 10, false) } diff --git a/test/workbook/tc_workbook.rb b/test/workbook/tc_workbook.rb index f10ad24a..1a9b9669 100644 --- a/test/workbook/tc_workbook.rb +++ b/test/workbook/tc_workbook.rb @@ -49,6 +49,14 @@ class TestWorkbook < Test::Unit::TestCase assert_equal(@wb.worksheets.first, ws, "the worksheet returned is the worksheet added") assert_equal(ws.name, "bob", "name option gets passed to worksheet") end + + def test_insert_worksheet + @wb.add_worksheet(:name => 'A') + @wb.add_worksheet(:name => 'B') + ws3 = @wb.insert_worksheet(0, :name => 'C') + assert_equal(ws3.name, @wb.worksheets.first.name) + end + def test_relationships #current relationship size is 1 due to style relation assert(@wb.relationships.size == 1) diff --git a/test/workbook/worksheet/tc_cell.rb b/test/workbook/worksheet/tc_cell.rb index 0b332d86..b237077c 100644 --- a/test/workbook/worksheet/tc_cell.rb +++ b/test/workbook/worksheet/tc_cell.rb @@ -162,9 +162,12 @@ class TestCell < Test::Unit::TestCase end def test_u + @c.type = :string assert_raise(ArgumentError) { @c.u = -1.1 } - assert_nothing_raised { @c.u = false } - assert_equal(@c.u, false) + assert_nothing_raised { @c.u = :single } + assert_equal(@c.u, :single) + doc = Nokogiri::XML(@c.to_xml_string(1,1)) + assert(doc.xpath('//u[@val="single"]')) end def test_i @@ -187,8 +190,8 @@ class TestCell < Test::Unit::TestCase def test_family assert_raise(ArgumentError) { @c.family = -1.1 } - assert_nothing_raised { @c.family = "Who knows!" } - assert_equal(@c.family, "Who knows!") + assert_nothing_raised { @c.family = 5 } + assert_equal(@c.family, 5) end def test_b @@ -251,6 +254,8 @@ class TestCell < Test::Unit::TestCase end def test_to_xml_string_with_run + # Actually quite a number of similar run styles + # but the processing should be the same @c.b = true @c.type = :string @c.value = "a" @@ -259,6 +264,7 @@ class TestCell < Test::Unit::TestCase c_xml = Nokogiri::XML(@c.to_xml_string(1,1)) assert(c_xml.xpath("//b")) end + def test_to_xml_string_formula p = Axlsx::Package.new ws = p.workbook.add_worksheet do |sheet| @@ -285,6 +291,7 @@ class TestCell < Test::Unit::TestCase sz = @c.send(:font_size) assert_equal(sz, 52) end + def test_cell_with_sz @c.sz = 25 @@ -293,6 +300,7 @@ class TestCell < Test::Unit::TestCase def test_to_xml # TODO This could use some much more stringent testing related to the xml content generated! @ws.add_row [Time.now, Date.today, true, 1, 1.0, "text", "=sum(A1:A2)"] + @ws.rows.last.cells[5].u = true schema = Nokogiri::XML::Schema(File.open(Axlsx::SML_XSD)) doc = Nokogiri::XML(@ws.to_xml_string) errors = [] diff --git a/test/workbook/worksheet/tc_col.rb b/test/workbook/worksheet/tc_col.rb index 9621eb14..c0c5e64b 100644 --- a/test/workbook/worksheet/tc_col.rb +++ b/test/workbook/worksheet/tc_col.rb @@ -58,6 +58,16 @@ class TestCol < Test::Unit::TestCase assert_nothing_raised(ArgumentError, 'phonetic must be boolean(ish)') { @col.phonetic = true } end + def test_to_xml_string + @col.width = 100 + doc = Nokogiri::XML(@col.to_xml_string) + assert_equal(1, doc.xpath("//col [@bestFit='#{@col.best_fit}']").size) + assert_equal(1, doc.xpath("//col [@max=#{@col.max}]").size) + assert_equal(1, doc.xpath("//col [@min=#{@col.min}]").size) + assert_equal(1, doc.xpath("//col [@width=#{@col.width}]").size) + assert_equal(1, doc.xpath("//col [@customWidth='#{@col.custom_width}']").size) + end + def test_style assert_equal(@col.style, nil) @col.style = 1 diff --git a/test/workbook/worksheet/tc_color_scale.rb b/test/workbook/worksheet/tc_color_scale.rb index d7e66fcf..a0d75fab 100644 --- a/test/workbook/worksheet/tc_color_scale.rb +++ b/test/workbook/worksheet/tc_color_scale.rb @@ -5,6 +5,37 @@ class TestColorScale < Test::Unit::TestCase @color_scale = Axlsx::ColorScale.new end + def test_three_tone + color_scale = Axlsx::ColorScale.three_tone + assert_equal 3, color_scale.value_objects.size + assert_equal 3, color_scale.colors.size + end + + def test_two_tone + color_scale = Axlsx::ColorScale.two_tone + assert_equal 2, color_scale.value_objects.size + assert_equal 2, color_scale.colors.size + end + def test_default_cfvo + first = Axlsx::ColorScale.default_cfvos.first + second = Axlsx::ColorScale.default_cfvos.last + assert_equal 'FFFF7128', first[:color] + assert_equal :min,first[:type] + assert_equal 0, first[:val] + + assert_equal 'FFFFEF9C', second[:color] + assert_equal :max, second[:type] + assert_equal 0, second[:val] + end + + def test_partial_default_cfvo_override + first_def = {:type => :percent, :val => "10.0", :color => 'FF00FF00'} + color_scale = Axlsx::ColorScale.new(first_def) + assert_equal color_scale.value_objects.first.val, first_def[:val] + assert_equal color_scale.value_objects.first.type, first_def[:type] + assert_equal color_scale.colors.first.rgb, first_def[:color] + end + def test_add @color_scale.add :type => :max, :val => 5, :color => "FFDEDEDE" assert_equal(@color_scale.value_objects.size,3) @@ -12,8 +43,6 @@ class TestColorScale < Test::Unit::TestCase end def test_delete_at - assert_raise(ArgumentError, "minimum two are protected") { @color_scale.delete_at 0 } - assert_raise(ArgumentError, "minimum two are protected") { @color_scale.delete_at 1 } @color_scale.add :type => :max, :val => 5, :color => "FFDEDEDE" assert_nothing_raised {@color_scale.delete_at 2} assert_equal(@color_scale.value_objects.size,2) diff --git a/test/workbook/worksheet/tc_data_bar.rb b/test/workbook/worksheet/tc_data_bar.rb index af803f23..a6194de9 100644 --- a/test/workbook/worksheet/tc_data_bar.rb +++ b/test/workbook/worksheet/tc_data_bar.rb @@ -11,6 +11,13 @@ class TestDataBar < Test::Unit::TestCase assert_equal @data_bar.showValue, true end + def test_override_default_cfvos + data_bar = Axlsx::DataBar.new({:color => 'FF00FF00'}, {:type => :min, :val => "20"}) + assert_equal("20", data_bar.value_objects.first.val) + assert_equal("0", data_bar.value_objects.last.val) + end + + def test_minLength assert_raise(ArgumentError) { @data_bar.minLength = :invalid_type } assert_nothing_raised { @data_bar.minLength = 0} diff --git a/test/workbook/worksheet/tc_header_footer.rb b/test/workbook/worksheet/tc_header_footer.rb new file mode 100644 index 00000000..4d78aa9d --- /dev/null +++ b/test/workbook/worksheet/tc_header_footer.rb @@ -0,0 +1,151 @@ +require 'tc_helper' + +class TestHeaderFooter < Test::Unit::TestCase + + def setup + @p = Axlsx::Package.new + ws = @p.workbook.add_worksheet :name => 'test' + @hf = ws.header_footer + end + + def test_initialize + assert_equal(nil, @hf.odd_header) + assert_equal(nil, @hf.odd_footer) + + assert_equal(nil, @hf.even_header) + assert_equal(nil, @hf.even_footer) + + assert_equal(nil, @hf.first_header) + assert_equal(nil, @hf.first_footer) + + assert_equal(nil, @hf.different_first) + assert_equal(nil, @hf.different_odd_even) + end + + def test_initialize_with_options + header_footer = { + :odd_header => 'oh', + :odd_footer => 'of', + + :even_header => 'eh', + :even_footer => 'ef', + + :first_header => 'fh', + :first_footer => 'ff', + + :different_first => true, + :different_odd_even => true + } + optioned = @p.workbook.add_worksheet(:name => 'optioned', :header_footer => header_footer).header_footer + + assert_equal('oh', optioned.odd_header) + assert_equal('of', optioned.odd_footer) + + assert_equal('eh', optioned.even_header) + assert_equal('ef', optioned.even_footer) + + assert_equal('fh', optioned.first_header) + assert_equal('ff', optioned.first_footer) + + assert_equal(true, optioned.different_first) + assert_equal(true, optioned.different_odd_even) + end + + def test_string_attributes + %w(odd_header odd_footer even_header even_footer first_header first_footer).each do |attr| + assert_raise(ArgumentError, 'only strings allowed in string attributes') { @hf.send("#{attr}=", 1) } + assert_nothing_raised { @hf.send("#{attr}=", 'test_string') } + end + end + + def test_boolean_attributes + %w(different_first different_odd_even).each do |attr| + assert_raise(ArgumentError, 'only booleanish allowed in string attributes') { @hf.send("#{attr}=", 'foo') } + assert_nothing_raised { @hf.send("#{attr}=", 1) } + end + end + + def test_set_all_values + @hf.set( + :odd_header => 'oh', + :odd_footer => 'of', + + :even_header => 'eh', + :even_footer => 'ef', + + :first_header => 'fh', + :first_footer => 'ff', + + :different_first => true, + :different_odd_even => true + ) + + assert_equal('oh', @hf.odd_header) + assert_equal('of', @hf.odd_footer) + + assert_equal('eh', @hf.even_header) + assert_equal('ef', @hf.even_footer) + + assert_equal('fh', @hf.first_header) + assert_equal('ff', @hf.first_footer) + + assert_equal(true, @hf.different_first) + assert_equal(true, @hf.different_odd_even) + end + + def test_to_xml_all_values + @hf.set( + :odd_header => 'oh', + :odd_footer => 'of', + + :even_header => 'eh', + :even_footer => 'ef', + + :first_header => 'fh', + :first_footer => 'ff', + + :different_first => true, + :different_odd_even => true + ) + + doc = Nokogiri::XML.parse(@hf.to_xml_string) + assert_equal(1, doc.xpath(".//headerFooter[@differentFirst='true'][@differentOddEven='true']").size) + + assert_equal(1, doc.xpath(".//headerFooter/oddHeader").size) + assert_equal('oh', doc.xpath(".//headerFooter/oddHeader").text) + assert_equal(1, doc.xpath(".//headerFooter/oddFooter").size) + assert_equal('of', doc.xpath(".//headerFooter/oddFooter").text) + + assert_equal(1, doc.xpath(".//headerFooter/evenHeader").size) + assert_equal('eh', doc.xpath(".//headerFooter/evenHeader").text) + assert_equal(1, doc.xpath(".//headerFooter/evenFooter").size) + assert_equal('ef', doc.xpath(".//headerFooter/evenFooter").text) + + assert_equal(1, doc.xpath(".//headerFooter/firstHeader").size) + assert_equal('fh', doc.xpath(".//headerFooter/firstHeader").text) + assert_equal(1, doc.xpath(".//headerFooter/firstFooter").size) + assert_equal('ff', doc.xpath(".//headerFooter/firstFooter").text) + end + + def test_to_xml_some_values + @hf.set( + :odd_header => 'oh', + :different_odd_even => false + ) + + doc = Nokogiri::XML.parse(@hf.to_xml_string) + assert_equal(1, doc.xpath(".//headerFooter[@differentOddEven='false']").size) + assert_equal(0, doc.xpath(".//headerFooter[@differentFirst]").size) + + assert_equal(1, doc.xpath(".//headerFooter/oddHeader").size) + assert_equal('oh', doc.xpath(".//headerFooter/oddHeader").text) + assert_equal(0, doc.xpath(".//headerFooter/oddFooter").size) + + assert_equal(0, doc.xpath(".//headerFooter/evenHeader").size) + assert_equal(0, doc.xpath(".//headerFooter/evenFooter").size) + + assert_equal(0, doc.xpath(".//headerFooter/firstHeader").size) + assert_equal(0, doc.xpath(".//headerFooter/firstFooter").size) + end +end + diff --git a/test/workbook/worksheet/tc_row.rb b/test/workbook/worksheet/tc_row.rb index b126845a..d847d475 100644 --- a/test/workbook/worksheet/tc_row.rb +++ b/test/workbook/worksheet/tc_row.rb @@ -12,13 +12,13 @@ class TestRow < Test::Unit::TestCase assert(@row.cells.empty?, "no cells by default") assert_equal(@row.worksheet, @ws, "has a reference to the worksheet") assert_nil(@row.height, "height defaults to nil") - assert([email protected]_height?, "no custom height by default") + assert([email protected]_height, "no custom height by default") end def test_initialize_with_fixed_height row = @ws.add_row([1,2,3,4,5], :height=>40) assert_equal(40, row.height) - assert(row.custom_height?) + assert(row.custom_height) end def test_style @@ -57,7 +57,7 @@ class TestRow < Test::Unit::TestCase def test_custom_height @row.height = 20 - assert(@row.custom_height?) + assert(@row.custom_height) end def test_height @@ -111,7 +111,7 @@ class TestRow < Test::Unit::TestCase @row.add_cell 1 @row.height = 20 r_s_xml = Nokogiri::XML(@row.to_xml_string(0, '')) - assert_equal(r_s_xml.xpath(".//row[@r=1][@ht=20][@customHeight=1]").size, 1) + assert_equal(r_s_xml.xpath(".//row[@r=1][@ht=20][@customHeight='true']").size, 1) end end diff --git a/test/workbook/worksheet/tc_worksheet.rb b/test/workbook/worksheet/tc_worksheet.rb index 4c8f2822..da90ffa0 100644 --- a/test/workbook/worksheet/tc_worksheet.rb +++ b/test/workbook/worksheet/tc_worksheet.rb @@ -55,6 +55,17 @@ class TestWorksheet < Test::Unit::TestCase end end + def test_header_footer + assert(@ws.header_footer.is_a? Axlsx::HeaderFooter) + end + + def test_header_footer_yield + @ws.header_footer do |hf| + assert(hf.is_a? Axlsx::HeaderFooter) + assert(@ws.header_footer == hf) + end + end + def test_no_autowidth @ws.workbook.use_autowidth = false @ws.add_row [1,2,3,4] @@ -65,7 +76,8 @@ class TestWorksheet < Test::Unit::TestCase page_margins = {:left => 2, :right => 2, :bottom => 2, :top => 2, :header => 2, :footer => 2} page_setup = {:fit_to_height => 1, :fit_to_width => 1, :orientation => :landscape, :paper_width => "210mm", :paper_height => "297mm", :scale => 80} print_options = {:grid_lines => true, :headings => true, :horizontal_centered => true, :vertical_centered => true} - optioned = @ws.workbook.add_worksheet(:name => 'bob', :page_margins => page_margins, :page_setup => page_setup, :print_options => print_options, :selected => true, :show_gridlines => false) + header_footer = {:different_first => false, :different_odd_even => false, :odd_header => 'Header'} + optioned = @ws.workbook.add_worksheet(:name => 'bob', :page_margins => page_margins, :page_setup => page_setup, :print_options => print_options, :header_footer => header_footer, :selected => true, :show_gridlines => false) page_margins.keys.each do |key| assert_equal(page_margins[key], optioned.page_margins.send(key)) end @@ -75,6 +87,9 @@ class TestWorksheet < Test::Unit::TestCase print_options.keys.each do |key| assert_equal(print_options[key], optioned.print_options.send(key)) end + header_footer.keys.each do |key| + assert_equal(header_footer[key], optioned.header_footer.send(key)) + end assert_equal(optioned.name, 'bob') assert_equal(optioned.selected, true) assert_equal(optioned.show_gridlines, false) @@ -293,6 +308,16 @@ class TestWorksheet < Test::Unit::TestCase assert_equal(doc.xpath('//xmlns:worksheet/xmlns:printOptions[@gridLines="true"][@horizontalCentered="true"]').size, 1) end + def test_to_xml_string_header_footer + @ws.header_footer do |hf| + hf.different_first = false + hf.different_odd_even = false + hf.odd_header = 'Test Header' + end + doc = Nokogiri::XML(@ws.to_xml_string) + assert_equal(doc.xpath('//xmlns:worksheet/xmlns:headerFooter[@differentFirst="false"][@differentOddEven="false"]').size, 1) + end + def test_to_xml_string_drawing @ws.add_chart Axlsx::Pie3DChart doc = Nokogiri::XML(@ws.to_xml_string) @@ -317,7 +342,7 @@ class TestWorksheet < Test::Unit::TestCase 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] @@ -400,7 +425,7 @@ class TestWorksheet < Test::Unit::TestCase @ws.add_row [1, 2, 3] assert_nothing_raised {@ws.protect_range(@ws.rows.first.cells) } assert_equal('A1:C1', @ws.send(:protected_ranges).last.sqref) - + end def test_merge_cells @ws.add_row [1,2,3] @@ -412,7 +437,7 @@ class TestWorksheet < Test::Unit::TestCase assert_equal(@ws.send(:merged_cells).size, 3) assert_equal(@ws.send(:merged_cells).last, "A3:B3") end - + def test_merge_cells_sorts_correctly_by_row_when_given_array 10.times do |i| @ws.add_row [i] @@ -420,7 +445,7 @@ class TestWorksheet < Test::Unit::TestCase @ws.merge_cells [@ws.rows[8].cells.first, @ws.rows[9].cells.first] assert_equal "A9:A10", @ws.send(:merged_cells).first end - + def test_auto_filter assert(@ws.auto_filter.range.nil?) assert_raise(ArgumentError) { @ws.auto_filter = 123 } @@ -432,6 +457,6 @@ class TestWorksheet < Test::Unit::TestCase @ws.auto_filter.range = 'A1:D9' @ws.auto_filter.add_column 0, :filters, :filter_items => [1] doc = Nokogiri::XML(@ws.to_xml_string) - assert(doc.xpath('//sheetPr[@filterMode="true"]')) + assert(doc.xpath('//sheetPr[@filterMode="true"]')) end end diff --git a/test/workbook/worksheet/tc_worksheet_hyperlink.rb b/test/workbook/worksheet/tc_worksheet_hyperlink.rb index ad993e62..278c5add 100644 --- a/test/workbook/worksheet/tc_worksheet_hyperlink.rb +++ b/test/workbook/worksheet/tc_worksheet_hyperlink.rb @@ -5,7 +5,7 @@ class TestWorksheetHyperlink < Test::Unit::TestCase p = Axlsx::Package.new wb = p.workbook @ws = wb.add_worksheet - @options = { :location => 'https://github.com/randym/axlsx', :tooltip => 'axlsx', :ref => 'A1', :display => 'AXSLX', :target => :internal } + @options = { :location => 'https://github.com/randym/axlsx?foo=1&bar=2', :tooltip => 'axlsx', :ref => 'A1', :display => 'AXSLX', :target => :internal } @a = @ws.add_hyperlink @options end |