diff options
50 files changed, 838 insertions, 507 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 3a685657..a7eace2a 100755 --- a/examples/example.rb +++ b/examples/example.rb @@ -7,8 +7,10 @@ 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 @@ -21,6 +23,7 @@ examples << :auto_filter examples << :data_types examples << :hyperlinks examples << :number_currency_format +examples << :venezuela_currency examples << :bar_chart examples << :chart_gridlines examples << :pie_chart @@ -32,8 +35,10 @@ 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 @@ -74,7 +79,26 @@ if examples.include? :custom_styles end end end -#``` + + +#```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 @@ -87,7 +111,14 @@ if examples.include? :cell_style_override 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 } @@ -111,11 +142,38 @@ if examples.include? :custom_borders end end -#```ruby +#```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 + + end +end + +#```ruby # Hacking border styles if examples.include? :deep_custom_borders wb.styles do |s| - top_bottom = s.add_style :border => { :style => :thick, :color =>"FFFF0000", :edges => [:top, :bottom] } + 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| @@ -291,7 +349,7 @@ 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 + 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'] @@ -314,6 +372,15 @@ if examples.include? :number_currency_format 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 @@ -446,7 +513,7 @@ 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') + wb.add_defined_name("'repeated header'!$1:$1", :local_sheet_id => sheet.index, :name => '_xlnm.Print_Titles') end end @@ -481,7 +548,17 @@ if examples.include? :printing end #``` -## Add Comments to your spreadsheet +## Add headers and footers to a worksheet +#``` ruby +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| @@ -493,7 +570,7 @@ end ## Frozen/Split panes ## ``` ruby if examples.include? :panes - wb.add_worksheet(:name => 'fixed headers') do |sheet| + 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| @@ -506,6 +583,33 @@ if examples.include? :panes end end +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 + + 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 + # conditional formatting # if examples.include? :conditional_formatting @@ -516,59 +620,61 @@ if examples.include? :conditional_formatting profitable = wb.styles.add_style( :fg_color=>"FF428751", :type => :dxf) - wb.add_worksheet(:name => "Conditional Cell Is") do |ws| + wb.add_worksheet(:name => "Conditional Cell Is") do |sheet| - # Generate 20 rows of data - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] + # Generate 20 rosheet of data + 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| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + 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 # 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 }) + sheet.add_conditional_formatting("B3:B100", { :type => :cellIs, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1 }) end - wb.add_worksheet(:name => "Conditional Color Scale") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] + 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 - 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] + 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 - # 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 }) + # 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 + + 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 - wb.add_worksheet(:name => "Conditional Data Bar") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] + 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| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + 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 - ws.add_conditional_formatting("B3:B100", { :type => :dataBar, :dxfId => profitable, :priority => 1, :data_bar => data_bar }) + sheet.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"] + 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| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] + 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 - ws.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set }) + sheet.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set }) end end 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/styles.rb b/examples/styles.rb index b69251b4..7a589e2e 100644 --- a/examples/styles.rb +++ b/examples/styles.rb @@ -48,6 +48,9 @@ wb.styles do |style| # A style that a applies a font size and a custom formatting code custom_format = wb.styles.add_style :sz => 20, :format_code => 'yyyy-mm-dd' + # A style that overrides top and left border style + override_border = wb.styles.add_style :border => { :style => :thin, :color =>"FAAC58", :edges => [:right, :top, :left] }, :border_top => { :style => :thick, :color => "01DF74" }, :border_left => { :color => "0101DF" } + wb.add_worksheet do |sheet| @@ -55,6 +58,7 @@ wb.styles do |style| sheet.add_row [123, "123", Time.now], style: [nil, large_font, predefined_format] sheet.add_row [123, "123", Date.new(2012, 9, 14)], style: [large_font, nil, custom_format] sheet.add_row [123, "123", Date.new(2000, 9, 12)] # This uses the axlsx default format_code (14) + sheet.add_row [123, "123", Time.now], style: [large_font, override_border, predefined_format] end end 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 index ac17dce9..0afaa53f 100644 --- a/lib/axlsx/content_type/abstract_content_type.rb +++ b/lib/axlsx/content_type/abstract_content_type.rb @@ -1,9 +1,12 @@ 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 @@ -18,6 +21,7 @@ module Axlsx 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(' ') diff --git a/lib/axlsx/content_type/default.rb b/lib/axlsx/content_type/default.rb index 0cd00d48..3fe15609 100644 --- a/lib/axlsx/content_type/default.rb +++ b/lib/axlsx/content_type/default.rb @@ -4,6 +4,7 @@ module Axlsx # An default content part. These parts are automatically created by for you based on the content of your package. class Default < AbstractContentType + # The serialization node name for this class NODE_NAME = 'Default' # The extension of the content type. @@ -15,6 +16,7 @@ module Axlsx def extension=(v) Axlsx::validate_string v; @extension = v end alias :Extension= :extension= + # Serializes this object to xml def to_xml_string(str ='') super(NODE_NAME, str) end diff --git a/lib/axlsx/content_type/override.rb b/lib/axlsx/content_type/override.rb index 01918321..7a8e33fa 100644 --- a/lib/axlsx/content_type/override.rb +++ b/lib/axlsx/content_type/override.rb @@ -5,6 +5,7 @@ module Axlsx # An override content part. These parts are automatically created by for you based on the content of your package. class Override < AbstractContentType + # Serialization node name for this object NODE_NAME = 'Override' # The name and location of the part. @@ -16,6 +17,7 @@ module Axlsx def part_name=(v) Axlsx::validate_string v; @part_name = v end alias :PartName= :part_name= + # Serializes this object to xml def to_xml_string(str = '') super(NODE_NAME, str) 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/rels/relationship.rb b/lib/axlsx/rels/relationship.rb index b19a18f7..385059f1 100644 --- a/lib/axlsx/rels/relationship.rb +++ b/lib/axlsx/rels/relationship.rb @@ -56,7 +56,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/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/styles.rb b/lib/axlsx/stylesheet/styles.rb index da5b277f..74894052 100644 --- a/lib/axlsx/stylesheet/styles.rb +++ b/lib/axlsx/stylesheet/styles.rb @@ -308,6 +308,8 @@ module Axlsx # may include an :edges entry that references an array of symbols identifying which border edges # you wish to apply the style or any other valid Border initializer options. # If the :edges entity is not provided the style is applied to all edges of cells that reference this style. + # Also available :border_top, :border_right, :border_bottom and :border_left options with :style and/or :color + # key-value entries, which override :border values. # @example # #apply a thick red border to the top and bottom # { :border => { :style => :thick, :color => "FFFF0000", :edges => [:top, :bottom] } @@ -319,7 +321,9 @@ module Axlsx raise ArgumentError, (ERR_INVALID_BORDER_OPTIONS % b_opts) unless b_opts.keys.include?(:style) && b_opts.keys.include?(:color) border = Border.new b_opts (b_opts[:edges] || [:left, :right, :top, :bottom]).each do |edge| - b_options = { :name => edge, :style => b_opts[:style], :color => Color.new(:rgb => b_opts[:color]) } + edge_options = options["border_#{edge}".to_sym] || {} + border_edge = b_opts.merge(edge_options) + b_options = { :name => edge, :style => border_edge[:style], :color => Color.new(:rgb => border_edge[:color]) } border.prs << BorderPr.new(b_options) end options[:type] == :dxf ? border : borders << border 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 50c8d3ae..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,11 +46,32 @@ 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}\" " 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 end end diff --git a/lib/axlsx/util/validators.rb b/lib/axlsx/util/validators.rb index 03f0c89a..aa8eb1ac 100644 --- a/lib/axlsx/util/validators.rb +++ b/lib/axlsx/util/validators.rb @@ -141,7 +141,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/workbook.rb b/lib/axlsx/workbook/workbook.rb index a3c42743..c1d304a4 100644 --- a/lib/axlsx/workbook/workbook.rb +++ b/lib/axlsx/workbook/workbook.rb @@ -9,6 +9,7 @@ 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' 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 index 06d9bdd4..d642bfc6 100644 --- a/lib/axlsx/workbook/worksheet/cfvos.rb +++ b/lib/axlsx/workbook/worksheet/cfvos.rb @@ -6,9 +6,6 @@ module Axlsx def initialize super(Cfvo) - @list << Cfvo.new(:type => :min, :val => 0) - @list << Cfvo.new(:type => :max, :val => 0) - lock end def to_xml_string(str='') diff --git a/lib/axlsx/workbook/worksheet/color_scale.rb b/lib/axlsx/workbook/worksheet/color_scale.rb index 60a2efa3..287e6869 100644 --- a/lib/axlsx/workbook/worksheet/color_scale.rb +++ b/lib/axlsx/workbook/worksheet/color_scale.rb @@ -7,6 +7,35 @@ 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 @@ -17,25 +46,31 @@ module Axlsx # 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_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} + colors << Color.new(:rgb => options[:color] || "FF000000") + {:cfvo => value_objects.last, :color => colors.last} end @@ -44,7 +79,7 @@ module Axlsx # @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 + colors.delete_at index end # Serialize this color_scale object data to an xml string @@ -53,18 +88,23 @@ module Axlsx def to_xml_string(str = '') str << '<colorScale>' value_objects.to_xml_string(str) - @colors.each { |color| color.to_xml_string(str) } + colors.each { |color| color.to_xml_string(str) } str << '</colorScale>' end private - - # creates the initial color objects - def initialize_colors - @colors = SimpleTypedList.new Color - @colors.concat [Color.new(:rgb => "FFFF0000"), Color.new(:rgb => "FF0000FF")] - @colors.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 - end end diff --git a/lib/axlsx/workbook/worksheet/data_bar.rb b/lib/axlsx/workbook/worksheet/data_bar.rb index 90cd417a..e67f76a4 100644 --- a/lib/axlsx/workbook/worksheet/data_bar.rb +++ b/lib/axlsx/workbook/worksheet/data_bar.rb @@ -10,16 +10,27 @@ 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 = {}) + # @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 @@ -70,27 +81,27 @@ module Axlsx end alias :minLength= :min_length= - # @see maxLength - def max_length=(v) - Axlsx.validate_unsigned_int(v) - @max_length = v - end + # @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 + # @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 + # 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 @@ -103,5 +114,18 @@ module Axlsx self.color.to_xml_string(str) str << '</dataBar>' end + + private + + 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/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/worksheet.rb b/lib/axlsx/workbook/worksheet/worksheet.rb index ff3e022e..4c59e340 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 @@ -199,6 +202,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 @@ -243,7 +261,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 @@ -252,7 +270,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 @@ -261,7 +279,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 @@ -271,7 +289,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 @@ -599,7 +617,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 diff --git a/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb b/lib/axlsx/workbook/worksheet/worksheet_hyperlink.rb index 3ea89c14..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/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/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_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 c488e83d..5d5e14a4 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) @@ -299,6 +314,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) 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 |