# -*- coding: utf-8 -*- require 'helper' require 'write_xlsx' class TestExampleMatch < Test::Unit::TestCase def setup setup_dir_var end def teardown File.delete(@xlsx) if File.exist?(@xlsx) end def test_a_simple @xlsx = 'a_simple.xlsx' # Create a new workbook called simple.xls and add a worksheet workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # The general syntax is write(row, column, token). Note that row and # column are zero indexed # # Write some text worksheet.write(0, 0, "Hi Excel!") # Write some numbers worksheet.write(2, 0, 3) # Writes 3 worksheet.write(3, 0, 3.00000) # Writes 3 worksheet.write(4, 0, 3.00001) # Writes 3.00001 worksheet.write(5, 0, 3.14159) # TeX revision no.? # Write some formulas worksheet.write(7, 0, '=A3 + A6') worksheet.write(8, 0, '=IF(A5>3,"Yes", "No")') # Write a hyperlink hyperlink_format = workbook.add_format( :color => 'blue', :underline => 1 ) worksheet.write(10, 0, 'http://www.ruby-lang.org/', hyperlink_format) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_array_formula @xlsx = 'array_formula.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Write some test data. worksheet.write('B1', [ [ 500, 10 ], [ 300, 15 ] ]) worksheet.write('B5', [ [ 1, 2, 3 ], [ 20234, 21003, 10000 ] ]) # Write an array formula that returns a single value worksheet.write('A1', '{=SUM(B1:C1*B2:C2)}') # Same as above but more verbose. worksheet.write_array_formula('A2:A2', '{=SUM(B1:C1*B2:C2)}') # Write an array formula that returns a range of values worksheet.write_array_formula('A5:A7', '{=TREND(C5:C7,B5:B7)}') workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_autofilter @xlsx = 'autofilter.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet worksheet2 = workbook.add_worksheet worksheet3 = workbook.add_worksheet worksheet4 = workbook.add_worksheet worksheet5 = workbook.add_worksheet worksheet6 = workbook.add_worksheet bold = workbook.add_format(:bold => 1 ) # Extract the data embedded at the end of this file. data_array = autofilter_data.split("\n") headings = data_array.shift.split data = [] data_array.each { |line| data << line.split } # Set up several sheets with the same data. workbook.worksheets.each do |worksheet| worksheet.set_column('A:D', 12 ) worksheet.set_row(0, 20, bold ) worksheet.write('A1', headings ) end ############################################################################### # # Example 1. Autofilter without conditions. # worksheet1.autofilter('A1:D51' ) worksheet1.write('A2', [ data ] ) ############################################################################### # # # Example 2. Autofilter with a filter condition in the first column. # # The range in this example is the same as above but in row-column notation. worksheet2.autofilter(0, 0, 50, 3 ) # The placeholder "Region" in the filter is ignored and can be any string # that adds clarity to the expression. # worksheet2.filter_column(0, 'Region eq East' ) # # Hide the rows that don't match the filter criteria. # row = 1 data.each do |row_data| region = row_data[0] worksheet2.set_row(row, nil, nil, 1) unless region == 'East' worksheet2.write(row, 0, row_data) row += 1 end ############################################################################### # # # Example 3. Autofilter with a dual filter condition in one of the columns. # worksheet3.autofilter('A1:D51' ) worksheet3.filter_column('A', 'x eq East or x eq South' ) # # Hide the rows that don't match the filter criteria. # row = 1 data.each do |row_data| region = row_data[0] worksheet3.set_row(row, nil, nil, 1) unless region == 'East' || region == 'South' worksheet3.write(row, 0,row_data) row += 1 end ############################################################################### # # # Example 4. Autofilter with filter conditions in two columns. # worksheet4.autofilter('A1:D51') worksheet4.filter_column('A', 'x eq East') worksheet4.filter_column('C', 'x > 3000 and x < 8000') # # Hide the rows that don't match the filter criteria. # row = 1 data.each do |row_data| region = row_data[0] volume = row_data[2] unless region == 'East' && volume.to_i > 3000 && volume.to_i < 8000 # Hide row. worksheet4.set_row(row, nil, nil, 1) end worksheet4.write(row, 0, row_data) row += 1 end ############################################################################### # # # Example 5. Autofilter with filter for blanks. # # Create a blank cell in our test data. data[5][0] = '' worksheet5.autofilter('A1:D51') worksheet5.filter_column('A', 'x eq Blanks') # # Hide the rows that don't match the filter criteria. # row = 1 data.each do |row_data| region = row_data[0] worksheet5.set_row(row, nil, nil, 1) unless region == '' worksheet5.write(row, 0, row_data) row += 1 end ############################################################################### # # # Example 6. Autofilter with filter for non-blanks. # worksheet6.autofilter('A1:D51') worksheet6.filter_column('A', 'x eq NonBlanks') # # Hide the rows that don't match the filter criteria. # row = 1 data.each do |row_data| region = row_data[0] worksheet6.set_row(row, nil, nil, 1) unless region != '' worksheet6.write(row, 0, row_data) row += 1 end workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_scatter06 @xlsx = 'chart_scatter06.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet chart = workbook.add_chart(:type => 'scatter', :embedded => 1) # For testing, copy the randomly generated axis ids in the target xlsx file. chart.instance_variable_set(:@axis_ids, [57708544, 44297600]) data = [ [ 1, 2, 3, 4, 5 ], [ 2, 4, 6, 8, 10 ], [ 3, 6, 9, 12, 15 ] ] worksheet.write('A1', data) chart.add_series( :categories => '=Sheet1!$A$1:$A$5', :values => '=Sheet1!$B$1:$B$5' ) chart.add_series( :categories => '=Sheet1!$A$1:$A$5', :values => '=Sheet1!$C$1:$C$5' ) chart.set_x_axis(:minor_unit => 1, :major_unit => 3) chart.set_y_axis(:minor_unit => 2, :major_unit => 4) worksheet.insert_chart('E9', chart) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def autofilter_data < 1) # Add the worksheet data that the charts will refer to. headings = [ 'Number', 'Batch 1', 'Batch 2' ] data = [ [ 2, 3, 4, 5, 6, 7 ], [ 40, 40, 50, 30, 25, 50 ], [ 30, 25, 30, 10, 5, 10 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new chart object. In this case an embedded chart. chart = workbook.add_chart(:type => 'area', :embedded => 1) # Configure the first series. chart.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. Note alternative use of array ref to define # ranges: [ sheetname, row_start, row_end, col_start, col_end ]. chart.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart.set_title(:name => 'Results of sample analysis') chart.set_x_axis(:name => 'Test number') chart.set_y_axis(:name => 'Sample length (mm)') # Set an Excel chart style. Blue colors with white outline and shadow. chart.set_style(11) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D2', chart, 25, 10) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_bar @xlsx = 'chart_bar.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Number', 'Batch 1', 'Batch 2' ] data = [ [ 2, 3, 4, 5, 6, 7 ], [ 10, 40, 50, 20, 10, 50 ], [ 30, 60, 70, 50, 40, 30 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new chart object. In this case an embedded chart. chart = workbook.add_chart(:type => 'bar', :embedded => 1) # Configure the first series. chart.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. Note alternative use of array ref to define # ranges: [ sheetname, row_start, row_end, col_start, col_end ]. chart.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart.set_title(:name => 'Results of sample analysis') chart.set_x_axis(:name => 'Test number') chart.set_y_axis(:name => 'Sample length (mm)') # Set an Excel chart style. Blue colors with white outline and shadow. chart.set_style(11) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D2', chart, 25, 10) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_column @xlsx = 'chart_column.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Number', 'Batch 1', 'Batch 2' ] data = [ [ 2, 3, 4, 5, 6, 7 ], [ 10, 40, 50, 20, 10, 50 ], [ 30, 60, 70, 50, 40, 30 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new chart object. In this case an embedded chart. chart = workbook.add_chart(:type => 'column', :embedded => 1) # Configure the first series. chart.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. Note alternative use of array ref to define # ranges: [ sheetname, row_start, row_end, col_start, col_end ]. chart.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart.set_title(:name => 'Results of sample analysis') chart.set_x_axis(:name => 'Test number') chart.set_y_axis(:name => 'Sample length (mm)') # Set an Excel chart style. Blue colors with white outline and shadow. chart.set_style(11) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D2', chart, 25, 10) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_line @xlsx = 'chart_line.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Number', 'Batch 1', 'Batch 2' ] data = [ [ 2, 3, 4, 5, 6, 7 ], [ 10, 40, 50, 20, 10, 50 ], [ 30, 60, 70, 50, 40, 30 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new chart object. In this case an embedded chart. chart = workbook.add_chart(:type => 'line', :embedded => 1) # Configure the first series. chart.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. Note alternative use of array ref to define # ranges: [ sheetname, row_start, row_end, col_start, col_end ]. chart.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart.set_title(:name => 'Results of sample analysis') chart.set_x_axis(:name => 'Test number') chart.set_y_axis(:name => 'Sample length (mm)') # Set an Excel chart style. Blue colors with white outline and shadow. chart.set_style(10) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D2', chart, 25, 10) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_pie @xlsx = 'chart_pie.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Category', 'Values' ] data = [ [ 'Apple', 'Cherry', 'Pecan' ], [ 60, 30, 10 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new chart object. In this case an embedded chart. chart = workbook.add_chart(:type => 'pie', :embedded => 1) # Configure the series. Note the use of the array ref to define ranges: # [ $sheetname, $row_start, $row_end, $col_start, $col_end ]. chart.add_series( :name => 'Pie sales data', :categories => [ 'Sheet1', 1, 3, 0, 0 ], :values => [ 'Sheet1', 1, 3, 1, 1 ] ) # Add a title. chart.set_title(:name => 'Popular Pie Types') # Set an Excel chart style. Blue colors with white outline and shadow. chart.set_style(10) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('C2', chart, 25, 10) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_radar @xlsx = 'chart_radar.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Number', 'Batch 1', 'Batch 2' ] data = [ [ 2, 3, 4, 5, 6, 7 ], [ 30, 60, 70, 50, 40, 30 ], [ 25, 40, 50, 30, 50, 40 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new chart object. In this case an embedded chart. chart1 = workbook.add_chart(:type => 'radar', :embedded => 1) # Configure the first series. chart1.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. Note alternative use of array ref to define # ranges: [ sheetname, row_start, row_end, col_start, col_end ]. chart1.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart1.set_title(:name => 'Results of sample analysis') chart1.set_x_axis(:name => 'Test number') chart1.set_y_axis(:name => 'Sample length (mm)') # Set an Excel chart style. Blue colors with white outline and shadow. chart1.set_style(11) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D2', chart1, 25, 10) # # Create a with_markers chart sub-type # chart2 = workbook.add_chart( :type => 'radar', :embedded => 1, :subtype => 'with_markers' ) # Configure the first series. chart2.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. chart2.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart2.set_title(:name => 'Stacked Chart') chart2.set_x_axis(:name => 'Test number') chart2.set_y_axis(:name => 'Sample length (mm)') # Set an Excel chart style. Blue colors with white outline and shadow. chart2.set_style(12) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D18', chart2, 25, 11) # # Create a filled chart sub-type # chart3 = workbook.add_chart( :type => 'radar', :embedded => 1, :subtype => 'filled' ) # Configure the first series. chart3.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. chart3.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart3.set_title(:name => 'Percent Stacked Chart') chart3.set_x_axis(:name => 'Test number') chart3.set_y_axis(:name => 'Sample length (mm)') # Set an Excel chart style. Blue colors with white outline and shadow. chart3.set_style(13) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D34', chart3, 25, 11) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_scatter @xlsx = 'chart_scatter.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Number', 'Batch 1', 'Batch 2' ] data = [ [ 2, 3, 4, 5, 6, 7 ], [ 10, 40, 50, 20, 10, 50 ], [ 30, 60, 70, 50, 40, 30 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new chart object. In this case an embedded chart. chart = workbook.add_chart(:type => 'scatter', :embedded => 1) # Configure the first series. chart.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. Note alternative use of array ref to define # ranges: [ $sheetname, $row_start, $row_end, $col_start, $col_end ].$chart->add_series( chart.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart.set_title(:name => 'Results of sample analysis') chart.set_x_axis(:name => 'Test number') chart.set_y_axis(:name => 'Sample length (mm)') # Set an Excel chart style. Blue colors with white outline and shadow. chart.set_style(10) # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D2', chart, 25, 10) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_stock @xlsx = 'chart_stock.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) date_format = workbook.add_format(:num_format => 'dd/mm/yyyy') chart = workbook.add_chart(:type => 'stock', :embedded => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Date', 'High', 'Low', 'Close' ] data = [ [ '2007-01-01T', '2007-01-02T', '2007-01-03T', '2007-01-04T', '2007-01-05T' ], [ 27.2, 25.03, 19.05, 20.34, 18.5 ], [ 23.49, 19.55, 15.12, 17.84, 16.34 ], [ 25.45, 23.05, 17.32, 20.45, 17.34 ] ] worksheet.write('A1', headings, bold) (0 .. 4).each do |row| worksheet.write_date_time(row + 1, 0, data[0][row], date_format) worksheet.write(row + 1, 1, data[1][row]) worksheet.write(row + 1, 2, data[2][row]) worksheet.write(row + 1, 3, data[3][row]) end worksheet.set_column('A:D', 11) # Add a series for each of the High-Low-Close columns. chart.add_series( :categories => '=Sheet1!$A$2:$A$6', :values => '=Sheet1!$B$2:$B$6' ) chart.add_series( :categories => '=Sheet1!$A$2:$A$6', :values => '=Sheet1!$C$2:$C$6' ) chart.add_series( :categories => '=Sheet1!$A$2:$A$6', :values => '=Sheet1!$D$2:$D$6' ) # Add a chart title and some axis labels. chart.set_title(:name => 'High-Low-Close') chart.set_x_axis(:name => 'Date') chart.set_y_axis(:name => 'Share price') worksheet.insert_chart('E9', chart) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_secondary_axis @xlsx = 'chart_secondary_axis.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Aliens', 'Humans'] data = [ [ 2, 3, 4, 5, 6, 7 ], [ 10, 40, 50, 20, 10, 50 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new chart object. In this case an embedded chart. chart = workbook.add_chart(:type => 'line', :embedded => 1) # Configure the first series. chart.add_series( :name => '=Sheet1!$A$1', :values => '=Sheet1!$A$2:$A$7', :y2_axis => 1 ) chart.add_series( :name => '=Sheet1!$B$1', :values => '=Sheet1!$B$2:$B$7' ) chart.set_legend(:position => 'right') # Add a chart title and some axis labels. chart.set_title(:name => 'Survey results') chart.set_x_axis(:name => 'Days') chart.set_y_axis(:name => 'Population', :major_gridlines => {:visible => 0}) chart.set_y2_axis(:name => 'Laser wounds') # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D2', chart, 25, 10) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_comments1 @xlsx = 'comments1.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet worksheet.write('A1', 'Hello') worksheet.write_comment('A1', 'This is a comment') workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_comments2 @xlsx = 'comments2.xlsx' workbook = WriteXLSX.new(@xlsx) text_wrap = workbook.add_format( :text_wrap => 1, :valign => 'top' ) worksheet1 = workbook.add_worksheet worksheet2 = workbook.add_worksheet worksheet3 = workbook.add_worksheet worksheet4 = workbook.add_worksheet worksheet5 = workbook.add_worksheet worksheet6 = workbook.add_worksheet worksheet7 = workbook.add_worksheet worksheet8 = workbook.add_worksheet # Variables that we will use in each example. cell_text = '' comment = '' ############################################################################### # # Example 1. Demonstrates a simple cell comments without formatting. # comments. # # Set up some formatting. worksheet1.set_column( 'C:C', 25 ) worksheet1.set_row( 2, 50 ) worksheet1.set_row( 5, 50 ) # Simple ascii string. cell_text = 'Hold the mouse over this cell to see the comment.' comment = 'This is a comment.' worksheet1.write( 'C3', cell_text, text_wrap ) worksheet1.write_comment( 'C3', comment ) cell_text = 'This is a UTF-8 string.' comment = '☺' worksheet1.write( 'C6', cell_text, text_wrap ) worksheet1.write_comment( 'C6', comment ) ############################################################################### # # Example 2. Demonstrates visible and hidden comments. # # Set up some formatting. worksheet2.set_column( 'C:C', 25 ) worksheet2.set_row( 2, 50 ) worksheet2.set_row( 5, 50 ) cell_text = 'This cell comment is visible.' comment = 'Hello.' worksheet2.write( 'C3', cell_text, text_wrap ) worksheet2.write_comment( 'C3', comment, :visible => 1 ) cell_text = "This cell comment isn't visible (the default)." comment = 'Hello.' worksheet2.write( 'C6', cell_text, text_wrap ) worksheet2.write_comment( 'C6', comment ) ############################################################################### # # Example 3. Demonstrates visible and hidden comments set at the worksheet # level. # # Set up some formatting. worksheet3.set_column( 'C:C', 25 ) worksheet3.set_row( 2, 50 ) worksheet3.set_row( 5, 50 ) worksheet3.set_row( 8, 50 ) # Make all comments on the worksheet visible. worksheet3.show_comments cell_text = 'This cell comment is visible, explicitly.' comment = 'Hello.' worksheet3.write( 'C3', cell_text, text_wrap ) worksheet3.write_comment( 'C3', comment, :visible => 1 ) cell_text = 'This cell comment is also visible because we used show_comments().' comment = 'Hello.' worksheet3.write( 'C6', cell_text, text_wrap ) worksheet3.write_comment( 'C6', comment ) cell_text = 'However, we can still override it locally.' comment = 'Hello.' worksheet3.write( 'C9', cell_text, text_wrap ) worksheet3.write_comment( 'C9', comment, :visible => 0 ) ############################################################################### # # Example 4. Demonstrates changes to the comment box dimensions. # # Set up some formatting. worksheet4.set_column( 'C:C', 25 ) worksheet4.set_row( 2, 50 ) worksheet4.set_row( 5, 50 ) worksheet4.set_row( 8, 50 ) worksheet4.set_row( 15, 50 ) worksheet4.show_comments cell_text = 'This cell comment is default size.' comment = 'Hello.' worksheet4.write( 'C3', cell_text, text_wrap ) worksheet4.write_comment( 'C3', comment ) cell_text = 'This cell comment is twice as wide.' comment = 'Hello.' worksheet4.write( 'C6', cell_text, text_wrap ) worksheet4.write_comment( 'C6', comment, :x_scale => 2 ) cell_text = 'This cell comment is twice as high.' comment = 'Hello.' worksheet4.write( 'C9', cell_text, text_wrap ) worksheet4.write_comment( 'C9', comment, :y_scale => 2 ) cell_text = 'This cell comment is scaled in both directions.' comment = 'Hello.' worksheet4.write( 'C16', cell_text, text_wrap ) worksheet4.write_comment( 'C16', comment, :x_scale => 1.2, :y_scale => 0.8 ) cell_text = 'This cell comment has width and height specified in pixels.' comment = 'Hello.' worksheet4.write( 'C19', cell_text, text_wrap ) worksheet4.write_comment( 'C19', comment, :width => 200, :height => 20 ) ############################################################################### # # Example 5. Demonstrates changes to the cell comment position. # worksheet5.set_column( 'C:C', 25 ) worksheet5.set_row( 2, 50 ) worksheet5.set_row( 5, 50 ) worksheet5.set_row( 8, 50 ) worksheet5.set_row( 11, 50 ) worksheet5.show_comments cell_text = 'This cell comment is in the default position.' comment = 'Hello.' worksheet5.write( 'C3', cell_text, text_wrap ) worksheet5.write_comment( 'C3', comment ) cell_text = 'This cell comment has been moved to another cell.' comment = 'Hello.' worksheet5.write( 'C6', cell_text, text_wrap ) worksheet5.write_comment( 'C6', comment, :start_cell => 'E4' ) cell_text = 'This cell comment has been moved to another cell.' comment = 'Hello.' worksheet5.write( 'C9', cell_text, text_wrap ) worksheet5.write_comment( 'C9', comment, :start_row => 8, :start_col => 4 ) cell_text = 'This cell comment has been shifted within its default cell.' comment = 'Hello.' worksheet5.write( 'C12', cell_text, text_wrap ) worksheet5.write_comment( 'C12', comment, :x_offset => 30, :y_offset => 12 ) ############################################################################### # # Example 6. Demonstrates changes to the comment background colour. # worksheet6.set_column( 'C:C', 25 ) worksheet6.set_row( 2, 50 ) worksheet6.set_row( 5, 50 ) worksheet6.set_row( 8, 50 ) worksheet6.show_comments cell_text = 'This cell comment has a different colour.' comment = 'Hello.' worksheet6.write( 'C3', cell_text, text_wrap ) worksheet6.write_comment( 'C3', comment, :color => 'green' ) cell_text = 'This cell comment has the default colour.' comment = 'Hello.' worksheet6.write( 'C6', cell_text, text_wrap ) worksheet6.write_comment( 'C6', comment ) cell_text = 'This cell comment has a different colour.' comment = 'Hello.' worksheet6.write( 'C9', cell_text, text_wrap ) worksheet6.write_comment( 'C9', comment, :color => 0x35 ) ############################################################################### # # Example 7. Demonstrates how to set the cell comment author. # worksheet7.set_column( 'C:C', 30 ) worksheet7.set_row( 2, 50 ) worksheet7.set_row( 5, 50 ) worksheet7.set_row( 8, 50 ) author = '' cell = 'C3' cell_text = "Move the mouse over this cell and you will see 'Cell commented " + "by #{author}' (blank) in the status bar at the bottom" comment = 'Hello.' worksheet7.write( cell, cell_text, text_wrap ) worksheet7.write_comment( cell, comment ) author = 'Ruby' cell = 'C6' cell_text = "Move the mouse over this cell and you will see 'Cell commented " + "by #{author}' in the status bar at the bottom" comment = 'Hello.' worksheet7.write( cell, cell_text, text_wrap ) worksheet7.write_comment( cell, comment, :author => author ) author = '€' cell = 'C9' cell_text = "Move the mouse over this cell and you will see 'Cell commented " + "by #{author}' in the status bar at the bottom" comment = 'Hello.' worksheet7.write( cell, cell_text, text_wrap ) worksheet7.write_comment( cell, comment, :author => author ) ############################################################################### # # Example 8. Demonstrates the need to explicitly set the row height. # # Set up some formatting. worksheet8.set_column( 'C:C', 25 ) worksheet8.set_row( 2, 80 ) worksheet8.show_comments cell_text = 'The height of this row has been adjusted explicitly using ' + 'set_row(). The size of the comment box is adjusted ' + 'accordingly by WriteXLSX.' comment = 'Hello.' worksheet8.write( 'C3', cell_text, text_wrap ) worksheet8.write_comment( 'C3', comment ) cell_text = 'The height of this row has been adjusted by Excel due to the ' + 'text wrap property being set. Unfortunately this means that ' + 'the height of the row is unknown to WriteXLSX at ' + "run time and thus the comment box is stretched as well.\n\n" + 'Use set_row() to specify the row height explicitly to avoid ' + 'this problem.' comment = 'Hello.' worksheet8.write( 'C6', cell_text, text_wrap ) worksheet8.write_comment( 'C6', comment ) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_conditional_format @xlsx = 'conditional_format.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet # Light red fill with dark red text. format1 = workbook.add_format( :bg_color => '#FFC7CE', :color => '#9C0006' ) # Green fill with dark green text. format2 = workbook.add_format( :bg_color => '#C6EFCE', :color => '#006100' ) # Some sample data to run the conditional formatting against. data = [ [ 90, 80, 50, 10, 20, 90, 40, 90, 30, 40 ], [ 20, 10, 90, 100, 30, 60, 70, 60, 50, 90 ], [ 10, 50, 60, 50, 20, 50, 80, 30, 40, 60 ], [ 10, 90, 20, 40, 10, 40, 50, 70, 90, 50 ], [ 70, 100, 10, 90, 10, 10, 20, 100, 100, 40 ], [ 20, 60, 10, 100, 30, 10, 20, 60, 100, 10 ], [ 10, 60, 10, 80, 100, 80, 30, 30, 70, 40 ], [ 30, 90, 60, 10, 10, 100, 40, 40, 30, 40 ], [ 80, 90, 10, 20, 20, 50, 80, 20, 60, 90 ], [ 60, 80, 30, 30, 10, 50, 80, 60, 50, 30 ] ] # This example below highlights cells that have a value greater than or # equal to 50 in red and cells below that value in green. caption = 'Cells with values >= 50 are in light red. ' + 'Values < 50 are in light green' # Write the data. worksheet1.write('A1', caption) worksheet1.write_col('B3', data) # Write a conditional format over a range. worksheet1.conditional_formatting('B3:K12', { :type => 'cell', :format => format1, :criteria => '>=', :value => 50 } ) # Write another conditional format over the same range. worksheet1.conditional_formatting('B3:K12', { :type => 'cell', :format => format2, :criteria => '<', :value => 50 } ) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_data_validate @xlsx = 'data_validate.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Add a format for the header cells. header_format = workbook.add_format( :border => 1, :bg_color => 43, :bold => 1, :text_wrap => 1, :valign => 'vcenter', :indent => 1 ) # Set up layout of the worksheet. worksheet.set_column('A:A', 68) worksheet.set_column('B:B', 15) worksheet.set_column('D:D', 15) worksheet.set_row(0, 36) worksheet.set_selection('B3') # Write the header cells and some data that will be used in the examples. row = 0 heading1 = 'Some examples of data validation in WriteXLSX' heading2 = 'Enter values in this column' heading3 = 'Sample Data' worksheet.write('A1', heading1, header_format) worksheet.write('B1', heading2, header_format) worksheet.write('D1', heading3, header_format) worksheet.write('D3', ['Integers', 1, 10]) worksheet.write('D4', ['List data', 'open', 'high', 'close']) worksheet.write('D5', ['Formula', '=AND(F5=50,G5=60)', 50, 60]) # # Example 1. Limiting input to an integer in a fixed range. # txt = 'Enter an integer between 1 and 10' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'integer', :criteria => 'between', :minimum => 1, :maximum => 10 }) # # Example 2. Limiting input to an integer outside a fixed range. # txt = 'Enter an integer that is not between 1 and 10 (using cell references)' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'integer', :criteria => 'not between', :minimum => '=E3', :maximum => '=F3' }) # # Example 3. Limiting input to an integer greater than a fixed value. # txt = 'Enter an integer greater than 0' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'integer', :criteria => '>', :value => 0 }) # # Example 4. Limiting input to an integer less than a fixed value. # txt = 'Enter an integer less than 10' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'integer', :criteria => '<', :value => 10 }) # # Example 5. Limiting input to a decimal in a fixed range. # txt = 'Enter a decimal between 0.1 and 0.5' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'decimal', :criteria => 'between', :minimum => 0.1, :maximum => 0.5 }) # # Example 6. Limiting input to a value in a dropdown list. # txt = 'Select a value from a drop down list' row += 2 bp=1 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'list', :source => ['open', 'high', 'close'] }) # # Example 6. Limiting input to a value in a dropdown list. # txt = 'Select a value from a drop down list (using a cell range)' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'list', :source => '=$E$4:$G$4' }) # # Example 7. Limiting input to a date in a fixed range. # txt = 'Enter a date between 1/1/2008 and 12/12/2008' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'date', :criteria => 'between', :minimum => '2008-01-01T', :maximum => '2008-12-12T' }) # # Example 8. Limiting input to a time in a fixed range. # txt = 'Enter a time between 6:00 and 12:00' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'time', :criteria => 'between', :minimum => 'T06:00', :maximum => 'T12:00' }) # # Example 9. Limiting input to a string greater than a fixed length. # txt = 'Enter a string longer than 3 characters' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'length', :criteria => '>', :value => 3 }) # # Example 10. Limiting input based on a formula. # txt = 'Enter a value if the following is true "=AND(F5=50,G5=60)"' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'custom', :value => '=AND(F5=50,G5=60)' }) # # Example 11. Displaying and modify data validation messages. # txt = 'Displays a message when you select the cell' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'integer', :criteria => 'between', :minimum => 1, :maximum => 100, :input_title => 'Enter an integer:', :input_message => 'between 1 and 100' }) # # Example 12. Displaying and modify data validation messages. # txt = 'Display a custom error message when integer isn\'t between 1 and 100' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'integer', :criteria => 'between', :minimum => 1, :maximum => 100, :input_title => 'Enter an integer:', :input_message => 'between 1 and 100', :error_title => 'Input value is not valid!', :error_message => 'It should be an integer between 1 and 100' }) # # Example 13. Displaying and modify data validation messages. # txt = 'Display a custom information message when integer isn\'t between 1 and 100' row += 2 worksheet.write(row, 0, txt) worksheet.data_validation(row, 1, { :validate => 'integer', :criteria => 'between', :minimum => 1, :maximum => 100, :input_title => 'Enter an integer:', :input_message => 'between 1 and 100', :error_title => 'Input value is not valid!', :error_message => 'It should be an integer between 1 and 100', :error_type => 'information' }) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_date_time @xlsx = 'date_time.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Expand the first column so that the date is visible. worksheet.set_column('A:B', 30) # Write the column headers. worksheet.write('A1', 'Formatted date', bold) worksheet.write('B1', 'Format', bold) # Examples date and time formats. In the outpu file compare how changing # the format codes change the appearance of the date. # date_formats = [ 'dd/mm/yy', 'mm/dd/yy', '', 'd mm yy', 'dd mm yy', '', 'dd m yy', 'dd mm yy', 'dd mmm yy', 'dd mmmm yy', '', 'dd mm y', 'dd mm yyy', 'dd mm yyyy', '', 'd mmmm yyyy', '', 'dd/mm/yy', 'dd/mm/yy hh:mm', 'dd/mm/yy hh:mm:ss', 'dd/mm/yy hh:mm:ss.000', '', 'hh:mm', 'hh:mm:ss', 'hh:mm:ss.000' ] # Write the same date and time using each of the above formats. The empty # string formats create a blank line to make the example clearer. # row = 0 date_formats.each do |date_format| row += 1 next if date_format == '' # Create a format for the date or time. format = workbook.add_format( :num_format => date_format, :align => 'left' ) # Write the same date using different formats. worksheet.write_date_time(row, 0, '2004-08-01T12:30:45.123', format) worksheet.write(row, 1, date_format) end # The following is an example of an invalid date. It is writen as a string # instead of a number. This is also Excel's default behaviour. # row += 2 worksheet.write_date_time(row, 0, '2004-13-01T12:30:45.123') worksheet.write(row, 1, 'Invalid date. Written as string.', bold) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_defined_name @xlsx = 'defined_name.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet worksheet2 = workbook.add_worksheet # Define some global/workbook names. workbook.define_name('Exchange_rate', '=0.96') workbook.define_name('Sales', '=Sheet1!$G$1:$H$10') # Define a local/worksheet name. workbook.define_name('Sheet2!Sales', '=Sheet2!$G$1:$G$10') # Write some text in the file and one of the defined names in a formula. workbook.worksheets.each do |worksheet| worksheet.set_column('A:A', 45) worksheet.write('A1', 'This worksheet contains some defined names.') worksheet.write('A2', 'See Formulas -> Name Manager above.') worksheet.write('A3', 'Example formula in cell B3 ->') worksheet.write('B3', '=Exchange_rate') end workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_demo @xlsx = 'demo.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet('Demo') worksheet2 = workbook.add_worksheet('Another sheet') worksheet3 = workbook.add_worksheet('And another') bold = workbook.add_format(:bold => 1) ####################################################################### # # Write a general heading # worksheet.set_column('A:A', 36, bold) worksheet.set_column('B:B', 20) worksheet.set_row(0, 40) heading = workbook.add_format( :bold => 1, :color => 'blue', :size => 16, :merge => 1, :align => 'vcenter' ) hyperlink_format = workbook.add_format( :color => 'blue', :underline => 1 ) headings = ['Features of WriteXLSX', ''] worksheet.write_row('A1', headings, heading) ####################################################################### # # Some text examples # text_format = workbook.add_format( :bold => 1, :italic => 1, :color => 'red', :size => 18, :font => 'Lucida Calligraphy' ) worksheet.write('A2', "Text") worksheet.write('B2', "Hello Excel") worksheet.write('A3', "Formatted text") worksheet.write('B3', "Hello Excel", text_format) worksheet.write('A4', "Unicode text") worksheet.write('B4', "А Б В Г Д") ####################################################################### # # Some numeric examples # num1_format = workbook.add_format(:num_format => '$#,##0.00') num2_format = workbook.add_format(:num_format => ' d mmmm yyy') worksheet.write('A5', "Numbers") worksheet.write('B5', 1234.56) worksheet.write('A6', "Formatted numbers") worksheet.write('B6', 1234.56, num1_format) worksheet.write('A7', "Formatted numbers") worksheet.write('B7', 37257, num2_format) ####################################################################### # # Formulae # worksheet.set_selection('B8') worksheet.write('A8', 'Formulas and functions, "=SIN(PI()/4)"') worksheet.write('B8', '=SIN(PI()/4)') ####################################################################### # # Hyperlinks # worksheet.write('A9', "Hyperlinks") worksheet.write('B9', 'http://www.ruby-lang.org/', hyperlink_format) ####################################################################### # # Images # # Not implemented yet. #worksheet.write('A10', "Images") #worksheet.insert_image('B10', 'republic.png', 16, 8) ####################################################################### # # Misc # worksheet.write('A18', "Page/printer setup") worksheet.write('A19', "Multiple worksheets") workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_diag_border @xlsx = 'diag_border.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet() format1 = workbook.add_format(:diag_type => 1) format2 = workbook.add_format(:diag_type => 2) format3 = workbook.add_format(:diag_type => 3) format4 = workbook.add_format( :diag_type => 3, :diag_border => 7, :diag_color => 'red' ) worksheet.write('B3', 'Text', format1) worksheet.write('B6', 'Text', format2) worksheet.write('B9', 'Text', format3) worksheet.write('B12', 'Text', format4) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_fit_to_pages @xlsx = 'fit_to_pages.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet worksheet2 = workbook.add_worksheet worksheet3 = workbook.add_worksheet worksheet4 = workbook.add_worksheet worksheet1.write( 0, 0, "fit_to_pages(1, 1)" ) worksheet1.fit_to_pages(1, 1) # Fit to 1x1 pages worksheet2.write( 0, 0, "fit_to_pages(2, 1)" ) worksheet2.fit_to_pages(2, 1) # Fit to 2x1 pages worksheet3.write( 0, 0, "fit_to_pages(1, 2)" ) worksheet3.fit_to_pages(1, 2) # Fit to 1x2 pages worksheet4.write( 0, 0, "fit_to_pages(1, 0)" ) worksheet4.fit_to_pages(1, 0) # 1 page wide and as long as necessary workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_formats @xlsx = 'formats.xlsx' workbook = WriteXLSX.new(@xlsx) # Some common formats center = workbook.add_format(:align => 'center') heading = workbook.add_format(:align => 'center', :bold => 1) # The named colors colors = { 0x08 => 'black', 0x0C => 'blue', 0x10 => 'brown', 0x0F => 'cyan', 0x17 => 'gray', 0x11 => 'green', 0x0B => 'lime', 0x0E => 'magenta', 0x12 => 'navy', 0x35 => 'orange', 0x21 => 'pink', 0x14 => 'purple', 0x0A => 'red', 0x16 => 'silver', 0x09 => 'white', 0x0D => 'yellow' } ###################################################################### # # Intro. # def intro(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Introduction') worksheet.set_column(0, 0, 60) format = workbook.add_format format.set_bold format.set_size(14) format.set_color('blue') format.set_align('center') format2 = workbook.add_format format2.set_bold format2.set_color('blue') format3 = workbook.add_format( :color => 'blue', :underline => 1 ) worksheet.write(2, 0, 'This workbook demonstrates some of', format) worksheet.write(3, 0, 'the formatting options provided by', format) worksheet.write(4, 0, 'the Excel::Writer::XLSX module.', format) worksheet.write('A7', 'Sections:', format2) worksheet.write('A8', "internal:Fonts!A1", 'Fonts', format3) worksheet.write('A9', "internal:'Named colors'!A1", 'Named colors', format3) worksheet.write( 'A10', "internal:'Standard colors'!A1", 'Standard colors', format3 ) worksheet.write( 'A11', "internal:'Numeric formats'!A1", 'Numeric formats', format3 ) worksheet.write('A12', "internal:Borders!A1", 'Borders', format3) worksheet.write('A13', "internal:Patterns!A1", 'Patterns', format3) worksheet.write('A14', "internal:Alignment!A1", 'Alignment', format3) worksheet.write('A15', "internal:Miscellaneous!A1", 'Miscellaneous', format3) end ###################################################################### # # Demonstrate the named colors. # def named_colors(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Named colors') worksheet.set_column(0, 3, 15) worksheet.write(0, 0, "Index", heading) worksheet.write(0, 1, "Index", heading) worksheet.write(0, 2, "Name", heading) worksheet.write(0, 3, "Color", heading) i = 1 [33, 11, 53, 17, 22, 18, 13, 16, 23, 9, 12, 15, 14, 20, 8, 10].each do |index| color = colors[index] format = workbook.add_format( :bg_color => color, :pattern => 1, :border => 1 ) worksheet.write(i + 1, 0, index, center) worksheet.write(i + 1, 1, sprintf("0x%02X", index), center) worksheet.write(i + 1, 2, color, center) worksheet.write(i + 1, 3, '', format) i += 1 end end ###################################################################### # # Demonstrate the standard Excel colors in the range 8..63. # def standard_colors(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Standard colors') worksheet.set_column(0, 3, 15) worksheet.write(0, 0, "Index", heading) worksheet.write(0, 1, "Index", heading) worksheet.write(0, 2, "Color", heading) worksheet.write(0, 3, "Name", heading) (8 .. 63).each do |i| format = workbook.add_format( :bg_color => i, :pattern => 1, :border => 1 ) worksheet.write((i - 7), 0, i, center) worksheet.write((i - 7), 1, sprintf("0x%02X", i), center) worksheet.write((i - 7), 2, '', format) # Add the color names if colors[i] worksheet.write((i - 7), 3, colors[i], center) end end end ###################################################################### # # Demonstrate the standard numeric formats. # def numeric_formats(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Numeric formats') worksheet.set_column(0, 4, 15) worksheet.set_column(5, 5, 45) worksheet.write(0, 0, "Index", heading) worksheet.write(0, 1, "Index", heading) worksheet.write(0, 2, "Unformatted", heading) worksheet.write(0, 3, "Formatted", heading) worksheet.write(0, 4, "Negative", heading) worksheet.write(0, 5, "Format", heading) formats = [] formats << [ 0x00, 1234.567, 0, 'General' ] formats << [ 0x01, 1234.567, 0, '0' ] formats << [ 0x02, 1234.567, 0, '0.00' ] formats << [ 0x03, 1234.567, 0, '#,##0' ] formats << [ 0x04, 1234.567, 0, '#,##0.00' ] formats << [ 0x05, 1234.567, -1234.567, '($#,##0_);($#,##0)' ] formats << [ 0x06, 1234.567, -1234.567, '($#,##0_);[Red]($#,##0)' ] formats << [ 0x07, 1234.567, -1234.567, '($#,##0.00_);($#,##0.00)' ] formats << [ 0x08, 1234.567, -1234.567, '($#,##0.00_);[Red]($#,##0.00)' ] formats << [ 0x09, 0.567, 0, '0%' ] formats << [ 0x0a, 0.567, 0, '0.00%' ] formats << [ 0x0b, 1234.567, 0, '0.00E+00' ] formats << [ 0x0c, 0.75, 0, '# ?/?' ] formats << [ 0x0d, 0.3125, 0, '# ??/??' ] formats << [ 0x0e, 36892.521, 0, 'm/d/yy' ] formats << [ 0x0f, 36892.521, 0, 'd-mmm-yy' ] formats << [ 0x10, 36892.521, 0, 'd-mmm' ] formats << [ 0x11, 36892.521, 0, 'mmm-yy' ] formats << [ 0x12, 36892.521, 0, 'h:mm AM/PM' ] formats << [ 0x13, 36892.521, 0, 'h:mm:ss AM/PM' ] formats << [ 0x14, 36892.521, 0, 'h:mm' ] formats << [ 0x15, 36892.521, 0, 'h:mm:ss' ] formats << [ 0x16, 36892.521, 0, 'm/d/yy h:mm' ] formats << [ 0x25, 1234.567, -1234.567, '(#,##0_);(#,##0)' ] formats << [ 0x26, 1234.567, -1234.567, '(#,##0_);[Red](#,##0)' ] formats << [ 0x27, 1234.567, -1234.567, '(#,##0.00_);(#,##0.00)' ] formats << [ 0x28, 1234.567, -1234.567, '(#,##0.00_);[Red](#,##0.00)' ] formats << [ 0x29, 1234.567, -1234.567, '_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)' ] formats << [ 0x2a, 1234.567, -1234.567, '_($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)' ] formats << [ 0x2b, 1234.567, -1234.567, '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)' ] formats << [ 0x2c, 1234.567, -1234.567, '_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)' ] formats << [ 0x2d, 36892.521, 0, 'mm:ss' ] formats << [ 0x2e, 3.0153, 0, '[h]:mm:ss' ] formats << [ 0x2f, 36892.521, 0, 'mm:ss.0' ] formats << [ 0x30, 1234.567, 0, '##0.0E+0' ] formats << [ 0x31, 1234.567, 0, '@' ] i = 0 formats.each do |format| style = workbook.add_format style.set_num_format(format[0]) i += 1 worksheet.write(i, 0, format[0], center) worksheet.write(i, 1, sprintf("0x%02X", format[0]), center) worksheet.write(i, 2, format[1], center) worksheet.write(i, 3, format[1], style) if format[2] != 0 worksheet.write(i, 4, format[2], style) end worksheet.write_string(i, 5, format[3]) end end ###################################################################### # # Demonstrate the font options. # def fonts(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Fonts') worksheet.set_column(0, 0, 30) worksheet.set_column(1, 1, 10) worksheet.write(0, 0, "Font name", heading) worksheet.write(0, 1, "Font size", heading) fonts = [] fonts << [ 10, 'Arial' ] fonts << [ 12, 'Arial' ] fonts << [ 14, 'Arial' ] fonts << [ 12, 'Arial Black' ] fonts << [ 12, 'Arial Narrow' ] fonts << [ 12, 'Century Schoolbook' ] fonts << [ 12, 'Courier' ] fonts << [ 12, 'Courier New' ] fonts << [ 12, 'Garamond' ] fonts << [ 12, 'Impact' ] fonts << [ 12, 'Lucida Handwriting' ] fonts << [ 12, 'Times New Roman' ] fonts << [ 12, 'Symbol' ] fonts << [ 12, 'Wingdings' ] fonts << [ 12, 'A font that doesn\'t exist' ] i = 0 fonts.each do |font| format = workbook.add_format format.set_size(font[0]) format.set_font(font[1]) i += 1 worksheet.write(i, 0, font[1], format) worksheet.write(i, 1, font[0], format) end end ###################################################################### # # Demonstrate the standard Excel border styles. # def borders(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Borders') worksheet.set_column(0, 4, 10) worksheet.set_column(5, 5, 40) worksheet.write(0, 0, "Index", heading) worksheet.write(0, 1, "Index", heading) worksheet.write(0, 3, "Style", heading) worksheet.write(0, 5, "The style is highlighted in red for ", heading) worksheet.write(1, 5, "emphasis, the default color is black.", heading) (0 .. 13).each do |i| format = workbook.add_format format.set_border(i) format.set_border_color('red') format.set_align('center') worksheet.write((2 * (i + 1)), 0, i, center) worksheet.write((2 * (i + 1)), 1, sprintf("0x%02X", i), center) worksheet.write((2 * (i + 1)), 3, "Border", format) end worksheet.write(30, 0, "Diag type", heading) worksheet.write(30, 1, "Index", heading) worksheet.write(30, 3, "Style", heading) worksheet.write(30, 5, "Diagonal Boder styles", heading) (1 .. 3).each do |i| format = workbook.add_format format.set_diag_type(i) format.set_diag_border(1) format.set_diag_color('red') format.set_align('center') worksheet.write((2 * (i + 15)), 0, i, center) worksheet.write((2 * (i + 15)), 1, sprintf("0x%02X", i), center) worksheet.write((2 * (i + 15)), 3, "Border", format) end end ###################################################################### # # Demonstrate the standard Excel cell patterns. # def patterns(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Patterns') worksheet.set_column(0, 4, 10) worksheet.set_column(5, 5, 50) worksheet.write(0, 0, "Index", heading) worksheet.write(0, 1, "Index", heading) worksheet.write(0, 3, "Pattern", heading) worksheet.write(0, 5, "The background colour has been set to silver.", heading) worksheet.write(1, 5, "The foreground colour has been set to green.", heading) (0 .. 18).each do |i| format = workbook.add_format format.set_pattern(i) format.set_bg_color('silver') format.set_fg_color('green') format.set_align('center') worksheet.write((2 * (i + 1)), 0, i, center) worksheet.write((2 * (i + 1)), 1, sprintf("0x%02X", i), center) worksheet.write((2 * (i + 1)), 3, "Pattern", format) if i == 1 worksheet.write((2 * (i + 1)), 5, "This is solid colour, the most useful pattern.", heading) end end end ###################################################################### # # Demonstrate the standard Excel cell alignments. # def alignment(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Alignment') worksheet.set_column(0, 7, 12) worksheet.set_row(0, 40) worksheet.set_selection(7, 0) format01 = workbook.add_format format02 = workbook.add_format format03 = workbook.add_format format04 = workbook.add_format format05 = workbook.add_format format06 = workbook.add_format format07 = workbook.add_format format08 = workbook.add_format format09 = workbook.add_format format10 = workbook.add_format format11 = workbook.add_format format12 = workbook.add_format format13 = workbook.add_format format14 = workbook.add_format format15 = workbook.add_format format16 = workbook.add_format format17 = workbook.add_format format02.set_align('top') format03.set_align('bottom') format04.set_align('vcenter') format05.set_align('vjustify') format06.set_text_wrap format07.set_align('left') format08.set_align('right') format09.set_align('center') format10.set_align('fill') format11.set_align('justify') format12.set_merge format13.set_rotation(45) format14.set_rotation(-45) format15.set_rotation(270) format16.set_shrink format17.set_indent(1) worksheet.write(0, 0, 'Vertical', heading) worksheet.write(0, 1, 'top', format02) worksheet.write(0, 2, 'bottom', format03) worksheet.write(0, 3, 'vcenter', format04) worksheet.write(0, 4, 'vjustify', format05) worksheet.write(0, 5, "text\nwrap", format06) worksheet.write(2, 0, 'Horizontal', heading) worksheet.write(2, 1, 'left', format07) worksheet.write(2, 2, 'right', format08) worksheet.write(2, 3, 'center', format09) worksheet.write(2, 4, 'fill', format10) worksheet.write(2, 5, 'justify', format11) worksheet.write(3, 1, 'merge', format12) worksheet.write(3, 2, '', format12) worksheet.write(3, 3, 'Shrink ' * 3, format16) worksheet.write(3, 4, 'Indent', format17) worksheet.write(5, 0, 'Rotation', heading) worksheet.write(5, 1, 'Rotate 45', format13) worksheet.write(6, 1, 'Rotate -45', format14) worksheet.write(7, 1, 'Rotate 270', format15) end ###################################################################### # # Demonstrate other miscellaneous features. # def misc(workbook, center, heading, colors) worksheet = workbook.add_worksheet('Miscellaneous') worksheet.set_column(2, 2, 25) format01 = workbook.add_format format02 = workbook.add_format format03 = workbook.add_format format04 = workbook.add_format format05 = workbook.add_format format06 = workbook.add_format format07 = workbook.add_format format01.set_underline(0x01) format02.set_underline(0x02) format03.set_underline(0x21) format04.set_underline(0x22) format05.set_font_strikeout format06.set_font_outline format07.set_font_shadow worksheet.write(1, 2, 'Underline 0x01', format01) worksheet.write(3, 2, 'Underline 0x02', format02) worksheet.write(5, 2, 'Underline 0x21', format03) worksheet.write(7, 2, 'Underline 0x22', format04) worksheet.write(9, 2, 'Strikeout', format05) worksheet.write(11, 2, 'Outline (Macintosh only)', format06) worksheet.write(13, 2, 'Shadow (Macintosh only)', format07) end # Call these subroutines to demonstrate different formatting options intro(workbook, center, heading, colors) fonts(workbook, center, heading, colors) named_colors(workbook, center, heading, colors) standard_colors(workbook, center, heading, colors) numeric_formats(workbook, center, heading, colors) borders(workbook, center, heading, colors) patterns(workbook, center, heading, colors) alignment(workbook, center, heading, colors) misc(workbook, center, heading, colors) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_headers @xlsx = 'headers.xlsx' workbook = WriteXLSX.new(@xlsx) preview = 'Select Print Preview to see the header and footer' ###################################################################### # # A simple example to start # worksheet1 = workbook.add_worksheet('Simple') header1 = '&CHere is some centred text.' footer1 = '&LHere is some left aligned text.' worksheet1.set_header(header1) worksheet1.set_footer(footer1) worksheet1.set_column('A:A', 50) worksheet1.write('A1', preview) ###################################################################### # # This is an example of some of the header/footer variables. # worksheet2 = workbook.add_worksheet('Variables') header2 = '&LPage &P of &N' + '&CFilename: &F' + '&RSheetname: &A' footer2 = '&LCurrent date: &D' + '&RCurrent time: &T' worksheet2.set_header(header2) worksheet2.set_footer(footer2) worksheet2.set_column('A:A', 50) worksheet2.write('A1', preview) worksheet2.write('A21', 'Next sheet') worksheet2.set_h_pagebreaks(20) ###################################################################### # # This example shows how to use more than one font # worksheet3 = workbook.add_worksheet('Mixed fonts') header3 = %q(&C&"Courier New,Bold"Hello &"Arial,Italic"World) footer3 = %q(&C&"Symbol"e&"Arial" = mc&X2) worksheet3.set_header(header3) worksheet3.set_footer(footer3) worksheet3.set_column('A:A', 50) worksheet3.write('A1', preview) ###################################################################### # # Example of line wrapping # worksheet4 = workbook.add_worksheet('Word wrap') header4 = "&CHeading 1\nHeading 2" worksheet4.set_header(header4) worksheet4.set_column('A:A', 50) worksheet4.write('A1', preview) ###################################################################### # # Example of inserting a literal ampersand & # worksheet5 = workbook.add_worksheet('Ampersand') header5 = '&CCuriouser && Curiouser - Attorneys at Law' worksheet5.set_header(header5) worksheet5.set_column('A:A', 50) worksheet5.write('A1', preview) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_hide_sheet @xlsx = 'hide_sheet.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet worksheet2 = workbook.add_worksheet worksheet3 = workbook.add_worksheet worksheet1.set_column('A:A', 30) worksheet2.set_column('A:A', 30) worksheet3.set_column('A:A', 30) # Sheet2 won't be visible until it is unhidden in Excel. worksheet2.hide worksheet1.write(0, 0, 'Sheet2 is hidden') worksheet2.write(0, 0, "Now it's my turn to find you.") worksheet3.write(0, 0, 'Sheet2 is hidden') workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_hyperlink @xlsx = 'hyperlink.xlsx' # Create a new workbook and add a worksheet workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet('Hyperlinks') # Format the first column worksheet.set_column('A:A', 30) worksheet.set_selection('B1') # Add the standard url link format. url_format = workbook.add_format( :color => 'blue', :underline => 1 ) # Add a sample format. red_format = workbook.add_format( :color => 'red', :bold => 1, :underline => 1, :size => 12 ) # Add an alternate description string to the URL. str = 'Perl home.' # Add a "tool tip" to the URL. tip = 'Get the latest Perl news here.' # Write some hyperlinks worksheet.write('A1', 'http://www.perl.com/', url_format) worksheet.write('A3', 'http://www.perl.com/', url_format, str) worksheet.write('A5', 'http://www.perl.com/', url_format, str, tip) worksheet.write('A7', 'http://www.perl.com/', red_format) worksheet.write('A9', 'mailto:jmcnamara@cpan.org', url_format, 'Mail me') # Write a URL that isn't a hyperlink worksheet.write_string('A11', 'http://www.perl.com/') workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_indent @xlsx = 'indent.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet indent1 = workbook.add_format(:indent => 1) indent2 = workbook.add_format(:indent => 2) worksheet.set_column('A:A', 40) worksheet.write('A1', "This text is indented 1 level", indent1) worksheet.write('A2', "This text is indented 2 levels", indent2) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_merge1 @xlsx = 'merge1.xlsx' # Create a new workbook and add a worksheet workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Increase the cell size of the merged cells to highlight the formatting. worksheet.set_column('B:D', 20) worksheet.set_row(2, 30) # Create a merge format format = workbook.add_format(:center_across => 1) # Only one cell should contain text, the others should be blank. worksheet.write(2, 1, "Center across selection", format) worksheet.write_blank(2, 2, format) worksheet.write_blank(2, 3, format) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_merge2 # Create a new workbook and add a worksheet @xlsx = 'merge2.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Increase the cell size of the merged cells to highlight the formatting. worksheet.set_column(1, 2, 30) worksheet.set_row(2, 40) # Create a merged format format = workbook.add_format( :center_across => 1, :bold => 1, :size => 15, :pattern => 1, :border => 6, :color => 'white', :fg_color => 'green', :border_color => 'yellow', :align => 'vcenter' ) # Only one cell should contain text, the others should be blank. worksheet.write(2, 1, "Center across selection", format) worksheet.write_blank(2, 2, format) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_merge3 @xlsx = 'merge3.xlsx' # Create a new workbook and add a worksheet workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet() # Increase the cell size of the merged cells to highlight the formatting. [3, 6, 7].each { |row| worksheet.set_row(row, 30) } worksheet.set_column('B:D', 20) ############################################################################### # # Example: Merge cells containing a hyperlink using merge_range(). # format = workbook.add_format( :border => 1, :underline => 1, :color => 'blue', :align => 'center', :valign => 'vcenter' ) # Merge 3 cells worksheet.merge_range('B4:D4', 'http://www.perl.com', format) # Merge 3 cells over two rows worksheet.merge_range('B7:D8', 'http://www.perl.com', format) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_merge4 @xlsx = 'merge4.xlsx' # Create a new workbook and add a worksheet workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Increase the cell size of the merged cells to highlight the formatting. (1 .. 11).each { |i| worksheet.set_row(i, 30) } worksheet.set_column('B:D', 20) ############################################################################### # # Example 1: Text centered vertically and horizontally # format1 = workbook.add_format( :border => 6, :bold => 1, :color => 'red', :valign => 'vcenter', :align => 'center' ) worksheet.merge_range('B2:D3', 'Vertical and horizontal', format1) ############################################################################### # # Example 2: Text aligned to the top and left # format2 = workbook.add_format( :border => 6, :bold => 1, :color => 'red', :valign => 'top', :align => 'left' ) worksheet.merge_range('B5:D6', 'Aligned to the top and left', format2) ############################################################################### # # Example 3: Text aligned to the bottom and right # format3 = workbook.add_format( :border => 6, :bold => 1, :color => 'red', :valign => 'bottom', :align => 'right' ) worksheet.merge_range('B8:D9', 'Aligned to the bottom and right', format3) ############################################################################### # # Example 4: Text justified (i.e. wrapped) in the cell # format4 = workbook.add_format( :border => 6, :bold => 1, :color => 'red', :valign => 'top', :align => 'justify' ) worksheet.merge_range('B11:D12', 'Justified: ' << 'so on and ' * 18, format4) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_merge5 @xlsx = 'merge5.xlsx' # Create a new workbook and add a worksheet workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Increase the cell size of the merged cells to highlight the formatting. (3 .. 8).each { |row| worksheet.set_row(row, 36 ) } [1, 3, 5].each { |col| worksheet.set_column( col, col, 15 ) } ############################################################################### # # Rotation 1, letters run from top to bottom # format1 = workbook.add_format( :border => 6, :bold => 1, :color => 'red', :valign => 'vcentre', :align => 'centre', :rotation => 270 ) worksheet.merge_range( 'B4:B9', 'Rotation 270', format1 ) ############################################################################### # # Rotation 2, 90ｰ anticlockwise # format2 = workbook.add_format( :border => 6, :bold => 1, :color => 'red', :valign => 'vcentre', :align => 'centre', :rotation => 90 ) worksheet.merge_range( 'D4:D9', 'Rotation 90°', format2 ) ############################################################################### # # Rotation 3, 90ｰ clockwise # format3 = workbook.add_format( :border => 6, :bold => 1, :color => 'red', :valign => 'vcentre', :align => 'centre', :rotation => -90 ) worksheet.merge_range( 'F4:F9', 'Rotation -90°', format3 ) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_merge6 @xlsx = 'merge6.xlsx' # Create a new workbook and add a worksheet workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Increase the cell size of the merged cells to highlight the formatting. (2 .. 9).each { |row| worksheet.set_row(row, 36) } worksheet.set_column('B:D', 25) # Format for the merged cells. format = workbook.add_format( :border => 6, :bold => 1, :color => 'red', :size => 20, :valign => 'vcentre', :align => 'left', :indent => 1 ) ############################################################################### # # Write an Ascii string. # worksheet.merge_range('B3:D4', 'ASCII: A simple string', format) ############################################################################### # # Write a UTF-8 Unicode string. # smiley = '☺' worksheet.merge_range('B6:D7', "UTF-8: A Unicode smiley #{smiley}", format) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_outline @xlsx = 'outline.xlsx' # Create a new workbook and add some worksheets workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet('Outlined Rows') worksheet2 = workbook.add_worksheet('Collapsed Rows') worksheet3 = workbook.add_worksheet('Outline Columns') worksheet4 = workbook.add_worksheet('Outline levels') # Add a general format bold = workbook.add_format(:bold => 1) ############################################################################### # # Example 1: Create a worksheet with outlined rows. It also includes SUBTOTAL() # functions so that it looks like the type of automatic outlines that are # generated when you use the Excel Data.SubTotals menu item. # # For outlines the important parameters are $hidden and $level. Rows with the # same $level are grouped together. The group will be collapsed if $hidden is # non-zero. $height and $XF are assigned default values if they are nil. # # The syntax is: set_row($row, $height, $XF, $hidden, $level, $collapsed) # worksheet1.set_row(1, nil, nil, 0, 2) worksheet1.set_row(2, nil, nil, 0, 2) worksheet1.set_row(3, nil, nil, 0, 2) worksheet1.set_row(4, nil, nil, 0, 2) worksheet1.set_row(5, nil, nil, 0, 1) worksheet1.set_row(6, nil, nil, 0, 2) worksheet1.set_row(7, nil, nil, 0, 2) worksheet1.set_row(8, nil, nil, 0, 2) worksheet1.set_row(9, nil, nil, 0, 2) worksheet1.set_row(10, nil, nil, 0, 1) # Add a column format for clarity worksheet1.set_column('A:A', 20) # Add the data, labels and formulas worksheet1.write('A1', 'Region', bold) worksheet1.write('A2', 'North') worksheet1.write('A3', 'North') worksheet1.write('A4', 'North') worksheet1.write('A5', 'North') worksheet1.write('A6', 'North Total', bold) worksheet1.write('B1', 'Sales', bold) worksheet1.write('B2', 1000) worksheet1.write('B3', 1200) worksheet1.write('B4', 900) worksheet1.write('B5', 1200) worksheet1.write('B6', '=SUBTOTAL(9,B2:B5)', bold) worksheet1.write('A7', 'South') worksheet1.write('A8', 'South') worksheet1.write('A9', 'South') worksheet1.write('A10', 'South') worksheet1.write('A11', 'South Total', bold) worksheet1.write('B7', 400) worksheet1.write('B8', 600) worksheet1.write('B9', 500) worksheet1.write('B10', 600) worksheet1.write('B11', '=SUBTOTAL(9,B7:B10)', bold) worksheet1.write('A12', 'Grand Total', bold) worksheet1.write('B12', '=SUBTOTAL(9,B2:B10)', bold) ############################################################################### # # Example 2: Create a worksheet with outlined rows. This is the same as the # previous example except that the rows are collapsed. # Note: We need to indicate the row that contains the collapsed symbol '+' # with the optional parameter, $collapsed. # The group will be collapsed if $hidden is non-zero. # The syntax is: set_row($row, $height, $XF, $hidden, $level, $collapsed) # worksheet2.set_row(1, nil, nil, 1, 2) worksheet2.set_row(2, nil, nil, 1, 2) worksheet2.set_row(3, nil, nil, 1, 2) worksheet2.set_row(4, nil, nil, 1, 2) worksheet2.set_row(5, nil, nil, 1, 1) worksheet2.set_row(6, nil, nil, 1, 2) worksheet2.set_row(7, nil, nil, 1, 2) worksheet2.set_row(8, nil, nil, 1, 2) worksheet2.set_row(9, nil, nil, 1, 2) worksheet2.set_row(10, nil, nil, 1, 1) worksheet2.set_row(11, nil, nil, 0, 0, 1) # Add a column format for clarity worksheet2.set_column('A:A', 20) # Add the data, labels and formulas worksheet2.write('A1', 'Region', bold) worksheet2.write('A2', 'North') worksheet2.write('A3', 'North') worksheet2.write('A4', 'North') worksheet2.write('A5', 'North') worksheet2.write('A6', 'North Total', bold) worksheet2.write('B1', 'Sales', bold) worksheet2.write('B2', 1000) worksheet2.write('B3', 1200) worksheet2.write('B4', 900) worksheet2.write('B5', 1200) worksheet2.write('B6', '=SUBTOTAL(9,B2:B5)', bold) worksheet2.write('A7', 'South') worksheet2.write('A8', 'South') worksheet2.write('A9', 'South') worksheet2.write('A10', 'South') worksheet2.write('A11', 'South Total', bold) worksheet2.write('B7', 400) worksheet2.write('B8', 600) worksheet2.write('B9', 500) worksheet2.write('B10', 600) worksheet2.write('B11', '=SUBTOTAL(9,B7:B10)', bold) worksheet2.write('A12', 'Grand Total', bold) worksheet2.write('B12', '=SUBTOTAL(9,B2:B10)', bold) ############################################################################### # # Example 3: Create a worksheet with outlined columns. # data = [ [ 'Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', ' Total' ], [ 'North', 50, 20, 15, 25, 65, 80, '=SUM(B2:G2)' ], [ 'South', 10, 20, 30, 50, 50, 50, '=SUM(B3:G3)' ], [ 'East', 45, 75, 50, 15, 75, 100, '=SUM(B4:G4)' ], [ 'West', 15, 15, 55, 35, 20, 50, '=SUM(B5:G5)' ], ] # Add bold format to the first row worksheet3.set_row(0, nil, bold) # Syntax: set_column($col1, $col2, $width, $XF, $hidden, $level, $collapsed) worksheet3.set_column('A:A', 10, bold) worksheet3.set_column('B:G', 5, nil, 0, 1) worksheet3.set_column('H:H', 10) # Write the data and a formula worksheet3.write_col('A1', data) worksheet3.write('H6', '=SUM(H2:H5)', bold) ############################################################################### # # Example 4: Show all possible outline levels. # levels = [ "Level 1", "Level 2", "Level 3", "Level 4", "Level 5", "Level 6", "Level 7", "Level 6", "Level 5", "Level 4", "Level 3", "Level 2", "Level 1" ] worksheet4.write_col('A1', levels) worksheet4.set_row(0, nil, nil, nil, 1) worksheet4.set_row(1, nil, nil, nil, 2) worksheet4.set_row(2, nil, nil, nil, 3) worksheet4.set_row(3, nil, nil, nil, 4) worksheet4.set_row(4, nil, nil, nil, 5) worksheet4.set_row(5, nil, nil, nil, 6) worksheet4.set_row(6, nil, nil, nil, 7) worksheet4.set_row(7, nil, nil, nil, 6) worksheet4.set_row(8, nil, nil, nil, 5) worksheet4.set_row(9, nil, nil, nil, 4) worksheet4.set_row(10, nil, nil, nil, 3) worksheet4.set_row(11, nil, nil, nil, 2) worksheet4.set_row(12, nil, nil, nil, 1) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end =begin def test_outline_collapsed @xlsx = 'outline_collapsed.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet('Outlined Rows') worksheet2 = workbook.add_worksheet('Collapsed Rows 1') worksheet3 = workbook.add_worksheet('Collapsed Rows 2') worksheet4 = workbook.add_worksheet('Collapsed Rows 3') worksheet5 = workbook.add_worksheet('Outline Columns') worksheet6 = workbook.add_worksheet('Collapsed Columns') # Add a general format bold = workbook.add_format(:bold => 1) ############################################################################### # # Example 1: Create a worksheet with outlined rows. It also includes SUBTOTAL() # functions so that it looks like the type of automatic outlines that are # generated when you use the Excel Data->SubTotals menu item. # # The syntax is: set_row(row, height, XF, hidden, level, collapsed) worksheet1.set_row(1, nil, nil, 0, 2) worksheet1.set_row(2, nil, nil, 0, 2) worksheet1.set_row(3, nil, nil, 0, 2) worksheet1.set_row(4, nil, nil, 0, 2) worksheet1.set_row(5, nil, nil, 0, 1) worksheet1.set_row(6, nil, nil, 0, 2) worksheet1.set_row(7, nil, nil, 0, 2) worksheet1.set_row(8, nil, nil, 0, 2) worksheet1.set_row(9, nil, nil, 0, 2) worksheet1.set_row(10, nil, nil, 0, 1) # Write the sub-total data that is common to the row examples. create_sub_totals(worksheet1, bold) ############################################################################### # # Example 2: Create a worksheet with collapsed outlined rows. # This is the same as the example 1 except that the all rows are collapsed. # Note: We need to indicate the row that contains the collapsed symbol '+' with # the optional parameter, collapsed. worksheet2.set_row(1, nil, nil, 1, 2) worksheet2.set_row(2, nil, nil, 1, 2) worksheet2.set_row(3, nil, nil, 1, 2) worksheet2.set_row(4, nil, nil, 1, 2) worksheet2.set_row(5, nil, nil, 1, 1) worksheet2.set_row(6, nil, nil, 1, 2) worksheet2.set_row(7, nil, nil, 1, 2) worksheet2.set_row(8, nil, nil, 1, 2) worksheet2.set_row(9, nil, nil, 1, 2) worksheet2.set_row(10, nil, nil, 1, 1) worksheet2.set_row(11, nil, nil, 0, 0, 1) # Write the sub-total data that is common to the row examples. create_sub_totals(worksheet2, bold) ############################################################################### # # Example 3: Create a worksheet with collapsed outlined rows. # Same as the example 1 except that the two sub-totals are collapsed. worksheet3.set_row(1, nil, nil, 1, 2) worksheet3.set_row(2, nil, nil, 1, 2) worksheet3.set_row(3, nil, nil, 1, 2) worksheet3.set_row(4, nil, nil, 1, 2) worksheet3.set_row(5, nil, nil, 0, 1, 1) worksheet3.set_row(6, nil, nil, 1, 2) worksheet3.set_row(7, nil, nil, 1, 2) worksheet3.set_row(8, nil, nil, 1, 2) worksheet3.set_row(9, nil, nil, 1, 2) worksheet3.set_row(10, nil, nil, 0, 1, 1) # Write the sub-total data that is common to the row examples. create_sub_totals(worksheet3, bold) ############################################################################### # # Example 4: Create a worksheet with outlined rows. # Same as the example 1 except that the two sub-totals are collapsed. worksheet4.set_row(1, nil, nil, 1, 2) worksheet4.set_row(2, nil, nil, 1, 2) worksheet4.set_row(3, nil, nil, 1, 2) worksheet4.set_row(4, nil, nil, 1, 2) worksheet4.set_row(5, nil, nil, 1, 1, 1) worksheet4.set_row(6, nil, nil, 1, 2) worksheet4.set_row(7, nil, nil, 1, 2) worksheet4.set_row(8, nil, nil, 1, 2) worksheet4.set_row(9, nil, nil, 1, 2) worksheet4.set_row(10, nil, nil, 1, 1, 1) worksheet4.set_row(11, nil, nil, 0, 0, 1) # Write the sub-total data that is common to the row examples. create_sub_totals(worksheet4, bold) ############################################################################### # # Example 5: Create a worksheet with outlined columns. # data = [ [ 'Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Total' ], [ 'North', 50, 20, 15, 25, 65, 80, '=SUM(B2:G2)' ], [ 'South', 10, 20, 30, 50, 50, 50, '=SUM(B3:G3)' ], [ 'East', 45, 75, 50, 15, 75, 100, '=SUM(B4:G4)' ], [ 'West', 15, 15, 55, 35, 20, 50, '=SUM(B5:G6)' ] ] # Add bold format to the first row worksheet5.set_row(0, nil, bold) # Syntax: set_column(col1, col2, width, XF, hidden, level, collapsed) worksheet5.set_column('A:A', 10, bold) worksheet5.set_column('B:G', 5, nil, 0, 1) worksheet5.set_column('H:H', 10) # Write the data and a formula worksheet5.write_col('A1', data) worksheet5.write('H6', '=SUM(H2:H5)', bold) ############################################################################### # # Example 6: Create a worksheet with collapsed outlined columns. # This is the same as the previous example except collapsed columns. # Add bold format to the first row worksheet6.set_row(0, nil, bold) # Syntax: set_column(col1, col2, width, XF, hidden, level, collapsed) worksheet6.set_column('A:A', 10, bold) worksheet6.set_column('B:G', 5, nil, 1, 1) worksheet6.set_column('H:H', 10, nil, 0, 0, 1) # Write the data and a formula worksheet6.write_col('A1', data) worksheet6.write('H6', '=SUM(H2:H5)', bold) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end # # This function will generate the same data and sub-totals on each worksheet. # def create_sub_totals(worksheet, bold) # Add a column format for clarity worksheet.set_column('A:A', 20) # Add the data, labels and formulas worksheet.write('A1', 'Region', bold) worksheet.write('A2', 'North') worksheet.write('A3', 'North') worksheet.write('A4', 'North') worksheet.write('A5', 'North') worksheet.write('A6', 'North Total', bold) worksheet.write('B1', 'Sales', bold) worksheet.write('B2', 1000) worksheet.write('B3', 1200) worksheet.write('B4', 900) worksheet.write('B5', 1200) worksheet.write('B6', '=SUBTOTAL(9,B2:B5)', bold) worksheet.write('A7', 'South') worksheet.write('A8', 'South') worksheet.write('A9', 'South') worksheet.write('A10', 'South') worksheet.write('A11', 'South Total', bold) worksheet.write('B7', 400) worksheet.write('B8', 600) worksheet.write('B9', 500) worksheet.write('B10', 600) worksheet.write('B11', '=SUBTOTAL(9,B7:B10)', bold) worksheet.write('A12', 'Grand Total', bold) worksheet.write('B12', '=SUBTOTAL(9,B2:B10)', bold) end =end def test_panes @xlsx = 'panes.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet('Panes 1') worksheet2 = workbook.add_worksheet('Panes 2') worksheet3 = workbook.add_worksheet('Panes 3') worksheet4 = workbook.add_worksheet('Panes 4') # Freeze panes worksheet1.freeze_panes(1, 0) # 1 row worksheet2.freeze_panes(0, 1) # 1 column worksheet3.freeze_panes(1, 1) # 1 row and column # Split panes. # The divisions must be specified in terms of row and column dimensions. # The default row height is 15 and the default column width is 8.43 # worksheet4.split_panes(15, 8.43) # 1 row and column ####################################################################### # # Set up some formatting and text to highlight the panes # header = workbook.add_format( :align => 'center', :valign => 'vcenter', :fg_color => 0x2A ) center = workbook.add_format(:align => 'center') ####################################################################### # # Sheet 1 # worksheet1.set_column('A:I', 16) worksheet1.set_row(0, 20) worksheet1.set_selection('C3') (0..8).each { |i| worksheet1.write(0, i, 'Scroll down', header) } (1..100).each do |i| (0..8).each {|j| worksheet1.write(i, j, i + 1, center)} end ####################################################################### # # Sheet 2 # worksheet2.set_column('A:A', 16) worksheet2.set_selection('C3') (0..49).each do |i| worksheet2.set_row(i, 15) worksheet2.write(i, 0, 'Scroll right', header) end (0..49).each do |i| (1..25).each {|j| worksheet2.write(i, j, j, center)} end ####################################################################### # # Sheet 3 # worksheet3.set_column('A:Z', 16) worksheet3.set_selection('C3') worksheet3.write(0, 0, '', header) (1..25).each {|i| worksheet3.write(0, i, 'Scroll down', header)} (1..49).each {|i| worksheet3.write(i, 0, 'Scroll right', header)} (1..49).each do |i| (1..25).each {|j| worksheet3.write(i, j, j, center)} end ####################################################################### # # Sheet 4 # worksheet4.set_selection('C3') (1..25).each {|i| worksheet4.write(0, i, 'Scroll', center)} (1..49).each {|i| worksheet4.write(i, 0, 'Scroll', center)} (1..49).each do |i| (1..25).each {|j| worksheet4.write(i, j, j, center)} end workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_print_scale @xlsx = 'print_scale.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet worksheet2 = workbook.add_worksheet worksheet3 = workbook.add_worksheet worksheet1.write(0, 0, "print_scale(100)") worksheet1.print_scale = 100 worksheet2.write(0, 0, "print_scale(50)") worksheet2.print_scale = 50 worksheet3.write(0, 0, "print_scale(200)") worksheet3.print_scale = 200 workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_properties @xlsx = 'properties.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet workbook.set_properties( :title => 'This is an example spreadsheet', :subject => 'With document properties', :author => 'John McNamara', :manager => 'Dr. Heinz Doofenshmirtz', :company => 'of Wolves', :category => 'Example spreadsheets', :keywords => 'Sample, Example, Properties', :comments => 'Created with Perl and Excel::Writer::XLSX', :status => 'Quo' ) worksheet.set_column('A:A', 70) worksheet.write('A1', "Select 'Office Button -> Prepare -> Properties' to see the file properties.") workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_protection @xlsx = 'protection.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Create some format objects unlocked = workbook.add_format(:locked => 0) hidden = workbook.add_format(:hidden => 1) # Format the columns worksheet.set_column('A:A', 45) worksheet.set_selection('B3') # Protect the worksheet worksheet.protect # Examples of cell locking and hiding. worksheet.write('A1', 'Cell B1 is locked. It cannot be edited.') worksheet.write_formula('B1', '=1+2', nil, 3) # Locked by default. worksheet.write('A2', 'Cell B2 is unlocked. It can be edited.') worksheet.write_formula('B2', '=1+2', unlocked, 3) worksheet.write('A3', "Cell B3 is hidden. The formula isn't visible.") worksheet.write_formula('B3', '=1+2', hidden, 3) worksheet.write('A5', 'Use Menu->Tools->Protection->Unprotect Sheet') worksheet.write('A6', 'to remove the worksheet protection.') workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_regions @xlsx = 'regions.xlsx' workbook = WriteXLSX.new(@xlsx) # Add some worksheets north = workbook.add_worksheet('North') south = workbook.add_worksheet('South') east = workbook.add_worksheet('East') west = workbook.add_worksheet('West') # Add a Format format = workbook.add_format format.set_bold format.set_color('blue') # Add a caption to each worksheet workbook.sheets.each do |worksheet| worksheet.write(0, 0, 'Sales', format) end # Write some data north.write(0, 1, 200000) south.write(0, 1, 100000) east.write(0, 1, 150000) west.write(0, 1, 100000) # Set the active worksheet south.activate # Set the width of the first column south.set_column(0, 0, 20) # Set the active cell south.set_selection(0, 1) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_rich_strings @xlsx = 'rich_strings.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet worksheet.set_column('A:A', 30) # Set some formats to use. bold = workbook.add_format(:bold => 1) italic = workbook.add_format(:italic => 1) red = workbook.add_format(:color => 'red') blue = workbook.add_format(:color => 'blue') center = workbook.add_format(:align => 'center') superc = workbook.add_format(:font_script => 1) # Write some strings with multiple formats. worksheet.write_rich_string('A1', 'This is ', bold, 'bold', ' and this is ', italic, 'italic') worksheet.write_rich_string('A3', 'This is ', red, 'red', ' and this is ', blue, 'blue') worksheet.write_rich_string('A5', 'Some ', bold, 'bold text', ' centered', center) worksheet.write_rich_string('A7', italic, 'j = k', superc, '(n-1)', center) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_right_to_left @xlsx = 'right_to_left.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet worksheet2 = workbook.add_worksheet worksheet2.right_to_left worksheet1.write(0, 0, 'Hello') # A1, B1, C1, ... worksheet2.write(0, 0, 'Hello') # ..., C1, B1, A1 workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape1 @xlsx = 'shape1.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Add a circle, with centered text. ellipse = workbook.add_shape( :type => 'ellipse', :text => "Hello\nWorld", :width => 60, :height => 60 ) worksheet.insert_shape('A1', ellipse, 50, 50) # Add a plus sign. plus = workbook.add_shape(:type => 'plus', :width => 20, :height => 20) worksheet.insert_shape('D8', plus) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape2 @xlsx = 'shape2.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet worksheet.hide_gridlines(2) plain = workbook.add_shape( :type => 'smileyFace', :text => "Plain", :width => 100, :height => 100 ) bbformat = workbook.add_format( :color => 'red', :font => 'Lucida Calligraphy' ) bbformat.set_bold bbformat.set_underline bbformat.set_italic decor = workbook.add_shape( :type => 'smileyFace', :text => 'Decorated', :rotation => 45, :width => 200, :height => 100, :format => bbformat, :line_type => 'sysDot', :line_weight => 3, :fill => 'FFFF00', :line => '3366FF' ) worksheet.insert_shape('A1', plain, 50, 50) worksheet.insert_shape('A1', decor, 250, 50) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape3 @xlsx = 'shape3.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet normal = workbook.add_shape( :name => 'chip', :type => 'diamond', :text => 'Normal', :width => 100, :height => 100 ) worksheet.insert_shape('A1', normal, 50, 50) normal[:text] = 'Scaled 3w x 2h' normal[:name] = 'Hope' worksheet.insert_shape('A1', normal, 250, 50, 3, 2) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape4 @xlsx = 'shape4.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet worksheet.hide_gridlines(2) type = 'rect' shape = workbook.add_shape( :type => type, :width => 90, :height => 90 ) (1..10).each do |n| # Change the last 5 rectangles to stars. Previously # inserted shapes stay as rectangles. type = 'star5' if n == 6 shape[:type] = type shape[:text] = "#{type} #{n}" worksheet.insert_shape('A1', shape, n * 100, 50) end stencil = workbook.add_shape( :stencil => 1, # The default. :width => 90, :height => 90, :text => 'started as a box' ) worksheet.insert_shape('A1', stencil, 100, 150) stencil[:stencil] = 0 worksheet.insert_shape('A1', stencil, 200, 150) worksheet.insert_shape('A1', stencil, 300, 150) # Ooopa! Changed my mind. # Change the rectangle to an ellipse (circle), # for the last two shapes. stencil[:type] = 'ellipse' stencil[:text] = 'Now its a circle' workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape5 @xlsx = 'shape5.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet s1 = workbook.add_shape( :type => 'ellipse', :width => 60, :height => 60 ) worksheet.insert_shape('A1', s1, 50, 50) s2 = workbook.add_shape( :type => 'plus', :width => 20, :height => 20 ) worksheet.insert_shape('A1', s2, 250, 200) # Create a connector to link the two shapes. cxn_shape = workbook.add_shape(:type => 'bentConnector3') # Link the start of the connector to the right side. cxn_shape[:start] = s1[:id] cxn_shape[:start_index] = 4 # 4th connection pt, clockwise from top(0). cxn_shape[:start_side] = 'b' # r)ight or b)ottom. # Link the end of the connector to the left side. cxn_shape[:end] = s2[:id] cxn_shape[:end_index] = 0 # clockwise from top(0). cxn_shape[:end_side] = 't' # t)op. worksheet.insert_shape('A1', cxn_shape, 0, 0) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape6 @xlsx = 'shape6.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet s1 = workbook.add_shape( :type => 'chevron', :width => 60, :height => 60 ) worksheet.insert_shape('A1', s1, 50, 50) s2 = workbook.add_shape( :type => 'pentagon', :width => 20, :height => 20 ) worksheet.insert_shape('A1', s2, 250, 200) # Create a connector to link the two shapes. cxn_shape = workbook.add_shape(:type => 'curvedConnector3') # Link the start of the connector to the right side. cxn_shape[:start] = s1[:id] cxn_shape[:start_index] = 2 # 2nd connection pt, clockwise from top(0). cxn_shape[:start_side] = 'r' # r)ight or b)ottom. # Link the end of the connector to the left side. cxn_shape[:end] = s2[:id] cxn_shape[:end_index] = 4 # 4th connection pt, clockwise from top(0). cxn_shape[:end_side] = 'l' # l)eft or t)op. worksheet.insert_shape('A1', cxn_shape, 0, 0) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape7 @xlsx = 'shape7.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Add a circle, with centered text. c is for circle, not center. cw = 60 ch = 60 cx = 210 cy = 190 ellipse = workbook.add_shape( :type => 'ellipse', :id => 2, :text => "Hello\nWorld", :width => cw, :height => ch ) worksheet.insert_shape('A1', ellipse, cx, cy) # Add a plus sign at 4 different positions around the circle. pw = 20 ph = 20 px = 120 py = 250 plus = workbook.add_shape( :type => 'plus', :id => 3, :width => pw, :height => ph ) p1 = worksheet.insert_shape('A1', plus, 350, 350) p2 = worksheet.insert_shape('A1', plus, 150, 350) p3 = worksheet.insert_shape('A1', plus, 350, 150) plus[:adjustments] = 35 # change shape of plus symbol. p4 = worksheet.insert_shape('A1', plus, 150, 150) cxn_shape = workbook.add_shape(:type => 'bentConnector3', :fill => 0) cxn_shape[:start] = ellipse[:id] cxn_shape[:start_index] = 4 # 4th connection pt, clockwise from top(0). cxn_shape[:start_side] = 'b' # r)ight or b)ottom. cxn_shape[:end] = p1[:id] cxn_shape[:end_index] = 0 cxn_shape[:end_side] = 't' # l)eft or t)op. worksheet.insert_shape('A1', cxn_shape, 0, 0) cxn_shape[:end] = p2[:id] worksheet.insert_shape('A1', cxn_shape, 0, 0) cxn_shape[:end] = p3[:id] worksheet.insert_shape('A1', cxn_shape, 0, 0) cxn_shape[:end] = p4[:id] cxn_shape[:adjustments] = [-50, 45, 120] worksheet.insert_shape('A1', cxn_shape, 0, 0) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape8 @xlsx = 'shape8.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Add a circle, with centered text. c is for circle, not center. cw = 60 ch = 60 cx = 210 cy = 190 ellipse = workbook.add_shape( :type => 'ellipse', :id => 2, :text => "Hello\nWorld", :width => cw, :height => ch ) worksheet.insert_shape('A1', ellipse, cx, cy) # Add a plus sign at 4 different positionos around the circle. pw = 20 ph = 20 px = 120 py = 250 plus = workbook.add_shape( :type => 'plus', :id => 3, :width => pw, :height => ph ) p1 = worksheet.insert_shape('A1', plus, 350, 150) p2 = worksheet.insert_shape('A1', plus, 350, 350) p3 = worksheet.insert_shape('A1', plus, 150, 350) p4 = worksheet.insert_shape('A1', plus, 150, 150) cxn_shape = workbook.add_shape(:type => 'bentConnector3', :fill => 0) cxn_shape[:start] = ellipse[:id] cxn_shape[:start_index] = 2 # 2nd connection pt, clockwise from top(0). cxn_shape[:start_side] = 'r' # r)ight or b)ottom. cxn_shape[:end] = p1[:id] cxn_shape[:end_index] = 3 # 3rd connection point on plus, right side cxn_shape[:end_side] = 'l' # l)eft or t)op. worksheet.insert_shape('A1', cxn_shape, 0, 0) cxn_shape[:end] = p2[:id] worksheet.insert_shape('A1', cxn_shape, 0, 0) cxn_shape[:end] = p3[:id] worksheet.insert_shape('A1', cxn_shape, 0, 0) cxn_shape[:end] = p4[:id] cxn_shape[:adjustments] = [-50, 45, 120] worksheet.insert_shape('A1', cxn_shape, 0, 0) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_shape_all @xlsx = 'shape_all.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = nil last_sheet = '' row = 0 shapes_list.each_line do |line| line = line.chomp next unless line =~ /^\w/ # Skip blank lines and comments. sheet, name = line.split(/\t/) if last_sheet != sheet worksheet = workbook.add_worksheet(sheet) row = 2 end last_sheet = sheet shape = workbook.add_shape( :type => name, :text => name, :width => 90, :height => 90 ) # Connectors can not have labels, so write the connector name in the cell # to the left. worksheet.write(row, 0, name ) if sheet == 'Connector' worksheet.insert_shape(row, 2, shape, 0, 0) row += 5 end workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def shapes_list < '$#,##0') # Some sample data for the table. data = [ [ 'Apples', 10000, 5000, 8000, 6000 ], [ 'Pears', 2000, 3000, 4000, 5000 ], [ 'Bananas', 6000, 6000, 6500, 6000 ], [ 'Oranges', 500, 300, 200, 700 ] ] ############################################################################### # # Example 1. # caption = 'Default table with no data.' # Set the columns widths. worksheet1.set_column('B:G', 12) # Write the caption. worksheet1.write('B1', caption) # Add a table to the worksheet. worksheet1.add_table('B3:F7') ############################################################################### # # Example 2. # caption = 'Default table with data.'; # Set the columns widths. worksheet2.set_column('B:G', 12) # Write the caption. worksheet2.write('B1', caption) # Add a table to the worksheet. worksheet2.add_table('B3:F7', { :data => data }) ############################################################################### # # Example 3. # caption = 'Table without default autofilter.' # Set the columns widths. worksheet3.set_column('B:G', 12) # Write the caption. worksheet3.write('B1', caption) # Add a table to the worksheet. worksheet3.add_table('B3:F7', { :autofilter => 0 }) # Table data can also be written separately, as an array or individual cells. worksheet3.write_col('B4', data) ############################################################################### # # Example 4. # caption = 'Table without default header row.' # Set the columns widths. worksheet4.set_column('B:G', 12) # Write the caption. worksheet4.write('B1', caption) # Add a table to the worksheet. worksheet4.add_table('B4:F7', { :header_row => 0 }) # Table data can also be written separately, as an array or individual cells. worksheet4.write_col('B4', data) ############################################################################### # # Example 5. # caption = 'Default table with "First Column" and "Last Column" options.' # Set the columns widths. worksheet5.set_column('B:G', 12) # Write the caption. worksheet5.write('B1', caption) # Add a table to the worksheet. worksheet5.add_table('B3:F7', { :first_column => 1, :last_column => 1 }) # Table data can also be written separately, as an array or individual cells. worksheet5.write_col('B4', data) ############################################################################### # # Example 6. # caption = 'Table with banded columns but without default banded rows.'; # Set the columns widths. worksheet6.set_column('B:G', 12) # Write the caption. worksheet6.write('B1', caption) # Add a table to the worksheet. worksheet6.add_table('B3:F7', { :banded_rows => 0, :banded_columns => 1 }) # Table data can also be written separately, as an array or individual cells. worksheet6.write_col('B4', data) ############################################################################### # # Example 7. # caption = 'Table with user defined column headers'; # Set the columns widths. worksheet7.set_column('B:G', 12) # Write the caption. worksheet7.write('B1', caption) # Add a table to the worksheet. worksheet7.add_table( 'B3:F7', { :data => data, :columns => [ { :header => 'Product' }, { :header => 'Quarter 1' }, { :header => 'Quarter 2' }, { :header => 'Quarter 3' }, { :header => 'Quarter 4' } ] } ) ############################################################################### # # Example 8. # caption = 'Table with user defined column headers'; # Set the columns widths. worksheet8.set_column('B:G', 12) # Write the caption. worksheet8.write('B1', caption) # Add a table to the worksheet. worksheet8.add_table( 'B3:G7', { :data => data, :columns => [ { :header => 'Product' }, { :header => 'Quarter 1' }, { :header => 'Quarter 2' }, { :header => 'Quarter 3' }, { :header => 'Quarter 4' }, { :header => 'Year', :formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])' } ] } ) ############################################################################### # # Example 9. # caption = 'Table with totals row (but no caption or totals).'; # Set the columns widths. worksheet9.set_column('B:G', 12) # Write the caption. worksheet9.write('B1', caption) # Add a table to the worksheet. worksheet9.add_table( 'B3:G8', { :data => data, :total_row => 1, :columns => [ { :header => 'Product' }, { :header => 'Quarter 1' }, { :header => 'Quarter 2' }, { :header => 'Quarter 3' }, { :header => 'Quarter 4' }, { :header => 'Year', :formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])' } ] } ) ############################################################################### # # Example 10. # caption = 'Table with totals row with user captions and functions.'; # Set the columns widths. worksheet10.set_column('B:G', 12) # Write the caption. worksheet10.write('B1', caption) # Add a table to the worksheet. worksheet10.add_table( 'B3:G8', { :data => data, :total_row => 1, :columns => [ { :header => 'Product', :total_string => 'Totals' }, { :header => 'Quarter 1', :total_function => 'sum' }, { :header => 'Quarter 2', :total_function => 'sum' }, { :header => 'Quarter 3', :total_function => 'sum' }, { :header => 'Quarter 4', :total_function => 'sum' }, { :header => 'Year', :formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])', :total_function => 'sum' } ] } ) ############################################################################### # # Example 11. # caption = 'Table with alternative Excel style.'; # Set the columns widths. worksheet11.set_column('B:G', 12) # Write the caption. worksheet11.write('B1', caption) # Add a table to the worksheet. worksheet11.add_table( 'B3:G8', { :data => data, :style => 'Table Style Light 11', :total_row => 1, :columns => [ { :header => 'Product', :total_string => 'Totals' }, { :header => 'Quarter 1', :total_function => 'sum' }, { :header => 'Quarter 2', :total_function => 'sum' }, { :header => 'Quarter 3', :total_function => 'sum' }, { :header => 'Quarter 4', :total_function => 'sum' }, { :header => 'Year', :formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])', :total_function => 'sum' } ] } ) ############################################################################### # # Example 12. # caption = 'Table with column formats.'; # Set the columns widths. worksheet12.set_column('B:G', 12) # Write the caption. worksheet12.write('B1', caption) # Add a table to the worksheet. worksheet12.add_table( 'B3:G8', { :data => data, :total_row => 1, :columns => [ { :header => 'Product', :total_string => 'Totals' }, { :header => 'Quarter 1', :total_function => 'sum', :format => currency_format, }, { :header => 'Quarter 2', :total_function => 'sum', :format => currency_format, }, { :header => 'Quarter 3', :total_function => 'sum', :format => currency_format, }, { :header => 'Quarter 4', :total_function => 'sum', :format => currency_format, }, { :header => 'Year', :formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])', :total_function => 'sum', :format => currency_format, } ] } ) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_sparklines1 @xlsx = 'sparklines1.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Some sample data to plot. data = [ [ -2, 2, 3, -1, 0 ], [ 30, 20, 33, 20, 15 ], [ 1, -1, -1, 1, -1 ] ] # Write the sample data to the worksheet. worksheet.write_col('A1', data) # Add a line sparkline (the default) with markers. worksheet.add_sparkline( { :location => 'F1', :range => 'Sheet1!A1:E1', :markers => 1 } ) # Add a column sparkline with non-default style. worksheet.add_sparkline( { :location => 'F2', :range => 'Sheet1!A2:E2', :type => 'column', :style => 12 } ) # Add a win/loss sparkline with negative values highlighted. worksheet.add_sparkline( { :location => 'F3', :range => 'Sheet1!A3:E3', :type => 'win_loss', :negative_points => 1 } ) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_sparklines2 @xlsx = 'sparklines2.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet1 = workbook.add_worksheet worksheet2 = workbook.add_worksheet bold = workbook.add_format(:bold => 1) row = 1 # Set the columns widths to make the output clearer. worksheet1.set_column('A:A', 14) worksheet1.set_column('B:B', 50) worksheet1.set_zoom(150) # Headings. worksheet1.write('A1', 'Sparkline', bold) worksheet1.write('B1', 'Description', bold) ########################################################################## # str = 'A default "line" sparkline.' worksheet1.add_sparkline( { :location => 'A2', :range => 'Sheet2!A1:J1' } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'A default "column" sparkline.' worksheet1.add_sparkline( { :location => 'A3', :range => 'Sheet2!A2:J2', :type => 'column' } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'A default "win/loss" sparkline.' worksheet1.add_sparkline( { :location => 'A4', :range => 'Sheet2!A3:J3', :type => 'win_loss' } ) worksheet1.write(row, 1, str) row += 2 ########################################################################## # str = 'Line with markers.' worksheet1.add_sparkline( { :location => 'A6', :range => 'Sheet2!A1:J1', :markers => 1 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Line with high and low points.' worksheet1.add_sparkline( { :location => 'A7', :range => 'Sheet2!A1:J1', :high_point => 1, :low_point => 1 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Line with first and last point markers.' worksheet1.add_sparkline( { :location => 'A8', :range => 'Sheet2!A1:J1', :first_point => 1, :last_point => 1 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Line with negative point markers.' worksheet1.add_sparkline( { :location => 'A9', :range => 'Sheet2!A1:J1', :negative_points => 1 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Line with axis.' worksheet1.add_sparkline( { :location => 'A10', :range => 'Sheet2!A1:J1', :axis => 1 } ) worksheet1.write(row, 1, str) row += 2 ########################################################################## # str = 'Column with default style (1).' worksheet1.add_sparkline( { :location => 'A12', :range => 'Sheet2!A2:J2', :type => 'column' } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Column with style 2.' worksheet1.add_sparkline( { :location => 'A13', :range => 'Sheet2!A2:J2', :type => 'column', :style => 2 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Column with style 3.' worksheet1.add_sparkline( { :location => 'A14', :range => 'Sheet2!A2:J2', :type => 'column', :style => 3 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Column with style 4.' worksheet1.add_sparkline( { :location => 'A15', :range => 'Sheet2!A2:J2', :type => 'column', :style => 4 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Column with style 5.' worksheet1.add_sparkline( { :location => 'A16', :range => 'Sheet2!A2:J2', :type => 'column', :style => 5 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Column with style 6.' worksheet1.add_sparkline( { :location => 'A17', :range => 'Sheet2!A2:J2', :type => 'column', :style => 6 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Column with a user defined colour.' worksheet1.add_sparkline( { :location => 'A18', :range => 'Sheet2!A2:J2', :type => 'column', :series_color => '#E965E0' } ) worksheet1.write(row, 1, str) row += 2 ########################################################################## # str = 'A win/loss sparkline.' worksheet1.add_sparkline( { :location => 'A20', :range => 'Sheet2!A3:J3', :type => 'win_loss' } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'A win/loss sparkline with negative points highlighted.' worksheet1.add_sparkline( { :location => 'A21', :range => 'Sheet2!A3:J3', :type => 'win_loss', :negative_points => 1 } ) worksheet1.write(row, 1, str) row += 2 ########################################################################## # str = 'A left to right column (the default).' worksheet1.add_sparkline( { :location => 'A23', :range => 'Sheet2!A4:J4', :type => 'column', :style => 20 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'A right to left column.' worksheet1.add_sparkline( { :location => 'A24', :range => 'Sheet2!A4:J4', :type => 'column', :style => 20, :reverse => 1 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # str = 'Sparkline and text in one cell.' worksheet1.add_sparkline( { :location => 'A25', :range => 'Sheet2!A4:J4', :type => 'column', :style => 20 } ) worksheet1.write(row, 0, 'Growth') worksheet1.write(row, 1, str) row += 2 ########################################################################## # str = 'A grouped sparkline. Changes are applied to all three.' worksheet1.add_sparkline( { :location => [ 'A27', 'A28', 'A29' ], :range => [ 'Sheet2!A5:J5', 'Sheet2!A6:J6', 'Sheet2!A7:J7' ], :markers => 1 } ) worksheet1.write(row, 1, str) row += 1 ########################################################################## # Create a second worksheet with data to plot. # worksheet2.set_column('A:J', 11) data = [ # Simple line data. [ -2, 2, 3, -1, 0, -2, 3, 2, 1, 0 ], # Simple column data. [ 30, 20, 33, 20, 15, 5, 5, 15, 10, 15 ], # Simple win/loss data. [ 1, 1, -1, -1, 1, -1, 1, 1, 1, -1 ], # Unbalanced histogram. [ 5, 6, 7, 10, 15, 20, 30, 50, 70, 100 ], # Data for the grouped sparkline example. [ -2, 2, 3, -1, 0, -2, 3, 2, 1, 0 ], [ 3, -1, 0, -2, 3, 2, 1, 0, 2, 1 ], [ 0, -2, 3, 2, 1, 0, 1, 2, 3, 1 ] ] # Write the sample data to the worksheet. worksheet2.write_col('A1', data) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_add_vba_project @xlsx = 'add_vba_project.xlsm' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet worksheet.set_column('A:A', 50) # Add the VBA project binary. workbook.add_vba_project(File.join(@test_dir, 'vbaProject.bin')) # Show text for the end user. worksheet.write('A1', 'Run the SampleMacro embedded in this file.') worksheet.write('A2', 'You may have to turn on the Excel Developer option first.') # Call a user defined function from the VBA project. worksheet.write('A6', 'Result from a user defined function:') worksheet.write('B6', '=MyFunction(7)') workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_hide_row_col @xlsx = 'hide_row_col.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet # Write some data worksheet.write('D1', 'Some hidden columns.') worksheet.write('A8', 'Some hidden rows.') # Hide all rows without data. worksheet.set_default_row(nil, 1) # Set emptys row that we do want to display. All other will be hidden. (1..6).each { |row| worksheet.set_row(row, 15) } # Hide a range of columns. worksheet.set_column('G:XFD', nil, nil, 1) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end def test_chart_data_table @xlsx = 'chart_data_table.xlsx' workbook = WriteXLSX.new(@xlsx) worksheet = workbook.add_worksheet bold = workbook.add_format(:bold => 1) # Add the worksheet data that the charts will refer to. headings = [ 'Number', 'Batch 1', 'Batch 2' ] data = [ [ 2, 3, 4, 5, 6, 7 ], [ 10, 40, 50, 20, 10, 50 ], [ 30, 60, 70, 50, 40, 30 ] ] worksheet.write('A1', headings, bold) worksheet.write('A2', data) # Create a new column chart with a data table. chart1 = workbook.add_chart(:type => 'column', :embedded => 1) # Configure the first series. chart1.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. Note alternative use of array ref to define # ranges: [ sheetname, row_start, row_end, col_start, col_end ]. chart1.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart1.set_title(:name => 'Chart with Data Table') chart1.set_x_axis(:name => 'Test number') chart1.set_y_axis(:name => 'Sample length (mm)') # Set a default data table on the X-Axis. chart1.set_table # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D2', chart1, 25, 10) # # Create a second charat. # chart2 = workbook.add_chart(:type => 'column', :embedded => 1) # Configure the first series. chart2.add_series( :name => '=Sheet1!$B$1', :categories => '=Sheet1!$A$2:$A$7', :values => '=Sheet1!$B$2:$B$7' ) # Configure second series. Note alternative use of array ref to define # ranges: [ sheetname, row_start, row_end, col_start, col_end ]. chart2.add_series( :name => '=Sheet1!$C$1', :categories => [ 'Sheet1', 1, 6, 0, 0 ], :values => [ 'Sheet1', 1, 6, 2, 2 ] ) # Add a chart title and some axis labels. chart2.set_title(:name => 'Data Table with legend keys') chart2.set_x_axis(:name => 'Test number') chart2.set_y_axis(:name => 'Sample length (mm)') # Set a default data table on the X-Axis with the legend keys shown. chart2.set_table(:show_keys => true) # Hide the chart legend since the keys are show on the data table. chart2.set_legend(:position => 'none') # Insert the chart into the worksheet (with an offset). worksheet.insert_chart('D18', chart2, 25, 11) workbook.close compare_xlsx(File.join(@perl_output, @xlsx), @xlsx) end end