diff options
| author | Zsolt Kozaroczy <[email protected]> | 2020-09-11 00:36:29 +0200 |
|---|---|---|
| committer | GitHub <[email protected]> | 2020-09-11 00:36:29 +0200 |
| commit | 282eec44ef01746ee25931fa6cd287ad083fd40b (patch) | |
| tree | 14504bf429ca264812679ca971c1592853d3f762 | |
| parent | 317e8244e4d17c394c1e181f86df3974623fb865 (diff) | |
| download | caxlsx-282eec44ef01746ee25931fa6cd287ad083fd40b.tar.gz caxlsx-282eec44ef01746ee25931fa6cd287ad083fd40b.zip | |
Restructure examples folder (#47)
Split examples into separate markdown files, each containing a description, sample code, and a screenshot of the resulting xlsx document.
The script `generate.rb` is provided to actually generate the example documents by executing the sample code contained in the markdown files.
168 files changed, 2082 insertions, 1875 deletions
diff --git a/examples/2010_comments.rb b/examples/2010_comments.rb deleted file mode 100644 index 6a7bedf8..00000000 --- a/examples/2010_comments.rb +++ /dev/null @@ -1,17 +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 -p.workbook.add_worksheet(:name => 'Excel 2010 comments') do |sheet| - sheet.add_row ['Cell with visible comment'] - sheet.add_row - sheet.add_row - sheet.add_row ['Cell with hidden comment'] - - sheet.add_comment :ref => 'A1', :author => 'XXX', :text => 'Visibile' - sheet.add_comment :ref => 'A4', :author => 'XXX', :text => 'Hidden', :visible => false -end -p.serialize('excel_2010_comment_test.xlsx') diff --git a/examples/3d_line_chart_example.md b/examples/3d_line_chart_example.md new file mode 100644 index 00000000..df732c96 --- /dev/null +++ b/examples/3d_line_chart_example.md @@ -0,0 +1,31 @@ +## Description + +Line chart example + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Line Chart') do |sheet| + sheet.add_row ['X-Y', 'X', 'Y'] + + sheet.add_row ['A', 3, 7] + sheet.add_row ['B', 10, 2] + sheet.add_row ['C', 7, 6] + + sheet.add_chart(Axlsx::Line3DChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.add_series data: sheet['B2:B4'], title: sheet['B1'], color: '0000FF' + chart.add_series data: sheet['C2:C4'], title: sheet['C1'], color: 'FF0000' + end +end + +p.serialize '3d_line_chart_example.xlsx' +``` + +## Output + + diff --git a/examples/3d_pie_chart_example.md b/examples/3d_pie_chart_example.md new file mode 100644 index 00000000..15ae1c4a --- /dev/null +++ b/examples/3d_pie_chart_example.md @@ -0,0 +1,30 @@ +## Description + +3D Pie chart example + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Pie Chart') do |sheet| + sheet.add_row ['A Simple Bar Chart'] + + sheet.add_row ['A', 3] + sheet.add_row ['B', 10] + sheet.add_row ['C', 7] + + sheet.add_chart(Axlsx::Pie3DChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.add_series data: sheet['B2:B4'], labels: sheet['A2:A4'], title: sheet['A1'] + end +end + +p.serialize '3d_pie_chart_example.xlsx' +``` + +## Output + + diff --git a/examples/README.md b/examples/README.md new file mode 100644 index 00000000..5b1abdc2 --- /dev/null +++ b/examples/README.md @@ -0,0 +1,116 @@ +## Examples + +Usage: +* change dir to `examples` +* `ruby generate.rb` to run all examples +* `ruby generate.rb file1.md file2.md` to run specific examples +* `./generate.rb` is also supported + +### Basic + +* [Basic](basic_example.md) +* [Custom Styles](custom_styles_example.md) +* [Style Overrides](style_overrides_example.md) +* [Asian Language Support](asian_language_support_example.md) +* [Shared strings](shared_strings_example.md) +* [Serialize to stream](stream_example.md) +* [Complex example](complex_example.md) + +### Borders + +* [Custom Borders](borders_example.md) +* [Surrounding Borders](surrounding_borders_example.md) + +### Cells + +* [Merge Cells](merge_cells_example.md) +* [Automatic data types](automatic_data_types_example.md) +* [Override data types](override_data_types_example.md) + +### Conditional formatting + +* [Greater than](conditional_formatting_greater_than_example.md) +* [Between](conditional_formatting_greater_than_example.md) +* [Color scale](conditional_formatting_color_scale_example.md) +* [Data bar](conditional_formatting_data_bar_example.md) +* [Icon set](conditional_formatting_icon_set_example.md) + +### Charts + +Types: +* [Bar chart](bar_chart_example.md) +* [Line chart](line_chart_example.md) +* [Line chart (3D)](3d_line_chart_example.md) +* [Pie chart (3D)](3d_pie_chart_example.md) +* [Scatter chart](scatter_chart_example.md) + +Customizations: +* [Chart colors](chart_colors_example.md) +* [Hide gridlines](hide_gridlines_in_chart_example.md) + +### Columns + +* [Custom Column Styles](column_styles_example.md) +* [Column widths](column_widths_example.md) +* [Outlines](column_outlines_example.md) +* [No autowidth](no_autowidth_example.md) + +### Filters + +* [Auto filters](auto_filter_example.md) +* [Filtered tables](filtered_table_example.md) + +### Formulas + +* [Basic formula](basic_formula_example.md) +* [Cached formula](cached_formula_example.md) +* [Escape formula](escape_formula_example.md) +* [Defined name](defined_name_example.md) + +### Media + +* [Image](image_example.md) +* [Locked Image](locked_image_example.md) +* [Hyperlinked Image](hyperlinked_image_example.md) +* [Hyperlinks](hyperlink_example.md) + +### Print + +* [Fit to page print settings](fit_to_page_example.md) +* [Headers and footers](header_footer_example.md) +* [Page breaks](page_break_example.md) +* [Print settings](print_settings_example.md) +* [Repeated header](repeated_header_example.md) + +### Rows + +* [Custom Row Styles](row_styles_example.md) +* [Outlines](row_outlines_example.md) +* [Row heights](row_heights_example.md) + +### Sheet + +* [Active tab](active_tab_example.md) +* [Hide gridlines](hide_gridlines_in_sheet_example.md) +* [Hide sheets](hide_sheet_example.md) +* [Sheet protection](sheet_protection_example.md) +* [Tab colors](tab_color_example.md) + +### Text decorations + +* [Fonts](font_example.md) +* [Format codes](format_codes_example.md) +* [Number formats and currencyc](number_format_example.md) +* [Rich text](rich_text_example.md) +* [Wrap text](wrap_text_example.md) + +### Validations + +* [List validation](list_validation_example.md) +* [Range validation](range_validation_example.md) +* [Text length validation](text_length_validation_example.md) + +### Other features + +* [Comments](comments_example.md) +* [Pivot tables](pivot_table_example.md) diff --git a/examples/active_tab_example.md b/examples/active_tab_example.md new file mode 100644 index 00000000..25f80523 --- /dev/null +++ b/examples/active_tab_example.md @@ -0,0 +1,32 @@ +## Description + +Book views let you specify which sheet the show as active when the user opens the work book as well as a bunch of other. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['First', 'Second', 'Third'] + sheet.add_row [1, 2, 3] +end + +wb.add_worksheet(name: 'Second Worksheet') do |sheet| + sheet.add_row ['First', 'Second', 'Third'] + sheet.add_row [1, 2, 3] +end + +# The horizontal scrollbar will be smaller +# The second tab will be selected +wb.add_view tab_ratio: 800, active_tab: 1 + +p.serialize 'active_tab_example.xlsx' +``` + +## Output + + diff --git a/examples/anchor_swapping.rb b/examples/anchor_swapping.rb deleted file mode 100644 index 88302005..00000000 --- a/examples/anchor_swapping.rb +++ /dev/null @@ -1,28 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -xls = Axlsx::Package.new -wb = xls.workbook -wb.add_worksheet do |ws| - img = File.expand_path('../image1.jpeg', __FILE__) - ws.add_image(:image_src => img) do |image| - image.start_at 2, 2 - image.end_at 5, 5 - end -end -wb.add_worksheet do |ws| - img = File.expand_path('../image1.jpeg', __FILE__) - ws.add_image(:image_src => img, :start_at => "B2") do |image| - image.width = 70 - image.height = 50 - end -end -wb.add_worksheet do |ws| - img = File.expand_path('../image1.jpeg', __FILE__) - ws.add_image(:image_src => img, :start_at => [1,1]) do |image| - image.end_at "E7" - end -end - - - -xls.serialize 'anchor.xlsx' diff --git a/examples/asian_language_support_example.md b/examples/asian_language_support_example.md new file mode 100644 index 00000000..1dd86497 --- /dev/null +++ b/examples/asian_language_support_example.md @@ -0,0 +1,27 @@ +## Description + +There is a support for asian language. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +s.fonts.first.name = 'Arial Unicode MS' + +wb.add_worksheet(name: '日本語でのシート名') do |sheet| + sheet.add_row ['日本語'] + sheet.add_row ['华语/華語'] + sheet.add_row ['한국어/조선말'] +end + +p.serialize 'asian_language_support_example.xlsx' +``` + +## Output + + diff --git a/examples/image1.jpeg b/examples/assets/image1.jpeg Binary files differindex ce1d0c56..ce1d0c56 100644 --- a/examples/image1.jpeg +++ b/examples/assets/image1.jpeg diff --git a/examples/auto_filter.rb b/examples/auto_filter.rb deleted file mode 100644 index b47082cb..00000000 --- a/examples/auto_filter.rb +++ /dev/null @@ -1,25 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- - -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -Axlsx::Package.new do |p| - p.workbook.add_worksheet(:name => "Table") do |sheet| - sheet.add_row ["Build Matrix"] - sheet.add_row ["Build", "Duration", "Finished", "Rvm"] - sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] - sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] - sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] - sheet.auto_filter = 'A2:D5' - sheet.auto_filter.add_column 3, :filters, :filter_items => ['1.9.2'] - end - p.workbook.add_worksheet(:name => "Table 2") do |sheet| - sheet.add_row ["Build Matrix"] - sheet.add_row ["Build", "Duration", "Finished", "Rvm"] - sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] - sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] - sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] - sheet.auto_filter = 'A2:D5' - sheet.auto_filter.add_column 3, :filters, :filter_items => ['1.9.2'] - end -end.serialize('auto_filter.xlsx') diff --git a/examples/auto_filter_example.md b/examples/auto_filter_example.md new file mode 100644 index 00000000..eb42ca77 --- /dev/null +++ b/examples/auto_filter_example.md @@ -0,0 +1,32 @@ +## Description + +You could mark areas as auto filtered tables. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Auto Filter') do |sheet| + sheet.add_row ['Build Matrix'] + sheet.add_row ['Build', 'Duration', 'Finished', 'Rvm'] + sheet.add_row ['19.1', '1 min 32 sec', 'about 10 hours ago', '1.8.7'] + sheet.add_row ['19.2', '1 min 28 sec', 'about 10 hours ago', '1.9.2'] + sheet.add_row ['19.3', '1 min 35 sec', 'about 10 hours ago', '1.9.3'] + sheet.auto_filter = 'A2:D5' + sheet.auto_filter.add_column 3, :filters, filter_items: ['1.9.2', '1.8.7'] +end + +p.serialize 'auto_filter_example.xlsx' +``` + +## Output + + + +After resetting the filters: + + diff --git a/examples/automatic_data_types_example.md b/examples/automatic_data_types_example.md new file mode 100644 index 00000000..cdae2d5c --- /dev/null +++ b/examples/automatic_data_types_example.md @@ -0,0 +1,25 @@ +## Description + +The data types are automatically calculated into the cell. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Automatic cell types') do |sheet| + sheet.add_row ['Date', 'Time', 'String', 'Boolean', 'Float', 'Integer'] + sheet.add_row [Date.today, Time.now, 'value', true, 0.1, 1] +end + +p.serialize 'automatic_data_types_example.xlsx' +``` + +## Output + + + +Notes: The time column has the full datetime in it, you could customize it with format codes. diff --git a/examples/bar_chart_example.md b/examples/bar_chart_example.md new file mode 100644 index 00000000..65b5969b --- /dev/null +++ b/examples/bar_chart_example.md @@ -0,0 +1,30 @@ +## Description + +Bar chart example + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Bar Chart') do |sheet| + sheet.add_row ['A Simple Bar Chart'] + + sheet.add_row ['A', 3] + sheet.add_row ['B', 10] + sheet.add_row ['C', 7] + + sheet.add_chart(Axlsx::Bar3DChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.add_series data: sheet['B2:B4'], labels: sheet['A2:A4'], title: sheet['A1'] + end +end + +p.serialize 'bar_chart_example.xlsx' +``` + +## Output + + diff --git a/examples/basic_charts.rb b/examples/basic_charts.rb deleted file mode 100644 index 097af57e..00000000 --- a/examples/basic_charts.rb +++ /dev/null @@ -1,58 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -p = Axlsx::Package.new -wb = p.workbook - -# Pie Chart -wb.add_worksheet(:name => "Pie Chart") do |sheet| - sheet.add_row ["First", "Second", "Third", "Fourth"] - sheet.add_row [1, 2, 3, 4] - sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,2], :end_at => [5, 15], :title=> 'dark corner here') do |chart| - chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"] - chart.d_lbls.show_val = true - chart.d_lbls.show_percent = true - chart.d_lbls.d_lbl_pos = :outEnd - chart.d_lbls.show_leader_lines = true - end -end - -# line chart - wb.add_worksheet(:name => "Line Chart") do |sheet| - sheet.add_row ['1', '2', '3', '4'] - sheet.add_row [1, 2, 3, '=sum(A2:C2)'] - sheet.add_chart(Axlsx::Line3DChart, :start_at => [0,2], :end_at => [5, 15], :title => "Chart") do |chart| - chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"], :title => 'bob' - chart.d_lbls.show_val = true - chart.d_lbls.show_cat_name = true - chart.catAxis.tick_lbl_pos = :none - - end - end - -# bar chart - wb.add_worksheet(:name => "Bar Chart") do |sheet| - sheet.add_row ["A Simple Bar Chart"] - sheet.add_row ["First", "Second", "Third"] - sheet.add_row [1, 2, 3] - sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A4", :end_at => "F17") do |chart| - chart.add_series :data => sheet["A3:C3"], :labels => sheet["A2:C2"], :title => sheet["A1"] - chart.valAxis.label_rotation = -45 - chart.catAxis.label_rotation = 45 - chart.d_lbls.d_lbl_pos = :outEnd - chart.d_lbls.show_val = true - - chart.catAxis.tick_lbl_pos = :none - end - end - -# specifying colors and title -wb.add_worksheet(:name => "Colored Pie Chart") do |sheet| - sheet.add_row ["First", "Second", "Third", "Fourth"] - sheet.add_row [1, 2, 3, "=PRODUCT(A2:C2)"] - sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,2], :end_at => [5, 15], :title => "example 3: Pie Chart") do |chart| - chart.add_series :data => sheet["A2:D2"], :labels => ["A1:D1"], :colors => ['FF0000', '00FF00', '0000FF'] - end -end - -p.serialize('basic_charts.xlsx') - diff --git a/examples/basic_example.md b/examples/basic_example.md new file mode 100644 index 00000000..40acd0bc --- /dev/null +++ b/examples/basic_example.md @@ -0,0 +1,23 @@ +## Description + +This is a very basic example with a worksheet and two rows of data. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['First', 'Second', 'Third'] + sheet.add_row [1, 2, 3] +end + +p.serialize 'basic_example.xlsx' +``` + +## Output + + diff --git a/examples/basic_formula_example.md b/examples/basic_formula_example.md new file mode 100644 index 00000000..d19288fb --- /dev/null +++ b/examples/basic_formula_example.md @@ -0,0 +1,23 @@ +## Description + +You could insert formulas + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['A', 'B', 'C', 'SUM'] + sheet.add_row [1, 2, 3, '=SUM(A2:C2)'] +end + +p.serialize 'basic_formula_example.xlsx' +``` + +## Output + + diff --git a/examples/borders_example.md b/examples/borders_example.md new file mode 100644 index 00000000..d637681c --- /dev/null +++ b/examples/borders_example.md @@ -0,0 +1,27 @@ +## Description + +Axlsx defines a thin border style, but you can easily create and use your own. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +red_border = s.add_style border: { style: :thick, color: 'FFFF0000', edges: [:left, :right] } +blue_border = s.add_style border: { style: :thick, color: 'FF0000FF' } + +wb.add_worksheet(name: 'Custom Borders') do |sheet| + sheet.add_row ['wrap', 'me', 'up in red'], style: red_border + sheet.add_row [1, 2, 3], style: blue_border +end + +p.serialize 'borders_example.xlsx' +``` + +## Output + + diff --git a/examples/cached_formula_example.md b/examples/cached_formula_example.md new file mode 100644 index 00000000..e5b82264 --- /dev/null +++ b/examples/cached_formula_example.md @@ -0,0 +1,23 @@ +## Description + +When you add a formula in Excel, it immediately calculates its value and store in the document. When you generate an xlsx file, the value is usually empty and it get calculated on the first run. If you want a nice preview with all the data provided, you could add the pre-calculated values + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Cached formula') do |sheet| + sheet.add_row [1, 2, 3] + sheet.add_row [1, 2, '=A2+B2'], formula_values: [nil, nil, 3] +end + +p.serialize 'cached_formula_example.xlsx' +``` + +## Output + + diff --git a/examples/chart_colors.rb b/examples/chart_colors.rb deleted file mode 100644 index 8349076a..00000000 --- a/examples/chart_colors.rb +++ /dev/null @@ -1,88 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' - -p = Axlsx::Package.new -wb = p.workbook - -##Generating A Bar Chart - -wb.add_worksheet(:name => "Bar Chart") do |sheet| - sheet.add_row ["A Simple Bar Chart"] - sheet.add_row ["First", "Second", "Third"] - sheet.add_row [1,2,3.5] - sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A4", :end_at => "F17", :title => sheet["A1"]) do |chart| - chart.add_series :data => sheet["A3:C3"], :labels => sheet["A2:C2"], :colors => ['FF0000', '00FF00', '0000FF'], :color => "000000" - chart.valAxis.label_rotation = -45 - chart.catAxis.label_rotation = 45 - end -end - -##Generating A Bar Chart without data in the sheet - -wb.add_worksheet(:name => "Hard Bar Chart") do |sheet| - sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A4", :end_at => "F17", :title => "hard code chart") do |chart| - chart.add_series :data => [1,3,5], :labels => ['a', 'b', 'c'], :colors => ['FF0000', '00FF00', '0000FF'] - chart.valAxis.label_rotation = -45 - chart.catAxis.label_rotation = 45 - end -end - - -##Generating A Pie Chart - - wb.add_worksheet(:name => "Pie Chart") do |sheet| - sheet.add_row ["First", "Second", "Third", "Fourth"] - sheet.add_row [1, 2, 3, "=PRODUCT(A2:C2)"] - sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,2], :end_at => [5, 15], :title => "example 3: Pie Chart") do |chart| - chart.add_series :data => sheet["A2:D2"], :labels => sheet["A1:D1"], :colors => ['FF0000', '00FF00', '0000FF', '000000'] - end - end - -# Line Chart - -wb.add_worksheet(:name => "Line Chart") do |sheet| - sheet.add_row ["First", 1, 5, 7, 9] - sheet.add_row ["Second", 5, 2, 14, 9] - sheet.add_chart(Axlsx::Line3DChart, :title => "example 6: Line Chart", :rotX => 30, :rotY => 30) do |chart| - chart.start_at 0, 2 - chart.end_at 10, 15 - chart.add_series :data => sheet["B1:E1"], :title => sheet["A1"], :color => "FF0000" - chart.add_series :data => sheet["B2:E2"], :title => sheet["A2"], :color => "00FF00" - end -end - -wb.add_worksheet(:name => 'Line Chart with Axis colors') do |sheet| - sheet.add_row ["First", 1, 5, 7, 9] - sheet.add_row ["Second", 5, 2, 14, 9] - sheet.add_chart(Axlsx::Line3DChart, :title => "example 7: Flat Line Chart", :rot_x => 0, :perspective => 0) do |chart| - chart.valAxis.color = "FFFF00" - chart.catAxis.color = "00FFFF" - chart.serAxis.delete = true - chart.start_at 0, 2 - chart.end_at 10, 15 - chart.add_series :data => sheet["B1:E1"], :title => sheet["A1"], :color => "FF0000" - chart.add_series :data => sheet["B2:E2"], :title => sheet["A2"], :color => "00FF00" - end - -end - -##Generating A Scatter Chart - - wb.add_worksheet(:name => "Scatter Chart") do |sheet| - sheet.add_row ["First", 1, 5, 7, 9] - sheet.add_row ["", 1, 25, 49, 81] - sheet.add_row ["Second", 5, 2, 14, 9] - sheet.add_row ["", 5, 10, 15, 20] - sheet.add_chart(Axlsx::ScatterChart, :title => "example 7: Scatter Chart") do |chart| - chart.start_at 0, 4 - chart.end_at 10, 19 - chart.add_series :xData => sheet["B1:E1"], :yData => sheet["B2:E2"], :title => sheet["A1"], :color => '00FF00' - chart.add_series :xData => sheet["B3:E3"], :yData => sheet["B4:E4"], :title => sheet["A3"], :color => 'FF0000' - end - end - - -p.validate.each { |e| puts e.message } -p.serialize 'chart_colors.xlsx' diff --git a/examples/chart_colors_example.md b/examples/chart_colors_example.md new file mode 100644 index 00000000..f017025c --- /dev/null +++ b/examples/chart_colors_example.md @@ -0,0 +1,30 @@ +## Description + +You could change the colors of the charts with providing a list of colors. You don't need to provide color for all data. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Bar Chart') do |sheet| + sheet.add_row ['A Simple Bar Chart'] + + sheet.add_row ['A', 3] + sheet.add_row ['B', 10] + sheet.add_row ['C', 7] + + sheet.add_chart(Axlsx::Bar3DChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.add_series data: sheet['B2:B4'], labels: sheet['A2:A4'], title: sheet['A1'], colors: ['FF0000', '00FF00'] + end +end + +p.serialize 'chart_colors_example.xlsx' +``` + +## Output + + diff --git a/examples/colored_links.rb b/examples/colored_links.rb deleted file mode 100644 index b9d42e33..00000000 --- a/examples/colored_links.rb +++ /dev/null @@ -1,45 +0,0 @@ -require 'axlsx' - -p = Axlsx::Package.new -wb = p.workbook - -# Each style only needs to be declared once in the workbook. -s = wb.styles -title_cell = s.add_style sz: 14, alignment: { horizontal: :center } -black_cell = s.add_style sz: 10, alignment: { horizontal: :center } -blue_link = s.add_style fg_color: '0000FF' - -# Create summary sheet -sum_sheet = wb.add_worksheet name: 'Summary' -sum_sheet.add_row ['Test Results'], sz: 16 -sum_sheet.add_row -sum_sheet.add_row -sum_sheet.add_row ['Note: Blue cells below are links to the Log sheet'], sz: 10 -sum_sheet.add_row -sum_sheet.add_row ['ID', 'Type', 'Match', 'Mismatch', 'Diffs', 'Errors', 'Result'], style: title_cell - -sum_sheet.column_widths 10, 10, 1 0, 11, 10, 10, 10 - -# Starting data row in summary spreadsheet (after header info) -current_row = 7 - -# Create Log Sheet -log_sheet = wb.add_worksheet name: 'Log' -log_sheet.column_widths 10 -log_sheet.add_row ['Log Detail'], sz: 16 -log_sheet.add_row - -# Add rows to summary sheet -(1..10).each do |sid| - sum_sheet.add_row [sid, 'test', '1', '2', '3', '4', '5'], style: black_cell - - if sid.odd? - link = "A#{current_row}" - sum_sheet[link].style = blue_link - sum_sheet.add_hyperlink location: "'Log'!A#{current_row}", target: :sheet, ref: link - end - - current_row += 1 -end - -p.serialize 'where_is_my_color.xlsx' diff --git a/examples/column_outlines_example.md b/examples/column_outlines_example.md new file mode 100644 index 00000000..6d402af5 --- /dev/null +++ b/examples/column_outlines_example.md @@ -0,0 +1,35 @@ +## Description + +If you have a list of data that you want to group and summarize, you can create an outline of up to eight levels. Each inner level, represented by a higher number in the outline symbols, displays detail data for the preceding outer level, represented by a lower number in the outline symbols. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +header = s.add_style bg_color: 'ADD8E6' +summary = s.add_style b: true + +wb.add_worksheet(name: 'Column outlines') do |sheet| + # Header + sheet.add_row ['Name', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Summary'], style: header + sheet.add_row ['Alice', 51, 67, 46, 23, 82, '=sum(B2:F2)'] + sheet.add_row ['Bob', 2, 97, 73, 9, 67, '=sum(B3:F3)'] + + sheet.outline_level_columns 1, 5, 1, false # From col 1 to col 5, level 1, open +end + +p.serialize 'column_outlines_example.xlsx' +``` + +## Output + + + +After closing the outline: + + diff --git a/examples/column_styles_example.md b/examples/column_styles_example.md new file mode 100644 index 00000000..87ab6d76 --- /dev/null +++ b/examples/column_styles_example.md @@ -0,0 +1,32 @@ +## Description + +There is a way to add styles to a whole column. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +percent = s.add_style num_fmt: 9, bg_color: '0000FF' + +wb.add_worksheet(name: 'Custom column types') do |sheet| + sheet.add_row ['A', 'B', 'Percent', 'Hidden', 'E'] + sheet.add_row [1, 2, 0.3, 4, 5.0] + sheet.add_row [1, 2, 0.2, 4, 5.0] + sheet.add_row [1, 2, 0.1, 4, 5.0] + + # Apply the percent style to the column at index 2 skipping the first row. + sheet.col_style 2, percent, row_offset: 1 + sheet.column_info[3].hidden = true +end + +p.serialize 'column_styles_example.xlsx' +``` + +## Output + + diff --git a/examples/column_widths_example.md b/examples/column_widths_example.md new file mode 100644 index 00000000..5d94090d --- /dev/null +++ b/examples/column_widths_example.md @@ -0,0 +1,34 @@ +## Description + +You can set the column widths + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Column widths') do |sheet| + sheet.add_row [ + 'I use autowidth and am very wide', + 'I use a custom width and am narrow' + ] + + sheet.add_row [ + 'abcdefg', + 'This is a very long text and should flow into the right cell', + nil, + 'xxx' + ] + + sheet.column_widths nil, 3, 5, nil +end + +p.serialize 'column_widths_example.xlsx' +``` + +## Output + + diff --git a/examples/comments_example.md b/examples/comments_example.md new file mode 100644 index 00000000..3aa2e877 --- /dev/null +++ b/examples/comments_example.md @@ -0,0 +1,28 @@ +## Description + +You could add comments (visible or hidden) to the cells. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Comments') do |sheet| + sheet.add_row ['Can we build it?'] + sheet.add_comment ref: 'A1', author: 'Bob', text: 'Yes We Can!' + sheet.add_comment ref: 'A2', author: 'Bob', text: 'Yes We Can! - but I dont think you need to know about it!', visible: false +end + +p.serialize 'comments_example.xlsx' +``` + +## Output + + + +After hovering on the hidden comment: + + diff --git a/examples/complex_example.md b/examples/complex_example.md new file mode 100644 index 00000000..51b85e6b --- /dev/null +++ b/examples/complex_example.md @@ -0,0 +1,84 @@ +## Description + +This is a complex example with a worksheet full of data. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +header = s.add_style bg_color: 'DD', sz: 16, b: true, alignment: { horizontal: :center } +tbl_header = s.add_style b: true, alignment: { horizontal: :center } +ind_header = s.add_style bg_color: 'FFDFDEDF', b: true, alignment: { indent: 1 } +col_header = s.add_style bg_color: 'FFDFDEDF', b: true, alignment: { horizontal: :center } +label = s.add_style alignment: { indent: 1 } +money = s.add_style num_fmt: 5 +t_label = s.add_style b: true, bg_color: 'FFDFDEDF' +t_money = s.add_style b: true, num_fmt: 5, bg_color: 'FFDFDEDF' + +wb.add_worksheet do |sheet| + sheet.add_row + sheet.add_row [nil, 'College Budget'], style: [nil, header] + + sheet.add_row + sheet.add_row [nil, "What's coming in this month.", nil, nil, 'How am I doing'], style: tbl_header + sheet.add_row [nil, 'Item', 'Amount', nil, 'Item', 'Amount'], style: [nil, ind_header, col_header, nil, ind_header, col_header] + sheet.add_row [nil, 'Estimated monthly net income', 500, nil, 'Monthly income', '=C9'], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Financial aid', 100, nil, 'Monthly expenses', '=C27'], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Allowance from mom & dad', 20000, nil, 'Semester expenses', '=F19'], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Total', '=SUM(C6:C8)', nil, 'Difference', '=F6 - SUM(F7:F8)'], style: [nil, t_label, t_money, nil, t_label, t_money] + + sheet.add_row + sheet.add_row [nil, "What's going out this month.", nil, nil, 'Semester Costs'], style: tbl_header + sheet.add_row [nil, 'Item', 'Amount', nil, 'Item', 'Amount'], style: [nil, ind_header, col_header, nil, ind_header, col_header] + sheet.add_row [nil, 'Rent', 650, nil, 'Tuition', 200], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Utilities', 120, nil, 'Lab fees', 50], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Cell phone', 100, nil, 'Other fees', 10], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Groceries', 75, nil, 'Books', 150], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Auto expenses', 0, nil, 'Deposits', 0], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Student loans', 0, nil, 'Transportation', 30], style: [nil, label, money, nil, label, money] + sheet.add_row [nil, 'Other loans', 350, nil, 'Total', '=SUM(F13:F18)'], style: [nil, label, money, nil, t_label, t_money] + sheet.add_row [nil, 'Credit cards', 450], style: [nil, label, money] + sheet.add_row [nil, 'Insurance', 0], style: [nil, label, money] + sheet.add_row [nil, 'Laundry', 10], style: [nil, label, money] + sheet.add_row [nil, 'Haircuts', 0], style: [nil, label, money] + sheet.add_row [nil, 'Medical expenses', 0], style: [nil, label, money] + sheet.add_row [nil, 'Entertainment', 500], style: [nil, label, money] + sheet.add_row [nil, 'Miscellaneous', 0], style: [nil, label, money] + sheet.add_row [nil, 'Total', '=SUM(C13:C26)'], style: [nil, t_label, t_money] + + sheet.add_chart(Axlsx::Pie3DChart) do |chart| + chart.title = sheet['B11'] + chart.add_series data: sheet['C13:C26'], labels: sheet['B13:B26'] + chart.start_at 7, 2 + chart.end_at 12, 15 + end + + sheet.add_chart(Axlsx::Bar3DChart, barDir: :col) do |chart| + chart.title = sheet['E11'] + chart.add_series labels: sheet['E13:E18'], data: sheet['F13:F18'] + chart.start_at 7, 16 + chart.end_at 12, 31 + end + + [ + 'B4:C4', + 'E4:F4', + 'B11:C11', + 'E11:F11', + 'B2:F2' + ].each { |range| sheet.merge_cells(range) } + + sheet.column_widths 2, nil, nil, 2, nil, nil, 2 +end + +p.serialize 'complex_example.xlsx' +``` + +## Output + + diff --git a/examples/conditional_formatting/example_conditional_formatting.rb b/examples/conditional_formatting/example_conditional_formatting.rb deleted file mode 100644 index 37df52f0..00000000 --- a/examples/conditional_formatting/example_conditional_formatting.rb +++ /dev/null @@ -1,89 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' - -p = Axlsx::Package.new -book = p.workbook - -# define your regular styles -percent = book.styles.add_style(:format_code => "0.00%", :border => Axlsx::STYLE_THIN_BORDER) -money = book.styles.add_style(:format_code => '0,000', :border => Axlsx::STYLE_THIN_BORDER) -status = book.styles.add_style(:border => Axlsx::STYLE_THIN_BORDER) - -# define the style for conditional formatting -profitable = book.styles.add_style( :fg_color => "428751", :type => :dxf ) -unprofitable = book.styles.add_style( :fg_color => "FF0000", :type => :dxf ) - -book.add_worksheet(:name => "Cell Is") do |ws| - - # Generate 20 rows of data - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] - 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 }) -# Apply conditional using the between operator; NOTE: supply an array to :formula for between/notBetween - ws.add_conditional_formatting("C3:C100", { :type => :cellIs, :operator => :between, :formula => ["0.00%","100.00%"], :dxfId => unprofitable, :priority => 1 }) -end - -book.add_worksheet(:name => "Color Scale") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] - end -# Apply conditional formatting to range B3:B100 in the worksheet - color_scale = Axlsx::ColorScale.new - ws.add_conditional_formatting("B3:B100", { :type => :colorScale, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1, :color_scale => color_scale }) -end - - -book.add_worksheet(:name => "Data Bar") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] - end -# Apply conditional formatting to range B3:B100 in the worksheet - data_bar = Axlsx::DataBar.new - ws.add_conditional_formatting("B3:B100", { :type => :dataBar, :dxfId => profitable, :priority => 1, :data_bar => data_bar }) -end - -book.add_worksheet(:name => "Icon Set") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - ws.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] - end -# Apply conditional formatting to range B3:B100 in the worksheet - icon_set = Axlsx::IconSet.new - ws.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set }) -end - -book.add_worksheet(:name => "Contains Text") do |ws| - ws.add_row ["Previous Year Quarterly Profits (JPY)"] - ws.add_row ["Quarter", "Profit", "% of Total", "Status"] - 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})", (10000*((rows/2-i) * (rows/2-i))) > 100000 ? "PROFIT" : "LOSS"], :style=>[nil, money, percent, status] - end - -# Apply conditional formatting to range D3:D100 in the worksheet - ws.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "PROFIT", :dxfId => profitable, :priority => 1 }) - ws.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "LOSS", :dxfId => unprofitable, :priority => 1 }) -end - -p.serialize('example_conditional_formatting.xlsx') diff --git a/examples/conditional_formatting/getting_barred.rb b/examples/conditional_formatting/getting_barred.rb deleted file mode 100644 index 6e69b6ab..00000000 --- a/examples/conditional_formatting/getting_barred.rb +++ /dev/null @@ -1,37 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -p = Axlsx::Package.new -p.workbook do |wb| - # define your regular styles - styles = wb.styles - title = styles.add_style :sz => 15, :b => true, :u => true - default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER - header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true - money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER - percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER - - # define the style for conditional formatting - its the :dxf bit that counts! - profitable = styles.add_style :fg_color => 'FF428751', :sz => 12, :type => :dxf, :b => true - - wb.add_worksheet(:name => 'Data Bar Conditional Formatting') do |ws| - ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title - ws.add_row - ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header - ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent] - - ws.merge_cells 'A1:C1' - - # Apply conditional formatting to range B4:B7 in the worksheet - data_bar = Axlsx::DataBar.new - ws.add_conditional_formatting 'B4:B7', { :type => :dataBar, - :dxfId => profitable, - :priority => 1, - :data_bar => data_bar } - end -end -p.serialize 'getting_barred.xlsx' diff --git a/examples/conditional_formatting/hitting_the_high_notes.rb b/examples/conditional_formatting/hitting_the_high_notes.rb deleted file mode 100644 index 6ea7ea0f..00000000 --- a/examples/conditional_formatting/hitting_the_high_notes.rb +++ /dev/null @@ -1,37 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -p = Axlsx::Package.new -p.workbook do |wb| - # define your regular styles - styles = wb.styles - title = styles.add_style :sz => 15, :b => true, :u => true - default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER - header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true - money = styles.add_style :format_code => '###,###,###,##0', :border => Axlsx::STYLE_THIN_BORDER - percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER - - # define the style for conditional formatting - its the :dxf bit that counts! - profitable = styles.add_style :fg_color => 'FF428751', :sz => 12, :type => :dxf, :b => true - - wb.add_worksheet(:name => 'The High Notes') do |ws| - ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title - ws.add_row - ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header - ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent] - - ws.merge_cells 'A1:C1' - - # Apply conditional formatting to range B4:B7 in the worksheet - ws.add_conditional_formatting 'B4:B7', { :type => :cellIs, - :operator => :greaterThan, - :formula => '27000000000', - :dxfId => profitable, - :priority => 1 } - end -end -p.serialize 'the_high_notes.xlsx' diff --git a/examples/conditional_formatting/scaled_colors.rb b/examples/conditional_formatting/scaled_colors.rb deleted file mode 100644 index dcbaf11d..00000000 --- a/examples/conditional_formatting/scaled_colors.rb +++ /dev/null @@ -1,39 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -p = Axlsx::Package.new -p.workbook do |wb| - # define your regular styles - styles = wb.styles - title = styles.add_style :sz => 15, :b => true, :u => true - default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER - header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true - money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER - percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER - - # define the style for conditional formatting - its the :dxf bit that counts! - profitable = styles.add_style :fg_color => 'FF428751', :sz => 12, :type => :dxf, :b => true - - wb.add_worksheet(:name => 'Scaled Colors') do |ws| - ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title - ws.add_row - ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header - ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent] - - ws.merge_cells 'A1:C1' - - # Apply conditional formatting to range B4:B7 in the worksheet - color_scale = Axlsx::ColorScale.new - ws.add_conditional_formatting 'B4:B7', { :type => :colorScale, - :operator => :greaterThan, - :formula => '27000000000', - :dxfId => profitable, - :priority => 1, - :color_scale => color_scale } - end -end -p.serialize 'scaled_colors.xlsx' diff --git a/examples/conditional_formatting/stop_and_go.rb b/examples/conditional_formatting/stop_and_go.rb deleted file mode 100644 index 301bf3fa..00000000 --- a/examples/conditional_formatting/stop_and_go.rb +++ /dev/null @@ -1,37 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -p = Axlsx::Package.new -p.workbook do |wb| - # define your regular styles - styles = wb.styles - title = styles.add_style :sz => 15, :b => true, :u => true - default = styles.add_style :border => Axlsx::STYLE_THIN_BORDER - header = styles.add_style :bg_color => '00', :fg_color => 'FF', :b => true - money = styles.add_style :format_code => '#,###,##0', :border => Axlsx::STYLE_THIN_BORDER - percent = styles.add_style :num_fmt => Axlsx::NUM_FMT_PERCENT, :border => Axlsx::STYLE_THIN_BORDER - - # define the style for conditional formatting - its the :dxf bit that counts! - profitable = styles.add_style :fg_color => 'FF428751', :sz => 12, :type => :dxf, :b => true - - wb.add_worksheet(:name => 'Downtown traffic') do |ws| - ws.add_row ['A$$le Q1 Revenue Historical Analysis (USD)'], :style => title - ws.add_row - ws.add_row ['Quarter', 'Profit', '% of Total'], :style => header - ws.add_row ['Q1-2010', '15680000000', '=B4/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2011', '26740000000', '=B5/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2012', '46330000000', '=B6/SUM(B4:B7)'], :style => [default, money, percent] - ws.add_row ['Q1-2013(est)', '72230000000', '=B7/SUM(B4:B7)'], :style => [default, money, percent] - - ws.merge_cells 'A1:C1' - - # Apply conditional formatting to range B3:B7 in the worksheet - icon_set = Axlsx::IconSet.new - ws.add_conditional_formatting 'B3:B7', { :type => :iconSet, - :dxfId => profitable, - :priority => 1, - :icon_set => icon_set } - end -end -p.serialize 'stop_and_go.xlsx' diff --git a/examples/conditional_formatting_between_example.md b/examples/conditional_formatting_between_example.md new file mode 100644 index 00000000..fc1a12cf --- /dev/null +++ b/examples/conditional_formatting_between_example.md @@ -0,0 +1,33 @@ +## Description + +Conditional format example: Between. Use array for formula. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +high = s.add_style bg_color: 'FF428751', type: :dxf + +wb.add_worksheet(name: 'Conditional') do |sheet| + # Use 10 random number + sheet.add_row Array.new(10) { (rand * 10).floor } + + sheet.add_conditional_formatting('A1:J1', + type: :cellIs, + operator: :between, + formula: ['3', '7'], + dxfId: high, + priority: 1) +end + +p.serialize 'conditional_formatting_between_example.xlsx' +``` + +## Output + + diff --git a/examples/conditional_formatting_color_scale_example.md b/examples/conditional_formatting_color_scale_example.md new file mode 100644 index 00000000..cebb10dc --- /dev/null +++ b/examples/conditional_formatting_color_scale_example.md @@ -0,0 +1,39 @@ +## Description + +Conditional format example: Color scale. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +high = s.add_style bg_color: 'FF428751', type: :dxf + +wb.add_worksheet(name: 'Conditional') do |sheet| + # Use 10 random number + sheet.add_row Array.new(10) { (rand * 10).floor } + + # Axlsx::ColorScale 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('A1:J1', + type: :colorScale, + operator: :greaterThan, + formula: '7', + dxfId: high, + priority: 1, + color_scale: color_scale) +end + +p.serialize 'conditional_formatting_color_scale_example.xlsx' +``` + +## Output + + diff --git a/examples/conditional_formatting_data_bar_example.md b/examples/conditional_formatting_data_bar_example.md new file mode 100644 index 00000000..1772530a --- /dev/null +++ b/examples/conditional_formatting_data_bar_example.md @@ -0,0 +1,33 @@ +## Description + +Conditional format example: Data bar. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +high = s.add_style bg_color: 'FF428751', type: :dxf + +wb.add_worksheet(name: 'Conditional') do |sheet| + # Use 10 random number + sheet.add_row Array.new(10) { (rand * 10).floor } + + data_bar = Axlsx::DataBar.new + sheet.add_conditional_formatting('A1:J1', + type: :dataBar, + dxfId: high, + priority: 1, + data_bar: data_bar) +end + +p.serialize 'conditional_formatting_data_bar_example.xlsx' +``` + +## Output + + diff --git a/examples/conditional_formatting_greater_than_example.md b/examples/conditional_formatting_greater_than_example.md new file mode 100644 index 00000000..a2ee8982 --- /dev/null +++ b/examples/conditional_formatting_greater_than_example.md @@ -0,0 +1,33 @@ +## Description + +Conditional format example: Greater Than. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +high = s.add_style bg_color: 'FF428751', type: :dxf + +wb.add_worksheet(name: 'Conditional') do |sheet| + # Use 10 random number + sheet.add_row Array.new(10) { (rand * 10).floor } + + sheet.add_conditional_formatting('A1:J1', + type: :cellIs, + operator: :greaterThan, + formula: '7', + dxfId: high, + priority: 1) +end + +p.serialize 'conditional_formatting_greater_than_example.xlsx' +``` + +## Output + + diff --git a/examples/conditional_formatting_icon_set_example.md b/examples/conditional_formatting_icon_set_example.md new file mode 100644 index 00000000..534154f4 --- /dev/null +++ b/examples/conditional_formatting_icon_set_example.md @@ -0,0 +1,29 @@ +## Description + +Conditional format example: Icon set. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +high = s.add_style bg_color: 'FF428751', type: :dxf + +wb.add_worksheet(name: 'Conditional') do |sheet| + # Use 10 random number + sheet.add_row Array.new(10) { (rand * 10).floor } + + icon_set = Axlsx::IconSet.new + sheet.add_conditional_formatting('A1:J1', { type: :iconSet, dxfId: high, priority: 1, icon_set: icon_set }) +end + +p.serialize 'conditional_formatting_icon_set_example.xlsx' +``` + +## Output + + diff --git a/examples/custom_styles_example.md b/examples/custom_styles_example.md new file mode 100644 index 00000000..aa602ea1 --- /dev/null +++ b/examples/custom_styles_example.md @@ -0,0 +1,31 @@ +## Description + +Each cell allows a single, predefined style. When using `add_row`, the value in the `style` array at the same index as the cell's column will be applied to that cell. Alternatively, you can apply a style to an entire row by using an integer value for `style`. + + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +black_cell = s.add_style bg_color: '00', fg_color: 'FF', sz: 14, alignment: { horizontal: :center } +blue_cell = s.add_style bg_color: '0000FF', fg_color: 'FF', sz: 20, alignment: { horizontal: :center } + +wb.add_worksheet(name: 'Custom Styles') do |sheet| + # Applies the black_cell style to the first and third cell, and the blue_cell style to the second. + sheet.add_row ['Text Autowidth', 'Second', 'Third'], style: [black_cell, blue_cell, black_cell] + + # Applies the thin border to all three cells + sheet.add_row [1, 2, 3], style: Axlsx::STYLE_THIN_BORDER +end + +p.serialize 'custom_styles_example.xlsx' +``` + +## Output + + diff --git a/examples/data_validation.rb b/examples/data_validation.rb deleted file mode 100644 index b95a6397..00000000 --- a/examples/data_validation.rb +++ /dev/null @@ -1,67 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' - -p = Axlsx::Package.new -p.workbook.add_worksheet do |ws| - - ws.add_row ["between", "lessThan", "bound list", "raw list"] - - 4.times do |i| - ws.add_row [nil, nil, nil, nil, (i+1) * 2] - end - - ws.add_data_validation("A2:A5", { - :type => :whole, - :operator => :between, - :formula1 => '5', - :formula2 => '10', - :showErrorMessage => true, - :errorTitle => 'Wrong input', - :error => 'Only values between 5 and 10', - :errorStyle => :information, - :showInputMessage => true, - :promptTitle => 'Be careful!', - :prompt => %{We really want a value between 5 and 10, -but it is OK if you want to break the rules. -}}) - - ws.add_data_validation("B1:B5", { - :type => :textLength, - :operator => :lessThan, - :formula1 => '10', - :showErrorMessage => true, - :errorTitle => 'Text is too long', - :error => 'Max text length is 10 characters', - :errorStyle => :stop, - :showInputMessage => true, - :promptTitle => 'Text length', - :prompt => 'Max text length is 10 characters'}) - - ws.add_data_validation("C2:C5", { - :type => :list, - :formula1 => 'E2:E5', - :showDropDown => false, - :showErrorMessage => true, - :errorTitle => '', - :error => 'Only values from E2:E5', - :errorStyle => :stop, - :showInputMessage => true, - :promptTitle => '', - :prompt => 'Only values from E2:E5'}) - - ws.add_data_validation("D2:D5", { - :type => :list, - :formula1 => '"Red, Orange, NavyBlue"', - :showDropDown => false, - :showErrorMessage => true, - :errorTitle => '', - :error => 'Please use the dropdown selector to choose the value', - :errorStyle => :stop, - :showInputMessage => true, - :prompt => '& Choose the value from the dropdown'}) - -end - -p.serialize 'data_validation.xlsx' diff --git a/examples/defined_name_example.md b/examples/defined_name_example.md new file mode 100644 index 00000000..ce998263 --- /dev/null +++ b/examples/defined_name_example.md @@ -0,0 +1,24 @@ +## Description + +You could use defined names in formulas + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Defined name') do |sheet| + sheet.add_row [1, 2, 17, '=FOOBAR'] + + wb.add_defined_name("'defined name'!$C1", local_sheet_id: sheet.index, name: 'FOOBAR') +end + +p.serialize 'defined_name_example.xlsx' +``` + +## Output + + diff --git a/examples/escape_formula_example.md b/examples/escape_formula_example.md new file mode 100644 index 00000000..fb23ce66 --- /dev/null +++ b/examples/escape_formula_example.md @@ -0,0 +1,27 @@ +## Description + +You could escape formulas + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Escaping Formulas') do |sheet| + sheet.add_row [1, 2, 3, '=SUM(A2:C2)'], escape_formulas: true + sheet.add_row [ + '=IF(2+2=4,4,5)', + '=IF(13+13=4,4,5)', + '=IF(99+99=4,4,5)' + ], escape_formulas: [true, false, true] +end + +p.serialize 'escape_formula_example.xlsx' +``` + +## Output + + diff --git a/examples/example.rb b/examples/example.rb deleted file mode 100755 index 8fcfe7d4..00000000 --- a/examples/example.rb +++ /dev/null @@ -1,900 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" - -#```ruby -require 'axlsx' -examples = [] -examples << :basic -examples << :custom_styles -examples << :wrap_text -examples << :cell_style_override -examples << :custom_borders -examples << :surrounding_border -examples << :deep_custom_borders -examples << :row_column_style -examples << :fixed_column_width -examples << :height -examples << :outline_level -examples << :merge_cells -examples << :images -examples << :format_dates -examples << :mbcs -examples << :formula -examples << :escape_formulas -examples << :auto_filter -examples << :sheet_protection -examples << :data_types -examples << :override_data_types -examples << :hyperlinks -examples << :number_currency_format -examples << :venezuela_currency -examples << :bar_chart -examples << :chart_gridlines -examples << :pie_chart -examples << :line_chart -examples << :scatter_chart -examples << :tables -examples << :fit_to_page -examples << :hide_gridlines -examples << :repeated_header -examples << :defined_name -examples << :printing -examples << :header_footer -examples << :comments -examples << :panes -examples << :book_view -examples << :sheet_view -examples << :hiding_sheets -examples << :conditional_formatting -examples << :streaming -examples << :shared_strings -examples << :no_autowidth -examples << :cached_formula -examples << :page_breaks -examples << :rich_text -examples << :tab_color - -p = Axlsx::Package.new -wb = p.workbook -#``` - -## A Simple Workbook - -#```ruby -if examples.include? :basic - wb.add_worksheet(:name => "Basic Worksheet") do |sheet| - sheet.add_row ["First Column", "Second", "Third"] - sheet.add_row [1, 2, 3] - sheet.add_row [' preserving whitespace'] - end -end -#``` - -#Using Custom Styles - -#```ruby -# Each cell allows a single, predified style. -# When using add_row, the value in the :style array at the same index as the cell's column will be applied to that cell. -# Alternatively, you can apply a style to an entire row by using an integer value for :style. -if examples.include? :custom_styles - wb.styles do |s| - black_cell = s.add_style :bg_color => "00", :fg_color => "FF", :sz => 14, :alignment => { :horizontal=> :center } - blue_cell = s.add_style :bg_color => "0000FF", :fg_color => "FF", :sz => 20, :alignment => { :horizontal=> :center } - wb.add_worksheet(:name => "Custom Styles") do |sheet| - - # Applies the black_cell style to the first and third cell, and the blue_cell style to the second. - sheet.add_row ["Text Autowidth", "Second", "Third"], :style => [black_cell, blue_cell, black_cell] - - # 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 - wb.add_worksheet(:name => "Cell Level Style Overrides") do |sheet| - - # this will set the font size for each cell. - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'], :sz => 16 - - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] - sheet.add_row %w(u shadow sz b i strike outline) - sheet.rows.last.cells[0].u = :double - sheet.rows.last.cells[1].shadow = true - sheet.rows.last.cells[2].sz = 20 - sheet.rows.last.cells[3].b = true - sheet.rows.last.cells[4].i = true - sheet.rows.last.cells[5].strike = true - sheet.rows.last.cells[6].outline = 1 - # You can also apply cell style overrides to a range of cells - sheet["A1:D1"].each { |c| c.color = "FF0000" } - sheet['A1:D2'].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } - end -end -##``` - -##Using Custom Border Styles - -#```ruby -#Axlsx defines a thin border style, but you can easily create and use your own. -if examples.include? :custom_borders - wb.styles do |s| - red_border = s.add_style :border => { :style => :thick, :color =>"FFFF0000", :edges => [:left, :right] } - blue_border = s.add_style :border => { :style => :thick, :color =>"FF0000FF"} - - wb.add_worksheet(:name => "Custom Borders") do |sheet| - sheet.add_row ["wrap", "me", "Up in Red"], :style => red_border - sheet.add_row [1, 2, 3], :style => blue_border - end - end -end - -#```ruby -# More Custom Borders -if examples.include? :surrounding_border - - # Stuff like this is why I LOVE RUBY - # If you dont know about hash default values - # LEARN IT! LIVE IT! LOVE IT! - defaults = { :style => :thick, :color => "000000" } - borders = Hash.new do |hash, key| - hash[key] = wb.styles.add_style :border => defaults.merge( { :edges => key.to_s.split('_').map(&:to_sym) } ) - end - top_row = [0, borders[:top_left], borders[:top], borders[:top], borders[:top_right]] - middle_row = [0, borders[:left], nil, nil, borders[:right]] - bottom_row = [0, borders[:bottom_left], borders[:bottom], borders[:bottom], borders[:bottom_right]] - - wb.add_worksheet(:name => "Surrounding Border") do |ws| - ws.add_row [] - ws.add_row ['', 1,2,3,4], :style => top_row - ws.add_row ['', 5,6,7,8], :style => middle_row - ws.add_row ['', 9, 10, 11, 12] - - #This works too! - ws.rows.last.style = bottom_row - - 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] } - border = s.borders[s.cellXfs[top_bottom].borderId] - # edit existing border parts - border.prs.each do |part| - case part.name - when :top - part.color = Axlsx::Color.new(:rgb => "FFFF0000") - when :bottom - part.color = Axlsx::Color.new(:rgb => "FF00FF00") - end - end - - border.prs << Axlsx::BorderPr.new(:name => :left, :color => Axlsx::Color.new(:rgb => '0000FF'), :style => :mediumDashed) - wb.add_worksheet(:name => 'hacked borders') do |sheet| - sheet.add_row [1,2,3], :style=>top_bottom - end - end -end -##``` - - -##Styling Rows and Columns - -#```ruby -if examples.include? :row_column_style - wb.styles do |s| - head = s.add_style :bg_color => "00", :fg_color => "FF" - percent = s.add_style :num_fmt => 9 - wb.add_worksheet(:name => "Columns and Rows") do |sheet| - # Note: you must add rows to the document *BEFORE* applying column styles to them - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4', 'col5'] - sheet.add_row [1, 2, 0.3, 4, 5.0] - sheet.add_row [1, 2, 0.2, 4, 5.0] - sheet.add_row [1, 2, 0.1, 4, 5.0] - - #apply the percent style to the column at index 2 skipping the first row. - sheet.col_style 2, percent, :row_offset => 1 - - # apply the head style to the first row. - sheet.row_style 0, head - - #Hide the 5th column - sheet.column_info[4].hidden = true - - #Set the second column outline level - sheet.column_info[1].outlineLevel = 2 - - sheet.rows[3].hidden = true - sheet.rows[1].outlineLevel = 2 - end - end -end -##``` - - -##Specifying Column Widths - -#```ruby -if examples.include? :fixed_column_width - wb.add_worksheet(:name => "custom column widths") do |sheet| - sheet.add_row ["I use autowidth and am very wide", "I use a custom width and am narrow"] - sheet.add_row ['abcdefg', 'This is a very long text and should flow into the right cell', nil, 'xxx' ] - sheet.column_widths nil, 3, 5, nil - end -end - - -##Specifying Row height - -#```ruby -if examples.include? :height - wb.styles do |s| - head = s.add_style :bg_color => "00", :fg_color => "FF" - wb.add_worksheet(:name => "fixed row height") do |sheet| - sheet.add_row ["This row will have a fixed height", "It will overwite the default row height"], :height => 30 - sheet.add_row ["This row can have a different height too"], :height => 10, :style => head - end - end -end - - -#```ruby -if examples.include? :outline_level - wb.add_worksheet(name: 'outline_level') do |sheet| - sheet.add_row [1, 2, 3, Time.now, 5, 149455.15] - sheet.add_row [1, 2, 5, 6, 5,14100.19] - sheet.add_row [9500002267, 1212, 1212, 5,14100.19] - sheet.outline_level_rows 0, 2 - sheet.outline_level_columns 0, 2 - end -end -##``` - -##Merging Cells. - -#```ruby -if examples.include? :merge_cells - wb.add_worksheet(:name => 'Merging Cells') do |sheet| - # cell level style overides when adding cells - sheet.add_row ["col 1", "col 2", "col 3", "col 4"], :sz => 16 - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] - sheet.add_row [2, 3, 4, "=SUM(A3:C3)"] - sheet.add_row ["total", "", "", "=SUM(D2:D3)"] - sheet.merge_cells("A4:C4") - sheet["A1:D1"].each { |c| c.color = "FF0000"} - sheet["A1:D4"].each { |c| c.style = Axlsx::STYLE_THIN_BORDER } - end -end -##``` - -##Add an Image with a hyperlink - -#```ruby -if examples.include? :images - wb.add_worksheet(:name => "Image with Hyperlink") do |sheet| - img = File.expand_path('../image1.jpeg', __FILE__) - # specifying the :hyperlink option will add a hyper link to your image. - # - # @note - Numbers does not support this part of the specification. - - sheet.add_image(:image_src => img, :noSelect => true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") do |image| - image.width=720 - image.height=666 - image.hyperlink.tooltip = "Labeled Link" - image.start_at 0, 0 - end - - # position in block - sheet.add_image(:image_src => img, :noSelect => true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") do |image| - image.start_at 22, 14 - image.end_at 23, 17 - end - # all in one go - sheet.add_image(:image_src => img, :start_at => [15, 33], :end_at => [20, 37], :noSelect => true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") - end -end -#``` - -##Using Custom Formatting and date1904 - -#```ruby -if examples.include? :format_dates - require 'date' - wb.styles do |s| - date = s.add_style(:format_code => "yyyy-mm-dd", :border => Axlsx::STYLE_THIN_BORDER) - padded = s.add_style(:format_code => "00#", :border => Axlsx::STYLE_THIN_BORDER) - percent = s.add_style(:format_code => "0000%", :border => Axlsx::STYLE_THIN_BORDER) - # wb.date1904 = true # Use the 1904 date system (Used by Excel for Mac < 2011) - wb.add_worksheet(:name => "Formatting Data") do |sheet| - sheet.add_row ["Custom Formatted Date", "Percent Formatted Float", "Padded Numbers"], :style => Axlsx::STYLE_THIN_BORDER - sheet.add_row [Date::strptime('2012-01-19','%Y-%m-%d'), 0.2, 32], :style => [date, percent, padded] - end - end -end -#``` - -##Asian Language Support - -#```ruby -if examples.include? :mbcs - wb.styles.fonts.first.name = 'Arial Unicode MS' - wb.add_worksheet(:name => "日本語でのシート名") do |sheet| - sheet.add_row ["日本語"] - sheet.add_row ["华语/華語"] - sheet.add_row ["한국어/조선말"] - end -end -##``` - -##Using formula - -#```ruby -if examples.include? :formula - wb.add_worksheet(:name => "Using Formulas") do |sheet| - sheet.add_row ['col 1', 'col 2', 'col 3', 'col 4'] - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"] - end -end -##``` - -##Escaping formulas for cells -#```ruby -if examples.include? :escape_formulas - wb.add_worksheet(:name => "Escaping Formulas") do |sheet| - sheet.add_row [1, 2, 3, "=SUM(A2:C2)"], escape_formulas: true - sheet.add_row [ - "=IF(2+2=4,4,5)", - "=IF(13+13=4,4,5)", - "=IF(99+99=4,4,5)" - ], escape_formulas: [true, false, true] - end - p.serialize("escaped_formulas.xlsx") -end -##``` - -##Auto Filter - -#```ruby -if examples.include? :auto_filter - wb.add_worksheet(:name => "Auto Filter") do |sheet| - sheet.add_row ["Build Matrix"] - sheet.add_row ["Build", "Duration", "Finished", "Rvm"] - sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] - sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] - sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] - sheet.auto_filter = "A2:D5" - sheet.auto_filter.add_column 3, :filters, :filter_items => ['1.9.2', '1.8.7'] - end -end -#``` - -##Automatic cell types - - -#```ruby -if examples.include? :data_types - wb.add_worksheet(:name => "Automatic cell types") do |sheet| - date_format = wb.styles.add_style :format_code => 'YYYY-MM-DD' - time_format = wb.styles.add_style :format_code => 'hh:mm:ss' - sheet.add_row ["Date", "Time", "String", "Boolean", "Float", "Integer"] - sheet.add_row [Date.today, Time.now, "value", true, 0.1, 1], :style => [date_format, time_format] - end -end - -#```ruby -if examples.include? :override_data_types - wb.add_worksheet(:name => "Override Data Type") do |sheet| - sheet.add_row ['dont eat my zeros!', '0088'] , :types => [nil, :string] - end -end -# 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 - # internal references - sheet.add_hyperlink :location => "'Next Sheet'!A1", :ref => 'A2', :target => :sheet - sheet.add_row ['next sheet'] - end - - wb.add_worksheet(:name => 'Next Sheet') do |sheet| - sheet.add_row ['hello!'] - end -end -###``` - -##Number formatting and currency -if examples.include? :number_currency_format - wb.add_worksheet(:name => "Formats and Currency") do |sheet| - currency = wb.styles.add_style :num_fmt => 5 - red_negative = wb.styles.add_style :num_fmt => 8 - comma = wb.styles.add_style :num_fmt => 3 - super_funk = wb.styles.add_style :format_code => '[Green]#' - sheet.add_row %w(Currency RedNegative Comma Custom) - sheet.add_row [1500, -122.34, 123456789, 594829], :style=> [currency, red_negative, comma, super_funk] - end -end - -## Venezuala currency -if examples.include? :venezuela_currency - wb.add_worksheet(:name => 'Venezuala_currency') do |sheet| - number = wb.styles.add_style :format_code => '#.##0\,00' - sheet.add_row [2.5] , :style => [number] - end -end - -##Generating A Bar Chart - -#```ruby -if examples.include? :bar_chart - wb.add_worksheet(:name => "Bar Chart") do |sheet| - sheet.add_row ["A Simple Bar Chart"] - %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] } - sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A6", :end_at => "F20") do |chart| - chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"], :title => sheet["A1"], :colors => ["00FF00", "0000FF"] - end - end -end - -##``` - -##Hide Gridlines in chart - -#```ruby -if examples.include? :chart_gridlines - wb.add_worksheet(:name => "Chart With No Gridlines") do |sheet| - sheet.add_row ["Bar Chart without gridlines"] - %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] } - sheet.add_chart(Axlsx::Bar3DChart, :start_at => "A6", :end_at => "F20") do |chart| - chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"], :colors => ["00FF00", "FF0000"] - chart.valAxis.gridlines = false - chart.catAxis.gridlines = false - end - end -end -#``` - -##Generating A Pie Chart - -#```ruby -if examples.include? :pie_chart - wb.add_worksheet(:name => "Pie Chart") do |sheet| - sheet.add_row ["Simple Pie Chart"] - %w(first second third).each { |label| sheet.add_row [label, rand(24)+1] } - sheet.add_chart(Axlsx::Pie3DChart, :start_at => [0,5], :end_at => [10, 20], :title => "example 3: Pie Chart") do |chart| - chart.add_series :data => sheet["B2:B4"], :labels => sheet["A2:A4"], :colors => ['FF0000', '00FF00', '0000FF'] - end - end -end -#``` - -##Generating A Line Chart - -#```ruby -if examples.include? :line_chart - wb.add_worksheet(:name => "Line Chart") do |sheet| - sheet.add_row ["Simple Line Chart"] - sheet.add_row %w(first second) - 4.times do - sheet.add_row [ rand(24)+1, rand(24)+1] - end - sheet.add_chart(Axlsx::Line3DChart, :title => "Simple 3D Line Chart", :rotX => 30, :rotY => 20) do |chart| - chart.start_at 0, 5 - chart.end_at 10, 20 - chart.add_series :data => sheet["A3:A6"], :title => sheet["A2"], :color => "0000FF" - chart.add_series :data => sheet["B3:B6"], :title => sheet["B2"], :color => "FF0000" - chart.catAxis.title = 'X Axis' - chart.valAxis.title = 'Y Axis' - end - sheet.add_chart(Axlsx::LineChart, :title => "Simple Line Chart", :rotX => 30, :rotY => 20) do |chart| - chart.start_at 0, 21 - chart.end_at 10, 41 - chart.add_series :data => sheet["A3:A6"], :title => sheet["A2"], :color => "FF0000", :show_marker => true, :smooth => true - chart.add_series :data => sheet["B3:B6"], :title => sheet["B2"], :color => "00FF00" - chart.catAxis.title = 'X Axis' - chart.valAxis.title = 'Y Axis' - end - - end -end -#``` - -##Generating A Scatter Chart - -#```ruby -if examples.include? :scatter_chart - wb.add_worksheet(:name => "Scatter Chart") do |sheet| - sheet.add_row ["First", 1, 5, 7, 9] - sheet.add_row ["", 1, 25, 49, 81] - sheet.add_row ["Second", 5, 2, 14, 9] - sheet.add_row ["", 5, 10, 15, 20] - sheet.add_chart(Axlsx::ScatterChart, :title => "example 7: Scatter Chart") do |chart| - chart.start_at 0, 4 - chart.end_at 10, 19 - chart.add_series :xData => sheet["B1:E1"], :yData => sheet["B2:E2"], :title => sheet["A1"], :color => "FF0000" - chart.add_series :xData => sheet["B3:E3"], :yData => sheet["B4:E4"], :title => sheet["A3"], :color => "00FF00" - end - end -end -#``` - - -##Tables - -#```ruby -if examples.include? :tables - wb.add_worksheet(:name => "Table") do |sheet| - sheet.add_row ["Build Matrix"] - sheet.add_row ["Build", "Duration", "Finished", "Rvm"] - sheet.add_row ["19.1", "1 min 32 sec", "about 10 hours ago", "1.8.7"] - sheet.add_row ["19.2", "1 min 28 sec", "about 10 hours ago", "1.9.2"] - sheet.add_row ["19.3", "1 min 35 sec", "about 10 hours ago", "1.9.3"] - sheet.add_table "A2:D5", :name => 'Build Matrix', :style_info => { :name => "TableStyleMedium23" } - end -end -#``` - - -##Fit to page printing - -#```ruby -if examples.include? :fit_to_page - wb.add_worksheet(:name => "fit to page") do |sheet| - sheet.add_row ['this all goes on one page'] - sheet.page_setup.fit_to :width => 1, :height => 1 - end -end -##``` - - -##Hide Gridlines in worksheet - -#```ruby -if examples.include? :hide_gridlines - wb.add_worksheet(:name => "No Gridlines") do |sheet| - sheet.add_row ["This", "Sheet", "Hides", "Gridlines"] - sheet.sheet_view.show_grid_lines = false - end -end -##``` - -# Repeat printing of header rows. -#```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') - end -end - -# Defined Names in formula -if examples.include? :defined_name - wb.add_worksheet(:name => 'defined name') do |sheet| - sheet.add_row [1, 2, 17, '=FOOBAR'] - wb.add_defined_name("'defined name'!$C1", :local_sheet_id => sheet.index, :name => 'FOOBAR') - wb.add_defined_name("'defined name'!$A$1:$C$1", :local_sheet_id => sheet.index, :name => '_xlnm.Print_Area') - end -end - -# Sheet Protection and excluding cells from locking. -if examples.include? :sheet_protection - unlocked = wb.styles.add_style :locked => false - wb.add_worksheet(:name => 'Sheet Protection') do |sheet| - sheet.sheet_protection do |protection| - protection.password = 'fish' - protection.auto_filter = false - end - - sheet.add_row [1, 2 ,3], :style => unlocked # These cells will be locked - sheet.add_row [4, 5, 6] - sheet.add_row [7, 8, 9] - sheet.auto_filter = "A1:C3" - end - -end - -##Specify page margins and other options for printing - -#```ruby -if examples.include? :printing - margins = {:left => 3, :right => 3, :top => 1.2, :bottom => 1.2, :header => 0.7, :footer => 0.7} - setup = {:fit_to_width => 1, :orientation => :landscape, :paper_width => "297mm", :paper_height => "210mm"} - options = {:grid_lines => true, :headings => true, :horizontal_centered => true} - wb.add_worksheet(:name => "print margins", :page_margins => margins, :page_setup => setup, :print_options => options) do |sheet| - sheet.add_row ["this sheet uses customized print settings"] - end -end -#``` - -## Add 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!' - sheet.add_comment :ref => 'A2', :author => 'Bob', :text => 'Yes We Can! - but I dont think you need to know about it!', :visible => false - - end -end - -## Frozen/Split panes -## ``` ruby -if examples.include? :panes - wb.add_worksheet(:name => 'panes') do |sheet| - sheet.add_row(['', (0..99).map { |i| "column header #{i}" }].flatten ) - 100.times.with_index { |index| sheet << ["row header", (0..index).to_a].flatten } - sheet.sheet_view.pane do |pane| - pane.top_left_cell = "B2" - pane.state = :frozen_split - pane.y_split = 1 - pane.x_split = 1 - pane.active_pane = :bottom_right - end - end -end - -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 - -## Book Views -# -## Book views let you specify which sheet the show as active when the user opens the work book as well as a bunch of other -## tuning values for the UI @see Axlsx::WorkbookView -## ```ruby -if examples.include? :book_view - # when you open example.xml the second sheet is selected, and the horizontal scroll bar is much smaller showing more sheets - wb.add_view tab_ratio: 800, active_tab: 1 -end - -## Hiding Sheets -## -## Sheets can be hidden with the state attribute -if examples.include? :hiding_sheets - wb.add_worksheet name: 'hidden', state: :hidden do |sheet| - sheet.add_row ['you cant see me!'] - end - wb.add_worksheet name: 'very hidden', state: :very_hidden do |sheet| - sheet.add_row ['you really cant see me!'] - 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) - status = wb.styles.add_style(:border => Axlsx::STYLE_THIN_BORDER) - - # define the style for conditional formatting - profitable = wb.styles.add_style( :fg_color => "FF428751", :type => :dxf ) - unprofitable = wb.styles.add_style( :fg_color => "FF0000", :type => :dxf ) - - wb.add_worksheet(:name => "Conditional Cell Is") do |sheet| - - # Generate 20 rosheet of data - sheet.add_row ["Previous Year Quarterly Profits (JPY)"] - sheet.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rosheet = 20 - offset.upto(rosheet + offset) do |i| - sheet.add_row ["Q#{i}", 10000*((rosheet/2-i) * (rosheet/2-i)), "=100*B#{i}/SUM(B3:B#{rosheet+offset})"], :style=>[nil, money, percent] - end - - # Apply conditional formatting to range B3:B100 in the worksheet - sheet.add_conditional_formatting("B3:B100", { :type => :cellIs, :operator => :greaterThan, :formula => "100000", :dxfId => profitable, :priority => 1 }) - # Apply conditional using the between operator; NOTE: supply an array to :formula for between/notBetween - sheet.add_conditional_formatting("C3:C100", { :type => :cellIs, :operator => :between, :formula => ["0.00%","100.00%"], :dxfId => unprofitable, :priority => 1 }) - end - - wb.add_worksheet(:name => "Conditional Color Scale") do |sheet| - sheet.add_row ["Previous Year Quarterly Profits (JPY)"] - sheet.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rosheet = 20 - offset.upto(rosheet + offset) do |i| - sheet.add_row ["Q#{i}", 10000*((rosheet/2-i) * (rosheet/2-i)), "=100*B#{i}/SUM(B3:B#{rosheet+offset})"], :style=>[nil, money, percent] - end - # color scale has two_tone and three_tone class methods to setup the excel defaults (2011) - # alternatively, you can pass in {:type => [:min, :max, :percent], :val => [whatever], :color =>[Some RGB String] to create a customized color scale object - - 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 |sheet| - sheet.add_row ["Previous Year Quarterly Profits (JPY)"] - sheet.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - sheet.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] - end - # Apply conditional formatting to range B3:B100 in the worksheet - data_bar = Axlsx::DataBar.new - sheet.add_conditional_formatting("B3:B100", { :type => :dataBar, :dxfId => profitable, :priority => 1, :data_bar => data_bar }) - end - - wb.add_worksheet(:name => "Conditional Format Icon Set") do |sheet| - sheet.add_row ["Previous Year Quarterly Profits (JPY)"] - sheet.add_row ["Quarter", "Profit", "% of Total"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - sheet.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})"], :style=>[nil, money, percent] - end - # Apply conditional formatting to range B3:B100 in the worksheet - icon_set = Axlsx::IconSet.new - sheet.add_conditional_formatting("B3:B100", { :type => :iconSet, :dxfId => profitable, :priority => 1, :icon_set => icon_set }) - end - - wb.add_worksheet(:name => "Contains Text") do |sheet| - sheet.add_row ["Previous Year Quarterly Profits (JPY)"] - sheet.add_row ["Quarter", "Profit", "% of Total", "Status"] - offset = 3 - rows = 20 - offset.upto(rows + offset) do |i| - sheet.add_row ["Q#{i}", 10000*((rows/2-i) * (rows/2-i)), "=100*B#{i}/SUM(B3:B#{rows+offset})", (10000*((rows/2-i) * (rows/2-i))) > 100000 ? "PROFIT" : "LOSS"], :style=>[nil, money, percent, status] - end - # Apply conditional formatting to range D3:D100 in the worksheet to match words. - sheet.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "PROFIT", :dxfId => profitable, :priority => 1 }) - sheet.add_conditional_formatting("D3:D100", { :type => :containsText, :operator => :equal, :text => "LOSS", :dxfId => unprofitable, :priority => 1 }) - end -end - -# Page Breaks -if examples.include? :page_breaks - ws = wb.add_worksheet(:name => "page breaks") do |sheet| - sheet.add_row ["A"] * 10 - sheet.add_row ["A"] * 10 - sheet.add_page_break("B2") - sheet.sheet_view.view = :page_layout # so you can see the breaks! - end -end - -#Validate and Serialize - -#```ruby -# Serialize directly to file - -p.serialize("example.xlsx") - -# or - -#Serialize to a stream -if examples.include? :streaming - s = p.to_stream() - File.open('example_streamed.xlsx', 'w') { |f| f.write(s.read) } -end -#``` - -##Using Shared Strings - -#```ruby -# This is required by Numbers -if examples.include? :shared_strings - p.use_shared_strings = true - p.serialize("shared_strings_example.xlsx") -end -#``` - -#```ruby -if examples.include? :no_autowidth - p = Axlsx::Package.new - p.use_autowidth = false - wb = p.workbook - wb.add_worksheet(:name => "Manual Widths") do | sheet | - sheet.add_row ['oh look! no autowidth'] - end - p.serialize("no-use_autowidth.xlsx") -end -#``` - -#```ruby -if examples.include? :cached_formula - p = Axlsx::Package.new - p.use_shared_strings = true - wb = p.workbook - wb.add_worksheet(:name => "cached formula") do | sheet | - sheet.add_row [1, 2, '=A1+B1'], :formula_values => [nil, nil, 3] - end - p.serialize 'cached_formula.xlsx' -end -#``` - -#```ruby -if examples.include? :rich_text - p = Axlsx::Package.new - p.use_shared_strings = true - wb = p.workbook - wrap_text = wb.styles.add_style({:alignment => {:horizontal => :center, :vertical => :center, :wrap_text => true}} ) - rt = Axlsx::RichText.new - rt.add_run('I\'m bold, ', :b => true) - rt.add_run('I\'m italic, ', :i => true) - rt.add_run('I\'m strike' + "\n", :strike => true) - rt.add_run('I\'m bold, italic and strike' + "\n", :b => true, :i => true, :strike => true) - rt.add_run('I\'m style-less :D') - wb.add_worksheet(:name => "RichText") do | sheet | - sheet.add_row [rt], :style => wrap_text - end - p.serialize 'rich_text.xlsx' -end -#``` - -##Change tab color of sheet - -#```ruby -if examples.include? :tab_color - p = Axlsx::Package.new - p.use_shared_strings = true - wb = p.workbook - wb.add_worksheet(:name => "Change Tab Color") do |sheet| - sheet.add_row ["Check", "out", "the", "Tab Color", "below!"] - sheet.sheet_pr.tab_color = "FFFF6666" - end - p.serialize 'tab_color.xlsx' -end -##``` diff --git a/examples/extractive.rb b/examples/extractive.rb deleted file mode 100644 index d8b57084..00000000 --- a/examples/extractive.rb +++ /dev/null @@ -1,45 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- - -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' - -data = [ ['oil-bbl',9591.411671,8860.714604,8237.936509,7700.523067,7231.831842,6819.307902,6453.297235,6126.251755,5832.182748,5566.276532,5324.618706,5103.992757,4901.730597,4715.600022,4543.718862,4384.488699,4236.543136,4098.707009,3969.963937,3849.430276,3736.33405,3629.997786,3529.824424,3435.28568,3345.912388,3261.286414,3181.033882,3104.819438,3032.341401,2963.327615,2897.531915,2834.731083,2774.722219,2717.320479,2662.357097,2609.677666,2559.140642,2510.616021,2463.984182,2419.134871,2375.966292,2334.384311,2294.301748,2255.637735,2218.317165,2182.270173,2147.431696,2113.741056,2081.141602,2049.580377,2019.007821,1989.377502,1960.645871,1932.772042,1905.71759,1879.446367,1853.924338,1829.119424,1805.001366,1781.541597,1758.713122,1736.490414,1714.849315,1693.766943,1673.22161,1653.192744,1633.660817,1614.607284,1596.014514,1577.865741,1560.145009,1542.837121,1525.927597,1509.402632,1493.249056,1477.454298,1462.006352,1446.893746,1432.105512,1417.631159,1403.460647,1389.584361,1375.993094,1362.678018,1349.630672,1336.842938,1324.307026,1312.015455,1299.961043,1288.136885,1276.536347,1265.153047,1253.980847,1243.013838,1232.246332,1221.672852,1211.288119,1201.087047,1191.064732,1181.216443,1171.537618,1162.023853,1152.670896,1143.474642,1134.431125,1125.536513,1116.787101,1108.179309,1099.709672,1091.37484,1083.171571,1075.096725,1067.147265,1059.320247,1051.612821,1044.022223,1036.545778,1029.180889,1021.92504,1014.775789], - -['gas-mcf', 940970.153,836556.732,748476.939,673307.64,608520.158,552204.935,502892.3,459432.263,420911.806,386596.658,355889.491,328299.357,303418.988,280907.679,260478.232,241886.857,224925.287,209414.552,195200.029,182147.455,170139.707,159074.174,148860.596,139419.282,130679.626,122578.864,115061.035,108076.1,101579.19,95529.972,89892.104,84632.762,79722.24,75133.597,70842.349,66826.213,63064.865,59539.742,56233.86,53131.662,50218.869,47482.365,44910.082,42490.907,40214.588,38071.662,36053.382,34151.656,32358.989,30668.434,29073.545,27568.334,26147.237,24805.079,23537.041,22338.633,21205.67,20134.246,19120.717,18161.677,17253.941,16394.533,15580.663,14809.724,14079.269,13387.006,12730.786,12108.59,11518.524,10958.809,10427.774,9923.847,9445.55,8991.492,8560.364,8150.934,7762.041,7392.59,7041.551,6707.951,6390.874,6089.452,5802.871,5530.358,5271.185,5024.664,4790.145,4567.013,4354.687,4152.618,3960.286,3777.198,3602.889,3436.919,3278.87,3128.347,2984.976,2848.401,2718.287,2594.316,2476.184,2363.606,2256.309,2154.037,2056.545,1963.599,1874.982,1790.482,1709.903,1633.054,1559.758,1489.844,1423.151,1359.525,1298.821,1240.899,1185.628,1132.883,1082.544,1034.499], - -['water-bbl',940.97,836.557,748.477,673.308,608.52,552.205,502.892,459.432,420.912,386.597,355.889,328.299,303.419,280.908,260.478,241.887,224.925,209.415,195.2,182.147,170.14,159.074,148.861,139.419,130.68,122.579,115.061,108.076,101.579,95.53,89.892,84.633,79.722,75.134,70.842,66.826,63.065,59.54,56.234,53.132,50.219,47.482,44.91,42.491,40.215,38.072,36.053,34.152,32.359,30.668,29.074,27.568,26.147,24.805,23.537,22.339,21.206,20.134,19.121,18.162,17.254,16.395,15.581,14.81,14.079,13.387,12.731,12.109,11.519,10.959,10.428,9.924,9.446,8.991,8.56,8.151,7.762,7.393,7.042,6.708,6.391,6.089,5.803,5.53,5.271,5.025,4.79,4.567,4.355,4.153,3.96,3.777,3.603,3.437,3.279,3.128,2.985,2.848,2.718,2.594,2.476,2.364,2.256,2.154,2.057,1.964,1.875,1.79,1.71,1.633,1.56,1.49,1.423,1.36,1.299,1.241,1.186,1.133,1.083,1.034] ] - -p = Axlsx::Package.new -wb = p.workbook -wb.add_worksheet(:name => 'volumes') do |ws| - - # perspective, rotX and rotY flatten the 3D chart - # hPercent sets the chart height to 100% - chart = ws.add_chart(Axlsx::Line3DChart, :perspective => 0, :hPercent => 100, :rotX => 0, :rotY => 0, :start_at => "A4", :end_at => 'M44', :title => 'Monthly forcast for Type Curve Gas secondary - gross ngl') - - # Set up log based scaling and appropriate min/max values for Y axis - chart.valAxis.scaling.logBase = 10 - chart.valAxis.scaling.min = 0.1 - chart.valAxis.scaling.max = 10000000.0 - - # No poing in showing the series axis labels as the chart is flattened! - chart.serAxis.delete = true - - # Knock the catAxis labels down to the forced min. - chart.catAxis.crosses = :min - - # set up cat labels / markes to show every 12th item - chart.catAxis.tickLblSkip = 12 - chart.catAxis.tickMarkSkip = 12 - - #rgb colors for our data series - colors = ['00FF00', 'FF0000', '0000FF'] - - data.each_with_index do |value, index| - ws.add_row value - chart.add_series :data => ws.rows.last.cells[(1..value.size-1)], :labels => (0..value.size).map{ |i| i }, :title => ws.rows.last.cells.first, :color => colors[index] - end -end - -p.serialize 'extractive.xlsx' diff --git a/examples/filtered_table_example.md b/examples/filtered_table_example.md new file mode 100644 index 00000000..1cab7648 --- /dev/null +++ b/examples/filtered_table_example.md @@ -0,0 +1,28 @@ +## Description + +You could add areas as filtered table + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Table') do |sheet| + sheet.add_row ['Build Matrix'] + sheet.add_row ['Build', 'Duration', 'Finished', 'Rvm'] + sheet.add_row ['19.1', '1 min 32 sec', 'about 10 hours ago', '1.8.7'] + sheet.add_row ['19.2', '1 min 28 sec', 'about 10 hours ago', '1.9.2'] + sheet.add_row ['19.3', '1 min 35 sec', 'about 10 hours ago', '1.9.3'] + + sheet.add_table 'A2:D5', name: 'Build Matrix', style_info: { name: 'TableStyleMedium23' } +end + +p.serialize 'filtered_table_example.xlsx' +``` + +## Output + + diff --git a/examples/fit_to_page_example.md b/examples/fit_to_page_example.md new file mode 100644 index 00000000..de02371c --- /dev/null +++ b/examples/fit_to_page_example.md @@ -0,0 +1,25 @@ +## Description + +This is a very basic example with a worksheet and two rows of data. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Fit to page') do |sheet| + sheet.add_row 'this all goes on one page'.split('') + sheet.page_setup.fit_to width: 1, height: 1 +end + +p.serialize 'fit_to_page_example.xlsx' +``` + +## Output + + + + diff --git a/examples/font_example.md b/examples/font_example.md new file mode 100644 index 00000000..de986ed8 --- /dev/null +++ b/examples/font_example.md @@ -0,0 +1,26 @@ +## Description + +You could set the font + + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +courier = s.add_style font_name: 'Courier' + +wb.add_worksheet(name: 'Custom Styles') do |sheet| + sheet.add_row ['First', 'Second', 'Third'], style: [nil, courier, nil] +end + +p.serialize 'font_example.xlsx' +``` + +## Output + + diff --git a/examples/format_codes_example.md b/examples/format_codes_example.md new file mode 100644 index 00000000..50ec11e2 --- /dev/null +++ b/examples/format_codes_example.md @@ -0,0 +1,29 @@ +## Description + +You could define custom format codes. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +date = s.add_style format_code: 'yyyy-mm-dd' +percent = s.add_style format_code: '0%' +padded = s.add_style format_code: '00#' + +wb.add_worksheet(name: 'Formatting Data') do |sheet| + # require 'date' + sheet.add_row ['Custom Formatted Date', 'Percent Formatted Float', 'Padded Numbers'] + sheet.add_row [Date.today, 0.2, 32], style: [date, percent, padded] +end + +p.serialize 'format_codes_example.xlsx' +``` + +## Output + + diff --git a/examples/generate.rb b/examples/generate.rb new file mode 100755 index 00000000..e8891adf --- /dev/null +++ b/examples/generate.rb @@ -0,0 +1,15 @@ +#!/usr/bin/env ruby + +files = if !ARGV.empty? + ARGV.select { |file| File.exist?(file) } + else + Dir['*_example.md'] + end + +files.each do |file| + puts "Executing #{file.split('.')[0].tr('_', ' ')}" + code = File.read(file).match(/```ruby(?<code>.+)```/m)[:code] + unless code.nil? + eval(['$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib"', code].join("\n")) + end +end diff --git a/examples/header_footer_example.md b/examples/header_footer_example.md new file mode 100644 index 00000000..d8f7a50f --- /dev/null +++ b/examples/header_footer_example.md @@ -0,0 +1,28 @@ +## Description + +You could add header and footer to the sheet, even with the templating of excel. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +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 + +p.serialize 'header_footer_example.xlsx' +``` + +## Output + + diff --git a/examples/hide_gridlines_in_chart_example.md b/examples/hide_gridlines_in_chart_example.md new file mode 100644 index 00000000..e1365bcf --- /dev/null +++ b/examples/hide_gridlines_in_chart_example.md @@ -0,0 +1,33 @@ +## Description + +Bar chart example + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Bar Chart') do |sheet| + sheet.add_row ['A Simple Bar Chart'] + + sheet.add_row ['A', 3] + sheet.add_row ['B', 10] + sheet.add_row ['C', 7] + + sheet.add_chart(Axlsx::Bar3DChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.add_series data: sheet['B2:B4'], labels: sheet['A2:A4'], title: sheet['A1'] + + chart.valAxis.gridlines = false + chart.catAxis.gridlines = false + end +end + +p.serialize 'hide_gridlines_in_chart_example.xlsx' +``` + +## Output + + diff --git a/examples/hide_gridlines_in_sheet_example.md b/examples/hide_gridlines_in_sheet_example.md new file mode 100644 index 00000000..814ac2cc --- /dev/null +++ b/examples/hide_gridlines_in_sheet_example.md @@ -0,0 +1,23 @@ +## Description + +This is a very basic example to hide gridlines. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'No Gridlines') do |sheet| + sheet.add_row ['This', 'Sheet', 'Hides', 'Gridlines'] + sheet.sheet_view.show_grid_lines = false +end + +p.serialize 'hide_gridlines_in_sheet_example.xlsx' +``` + +## Output + + diff --git a/examples/hide_sheet_example.md b/examples/hide_sheet_example.md new file mode 100644 index 00000000..8b73c34e --- /dev/null +++ b/examples/hide_sheet_example.md @@ -0,0 +1,30 @@ +## Description + +This is a very basic example with a worksheet and two rows of data. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'normal') do |sheet| + sheet.add_row ['I am here'] +end + +wb.add_worksheet(name: 'hidden', state: :hidden) do |sheet| + sheet.add_row ['you cant see me!'] +end + +wb.add_worksheet(name: 'very hidden', state: :very_hidden) do |sheet| + sheet.add_row ['you really cant see me!'] +end + +p.serialize 'hide_sheet_example.xlsx' +``` + +## Output + + diff --git a/examples/hyperlink_example.md b/examples/hyperlink_example.md new file mode 100644 index 00000000..f6d21ca9 --- /dev/null +++ b/examples/hyperlink_example.md @@ -0,0 +1,31 @@ +## Description + +You could create an internal or external hyperlink. The ref could be a string referencing to an existing cell or a cell object. + +## Code + +```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/caxlsx/caxlsx', ref: 'A1' + # internal references + sheet.add_hyperlink location: "'Next Sheet'!A1", ref: 'A2', target: :sheet + sheet.add_row ['next sheet'] +end + +wb.add_worksheet(name: 'Next Sheet') do |sheet| + sheet.add_row ['hello!'] +end + +p.serialize 'hyperlink_example.xlsx' +``` + +## Output + + diff --git a/examples/hyperlinked_image_example.md b/examples/hyperlinked_image_example.md new file mode 100644 index 00000000..e79237da --- /dev/null +++ b/examples/hyperlinked_image_example.md @@ -0,0 +1,26 @@ +## Description + +You could add hyerlinks to images. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +image = File.expand_path('assets/image1.jpeg') + +wb.add_worksheet(name: 'Image') do |sheet| + sheet.add_image(image_src: image, start_at: 'B2', width: 300, height: 300, hyperlink: 'https://github.com/caxlsx') do |img| + img.hyperlink.tooltip = 'Labeled Link' + end +end + +p.serialize 'hyperlinked_image_example.xlsx' +``` + +## Output + + diff --git a/examples/image_example.md b/examples/image_example.md new file mode 100644 index 00000000..e434c1b3 --- /dev/null +++ b/examples/image_example.md @@ -0,0 +1,25 @@ +## Description + +You could add images. All the options could be added in a do block, so instead of `sheet.add_image(width: 720)` you could say `sheet.add_image { |image| image.width = 720 }`. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +image = File.expand_path('assets/image1.jpeg') + +wb.add_worksheet(name: 'Image') do |sheet| + sheet.add_image(image_src: image, start_at: 'B2', width: 100, height: 100) + sheet.add_image(image_src: image, start_at: 'E1', end_at: 'G3') +end + +p.serialize 'image_example.xlsx' +``` + +## Output + + diff --git a/examples/images/3d_line_chart_example.png b/examples/images/3d_line_chart_example.png Binary files differnew file mode 100644 index 00000000..be7935c8 --- /dev/null +++ b/examples/images/3d_line_chart_example.png diff --git a/examples/images/3d_pie_chart_example.png b/examples/images/3d_pie_chart_example.png Binary files differnew file mode 100644 index 00000000..9e5fd508 --- /dev/null +++ b/examples/images/3d_pie_chart_example.png diff --git a/examples/images/active_tab_example.png b/examples/images/active_tab_example.png Binary files differnew file mode 100644 index 00000000..50bcffef --- /dev/null +++ b/examples/images/active_tab_example.png diff --git a/examples/images/asian_language_support_example.png b/examples/images/asian_language_support_example.png Binary files differnew file mode 100644 index 00000000..6cc1dac2 --- /dev/null +++ b/examples/images/asian_language_support_example.png diff --git a/examples/images/auto_filter_example_1.png b/examples/images/auto_filter_example_1.png Binary files differnew file mode 100644 index 00000000..1708e4eb --- /dev/null +++ b/examples/images/auto_filter_example_1.png diff --git a/examples/images/auto_filter_example_2.png b/examples/images/auto_filter_example_2.png Binary files differnew file mode 100644 index 00000000..1765b0e3 --- /dev/null +++ b/examples/images/auto_filter_example_2.png diff --git a/examples/images/automatic_data_types_example.png b/examples/images/automatic_data_types_example.png Binary files differnew file mode 100644 index 00000000..67f2b7b2 --- /dev/null +++ b/examples/images/automatic_data_types_example.png diff --git a/examples/images/bar_chart_example.png b/examples/images/bar_chart_example.png Binary files differnew file mode 100644 index 00000000..f495cf47 --- /dev/null +++ b/examples/images/bar_chart_example.png diff --git a/examples/images/basic_example.png b/examples/images/basic_example.png Binary files differnew file mode 100644 index 00000000..2929c9b9 --- /dev/null +++ b/examples/images/basic_example.png diff --git a/examples/images/basic_formula_example.png b/examples/images/basic_formula_example.png Binary files differnew file mode 100644 index 00000000..bded77c2 --- /dev/null +++ b/examples/images/basic_formula_example.png diff --git a/examples/images/borders_example.png b/examples/images/borders_example.png Binary files differnew file mode 100644 index 00000000..e6b7fe8f --- /dev/null +++ b/examples/images/borders_example.png diff --git a/examples/images/cached_formula_example.png b/examples/images/cached_formula_example.png Binary files differnew file mode 100644 index 00000000..e89e69ea --- /dev/null +++ b/examples/images/cached_formula_example.png diff --git a/examples/images/chart_colors_example.png b/examples/images/chart_colors_example.png Binary files differnew file mode 100644 index 00000000..e6bcb877 --- /dev/null +++ b/examples/images/chart_colors_example.png diff --git a/examples/images/column_outlines_example_1.png b/examples/images/column_outlines_example_1.png Binary files differnew file mode 100644 index 00000000..270dc972 --- /dev/null +++ b/examples/images/column_outlines_example_1.png diff --git a/examples/images/column_outlines_example_2.png b/examples/images/column_outlines_example_2.png Binary files differnew file mode 100644 index 00000000..73e529f0 --- /dev/null +++ b/examples/images/column_outlines_example_2.png diff --git a/examples/images/column_styles_example.png b/examples/images/column_styles_example.png Binary files differnew file mode 100644 index 00000000..dcda744c --- /dev/null +++ b/examples/images/column_styles_example.png diff --git a/examples/images/column_widths_example.png b/examples/images/column_widths_example.png Binary files differnew file mode 100644 index 00000000..3680e204 --- /dev/null +++ b/examples/images/column_widths_example.png diff --git a/examples/images/comments_example_1.png b/examples/images/comments_example_1.png Binary files differnew file mode 100644 index 00000000..8d8cb795 --- /dev/null +++ b/examples/images/comments_example_1.png diff --git a/examples/images/comments_example_2.png b/examples/images/comments_example_2.png Binary files differnew file mode 100644 index 00000000..4abc1f30 --- /dev/null +++ b/examples/images/comments_example_2.png diff --git a/examples/images/complex_example.png b/examples/images/complex_example.png Binary files differnew file mode 100644 index 00000000..d85cde00 --- /dev/null +++ b/examples/images/complex_example.png diff --git a/examples/images/conditional_formatting_between_example.png b/examples/images/conditional_formatting_between_example.png Binary files differnew file mode 100644 index 00000000..3e3740f2 --- /dev/null +++ b/examples/images/conditional_formatting_between_example.png diff --git a/examples/images/conditional_formatting_color_scale_example.png b/examples/images/conditional_formatting_color_scale_example.png Binary files differnew file mode 100644 index 00000000..a158b839 --- /dev/null +++ b/examples/images/conditional_formatting_color_scale_example.png diff --git a/examples/images/conditional_formatting_data_bar_example.png b/examples/images/conditional_formatting_data_bar_example.png Binary files differnew file mode 100644 index 00000000..9270ecb6 --- /dev/null +++ b/examples/images/conditional_formatting_data_bar_example.png diff --git a/examples/images/conditional_formatting_greater_than_example.png b/examples/images/conditional_formatting_greater_than_example.png Binary files differnew file mode 100644 index 00000000..619e0ce7 --- /dev/null +++ b/examples/images/conditional_formatting_greater_than_example.png diff --git a/examples/images/conditional_formatting_icon_set_example.png b/examples/images/conditional_formatting_icon_set_example.png Binary files differnew file mode 100644 index 00000000..1a3f2b62 --- /dev/null +++ b/examples/images/conditional_formatting_icon_set_example.png diff --git a/examples/images/custom_styles_example.png b/examples/images/custom_styles_example.png Binary files differnew file mode 100644 index 00000000..9b4116c1 --- /dev/null +++ b/examples/images/custom_styles_example.png diff --git a/examples/images/defined_name_example.png b/examples/images/defined_name_example.png Binary files differnew file mode 100644 index 00000000..b851ec2d --- /dev/null +++ b/examples/images/defined_name_example.png diff --git a/examples/images/escape_formula_example.png b/examples/images/escape_formula_example.png Binary files differnew file mode 100644 index 00000000..93c530a5 --- /dev/null +++ b/examples/images/escape_formula_example.png diff --git a/examples/images/filtered_table_example.png b/examples/images/filtered_table_example.png Binary files differnew file mode 100644 index 00000000..770cf1f4 --- /dev/null +++ b/examples/images/filtered_table_example.png diff --git a/examples/images/fit_to_page_example_1.png b/examples/images/fit_to_page_example_1.png Binary files differnew file mode 100644 index 00000000..a63abc6e --- /dev/null +++ b/examples/images/fit_to_page_example_1.png diff --git a/examples/images/fit_to_page_example_2.png b/examples/images/fit_to_page_example_2.png Binary files differnew file mode 100644 index 00000000..d45b3f57 --- /dev/null +++ b/examples/images/fit_to_page_example_2.png diff --git a/examples/images/font_example.png b/examples/images/font_example.png Binary files differnew file mode 100644 index 00000000..34ffe3c1 --- /dev/null +++ b/examples/images/font_example.png diff --git a/examples/images/format_codes_example.png b/examples/images/format_codes_example.png Binary files differnew file mode 100644 index 00000000..abfea1ce --- /dev/null +++ b/examples/images/format_codes_example.png diff --git a/examples/images/header_footer_example.png b/examples/images/header_footer_example.png Binary files differnew file mode 100644 index 00000000..e5640e2b --- /dev/null +++ b/examples/images/header_footer_example.png diff --git a/examples/images/hide_gridlines_in_chart_example.png b/examples/images/hide_gridlines_in_chart_example.png Binary files differnew file mode 100644 index 00000000..43831e98 --- /dev/null +++ b/examples/images/hide_gridlines_in_chart_example.png diff --git a/examples/images/hide_gridlines_in_sheet_example.png b/examples/images/hide_gridlines_in_sheet_example.png Binary files differnew file mode 100644 index 00000000..a735783b --- /dev/null +++ b/examples/images/hide_gridlines_in_sheet_example.png diff --git a/examples/images/hide_sheet_example.png b/examples/images/hide_sheet_example.png Binary files differnew file mode 100644 index 00000000..fa0eab28 --- /dev/null +++ b/examples/images/hide_sheet_example.png diff --git a/examples/images/hyperlink_example.png b/examples/images/hyperlink_example.png Binary files differnew file mode 100644 index 00000000..8d752e3c --- /dev/null +++ b/examples/images/hyperlink_example.png diff --git a/examples/images/hyperlinked_image_example.png b/examples/images/hyperlinked_image_example.png Binary files differnew file mode 100644 index 00000000..67e418e0 --- /dev/null +++ b/examples/images/hyperlinked_image_example.png diff --git a/examples/images/image_example.png b/examples/images/image_example.png Binary files differnew file mode 100644 index 00000000..6d29b7ce --- /dev/null +++ b/examples/images/image_example.png diff --git a/examples/images/line_chart_example.png b/examples/images/line_chart_example.png Binary files differnew file mode 100644 index 00000000..9c87d789 --- /dev/null +++ b/examples/images/line_chart_example.png diff --git a/examples/images/list_validation_example_1.png b/examples/images/list_validation_example_1.png Binary files differnew file mode 100644 index 00000000..3f0b9077 --- /dev/null +++ b/examples/images/list_validation_example_1.png diff --git a/examples/images/list_validation_example_2.png b/examples/images/list_validation_example_2.png Binary files differnew file mode 100644 index 00000000..8feaaba6 --- /dev/null +++ b/examples/images/list_validation_example_2.png diff --git a/examples/images/locked_image_example.png b/examples/images/locked_image_example.png Binary files differnew file mode 100644 index 00000000..94d04741 --- /dev/null +++ b/examples/images/locked_image_example.png diff --git a/examples/images/merge_cells_example.png b/examples/images/merge_cells_example.png Binary files differnew file mode 100644 index 00000000..2bf81468 --- /dev/null +++ b/examples/images/merge_cells_example.png diff --git a/examples/images/no_autowidth_example.png b/examples/images/no_autowidth_example.png Binary files differnew file mode 100644 index 00000000..0f66629b --- /dev/null +++ b/examples/images/no_autowidth_example.png diff --git a/examples/images/number_format_example.png b/examples/images/number_format_example.png Binary files differnew file mode 100644 index 00000000..97cc7e8f --- /dev/null +++ b/examples/images/number_format_example.png diff --git a/examples/images/override_data_types_example.png b/examples/images/override_data_types_example.png Binary files differnew file mode 100644 index 00000000..4acc5a40 --- /dev/null +++ b/examples/images/override_data_types_example.png diff --git a/examples/images/page_break_example.png b/examples/images/page_break_example.png Binary files differnew file mode 100644 index 00000000..e7cf2f66 --- /dev/null +++ b/examples/images/page_break_example.png diff --git a/examples/images/pivot_table_example.png b/examples/images/pivot_table_example.png Binary files differnew file mode 100644 index 00000000..70a625f8 --- /dev/null +++ b/examples/images/pivot_table_example.png diff --git a/examples/images/print_settings_example.png b/examples/images/print_settings_example.png Binary files differnew file mode 100644 index 00000000..fb62a89f --- /dev/null +++ b/examples/images/print_settings_example.png diff --git a/examples/images/range_validation_example.png b/examples/images/range_validation_example.png Binary files differnew file mode 100644 index 00000000..5e0e4918 --- /dev/null +++ b/examples/images/range_validation_example.png diff --git a/examples/images/repeated_header_example.png b/examples/images/repeated_header_example.png Binary files differnew file mode 100644 index 00000000..841a5c06 --- /dev/null +++ b/examples/images/repeated_header_example.png diff --git a/examples/images/rich_text_example.png b/examples/images/rich_text_example.png Binary files differnew file mode 100644 index 00000000..24f99a90 --- /dev/null +++ b/examples/images/rich_text_example.png diff --git a/examples/images/row_heights_example.png b/examples/images/row_heights_example.png Binary files differnew file mode 100644 index 00000000..13d58a62 --- /dev/null +++ b/examples/images/row_heights_example.png diff --git a/examples/images/row_outlines_example_1.png b/examples/images/row_outlines_example_1.png Binary files differnew file mode 100644 index 00000000..7b595831 --- /dev/null +++ b/examples/images/row_outlines_example_1.png diff --git a/examples/images/row_outlines_example_2.png b/examples/images/row_outlines_example_2.png Binary files differnew file mode 100644 index 00000000..98ee9c0a --- /dev/null +++ b/examples/images/row_outlines_example_2.png diff --git a/examples/images/row_outlines_example_3.png b/examples/images/row_outlines_example_3.png Binary files differnew file mode 100644 index 00000000..fe66e266 --- /dev/null +++ b/examples/images/row_outlines_example_3.png diff --git a/examples/images/row_styles_example.png b/examples/images/row_styles_example.png Binary files differnew file mode 100644 index 00000000..42f88822 --- /dev/null +++ b/examples/images/row_styles_example.png diff --git a/examples/images/scatter_chart_example.png b/examples/images/scatter_chart_example.png Binary files differnew file mode 100644 index 00000000..28a6dc82 --- /dev/null +++ b/examples/images/scatter_chart_example.png diff --git a/examples/images/sheet_protection_example.png b/examples/images/sheet_protection_example.png Binary files differnew file mode 100644 index 00000000..a0cdbcd6 --- /dev/null +++ b/examples/images/sheet_protection_example.png diff --git a/examples/images/style_overrides_example.png b/examples/images/style_overrides_example.png Binary files differnew file mode 100644 index 00000000..53412969 --- /dev/null +++ b/examples/images/style_overrides_example.png diff --git a/examples/images/surrounding_borders_example.png b/examples/images/surrounding_borders_example.png Binary files differnew file mode 100644 index 00000000..47a21a91 --- /dev/null +++ b/examples/images/surrounding_borders_example.png diff --git a/examples/images/tab_color_example.png b/examples/images/tab_color_example.png Binary files differnew file mode 100644 index 00000000..f37d0d61 --- /dev/null +++ b/examples/images/tab_color_example.png diff --git a/examples/images/text_length_validation_example.png b/examples/images/text_length_validation_example.png Binary files differnew file mode 100644 index 00000000..f99533f5 --- /dev/null +++ b/examples/images/text_length_validation_example.png diff --git a/examples/images/wrap_text_example.png b/examples/images/wrap_text_example.png Binary files differnew file mode 100644 index 00000000..0581055e --- /dev/null +++ b/examples/images/wrap_text_example.png diff --git a/examples/ios_preview.rb b/examples/ios_preview.rb deleted file mode 100644 index 60b3010f..00000000 --- a/examples/ios_preview.rb +++ /dev/null @@ -1,14 +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 -p.use_shared_strings = true -s = p.workbook.add_worksheet(:name => "Formula test") -s.add_row [1, 2, 3] -s.add_row %w(a b c) -s.add_row ["=SUM(A1:C1)"], :formula_values => [6] -p.serialize "ios_preview.xlsx" diff --git a/examples/line_chart_example.md b/examples/line_chart_example.md new file mode 100644 index 00000000..ca9fd23c --- /dev/null +++ b/examples/line_chart_example.md @@ -0,0 +1,31 @@ +## Description + +Line chart example + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Line Chart') do |sheet| + sheet.add_row ['X-Y', 'X', 'Y'] + + sheet.add_row ['A', 3, 7] + sheet.add_row ['B', 10, 2] + sheet.add_row ['C', 7, 6] + + sheet.add_chart(Axlsx::LineChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.add_series data: sheet['B2:B4'], title: sheet['B1'], color: '0000FF' + chart.add_series data: sheet['C2:C4'], title: sheet['C1'], color: 'FF0000' + end +end + +p.serialize 'line_chart_example.xlsx' +``` + +## Output + + diff --git a/examples/list_validation_example.md b/examples/list_validation_example.md new file mode 100644 index 00000000..1f2cfc0c --- /dev/null +++ b/examples/list_validation_example.md @@ -0,0 +1,52 @@ +## Description + +You could validate data based on an explicit list or a given range of cells. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['First', 'Second', 'Third'] + sheet.add_row [1, 6, 11] + + sheet.add_data_validation('A2:A2', + type: :list, + formula1: 'A1:C1', + showDropDown: false, + showErrorMessage: true, + errorTitle: '', + error: 'Only values from A1:C1', + errorStyle: :stop, + showInputMessage: true, + promptTitle: '', + prompt: 'Only values from A1:C1') + + sheet.add_data_validation('B2:B2', + type: :list, + formula1: '"Red, Orange, NavyBlue"', + showDropDown: false, + showErrorMessage: true, + errorTitle: '', + error: 'Please use the dropdown selector to choose the value', + errorStyle: :stop, + showInputMessage: true, + prompt: 'Choose the value from the dropdown') +end + +p.serialize 'list_validation_example.xlsx' +``` + +## Output + +Validating by cells: + + + +Validating by list: + + diff --git a/examples/locked_image_example.md b/examples/locked_image_example.md new file mode 100644 index 00000000..b0fed85b --- /dev/null +++ b/examples/locked_image_example.md @@ -0,0 +1,35 @@ +## Description + +Images could have a lot of restrictions. + +* noGrp +* noSelect - disable selection +* noRot - disable rotation +* noChangeAspect (default true) - disable aspect change +* noMove - disable move +* noResize - disable resize +* noEditPoints +* noAdjustHandles +* noChangeArrowheads +* noChangeShapeType + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +image = File.expand_path('assets/image1.jpeg') + +wb.add_worksheet(name: 'Image') do |sheet| + sheet.add_image(image_src: image, start_at: 'B2', width: 300, height: 300, noRot: true) +end + +p.serialize 'locked_image_example.xlsx' +``` + +## Output + + diff --git a/examples/merge_cells.rb b/examples/merge_cells.rb deleted file mode 100644 index 8b2ead2c..00000000 --- a/examples/merge_cells.rb +++ /dev/null @@ -1,17 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" - -#```ruby -require 'axlsx' -package = Axlsx::Package.new -package.workbook do |workbook| - workbook.add_worksheet name: 'merged_cells' do |sheet| - 4.times do - sheet.add_row %w(a b c d e f g) - end - sheet.merge_cells "A1:A2" - sheet.merge_cells "B1:B2" - end -end -package.serialize 'merged_cells.xlsx' diff --git a/examples/merge_cells_example.md b/examples/merge_cells_example.md new file mode 100644 index 00000000..f8b3aa46 --- /dev/null +++ b/examples/merge_cells_example.md @@ -0,0 +1,28 @@ +## Description + +You could merge cells + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Merge cells') do |sheet| + sheet.add_row ['Vertical', '', ''] + sheet.add_row ['Horizontal'] + sheet.add_row [''] + sheet.add_row [''] + + sheet.merge_cells('A1:C1') + sheet.merge_cells('A2:A4') +end + +p.serialize 'merge_cells_example.xlsx' +``` + +## Output + + diff --git a/examples/no_autowidth_example.md b/examples/no_autowidth_example.md new file mode 100644 index 00000000..bd01203d --- /dev/null +++ b/examples/no_autowidth_example.md @@ -0,0 +1,24 @@ +## Description + +This is a very basic example with a worksheet and two rows of data. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +p.use_autowidth = false + +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['oh look! no autowidth'] +end + +p.serialize 'no_autowidth_example.xlsx' +``` + +## Output + + diff --git a/examples/no_grid_with_borders.rb b/examples/no_grid_with_borders.rb deleted file mode 100644 index aed9eb76..00000000 --- a/examples/no_grid_with_borders.rb +++ /dev/null @@ -1,18 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" - -#```ruby -require 'axlsx' -package = Axlsx::Package.new -package.workbook do |workbook| - workbook.styles do |s| - gridstyle_border = s.add_style :border => { :style => :thin, :color =>"FFCDCDCD" } - workbook.add_worksheet :name => "Custom Borders" do |sheet| - sheet.sheet_view.show_grid_lines = false - sheet.add_row ["with", "grid", "style"], :style => gridstyle_border - sheet.add_row ["no", "border"] - end - end -end -package.serialize 'no_grid_with_borders.xlsx' diff --git a/examples/number_format_example.md b/examples/number_format_example.md new file mode 100644 index 00000000..fe053880 --- /dev/null +++ b/examples/number_format_example.md @@ -0,0 +1,30 @@ +## Description + + + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +comma = s.add_style num_fmt: 3 +currency = s.add_style num_fmt: 5 +red_negative = s.add_style num_fmt: 8 +super_funk = s.add_style format_code: '[Green]#' +venezuela_currency = s.add_style format_code: '#.##0\,00' + +wb.add_worksheet(name: 'Formats and Currency') do |sheet| + sheet.add_row ['Comma', 'Currency', 'Red Negative', 'Super funk', 'Venezuela'] + sheet.add_row [123456789, 1500, -122.34, 594829, 2.5], style: [comma, currency, red_negative, super_funk, venezuela_currency] +end + +p.serialize 'number_format_example.xlsx' +``` + +## Output + + diff --git a/examples/override_data_types_example.md b/examples/override_data_types_example.md new file mode 100644 index 00000000..c5ce777d --- /dev/null +++ b/examples/override_data_types_example.md @@ -0,0 +1,22 @@ +## Description + +You could override the automatic data types + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Override Data Type') do |sheet| + sheet.add_row ['do not eat my zeros!', '0088'], types: [nil, :string] +end + +p.serialize 'override_data_types_example.xlsx' +``` + +## Output + + diff --git a/examples/page_break_example.md b/examples/page_break_example.md new file mode 100644 index 00000000..b5e5ede3 --- /dev/null +++ b/examples/page_break_example.md @@ -0,0 +1,25 @@ +## Description + +This is a very basic example with a worksheet and two rows of data. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['This is the first line'] + sheet.add_row ['This is the second line'] + sheet.add_page_break('B2') + sheet.sheet_view.view = :page_layout # so you can see the breaks! +end + +p.serialize 'page_break_example.xlsx' +``` + +## Output + + diff --git a/examples/page_setup.rb b/examples/page_setup.rb deleted file mode 100644 index 67a3eeb0..00000000 --- a/examples/page_setup.rb +++ /dev/null @@ -1,11 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -xls = Axlsx::Package.new -wb = xls.workbook -wb.add_worksheet do |ws| - # Excel does not currently follow the specification and will ignore paper_width and paper_height so if you need - # to for a paper size, be sure to set :paper_size - ws.page_setup.set :paper_width => "210mm", :paper_size => 10, :paper_height => "297mm", :orientation => :landscape - ws.add_row %w(AXLSX is cool) -end -xls.serialize "page_setup.xlsx" diff --git a/examples/panes_example.md b/examples/panes_example.md new file mode 100644 index 00000000..49d4b81f --- /dev/null +++ b/examples/panes_example.md @@ -0,0 +1,32 @@ +## Description + +You can split the sheet into panes. `pane.state` could have three options: `split`, `frozen` or `frozen_split` + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Panes') do |sheet| + # Generate a big dataset + sheet.add_row [''] + (0..99).map { |i| "column header #{i}" } + 100.times { |index| sheet.add_row ['row header'] + (0..index).to_a } + + sheet.sheet_view.pane do |pane| + pane.top_left_cell = 'B2' + pane.state = :frozen_split + pane.y_split = 1 + pane.x_split = 1 + pane.active_pane = :bottom_right + end +end + +p.serialize 'panes_example.xlsx' +``` + +## Output + + diff --git a/examples/pivot_table.rb b/examples/pivot_table.rb deleted file mode 100644 index 07d03d55..00000000 --- a/examples/pivot_table.rb +++ /dev/null @@ -1,39 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- - -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' - -p = Axlsx::Package.new -wb = p.workbook - -# Create some data in a sheet -def month - %w(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec).sample -end -def year - %w(2010 2011 2012).sample -end -def type - %w(Meat Dairy Beverages Produce).sample -end -def sales - rand(5000) -end -def region - %w(East West North South).sample -end - -wb.add_worksheet(:name => "Data Sheet") do |sheet| - sheet.add_row ['Month', 'Year', 'Type', 'Sales', 'Region'] - 30.times { sheet.add_row [month, year, type, sales, region] } - sheet.add_pivot_table 'G4:L17', "A1:E31" do |pivot_table| - pivot_table.rows = ['Month', 'Year'] - pivot_table.columns = ['Type'] - pivot_table.data = ['Sales'] - pivot_table.pages = ['Region'] - end -end - -# Write the excel file -p.serialize("pivot_table.xlsx") diff --git a/examples/pivot_table_example.md b/examples/pivot_table_example.md new file mode 100644 index 00000000..21176a73 --- /dev/null +++ b/examples/pivot_table_example.md @@ -0,0 +1,40 @@ +## Description + +Pivot tables are cool. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['Month', 'Year', 'Type', 'Sales', 'Region'] + + # Generate some data + 30.times do + sheet.add_row [ + ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'].sample, + [2010, 2011, 2012].sample, + ['Meat', 'Dairy', 'Beverages', 'Produce'].sample, + rand(5000), + ['East', 'West', 'North', 'South'].sample + ] + end + + sheet.add_pivot_table 'G4:L17', 'A1:E31' do |pivot_table| + pivot_table.rows = ['Month', 'Year'] + pivot_table.columns = ['Type'] + pivot_table.data = ['Sales'] + pivot_table.pages = ['Region'] + end +end + +p.serialize 'pivot_table_example.xlsx' +``` + +## Output + + diff --git a/examples/pivot_test.rb b/examples/pivot_test.rb deleted file mode 100644 index 80bdcd84..00000000 --- a/examples/pivot_test.rb +++ /dev/null @@ -1,63 +0,0 @@ -class RandomReportGenerator - def date - Date.today.strftime("%m/%d/%Y") - end - def member_id - @i ||= 0 - @i += 1 - end - def name - "John S." - end - def gender - ["Male", "Female"].sample - end - def age - rand(100) - end - def city - ["New York", "Mountain View", "Newark", "Phoenix"].sample - end - def state - ["NY", "CA", "NJ", "AZ"].sample - end - def parenting - "Foo" - end - def student - "Bar" - end - def income - "Bar" - end - def education - "Bar" - end - def answer - ["Yes", "No", "Maybe", "I dont know"].sample - end - def run - package = Axlsx::Package.new - workbook = package.workbook - - workbook.add_worksheet(:name => "Data Sheet") do |sheet| - sheet.add_row [ - "Date", "Member ID", "Name", "Gender", "Age", "City", "State", - "Parenting Status", "Student Status", "Income", "Education", "Answer" - ] - 30.times do - sheet.add_row [date, member_id, name, gender, age, city, state, - parenting, student, income, education, answer] - end - end - - workbook.add_worksheet(:name => "Summary") do |sheet| - pivot_table = Axlsx::PivotTable.new 'A1:B15', "A1:L31", workbook.worksheets[0] - pivot_table.rows = ['Answer'] - pivot_table.data = [{:ref => "Member ID", :subtotal => "count"}] - sheet.pivot_tables << pivot_table - end - - package.serialize("pivot_table.xlsx") - end -end diff --git a/examples/print_settings_example.md b/examples/print_settings_example.md new file mode 100644 index 00000000..ab536082 --- /dev/null +++ b/examples/print_settings_example.md @@ -0,0 +1,47 @@ +## Description + +You could change the page settings for printing. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +margins = { + left: 3, + right: 3, + top: 1.2, + bottom: 1.2, + header: 0.7, + footer: 0.7 +} + +setup = { + fit_to_width: 1, + orientation: :landscape, + paper_width: '297mm', + paper_height: '210mm' +} + +options = { + grid_lines: true, + headings: true, + horizontal_centered: true +} + +wb.add_worksheet(name: 'Print settings', + page_margins: margins, + page_setup: setup, + print_options: options) do |sheet| + sheet.add_row ['this sheet uses customized print settings'] +end + +p.serialize 'print_settings_example.xlsx' +``` + +## Output + + diff --git a/examples/range_validation_example.md b/examples/range_validation_example.md new file mode 100644 index 00000000..4b7aaf6d --- /dev/null +++ b/examples/range_validation_example.md @@ -0,0 +1,36 @@ +## Description + +You could validate data to be between two values. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['First', 'Second', 'Third'] + sheet.add_row [1, 6, 11] + + sheet.add_data_validation('A2:C2', + type: :whole, + operator: :between, + formula1: '5', + formula2: '10', + showErrorMessage: true, + errorTitle: 'Wrong input', + error: 'Only values between 5 and 10', + errorStyle: :information, + showInputMessage: true, + promptTitle: 'Be careful!', + prompt: 'We really want a value between 5 and 10, but it is OK if you want to break the rules.') +end + +p.serialize 'range_validation_example.xlsx' +``` + +## Output + + diff --git a/examples/repeated_header_example.md b/examples/repeated_header_example.md new file mode 100644 index 00000000..f4caf2e2 --- /dev/null +++ b/examples/repeated_header_example.md @@ -0,0 +1,28 @@ +## Description + +Repeat headers on print layout + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Repeated header') do |sheet| + sheet.add_row ['This', 'Column', 'Header', 'Will', 'Render', 'On', 'Every', 'Printed', 'Sheet'] + + 200.times do + sheet.add_row [1, 2, 3, 4, 5, 6, 7, 8] + end + + wb.add_defined_name("'repeated header'!$1:$1", local_sheet_id: sheet.index, name: '_xlnm.Print_Titles') +end + +p.serialize 'repeated_header_example.xlsx' +``` + +## Output + + diff --git a/examples/rich_text_example.md b/examples/rich_text_example.md new file mode 100644 index 00000000..06b22502 --- /dev/null +++ b/examples/rich_text_example.md @@ -0,0 +1,34 @@ +## Description + +This is a very basic example with a worksheet and two rows of data. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +wrap_text = s.add_style(alignment: { horizontal: :center, vertical: :center, wrap_text: true }) + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + rt = Axlsx::RichText.new + rt.add_run('I am bold, ', b: true) + rt.add_run('I am italic, ', i: true) + rt.add_run('I am strike', strike: true) + rt.add_run("\n") + rt.add_run('I am bold, italic and strike', b: true, i: true, strike: true) + rt.add_run("\n") + rt.add_run('I am style-less :D') + + sheet.add_row [rt], style: wrap_text +end + +p.serialize 'rich_text_example.xlsx' +``` + +## Output + + diff --git a/examples/row_heights_example.md b/examples/row_heights_example.md new file mode 100644 index 00000000..5a632cdc --- /dev/null +++ b/examples/row_heights_example.md @@ -0,0 +1,24 @@ +## Description + +You can set the row heights + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Row heights') do |sheet| + sheet.add_row ['This row will have a fixed height', 'It will overwite the default row height'], height: 30 + sheet.add_row ['This row can have a different height too'], height: 10 + sheet.add_row ['This row is the original'] +end + +p.serialize 'row_heights_example.xlsx' +``` + +## Output + + diff --git a/examples/row_outlines_example.md b/examples/row_outlines_example.md new file mode 100644 index 00000000..baa48cfe --- /dev/null +++ b/examples/row_outlines_example.md @@ -0,0 +1,48 @@ +## Description + +If you have a list of data that you want to group and summarize, you can create an outline of up to eight levels. Each inner level, represented by a higher number in the outline symbols, displays detail data for the preceding outer level, represented by a lower number in the outline symbols. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +header = s.add_style bg_color: 'ADD8E6' +summary = s.add_style b: true + +wb.add_worksheet(name: 'Row outlines') do |sheet| + # Header + sheet.add_row ['Folder', 'File', 'Line of code'], style: header + + # First folder + sheet.add_row ['lib', 'package.rb', 351] + sheet.add_row ['lib', 'version.rb', 6] + sheet.outline_level_rows 1, 2, 1, true # From line 1 to line 2, level 1, collapsed + sheet.add_row ['Summary for lib', nil, '=sum(C2:C3)'], style: summary + + # Second folder + sheet.add_row ['test', 'benchmark.rb', 73] + sheet.add_row ['test', 'helper.rb', 13] + sheet.add_row ['test', 'profile.rb', 24] + sheet.outline_level_rows 4, 6, 1, false # From line 4 to line 6, level 1, open + sheet.add_row ['Summary for test', nil, '=sum(C5:C7)'], style: summary +end + +p.serialize 'row_outlines_example.xlsx' +``` + +## Output + + + +After opening the outline: + + + +Closing both outlines: + + diff --git a/examples/row_styles_example.md b/examples/row_styles_example.md new file mode 100644 index 00000000..973b0a13 --- /dev/null +++ b/examples/row_styles_example.md @@ -0,0 +1,32 @@ +## Description + +There is a way to add styles to a whole row. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +percent = s.add_style num_fmt: 9, bg_color: '0000FF' + +wb.add_worksheet(name: 'Custom row styles') do |sheet| + sheet.add_row ['A', 'B', 'C', 'D', 'E'] + sheet.add_row [0.1, 0.2, 0.3, 0.4, 0.5] + sheet.add_row ['H', 'I', 'D', 'E', '!'] + sheet.add_row [1, 2, 0.1, 4, 5.0] + + # Apply the percent style to the row at index 2 skipping the first column. + sheet.row_style 1, percent, col_offset: 1 + sheet.rows[2].hidden = true +end + +p.serialize 'row_styles_example.xlsx' +``` + +## Output + + diff --git a/examples/scatter_chart_example.md b/examples/scatter_chart_example.md new file mode 100644 index 00000000..1e60cef6 --- /dev/null +++ b/examples/scatter_chart_example.md @@ -0,0 +1,30 @@ +## Description + +Scatter chart example + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Bar Chart') do |sheet| + sheet.add_row ['First', 1, 5, 7, 9] + sheet.add_row ['', 1, 25, 49, 81] + sheet.add_row ['Second', 5, 2, 14, 9] + sheet.add_row ['', 5, 10, 15, 20] + + sheet.add_chart(Axlsx::ScatterChart, start_at: 'A6', end_at: 'F20') do |chart| + chart.add_series xData: sheet['B1:E1'], yData: sheet['B2:E2'], title: sheet['A1'] + chart.add_series xData: sheet['B3:E3'], yData: sheet['B4:E4'], title: sheet['A3'] + end +end + +p.serialize 'scatter_chart_example.xlsx' +``` + +## Output + + diff --git a/examples/shared_strings_example.md b/examples/shared_strings_example.md new file mode 100644 index 00000000..cb6d811d --- /dev/null +++ b/examples/shared_strings_example.md @@ -0,0 +1,24 @@ +## Description + +To work with Apple Numbers, you must set `use_shared_strings` to true. This is most conveniently done just before rendering by calling `p.use_shared_strings = true` prior to serialization. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['First', 'Second', 'Third'] + sheet.add_row [1, 2, 3] +end + +p.use_shared_strings = true +p.serialize 'shared_strings_example.xlsx' +``` + +## Output + +No difference should be visible diff --git a/examples/sheet_protection.rb b/examples/sheet_protection.rb deleted file mode 100644 index e8d87e12..00000000 --- a/examples/sheet_protection.rb +++ /dev/null @@ -1,10 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' - -p = Axlsx::Package.new -p.workbook.add_worksheet(:name => 'Open Office') { |ws| ws.sheet_protection.password = 'fish' } -p.serialize 'sheet_protection.xlsx' - - diff --git a/examples/sheet_protection_example.md b/examples/sheet_protection_example.md new file mode 100644 index 00000000..da3a4f21 --- /dev/null +++ b/examples/sheet_protection_example.md @@ -0,0 +1,35 @@ +## Description + +You could lock sheets and exclude some of the cells. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +s = wb.styles +unlocked = s.add_style locked: false + +wb.add_worksheet(name: 'Sheet Protection') do |sheet| + sheet.sheet_protection do |protection| + protection.password = 'fish' + protection.auto_filter = false + end + + sheet.add_row [1, 2, 3], style: unlocked # These cells won't be locked + sheet.add_row [4, 5, 6] + sheet.add_row [7, 8, 9] + + # Set up auto filters + sheet.auto_filter = 'A1:C3' +end + +p.serialize 'sheet_protection_example.xlsx' +``` + +## Output + + diff --git a/examples/skydrive/real_example.rb b/examples/skydrive/real_example.rb deleted file mode 100644 index bd72b56c..00000000 --- a/examples/skydrive/real_example.rb +++ /dev/null @@ -1,63 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../../lib" - -require 'axlsx' - -Axlsx::Package.new do |p| - p.workbook do |wb| - styles = wb.styles - header = styles.add_style :bg_color => "DD", :sz => 16, :b => true, :alignment => {:horizontal => :center} - tbl_header = styles.add_style :b => true, :alignment => { :horizontal => :center } - ind_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:indent => 1} - col_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => { :horizontal => :center } - label = styles.add_style :alignment => { :indent => 1 } - money = styles.add_style :num_fmt => 5 - t_label = styles.add_style :b => true, :bg_color => "FFDFDEDF" - t_money = styles.add_style :b => true, :num_fmt => 5, :bg_color => "FFDFDEDF" - - wb.add_worksheet do |sheet| - sheet.add_row - sheet.add_row [nil, "College Budget"], :style => [nil, header] - sheet.add_row - sheet.add_row [nil, "What's coming in this month.", nil, nil, "How am I doing"], :style => tbl_header - sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"], :style => [nil, ind_header, col_header, nil, ind_header, col_header] - sheet.add_row [nil, "Estimated monthly net income", 500, nil, "Monthly income", "=C9"], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Financial aid", 100, nil, "Monthly expenses", "=C27"], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Allowance from mom & dad", 20000, nil, "Semester expenses", "=F19"], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Total", "=SUM(C6:C8)", nil, "Difference", "=F6 - SUM(F7:F8)"], :style => [nil, t_label, t_money, nil, t_label, t_money] - sheet.add_row - sheet.add_row [nil, "What's going out this month.", nil, nil, "Semester Costs"], :style => tbl_header - sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"], :style => [nil, ind_header, col_header, nil, ind_header, col_header] - sheet.add_row [nil, "Rent", 650, nil, "Tuition", 200], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Utilities", 120, nil, "Lab fees", 50], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Cell phone", 100, nil, "Other fees", 10], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Groceries", 75, nil, "Books", 150], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Auto expenses", 0, nil, "Deposits", 0], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Student loans", 0, nil, "Transportation", 30], :style => [nil, label, money, nil, label, money] - sheet.add_row [nil, "Other loans", 350, nil, "Total", "=SUM(F13:F18)"], :style => [nil, label, money, nil, t_label, t_money] - sheet.add_row [nil, "Credit cards", 450], :style => [nil, label, money] - sheet.add_row [nil, "Insurance", 0], :style => [nil, label, money] - sheet.add_row [nil, "Laundry", 10], :style => [nil, label, money] - sheet.add_row [nil, "Haircuts", 0], :style => [nil, label, money] - sheet.add_row [nil, "Medical expenses", 0], :style => [nil, label, money] - sheet.add_row [nil, "Entertainment", 500], :style => [nil, label, money] - sheet.add_row [nil, "Miscellaneous", 0], :style => [nil, label, money] - sheet.add_row [nil, "Total", "=SUM(C13:C26)"], :style => [nil, t_label, t_money] - sheet.add_chart(Axlsx::Pie3DChart) do |chart| - chart.title = sheet["B11"] - chart.add_series :data => sheet["C13:C26"], :labels => sheet["B13:B26"] - chart.start_at 7, 2 - chart.end_at 12, 15 - end - sheet.add_chart(Axlsx::Bar3DChart, :barDir => :col) do |chart| - chart.title = sheet["E11"] - chart.add_series :labels => sheet["E13:E18"], :data => sheet["F13:F18"] - chart.start_at 7, 16 - chart.end_at 12, 31 - end - %w(B4:C4 E4:F4 B11:C11 E11:F11 B2:F2).each { |range| sheet.merge_cells(range) } - sheet.column_widths 2, nil, nil, 2, nil, nil, 2 - end - end - p.use_shared_strings = true - p.serialize 'axlsx.xlsx' -end diff --git a/examples/split.rb b/examples/split.rb deleted file mode 100644 index b5ffcc05..00000000 --- a/examples/split.rb +++ /dev/null @@ -1,16 +0,0 @@ -#!/usr/bin/env ruby -w -s -# -*- coding: utf-8 -*- -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -p = Axlsx::Package.new -wb = p.workbook -wb.add_worksheet name: 'pane' do |sheet| - sheet.sheet_view.pane do |pane| - pane.top_left_cell = "B2" - pane.state = :frozen_split - pane.y_split = 2 - pane.x_split = 1 - pane.active_pane = :bottom_right - end -end -p.serialize 'pane.xlsx' diff --git a/examples/stream_example.md b/examples/stream_example.md new file mode 100644 index 00000000..409cc8fb --- /dev/null +++ b/examples/stream_example.md @@ -0,0 +1,24 @@ +## Description + +You could serialize the package into a stream instead of writing to a file directly. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['First', 'Second', 'Third'] + sheet.add_row [1, 2, 3] +end + +stream = p.to_stream +File.open('stream_example.xlsx', 'w') { |f| f.write(stream.read) } +``` + +## Output + +No difference should be visible diff --git a/examples/style_overrides_example.md b/examples/style_overrides_example.md new file mode 100644 index 00000000..731ce2e7 --- /dev/null +++ b/examples/style_overrides_example.md @@ -0,0 +1,37 @@ +## Description + +Some of the style attributes can also be set at the cell level. These cell level styles take precedence over custom styles shown in other examples. Use with caution, it could override more styles than you set (see text color in example). + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook +s = wb.styles + +default_style = s.add_style fg_color: 'FF0000', bg_color: '00FF00' + +wb.add_worksheet(name: 'Style overrides') do |sheet| + sheet.add_row ['Underline', 'Size', 'Bold', 'Italic', 'Outline', 'Color', 'Original'], style: [default_style] * 7 + + sheet.rows.last.tap do |row| + row.cells[0].u = :double + row.cells[1].sz = 20 + row.cells[2].b = true + row.cells[3].i = true + row.cells[4].outline = 1 + row.cells[5].color = '0000FF' # Inline style uses this instead of fg_color + end + + # Could be used on cell range too + sheet['A1:C1'].each { |cell| cell.strike = true } +end + +p.serialize 'style_overrides_example.xlsx' +``` + +## Output + + diff --git a/examples/styles.rb b/examples/styles.rb deleted file mode 100644 index 7a589e2e..00000000 --- a/examples/styles.rb +++ /dev/null @@ -1,66 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -require 'date' - -p = Axlsx::Package.new -wb = p.workbook -wb.styles do |style| - - # Date/Time Styles - # - # The most important thing to remember about OOXML styles is that they are - # exclusive. This means that each style must define all the components it - # requires to render the cell the way you want. A good example of this is - # changing the font size for a date. You cannot specify just the font size, - # you must also specify the number format or format code so that renders - # know how to display the serialized date float value - # - # The parts that make up a custom styles are: - # - # fonts(Font), fills(Fill), borders(Border) and number formats(NumFmt). - # Getting to know those classes will help you make the most out of custom - # styling. However axlsx certainly does not expect you to create all those - # objects manually. - # - # workbook.styles.add_style provides a helper method 'add_style' for defining - # styles in one go. The docs for that method are definitely worth a read. - # @see Style#add_style - - # When no style is applied to a cell, axlsx will automatically apply date/time - # formatting to Date and Time objects for you. However, if you are defining - # custom styles, you define all aspects of the style you want to apply. - # - # An aside on styling and auto-width. Auto-width calculations do not - # currently take into account any style or formatting you have applied to the - # data in your cells as it would require the creation of a rendering engine, - # and frankly kill performance. If you are doing a lot of custom formatting, - # you are going to be better served by specifying fixed column widths. - # - # Let's look at an example: - # - # A style that only applies a font size - large_font = wb.styles.add_style :sz => 20 - - # A style that applies both a font size and a predefined number format. - # @see NumFmt - predefined_format = wb.styles.add_style :sz => 20, :num_fmt => 14 - - # 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| - - # We then apply those styles positionally - 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 -p.serialize 'styles.xlsx' - diff --git a/examples/surrounding_borders_example.md b/examples/surrounding_borders_example.md new file mode 100644 index 00000000..d3135768 --- /dev/null +++ b/examples/surrounding_borders_example.md @@ -0,0 +1,38 @@ +## Description + +More programmatic way to set the borders + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook +s = wb.styles + +defaults = { style: :thick, color: '000000' } +borders = Hash.new do |hash, key| + hash[key] = s.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], 0, 0, borders[:right]] +bottom_row = [0, borders[:bottom_left], borders[:bottom], borders[:bottom], borders[:bottom_right]] + +wb.add_worksheet(name: 'Surrounding Border') do |sheet| + sheet.add_row [] + sheet.add_row ['', 1, 2, 3, 4], style: top_row + sheet.add_row ['', 5, 6, 7, 8], style: middle_row + sheet.add_row ['', 9, 10, 11, 12] + + #This works too! + sheet.rows.last.style = bottom_row +end + +p.serialize 'surrounding_borders_example.xlsx' +``` + +## Output + + diff --git a/examples/tab_color_example.md b/examples/tab_color_example.md new file mode 100644 index 00000000..31374e1e --- /dev/null +++ b/examples/tab_color_example.md @@ -0,0 +1,23 @@ +## Description + +Change the color of the sheet tab. + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Change Tab Color') do |sheet| + sheet.add_row ['Check', 'out', 'the', 'Tab Color', 'below!'] + sheet.sheet_pr.tab_color = 'FFFF6666' +end + +p.serialize 'tab_color_example.xlsx' +``` + +## Output + + diff --git a/examples/text_length_validation_example.md b/examples/text_length_validation_example.md new file mode 100644 index 00000000..3ccb9df3 --- /dev/null +++ b/examples/text_length_validation_example.md @@ -0,0 +1,35 @@ +## Description + +You could make validations based on text length + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook + +wb.add_worksheet(name: 'Basic Worksheet') do |sheet| + sheet.add_row ['First', 'Second', 'Third'] + sheet.add_row ['Some', 'text', 'to validate'] + + sheet.add_data_validation('A2:C2', + type: :textLength, + operator: :lessThan, + formula1: '10', + showErrorMessage: true, + errorTitle: 'Text is too long', + error: 'Max text length is 10 characters', + errorStyle: :stop, + showInputMessage: true, + promptTitle: 'Text length', + prompt: 'Max text length is 10 characters') +end + +p.serialize 'text_length_validation_example.xlsx' +``` + +## Output + + diff --git a/examples/underline.rb b/examples/underline.rb deleted file mode 100644 index ccd1b394..00000000 --- a/examples/underline.rb +++ /dev/null @@ -1,13 +0,0 @@ -$LOAD_PATH.unshift "#{File.dirname(__FILE__)}/../lib" -require 'axlsx' -p = Axlsx::Package.new -p.workbook do |wb| - wb.styles do |s| - no_underline = s.add_style :sz => 10, :b => true, :u => false, :alignment => { :horizontal=> :right } - wb.add_worksheet(:name => 'wunderlinen') do |sheet| - sheet.add_row %w{a b c really?}, :style => no_underline - end - end -end -p.serialize 'no_underline.xlsx' - diff --git a/examples/wrap_text.rb b/examples/wrap_text.rb deleted file mode 100644 index c2e1aeb5..00000000 --- a/examples/wrap_text.rb +++ /dev/null @@ -1,21 +0,0 @@ -$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/examples/wrap_text_example.md b/examples/wrap_text_example.md new file mode 100644 index 00000000..caa75b17 --- /dev/null +++ b/examples/wrap_text_example.md @@ -0,0 +1,26 @@ +## Description + +Set text wrapping from the styles + +## Code + +```ruby +require 'axlsx' + +p = Axlsx::Package.new +wb = p.workbook +s = wb.styles + +wrap_text = s.add_style alignment: { wrap_text: true } + +wb.add_worksheet(name: 'Wrap Text') do |sheet| + sheet.add_row ['First, Second and Third'], style: wrap_text + sheet.column_widths 9 +end + +p.serialize 'wrap_text_example.xlsx' +``` + +## Output + + diff --git a/test/drawing/tc_drawing.rb b/test/drawing/tc_drawing.rb index c9095fe7..05ffee98 100644 --- a/test/drawing/tc_drawing.rb +++ b/test/drawing/tc_drawing.rb @@ -23,7 +23,7 @@ class TestDrawing < Test::Unit::TestCase end def test_add_image - src = File.dirname(__FILE__) + "/../../examples/image1.jpeg" + src = File.dirname(__FILE__) + "/../fixtures/image1.jpeg" image = @ws.add_image(:image_src => src, :start_at=>[0,0], :width=>600, :height=>400) assert(@ws.drawing.anchors.last.is_a?(Axlsx::OneCellAnchor)) assert(image.is_a?(Axlsx::Pic)) @@ -31,7 +31,7 @@ class TestDrawing < Test::Unit::TestCase assert_equal(400, image.height) end def test_add_two_cell_anchor_image - src = File.dirname(__FILE__) + "/../../examples/image1.jpeg" + src = File.dirname(__FILE__) + "/../fixtures/image1.jpeg" image = @ws.add_image(:image_src => src, :start_at=>[0,0], :end_at => [15,0]) assert(@ws.drawing.anchors.last.is_a?(Axlsx::TwoCellAnchor)) assert(image.is_a?(Axlsx::Pic)) diff --git a/test/drawing/tc_hyperlink.rb b/test/drawing/tc_hyperlink.rb index b3f204b5..3a95886a 100644 --- a/test/drawing/tc_hyperlink.rb +++ b/test/drawing/tc_hyperlink.rb @@ -5,7 +5,7 @@ class TestHyperlink < Test::Unit::TestCase def setup @p = Axlsx::Package.new ws = @p.workbook.add_worksheet - @test_img = File.dirname(__FILE__) + "/../../examples/image1.jpeg" + @test_img = File.dirname(__FILE__) + "/../fixtures/image1.jpeg" @image = ws.add_image :image_src => @test_img, :hyperlink => "http://axlsx.blogspot.com" @hyperlink = @image.hyperlink end diff --git a/test/drawing/tc_one_cell_anchor.rb b/test/drawing/tc_one_cell_anchor.rb index cba66112..3c1db55f 100644 --- a/test/drawing/tc_one_cell_anchor.rb +++ b/test/drawing/tc_one_cell_anchor.rb @@ -5,7 +5,7 @@ class TestOneCellAnchor < Test::Unit::TestCase def setup @p = Axlsx::Package.new @ws = @p.workbook.add_worksheet - @test_img = File.dirname(__FILE__) + "/../../examples/image1.jpeg" + @test_img = File.dirname(__FILE__) + "/../fixtures/image1.jpeg" @image = @ws.add_image :image_src => @test_img @anchor = @image.anchor end diff --git a/test/drawing/tc_pic.rb b/test/drawing/tc_pic.rb index 67e6c416..8c2088d4 100644 --- a/test/drawing/tc_pic.rb +++ b/test/drawing/tc_pic.rb @@ -5,10 +5,10 @@ class TestPic < Test::Unit::TestCase def setup @p = Axlsx::Package.new ws = @p.workbook.add_worksheet - @test_img = @test_img_jpg = File.dirname(__FILE__) + "/../../examples/image1.jpeg" - @test_img_png = File.dirname(__FILE__) + "/../../examples/image1.png" - @test_img_gif = File.dirname(__FILE__) + "/../../examples/image1.gif" - @test_img_fake = File.dirname(__FILE__) + "/../../examples/image1_fake.jpg" + @test_img = @test_img_jpg = File.dirname(__FILE__) + "/../fixtures/image1.jpeg" + @test_img_png = File.dirname(__FILE__) + "/../fixtures/image1.png" + @test_img_gif = File.dirname(__FILE__) + "/../fixtures/image1.gif" + @test_img_fake = File.dirname(__FILE__) + "/../fixtures/image1_fake.jpg" @image = ws.add_image :image_src => @test_img, :hyperlink => 'https://github.com/randym', :tooltip => "What's up doc?", :opacity => 5 end diff --git a/examples/image1.gif b/test/fixtures/image1.gif Binary files differindex 06d56d2b..06d56d2b 100644 --- a/examples/image1.gif +++ b/test/fixtures/image1.gif diff --git a/test/fixtures/image1.jpeg b/test/fixtures/image1.jpeg Binary files differnew file mode 100644 index 00000000..ce1d0c56 --- /dev/null +++ b/test/fixtures/image1.jpeg diff --git a/examples/image1.jpg b/test/fixtures/image1.jpg Binary files differindex 25efc268..25efc268 100644 --- a/examples/image1.jpg +++ b/test/fixtures/image1.jpg diff --git a/examples/image1.png b/test/fixtures/image1.png Binary files differindex 20d9e3b8..20d9e3b8 100644 --- a/examples/image1.png +++ b/test/fixtures/image1.png diff --git a/examples/image1_fake.jpg b/test/fixtures/image1_fake.jpg index e69de29b..e69de29b 100644 --- a/examples/image1_fake.jpg +++ b/test/fixtures/image1_fake.jpg diff --git a/test/tc_package.rb b/test/tc_package.rb index 18c08f5c..ac79b487 100644 --- a/test/tc_package.rb +++ b/test/tc_package.rb @@ -65,7 +65,7 @@ class TestPackage < Test::Unit::TestCase end @fname = 'axlsx_test_serialization.xlsx' - img = File.expand_path('../../examples/image1.jpeg', __FILE__) + img = File.expand_path('../fixtures/image1.jpeg', __FILE__) ws.add_image(:image_src => img, :noSelect => true, :noMove => true, :hyperlink=>"http://axlsx.blogspot.com") do |image| image.width=720 image.height=666 @@ -73,12 +73,12 @@ class TestPackage < Test::Unit::TestCase image.start_at 5, 5 image.end_at 10, 10 end - ws.add_image :image_src => File.expand_path('../../examples/image1.gif', __FILE__) do |image| + ws.add_image :image_src => File.expand_path('../fixtures/image1.gif', __FILE__) do |image| image.start_at 0, 20 image.width=360 image.height=333 end - ws.add_image :image_src => File.expand_path('../../examples/image1.png', __FILE__) do |image| + ws.add_image :image_src => File.expand_path('../fixtures/image1.png', __FILE__) do |image| image.start_at 9, 20 image.width = 180 image.height = 167 diff --git a/test/util/tc_mime_type_utils.rb b/test/util/tc_mime_type_utils.rb index ff54ae0a..cfeec9d3 100644 --- a/test/util/tc_mime_type_utils.rb +++ b/test/util/tc_mime_type_utils.rb @@ -1,7 +1,7 @@ require 'tc_helper.rb' class TestMimeTypeUtils < Test::Unit::TestCase def setup - @test_img = File.dirname(__FILE__) + "/../../examples/image1.jpeg" + @test_img = File.dirname(__FILE__) + "/../fixtures/image1.jpeg" end def teardown |
