examples/example.rb in axlsx-1.3.3 vs examples/example.rb in axlsx-1.3.4
- old
+ new
@@ -5,10 +5,11 @@
#```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
@@ -20,10 +21,11 @@
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
@@ -31,12 +33,14 @@
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
@@ -73,12 +77,31 @@
# Applies the thin border to all three cells
sheet.add_row [1, 2, 3], :style => Axlsx::STYLE_THIN_BORDER
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
#```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.
if examples.include? :cell_style_override
@@ -126,11 +149,11 @@
# 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) } )
+ 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]]
@@ -144,15 +167,15 @@
ws.rows.last.style = bottom_row
end
end
-#```ruby
+#```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|
case part.name
when :top
@@ -324,11 +347,11 @@
# Hyperlinks in worksheet
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']
end
@@ -347,10 +370,19 @@
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
if examples.include? :bar_chart
wb.add_worksheet(:name => "Bar Chart") do |sheet|
@@ -479,11 +511,11 @@
#```ruby
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
# Defined Names in formula
if examples.include? :defined_name
@@ -514,23 +546,33 @@
sheet.add_row ["this sheet uses customized print settings"]
end
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|
sheet.add_row ['Can we build it?']
sheet.add_comment :ref => 'A1', :author => 'Bob', :text => 'Yes We Can!'
end
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|
pane.top_left_cell = "B2"
pane.state = :frozen_split
@@ -539,72 +581,101 @@
pane.active_pane = :bottom_right
end
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
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 |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
##Validate and Serialize