lib/write_xlsx/worksheet.rb in write_xlsx-0.64.1 vs lib/write_xlsx/worksheet.rb in write_xlsx-0.65.0

- old
+ new

@@ -7,23 +7,26 @@ require 'write_xlsx/sparkline' require 'write_xlsx/compatibility' require 'write_xlsx/utility' require 'write_xlsx/package/conditional_format' require 'write_xlsx/worksheet/cell_data' -require 'write_xlsx/worksheet/print_style' +require 'write_xlsx/worksheet/data_validation' +require 'write_xlsx/worksheet/hyperlink' +require 'write_xlsx/worksheet/page_setup' require 'tempfile' module Writexlsx # - # A new worksheet is created by calling the add_worksheet() method from a workbook object: + # A new worksheet is created by calling the add_worksheet() method from a + # workbook object: # # worksheet1 = workbook.add_worksheet # worksheet2 = workbook.add_worksheet # # The following methods are available through a new worksheet: # - # * write + # * {#write}[#method-i-write] # * write_number # * write_string # * write_rich_string # * write_blank # * write_row @@ -31,41 +34,79 @@ # * write_date_time # * write_url # * write_formula # * write_comment # * show_comments - # * comments_author=() + # * {#comments_author=}[#method-i-comments_author-3D] # * insert_image # * insert_chart # * insert_shape # * insert_button # * data_validation # * conditional_formatting # * add_sparkline # * add_table - # * name - # * activate - # * select - # * hide + # * {#name}[#method-i-name] + # * {#activate}[#method-i-activate] + # * {#select}[#method-i-select] + # * {#hide}[#method-i-hide] # * set_first_sheet - # * protect + # * {#protect}[#method-i-protect] # * set_selection # * set_row # * set_column # * outline_settings # * freeze_panes # * split_panes # * merge_range # * merge_range_type - # * zoom=() + # * {#zoom=}[#method-i-zoom-3D] # * right_to_left # * hide_zero - # * tab_color=() - # * autofilter + # * {#tab_color=}[#method-i-tab_color-3D] + # * {#autofilter}[#method-i-autofilter] # * filter_column # * filter_column_list # + # == PAGE SET-UP METHODS + # + # Page set-up methods affect the way that a worksheet looks + # when it is printed. They control features such as page headers and footers + # and margins. These methods are really just standard worksheet methods. + # + # The following methods are available for page set-up: + # + # * set_landscape + # * set_portrait + # * set_page_view + # * set_paper + # * center_horizontally + # * center_vertically + # * set_margins + # * set_header + # * set_footer + # * repeat_rows + # * repeat_columns + # * hide_gridlines + # * print_row_col_headers + # * print_area + # * print_across + # * fit_to_pages + # * set_start_page + # * set_print_scale + # * set_h_pagebreaks + # * set_v_pagebreaks + # + # A common requirement when working with WriteXLSX is to apply the same + # page set-up features to all of the worksheets in a workbook. To do this + # you can use the sheets() method of the workbook class to access the array + # of worksheets in a workbook: + # + # workbook.sheets.each do |worksheet| + # worksheet.set_landscape + # end + # # ==Cell notation # # WriteXLSX supports two forms of notation to designate the position of cells: # Row-column notation and A1 notation. # @@ -109,48 +150,10 @@ # it is also possible to use A1 notation. # # Note: in Excel it is also possible to use a R1C1 notation. This is not # supported by WriteXLSX. # - # == PAGE SET-UP METHODS - # - # Page set-up methods affect the way that a worksheet looks - # when it is printed. They control features such as page headers and footers - # and margins. These methods are really just standard worksheet methods. - # - # The following methods are available for page set-up: - # - # * set_landscape - # * set_portrait - # * set_page_view - # * set_paper - # * center_horizontally - # * center_vertically - # * set_margins - # * set_header - # * set_footer - # * repeat_rows - # * repeat_columns - # * hide_gridlines - # * print_row_col_headers - # * print_area - # * print_across - # * fit_to_pages - # * set_start_page - # * set_print_scale - # * set_h_pagebreaks - # * set_v_pagebreaks - # - # A common requirement when working with WriteXLSX is to apply the same - # page set-up features to all of the worksheets in a workbook. To do this - # you can use the sheets() method of the workbook class to access the array - # of worksheets in a workbook: - # - # workbook.sheets.each do |worksheet| - # worksheet.set_landscape - # end - # # == FORMULAS AND FUNCTIONS IN EXCEL # # === Introduction # # The following is a brief introduction to formulas and functions in Excel @@ -275,20 +278,20 @@ # applications are more flexible than Excel with formula syntax. # class Worksheet include Writexlsx::Utility + MAX_DIGIT_WIDTH = 7 # For Calabri 11. # :nodoc: + PADDING = 5 # :nodoc: + attr_reader :index # :nodoc: attr_reader :charts, :images, :tables, :shapes, :drawing # :nodoc: - attr_reader :external_hyper_links, :external_drawing_links # :nodoc: - attr_reader :external_vml_links, :external_table_links # :nodoc: - attr_reader :external_comment_links, :drawing_links # :nodoc: attr_reader :vml_data_id # :nodoc: attr_reader :autofilter_area # :nodoc: attr_reader :writer, :set_rows, :col_formats # :nodoc: - attr_accessor :vml_shape_id, :rel_count, :hlink_refs # :nodoc: - attr_reader :comments_author # :nodoc: + attr_reader :vml_shape_id # :nodoc: + attr_reader :comments, :comments_author # :nodoc: attr_accessor :dxf_priority # :nodoc: attr_reader :vba_codename # :nodoc: def initialize(workbook, index, name) #:nodoc: @writer = Package::XMLWriterSimple.new @@ -298,14 +301,12 @@ @name = name @colinfo = [] @cell_data_table = {} @excel_version = 2007 - @print_style = PrintStyle.new + @page_setup = PageSetup.new - @print_area = '' - @screen_gridlines = true @show_zeros = true @dim_rowmin = nil @dim_rowmax = nil @dim_colmin = nil @@ -370,49 +371,49 @@ @writer.set_xml_writer(filename) end def assemble_xml_file #:nodoc: @writer.xml_decl - write_worksheet - write_sheet_pr - write_dimension - write_sheet_views - write_sheet_format_pr - write_cols - write_sheet_data - write_sheet_protection - write_auto_filter - write_merge_cells - write_conditional_formats - write_data_validations - write_hyperlinks - write_print_options - write_page_margins - write_page_setup - write_header_footer - write_row_breaks - write_col_breaks - write_drawings - write_legacy_drawing - write_table_parts - write_ext_sparklines - @writer.end_tag('worksheet') + @writer.tag_elements('worksheet', write_worksheet_attributes) do + write_sheet_pr + write_dimension + write_sheet_views + write_sheet_format_pr + write_cols + write_sheet_data + write_sheet_protection + write_auto_filter + write_merge_cells + write_conditional_formats + write_data_validations + write_hyperlinks + write_print_options + write_page_margins + write_page_setup + write_header_footer + write_row_breaks + write_col_breaks + write_drawings + write_legacy_drawing + write_table_parts + write_ext_sparklines + end @writer.crlf @writer.close end # - # The name() method is used to retrieve the name of a worksheet. + # The name method is used to retrieve the name of a worksheet. # For example: # # workbook.sheets.each do |sheet| # print sheet.name # end # # For reasons related to the design of WriteXLSX and to the internals # of Excel there is no set_name() method. The only way to set the - # worksheet name is via the add_worksheet() method. + # worksheet name is via the Workbook#add_worksheet() method. # def name @name end @@ -449,11 +450,11 @@ # worksheet3 = workbook.add_worksheet('wind') # # worksheet3.activate # # This is similar to the Excel VBA activate method. More than one worksheet - # can be selected via the select() method, see below, however only one + # can be selected via the select() method, however only one # worksheet can be active. # # The default active worksheet is the first worksheet. # def activate @@ -529,12 +530,12 @@ # and hidden properties if they have been set. A locked cell cannot be # edited and this property is on by default for all cells. A hidden # cell will display the results of a formula but not the formula itself. # # See the protection.rb program in the examples directory of the distro - # for an illustrative example and the set_locked and set_hidden format - # methods in "CELL FORMATTING". + # for an illustrative example and the +set_locked+ and +set_hidden+ format + # methods in "CELL FORMATTING", see Format. # # You can optionally add a password to the worksheet protection: # # worksheet.protect('drowssap') # @@ -546,11 +547,11 @@ # deactivate. Full workbook encryption is not supported by WriteXLSX # since it requires a completely different file format and would take # several man months to implement. # # You can specify which worksheet elements that you which to protect - # by passing a hash_ref with any or all of the following keys: + # by passing a hash with any or all of the following keys: # # # Default shown. # options = { # :objects => false, # :scenarios => false, @@ -566,10 +567,11 @@ # :sort => false, # :autofilter => false, # :pivot_tables => false, # :select_unlocked_cells => true # } + # # The default boolean values are shown above. Individual elements # can be protected as follows: # # worksheet.protect('drowssap', { :insert_rows => true } ) # @@ -605,19 +607,19 @@ end private :protect_default_settings # # :call-seq: - # set_column(firstcol, lastcol, width, format, hidden, level) + # set_column(firstcol, lastcol, width, format, hidden, level, collapsed) # # This method can be used to change the default properties of a single - # column or a range of columns. All parameters apart from first_col - # and last_col are optional. + # column or a range of columns. All parameters apart from +first_col+ + # and +last_col+ are optional. # - # If set_column() is applied to a single column the value of first_col - # and last_col should be the same. In the case where last_col is zero - # it is set to the same value as first_col. + # If set_column() is applied to a single column the value of +first_col+ + # and +last_col+ should be the same. In the case where +last_col+ is zero + # it is set to the same value as +first_col+. # # It is also possible, and generally clearer, to specify a column range # using the form of A1 notation used for columns. See the note about # {"Cell notation"}[#label-Cell+notation]. # @@ -633,64 +635,66 @@ # default font of Arial 10. Unfortunately, there is no way to specify # "AutoFit" for a column in the Excel file format. This feature is # only available at runtime from within Excel. # # As usual the format parameter is optional, for additional information, - # see "CELL FORMATTING". If you wish to set the format without changing - # the width you can pass nil as the width parameter: + # See {"CELL FORMATTING"}[Format.html#label-CELL+FORMATTING]. + # If you wish to set the format without changing the width you can pass + # nil as the width parameter: # # worksheet.set_column(0, 0, nil, format) # # The format parameter will be applied to any cells in the column that # don't have a format. For example # - # worksheet.set_column( 'A:A', nil, format1 ) # Set format for col 1 - # worksheet.write( 'A1', 'Hello' ) # Defaults to format1 - # worksheet.write( 'A2', 'Hello', format2 ) # Keeps format2 + # worksheet.set_column('A:A', nil, format1) # Set format for col 1 + # worksheet.write('A1', 'Hello') # Defaults to format1 + # worksheet.write('A2', 'Hello', format2) # Keeps format2 # # If you wish to define a column format in this way you should call the - # method before any calls to write(). If you call it afterwards it - # won't have any effect. + # method before any calls to {#write()}[#method-i-write]. + # If you call it afterwards it won't have any effect. # # A default row format takes precedence over a default column format # # worksheet.set_row( 0, nil, format1 ) # Set format for row 1 # worksheet.set_column( 'A:A', nil, format2 ) # Set format for col 1 # worksheet.write( 'A1', 'Hello' ) # Defaults to format1 # worksheet.write( 'A2', 'Hello' ) # Defaults to format2 # - # The hidden parameter should be set to 1 if you wish to hide a column. + # The +hidden+ parameter should be set to 1 if you wish to hide a column. # This can be used, for example, to hide intermediary steps in a # complicated calculation: # # worksheet.set_column( 'D:D', 20, format, 1 ) # worksheet.set_column( 'E:E', nil, nil, 1 ) # - # The level parameter is used to set the outline level of the column. - # Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent - # columns with the same outline level are grouped together into a single - # outline. + # The +level+ parameter is used to set the outline level of the column. + # Outlines are described in + # {"OUTLINES AND GROUPING IN EXCEL"}["method-i-set_row-label-OUTLINES+AND+GROUPING+IN+EXCEL"]. + # Adjacent columns with the same outline level are grouped together into + # a single outline. # # The following example sets an outline level of 1 for columns B to G: # # worksheet.set_column( 'B:G', nil, nil, 0, 1 ) # - # The hidden parameter can also be used to hide collapsed outlined - # columns when used in conjunction with the level parameter. + # The +hidden+ parameter can also be used to hide collapsed outlined + # columns when used in conjunction with the +level+ parameter. # # worksheet.set_column( 'B:G', nil, nil, 1, 1 ) # # For collapsed outlines you should also indicate which row has the - # collapsed + symbol using the optional collapsed parameter. + # collapsed + symbol using the optional +collapsed+ parameter. # # worksheet.set_column( 'H:H', nil, nil, 0, 0, 1 ) # # For a more complete example see the outline.rb and outline_collapsed.rb # programs in the examples directory of the distro. # # Excel allows up to 7 outline levels. Therefore the level parameter - # should be in the range 0 <= level <= 7. + # should be in the range <tt>0 <= level <= 7</tt>. # def set_column(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ row1, firstcol, row2, lastcol, *data = substitute_cellref(*args) @@ -705,11 +709,11 @@ lastcol = firstcol unless ptrue?(lastcol) # Ensure 2nd col is larger than first. Also for KB918419 bug. firstcol, lastcol = lastcol, firstcol if firstcol > lastcol - width, format, hidden, level = data + width, format, hidden, level, collapsed = data # Check that cols are valid and store max and min values with default row. # NOTE: The check shouldn't modify the row dimensions and should only modify # the column dimensions in certain cases. ignore_row = 1 @@ -726,11 +730,11 @@ level = 7 if level > 7 @outline_col_level = level if level > @outline_col_level # Store the column data. - @colinfo.push([firstcol, lastcol] + data) + @colinfo << [firstcol, lastcol, width, format, hidden, level, collapsed] # Store the column change to allow optimisations. @col_size_changed = 1 # Store the col sizes for use when calculating image vertices taking @@ -750,14 +754,15 @@ # # Set which cell or cells are selected in a worksheet. # # This method can be used to specify which cell or cells are selected # in a worksheet. The most common requirement is to select a single cell, - # in which case last_row and last_col can be omitted. The active cell - # within a selected range is determined by the order in which first and - # last are specified. It is also possible to specify a cell or a range - # using A1 notation. See the note about {"Cell notation"}[#label-Cell+notation]. + # in which case +last_row+ and +last_col+ can be omitted. The active cell + # within a selected range is determined by the order in which +first+ and + # +last+ are specified. It is also possible to specify a cell or a range + # using A1 notation. See the note about + # {"Cell notation"}[#label-Cell+notation]. # # Examples: # # worksheet1.set_selection(3, 3) # 1. Cell D4. # worksheet2.set_selection(3, 3, 6, 6) # 2. Cells D4 to G7. @@ -800,21 +805,21 @@ # freeze_panes(row, col [ , top_row, left_col ] ) # # This method can be used to divide a worksheet into horizontal or # vertical regions known as panes and to also "freeze" these panes so # that the splitter bars are not visible. This is the same as the - # Window->Freeze Panes menu command in Excel + # <tt>Window->Freeze</tt> Panes menu command in Excel # - # The parameters row and col are used to specify the location of + # The parameters +row+ and +col+ are used to specify the location of # the split. It should be noted that the split is specified at the # top or left of a cell and that the method uses zero based indexing. # Therefore to freeze the first row of a worksheet it is necessary # to specify the split at row 2 (which is 1 as the zero-based index). # This might lead you to think that you are using a 1 based index # but this is not the case. # - # You can set one of the row and col parameters as zero if you + # You can set one of the row and +col+ parameters as zero if you # do not want either a vertical or horizontal split. # # Examples: # # worksheet.freeze_panes(1, 0) # Freeze the first row @@ -822,18 +827,18 @@ # worksheet.freeze_panes(0, 1) # Freeze the first column # worksheet.freeze_panes('B1') # Same using A1 notation # worksheet.freeze_panes(1, 2) # Freeze first row and first 2 columns # worksheet.freeze_panes('C2') # Same using A1 notation # - # The parameters top_row and left_col are optional. They are used + # The parameters +top_row+ and +left_col+ are optional. They are used # to specify the top-most or left-most visible row or column in the # scrolling region of the panes. For example to freeze the first row # and to have the scrolling region begin at row twenty: # # worksheet.freeze_panes(1, 0, 20, 0) # - # You cannot use A1 notation for the top_row and left_col parameters. + # You cannot use A1 notation for the +top_row+ and +left_col+ parameters. # # See also the panes.rb program in the examples directory of the # distribution. # def freeze_panes(*args) @@ -850,11 +855,11 @@ @panes = [row, col, top_row, left_col, type ] end # # :call-seq: - # split_panes(y, x, top_row, left_col, offset_row, offset_col) + # split_panes(y, x, top_row, left_col) # # Set panes and mark them as split. #-- # Implementers note. The API for this method doesn't map well from the XLS # file format and isn't sufficient to describe all cases of split panes. @@ -867,22 +872,22 @@ # This method can be used to divide a worksheet into horizontal or vertical # regions known as panes. This method is different from the freeze_panes() # method in that the splits between the panes will be visible to the user # and each pane will have its own scroll bars. # - # The parameters y and x are used to specify the vertical and horizontal + # The parameters +y+ and +x+ are used to specify the vertical and horizontal # position of the split. The units for y and x are the same as those # used by Excel to specify row height and column width. However, the # vertical and horizontal units are different from each other. Therefore # you must specify the y and x parameters in terms of the row heights # and column widths that you have set or the default values which are 15 # for a row and 8.43 for a column. # - # You can set one of the y and x parameters as zero if you do not want - # either a vertical or horizontal split. The parameters top_row and left_col - # are optional. They are used to specify the top-most or left-most visible - # row or column in the bottom-right pane. + # You can set one of the +y+ and +x+ parameters as zero if you do not want + # either a vertical or horizontal split. The parameters +top_row+ and + # +left_col+ are optional. They are used to specify the top-most or + # left-most visible row or column in the bottom-right pane. # # Example: # # worksheet.split_panes(15, 0 ) # First row # worksheet.split_panes( 0, 8.43) # First column @@ -902,20 +907,20 @@ # Set the page orientation as portrait. # The default worksheet orientation is portrait, so you won't generally # need to call this method. # def set_portrait - @print_style.orientation = true - @print_style.page_setup_changed = true + @page_setup.orientation = true + @page_setup.page_setup_changed = true end # # Set the page orientation as landscape. # def set_landscape - @print_style.orientation = false - @print_style.page_setup_changed = true + @page_setup.orientation = false + @page_setup.page_setup_changed = true end # # This method is used to display the worksheet in "Page View/Layout" mode. # @@ -1008,14 +1013,11 @@ # # If you do not specify a paper type the worksheet will print using # the printer's default paper. # def paper=(paper_size) - if paper_size - @paper_size = paper_size - @print_style.page_setup_changed = true - end + @page_setup.paper = paper_size end def set_paper(paper_size) put_deprecate_message("#{self}.set_paper") self::paper = paper_size @@ -1171,42 +1173,40 @@ # distribution. # def set_header(string = '', margin = 0.3) raise 'Header string must be less than 255 characters' if string.length >= 255 - @header = string - @print_style.margin_header = margin - @header_footer_changed = true + @page_setup.header = string + @page_setup.margin_header = margin + @page_setup.header_footer_changed = true end # # Set the page footer caption and optional margin. # # The syntax of the set_footer() method is the same as set_header() # def set_footer(string = '', margin = 0.3) raise 'Footer string must be less than 255 characters' if string.length >= 255 - @footer = string - @print_style.margin_footer = margin - @header_footer_changed = true + @page_setup.footer = string + @page_setup.margin_footer = margin + @page_setup.header_footer_changed = true end # # Center the worksheet data horizontally between the margins on the printed page: # def center_horizontally - @print_options_changed = true - @hcenter = true + @page_setup.center_horizontally end # # Center the worksheet data vertically between the margins on the printed page: # def center_vertically - @print_options_changed = true - @vcenter = true + @page_setup.center_vertically end # # Set all the page margins to the same value in inches. # @@ -1255,35 +1255,35 @@ # # Set the left margin in inches. # See margins=() # def margin_left=(margin) - @print_style.margin_left = remove_white_space(margin) + @page_setup.margin_left = remove_white_space(margin) end # # Set the right margin in inches. # See margins=() # def margin_right=(margin) - @print_style.margin_right = remove_white_space(margin) + @page_setup.margin_right = remove_white_space(margin) end # # Set the top margin in inches. # See margins=() # def margin_top=(margin) - @print_style.margin_top = remove_white_space(margin) + @page_setup.margin_top = remove_white_space(margin) end # # Set the bottom margin in inches. # See margins=() # def margin_bottom=(margin) - @print_style.margin_bottom = remove_white_space(margin) + @page_setup.margin_bottom = remove_white_space(margin) end # # set_margin_* methods are deprecated. use margin_*=(). # @@ -1392,15 +1392,15 @@ area = "$#{row_min}:$#{row_max}" # Build up the print titles "Sheet1!$1:$2" sheetname = quote_sheetname(name) - @print_style.repeat_rows = "#{sheetname}!#{area}" + @page_setup.repeat_rows = "#{sheetname}!#{area}" end def print_repeat_rows # :nodoc: - @print_style.repeat_rows + @page_setup.repeat_rows end # # :call-seq: # repeat_columns(first_col, last_col = nil) # @@ -1426,15 +1426,15 @@ first_col, last_col = args end last_col ||= first_col area = "#{xl_col_to_name(first_col, 1)}:#{xl_col_to_name(last_col, 1)}" - @print_style.repeat_cols = "#{quote_sheetname(@name)}!#{area}" + @page_setup.repeat_cols = "#{quote_sheetname(@name)}!#{area}" end def print_repeat_cols # :nodoc: - @print_style.repeat_cols + @page_setup.repeat_cols end # # :call-seq: # print_area(first_row, first_col, last_row, last_col) @@ -1446,25 +1446,25 @@ # worksheet1.print_area( 'A1:H20' ); # Cells A1 to H20 # worksheet2.print_area( 0, 0, 19, 7 ); # The same # worksheet2.print_area( 'A:H' ); # Columns A to H if rows have data # def print_area(*args) - return @print_area.dup if args.empty? + return @page_setup.print_area.dup if args.empty? row1, col1, row2, col2 = row_col_notation(args) return if [row1, col1, row2, col2].include?(nil) # Ignore max print area since this is the same as no print area for Excel. if row1 == 0 && col1 == 0 && row2 == ROW_MAX - 1 && col2 == COL_MAX - 1 return end # Build up the print area range "=Sheet2!R1C1:R2C1" - @print_area = convert_name_area(row1, col1, row2, col2) + @page_setup.print_area = convert_name_area(row1, col1, row2, col2) end # - # Set the worksheet zoom factor in the range 10 <= $scale <= 400: + # Set the worksheet zoom factor in the range <tt>10 <= scale <= 400</tt>: # # worksheet1.zoom = 50 # worksheet2.zoom = 75 # worksheet3.zoom = 300 # worksheet4.zoom = 400 @@ -1513,14 +1513,14 @@ scale_val = scale.to_i # Confine the scale to Excel's range scale_val = 100 if scale_val < 10 || scale_val > 400 # Turn off "fit to page" option. - @print_style.fit_page = false + @page_setup.fit_page = false - @print_style.scale = scale_val - @print_style.page_setup_changed = true + @page_setup.scale = scale_val + @page_setup.page_setup_changed = true end # # This method is deprecated. use print_scale=(). # @@ -1579,14 +1579,14 @@ # [1] [2] # [3] [4] # def print_across(across = true) if across - @print_style.across = true - @print_style.page_setup_changed = true + @page_setup.across = true + @page_setup.page_setup_changed = true else - @print_style.across = false + @page_setup.across = false end end # # Not implememt yet. @@ -1607,11 +1607,11 @@ # :call-seq: # write(row, column [ , token [ , format ] ]) # # Excel makes a distinction between data types such as strings, numbers, # blanks, formulas and hyperlinks. To simplify the process of writing - # data the write() method acts as a general alias for several more + # data the {#write()}[#method-i-write] method acts as a general alias for several more # specific methods: # # write_string # write_number # write_blank @@ -1653,15 +1653,15 @@ # format.set_color('red') # format.set_align('center') # # worksheet.write(4, 0, 'Hello', format) # Formatted string # - # The write() method will ignore empty strings or +nil+ tokens unless a + # The {#write()}[#method-i-write] method will ignore empty strings or +nil+ tokens unless a # format is also supplied. As such you needn't worry about special handling # for empty or nil in your data. See also the write_blank() method. # - # One problem with the write() method is that occasionally data looks like + # One problem with the {#write()}[#method-i-write] method is that occasionally data looks like # a number but you don't want it treated as a number. For example, zip # codes or ID numbers often start with a leading zero. # If you want to write this data with leading zero(s), use write_string. # # The write methods return: @@ -1711,11 +1711,11 @@ # of 1D or 2D arrays of data in one go. # # The write_row() method can be used to write a 1D or 2D array of data # in one go. This is useful for converting the results of a database # query into an Excel worksheet. You must pass a reference to the array - # of data rather than the array itself. The write() method is then + # of data rather than the array itself. The {#write()}[#method-i-write] method is then # called for each element of the data. For example: # # array = ['awk', 'gawk', 'mawk'] # # worksheet.write_row(0, 0, array) @@ -1723,11 +1723,11 @@ # # The above example is equivalent to: # worksheet.write(0, 0, array[0]) # worksheet.write(0, 1, array[1]) # worksheet.write(0, 2, array[2]) # - # Note: For convenience the write() method behaves in the same way as + # Note: For convenience the {#write()}[#method-i-write] method behaves in the same way as # write_row() if it is passed an array. # Therefore the following two method calls are equivalent: # # worksheet.write_row('A1', array) # Write a row of data # worksheet.write( 'A1', array) # Same thing @@ -1842,11 +1842,11 @@ # Any +nil+ in the data will be ignored unless a format is applied to # the data, in which case a formatted blank cell will be written. # In either case the appropriate row or column value will still be # incremented. # - # As noted above the write() method can be used as a synonym for + # As noted above the {#write()}[#method-i-write] method can be used as a synonym for # write_row() and write_row() handles nested array refs as columns. # Therefore, the following two method calls are equivalent although # the more explicit call to write_col() would be preferable for # maintainability: # @@ -1856,11 +1856,10 @@ # See also the write_arrays.rb program in the examples directory of # the distro. # def write_col(*args) row, col, tokens, *options = row_col_notation(args) - raise "Not an array ref in call to write_col()$!" unless tokens.respond_to?(:to_ary) tokens.each do |token| # write() will deal with any nested arrays write(row, col, token, *options) row += 1 @@ -2069,11 +2068,11 @@ # worksheet.write_number('A2', 2.3451) # # See the note about {"Cell notation"}[#label-Cell+notation]. # The +format+ parameter is optional. # - # In general it is sufficient to use the write() method. + # In general it is sufficient to use the {#write()}[#method-i-write] method. # # Note: some versions of Excel 2007 do not display the calculated values # of formulas written by WriteXLSX. Applying all available Service Packs # to Excel should fix this. # @@ -2101,11 +2100,11 @@ # # The maximum string size is 32767 characters. However the maximum # string segment that Excel can display in a cell is 1000. # All 32767 characters can be displayed in the formula bar. # - # In general it is sufficient to use the write() method. + # In general it is sufficient to use the {#write()}[#method-i-write] method. # However, you may sometimes wish to use the write_string() method # to write data that looks like a number but that you don't want # treated as a number. For example, zip codes or phone numbers: # # # Write as a plain string @@ -2228,51 +2227,22 @@ def write_rich_string(*args) # Check for a cell reference in A1 notation and substitute row and column row, col, *rich_strings = row_col_notation(args) raise WriteXLSXInsufficientArgumentError if [row, col, rich_strings[0]].include?(nil) - # If the last arg is a format we use it as the cell format. - if rich_strings[-1].respond_to?(:xf_index) - xf = rich_strings.pop - else - xf = nil - end + xf = cell_format_of_rich_string(rich_strings) # Check that row and col are valid and store max and min values check_dimensions(row, col) store_row_col_max_min_values(row, col) - # Create a temp XML::Writer object and use it to write the rich string - # XML to a string. - writer = Package::XMLWriterSimple.new - fragments, length = rich_strings_fragments(rich_strings) # can't allow 2 formats in a row return -4 unless fragments - # If the first token is a string start the <r> element. - writer.start_tag('r') if !fragments[0].respond_to?(:xf_index) + index = shared_string_index(xml_str_of_rich_string(fragments)) - # Write the XML elements for the format string fragments. - fragments.each do |token| - if token.respond_to?(:xf_index) - # Write the font run. - writer.start_tag('r') - write_font(writer, token) - else - # Write the string fragment part, with whitespace handling. - attributes = [] - - attributes << 'xml:space' << 'preserve' if token =~ /^\s/ || token =~ /\s$/ - writer.data_element('t', token, attributes) - writer.end_tag('r') - end - end - - # Add the XML string to the shared string table. - index = shared_string_index(writer.string) - store_data_to_table(StringCellData.new(self, row, col, index, xf)) end # # :call-seq: @@ -2362,11 +2332,11 @@ if formula =~ /^\{=.*\}$/ write_array_formula(row, col, row, col, formula, format, value) else check_dimensions(row, col) store_row_col_max_min_values(row, col) - formula.sub!(/^=/, '') + formula = formula.sub(/^=/, '') store_data_to_table(FormulaCellData.new(self, row, col, formula, format, value)) end end @@ -2383,11 +2353,11 @@ # value then the +first_+ and +last_+ parameters should be the same: # # worksheet.write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}') # # It this case however it is easier to just use the write_formula() - # or write() methods: + # or {#write()}[#method-i-write] methods: # # # Same as above but more concise. # worksheet.write('A1', '{=SUM(B1:C1*B2:C2)}') # worksheet.write_formula('A1', '{=SUM(B1:C1*B2:C2)}') # @@ -2432,12 +2402,11 @@ else range ="#{xl_rowcol_to_cell(row1, col1)}:#{xl_rowcol_to_cell(row2, col2)}" end # Remove array formula braces and the leading =. - formula.sub!(/^\{(.*)\}$/, '\1') - formula.sub!(/^=/, '') + formula = formula.sub(/^\{(.*)\}$/, '\1').sub(/^=/, '') store_data_to_table(FormulaArrayCellData.new(self, row1, col1, formula, xf, range, value)) # Pad out the rest of the area with formatted zeroes. (row1..row2).each do |row| @@ -2446,31 +2415,33 @@ write_number(row, col, 0, xf) end end end + # # The outline_settings() method is used to control the appearance of - # outlines in Excel. Outlines are described in "OUTLINES AND GROUPING IN EXCEL". + # outlines in Excel. Outlines are described in + # {"OUTLINES AND GROUPING IN EXCEL"}["method-i-set_row-label-OUTLINES+AND+GROUPING+IN+EXCEL"]. # - # The visible parameter is used to control whether or not outlines are + # The +visible+ parameter is used to control whether or not outlines are # visible. Setting this parameter to 0 will cause all outlines on the # worksheet to be hidden. They can be unhidden in Excel by means of the # "Show Outline Symbols" command button. The default setting is 1 for # visible outlines. # # worksheet.outline_settings(0) # - # The symbols_below parameter is used to control whether the row outline + # The +symbols_below+ parameter is used to control whether the row outline # symbol will appear above or below the outline level bar. The default # setting is 1 for symbols to appear below the outline level bar. # - # The symbols_right parameter is used to control whether the column + # The +symbols_right+ parameter is used to control whether the column # outline symbol will appear to the left or the right of the outline level # bar. The default setting is 1 for symbols to appear to the right of # the outline level bar. # - # The auto_style parameter is used to control whether the automatic + # The +auto_style+parameter is used to control whether the automatic # outline generator in Excel uses automatic styles when creating an # outline. This has no effect on a file generated by WriteXLSX but it # does have an effect on how the worksheet behaves after it is created. # The default setting is 0 for "Automatic Styles" to be turned off. # @@ -2502,11 +2473,11 @@ # # Write a hyperlink to a URL in the cell specified by +row+ and +column+. # The hyperlink is comprised of two elements: the visible label and # the invisible link. The visible label is the same as the link unless # an alternative label is specified. The label parameter is optional. - # The label is written using the write() method. Therefore it is + # The label is written using the {#write()}[#method-i-write] method. Therefore it is # possible to write strings, numbers or formulas as labels. # # The +format+ parameter is also optional, however, without a format # the link won't look like a format. # @@ -2549,11 +2520,11 @@ # worksheet.write_url('A11', 'external:c:\foo.xlsx#Sheet2!A1', format) # worksheet.write_url('A12', 'external:..\foo.xlsx', format) # worksheet.write_url('A13', 'external:..\foo.xlsx#Sheet2!A1', format) # worksheet.write_url('A13', 'external:\\\\NET\share\foo.xlsx', format) # - # All of the these URI types are recognised by the write() method, see above. + # All of the these URI types are recognised by the {#write()}[#method-i-write] method, see above. # # Worksheet references are typically of the form Sheet1!A1. You can # also refer to a worksheet range using the standard Excel notation: # +Sheet1!A1:B2+. # @@ -2580,106 +2551,30 @@ # Check for a cell reference in A1 notation and substitute row and column row, col, url, xf, str, tip = row_col_notation(args) xf, str = str, xf if str.respond_to?(:xf_index) || !xf.respond_to?(:xf_index) raise WriteXLSXInsufficientArgumentError if [row, col, url].include?(nil) - link_type = 1 - - # Remove the URI scheme from internal links. - if url =~ /^internal:/ - url.sub!(/^internal:/, '') - link_type = 2 - # Remove the URI scheme from external links. - elsif url =~ /^external:/ - url.sub!(/^external:/, '') - link_type = 3 - end - - # The displayed string defaults to the url string. - str ||= url.dup - - # For external links change the directory separator from Unix to Dos. - if link_type == 3 - url.gsub!(%r|/|, '\\') - str.gsub!(%r|/|, '\\') - end - - # Strip the mailto header. - str.sub!(/^mailto:/, '') - # Check that row and col are valid and store max and min values check_dimensions(row, col) store_row_col_max_min_values(row, col) - # Copy string for use in hyperlink elements. - url_str = str.dup + hyperlink = Hyperlink.new(url, str) + hyperlink.tip = tip - # External links to URLs and to other Excel workbooks have slightly - # different characteristics that we have to account for. - if link_type == 1 - # Escape URL unless it looks already escaped. - unless url =~ /%[0-9a-fA-F]{2}/ - # Escape the URL escape symbol. - url = url.gsub(/%/, "%25") - - # Escape whitespae in URL. - url = url.gsub(/[\s\x00]/, '%20') - - # Escape other special characters in URL. - re = /(["<>\[\]`^{}])/ - while re =~ url - match = $~[1] - url = url.sub(re, sprintf("%%%x", match.ord)) - end - end - - # Ordinary URL style external links don't have a "location" string. - url_str = nil - elsif link_type == 3 - # External Workbook links need to be modified into the right format. - # The URL will look something like 'c:\temp\file.xlsx#Sheet!A1'. - # We need the part to the left of the # as the URL and the part to - # the right as the "location" string (if it exists). - url, url_str = url.split(/#/) - - # Add the file:/// URI to the url if non-local. - if url =~ %r![:]! || # Windows style "C:/" link. - url =~ %r!^\\\\! # Network share. - url = "file:///#{url}" - end - - # Convert a ./dir/file.xlsx link to dir/file.xlsx. - url = url.sub(%r!^.\\!, '') - - # Treat as a default external link now that the data has been modified. - link_type = 1 - end - - # Excel limits escaped URL to 255 characters. - if url.bytesize > 255 - raise "URL '#{url}' > 255 characters, it exceeds Excel's limit for URLS." - end - - # Check the limit of URLS per worksheet. @hlink_count += 1 if @hlink_count > 65_530 - raise "URL '#{url}' added but number of URLS is over Excel's limit of 65,530 URLS per worksheet." + raise "URL '#{hyperlink.url}' added but number of URLS is over Excel's limit of 65,530 URLS per worksheet." end # Write the hyperlink string. - write_string(row, col, str, xf) + write_string(row, col, hyperlink.str, xf) # Store the hyperlink data in a separate structure. @hyperlinks ||= {} @hyperlinks[row] ||= {} - @hyperlinks[row][col] = { - :_link_type => link_type, - :_url => url, - :_str => url_str, - :_tip => tip - } + @hyperlinks[row][col] = hyperlink end # # :call-seq: # write_date_time (row, col, date_string [ , format ] ) @@ -2729,18 +2624,18 @@ # == DATES AND TIME IN EXCEL # # There are two important things to understand about dates and times in Excel: # # 1 A date/time in Excel is a real number plus an Excel number format. - # 2 WriteXLSX doesn't automatically convert date/time strings in write() to an Excel date/time. + # 2 WriteXLSX doesn't automatically convert date/time strings in {#write()}[#method-i-write] to an Excel date/time. # # These two points are explained in more detail below along with some # suggestions on how to convert times and dates to the required format. # # === An Excel date/time is a number plus a format # - # If you write a date string with write() then all you will get is a string: + # If you write a date string with {#write()}[#method-i-write] then all you will get is a string: # # worksheet.write('A1', '02/03/04') # !! Writes a string not a date. !! # # Dates and times in Excel are represented by real numbers, for example # "Jan 1 2001 12:30 AM" is represented by the number 36892.521. @@ -2808,11 +2703,11 @@ # # 1. Identify incoming date/time strings with a regex. # 2. Extract the component parts of the date/time using the same regex. # 3. Convert the date/time to the ISO8601 format. # 4. Write the date/time using write_date_time() and a number format. - # For a slightly more advanced solution you can modify the write() method + # For a slightly more advanced solution you can modify the {#write()}[#method-i-write] method # to handle date formats of your choice via the add_write_handler() method. # See the add_write_handler() section of the docs and the # write_handler3.rb and write_handler4.rb programs in the examples # directory of the distro. # @@ -3002,67 +2897,155 @@ # # :call-seq: # set_row(row [ , height, format, hidden, level, collapsed ] ) # # This method can be used to change the default properties of a row. - # All parameters apart from row are optional. + # All parameters apart from +row+ are optional. # # The most common use for this method is to change the height of a row: # # worksheet.set_row(0, 20) # Row 1 height set to 20 # # If you wish to set the format without changing the height you can - # pass nil as the height parameter: + # pass +nil+ as the height parameter: # # worksheet.set_row(0, nil, format) # - # The format parameter will be applied to any cells in the row that + # The +format+ parameter will be applied to any cells in the row that # don't have a format. For example # # worksheet.set_row(0, nil, format1) # Set the format for row 1 # worksheet.write('A1', 'Hello') # Defaults to format1 # worksheet.write('B1', 'Hello', format2) # Keeps format2 # # If you wish to define a row format in this way you should call the - # method before any calls to write(). Calling it afterwards will overwrite + # method before any calls to {#write()}[#method-i-write]. Calling it afterwards will overwrite # any format that was previously specified. # - # The hidden parameter should be set to 1 if you wish to hide a row. + # The +hidden+ parameter should be set to 1 if you wish to hide a row. # This can be used, for example, to hide intermediary steps in a # complicated calculation: # # worksheet.set_row(0, 20, format, 1) # worksheet.set_row(1, nil, nil, 1) # - # The level parameter is used to set the outline level of the row. + # The +level+ parameter is used to set the outline level of the row. # Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent # rows with the same outline level are grouped together into a single # outline. # # The following example sets an outline level of 1 for rows 1 # and 2 (zero-indexed): # # worksheet.set_row(1, nil, nil, 0, 1) # worksheet.set_row(2, nil, nil, 0, 1) # - # The hidden parameter can also be used to hide collapsed outlined rows - # when used in conjunction with the level parameter. + # The +hidden+ parameter can also be used to hide collapsed outlined rows + # when used in conjunction with the +level+ parameter. # # worksheet.set_row(1, nil, nil, 1, 1) # worksheet.set_row(2, nil, nil, 1, 1) # # For collapsed outlines you should also indicate which row has the - # collapsed + symbol using the optional collapsed parameter. + # collapsed + symbol using the optional +collapsed+ parameter. # # worksheet.set_row(3, nil, nil, 0, 0, 1) # # For a more complete example see the outline.rb and outline_collapsed.rb # programs in the examples directory of the distro. # - # Excel allows up to 7 outline levels. Therefore the level parameter - # should be in the range 0 <= level <= 7. + # Excel allows up to 7 outline levels. Therefore the +level+ parameter + # should be in the range <tt>0 <= level <= 7</tt>. # + # == OUTLINES AND GROUPING IN EXCEL + # + # Excel allows you to group rows or columns so that they can be hidden or + # displayed with a single mouse click. This feature is referred to as + # outlines. + # + # Outlines can reduce complex data down to a few salient sub-totals or + # summaries. + # + # This feature is best viewed in Excel but the following is an ASCII + # representation of what a worksheet with three outlines might look like. + # Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at + # level 1. The lines at the left hand side are called outline level bars. + # + # ------------------------------------------ + # 1 2 3 | | A | B | C | D | ... + # ------------------------------------------ + # _ | 1 | A | | | | ... + # | _ | 2 | B | | | | ... + # | | | 3 | (C) | | | | ... + # | | | 4 | (D) | | | | ... + # | - | 5 | E | | | | ... + # | _ | 6 | F | | | | ... + # | | | 7 | (G) | | | | ... + # | | | 8 | (H) | | | | ... + # | - | 9 | I | | | | ... + # - | . | ... | ... | ... | ... | ... + # + # Clicking the minus sign on each of the level 2 outlines will collapse + # and hide the data as shown in the next figure. The minus sign changes + # to a plus sign to indicate that the data in the outline is hidden. + # + # ------------------------------------------ + # 1 2 3 | | A | B | C | D | ... + # ------------------------------------------ + # _ | 1 | A | | | | ... + # | | 2 | B | | | | ... + # | + | 5 | E | | | | ... + # | | 6 | F | | | | ... + # | + | 9 | I | | | | ... + # - | . | ... | ... | ... | ... | ... + # + # Clicking on the minus sign on the level 1 outline will collapse the + # remaining rows as follows: + # + # ------------------------------------------ + # 1 2 3 | | A | B | C | D | ... + # ------------------------------------------ + # | 1 | A | | | | ... + # + | . | ... | ... | ... | ... | ... + # + # Grouping in WritXLSX is achieved by setting the outline level via the + # set_row() and set_column() worksheet methods: + # + # set_row(row, height, format, hidden, level, collapsed) + # set_column(first_col, last_col, width, format, hidden, level, collapsed) + # + # The following example sets an outline level of 1 for rows 1 and 2 + # (zero-indexed) and columns B to G. The parameters $height and $XF are + # assigned default values since they are undefined: + # + # worksheet.set_row(1, nil, nil, 0, 1) + # worksheet.set_row(2, nil, nil, 0, 1) + # worksheet.set_column('B:G', nil, nil, 0, 1) + # + # Excel allows up to 7 outline levels. Therefore the +level+ parameter + # should be in the range <tt>0 <= $level <= 7</tt>. + # + # Rows and columns can be collapsed by setting the +hidden+ flag for the + # hidden rows/columns and setting the +collapsed+ flag for the row/column + # that has the collapsed + symbol: + # + # worksheet.set_row(1, nil, nil, 1, 1) + # worksheet.set_row(2, nil, nil, 1, 1) + # worksheet.set_row(3, nil, nil, 0, 0, 1) # Collapsed flag. + # + # worksheet.set_column('B:G', nil, nil, 1, 1) + # worksheet.set_column('H:H', nil, nil, 0, 0, 1) # Collapsed flag. + # + # Note: Setting the $collapsed flag is particularly important for + # compatibility with OpenOffice.org and Gnumeric. + # + # For a more complete example see the outline.rb and outline_collapsed.rb + # programs in the examples directory of the distro. + # + # Some additional outline properties can be set via the outline_settings() + # worksheet method, see above. + # def set_row(*args) row = args[0] height = args[1] || @default_height xf = args[2] hidden = args[3] || 0 @@ -3125,13 +3108,32 @@ end # # merge_range(first_row, first_col, last_row, last_col, string, format) # - # Merge a range of cells. The first cell should contain the data and the others - # should be blank. All cells should contain the same format. + # Merge a range of cells. The first cell should contain the data and the + # others should be blank. All cells should contain the same format. # + # The merge_range() method allows you to merge cells that contain other + # types of alignment in addition to the merging: + # + # format = workbook.add_format( + # :border => 6, + # :valign => 'vcenter', + # :align => 'center' + # ) + # + # worksheet.merge_range('B3:D4', 'Vertical and horizontal', format) + # + # merge_range() writes its +token+ argument using the worksheet + # {#write()}[#method-i-write] method. Therefore it will handle numbers, + # strings, formulas or urls as required. If you need to specify the + # required write_*() method use the merge_range_type() method, see below. + # + # The full possibilities of this method are shown in the merge3.rb to + # merge6.rb programs in the examples directory of the distribution. + # def merge_range(*args) row_first, col_first, row_last, col_last, string, format, *extra_args = row_col_notation(args) raise "Incorrect number of arguments" if [row_first, col_first, row_last, col_last, format].include?(nil) raise "Fifth parameter must be a format object" unless format.respond_to?(:xf_index) @@ -3154,12 +3156,44 @@ # Pad out the rest of the area with formatted blank cells. write_formatted_blank_to_area(row_first, row_last, col_first, col_last, format) end # - # Same as merge_range() above except the type of write() is specified. + # Same as merge_range() above except the type of + # {#write()}[#method-i-write] is specified. # + # The merge_range() method, see above, uses write() to insert the required + # data into to a merged range. However, there may be times where this + # isn't what you require so as an alternative the merge_range_type () + # method allows you to specify the type of data you wish to write. + # For example: + # + # worksheet.merge_range_type('number', 'B2:C2', 123, format1) + # worksheet.merge_range_type('string', 'B4:C4', 'foo', format2) + # worksheet.merge_range_type('formula', 'B6:C6', '=1+2', format3) + # + # The +type+ must be one of the following, which corresponds to a write_*() + # method: + # + # 'number' + # 'string' + # 'formula' + # 'array_formula' + # 'blank' + # 'rich_string' + # 'date_time' + # 'url' + # + # Any arguments after the range should be whatever the appropriate method + # accepts: + # + # worksheet.merge_range_type('rich_string', 'B8:C8', + # 'This is ', bold, 'bold', format4) + # + # Note, you must always pass a format object as an argument, even if it is + # a default format. + # def merge_range_type(type, *args) case type when 'array_formula', 'blank', 'rich_string' row_first, col_first, row_last, col_last, *others = row_col_notation(args) format = others.pop @@ -3213,52 +3247,44 @@ # # :call-seq: # conditional_formatting(cell_or_cell_range, options) # - # This method handles the interface to Excel conditional formatting. + # Conditional formatting is a feature of Excel which allows you to apply a + # format to a cell or a range of cells based on a certain criteria. # - # This method contains a lot of parameters and is described in detail in - # the section below. + # For example the following criteria is used to highlight cells >= 50 in + # red in the conditional_format.rb example from the distro. # - # We allow the format to be called on one cell or a range of cells. The - # hashref contains the formatting parameters and must be the last param: - # - # conditional_formatting(row, col, {...}) - # conditional_formatting(first_row, first_col, last_row, last_col, {...}) - # - # The conditional_format() method is used to add formatting to a cell - # or range of cells based on user defined criteria. - # # worksheet.conditional_formatting('A1:J10', # { # :type => 'cell', # :criteria => '>=', # :value => 50, # :format => format1 # } # ) # - # See also the conditional_format.rb program in the examples directory of - # the distro. + # http://jmcnamara.github.com/excel-writer-xlsx/images/examples/conditional_example.jpg # # The conditional_formatting method is used to apply formatting based # on user defined criteria to an write_xlsx file. # # It can be applied to a single cell or a range of cells. - # You can pass 3 parameters such as (row, col, {...}) - # or 5 parameters such as (first_row, first_col, last_row, last_col, {...}). + # You can pass 3 parameters such as (+row+, +col+, {...}) + # or 5 parameters such as (+first_row+, +first_col+, +last_row+, +last_col+, {...}). # You can also use A1 style notation. For example: # # worksheet.conditional_formatting( 0, 0, {...} ) # worksheet.conditional_formatting( 0, 0, 4, 1, {...} ) # # # Which are the same as: # # worksheet.conditional_formatting( 'A1', {...} ) # worksheet.conditional_formatting( 'A1:B5', {...} ) # + # # Using A1 style notation is is also possible to specify # non-contiguous ranges, separated by a comma. For example: # # worksheet.conditional_formatting( 'A1:D5,A8:D12', {...} ) # The last parameter in conditional_formatting must be a hash containing @@ -3270,10 +3296,11 @@ # :format # :criteria # :value # :minimum # :maximum + # # Other, less commonly used parameters are: # # :min_type # :mid_type # :max_type @@ -3282,18 +3309,19 @@ # :max_value # :min_color # :mid_color # :max_color # :bar_color + # # Additional parameters which are used for specific conditional format types # are shown in the relevant sections below. # - # == :type + # === :type # # This parameter is passed in a hash to conditional_formatting. # - # The type parameter is used to set the type of conditional formatting + # The +:type+ parameter is used to set the type of conditional formatting # that you wish to apply. It is always required and it has no default value. # Allowable type values and their associated parameters are: # # Type Parameters # ====== ========== @@ -3337,14 +3365,15 @@ # '3_color_scale' (none) # # 'data_bar' (none) # # 'formula' :criteria + # # All conditional formatting types have a format parameter, see below. # Other types and parameters such as icon sets will be added in time. # - # == :type => 'cell' + # === :type => 'cell' # # This is the most common conditional formatting type. It is used when # a format is applied to a cell based on a simple criterion. For example: # # worksheet.conditional_formatting( 'A1', @@ -3364,13 +3393,13 @@ # :minimum => 20, # :maximum => 30, # :format => green_format # } # ) - # == :criteria + # === :criteria # - # The criteria parameter is used to set the criteria by which the cell data + # The +:criteria+ parameter is used to set the criteria by which the cell data # will be evaluated. It has no default value. The most common criteria # as applied to { type => 'cell' } are: # # 'between' # 'not between' @@ -3378,34 +3407,37 @@ # 'not equal to' | '!=' | '<>' # 'greater than' | '>' # 'less than' | '<' # 'greater than or equal to' | '>=' # 'less than or equal to' | '<=' + # # You can either use Excel's textual description strings, # in the first column above, or the more common symbolic alternatives. # # Additional criteria which are specific to other conditional format types # are shown in the relevant sections below. # - # == :value + # === :value # - # The value is generally used along with the criteria parameter to set the + # The +:value+ is generally used along with the criteria parameter to set the # rule by which the cell data will be evaluated. # # :type => 'cell', # :criteria => '>', # :value => 5 # :format => format - # The value property can also be an cell reference. # + # The +:value+ property can also be an cell reference. + # # :type => 'cell', # :criteria => '>', # :value => '$C$1', # :format => format - # == :format # - # The format parameter is used to specify the format that will be applied + # === :format + # + # The +:format+ parameter is used to specify the format that will be applied # to the cell when the conditional formatting criterion is met. # The format is created using the add_format method in the same way as cell # formats: # # format = workbook.add_format( :bold => 1, :italic => 1 ) @@ -3416,10 +3448,11 @@ # :criteria => '>', # :value => 5 # :format => format # } # ) + # # The conditional format follows the same rules as in Excel: # it is superimposed over the existing cell format and not all font and # border properties can be modified. Font properties that can't be modified # are font name, font size, superscript and subscript. # The border property that cannot be modified is diagonal borders. @@ -3445,26 +3478,28 @@ # # format3 = workbook.add_format( # :bg_color => '#C6EFCE', # :color => '#006100' # ) - # == :minimum # - # The minimum parameter is used to set the lower limiting value when the - # criteria is either 'between' or 'not between': + # === :minimum # + # The +:minimum+ parameter is used to set the lower limiting value when the + # +:criteria+ is either 'between' or 'not between': + # # :validate => 'integer', # :criteria => 'between', # :minimum => 1, # :maximum => 100 - # == :maximum # - # The maximum parameter is used to set the upper limiting value when the - # criteria is either 'between' or 'not between'. See the previous example. + # === :maximum # - # == :type => 'date' + # The +:maximum+ parameter is used to set the upper limiting value when the + # +:criteria+ is either 'between' or 'not between'. See the previous example. # + # === :type => 'date' + # # The date type is the same as the cell type and uses the same criteria # and values. However it allows the value, minimum and maximum properties # to be specified in the ISO8601 yyyy-mm-ddThh:mm:ss.sss date format which # is detailed in the write_date_time() method. # @@ -3474,22 +3509,24 @@ # :criteria => 'greater than', # :value => '2011-01-01T', # :format => format # } # ) - # == :type => 'time_period' # + # === :type => 'time_period' + # # The time_period type is used to specify Excel's "Dates Occurring" style # conditional format. # # worksheet.conditional_formatting( 'A1:A4', # { # :type => 'time_period', # :criteria => 'yesterday', # :format => format # } # ) + # # The period is set in the criteria and can have one of the following # values: # # :criteria => 'yesterday', # :criteria => 'today', @@ -3498,12 +3535,13 @@ # :criteria => 'this week', # :criteria => 'next week', # :criteria => 'last month', # :criteria => 'this month', # :criteria => 'next month' - # == :type => 'text' # + # === :type => 'text' + # # The text type is used to specify Excel's "Specific Text" style conditional # format. It is used to do simple string matching using the criteria and # value parameters: # # worksheet.conditional_formatting( 'A1:A4', @@ -3512,19 +3550,21 @@ # :criteria => 'containing', # :value => 'foo', # :format => format # } # ) + # # The criteria can have one of the following values: # # :criteria => 'containing', # :criteria => 'not containing', # :criteria => 'begins with', # :criteria => 'ends with' + # # The value parameter should be a string or single character. # - # == :type => 'average' + # === :type => 'average' # # The average type is used to specify Excel's "Average" style conditional # format. # # worksheet.conditional_formatting( 'A1:A4', @@ -3532,10 +3572,11 @@ # :type => 'average', # :criteria => 'above', # :format => format # } # ) + # # The type of average for the conditional format range is specified by the # criteria: # # :criteria => 'above', # :criteria => 'below', @@ -3545,42 +3586,46 @@ # :criteria => '1 std dev below', # :criteria => '2 std dev above', # :criteria => '2 std dev below', # :criteria => '3 std dev above', # :criteria => '3 std dev below' - # == :type => 'duplicate' # + # === :type => 'duplicate' + # # The duplicate type is used to highlight duplicate cells in a range: # # worksheet.conditional_formatting( 'A1:A4', # { # :type => 'duplicate', # :format => format # } # ) - # == :type => 'unique' # + # === :type => 'unique' + # # The unique type is used to highlight unique cells in a range: # # worksheet.conditional_formatting( 'A1:A4', # { # :type => 'unique', # :format => format # } # ) - # == :type => 'top' # + # === :type => 'top' + # # The top type is used to specify the top n values by number or percentage # in a range: # # worksheet.conditional_formatting( 'A1:A4', # { # :type => 'top', # :value => 10, # :format => format # } # ) + # # The criteria can be used to indicate that a percentage condition is # required: # # worksheet.conditional_formatting( 'A1:A4', # { @@ -3588,97 +3633,105 @@ # :value => 10, # :criteria => '%', # :format => format # } # ) - # == :type => 'bottom' # + # === :type => 'bottom' + # # The bottom type is used to specify the bottom n values by number or # percentage in a range. # # It takes the same parameters as top, see above. # - # == :type => 'blanks' + # === :type => 'blanks' # # The blanks type is used to highlight blank cells in a range: # # worksheet.conditional_formatting( 'A1:A4', # { # :type => 'blanks', # :format => format # } # ) - # == :type => 'no_blanks' # + # === :type => 'no_blanks' + # # The no_blanks type is used to highlight non blank cells in a range: # # worksheet.conditional_formatting( 'A1:A4', # { # :type => 'no_blanks', # :format => format # } # ) - # == :type => 'errors' # + # === :type => 'errors' + # # The errors type is used to highlight error cells in a range: # # worksheet.conditional_formatting( 'A1:A4', # { # :type => 'errors', # :format => format # } # ) - # == :type => 'no_errors' # + # === :type => 'no_errors' + # # The no_errors type is used to highlight non error cells in a range: # # worksheet.conditional_formatting( 'A1:A4', # { # :type => 'no_errors', # :format => format # } # ) - # == :type => '2_color_scale' # + # === :type => '2_color_scale' + # # The 2_color_scale type is used to specify Excel's "2 Color Scale" style # conditional format. # # worksheet.conditional_formatting( 'A1:A12', # { # :type => '2_color_scale' # } # ) + # # At the moment only the default colors and properties can be used. These # will be extended in time. # - # == :type => '3_color_scale' + # === :type => '3_color_scale' # # The 3_color_scale type is used to specify Excel's "3 Color Scale" style # conditional format. # # worksheet.conditional_formatting( 'A1:A12', # { # :type => '3_color_scale' # } # ) + # # At the moment only the default colors and properties can be used. # These will be extended in time. # - # == :type => 'data_bar' + # === :type => 'data_bar' # # The data_bar type is used to specify Excel's "Data Bar" style conditional # format. # # worksheet.conditional_formatting( 'A1:A12', # { # :type => 'data_bar', # } # ) + # # At the moment only the default colors and properties can be used. These # will be extended in time. # - # == :type => 'formula' + # === :type => 'formula' # # The formula type is used to specify a conditional format based on # a user defined formula: # # worksheet.conditional_formatting( 'A1:A4', @@ -3686,13 +3739,14 @@ # :type => 'formula', # :criteria => '=$A$1 > 5', # :format => format # } # ) + # # The formula is specified in the criteria. # - # == :min_type, :mid_type, :max_type + # === :min_type, :mid_type, :max_type # # The min_type and max_type properties are available when the conditional # formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_type # is available for 3_color_scale. The properties are used as follows: # @@ -3701,31 +3755,35 @@ # :type => '2_color_scale', # :min_type => 'percent', # :max_type => 'percent' # } # ) + # # The available min/mid/max types are: # # 'num' # 'percent' # 'percentile' # 'formula' - # == :min_value, :mid_value, :max_value # - # The min_value and max_value properties are available when the conditional - # formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_value - # is available for 3_color_scale. The properties are used as follows: + # === :min_value, :mid_value, :max_value # + # The +:min_value+ and +:max_value+ properties are available when the + # conditional formatting type is 2_color_scale, 3_color_scale or + # data_bar. The +:mid_value+ is available for 3_color_scale. The properties + # are used as follows: + # # worksheet.conditional_formatting( 'A1:A12', # { # :type => '2_color_scale', # :min_value => 10, # :max_value => 90 # } # ) - # == :min_color, :mid_color, :max_color, :bar_color # + # === :min_color, :mid_color, :max_color, :bar_color + # # The min_color and max_color properties are available when the conditional # formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_color # is available for 3_color_scale. The properties are used as follows: # # worksheet.conditional_formatting( 'A1:A12', @@ -3733,14 +3791,15 @@ # ;type => '2_color_scale', # :min_color => "#C5D9F1", # :max_color => "#538ED5" # } # ) + # # The color can be specifies as an Excel::Writer::XLSX color index or, # more usefully, as a HTML style RGB hex number, as shown above. # - # == Conditional Formatting Examples + # === Conditional Formatting Examples # # === Example 1. Highlight cells greater than an integer value. # # worksheet.conditional_formatting( 'A1:F10', # { @@ -3837,28 +3896,327 @@ @cond_formats[cond_format.range] ||= [] @cond_formats[cond_format.range] << cond_format end # + # :call-seq: + # add_table(row1, col1, row2, col2, properties) + # # Add an Excel table to a worksheet. # # The add_table() method is used to group a range of cells into # an Excel Table. # # worksheet.add_table('B3:F7', { ... } ) # # This method contains a lot of parameters and is described - # in detail in a separate section "TABLES IN EXCEL". + # in detail in a section + # {"TABLES IN EXCEL"}[#method-i-add_table-label-TABLES+IN+EXCEL]. # # See also the tables.rb program in the examples directory of the distro # + # ==TABLES IN EXCEL + # + # Tables in Excel are a way of grouping a range of cells into a single + # entity that has common formatting or that can be referenced from + # formulas. Tables can have column headers, autofilters, total rows, + # column formulas and default formatting. + # + # http://jmcnamara.github.com/excel-writer-xlsx/images/examples/tables.jpg + # + # For more information see "An Overview of Excel Tables" + # http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx. + # + # Tables are added to a worksheet using the add_table() method: + # + # worksheet.add_table('B3:F7', parameters) + # + # The data range can be specified in 'A1' or 'row/col' notation (see also + # the note about + # {"Cell notation"}[#label-Cell+notation] for more information. + # + # worksheet.add_table('B3:F7') + # + # # Same as: + # worksheet.add_table(2, 1, 6, 5) + # + # The last parameter in add_table() should be a hash ref containing the + # parameters that describe the table options and data. The available + # parameters are: + # + # :data + # :autofilter + # :header_row + # :banded_columns + # :banded_rows + # :first_column + # :last_column + # :style + # :total_row + # :columns + # :name + # + # The table parameters are detailed below. There are no required parameters + # and the hash ref isn't required if no options are specified. + # + # ===:data + # + # The +:data+ parameter can be used to specify the data in the cells of the + # table. + # + # data = [ + # [ 'Apples', 10000, 5000, 8000, 6000 ], + # [ 'Pears', 2000, 3000, 4000, 5000 ], + # [ 'Bananas', 6000, 6000, 6500, 6000 ], + # [ 'Oranges', 500, 300, 200, 700 ] + # ] + # + # worksheet.add_table('B3:F7', :data => data) + # + # Table data can also be written separately, as an array or individual + # cells. + # + # # These two statements are the same as the single statement above. + # worksheet.add_table('B3:F7') + # worksheet.write_col('B4', data) + # + # Writing the cell data separately is occasionally required when you need + # to control the write_*() method used to populate the cells or if you + # wish to tweak the cell formatting. + # + # The data structure should be an array ref of array refs holding row data + # as shown above. + # + # ===:header_row + # + # The +:header_row+ parameter can be used to turn on or off the header row + # in the table. It is on by default. + # + # worksheet.add_table('B4:F7', :header_row => 0) # Turn header off. + # + # The header row will contain default captions such as Column 1, Column 2, + # etc. These captions can be overridden using the +:columns+ parameter + # below. + # + # ===:autofilter + # + # The +:autofilter+ parameter can be used to turn on or off the autofilter + # in the header row. It is on by default. + # + # worksheet.add_table('B3:F7', :autofilter => 0) # Turn autofilter off. + # + # The +:autofilter+ is only shown if the +:header_row+ is on. Filters + # within the table are not supported. + # + # ===:banded_rows + # + # The +:banded_rows+ parameter can be used to used to create rows of + # alternating colour in the table. It is on by default. + # + # worksheet.add_table('B3:F7', :banded_rows => 0) + # + # ===:banded_columns + # + # The +:banded_columns+ parameter can be used to used to create columns + # of alternating colour in the table. It is off by default. + # + # worksheet.add_table('B3:F7', :banded_columns => 1) + # + # ===:first_column + # + # The +:first_column+ parameter can be used to highlight the first column + # of the table. The type of highlighting will depend on the style of the + # table. It may be bold text or a different colour. It is off by default. + # + # worksheet.add_table('B3:F7', :first_column => 1) + # + # ===:last_column + # + # The +:last_column+ parameter can be used to highlight the last column + # of the table. The type of highlighting will depend on the style of the + # table. It may be bold text or a different colour. It is off by default. + # + # worksheet.add_table('B3:F7', :last_column => 1) + # + # ===:style + # + # The +:style+ parameter can be used to set the style of the table. + # Standard Excel table format names should be used (with matching + # capitalisation): + # + # worksheet11.add_table( + # 'B3:F7', + # { + # :data => data, + # :style => 'Table Style Light 11' + # } + # ) + # + # The default table style is 'Table Style Medium 9'. + # + # ===:name + # + # The +:name+ parameter can be used to set the name of the table. + # + # By default tables are named Table1, Table2, etc. If you override the + # table name you must ensure that it doesn't clash with an existing table + # name and that it follows Excel's requirements for table names. + # + # worksheet.add_table('B3:F7', :name => 'SalesData') + # + # If you need to know the name of the table, for example to use it in a + # formula, you can get it as follows: + # + # table = worksheet2.add_table('B3:F7') + # table_name = table.name + # + # ===:total_row + # + # The +:total_row+ parameter can be used to turn on the total row in the + # last row of a table. It is distinguished from the other rows by a + # different formatting and also with dropdown SUBTOTAL functions. + # + # worksheet.add_table('B3:F7', :total_row => 1) + # + # The default total row doesn't have any captions or functions. These must + # by specified via the +:columns+ parameter below. + # + # ===:columns + # + # The +:columns+ parameter can be used to set properties for columns + # within the table. + # + # The sub-properties that can be set are: + # + # :header + # :formula + # :total_string + # :total_function + # :format + # + # The column data must be specified as an array of hash. For example to + # override the default 'Column n' style table headers: + # + # worksheet.add_table( + # 'B3:F7', + # { + # :data => data, + # :columns => [ + # { :header => 'Product' }, + # { :header => 'Quarter 1' }, + # { :header => 'Quarter 2' }, + # { :header => 'Quarter 3' }, + # { :header => 'Quarter 4' } + # ] + # } + # ) + # + # If you don't wish to specify properties for a specific column you pass + # an empty hash and the defaults will be applied: + # + # ... + # :columns => [ + # { :header => 'Product' }, + # { :header => 'Quarter 1' }, + # { }, # Defaults to 'Column 3'. + # { :header => 'Quarter 3' }, + # { :header => 'Quarter 4' } + # ] + # ... + # + # Column formulas can by applied using the formula column property: + # + # 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]])' + # } + # ] + # } + # ) + # + # The Excel 2007 [#This Row] and Excel 2010 @ structural references are + # supported within the formula. + # + # As stated above the total_row table parameter turns on the "Total" row + # in the table but it doesn't populate it with any defaults. Total + # captions and functions must be specified via the columns property and + # the total_string and total_function sub properties: + # + # worksheet10.add_table( + # 'B3:F8', + # { + # :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' } + # ] + # } + # ) + # + # The supported totals row SUBTOTAL functions are: + # + # average + # count_nums + # count + # max + # min + # std_dev + # sum + # var + # + # User defined functions or formulas aren't supported. + # + # Format can also be applied to columns: + # + # currency_format = workbook.add_format(:num_format => '$#,##0') + # + # worksheet.add_table( + # 'B3:D8', + # { + # :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 + # } + # ] + # } + # ) + # + # Standard WriteXLSX format objects can be used. However, they should be + # limited to numerical formats. Overriding other table formatting may + # produce inconsistent results. + # def add_table(*args) # Table count is a member of Workbook, global to all Worksheet. @workbook.table_count += 1 - table = Package::Table.new(self, @workbook.table_count, *args) + id = @workbook.table_count + table = Package::Table.new(self, id, *args) - @external_table_links << ['/table', "../tables/table#{table.id}.xml"] + @external_table_links << ['/table', "../tables/table#{id}.xml"] @tables << table table end # @@ -3881,10 +4239,12 @@ # :type => 'column', # :style => 12 # } # ) # + # http://jmcnamara.github.com/excel-writer-xlsx/images/examples/sparklines1.jpg + # # Note: Sparklines are a feature of Excel 2010+ only. You can write them # to an XLSX file that can be read by Excel 2007 but they won't be # displayed. # # The add_sparkline() worksheet method is used to add sparklines to a @@ -3927,222 +4287,252 @@ # # ===:location # # This is the cell where the sparkline will be displayed: # - # location => 'F1' - # The location should be a single cell. (For multiple cells see "Grouped Sparklines" below). + # :location => 'F1' # - # To specify the location in row-column notation use the xl_rowcol_to_cell() function from the Excel::Writer::XLSX::Utility module. + # The location should be a single cell. (For multiple cells see + # {"Grouped Sparklines"}[#method-i-add_sparkline-label-Grouped+Sparklines] + # below). # - # use Excel::Writer::XLSX::Utility ':rowcol'; + # To specify the location in row-column notation use the + # xl_rowcol_to_cell() function from the Writexlsx::Utility module. + # + # include Writexlsx::Utility # ... # location => xl_rowcol_to_cell( 0, 5 ), # F1 - # range # + # ===:range + # # This specifies the cell data range that the sparkline will plot: # - # $worksheet->add_sparkline( + # worksheet.add_sparkline( # { - # location => 'F1', - # range => 'A1:E1', + # :location => 'F1', + # :range => 'A1:E1' # } - # ); - # The range should be a 2D array. (For 3D arrays of cells see "Grouped Sparklines" below). + # ) # - # If range is not on the same worksheet you can specify its location using the usual Excel notation: + # The range should be a 2D array. (For 3D arrays of cells see + # {"Grouped Sparklines"}[#method-i-add_sparkline-label-Grouped+Sparklines] + # below). # - # range => 'Sheet1!A1:E1', - # If the worksheet contains spaces or special characters you should quote the worksheet name in the same way that Excel does: + # If range is not on the same worksheet you can specify its location using + # the usual Excel notation: # - # range => q('Monthly Data'!A1:E1), - # To specify the location in row-column notation use the xl_range() or xl_range_formula() functions from the Excel::Writer::XLSX::Utility module. + # Lrange => 'Sheet1!A1:E1' # - # use Excel::Writer::XLSX::Utility ':rowcol'; + # If the worksheet contains spaces or special characters you should quote + # the worksheet name in the same way that Excel does: + # + # :range => q('Monthly Data'!A1:E1) + # + # To specify the location in row-column notation use the xl_range() or + # xl_range_formula() functions from the Writexlsx::Utility module. + # + # include Writexlsx::Utility # ... # range => xl_range( 1, 1, 0, 4 ), # 'A1:E1' # range => xl_range_formula( 'Sheet1', 0, 0, 0, 4 ), # 'Sheet1!A2:E2' - # type # + # ===:type + # # Specifies the type of sparkline. There are 3 available sparkline types: # - # line (default) - # column - # win_loss + # :line (default) + # :column + # :win_loss + # # For example: # # { - # location => 'F1', - # range => 'A1:E1', - # type => 'column', + # :location => 'F1', + # :range => 'A1:E1', + # :type => 'column' # } - # style # - # Excel provides 36 built-in Sparkline styles in 6 groups of 6. The style parameter can be used to replicate these and should be a corresponding number from 1 .. 36. + # ===:style # + # Excel provides 36 built-in Sparkline styles in 6 groups of 6. The style + # parameter can be used to replicate these and should be a corresponding + # number from 1 .. 36. + # # { - # location => 'A14', - # range => 'Sheet2!A2:J2', - # style => 3, + # :location => 'A14', + # :range => 'Sheet2!A2:J2', + # :style => 3 # } - # The style number starts in the top left of the style grid and runs left to right. The default style is 1. It is possible to override colour elements of the sparklines using the *_color parameters below. # - # markers + # The style number starts in the top left of the style grid and runs left + # to right. The default style is 1. It is possible to override colour + # elements of the sparklines using the *_color parameters below. # + # ===:markers + # # Turn on the markers for line style sparklines. # # { - # location => 'A6', - # range => 'Sheet2!A1:J1', - # markers => 1, + # :location => 'A6', + # :range => 'Sheet2!A1:J1', + # :markers => 1 # } + # # Markers aren't shown in Excel for column and win_loss sparklines. # - # negative_points + # ===:negative_points # - # Highlight negative values in a sparkline range. This is usually required with win_loss sparklines. + # Highlight negative values in a sparkline range. This is usually required + # with win_loss sparklines. # # { - # location => 'A21', - # range => 'Sheet2!A3:J3', - # type => 'win_loss', - # negative_points => 1, + # :location => 'A21', + # :range => 'Sheet2!A3:J3', + # :type => 'win_loss', + # :negative_points => 1 # } - # axis # + # ===:axis + # # Display a horizontal axis in the sparkline: # # { - # location => 'A10', - # range => 'Sheet2!A1:J1', - # axis => 1, + # :location => 'A10', + # :range => 'Sheet2!A1:J1', + # :axis => 1 # } - # reverse # + # ===:reverse + # # Plot the data from right-to-left instead of the default left-to-right: # # { - # location => 'A24', - # range => 'Sheet2!A4:J4', - # type => 'column', - # reverse => 1, + # :location => 'A24', + # :range => 'Sheet2!A4:J4', + # :type => 'column', + # :reverse => 1 # } - # weight # + # ===:weight + # # Adjust the default line weight (thickness) for line style sparklines. # - # weight => 0.25, + # :weight => 0.25 + # # The weight value should be one of the following values allowed by Excel: # # 0.25 0.5 0.75 # 1 1.25 # 2.25 # 3 # 4.25 # 6 - # high_point, low_point, first_point, last_point # + # ===:high_point, low_point, first_point, last_point + # # Highlight points in a sparkline range. # - # high_point => 1, - # low_point => 1, - # first_point => 1, - # last_point => 1, - # max, min + # :high_point => 1, + # :low_point => 1, + # :first_point => 1, + # :last_point => 1 # + # ===:max, min + # # Specify the maximum and minimum vertical axis values: # - # max => 0.5, - # min => -0.5, - # As a special case you can set the maximum and minimum to be for a group of sparklines rather than one: + # :max => 0.5, + # :min => -0.5 # - # max => 'group', - # See "Grouped Sparklines" below. + # As a special case you can set the maximum and minimum to be for a group + # of sparklines rather than one: # - # empty_cells + # max => 'group' + # See + # {"Grouped Sparklines"}[#method-i-add_sparkline-label-Grouped+Sparklines] + # below. # + # ===:empty_cells + # # Define how empty cells are handled in a sparkline. # - # empty_cells => 'zero', + # :empty_cells => 'zero', + # # The available options are: # # gaps : show empty cells as gaps (the default). # zero : plot empty cells as 0. # connect: Connect points with a line ("line" type sparklines only). - # show_hidden # + # ===:show_hidden + # # Plot data in hidden rows and columns: # - # show_hidden => 1, + # :show_hidden => 1 + # # Note, this option is off by default. # - # date_axis + # ===:date_axis # - # Specify an alternative date axis for the sparkline. This is useful if the data being plotted isn't at fixed width intervals: + # Specify an alternative date axis for the sparkline. This is useful if + # the data being plotted isn't at fixed width intervals: # # { - # location => 'F3', - # range => 'A3:E3', - # date_axis => 'A4:E4', + # :location => 'F3', + # :range => 'A3:E3', + # :date_axis => 'A4:E4' # } - # The number of cells in the date range should correspond to the number of cells in the data range. # - # series_color + # The number of cells in the date range should correspond to the number + # of cells in the data range. # - # It is possible to override the colour of a sparkline style using the following parameters: + # ===:series_color # - # series_color - # negative_color - # markers_color - # first_color - # last_color - # high_color - # low_color + # It is possible to override the colour of a sparkline style using the + # following parameters: + # + # :series_color + # :negative_color + # :markers_color + # :first_color + # :last_color + # :high_color + # :low_color + # # The color should be specified as a HTML style #rrggbb hex value: # # { - # location => 'A18', - # range => 'Sheet2!A2:J2', - # type => 'column', - # series_color => '#E965E0', + # :location => 'A18', + # :range => 'Sheet2!A2:J2', + # :type => 'column', + # :series_color => '#E965E0' # } - # Grouped Sparklines # - # The add_sparkline() worksheet method can be used multiple times to write as many sparklines as are required in a worksheet. + # ==Grouped Sparklines # - # However, it is sometimes necessary to group contiguous sparklines so that changes that are applied to one are applied to all. In Excel this is achieved by selecting a 3D range of cells for the data range and a 2D range of cells for the location. + # The add_sparkline() worksheet method can be used multiple times to write + # as many sparklines as are required in a worksheet. # - # In Excel::Writer::XLSX, you can simulate this by passing an array refs of values to location and range: + # However, it is sometimes necessary to group contiguous sparklines so that + # changes that are applied to one are applied to all. In Excel this is + # achieved by selecting a 3D range of cells for the data range and a + # 2D range of cells for the location. # + # In WriteXLSX, you can simulate this by passing an array of values to + # location and range: + # # { - # location => [ 'A27', 'A28', 'A29' ], - # range => [ 'Sheet2!A5:J5', 'Sheet2!A6:J6', 'Sheet2!A7:J7' ], - # markers => 1, + # :location => [ 'A27', 'A28', 'A29' ], + # :range => [ 'Sheet2!A5:J5', 'Sheet2!A6:J6', 'Sheet2!A7:J7' ], + # :markers => 1 # } - # Sparkline examples # - # See the sparklines1.pl and sparklines2.pl example programs in the examples directory of the distro. + # ===Sparkline examples # - # The add_sparkline worksheet method is used to add sparklines to a cell or a range of cells. + # See the sparklines1.rb and sparklines2.rb example programs in the + # examples directory of the distro. # - # worksheet.add_sparkline( - # { - # :location => 'F2', - # :range => 'Sheet1!A2:E2', - # :type => 'column', - # :style => 12 - # } - # ) - # - # See also the sparklines1.rb and sparklines2.rb example programs in the examples directory of the distro. - # - # Note: Sparklines are a feature of Excel 2010+ only. - # You can write them to an XLSX file that can be read by Excel 2007 but they won't be displayed. - # - # Sparklines are a feature of Excel 2010+ which allows you to add small charts to worksheet cells. - # These are useful for showing visual trends in data in a compact format. - # def add_sparkline(param) @sparklines << Sparkline.new(self, param, quote_sheetname(@name)) end # @@ -4329,66 +4719,82 @@ # :date # :time # :length # :custom # - # :any is used to specify that the type of data is unrestricted. + # +:any+ is used to specify that the type of data is unrestricted. # This is the same as not applying a data validation. It is only # provided for completeness and isn't used very often in the # context of WriteXLSX. # - # :integer restricts the cell to integer values. Excel refers to this + # +:integer+ restricts the cell to integer values. Excel refers to this # as 'whole number'. + # # :validate => 'integer', # :criteria => '>', # :value => 100, - # :decimal restricts the cell to decimal values. + # + # +:decimal+ restricts the cell to decimal values. + # # :validate => 'decimal', # :criteria => '>', # :value => 38.6, - # :list restricts the cell to a set of user specified values. These + # + # +:list+ restricts the cell to a set of user specified values. These # can be passed in an array ref or as a cell range (named ranges aren't # currently supported): + # # :validate => 'list', # :value => ['open', 'high', 'close'], # # Or like this: # :value => 'B1:B3', + # # Excel requires that range references are only to cells on the same # worksheet. # - # :date restricts the cell to date values. Dates in Excel are expressed + # +:date+ restricts the cell to date values. Dates in Excel are expressed # as integer values but you can also pass an ISO860 style string as used - # in write_date_time(). See also "DATES AND TIME IN EXCEL" for more - # information about working with Excel's dates. + # in write_date_time(). See also + # {"DATES AND TIME IN EXCEL"}[#method-i-write_date_time-label-DATES+AND+TIME+IN+EXCEL] + # for more information about working with Excel's dates. + # # :validate => 'date', # :criteria => '>', # :value => 39653, # 24 July 2008 # # Or like this: # :value => '2008-07-24T', - # :time restricts the cell to time values. Times in Excel are expressed + # + # +:time+ restricts the cell to time values. Times in Excel are expressed # as decimal values but you can also pass an ISO860 style string as used - # in write_date_time(). See also "DATES AND TIME IN EXCEL" for more - # information about working with Excel's times. + # in write_date_time(). See also + # {"DATES AND TIME IN EXCEL"}[#method-i-write_date_time-label-DATES+AND+TIME+IN+EXCEL] + # for more information about working with Excel's times. + # # :validate => 'time', # :criteria => '>', # :value => 0.5, # Noon # # Or like this: # :value => 'T12:00:00', - # :length restricts the cell data based on an integer string length. + # + # +:length+ restricts the cell data based on an integer string length. # Excel refers to this as 'Text length'. + # # :validate => 'length', # :criteria => '>', # :value => 10, - # :custom restricts the cell based on an external Excel formula + # + # +:custom+ restricts the cell based on an external Excel formula # that returns a TRUE/FALSE value. + # # :validate => 'custom', # :value => '=IF(A10>B10,TRUE,FALSE)', + # # ===criteria # # This parameter is passed in a hash ref to data_validation(). # - # The criteria parameter is used to set the criteria by which the data + # The +:criteria+ parameter is used to set the criteria by which the data # in the cell is validated. It is almost always required except for # the list and custom validate options. It has no default value. # Allowable values are: # # 'between' @@ -4418,14 +4824,15 @@ # :validate => 'list', # :value => ['open', 'high', 'close'], # # :validate => 'custom', # :value => '=IF(A10>B10,TRUE,FALSE)', - # ===value | minimum | source # - # This parameter is passed in a hash ref to data_validation(). + # ===:value | :minimum | :source # + # This parameter is passed in a hash to data_validation(). + # # The value parameter is used to set the limiting value to which the # criteria is applied. It is always required and it has no default value. # You can also use the synonyms minimum or source to make the validation # a little clearer and closer to Excel's description of the parameter: # @@ -4441,58 +4848,63 @@ # :maximum => 100, # # # Use 'source' # :validate => 'list', # :source => '$B$1:$B$3', - # ===maximum # + # ===:maximum + # # This parameter is passed in a hash ref to data_validation(). # - # The maximum parameter is used to set the upper limiting value when + # The +:maximum: parameter is used to set the upper limiting value when # the criteria is either 'between' or 'not between': # # :validate => 'integer', # :criteria => 'between', # :minimum => 1, # :maximum => 100, - # ===ignore_blank # + # ===:ignore_blank + # # This parameter is passed in a hash ref to data_validation(). # - # The ignore_blank parameter is used to toggle on and off the + # The +:ignore_blank+ parameter is used to toggle on and off the # 'Ignore blank' option in the Excel data validation dialog. When the # option is on the data validation is not applied to blank data in the # cell. It is on by default. # # :ignore_blank => 0, # Turn the option off - # ===dropdown # + # ===:dropdown + # # This parameter is passed in a hash ref to data_validation(). # - # The dropdown parameter is used to toggle on and off the + # The +:dropdown+ parameter is used to toggle on and off the # 'In-cell dropdown' option in the Excel data validation dialog. # When the option is on a dropdown list will be shown for list validations. # It is on by default. # # :dropdown => 0, # Turn the option off - # ===input_title # + # ===:input_title + # # This parameter is passed in a hash ref to data_validation(). # - # The input_title parameter is used to set the title of the input + # The +:input_title+ parameter is used to set the title of the input # message that is displayed when a cell is entered. It has no default # value and is only displayed if the input message is displayed. # See the input_message parameter below. # # :input_title => 'This is the input title', + # # The maximum title length is 32 characters. # - # ===input_message + # ===:input_message # # This parameter is passed in a hash ref to data_validation(). # - # The input_message parameter is used to set the input message that + # The +:input_message+ parameter is used to set the input message that # is displayed when a cell is entered. It has no default value. # # :validate => 'integer', # :criteria => 'between', # :minimum => 1, @@ -4505,38 +4917,38 @@ # # input_message => "This is\na test.", # # The maximum message length is 255 characters. # - # ===show_input + # ===:show_input # # This parameter is passed in a hash ref to data_validation(). # - # The show_input parameter is used to toggle on and off the 'Show input + # The +:show_input+ parameter is used to toggle on and off the 'Show input # message when cell is selected' option in the Excel data validation # dialog. When the option is off an input message is not displayed even # if it has been set using input_message. It is on by default. # # :show_input => 0, # Turn the option off # - # ===error_title + # ===:error_title # # This parameter is passed in a hash ref to data_validation(). # - # The error_title parameter is used to set the title of the error message + # The +:error_title+ parameter is used to set the title of the error message # that is displayed when the data validation criteria is not met. # The default error title is 'Microsoft Excel'. # # :error_title => 'Input value is not valid', # # The maximum title length is 32 characters. # - # ===error_message + # ===:error_message # # This parameter is passed in a hash ref to data_validation(). # - # The error_message parameter is used to set the error message that is + # The +:error_message+ parameter is used to set the error message that is # displayed when a cell is entered. The default error message is # "The value you entered is not valid.\nA user has restricted values # that can be entered into the cell.". # # :validate => 'integer', @@ -4551,84 +4963,85 @@ # # :input_message => "This is\na test.", # # The maximum message length is 255 characters. # - # ===error_type + # ===:error_type # # This parameter is passed in a hash ref to data_validation(). # - # The error_type parameter is used to specify the type of error dialog that is displayed. There are 3 options: + # The +:error_type+ parameter is used to specify the type of error dialog + # that is displayed. There are 3 options: # # 'stop' # 'warning' # 'information' # # The default is 'stop'. # - # ===show_error + # ===:show_error # # This parameter is passed in a hash ref to data_validation(). # - # The show_error parameter is used to toggle on and off the 'Show error + # The +:show_error+ parameter is used to toggle on and off the 'Show error # alert after invalid data is entered' option in the Excel data validation # dialog. When the option is off an error message is not displayed # even if it has been set using error_message. It is on by default. # # :show_error => 0, # Turn the option off # # ===Data Validation Examples # - # ====Example 1. Limiting input to an integer greater than a fixed value. + # ===Example 1. Limiting input to an integer greater than a fixed value. # # worksheet.data_validation('A1', # { # :validate => 'integer', # :criteria => '>', # :value => 0, # }); - # ====Example 2. Limiting input to an integer greater than a fixed value where the value is referenced from a cell. + # ===Example 2. Limiting input to an integer greater than a fixed value where the value is referenced from a cell. # # worksheet.data_validation('A2', # { # :validate => 'integer', # :criteria => '>', # :value => '=E3', # }); - # ====Example 3. Limiting input to a decimal in a fixed range. + # ===Example 3. Limiting input to a decimal in a fixed range. # # worksheet.data_validation('A3', # { # :validate => 'decimal', # :criteria => 'between', # :minimum => 0.1, # :maximum => 0.5, # }); - # ====Example 4. Limiting input to a value in a dropdown list. + # ===Example 4. Limiting input to a value in a dropdown list. # # worksheet.data_validation('A4', # { # :validate => 'list', # :source => ['open', 'high', 'close'], # }); - # ====Example 5. Limiting input to a value in a dropdown list where the list is specified as a cell range. + # ===Example 5. Limiting input to a value in a dropdown list where the list is specified as a cell range. # # worksheet.data_validation('A5', # { # :validate => 'list', # :source => '=$E$4:$G$4', # }); - # ====Example 6. Limiting input to a date in a fixed range. + # ===Example 6. Limiting input to a date in a fixed range. # # worksheet.data_validation('A6', # { # :validate => 'date', # :criteria => 'between', # :minimum => '2008-01-01T', # :maximum => '2008-12-12T', # }); - # ====Example 7. Displaying a message when the cell is selected. + # ===Example 7. Displaying a message when the cell is selected. # # worksheet.data_validation('A7', # { # :validate => 'integer', # :criteria => 'between', @@ -4639,54 +5052,12 @@ # }); # See also the data_validate.rb program in the examples directory # of the distro. # def data_validation(*args) - # Check for a cell reference in A1 notation and substitute row and column. - row1, col1, row2, col2, options = row_col_notation(args) - if row2.respond_to?(:keys) - param = row2.dup - row2, col2 = row1, col1 - elsif options.respond_to?(:keys) - param = options.dup - else - raise WriteXLSXInsufficientArgumentError - end - raise WriteXLSXInsufficientArgumentError if [row1, col1, row2, col2, param].include?(nil) - - check_dimensions(row1, col1) - check_dimensions(row2, col2) - - check_for_valid_input_params(param) - - param[:value] = param[:source] if param[:source] - param[:value] = param[:minimum] if param[:minimum] - - param[:validate] = valid_validation_type[param[:validate].downcase] - return if param[:validate] == 'none' - if ['list', 'custom'].include?(param[:validate]) - param[:criteria] = 'between' - param[:maximum] = nil - end - - check_criteria_required(param) - check_valid_citeria_types(param) - param[:criteria] = valid_criteria_type[param[:criteria].downcase] - - check_maximum_value_when_criteria_is_between_or_notbetween(param) - param[:error_type] = param.has_key?(:error_type) ? error_type[param[:error_type].downcase] : 0 - - convert_date_time_value_if_required(param) - set_some_defaults(param) - - param[:cells] = [[row1, col1, row2, col2]] - - # A (for now) undocumented parameter to pass additional cell ranges. - param[:other_cells].each { |cells| param[:cells] << cells } if param.has_key?(:other_cells) - - # Store the validation information until we close the worksheet. - @validations << param + validation = DataValidation.new(*args) + @validations << validation unless validation.validate_none? end # # Set the option to hide gridlines on the screen and the printed page. # @@ -4709,21 +5080,17 @@ # # If you don't supply an argument or use nil the default option # is true, i.e. only the printed gridlines are hidden. # def hide_gridlines(option = 1) - if option == 0 || !option - @print_gridlines = true # 1 = display, 0 = hide - @screen_gridlines = true - @print_options_changed = true - elsif option == 1 - @print_gridlines = false - @screen_gridlines = true - else - @print_gridlines = false + if option == 2 @screen_gridlines = false + else + @screen_gridlines = true end + + @page_setup.hide_gridlines(option) end # Set the option to print the row and column headers on the printed page. # # An Excel worksheet looks something like the following; @@ -4746,17 +5113,18 @@ # worksheet.print_row_col_headers # # Do not confuse these headers with page headers as described in the # set_header() section above. # - def print_row_col_headers(headers = 1) - if headers - @print_headers = 1 - @print_options_changed = 1 - else - @print_headers = 0 - end + def print_row_col_headers(headers = true) + @page_setup.print_row_col_headers(headers) + # if headers + # @print_headers = 1 + # @page_setup.print_options_changed = 1 + # else + # @print_headers = 0 + # end end # # The fit_to_pages() method is used to fit the printed area to a specific # number of pages both vertically and horizontally. If the printed area @@ -4783,14 +5151,14 @@ # # Note that fit_to_pages() will override any manual page breaks that # are defined in the worksheet. # def fit_to_pages(width = 1, height = 1) - @print_style.fit_page = true - @print_style.fit_width = width - @print_style.fit_height = height - @print_style.page_setup_changed = true + @page_setup.fit_page = true + @page_setup.fit_width = width + @page_setup.fit_height = height + @page_setup.page_setup_changed = true end # # :call-seq: # autofilter(first_row, first_col, last_row, last_col) @@ -4832,21 +5200,21 @@ # The filter_column method can be used to filter columns in a autofilter # range based on simple conditions. # # NOTE: It isn't sufficient to just specify the filter condition. # You must also hide any rows that don't match the filter condition. - # Rows are hidden using the set_row() visible parameter. WriteXLSX cannot + # Rows are hidden using the set_row() +visible+ parameter. WriteXLSX cannot # do this automatically since it isn't part of the file format. # See the autofilter.rb program in the examples directory of the distro # for an example. # # The conditions for the filter are specified using simple expressions: # # worksheet.filter_column('A', 'x > 2000') # worksheet.filter_column('B', 'x > 2000 and x < 5000') # - # The column parameter can either be a zero indexed column number or + # The +column+ parameter can either be a zero indexed column number or # a string column name. # # The following operators are available: # # Operator Synonyms @@ -4863,20 +5231,20 @@ # The operator synonyms are just syntactic sugar to make you more # comfortable using the expressions. It is important to remember that # the expressions will be interpreted by Excel and not by ruby. # # An expression can comprise a single statement or two statements - # separated by the and and or operators. For example: + # separated by the +and+ and +or+ operators. For example: # # 'x < 2000' # 'x > 2000' # 'x == 2000' # 'x > 2000 and x < 5000' # 'x == 2000 or x == 5000' # # Filtering of blank or non-blank data can be achieved by using a value - # of Blanks or NonBlanks in the expression: + # of +Blanks+ or +NonBlanks+ in the expression: # # 'x == Blanks' # 'x == NonBlanks' # # Excel also allows some simple string matching operations: @@ -4889,13 +5257,13 @@ # 'x !~ *b*' # doesn't contains b # # You can also use * to match any character or number and ? to match any # single character or number. No other regular expression quantifier is # supported by Excel's filters. Excel's regular expression characters can - # be escaped using ~. + # be escaped using +~+. # - # The placeholder variable x in the above examples can be replaced by any + # The placeholder variable +x+ in the above examples can be replaced by any # simple string. The actual placeholder name is ignored internally so the # following are all equivalent: # # 'x < 2000' # 'col < 2000' @@ -4905,11 +5273,11 @@ # in a range specified by the autofilter() Worksheet method. # # See the autofilter.rb program in the examples directory of the distro # for a more detailed example. # - # Note Spreadsheet::WriteExcel supports Top 10 style filters. These aren't + # Note writeExcel gem supports Top 10 style filters. These aren't # currently supported by WriteXLSX but may be added later. # def filter_column(col, expression) raise "Must call autofilter before filter_column" unless @autofilter_area @@ -5021,11 +5389,11 @@ # def set_h_pagebreaks(*args) breaks = args.collect do |brk| Array(brk) end.flatten - @print_style.hbreaks += breaks + @page_setup.hbreaks += breaks end # # Store the vertical page breaks on a worksheet. # @@ -5046,11 +5414,11 @@ # # Note: If you specify the "fit to page" option via the fit_to_pages() # method it will override all manual page breaks. # def set_v_pagebreaks(*args) - @print_style.vbreaks += args + @page_setup.vbreaks += args end # # This method is used to make all cell comments visible when a worksheet # is opened. @@ -5290,18 +5658,10 @@ def comments_visible? # :nodoc: !!@comments_visible end - def comments_xml_writer=(file) # :nodoc: - @comments.set_xml_writer(file) - end - - def comments_assemble_xml_file # :nodoc: - @comments.assemble_xml_file - end - def comments_array # :nodoc: @comments.sorted_comments end # @@ -5322,13 +5682,11 @@ # # Write the cell array formula <f> element. # def write_cell_array_formula(formula, range) #:nodoc: - attributes = ['t', 'array', 'ref', range] - - @writer.data_element('f', formula, attributes) + @writer.data_element('f', formula, ['t', 'array', 'ref', range]) end def date_1904? #:nodoc: @workbook.date_1904? end @@ -5354,10 +5712,24 @@ def buttons_data # :nodoc: @buttons_array end + def external_links + [ + @external_hyper_links, + @external_drawing_links, + @external_vml_links, + @external_table_links, + @external_comment_links + ].reject { |a| a.empty? } + end + + def drawing_links + [@drawing_links] + end + # # Turn the HoH that stores the comments into an array for easier handling # and set the external links for comments and buttons. # def prepare_vml_objects(vml_data_id, vml_shape_id, comment_id) @@ -5382,110 +5754,25 @@ @vml_shape_id = vml_shape_id count end - private - - def check_for_valid_input_params(param) - check_parameter(param, valid_validation_parameter, 'data_validation') - - unless param.has_key?(:validate) - raise WriteXLSXOptionParameterError, "Parameter :validate is required in data_validation()" - end - unless valid_validation_type.has_key?(param[:validate].downcase) - raise WriteXLSXOptionParameterError, - "Unknown validation type '#{param[:validate]}' for parameter :validate in data_validation()" - end - if param[:error_type] && !error_type.has_key?(param[:error_type].downcase) - raise WriteXLSXOptionParameterError, - "Unknown criteria type '#param[:error_type}' for parameter :error_type in data_validation()" - end + def tables_count + @tables.size end - def check_criteria_required(param) - unless param.has_key?(:criteria) - raise WriteXLSXOptionParameterError, "Parameter :criteria is required in data_validation()" - end - end + private - def check_valid_citeria_types(param) - unless valid_criteria_type.has_key?(param[:criteria].downcase) - raise WriteXLSXOptionParameterError, - "Unknown criteria type '#{param[:criteria]}' for parameter :criteria in data_validation()" - end - end - - def check_maximum_value_when_criteria_is_between_or_notbetween(param) - if param[:criteria] == 'between' || param[:criteria] == 'notBetween' - unless param.has_key?(:maximum) - raise WriteXLSXOptionParameterError, - "Parameter :maximum is required in data_validation() when using :between or :not between criteria" - end + def cell_format_of_rich_string(rich_strings) + # If the last arg is a format we use it as the cell format. + if rich_strings[-1].respond_to?(:xf_index) + rich_strings.pop else - param[:maximum] = nil + nil end end - def error_type - {'stop' => 0, 'warning' => 1, 'information' => 2} - end - - def convert_date_time_value_if_required(param) - if param[:validate] == 'date' || param[:validate] == 'time' - unless convert_date_time_value(param, :value) && convert_date_time_value(param, :maximum) - raise WriteXLSXOptionParameterError, "Invalid date/time value." - end - end - end - - def set_some_defaults(param) - param[:ignore_blank] ||= 1 - param[:dropdown] ||= 1 - param[:show_input] ||= 1 - param[:show_error] ||= 1 - end - - # List of valid input parameters. - def valid_validation_parameter - [ - :validate, - :criteria, - :value, - :source, - :minimum, - :maximum, - :ignore_blank, - :dropdown, - :show_input, - :input_title, - :input_message, - :show_error, - :error_title, - :error_message, - :error_type, - :other_cells - ] - end - - def valid_validation_type # :nodoc: - { - 'any' => 'none', - 'any value' => 'none', - 'whole number' => 'whole', - 'whole' => 'whole', - 'integer' => 'whole', - 'decimal' => 'decimal', - 'list' => 'list', - 'date' => 'date', - 'time' => 'time', - 'text length' => 'textLength', - 'length' => 'textLength', - 'custom' => 'custom' - } - end - # Convert the list of format, string tokens to pairs of (format, string) # except for the first string fragment which doesn't require a default # formatting run. Use the default for strings without a leading format. def rich_strings_fragments(rich_strings) # :nodoc: # Create a temp format with the default font for unformatted fragments. @@ -5520,10 +5807,36 @@ pos += 1 end [fragments, length] end + def xml_str_of_rich_string(fragments) + # Create a temp XML::Writer object and use it to write the rich string + # XML to a string. + writer = Package::XMLWriterSimple.new + + # If the first token is a string start the <r> element. + writer.start_tag('r') if !fragments[0].respond_to?(:xf_index) + + # Write the XML elements for the format string fragments. + fragments.each do |token| + if token.respond_to?(:xf_index) + # Write the font run. + writer.start_tag('r') + token.write_font(writer, self) + else + # Write the string fragment part, with whitespace handling. + attributes = [] + + attributes << 'xml:space' << 'preserve' if token =~ /^\s/ || token =~ /\s$/ + writer.data_element('t', token, attributes) + writer.end_tag('r') + end + end + writer.string + end + # Pad out the rest of the area with formatted blank cells. def write_formatted_blank_to_area(row_first, row_last, col_first, col_last, format) (row_first .. row_last).each do |row| (col_first .. col_last).each do |col| next if row == row_first && col == col_first @@ -5756,24 +6069,21 @@ # Convert the width of a cell from user's units to pixels. Excel rounds the # column width to the nearest pixel. If the width hasn't been set by the user # we use the default value. If the column is hidden it has a value of zero. # def size_col(col) #:nodoc: - max_digit_width = 7 # For Calabri 11. - padding = 5 - # Look up the cell value to see if it has been changed. if @col_sizes[col] width = @col_sizes[col] # Convert to pixels. if width == 0 pixels = 0 elsif width < 1 pixels = (width * 12 + 0.5).to_i else - pixels = (width * max_digit_width + 0.5).to_i + padding + pixels = (width * MAX_DIGIT_WIDTH + 0.5).to_i + PADDING end else pixels = 64 end pixels @@ -6031,57 +6341,54 @@ end # # Write the <worksheet> element. This is the root element of Worksheet. # - def write_worksheet #:nodoc: - schema = 'http://schemas.openxmlformats.org/' + def write_worksheet_attributes #:nodoc: + schema = 'http://schemas.openxmlformats.org/' attributes = [ - 'xmlns', schema + 'spreadsheetml/2006/main', - 'xmlns:r', schema + 'officeDocument/2006/relationships' + 'xmlns', "#{schema}spreadsheetml/2006/main", + 'xmlns:r', "#{schema}officeDocument/2006/relationships" ] if @excel_version == 2010 - attributes << 'xmlns:mc' << "#{schema}markup-compatibility/2006" - attributes << 'xmlns:x14ac' << - 'http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac' + attributes << 'xmlns:mc' << "#{schema}markup-compatibility/2006" + attributes << 'xmlns:x14ac' << "#{OFFICE_URL}spreadsheetml/2009/9/ac" attributes << 'mc:Ignorable' << 'x14ac' end - @writer.start_tag('worksheet', attributes) + attributes end # # Write the <sheetPr> element for Sheet level properties. # def write_sheet_pr #:nodoc: - if !fit_page? && !filter_on? && !tab_color? && - !outline_changed? && !vba_codename? - return - end - codename = @vba_codename + return unless tab_outline_fit? || vba_codename? || filter_on? + attributes = [] - (attributes << 'codeName' << codename) if codename - (attributes << 'filterMode' << 1) if filter_on? + attributes << 'codeName' << @vba_codename if vba_codename? + attributes << 'filterMode' << 1 if filter_on? - if fit_page? || tab_color? || outline_changed? + if tab_outline_fit? @writer.tag_elements('sheetPr', attributes) do write_tab_color write_outline_pr write_page_set_up_pr end else @writer.empty_tag('sheetPr', attributes) end end + def tab_outline_fit? + tab_color? || outline_changed? || fit_page? + end + # # Write the <pageSetUpPr> element. # def write_page_set_up_pr #:nodoc: - return unless fit_page? - - attributes = ['fitToPage', 1] - @writer.empty_tag('pageSetUpPr', attributes) + @writer.empty_tag('pageSetUpPr', ['fitToPage', 1]) if fit_page? end # Write the <dimension> element. This specifies the range of cells in the # worksheet. As a special case, empty spreadsheets use 'A1' as a range. # @@ -6109,12 +6416,11 @@ # The dimensions are a cell range. cell_1 = xl_rowcol_to_cell(@dim_rowmin, @dim_colmin) cell_2 = xl_rowcol_to_cell(@dim_rowmax, @dim_colmax) ref = cell_1 + ':' + cell_2 end - attributes = ['ref', ref] - @writer.empty_tag('dimension', attributes) + @writer.empty_tag('dimension', ['ref', ref]) end # # Write the <sheetViews> element. # def write_sheet_views #:nodoc: @@ -6122,11 +6428,11 @@ end def write_sheet_view #:nodoc: attributes = [] # Hide screen gridlines if required - attributes << 'showGridLines' << 0 unless screen_gridlines? + attributes << 'showGridLines' << 0 unless @screen_gridlines # Hide zeroes in cells. attributes << 'showZeros' << 0 unless show_zeros? # Display worksheet right to left for Hebrew, Arabic and others. @@ -6208,18 +6514,22 @@ def write_cols #:nodoc: # Exit unless some column have been formatted. return if @colinfo.empty? @writer.tag_elements('cols') do - @colinfo.each {|col_info| write_col_info(*col_info) } + @colinfo.each {|col_info| write_col_info(col_info) } end end # # Write the <col> element. # - def write_col_info(*args) #:nodoc: + def write_col_info(args) #:nodoc: + @writer.empty_tag('col', col_info_attributes(args)) + end + + def col_info_attributes(args) min = args[0] || 0 # First formatted column. max = args[1] || 0 # Last formatted column. width = args[2] # Col width in user units. format = args[3] # Format index. hidden = args[4] || 0 # Hidden flag. @@ -6234,29 +6544,26 @@ if width.nil? width = hidden == 0 ? 8.43 : 0 end # Convert column width from user units to character width. - max_digit_width = 7.0 # For Calabri 11. - padding = 5.0 if width && width > 0 - width = ((width * max_digit_width + padding) / max_digit_width * 256).to_i/256.0 + width = ((width * MAX_DIGIT_WIDTH + PADDING) / MAX_DIGIT_WIDTH.to_f * 256).to_i/256.0 width = width.to_i if width.to_s =~ /\.0+$/ end attributes = [ 'min', min + 1, 'max', max + 1, 'width', width ] - (attributes << 'style' << xf_index) if xf_index != 0 - (attributes << 'hidden' << 1) if hidden != 0 - (attributes << 'customWidth' << 1) if custom_width - (attributes << 'outlineLevel' << level) if level != 0 - (attributes << 'collapsed' << 1) if collapsed != 0 - - @writer.empty_tag('col', attributes) + attributes << 'style' << xf_index if xf_index != 0 + attributes << 'hidden' << 1 if hidden != 0 + attributes << 'customWidth' << 1 if custom_width + attributes << 'outlineLevel' << level if level != 0 + attributes << 'collapsed' << 1 if collapsed != 0 + attributes end # # Write the <sheetData> element. # @@ -6485,18 +6792,15 @@ # # Convert column width from user units to pane split width. # def calculate_x_split_width(width) #:nodoc: - max_digit_width = 7 # For Calabri 11. - padding = 5 - # Convert to pixels. if width < 1 pixels = int(width * 12 + 0.5) else - pixels = (width * max_digit_width + 0.5).to_i + padding + pixels = (width * MAX_DIGIT_WIDTH + 0.5).to_i + PADDING end # Convert to points. points = pixels * 3 / 4 @@ -6509,85 +6813,37 @@ # # Write the <sheetCalcPr> element for the worksheet calculation properties. # def write_sheet_calc_pr #:nodoc: - full_calc_on_load = 1 - - attributes = ['fullCalcOnLoad', full_calc_on_load] - - @writer.empty_tag('sheetCalcPr', attributes) + @writer.empty_tag('sheetCalcPr', ['fullCalcOnLoad', 1]) end # # Write the <phoneticPr> element. # def write_phonetic_pr #:nodoc: - font_id = 1 - type = 'noConversion' - attributes = [ - 'fontId', font_id, - 'type', type - ] + 'fontId', 1, + 'type', 'noConversion' + ] @writer.empty_tag('phoneticPr', attributes) end # # Write the <pageMargins> element. # def write_page_margins #:nodoc: - @writer.empty_tag('pageMargins', @print_style.attributes) + @page_setup.write_page_margins(@writer) end # # Write the <pageSetup> element. # - # The following is an example taken from Excel. - # - # <pageSetup - # paperSize="9" - # scale="110" - # fitToWidth="2" - # fitToHeight="2" - # pageOrder="overThenDown" - # orientation="portrait" - # blackAndWhite="1" - # draft="1" - # horizontalDpi="200" - # verticalDpi="200" - # r:id="rId1" - # /> - # def write_page_setup #:nodoc: - attributes = [] - - return unless page_setup_changed? - - # Set paper size. - attributes << 'paperSize' << @paper_size if @paper_size - - # Set the scale - attributes << 'scale' << @print_style.scale if @print_style.scale != 100 - - # Set the "Fit to page" properties. - attributes << 'fitToWidth' << @print_style.fit_width if @print_style.fit_page && @print_style.fit_width != 1 - - attributes << 'fitToHeight' << @print_style.fit_height if @print_style.fit_page && @print_style.fit_height != 1 - - # Set the page print direction. - attributes << 'pageOrder' << "overThenDown" if print_across? - - # Set page orientation. - if @print_style.orientation? - attributes << 'orientation' << 'portrait' - else - attributes << 'orientation' << 'landscape' - end - - @writer.empty_tag('pageSetup', attributes) + @page_setup.write_page_setup(@writer) end # # Write the <mergeCells> element. # @@ -6598,13 +6854,11 @@ end def write_some_elements(tag, container) return if container.empty? - attributes = ['count', container.size] - - @writer.tag_elements(tag, attributes) do + @writer.tag_elements(tag, ['count', container.size]) do yield end end # @@ -6614,67 +6868,29 @@ row_min, col_min, row_max, col_max = merged_range # Convert the merge dimensions to a cell range. cell_1 = xl_rowcol_to_cell(row_min, col_min) cell_2 = xl_rowcol_to_cell(row_max, col_max) - ref = "#{cell_1}:#{cell_2}" - attributes = ['ref', ref] - - @writer.empty_tag('mergeCell', attributes) + @writer.empty_tag('mergeCell', ['ref', "#{cell_1}:#{cell_2}"]) end # # Write the <printOptions> element. # def write_print_options #:nodoc: - attributes = [] - - return unless print_options_changed? - - # Set horizontal centering. - attributes << 'horizontalCentered' << 1 if hcenter? - - # Set vertical centering. - attributes << 'verticalCentered' << 1 if vcenter? - - # Enable row and column headers. - attributes << 'headings' << 1 if print_headers? - - # Set printed gridlines. - attributes << 'gridLines' << 1 if print_gridlines? - - @writer.empty_tag('printOptions', attributes) + @page_setup.write_print_options(@writer) end # # Write the <headerFooter> element. # def write_header_footer #:nodoc: - return unless header_footer_changed? - - @writer.tag_elements('headerFooter') do - write_odd_header if @header && @header != '' - write_odd_footer if @footer && @footer != '' - end + @page_setup.write_header_footer(@writer) end # - # Write the <oddHeader> element. - # - def write_odd_header #:nodoc: - @writer.data_element('oddHeader', @header) - end - - # - # Write the <oddFooter> element. - # - def write_odd_footer #:nodoc: - @writer.data_element('oddFooter', @footer) - end - - # # Write the <rowBreaks> element. # def write_row_breaks #:nodoc: write_breaks('rowBreaks') end @@ -6687,14 +6903,14 @@ end def write_breaks(tag) # :nodoc: case tag when 'rowBreaks' - page_breaks = sort_pagebreaks(*(@print_style.hbreaks)) + page_breaks = sort_pagebreaks(*(@page_setup.hbreaks)) max = 16383 when 'colBreaks' - page_breaks = sort_pagebreaks(*(@print_style.vbreaks)) + page_breaks = sort_pagebreaks(*(@page_setup.vbreaks)) max = 1048575 else raise "Invalid parameter '#{tag}' in write_breaks." end count = page_breaks.size @@ -6761,13 +6977,11 @@ # # Write the <filterColumn> element. # def write_filter_column(col_id, type, *filters) #:nodoc: - attributes = ['colId', col_id] - - @writer.tag_elements('filterColumn', attributes) do + @writer.tag_elements('filterColumn', ['colId', col_id]) do if type == 1 # Type == 1 is the new XLSX style filter. write_filters(*filters) else # Type == 0 is the classic "custom" filter. @@ -6796,11 +7010,10 @@ # def write_filter(val) #:nodoc: @writer.empty_tag('filter', ['val', val]) end - # # Write the <customFilters> element. # def write_custom_filters(*tokens) #:nodoc: if tokens.size == 2 @@ -6822,11 +7035,10 @@ write_custom_filter(tokens[3], tokens[4]) end end end - # # Write the <customFilter> element. # def write_custom_filter(operator, val) #:nodoc: operators = { @@ -6859,113 +7071,77 @@ # element. The attributes are different for internal and external links. # def write_hyperlinks #:nodoc: return unless @hyperlinks - # Sort the hyperlinks into row order. - row_nums = @hyperlinks.keys.sort - - # Exit if there are no hyperlinks to process. - return if row_nums.empty? - - # Iterate over the rows. - row_nums.each do |row_num| + @hyperlinks.keys.sort.each do |row_num| # Sort the hyperlinks into column order. col_nums = @hyperlinks[row_num].keys.sort # Iterate over the columns. col_nums.each do |col_num| # Get the link data for this cell. - link = @hyperlinks[row_num][col_num] - link_type = link[:_link_type] + link = @hyperlinks[row_num][col_num] # If the cell isn't a string then we have to add the url as # the string to display if ptrue?(@cell_data_table) && ptrue?(@cell_data_table[row_num]) && ptrue?(@cell_data_table[row_num][col_num]) if @cell_data_table[row_num][col_num].display_url_string? - display = link[:_url] - else - display = nil + link.display = link.url_str end end - if link_type == 1 + if link.link_type == 1 # External link with rel file relationship. @rel_count += 1 - @hlink_refs << [ - link_type, row_num, col_num, - @rel_count, link[:_str], display, link[:_tip] - ] + @hlink_refs << [link, row_num, col_num, @rel_count] # Links for use by the packager. - @external_hyper_links << [ - '/hyperlink', link[:_url], 'External' - ] + @external_hyper_links << ['/hyperlink', link.url, 'External'] else # Internal link with rel file relationship. - @hlink_refs << [ - link_type, row_num, col_num, - link[:_url], link[:_str], link[:_tip] - ] + @hlink_refs << [link, row_num, col_num] end end end return if @hlink_refs.empty? # Write the hyperlink elements. @writer.tag_elements('hyperlinks') do @hlink_refs.each do |aref| - type, *args = aref - - if type == 1 - write_hyperlink_external(*args) - elsif type == 2 - write_hyperlink_internal(*args) + case aref[0].link_type + when 1 + write_hyperlink_external(*aref) + when 2 + write_hyperlink_internal(*aref) end end end end # # Write the <hyperlink> element for external links. # - def write_hyperlink_external(row, col, id, location = nil, display = nil, tooltip = nil) #:nodoc: - ref = xl_rowcol_to_cell(row, col) - r_id = "rId#{id}" - - attributes = ['ref', ref, 'r:id', r_id] - - attributes << 'location' << location if location - attributes << 'display' << display if display - attributes << 'tooltip' << tooltip if tooltip - - @writer.empty_tag('hyperlink', attributes) + def write_hyperlink_external(link, row, col, id) # :nodoc: + @writer.empty_tag('hyperlink', link.write_external_attributes(row, col, id)) end # # Write the <hyperlink> element for internal links. # - def write_hyperlink_internal(row, col, location, display, tooltip = nil) #:nodoc: - ref = xl_rowcol_to_cell(row, col) - - attributes = ['ref', ref, 'location', location] - - attributes << 'tooltip' << tooltip if tooltip - attributes << 'display' << display - - @writer.empty_tag('hyperlink', attributes) + def write_hyperlink_internal(link, row, col) #:nodoc: + @writer.empty_tag('hyperlink', link.write_internal_attributes(row, col)) end # # Write the <tabColor> element. # def write_tab_color #:nodoc: return unless tab_color? - attributes = ['rgb', get_palette_color(@tab_color)] - @writer.empty_tag('tabColor', attributes) + @writer.empty_tag('tabColor', ['rgb', get_palette_color(@tab_color)]) end # # Write the <outlinePr> element. # @@ -7016,418 +7192,98 @@ # # Write the <drawing> elements. # def write_drawings #:nodoc: - return unless drawing? - @rel_count += 1 - write_drawing(@rel_count) + increment_rel_id_and_write_r_id('drawing') if drawing? end # - # Write the <drawing> element. - # - def write_drawing(id) #:nodoc: - r_id = "rId#{id}" - - attributes = ['r:id', r_id] - - @writer.empty_tag('drawing', attributes) - end - - # # Write the <legacyDrawing> element. # def write_legacy_drawing #:nodoc: - return unless @has_vml - - # Increment the relationship id for any drawings or comments. - @rel_count += 1 - id = @rel_count - - attributes = ['r:id', "rId#{id}"] - - @writer.empty_tag('legacyDrawing', attributes) + increment_rel_id_and_write_r_id('legacyDrawing') if has_vml? end # - # Write the <font> element. - # - def write_font(writer, format) #:nodoc: - writer.tag_elements('rPr') do - writer.empty_tag('b') if format.bold? - writer.empty_tag('i') if format.italic? - writer.empty_tag('strike') if format.strikeout? - writer.empty_tag('outline') if format.outline? - writer.empty_tag('shadow') if format.shadow? - - # Handle the underline variants. - write_underline(writer, format.underline) if format.underline? - - write_vert_align(writer, 'superscript') if format.font_script == 1 - write_vert_align(writer, 'subscript') if format.font_script == 2 - - writer.empty_tag('sz', ['val', format.size]) - - theme = format.theme - color = format.color - if ptrue?(theme) - write_color(writer, 'theme', theme) - elsif ptrue?(color) - color = get_palette_color(color) - write_color(writer, 'rgb', color) - else - write_color(writer, 'theme', 1) - end - - writer.empty_tag('rFont', ['val', format.font]) - writer.empty_tag('family', ['val', format.font_family]) - - if format.font == 'Calibri' && format.hyperlink == 0 - writer.empty_tag('scheme', ['val', format.font_scheme]) - end - end - end - - # # Write the underline font element. # def write_underline(writer, underline) #:nodoc: - attributes = underline_attributes(underline) - writer.empty_tag('u', attributes) + writer.empty_tag('u', underline_attributes(underline)) end # - # Write the <vertAlign> font sub-element. - # - def write_vert_align(writer, val) #:nodoc: - attributes = ['val', val] - - writer.empty_tag('vertAlign', attributes) - end - - # # Write the <tableParts> element. # def write_table_parts - # Return if worksheet doesn't contain any tables. return if @tables.empty? - attributes = ['count', @tables.size] - - @writer.tag_elements('tableParts', attributes) do - - @tables.each do |table| - # Write the tablePart element. - @rel_count += 1 - write_table_part(@rel_count) - end + @writer.tag_elements('tableParts', ['count', tables_count]) do + tables_count.times { increment_rel_id_and_write_r_id('tablePart') } end end # # Write the <tablePart> element. # def write_table_part(id) - r_id = "rId#{id}" + @writer.empty_tag('tablePart', ['r:id', "rId#{id}"]) + end - attributes = ['r:id', r_id] + def increment_rel_id_and_write_r_id(tag) + @rel_count += 1 + write_r_id(tag, @rel_count) + end - @writer.empty_tag('tablePart', attributes) + def write_r_id(tag, id) + @writer.empty_tag(tag, ['r:id', "rId#{id}"]) end # # Write the <extLst> element and sparkline subelements. # def write_ext_sparklines # :nodoc: - sparklines = @sparklines + @writer.tag_elements('extLst') { write_ext } unless @sparklines.empty? + end - # Return if worksheet doesn't contain any sparklines. - return if sparklines.empty? - - # Write the extLst element. - @writer.start_tag('extLst') - - # Write the ext element. - write_ext - - # Write the x14:sparklineGroups element. - write_sparkline_groups - - # Write the sparkline elements. - sparklines.reverse.each do |sparkline| - # Write the x14:sparklineGroup element. - write_sparkline_group(sparkline) - - # Write the x14:colorSeries element. - write_color_series(sparkline.series_color) - - # Write the x14:colorNegative element. - write_color_negative(sparkline.negative_color) - - # Write the x14:colorAxis element. - write_color_axis - - # Write the x14:colorMarkers element. - write_color_markers(sparkline.markers_color) - - # Write the x14:colorFirst element. - write_color_first(sparkline.first_color) - - # Write the x14:colorLast element. - write_color_last(sparkline.last_color) - - # Write the x14:colorHigh element. - write_color_high(sparkline.high_color) - - # Write the x14:colorLow element. - write_color_low(sparkline.low_color) - - if sparkline.date_axis - @writer.data_element('xm:f', sparkline.date_axis) - end - - write_sparklines(sparkline) - - @writer.end_tag('x14:sparklineGroup') + def write_ext + @writer.tag_elements('ext', write_ext_attributes) do + write_sparkline_groups end + end - @writer.end_tag('x14:sparklineGroups') - @writer.end_tag('ext') - @writer.end_tag('extLst') + def write_ext_attributes + [ + 'xmlns:x14', "#{OFFICE_URL}spreadsheetml/2009/9/main", + 'uri', '{05C60535-1F16-4fd2-B633-F4F36F0B64E0}' + ] end - # - # Write the <x14:sparklines> element and <x14:sparkline> subelements. - # - def write_sparklines(sparkline) # :nodoc: - # Write the sparkline elements. - @writer.tag_elements('x14:sparklines') do - - (0 .. sparkline.count-1).each do |i| - range = sparkline.ranges[i] - location = sparkline.locations[i] - - @writer.tag_elements('x14:sparkline') do - @writer.data_element('xm:f', range) - @writer.data_element('xm:sqref', location) - end + def write_sparkline_groups + # Write the x14:sparklineGroups element. + @writer.tag_elements('x14:sparklineGroups', sparkline_groups_attributes) do + # Write the sparkline elements. + @sparklines.reverse.each do |sparkline| + sparkline.write_sparkline_group(@writer) end end end - # - # Write the <ext> element. - # - def write_ext # :nodoc: - schema = 'http://schemas.microsoft.com/office/' - xmlns_x_14 = "#{schema}spreadsheetml/2009/9/main" - uri = '{05C60535-1F16-4fd2-B633-F4F36F0B64E0}' - - attributes = [ - 'xmlns:x14', xmlns_x_14, - 'uri', uri - ] - - @writer.start_tag('ext', attributes) + def sparkline_groups_attributes # :nodoc: + ['xmlns:xm', "#{OFFICE_URL}excel/2006/main"] end # - # Write the <x14:sparklineGroups> element. - # - def write_sparkline_groups # :nodoc: - xmlns_xm = 'http://schemas.microsoft.com/office/excel/2006/main' - - attributes = ['xmlns:xm', xmlns_xm] - - @writer.start_tag('x14:sparklineGroups', attributes) - end - - # - # Write the <x14:sparklineGroup> element. - # - # Example for order. - # - # <x14:sparklineGroup - # manualMax="0" - # manualMin="0" - # lineWeight="2.25" - # type="column" - # dateAxis="1" - # displayEmptyCellsAs="span" - # markers="1" - # high="1" - # low="1" - # first="1" - # last="1" - # negative="1" - # displayXAxis="1" - # displayHidden="1" - # minAxisType="custom" - # maxAxisType="custom" - # rightToLeft="1"> - # - def write_sparkline_group(sparkline) # :nodoc: - @writer.start_tag('x14:sparklineGroup', sparkline.group_attributes) - end - - # - # Helper function for the sparkline color functions below. - # - def write_spark_color(element, color) # :nodoc: - attr = [] - - attr << 'rgb' << color[:_rgb] if color[:_rgb] - attr << 'theme' << color[:_theme] if color[:_theme] - attr << 'tint' << color[:_tint] if color[:_tint] - - @writer.empty_tag(element, attr) - end - - # - # Write the <x14:colorSeries> element. - # - def write_color_series(param) # :nodoc: - write_spark_color('x14:colorSeries', param) - end - - # - # Write the <x14:colorNegative> element. - # - def write_color_negative(param) # :nodoc: - write_spark_color('x14:colorNegative', param) - end - - # - # Write the <x14:colorAxis> element. - # - def write_color_axis # :nodoc: - write_spark_color('x14:colorAxis', { :_rgb => 'FF000000'} ) - end - - # - # Write the <x14:colorMarkers> element. - # - def write_color_markers(param) # :nodoc: - write_spark_color('x14:colorMarkers', param) - end - - # - # Write the <x14:colorFirst> element. - # - def write_color_first(param) # :nodoc: - write_spark_color('x14:colorFirst', param) - end - - # - # Write the <x14:colorLast> element. - # - def write_color_last(param) # :nodoc: - write_spark_color('x14:colorLast', param) - end - - # - # Write the <x14:colorHigh> element. - # - def write_color_high(param) # :nodoc: - write_spark_color('x14:colorHigh', param) - end - - # - # Write the <x14:colorLow> element. - # - def write_color_low(param) # :nodoc: - write_spark_color('x14:colorLow', param) - end - - # # Write the <dataValidations> element. # def write_data_validations #:nodoc: write_some_elements('dataValidations', @validations) do - @validations.each { |validation| write_data_validation(validation) } + @validations.each { |validation| validation.write_data_validation(@writer) } end end # - # Write the <dataValidation> element. - # - def write_data_validation(param) #:nodoc: - sqref = '' - attributes = [] - - # Set the cell range(s) for the data validation. - param[:cells].each do |cells| - # Add a space between multiple cell ranges. - sqref += ' ' if sqref != '' - - row_first, col_first, row_last, col_last = cells - - # Swap last row/col for first row/col as necessary - row_first, row_last = row_last, row_first if row_first > row_last - col_first, col_last = col_last, col_first if col_first > col_last - - # If the first and last cell are the same write a single cell. - if row_first == row_last && col_first == col_last - sqref += xl_rowcol_to_cell(row_first, col_first) - else - sqref += xl_range(row_first, row_last, col_first, col_last) - end - end - - #use Data::Dumper::Perltidy - #print Dumper param - - attributes << 'type' << param[:validate] - attributes << 'operator' << param[:criteria] if param[:criteria] != 'between' - - if param[:error_type] - attributes << 'errorStyle' << 'warning' if param[:error_type] == 1 - attributes << 'errorStyle' << 'information' if param[:error_type] == 2 - end - attributes << 'allowBlank' << 1 if param[:ignore_blank] != 0 - attributes << 'showDropDown' << 1 if param[:dropdown] == 0 - attributes << 'showInputMessage' << 1 if param[:show_input] != 0 - attributes << 'showErrorMessage' << 1 if param[:show_error] != 0 - - attributes << 'errorTitle' << param[:error_title] if param[:error_title] - attributes << 'error' << param[:error_message] if param[:error_message] - attributes << 'promptTitle' << param[:input_title] if param[:input_title] - attributes << 'prompt' << param[:input_message] if param[:input_message] - attributes << 'sqref' << sqref - - @writer.tag_elements('dataValidation', attributes) do - # Write the formula1 element. - write_formula_1(param[:value]) - # Write the formula2 element. - write_formula_2(param[:maximum]) if param[:maximum] - end - end - - # - # Write the <formula1> element. - # - def write_formula_1(formula) #:nodoc: - # Convert a list array ref into a comma separated string. - formula = %!"#{formula.join(',')}"! if formula.kind_of?(Array) - - formula = formula.sub(/^=/, '') if formula.respond_to?(:sub) - - @writer.data_element('formula1', formula) - end - - # write_formula_2() - # - # Write the <formula2> element. - # - def write_formula_2(formula) #:nodoc: - formula = formula.sub(/^=/, '') if formula.respond_to?(:sub) - - @writer.data_element('formula2', formula) - end - - # # Write the Worksheet conditional formats. # def write_conditional_formats #:nodoc: @cond_formats.keys.sort.each do |range| write_conditional_formatting(range, @cond_formats[range]) @@ -7436,13 +7292,11 @@ # # Write the <conditionalFormatting> element. # def write_conditional_formatting(range, cond_formats) #:nodoc: - attributes = ['sqref', range] - - @writer.tag_elements('conditionalFormatting', attributes) do + @writer.tag_elements('conditionalFormatting', ['sqref', range]) do cond_formats.each { |cond_format| cond_format.write_cf_rule } end end def store_data_to_table(cell_data) #:nodoc: @@ -7509,18 +7363,10 @@ end end [span_min, span_max] end - def xf(format) #:nodoc: - if format.kind_of?(Format) - format.xf_index - else - 0 - end - end - # # Add a string to the shared string table, if it isn't already there, and # return the string index. # def shared_string_index(str, params = {}) #:nodoc: @@ -7578,11 +7424,11 @@ return sheetname if sheetname =~ /^Sheet\d+$/ return "'#{sheetname}'" end def fit_page? #:nodoc: - @print_style.fit_page + @page_setup.fit_page end def filter_on? #:nodoc: ptrue?(@filter_on) end @@ -7613,50 +7459,18 @@ def show_zeros? #:nodoc: !!@show_zeros end - def screen_gridlines? #:nodoc: - !!@screen_gridlines - end - def protect? #:nodoc: !!@protect end def autofilter_ref? #:nodoc: !!@autofilter_ref end - def print_options_changed? #:nodoc: - !!@print_options_changed - end - - def hcenter? #:nodoc: - !!@hcenter - end - - def vcenter? #:nodoc: - !!@vcenter - end - - def print_headers? #:nodoc: - !!@print_headers - end - - def print_gridlines? #:nodoc: - !!@print_gridlines - end - - def page_setup_changed? #:nodoc: - @print_style.page_setup_changed - end - - def header_footer_changed? #:nodoc: - !!@header_footer_changed - end - def drawing? #:nodoc: !!@drawing end def remove_white_space(margin) #:nodoc: @@ -7665,36 +7479,10 @@ else margin end end - def print_across? - @print_style.across - end - - # List of valid criteria types. - def valid_criteria_type # :nodoc: - { - 'between' => 'between', - 'not between' => 'notBetween', - 'equal to' => 'equal', - '=' => 'equal', - '==' => 'equal', - 'not equal to' => 'notEqual', - '!=' => 'notEqual', - '<>' => 'notEqual', - 'greater than' => 'greaterThan', - '>' => 'greaterThan', - 'less than' => 'lessThan', - '<' => 'lessThan', - 'greater than or equal to' => 'greaterThanOrEqual', - '>=' => 'greaterThanOrEqual', - 'less than or equal to' => 'lessThanOrEqual', - '<=' => 'lessThanOrEqual' - } - end - def set_active_pane_and_cell_selections(row, col, top_row, left_col, active_cell, sqref) # :nodoc: if row > 0 && col > 0 active_pane = 'bottomRight' row_cell = xl_rowcol_to_cell(top_row, 0) col_cell = xl_rowcol_to_cell(0, left_col) @@ -7728,18 +7516,8 @@ # Reject column if it is outside filter range. if col < col_first or col > col_last raise "Column '#{col}' outside autofilter column range (#{col_first} .. #{col_last})" end col - end - - def convert_date_time_value(param, key) # :nodoc: - if param[key] && param[key] =~ /T/ - date_time = convert_date_time(param[key]) - param[key] = date_time if date_time - date_time - else - true - end end end end