############################################################################### # # Worksheet - A writer class for Excel Worksheets. # # # Used in conjunction with WriteExcel # # Copyright 2000-2010, John McNamara, jmcnamara@cpan.org # # original written in Perl by John McNamara # converted to Ruby by Hideo Nakamura, cxn03651@msj.biglobe.ne.jp # require 'writeexcel/format' require 'writeexcel/formula' require 'writeexcel/workbook' class MaxSizeError < StandardError #:nodoc: end # # = class Worksheet # # A new worksheet is created by calling the add_worksheet() method from a workbook object: # # Examples: # # workbook = WriteExcel.new('file.xls') # worksheet1 = workbook.add_worksheet # worksheet2 = workbook.add_worksheet # class Worksheet < BIFFWriter RowMax = 65536 # :nodoc: ColMax = 256 # :nodoc: StrMax = 0 # :nodoc: Buffer = 4096 # :nodoc: NonAscii = /[^!"#\$%&'\(\)\*\+,\-\.\/\:\;<=>\?@0-9A-Za-z_\[\\\]^` ~\0\n]/ # :nodoc: ############################################################################### # # new() # # Constructor. Creates a new Worksheet object from a BIFFwriter object # def initialize(name, index, encoding, url_format, parser, tempdir, date_1904, compatibility, palette, sinfo) # :nodoc: super() @name = name @index = index @encoding = encoding @url_format = url_format @parser = parser @tempdir = tempdir @date_1904 = date_1904 @compatibility = compatibility @palette = palette @sinfo = sinfo # key: :activesheet, :firstsheet, :str_total, :str_unique, :str_table @sheet_type = 0x0000 @ext_sheets = [] @using_tmpfile = true @fileclosed = false @offset = 0 @xls_rowmax = RowMax @xls_colmax = ColMax @xls_strmax = StrMax @dim_rowmin = nil @dim_rowmax = nil @dim_colmin = nil @dim_colmax = nil @colinfo = [] @selection = [0, 0] @panes = [] @active_pane = 3 @frozen = 0 @frozen_no_split = 1 @selected = 0 @hidden = 0 @active = 0 @tab_color = 0 @first_row = 0 @first_col = 0 @display_formulas = 0 @display_headers = 1 @display_zeros = 1 @display_arabic = 0 @paper_size = 0x0 @orientation = 0x1 @header = '' @footer = '' @header_encoding = 0 @footer_encoding = 0 @hcenter = 0 @vcenter = 0 @margin_header = 0.50 @margin_footer = 0.50 @margin_left = 0.75 @margin_right = 0.75 @margin_top = 1.00 @margin_bottom = 1.00 @title_rowmin = nil @title_rowmax = nil @title_colmin = nil @title_colmax = nil @print_rowmin = nil @print_rowmax = nil @print_colmin = nil @print_colmax = nil @print_gridlines = 1 @screen_gridlines = 1 @print_headers = 0 @page_order = 0 @black_white = 0 @draft_quality = 0 @print_comments = 0 @page_start = 1 @custom_start = 0 @fit_page = 0 @fit_width = 0 @fit_height = 0 @hbreaks = [] @vbreaks = [] @protect = 0 @password = nil @col_sizes = {} @row_sizes = {} @col_formats = {} @row_formats = {} @zoom = 100 @print_scale = 100 @page_view = 0 @leading_zeros = false @outline_row_level = 0 @outline_style = 0 @outline_below = 1 @outline_right = 1 @outline_on = 1 @write_match = [] @object_ids = [] @images = {} @images_array = [] @charts = {} @charts_array = [] @comments = {} @comments_array = [] @comments_author = '' @comments_author_enc = 0 @comments_visible = 0 @num_images = 0 @image_mso_size = 0 @filter_area = [] @filter_count = 0 @filter_on = 0 @filter_cols = [] @writing_url = 0 @db_indices = [] @validations = [] @table = [] @row_data = {} end ############################################################################### # # _close() # # Add data to the beginning of the workbook (note the reverse order) # and to the end of the workbook. # def close(*sheetnames) #:nodoc: num_sheets = sheetnames.size ################################################ # Prepend in reverse order!! # # Prepend the sheet dimensions store_dimensions # Prepend the autofilter filters. store_autofilters # Prepend the sheet autofilter info. store_autofilterinfo # Prepend the sheet filtermode record. store_filtermode # Prepend the COLINFO records if they exist unless @colinfo.empty? while (!@colinfo.empty?) arrayref = @colinfo.pop store_colinfo(*arrayref) end end # Prepend the DEFCOLWIDTH record store_defcol # Prepend the sheet password store_password # Prepend the sheet protection store_protect store_obj_protect # Prepend the page setup store_setup # Prepend the bottom margin store_margin_bottom # Prepend the top margin store_margin_top # Prepend the right margin store_margin_right # Prepend the left margin store_margin_left # Prepend the page vertical centering store_vcenter # Prepend the page horizontal centering store_hcenter # Prepend the page footer store_footer # Prepend the page header store_header # Prepend the vertical page breaks store_vbreak # Prepend the horizontal page breaks store_hbreak # Prepend WSBOOL store_wsbool # Prepend the default row height. store_defrow # Prepend GUTS store_guts # Prepend GRIDSET store_gridset # Prepend PRINTGRIDLINES store_print_gridlines # Prepend PRINTHEADERS store_print_headers # # End of prepend. Read upwards from here. ################################################ # Append store_table store_images store_charts store_filters store_comments store_window2 store_page_view store_zoom store_panes(*@panes) if !@panes.nil? && !@panes.empty? store_selection(*@selection) store_validation_count store_validations store_tab_color store_eof # Prepend the BOF and INDEX records store_index store_bof(0x0010) end ############################################################################### # # _compatibility_mode() # # Set the compatibility mode. # # See the explanation in Workbook::compatibility_mode(). This private method # is mainly used for test purposes. # def compatibility_mode(compatibility = 1) # :nodoc: @compatibility = compatibility end private :compatibility_mode # # 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 WriteExcel 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. # def name @name end # # Set this worksheet as a selected worksheet, i.e. the worksheet has its tab # highlighted. # # The select() method is used to indicate that a worksheet is selected in a # multi-sheet workbook: # # worksheet1.activate # worksheet2.select # worksheet3.select # # A selected worksheet has its tab highlighted. Selecting worksheets is a way # of grouping them together so that, for example, several worksheets could be # printed in one go. A worksheet that has been activated via the activate() # method will also appear as selected. # def select @hidden = 0 # Selected worksheet can't be hidden. @selected = 1 end # # Set this worksheet as the active worksheet, i.e. the worksheet that is # displayed when the workbook is opened. Also set it as selected. # # The activate() method is used to specify which worksheet is initially # visible in a multi-sheet workbook: # # worksheet1 = workbook.add_worksheet('To') # worksheet2 = workbook.add_worksheet('the') # 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 # worksheet can be active. # # The default active worksheet is the first worksheet. # def activate @hidden = 0 # Active worksheet can't be hidden. @selected = 1 @sinfo[:activesheet] = @index end # # Hide this worksheet. # # The hide() method is used to hide a worksheet: # # worksheet2.hide # # You may wish to hide a worksheet in order to avoid confusing a user with # intermediate data or calculations. # # A hidden worksheet can not be activated or selected so this method is # mutually exclusive with the activate() and select() methods. In addition, # since the first worksheet will default to being the active worksheet, # you cannot hide the first worksheet without activating another sheet: # # worksheet2.activate # worksheet1.hide # def hide @hidden = 1 # A hidden worksheet shouldn't be active or selected. @selected = 0 @sinfo[:activesheet] = 0 @sinfo[:firstsheet] = 0 end # # Set this worksheet as the first visible sheet. This is necessary # when there are a large number of worksheets and the activated # worksheet is not visible on the screen. # # The activate() method determines which worksheet is initially selected. # However, if there are a large number of worksheets the selected worksheet # may not appear on the screen. To avoid this you can select which is the # leftmost visible worksheet using set_first_sheet # # 20.times { workbook.add_worksheet } # # worksheet21 = workbook.add_worksheet # worksheet22 = workbook.add_worksheet # # worksheet21.set_first_sheet # worksheet22.activate # # This method is not required very often. The default value is the first # worksheet. # def set_first_sheet @hidden = 0 # Active worksheet can't be hidden. set_firstsheet(@index) end # # Set the worksheet protection flag to prevent accidental modification and to # hide formulas if the locked and hidden format properties have been set. # # The protect() method is used to protect a worksheet from modification: # # worksheet.protect # # It can be turned off in Excel via the # Tools->Protection->Unprotect Sheet menu command. # # The protect() method also has the effect of enabling a cell's locked and # hidden properties if they have been set. A "locked" cell cannot be edited. # A "hidden" cell will display the results of a formula but not the formula # itself. In Excel a cell's locked property is on by default. # # # Set some format properties # unlocked = workbook.add_format(locked => 0) # hidden = workbook.add_format(hidden => 1) # # # Enable worksheet protection # worksheet.protect # # # This cell cannot be edited, it is locked by default # worksheet.write('A1', '=1+2') # # # This cell can be edited # worksheet.write('A2', '=1+2', unlocked) # # # The formula in this cell isn't visible # worksheet.write('A3', '=1+2', hidden) # # See also the set_locked and set_hidden format methods in "CELL FORMATTING". # # You can optionally add a password to the worksheet protection: # # worksheet.protect('drowssap') # # Note, # # the worksheet level password in Excel provides very weak protection. It # does not encrypt your data in any way and it is very easy to deactivate. # Therefore, do not use the above method if you wish to protect sensitive # data or calculations. However, before you get worried, Excel's own # workbook level password protection does provide strong encryption in # Excel 97+. For technical reasons this will never be supported by # WriteExcel. # def protect(password = nil) @protect = 1 @password = encode_password(password) unless password.nil? end # # row : Row Number # height : Format object # format : Format object # hidden : Hidden flag # level : Outline level # collapsed : Collapsed row # # This method is used to set the height and XF format for a row. # Writes the BIFF record ROW. # # This method can be used to change the default properties of a row. 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: # # worksheet.set_row(0, nil, format) # # 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 any format # that was previously specified. # # 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, undef, nil, 1) # # 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. # # 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. # # worksheet.set_row(3, nil, nil, 0, 0, 1) # # For a more complete example see the outline.pl 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. # def set_row(row, height = nil, format = nil, hidden = 0, level = 0, collapsed = 0) record = 0x0208 # Record identifier length = 0x0010 # Number of bytes to follow colMic = 0x0000 # First defined column colMac = 0x0000 # Last defined column # miyRw; # Row height irwMac = 0x0000 # Used by Excel to optimise loading reserved = 0x0000 # Reserved grbit = 0x0000 # Option flags # ixfe; # XF index return if row.nil? # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, 0, 0, 1) != 0 # Check for a format object if format.kind_of?(Format) ixfe = format.xf_index else ixfe = 0x0F end # Set the row height in units of 1/20 of a point. Note, some heights may # not be obtained exactly due to rounding in Excel. # unless height.nil? miyRw = height *20 else miyRw = 0xff # The default row height height = 0 end # Set the limits for the outline levels (0 <= x <= 7). level = 0 if level < 0 level = 7 if level > 7 @outline_row_level = level if level > @outline_row_level # Set the options flags. # 0x10: The fCollapsed flag indicates that the row contains the "+" # when an outline group is collapsed. # 0x20: The fDyZero height flag indicates a collapsed or hidden row. # 0x40: The fUnsynced flag is used to show that the font and row heights # are not compatible. This is usually the case for WriteExcel. # 0x80: The fGhostDirty flag indicates that the row has been formatted. # grbit |= level grbit |= 0x0010 if collapsed != 0 grbit |= 0x0020 if hidden != 0 grbit |= 0x0040 grbit |= 0x0080 unless format.nil? grbit |= 0x0100 header = [record, length].pack("vv") data = [row, colMic, colMac, miyRw, irwMac, reserved, grbit, ixfe].pack("vvvvvvvv") # Store the data or write immediately depending on the compatibility mode. if @compatibility != 0 @row_data[row] = header + data else print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end # Store the row sizes for use when calculating image vertices. # Also store the column formats. @row_sizes[row] = height @row_formats[row] = format unless format.nil? end # # :call-seq: # set_column(first_col, last_col, width, format, hidden, level, collapsed) # set_column(A1_notation, width, format, hidden, level, collapsed) # # Set the width of a single column or a range of columns. #-- # See also: _store_colinfo #++ # # 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. # # 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". # # Examples: # # worksheet.set_column(0, 0, 20) # Column A width set to 20 # worksheet.set_column(1, 3, 30) # Columns B-D width set to 30 # worksheet.set_column('E:E', 20) # Column E width set to 20 # worksheet.set_column('F:H', 30) # Columns F-H width set to 30 # # The width corresponds to the column width value that is specified in Excel. # It is approximately equal to the length of a string in the 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 undef 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 # # 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. # # 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. # 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 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. # # 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. # # worksheet.set_column('H:H', nil, nil, 0, 0, 1) # # For a more complete example see the outline.pl 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. # def set_column(*args) data = args cell = data[0] # Check for a cell reference in A1 notation and substitute row and column if cell =~ /^\D/ data = substitute_cellref(*args) # Returned values $row1 and $row2 aren't required here. Remove them. data.shift # $row1 data.delete_at(1) # $row2 end return if data.size < 3 # Ensure at least $firstcol, $lastcol and $width return if data[0].nil? # Columns must be defined. return if data[1].nil? # Assume second column is the same as first if 0. Avoids KB918419 bug. data[1] = data[0] if data[1] == 0 # Ensure 2nd col is larger than first. Also for KB918419 bug. data[0], data[1] = data[1], data[0] if data[0] > data[1] # Limit columns to Excel max of 255. data[0] = ColMax - 1 if data[0] > ColMax - 1 data[1] = ColMax - 1 if data[1] > ColMax - 1 @colinfo.push(data) # Store the col sizes for use when calculating image vertices taking # hidden columns into account. Also store the column formats. # firstcol, lastcol, width, format, hidden = data width ||= 0 # Ensure width isn't undef. hidden ||= 0 width = 0 if hidden > 1 # Set width to zero if col is hidden (firstcol .. lastcol).each do |col| @col_sizes[col] = width @col_formats[col] = format unless format.nil? end end # # :call-seq: # set_selection(first_row, first_col[, last_row, last_col]) # set_selection('B3') # set_selection('B3:C8') # # Set which cell or cells are selected in a worksheet: see also the # sub _store_selection # # 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". # # Examples: # # worksheet1.set_selection(3, 3) # 1. Cell D4. # worksheet2.set_selection(3, 3, 6, 6) # 2. Cells D4 to G7. # worksheet3.set_selection(6, 6, 3, 3) # 3. Cells G7 to D4. # worksheet4.set_selection('D4') # Same as 1. # worksheet5.set_selection('D4:G7') # Same as 2. # worksheet6.set_selection('G7:D4') # Same as 3. # # The default cell selections is (0, 0), 'A1'. # def set_selection(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end @selection = args end # # :call-seq: # outline_settings(visible, symbols_below, symbols_right, auto_style) # # This method sets the properties for outlining and grouping. The defaults # correspond to Excel's defaults. # # The outline_settings() method is used to control the appearance of # outlines in Excel. Outlines are described in # "OUTLINES AND GROUPING IN EXCEL". # # 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 # 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 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 outline # generator in Excel uses automatic styles when creating an outline. This has # no effect on a file generated by WriteExcel 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. # # The default settings for all of these parameters correspond to Excel's # default parameters. # # The worksheet parameters controlled by outline_settings() are rarely used. # def outline_settings(*args) @outline_on = args[0] || 1 @outline_below = args[1] || 1 @outline_right = args[2] || 1 @outline_style = args[3] || 0 # Ensure this is a boolean vale for Window2 @outline_on = 1 if @outline_on == 0 end # # :call-seq: # freeze_pane(row, col, top_row, left_col) # # Set panes and mark them as frozen. #-- # See also _store_panes(). #++ # # 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 # # 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 do not # want either a vertical or horizontal split. # # Examples: # # worksheet.freeze_panes(1, 0) # Freeze the first row # worksheet.freeze_panes('A2') # Same using A1 notation # 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 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. # # See also the panes.rb program in the examples directory of the distribution. # def freeze_panes(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end # Extra flag indicated a split and freeze. @frozen_no_split = 0 if !args[4].nil? && args[4] != 0 @frozen = 1 @panes = args end # # :call-seq: # split_panes(y, x, top_row, left_col) # # Set panes and mark them as split. #-- # See also _store_panes(). #++ # # 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 # 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 12.75 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. # # Example: # # worksheet.split_panes(12.75, 0, 1, 0) # First row # worksheet.split_panes(0, 8.43, 0, 1) # First column # worksheet.split_panes(12.75, 8.43, 1, 1) # First row and column # # You cannot use A1 notation with this method. # # See also the freeze_panes() method and the panes.pl program in the examples # directory of the distribution. # # Note: # def split_panes(*args) @frozen = 0 @frozen_no_split = 0 @panes = args end # Older method name for backwards compatibility. # *thaw_panes = *split_panes; # # :call-seq: # merge_range(first_row, first_col, last_row, last_col, token, format, utf_16_be) # # This is a wrapper to ensure correct use of the merge_cells method, i.e., # write the first cell of the range, write the formatted blank cells in the # range and then call the merge_cells record. Failing to do the steps in # this order will cause Excel 97 to crash. # # Merging cells can be achieved by setting the merge property of a Format # object, see "CELL FORMATTING". However, this only allows simple Excel5 # style horizontal merging which Excel refers to as "center across selection". # # # The merge_range() method allows you to do Excel97+ style formatting where # the cells can 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) # # WARNING. The format object that is used with a merge_range() # method call is marked internally as being associated with a merged range. # It is a fatal error to use a merged format in a non-merged cell. Instead # you should use separate formats for merged and non-merged cells. This # restriction will be removed in a future release. # # The _utf_16_be_ parameter is optional, see below. # # merge_range() writes its _token_ argument using the worksheet write() # method. Therefore it will handle numbers, strings, formulas or urls as # required. # # Setting the merge property of the format isn't required when you are using # merge_range(). In fact using it will exclude the use of any other horizontal # alignment option. # # Your can specify UTF-16BE worksheet names using an additional optional # parameter: # # str = [0x263a].pack('n') # worksheet.merge_range('B3:D4', str, format, 1) # Smiley # # The full possibilities of this method are shown in the merge3.rb to # merge65.rb programs in the examples directory of the distribution. # def merge_range(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end raise "Incorrect number of arguments" if args.size != 6 and args.size != 7 raise "Format argument is not a format object" unless args[5].kind_of?(Format) rwFirst = args[0] colFirst = args[1] rwLast = args[2] colLast = args[3] string = args[4] format = args[5] encoding = args[6] ? 1 : 0 # Temp code to prevent merged formats in non-merged cells. error = "Error: refer to merge_range() in the documentation. " + "Can't use previously non-merged format in merged cells" raise error if format.used_merge == -1 format.used_merge = 0 # Until the end of this function. # Set the merge_range property of the format object. For BIFF8+. format.set_merge_range # Excel doesn't allow a single cell to be merged raise "Can't merge single cell" if rwFirst == rwLast and colFirst == colLast # Swap last row/col with first row/col as necessary rwFirst, rwLast = rwLast, rwFirst if rwFirst > rwLast colFirst, colLast = colLast, colFirst if colFirst > colLast # Write the first cell if encoding != 0 write_utf16be_string(rwFirst, colFirst, string, format) else write(rwFirst, colFirst, string, format) end # Pad out the rest of the area with formatted blank cells. (rwFirst .. rwLast).each do |row| (colFirst .. colLast).each do |col| next if row == rwFirst and col == colFirst write_blank(row, col, format) end end merge_cells(rwFirst, colFirst, rwLast, colLast) # Temp code to prevent merged formats in non-merged cells. format.used_merge = 1 end # # Set the worksheet zoom factor in the range 10 <= $scale <= 400: # # worksheet1.set_zoom(50) # worksheet2.set_zoom(75) # worksheet3.set_zoom(300) # worksheet4.set_zoom(400) # # The default zoom factor is 100. You cannot zoom to "Selection" because # it is calculated by Excel at run-time. # # Note, set_zoom() does not affect the scale of the printed page. For that # you should use set_print_scale(). # def set_zoom(scale = 100) # Confine the scale to Excel's range if scale < 10 or scale > 400 # carp "Zoom factor $scale outside range: 10 <= zoom <= 400"; scale = 100 end @zoom = scale.to_i end # # Display the worksheet right to left for some eastern versions of Excel. # # The right_to_left() method is used to change the default direction of the # worksheet from left-to-right, with the A1 cell in the top left, to # right-to-left, with the he A1 cell in the top right. # # worksheet.right_to_left # # This is useful when creating Arabic, Hebrew or other near or far eastern # worksheets that use right-to-left as the default direction. # def right_to_left @display_arabic = 1 end # # Hide cell zero values. # # The hide_zero() method is used to hide any zero values that appear in # cells. # # worksheet.hide_zero # # In Excel this option is found under Tools->Options->View. # def hide_zero @display_zeros = 1 end # # Set the colour of the worksheet colour. # # The set_tab_color() method is used to change the colour of the worksheet # tab. This feature is only available in Excel 2002 and later. You can # use one of the standard colour names provided by the Format object or a # colour index. See "COLOURS IN EXCEL" and the set_custom_color() method. # # worksheet1.set_tab_color('red') # worksheet2.set_tab_color(0x0C) # # See the tab_colors.rb program in the examples directory of the distro. # def set_tab_color(colour) color = Format._get_color(colour) color = 0 if color == 0x7FFF # Default color. @tab_color = color end # # :call-seq: # autofilter(first_row, first_col, last_row, last_col) # autofilter("A1:G10") # # Set the autofilter area in the worksheet. # # # This method allows an autofilter to be added to a worksheet. An # autofilter is a way of adding drop down lists to the headers of a 2D range # of worksheet data. This is turn allow users to filter the data based on # simple criteria so that some data is highlighted and some is hidden. # # To add an autofilter to a worksheet: # # worksheet.autofilter(0, 0, 10, 3) # worksheet.autofilter('A1:D11') # Same as above in A1 notation. # # Filter conditions can be applied using the filter_column() method. # # See the autofilter.rb program in the examples directory of the distro # for a more detailed example. # def autofilter(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end return if args.size != 4 # Require 4 parameters row1, col1, row2, col2 = args # Reverse max and min values if necessary. if row2 < row1 tmp = row1 row1 = row2 row2 = tmp end if col2 < col1 tmp = col1 col1 = col2 col2 = col1 end # Store the Autofilter information @filter_area = [row1, row2, col1, col2] @filter_count = 1 + col2 -col1 end # # :call-seq: # filter_column(column, expression) # # Set the column filter criteria. # # 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. Spreadsheet::WriteExcel 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 a # string column name. # # The following operators are available: # # Operator Synonyms # == = eq =~ # != <> ne != # > # < # >= # <= # # and && # or || # # 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: # # '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: # # 'x == Blanks' # 'x == NonBlanks' # # Top 10 style filters can be specified using a expression like the # following: # # Top|Bottom 1-500 Items|% # # For example: # # 'Top 10 Items' # 'Bottom 5 Items' # 'Top 25 %' # 'Bottom 50 %' # # Excel also allows some simple string matching operations: # # 'x =~ b*' # begins with b # 'x !~ b*' # doesn't begin with b # 'x =~ *b' # ends with b # 'x !~ *b' # doesn't end with b # 'x =~ *b*' # contains b # '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 ~. # # 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' # 'Price < 2000' # # Also, note that a filter condition can only be applied to a column 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. # def filter_column(col, expression) raise "Must call autofilter() before filter_column()" if @filter_count == 0 # raise "Incorrect number of arguments to filter_column()" unless @_ == 2 # Check for a column reference in A1 notation and substitute. if col =~ /^\D/ # Convert col ref to a cell ref and then to a col number. no_use, col = substitute_cellref(col + '1') end col_first = @filter_area[2] col_last = @filter_area[3] # 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 tokens = extract_filter_tokens(expression) unless (tokens.size == 3 or tokens.size == 7) raise "Incorrect number of tokens in expression '#{expression}'" end tokens = parse_filter_expression(expression, tokens) @filter_cols[col] = Array.new(tokens) @filter_on = 1 end # # Set the page orientation as portrait. # # This method is used to set the orientation of a worksheet's printed page # to portrait. The default worksheet orientation is portrait, so you won't # generally need to call this method. # def set_portrait @orientation = 1 end # # Set the page orientation as landscape. # def set_landscape @orientation = 0 end # # This method is used to display the worksheet in "Page View" mode. This # is currently only supported by Mac Excel, where it is the default. # def set_page_view @page_view = 1 end # # set_paper() # # Set the paper type. Ex. 1 = US Letter, 9 = A4 # # This method is used to set the paper format for the printed output of a # worksheet. The following paper styles are available: # # Index Paper format Paper size # ===== ============ ========== # 0 Printer default - # 1 Letter 8 1/2 x 11 in # 2 Letter Small 8 1/2 x 11 in # 3 Tabloid 11 x 17 in # 4 Ledger 17 x 11 in # 5 Legal 8 1/2 x 14 in # 6 Statement 5 1/2 x 8 1/2 in # 7 Executive 7 1/4 x 10 1/2 in # 8 A3 297 x 420 mm # 9 A4 210 x 297 mm # 10 A4 Small 210 x 297 mm # 11 A5 148 x 210 mm # 12 B4 250 x 354 mm # 13 B5 182 x 257 mm # 14 Folio 8 1/2 x 13 in # 15 Quarto 215 x 275 mm # 16 - 10x14 in # 17 - 11x17 in # 18 Note 8 1/2 x 11 in # 19 Envelope 9 3 7/8 x 8 7/8 # 20 Envelope 10 4 1/8 x 9 1/2 # 21 Envelope 11 4 1/2 x 10 3/8 # 22 Envelope 12 4 3/4 x 11 # 23 Envelope 14 5 x 11 1/2 # 24 C size sheet - # 25 D size sheet - # 26 E size sheet - # 27 Envelope DL 110 x 220 mm # 28 Envelope C3 324 x 458 mm # 29 Envelope C4 229 x 324 mm # 30 Envelope C5 162 x 229 mm # 31 Envelope C6 114 x 162 mm # 32 Envelope C65 114 x 229 mm # 33 Envelope B4 250 x 353 mm # 34 Envelope B5 176 x 250 mm # 35 Envelope B6 176 x 125 mm # 36 Envelope 110 x 230 mm # 37 Monarch 3.875 x 7.5 in # 38 Envelope 3 5/8 x 6 1/2 in # 39 Fanfold 14 7/8 x 11 in # 40 German Std Fanfold 8 1/2 x 12 in # 41 German Legal Fanfold 8 1/2 x 13 in # # Note, it is likely that not all of these paper types will be available to # the end user since it will depend on the paper formats that the user's # printer supports. Therefore, it is best to stick to standard paper types. # # worksheet.set_paper(1) # US Letter # worksheet.set_paper(9) # A4 # # If you do not specify a paper type the worksheet will print using the # printer's default paper. # def set_paper(paper_size = 0) @paper_size = paper_size end # # Center the worksheet data horizontally between the margins on the printed page. # def center_horizontally @hcenter = 1 end # # Center the worksheet data vertically between the margins on the printed page: # def center_vertically @vcenter = 1 end # # Set all the page margins to the same value in inches. # # There are several methods available for setting the worksheet margins on # the printed page: # # set_margins() # Set all margins to the same value # set_margins_LR() # Set left and right margins to the same value # set_margins_TB() # Set top and bottom margins to the same value # set_margin_left(); # Set left margin # set_margin_right(); # Set right margin # set_margin_top(); # Set top margin # set_margin_bottom(); # Set bottom margin # # All of these methods take a distance in inches as a parameter. # # Note: 1 inch = 25.4mm. ;-) The default left and right margin is 0.75 inch. # The default top and bottom margin is 1.00 inch. # def set_margins(margin) set_margin_left(margin) set_margin_right(margin) set_margin_top(margin) set_margin_bottom(margin) end ############################################################################### # # set_margins_LR() # # Set the left and right margins to the same value in inches. # def set_margins_LR(margin) set_margin_left(margin) set_margin_right(margin) end ############################################################################### # # set_margins_TB() # # Set the top and bottom margins to the same value in inches. # def set_margins_TB(margin) set_margin_top(margin) set_margin_bottom(margin) end ############################################################################### # # set_margin_left() # # Set the left margin in inches. # def set_margin_left(margin = 0.75) @margin_left = margin end ############################################################################### # # set_margin_right() # # Set the right margin in inches. # def set_margin_right(margin = 0.75) @margin_right = margin end ############################################################################### # # set_margin_top() # # Set the top margin in inches. # def set_margin_top(margin = 1.00) @margin_top = margin end ############################################################################### # # set_margin_bottom() # # Set the bottom margin in inches. # def set_margin_bottom(margin = 1.00) @margin_bottom = margin end # # Set the page header caption and optional margin. # # Headers and footers are generated using a _string_ which is a combination # of plain text and control characters. The _margin_ parameter is optional. # # The available control character are: # # Control Category Description # ======= ======== =========== # &L Justification Left # &C Center # &R Right # # &P Information Page number # &N Total number of pages # &D Date # &T Time # &F File name # &A Worksheet name # &Z Workbook path # # &fontsize Font Font size # &"font,style" Font name and style # &U Single underline # &E Double underline # &S Strikethrough # &X Superscript # &Y Subscript # # && Miscellaneous Literal ampersand & # # Text in headers and footers can be justified (aligned) to the left, center # and right by prefixing the text with the control characters &L, &C and &R. # # For example (with ASCII art representation of the results): # # worksheet.set_header('&LHello') # # --------------------------------------------------------------- # | | # | Hello | # | | # # # worksheet.set_header('&CHello') # # --------------------------------------------------------------- # | | # | Hello | # | | # # # worksheet.set_header('&RHello') # # --------------------------------------------------------------- # | | # | Hello | # | | # # For simple text, if you do not specify any justification the text will be # centred. However, you must prefix the text with &C if you specify a font # name or any other formatting: # # worksheet.set_header('Hello') # # --------------------------------------------------------------- # | | # | Hello | # | | # # You can have text in each of the justification regions: # # worksheet.set_header('&LCiao&CBello&RCielo') # # --------------------------------------------------------------- # | | # | Ciao Bello Cielo | # | | # # The information control characters act as variables that Excel will update # as the workbook or worksheet changes. Times and dates are in the users # default format: # # worksheet.set_header('&CPage &P of &N') # # --------------------------------------------------------------- # | | # | Page 1 of 6 | # | | # # # worksheet.set_header('&CUpdated at &T') # # --------------------------------------------------------------- # | | # | Updated at 12:30 PM | # | | # # You can specify the font size of a section of the text by prefixing it # with the control character &n where n is the font size: # # worksheet1.set_header('&C&30Hello Big' ) # worksheet2.set_header('&C&10Hello Small') # # You can specify the font of a section of the text by prefixing it with the # control sequence &"font,style" where fontname is a font name such as # "Courier New" or "Times New Roman" and style is one of the standard Windows # font descriptions: "Regular", "Italic", "Bold" or "Bold Italic": # # worksheet1.set_header('&C&"Courier New,Italic"Hello') # worksheet2.set_header('&C&"Courier New,Bold Italic"Hello') # worksheet3.set_header('&C&"Times New Roman,Regular"Hello') # # It is possible to combine all of these features together to create # sophisticated headers and footers. As an aid to setting up complicated # headers and footers you can record a page set-up as a macro in Excel and # look at the format strings that VBA produces. Remember however that VBA uses # two double quotes "" to indicate a single double quote. For the last example # above the equivalent VBA code looks like this: # # .LeftHeader = "" # .CenterHeader = "&""Times New Roman,Regular""Hello" # .RightHeader = "" # # To include a single literal ampersand & in a header or footer you should # use a double ampersand &&: # # worksheet1.set_header('&CCuriouser && Curiouser - Attorneys at Law') # # As stated above the margin parameter is optional. As with the other margins # the value should be in inches. The default header and footer margin is 0.50 # inch. The header and footer margin size can be set as follows: # # worksheet.set_header('&CHello', 0.75) # # The header and footer margins are independent of the top and bottom margins. # # Note, the header or footer string must be less than 255 characters. Strings # longer than this will not be written and a warning will be generated. # # worksheet.set_header("&C\x{263a}") # # See, also the headers.rb program in the examples directory of the # distribution. # def set_header(string = '', margin = 0.50, encoding = 0) limit = encoding != 0 ? 255 *2 : 255 # Handle utf8 strings if string =~ NonAscii string = NKF.nkf('-w16B0 -m0 -W', string) encoding = 1 end if string.length >= limit # carp 'Header string must be less than 255 characters'; return end @header = string @margin_header = margin @header_encoding = encoding end # # Set the page footer caption and optional margin. # # The syntax of the set_footer() method is the same as set_header(), see # there. # def set_footer(string = '', margin = 0.50, encoding = 0) limit = encoding != 0 ? 255 *2 : 255 # Handle utf8 strings if string =~ NonAscii string = NKF.nkf('-w16B0 -m0 -W', string) encoding = 1 end if string.length >= limit # carp 'Header string must be less than 255 characters'; return end @footer = string @margin_footer = margin @footer_encoding = encoding end # # Set the rows to repeat at the top of each printed page. #-- # See also the _store_name_xxxx() methods in Workbook.rb. #++ # # Set the number of rows to repeat at the top of each printed page. # # For large Excel documents it is often desirable to have the first row or # rows of the worksheet print out at the top of each page. This can be # achieved by using the repeat_rows() method. The parameters _first_row_ and # _last_row_ are zero based. The _last_row_ parameter is optional if you # only wish to specify one row: # # worksheet1.repeat_rows(0) # Repeat the first row # worksheet2.repeat_rows(0, 1) # Repeat the first two rows # def repeat_rows(first_row, last_row = nil) @title_rowmin = first_row @title_rowmax = last_row || first_row # Second row is optional end # # :call-seq: # repeat_columns(first_col[, last_col]) # repeat_columns(A1_notation) # # Set the columns to repeat at the left hand side of each printed page. #-- # See also the _store_names() methods in Workbook.pm. #++ # # For large Excel documents it is often desirable to have the first column # or columns of the worksheet print out at the left hand side of each page. # This can be achieved by using the repeat_columns() method. The parameters # _first_column_ and _last_column_ are zero based. The _last_column_ # parameter is optional if you only wish to specify one column. You can also # specify the columns using A1 column notation, see the note about # "Cell notation". # # worksheet1.repeat_columns(0) # Repeat the first column # worksheet2.repeat_columns(0, 1) # Repeat the first two columns # worksheet3.repeat_columns('A:A') # Repeat the first column # worksheet4.repeat_columns('A:B') # Repeat the first two columns # def repeat_columns(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) # Returned values $row1 and $row2 aren't required here. Remove them. args.shift # $row1 args.delete_at(1) # $row2 end @title_colmin = args[0] @title_colmax = args[1] || args[0] # Second col is optional end # # :call-seq: # hide_gridlines(option = 1) # # Set the option to hide gridlines on the screen and the printed page. #-- # There are two ways of doing this in the Excel BIFF format: The first is by # setting the DspGrid field of the WINDOW2 record, this turns off the screen # and subsequently the print gridline. The second method is to via the # PRINTGRIDLINES and GRIDSET records, this turns off the printed gridlines # only. The first method is probably sufficient for most cases. The second # method is supported for backwards compatibility. Porters take note. #++ # # This method is used to hide the gridlines on the screen and printed page. # Gridlines are the lines that divide the cells on a worksheet. Screen and # printed gridlines are turned on by default in an Excel worksheet. If you # have defined your own cell borders you may wish to hide the default # gridlines. # # worksheet.hide_gridlines # # The following values of _option_ are valid: # # 0 : Don't hide gridlines # 1 : Hide printed gridlines only # 2 : Hide screen and printed gridlines # # If you don't supply an argument the default option is 1, i.e. # only the printed gridlines are hidden. # def hide_gridlines(option = 1) if option == 0 @print_gridlines = 1 # 1 = display, 0 = hide @screen_gridlines = 1 elsif option == 1 @print_gridlines = 0 @screen_gridlines = 1 else @print_gridlines = 0 @screen_gridlines = 0 end end # # Set the option to print the row and column headers on the printed page. # See also the _store_print_headers() method. # # An Excel worksheet looks something like the following; # # ------------------------------------------ # | | A | B | C | D | ... # ------------------------------------------ # | 1 | | | | | ... # | 2 | | | | | ... # | 3 | | | | | ... # | 4 | | | | | ... # |...| ... | ... | ... | ... | ... # # The headers are the letters and numbers at the top and the left of the # worksheet. Since these headers serve mainly as a indication of position on # the worksheet they generally do not appear on the printed page. If you wish # to have them printed you can use the print_row_col_headers() method : # # worksheet.print_row_col_headers # # Do not confuse these headers with page headers as described in the # set_header() section. # def print_row_col_headers(option = nil) if option.nil? @print_headers = 1 else @print_headers = option end end # # :call-seq: # print_area(first_row, first_col, last_row, last_col) # print_area(A1_notation) # # Set the area of each worksheet that will be printed. #-- # See also the_store_names() methods in Workbook.rb. #++ # # This method is used to specify the area of the worksheet that will be # printed. All four parameters must be specified. You can also use # A1 notation, see the note about "Cell notation". # # 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) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end return if args.size != 4 # Require 4 parameters @print_rowmin, @print_colmin, @print_rowmax, @print_colmax = args end # # Set the order in which pages are printed. # # The print_across method is used to change the default print direction. # This is referred to by Excel as the sheet "page order". # # worksheet.print_across # # The default page order is shown below for a worksheet that extends over 4 # pages. The order is called "down then across": # # [1] [3] # [2] [4] # # However, by using the print_across method the print order will be changed # to "across then down": # # [1] [2] # [3] [4] # def print_across @page_order = 1 end # # Store the vertical and horizontal number of pages that will define the # maximum area printed. #-- # See also _store_setup() and _store_wsbool() below. #++ # # 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 # exceeds the specified number of pages it will be scaled down to fit. # This guarantees that the printed area will always appear on the # specified number of pages even if the page size or margins change. # # worksheet1.fit_to_pages(1, 1) # Fit to 1x1 pages # worksheet2.fit_to_pages(2, 1) # Fit to 2x1 pages # worksheet3.fit_to_pages(1, 2) # Fit to 1x2 pages # # The print area can be defined using the print_area() method. # # A common requirement is to fit the printed output to n pages wide but # have the height be as long as necessary. To achieve this set the _height_ # to zero or leave it blank: # # worksheet1.fit_to_pages(1, 0) # 1 page wide and as long as necessary # worksheet2.fit_to_pages(1) # The same # # Note that although it is valid to use both fit_to_pages() and set_print #_scale() on the same worksheet only one of these options can be active at # a time. The last method call made will set the active option. # # Note that fit_to_pages() will override any manual page breaks that are # defined in the worksheet. # def fit_to_pages(width = 0, height = 0) @fit_page = 1 @fit_width = width @fit_height = height end # # Set the scale factor of the printed page. Scale factors in the range # 10 <= _scale_ <= 400 are valid: # # worksheet1.set_print_scale(50) # worksheet2.set_print_scale(75) # worksheet3.set_print_scale(300) # worksheet4.set_print_scale(400) # # The default scale factor is 100. Note, set_print_scale() does not affect # the scale of the visible page in Excel. For that you should use set_zoom(). # # Note also that although it is valid to use both fit_to_pages() and # set_print_scale() on the same worksheet only one of these options can be # active at a time. The last method call made will set the active option. # def set_print_scale(scale = 100) # Confine the scale to Excel's range if scale < 10 or scale > 400 # carp "Print scale $scale outside range: 10 <= zoom <= 400"; scale = 100 end # Turn off "fit to page" option @fit_page = 0 @print_scale = scale.to_i end # # Store the horizontal page breaks on a worksheet. _breaks_ is Fixnum or Array # of Fixnum. # # Add horizontal page breaks to a worksheet. A page break causes all the # data that follows it to be printed on the next page. Horizontal page breaks # act between rows. To create a page break between rows 20 and 21 you must # specify the break at row 21. However in zero index notation this is # actually row 20. So you can pretend for a small while that you are using 1 # index notation: # # worksheet1.set_h_pagebreaks(20) # Break between row 20 and 21 # # The set_h_pagebreaks() method will accept a array of page breaks and you # can call it more than once: # # worksheet2.set_h_pagebreaks([ 20, 40, 60, 80, 100]) # Add breaks # worksheet2.set_h_pagebreaks([120, 140, 160, 180, 200]) # Add some more # # Note: If you specify the "fit to page" option via the fit_to_pages() # method it will override all manual page breaks. # # There is a silent limitation of about 1000 horizontal page breaks per # worksheet in line with an Excel internal limitation. # def set_h_pagebreaks(breaks) @hbreaks += breaks.kind_of?(Array) ? breaks : [breaks] end # # Store the vertical page breaks on a worksheet. _breaks_ is Fixnum or Array # of Fixnum. # # Add vertical page breaks to a worksheet. A page break causes all the data # that follows it to be printed on the next page. Vertical page breaks act # between columns. To create a page break between columns 20 and 21 you must # specify the break at column 21. However in zero index notation this is # actually column 20. So you can pretend for a small while that you are using # 1 index notation: # # worksheet1.set_v_pagebreaks(20) # Break between column 20 and 21 # # The set_v_pagebreaks() method will accept a list of page breaks and you # can call it more than once: # # worksheet2.set_v_pagebreaks([ 20, 40, 60, 80, 100]) # Add breaks # worksheet2.set_v_pagebreaks([120, 140, 160, 180, 200]) # Add some more # # 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(breaks) @vbreaks += breaks.kind_of?(Array) ? breaks : [breaks] end ############################################################################### # # _extract_filter_tokens($expression) # # Extract the tokens from the filter expression. The tokens are mainly non- # whitespace groups. The only tricky part is to extract string tokens that # contain whitespace and/or quoted double quotes (Excel's escaped quotes). # # Examples: 'x < 2000' # 'x > 2000 and x < 5000' # 'x = "foo"' # 'x = "foo bar"' # 'x = "foo "" bar"' # def extract_filter_tokens(expression = nil) #:nodoc: return [] unless expression # @tokens = ($expression =~ /"(?:[^"]|"")*"|\S+/g); #" tokens = [] str = expression while str =~ /"(?:[^"]|"")*"|\S+/ tokens << $& str = $~.post_match end # Remove leading and trailing quotes and unescape other quotes tokens.map! do |token| token.sub!(/^"/, '') token.sub!(/"$/, '') token.gsub!(/""/, '"') # if token is number, convert to numeric. if token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/ token.to_f == token.to_i ? token.to_i : token.to_f else token end end return tokens end # private :extract_filter_tokens ############################################################################### # # _parse_filter_expression(expression, @token) # # Converts the tokens of a possibly conditional expression into 1 or 2 # sub expressions for further parsing. # # Examples: # ('x', '==', 2000) -> exp1 # ('x', '>', 2000, 'and', 'x', '<', 5000) -> exp1 and exp2 # def parse_filter_expression(expression, tokens) #:nodoc: # The number of tokens will be either 3 (for 1 expression) # or 7 (for 2 expressions). # if (tokens.size == 7) conditional = tokens[3] if conditional =~ /^(and|&&)$/ conditional = 0 elsif conditional =~ /^(or|\|\|)$/ conditional = 1 else raise "Token '#{conditional}' is not a valid conditional " + "in filter expression '#{expression}'" end expression_1 = parse_filter_tokens(expression, tokens[0..2]) expression_2 = parse_filter_tokens(expression, tokens[4..6]) return [expression_1, conditional, expression_2].flatten else return parse_filter_tokens(expression, tokens) end end # private :parse_filter_expression ############################################################################### # # _parse_filter_tokens(@token) # (@expression, @token) # # Parse the 3 tokens of a filter expression and return the operator and token. # def parse_filter_tokens(expression, tokens) #:nodoc: operators = { '==' => 2, '=' => 2, '=~' => 2, 'eq' => 2, '!=' => 5, '!~' => 5, 'ne' => 5, '<>' => 5, '<' => 1, '<=' => 3, '>' => 4, '>=' => 6, } operator = operators[tokens[1]] token = tokens[2] # Special handling of "Top" filter expressions. if tokens[0] =~ /^top|bottom$/i value = tokens[1] if (value =~ /\D/ or value.to_i < 1 or value.to_i > 500) raise "The value '#{value}' in expression '#{expression}' " + "must be in the range 1 to 500" end token.downcase! if (token != 'items' and token != '%') raise "The type '#{token}' in expression '#{expression}' " + "must be either 'items' or '%'" end if (tokens[0] =~ /^top$/i) operator = 30 else operator = 32 end if (tokens[2] == '%') operator = operator + 1 end token = value end if (not operator and tokens[0]) raise "Token '#{tokens[1]}' is not a valid operator " + "in filter expression '#{expression}'" end # Special handling for Blanks/NonBlanks. if (token =~ /^blanks|nonblanks$/i) # Only allow Equals or NotEqual in this context. if (operator != 2 and operator != 5) raise "The operator '#{tokens[1]}' in expression '#{expression}' " + "is not valid in relation to Blanks/NonBlanks'" end token.downcase! # The operator should always be 2 (=) to flag a "simple" equality in # the binary record. Therefore we convert <> to =. if (token == 'blanks') if (operator == 5) operator = 2 token = 'nonblanks' end else if (operator == 5) operator = 2 token = 'blanks' end end end # if the string token contains an Excel match character then change the # operator type to indicate a non "simple" equality. if (operator == 2 and token =~ /[*?]/) operator = 22 end return [operator, token] end private :parse_filter_tokens # # Causes the write() method to treat integers with a leading zero as a string. # This ensures that any leading zeros such, as in zip codes, are maintained. # # This method changes the default handling of integers with leading zeros # when using the write() method. # # The write() method uses regular expressions to determine what type of data # to write to an Excel worksheet. If the data looks like a number it writes # a number using write_number(). One problem with this approach is that # occasionally data looks like a number but you don't want it treated as # a number. # # Zip codes and ID numbers, for example, often start with a leading zero. # If you write this data as a number then the leading zero(s) will be # stripped. This is the also the default behaviour when you enter data # manually in Excel. # # To get around this you can use one of three options. Write a formatted # number, write the number as a string or use the keep_leading_zeros() # method to change the default behaviour of write(): # # # Implicitly write a number, the leading zero is removed: 1209 # worksheet.write('A1', '01209') # # # Write a zero padded number using a format: 01209 # my $format1 = $workbook.add_format(num_format => '00000') # $worksheet.write('A2', '01209', $format1) # # # Write explicitly as a string: 01209 # $worksheet.write_string('A3', '01209') # # # Write implicitly as a string: 01209 # $worksheet.keep_leading_zeros() # $worksheet.write('A4', '01209') # # The above code would generate a worksheet that looked like the following: # # ----------------------------------------------------------- # | | A | B | C | D | ... # ----------------------------------------------------------- # | 1 | 1209 | | | | ... # | 2 | 01209 | | | | ... # | 3 | 01209 | | | | ... # | 4 | 01209 | | | | ... # # The examples are on different sides of the cells due to the fact that Excel # displays strings with a left justification and numbers with a right # justification by default. You can change this by using a format to justify # the data, see "CELL FORMATTING". # # It should be noted that if the user edits the data in examples A3 and A4 # the strings will revert back to numbers. Again this is Excel's default # behaviour. To avoid this you can use the text format @: # # # Format as a string (01209) # format2 = workbook.add_format(num_format => '@') # worksheet.write_string('A5', '01209', $format2) # # The keep_leading_zeros() property is off by default. The keep #_leading_zeros() method takes 0 or 1 as an argument. It defaults to 1 if # an argument isn't specified: # # worksheet.keep_leading_zeros() # Set on # worksheet.keep_leading_zeros(1) # Set on # worksheet.keep_leading_zeros(0) # Set off # # See also the add_write_handler() method. # def keep_leading_zeros(val = true) @leading_zeros = val end ############################################################################### # # show_comments() # # Make any comments in the worksheet visible. # # This method is used to make all cell comments visible when a worksheet is # opened. # # Individual comments can be made visible using the visible parameter of the # write_comment method (see above): # # worksheet.write_comment('C3', 'Hello', visible => 1) # # If all of the cell comments have been made visible you can hide individual # comments as follows: # # worksheet.write_comment('C3', 'Hello', visible => 0) # # def show_comments(val = nil) @comments_visible = val.nil? ? 1 : val end ############################################################################### # # set_comments_author() # # Set the default author of the cell comments. # def set_comments_author(author = '', author_enc = 0) @comments_author = author @comments_author_enc = author_enc end # # Set the start page number. # # The set_start_page() method is used to set the number of the starting page # when the worksheet is printed out. The default value is 1. # # worksheet.set_start_page(2) # def set_start_page(start_page = 1) @page_start = start_page @custom_start = 1 end ############################################################################### # # set_first_row_column() # # Set the topmost and leftmost visible row and column. # TODO: Document this when tested fully for interaction with panes. # def set_first_row_column(row = 0, col = 0) row = RowMax - 1 if row > RowMax - 1 col = ColMax - 1 if col > ColMax - 1 @first_row = row @first_col = col end # # Allow the user to add their own matches and handlers to the write() method. # # This method is used to extend the WriteExcel write() method to handle user # defined data. # # If you refer to the section on write() above you will see that it acts as an # alias for several more specific write_* methods. However, it doesn't always # act in exactly the way that you would like it to. # # One solution is to filter the input data yourself and call the appropriate # write_* method. Another approach is to use the add_write_handler() method # to add your own automated behaviour to write(). # # The add_write_handler() method take two arguments, _re_, a regular # expression to match incoming data and _code_ a callback function to handle # the matched data: # # worksheet.add_write_handler(qr/^\d\d\d\d$/, \&my_write) # # (In the these examples the qr operator is used to quote the regular expression # strings, see perlop for more details). # # The method is used as follows. say you wished to write 7 digit ID numbers as # a string so that any leading zeros were preserved*, you could do something # like the following: # # worksheet.add_write_handler(qr/^\d{7}$/, \&write_my_id) # # sub write_my_id { # my $worksheet = shift; # return $worksheet->write_string(@_); # } # # * You could also use the keep_leading_zeros() method for this. # # Then if you call write() with an appropriate string it will be handled automatically: # # # Writes 0000000. It would normally be written as a number; 0. # $worksheet->write('A1', '0000000'); # # The callback function will receive a reference to the calling worksheet # and all of the other arguments that were passed to write(). The callback # will see an @_ argument list that looks like the following: # # $_[0] A ref to the calling worksheet. * # $_[1] Zero based row number. # $_[2] Zero based column number. # $_[3] A number or string or token. # $_[4] A format ref if any. # $_[5] Any other arguments. # ... # # * It is good style to shift this off the list so the @_ is the same # as the argument list seen by write(). # # Your callback should return() the return value of the write_* method that # was called or undef to indicate that you rejected the match and want # write() to continue as normal. # # So for example if you wished to apply the previous filter only to ID # values that occur in the first column you could modify your callback # function as follows: # # sub write_my_id { # my $worksheet = shift; # my $col = $_[1]; # # if ($col == 0) { # return $worksheet->write_string(@_); # } # else { # # Reject the match and return control to write() # return undef; # } # } # # Now, you will get different behaviour for the first column and other # columns: # # $worksheet->write('A1', '0000000'); # Writes 0000000 # $worksheet->write('B1', '0000000'); # Writes 0 # # You may add more than one handler in which case they will be called in the # order that they were added. # # Note, the add_write_handler() method is particularly suited for handling # dates. # # See the write_handler 1-4 programs in the examples directory for further # examples. # def add_write_handler(regexp, code_ref) # return unless ref $_[1] eq 'CODE'; @write_match.push([regexp, code_ref]) end # # :call-seq: # write(row, col, token, format) # write(A1_notation, token, format) # # Parse token and call appropriate write method. row and column are zero # indexed. format is optional. # # The write_url() methods have a flag to prevent recursion when writing a # string that looks like a url. # # Returns: return value of called subroutine # # # 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 # specific methods: # write_string() # write_number() # write_blank() # write_formula() # write_url() # write_row() # write_col() # # The general rule is that if the data looks like a something then a # something is written. Here are some examples in both row-column # and A1 notation: # # Same as: # worksheet.write(0, 0, 'Hello' ) # write_string() # worksheet.write(1, 0, 'One' ) # write_string() # worksheet.write(2, 0, 2 ) # write_number() # worksheet.write(3, 0, 3.00001 ) # write_number() # worksheet.write(4, 0, "" ) # write_blank() # worksheet.write(5, 0, '' ) # write_blank() # worksheet.write(6, 0, nil ) # write_blank() # worksheet.write(7, 0 ) # write_blank() # worksheet.write(8, 0, 'http://www.ruby-lang.org/') # write_url() # worksheet.write('A9', 'ftp://ftp.ruby-lang.org/' ) # write_url() # worksheet.write('A10', 'internal:Sheet1!A1' ) # write_url() # worksheet.write('A11', 'external:c:\foo.xls' ) # write_url() # worksheet.write('A12', '=A3 + 3*A4' ) # write_formula() # worksheet.write('A13', '=SIN(PI()/4)' ) # write_formula() # worksheet.write('A14', ['name', 'company'] ) # write_row() # worksheet.write('A15', [ ['name', 'company'] ] ) # write_col() # # And if the keep_leading_zeros property is set: # $worksheet.write('A16, 2 ); # write_number() # $worksheet.write('A17, 02 ); # write_string() # $worksheet.write('A18, 00002 ); # write_string() # # The "looks like" rule is defined by regular expressions: # # write_number() if _token_ is a number based on the following regex: # token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/. # # write_string() if keep_leading_zeros() is set and _token_ is an integer # with leading zeros based on the following regex: token =~ /^0\d+$/. # # write_blank() if _token_ is undef or a blank string: undef, "" or ''. # # write_url() if _token_ is a http, https, ftp or mailto URL based on the # following regexes: token =~ m|^[fh]tt?ps?://| or $token =~ m|^mailto:|. # # write_url() if _token_ is an internal or external sheet reference based # on the following regex: token =~ m[^(in|ex)ternal:]. # # write_formula() if the first character of _token_ is "=". # # write_row() if _token_ is an array. # # write_col() if _token+ is an array of array. # # write_string() if none of the previous conditions apply. # # The format parameter is optional. It should be a valid Format object, see # "CELL FORMATTING": # # format = workbook.add_format # format.set_bold # format.set_color('red') # format.set_align('center') # # worksheet.write(4, 0, 'Hello', format) # Formatted string # # The write() method will ignore empty strings or undef tokens unless a # format is also supplied. As such you needn't worry about special # handling for empty or undef values in your data. See also the # write_blank() method. # # One problem with the 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 write this data as a # number then the leading zero(s) will be stripped. You can change this # default behaviour by using the keep_leading_zeros() method. While this # property is in place any integers with leading zeros will be treated as # strings and the zeros will be preserved. See the keep_leading_zeros() # section for a full discussion of this issue. # # You can also add your own data handlers to the write() method using # add_write_handler(). # # The write methods return: # # 0 for success. # -1 for insufficient number of arguments. # -2 for row or column out of bounds. # -3 for string too long. # def write(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end token = args[2] # Handle undefs as blanks token ||= '' # First try user defined matches. @write_match.each do |aref| re = aref[0] sub = aref[1] if token =~ Regexp.new(re) match = eval("#{sub} self, args") return match unless match.nil? end end # Match an array ref. if token.kind_of?(Array) return write_row(*args) elsif token.kind_of?(Numeric) return write_number(*args) # Match http, https or ftp URL elsif token =~ %r|^[fh]tt?ps?://| and @writing_url == 0 return write_url(*args) # Match mailto: elsif token =~ %r|^mailto:| and @writing_url == 0 return write_url(*args) # Match internal or external sheet link elsif token =~ %r!^(?:in|ex)ternal:! and @writing_url == 0 return write_url(*args) # Match formula elsif token =~ /^=/ return write_formula(*args) # Match blank elsif token == '' args.delete_at(2) # remove the empty string from the parameter list return write_blank(*args) else return write_string(*args) end end # # :call-seq: # write_number(row, col, token[, format]) # write_number(A1_notation, token[, format]) # # Write a double to the specified row and column (zero indexed). # An integer can be written as a double. Excel will display an # integer. $format is optional. # # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # # Write an integer or a float to the cell specified by row and column # # worksheet.write_number(0, 0, 123456) # worksheet.write_number('A2', 2.3451) # # See the note about "Cell notation". The format parameter is optional. # # In general it is sufficient to use the write() method. # def write_number(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end return -1 if args.size < 3 # Check the number of args record = 0x0203 # Record identifier length = 0x000E # Number of bytes to follow row = args[0] # Zero indexed row col = args[1] # Zero indexed column num = args[2] xf = xf_record_index(row, col, args[3]) # The cell format # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, col) != 0 header = [record, length].pack('vv') data = [row, col, xf].pack('vvv') xl_double = [num].pack("d") xl_double.reverse! if @byte_order != 0 && @byte_order != '' # Store the data or write immediately depending on the compatibility mode. if @compatibility != 0 tmp = [] tmp[col] = header + data + xl_double @table[row] = tmp else print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data, xl_double) end return 0 end # # :call-seq: # write_string(row, col, token[, format]) # write_string(A1_notation, token[, format]) # # Write a string to the specified row and column (zero indexed). # # The format parameter is optional. # # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # -3 : long string truncated to 255 chars # # # worksheet.write_string(0, 0, 'Your text here') # worksheet.write_string('A2', 'or here') # # 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. # # The write() method will also handle strings in UTF-8 format. # You can also write Unicode in UTF16 format via the # write_utf16be_string() method. # # In general it is sufficient to use the 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 # worksheet.write_string('A1', '01209') # # However, if the user edits this string Excel may convert it back to a # number. To get around this you can use the Excel text format @: # # # Format as a string. Doesn't change to a number when edited # format1 = workbook.add_format(num_format => '@') # worksheet.write_string('A2', '01209', format1) # def write_string(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end return -1 if (args.size < 3) # Check the number of args record = 0x00FD # Record identifier length = 0x000A # Bytes to follow row = args[0] # Zero indexed row col = args[1] # Zero indexed column str = args[2].to_s strlen = str.length xf = xf_record_index(row, col, args[3]) # The cell format encoding = 0x0 str_error = 0 # Handle utf8 strings if str =~ NonAscii return write_utf16le_string(row, col, NKF.nkf('-w16L0 -m0 -W', str), args[3]) end # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, col) != 0 # Limit the string to the max number of chars. if (strlen > 32767) str = str[0, 32767] str_error = -3 end # Prepend the string with the type. str_header = [str.length, encoding].pack('vC') str = str_header + str if @sinfo[:str_table][str].nil? @sinfo[:str_table][str] = @sinfo[:str_unique] @sinfo[:str_unique] += 1 end @sinfo[:str_total] += 1 header = [record, length].pack('vv') data = [row, col, xf, @sinfo[:str_table][str]].pack('vvvV') # Store the data or write immediately depending on the compatibility mode. if @compatibility != 0 tmp = [] tmp[col] = header + data @table[row] = tmp else print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end return str_error end # # :call-seq: # write_blank(row, col , format) -> Fixnum # write_blank(A1_notation, format) -> Fixnum # # Write a blank cell to the specified row and column (zero indexed). # A blank cell is used to specify formatting without adding a string # or a number. # # A blank cell without a format serves no purpose. Therefore, we don't write # a BLANK record unless a format is specified. This is mainly an optimisation # for the write_row() and write_col() methods. # # Returns 0 : normal termination (including no format) # -1 : insufficient number of arguments # -2 : row or column out of range # #This method is used to add formatting to a cell which doesn't contain a # string or number value. # # Excel differentiates between an "Empty" cell and a "Blank" cell. An # "Empty" cell is a cell which doesn't contain data whilst a "Blank" cell # is a cell which doesn't contain data but does contain formatting. Excel # stores "Blank" cells but ignores "Empty" cells. # # As such, if you write an empty cell without formatting it is ignored: # # worksheet.write('A1', nil, format) # write_blank() # worksheet.write('A2', nil ) # Ignored # # This seemingly uninteresting fact means that you can write arrays of data # without special treatment for undef or empty string values. # # See the note about "Cell notation". # def write_blank(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end # Check the number of args return -1 if args.size < 2 # Don't write a blank cell unless it has a format return 0 if args[2].nil? record = 0x0201 # Record identifier length = 0x0006 # Number of bytes to follow row = args[0] # Zero indexed row col = args[1] # Zero indexed column xf = xf_record_index(row, col, args[2]) # The cell format # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, col) != 0 header = [record, length].pack('vv') data = [row, col, xf].pack('vvv') # Store the data or write immediately depending on the compatibility mode. if @compatibility != 0 tmp = [] tmp[col] = header + data @table[row] = tmp else print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end return 0 end # # :call-seq: # write_formula(row, col , formula[, format, value]) -> Fixnum # write_formula(A1_notation, formula[, format, value]) -> Fixnum # # Write a formula to the specified row and column (zero indexed). # # format is optional. # value is an optional result of the formula that can be supplied by the # user. # # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # # Write a formula or function to the cell specified by row and column: # # worksheet.write_formula(0, 0, '=$B$3 + B4' ) # worksheet.write_formula(1, 0, '=SIN(PI()/4)') # worksheet.write_formula(2, 0, '=SUM(B1:B5)' ) # worksheet.write_formula('A4', '=IF(A3>1,"Yes", "No")' ) # worksheet.write_formula('A5', '=AVERAGE(1, 2, 3, 4)' ) # worksheet.write_formula('A6', '=DATEVALUE("1-Jan-2001")') # # See the note about "Cell notation". For more information about writing # Excel formulas see "FORMULAS AND FUNCTIONS IN EXCEL" # # See also the section "Improving performance when working with formulas" # and the store_formula() and repeat_formula() methods. # # If required, it is also possible to specify the calculated value of the # formula. This is occasionally necessary when working with non-Excel # applications that don't calculated the value of the formula. The # calculated value is added at the end of the argument list: # # worksheet.write('A1', '=2+2', format, 4); # # However, this probably isn't something that will ever need to do. If you # do use this feature then do so with care. # # =FORMULAS AND FUNCTIONS IN EXCEL # # ==Caveats # # The first thing to note is that there are still some outstanding issues # with the implementation of formulas and functions: # # 1. Writing a formula is much slower than writing the equivalent string. # 2. You cannot use array constants, i.e. {1;2;3}, in functions. # 3. Unary minus isn't supported. # 4. Whitespace is not preserved around operators. # 5. Named ranges are not supported. # 6. Array formulas are not supported. # # However, these constraints will be removed in future versions. They are # here because of a trade-off between features and time. Also, it is possible # to work around issue 1 using the store_formula() and repeat_formula() # methods as described later in this section. # # ==Introduction # # The following is a brief introduction to formulas and functions in Excel # and WriteExcel. # # A formula is a string that begins with an equals sign: # # '=A1+B1' # '=AVERAGE(1, 2, 3)' # # The formula can contain numbers, strings, boolean values, cell references, # cell ranges and functions. Named ranges are not supported. Formulas should # be written as they appear in Excel, that is cells and functions must be # in uppercase. # # Cells in Excel are referenced using the A1 notation system where the column # is designated by a letter and the row by a number. Columns range from A to # IV i.e. 0 to 255, rows range from 1 to 65536. # # The Excel $ notation in cell references is also supported. This allows you # to specify whether a row or column is relative or absolute. This only has # an effect if the cell is copied. The following examples show relative and # absolute values. # # '=A1' # Column and row are relative # '=$A1' # Column is absolute and row is relative # '=A$1' # Column is relative and row is absolute # '=$A$1' # Column and row are absolute # # Formulas can also refer to cells in other worksheets of the current # workbook. For example: # # '=Sheet2!A1' # '=Sheet2!A1:A5' # '=Sheet2:Sheet3!A1' # '=Sheet2:Sheet3!A1:A5' # %q{='Test Data'!A1} # %q{='Test Data1:Test Data2'!A1} # # The sheet reference and the cell reference are separated by ! the exclamation # mark symbol. If worksheet names contain spaces, commas o parentheses then # Excel requires that the name is enclosed in single quotes as shown in the # last two examples above. In order to avoid using a lot of escape characters # you can use the quote operator %q{} to protect the quotes. See perlop in # the main Perl documentation. Only valid sheet names that have been added # using the add_worksheet() method can be used in formulas. You cannot # reference external workbooks. # # The following table lists the operators that are available in Excel's # formulas. The majority of the operators are the same as Perl's, # differences are indicated: # # Arithmetic operators: # ===================== # Operator Meaning Example # + Addition 1+2 # - Subtraction 2-1 # * Multiplication 2*3 # / Division 1/4 # ^ Exponentiation 2^3 # Equivalent to ** # - Unary minus -(1+2) # Not yet supported # % Percent (Not modulus) 13% # Not supported, [1] # # # Comparison operators: # ===================== # Operator Meaning Example # = Equal to A1 = B1 # Equivalent to == # <> Not equal to A1 <> B1 # Equivalent to != # > Greater than A1 > B1 # < Less than A1 < B1 # >= Greater than or equal to A1 >= B1 # <= Less than or equal to A1 <= B1 # # # String operator: # ================ # Operator Meaning Example # & Concatenation "Hello " & "World!" # [2] # # # Reference operators: # ==================== # Operator Meaning Example # : Range operator A1:A4 # [3] # , Union operator SUM(1, 2+2, B3) # [4] # # # Notes: # [1]: You can get a percentage with formatting and modulus with MOD(). # [2]: Equivalent to ("Hello " . "World!") in Perl. # [3]: This range is equivalent to cells A1, A2, A3 and A4. # [4]: The comma behaves like the list separator in Perl. # # The range and comma operators can have different symbols in non-English # versions of Excel. These will be supported in a later version of # WriteExcel. European users of Excel take note: # # worksheet.write('A1', '=SUM(1; 2; 3)') # Wrong!! # worksheet.write('A1', '=SUM(1, 2, 3)') # Okay # # The following table lists all of the core functions supported by Excel 5 # and WriteExcel. Any additional functions that are available through the # "Analysis ToolPak" or other add-ins are not supported. These functions # have all been tested to verify that they work. # # ABS DB INDIRECT NORMINV SLN # ACOS DCOUNT INFO NORMSDIST SLOPE # ACOSH DCOUNTA INT NORMSINV SMALL # ADDRESS DDB INTERCEPT NOT SQRT # AND DEGREES IPMT NOW STANDARDIZE # AREAS DEVSQ IRR NPER STDEV # ASIN DGET ISBLANK NPV STDEVP # ASINH DMAX ISERR ODD STEYX # ATAN DMIN ISERROR OFFSET SUBSTITUTE # ATAN2 DOLLAR ISLOGICAL OR SUBTOTAL # ATANH DPRODUCT ISNA PEARSON SUM # AVEDEV DSTDEV ISNONTEXT PERCENTILE SUMIF # AVERAGE DSTDEVP ISNUMBER PERCENTRANK SUMPRODUCT # BETADIST DSUM ISREF PERMUT SUMSQ # BETAINV DVAR ISTEXT PI SUMX2MY2 # BINOMDIST DVARP KURT PMT SUMX2PY2 # CALL ERROR.TYPE LARGE POISSON SUMXMY2 # CEILING EVEN LEFT POWER SYD # CELL EXACT LEN PPMT T # CHAR EXP LINEST PROB TAN # CHIDIST EXPONDIST LN PRODUCT TANH # CHIINV FACT LOG PROPER TDIST # CHITEST FALSE LOG10 PV TEXT # CHOOSE FDIST LOGEST QUARTILE TIME # CLEAN FIND LOGINV RADIANS TIMEVALUE # CODE FINV LOGNORMDIST RAND TINV # COLUMN FISHER LOOKUP RANK TODAY # COLUMNS FISHERINV LOWER RATE TRANSPOSE # COMBIN FIXED MATCH REGISTER.ID TREND # CONCATENATE FLOOR MAX REPLACE TRIM # CONFIDENCE FORECAST MDETERM REPT TRIMMEAN # CORREL FREQUENCY MEDIAN RIGHT TRUE # COS FTEST MID ROMAN TRUNC # COSH FV MIN ROUND TTEST # COUNT GAMMADIST MINUTE ROUNDDOWN TYPE # COUNTA GAMMAINV MINVERSE ROUNDUP UPPER # COUNTBLANK GAMMALN MIRR ROW VALUE # COUNTIF GEOMEAN MMULT ROWS VAR # COVAR GROWTH MOD RSQ VARP # CRITBINOM HARMEAN MODE SEARCH VDB # DATE HLOOKUP MONTH SECOND VLOOKUP # DATEVALUE HOUR N SIGN WEEKDAY # DAVERAGE HYPGEOMDIST NA SIN WEIBULL # DAY IF NEGBINOMDIST SINH YEAR # DAYS360 INDEX NORMDIST SKEW ZTEST # # -- # You can also modify the module to support function names in the following # languages: German, French, Spanish, Portuguese, Dutch, Finnish, Italian # and Swedish. See the function_locale.pl program in the examples # directory of the distro. # ++ # # For a general introduction to Excel's formulas and an explanation of # the syntax of the function refer to the Excel help files or the # following: http://office.microsoft.com/en-us/assistance/CH062528031033.aspx # # If your formula doesn't work in Spreadsheet::WriteExcel try the following: # # 1. Verify that the formula works in Excel (or Gnumeric or OpenOffice.org). # 2. Ensure that it isn't on the Caveats list shown above. # 3. Ensure that cell references and formula names are in uppercase. # 4. Ensure that you are using ':' as the range operator, A1:A4. # 5. Ensure that you are using ',' as the union operator, SUM(1,2,3). # 6. Ensure that the function is in the above table. # # If you go through steps 1-6 and you still have a problem, mail me. # # ==Improving performance when working with formulas # # Writing a large number of formulas with Spreadsheet::WriteExcel can be # slow. This is due to the fact that each formula has to be parsed and # with the current implementation this is computationally expensive. # # However, in a lot of cases the formulas that you write will be quite # similar, for example: # # worksheet.write_formula('B1', '=A1 * 3 + 50', format) # worksheet.write_formula('B2', '=A2 * 3 + 50', format) # ... # ... # worksheet.write_formula('B99', '=A999 * 3 + 50', format) # worksheet.write_formula('B1000', '=A1000 * 3 + 50', format) # # In this example the cell reference changes in iterations from A1 to A1000. # The parser treats this variable as a token and arranges it according to # predefined rules. However, since the parser is oblivious to the value of # the token, it is essentially performing the same calculation 1000 times. # This is inefficient. # # The way to avoid this inefficiency and thereby speed up the writing of # formulas is to parse the formula once and then repeatedly substitute # similar tokens. # # A formula can be parsed and stored via the store_formula() worksheet # method. You can then use the repeat_formula() method to substitute # _pattern_, _replace_ pairs in the stored formula: # # formula = worksheet.store_formula('=A1 * 3 + 50') # # (0...1000).each do |row| # worksheet.repeat_formula(row, 1, formula, format, 'A1', 'A'.(row +1)) # end # # On an arbitrary test machine this method was 10 times faster than the # brute force method shown above. # -- # For more information about how WriteExcel parses and stores formulas see # the WriteExcel::Formula man page. # # It should be noted however that the overall speed of direct formula # parsing will be improved in a future version. # ++ def write_formula(*args) # Check for a cell reference in A1 notation and substitute row and column if (args[0] =~ /^\D/) args = substitute_cellref(args) end return -1 if args.size < 3 # Check the number of args record = 0x0006 # Record identifier # length # Bytes to follow row = args[0] # Zero indexed row col = args[1] # Zero indexed column formula = args[2].dup # The formula text string value = args[4] # The formula text string xf = xf_record_index(row, col, args[3]) # The cell format chn = 0x0000 # Must be zero is_string = 0 # Formula evaluates to str # num # Current value of formula # grbi # Option flags # Excel normally stores the last calculated value of the formula in num. # Clearly we are not in a position to calculate this "a priori". Instead # we set num to zero and set the option flags in grbit to ensure # automatic calculation of the formula when the file is opened. # As a workaround for some non-Excel apps we also allow the user to # specify the result of the formula. # num, grbit, is_string = encode_formula_result(value) # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, col) != 0 # Strip the = sign at the beginning of the formula string formula.sub!(/^=/, '') # Parse the formula using the parser in Formula.pm # nakamura add: to get byte_stream, set second arg TRUE # because ruby doesn't have Perl's "wantarray" formula = @parser.parse_formula(formula, true) # if ($@) { # $@ =~ s/\n$// # Strip the \n used in the Formula.pm die() # croak $@ # Re-raise the error # } formlen = formula.length # Length of the binary string length = 0x16 + formlen # Length of the record data header = [record, length].pack("vv") data = [row, col, xf].pack("vvv") + num + [grbit, chn, formlen].pack('vVv') # The STRING record if the formula evaluates to a string. string = '' string = get_formula_string(value) if is_string != 0 # Store the data or write immediately depending on the compatibility mode. if @compatibility != 0 tmp = [] tmp[col] = header + data + formula + string @table[row] = tmp else print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data, formula, string) end return 0 end # # :call-seq: # write_row(row, col , array[, format]) # write_row(A1_notation, array[, format]) # # Write a row of data starting from (row, col). Call write_col() if any of # the elements of the array are in turn array. This allows the writing of # 1D or 2D arrays of data in one go. # # Returns: the first encountered error value or zero for no errors # # # 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 called for each element # of the data. For example: # # array = ['awk', 'gawk', 'mawk'] # # worksheet.write_row(0, 0, array_ref) # # # 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 # 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 # # As with all of the write methods the format parameter is optional. If a # format is specified it is applied to all the elements of the data array. # # Array references within the data will be treated as columns. This allows you # to write 2D arrays of data in one go. For example: # # eec = [ # ['maggie', 'milly', 'molly', 'may' ], # [13, 14, 15, 16 ], # ['shell', 'star', 'crab', 'stone'] # ] # # worksheet.write_row('A1', eec) # # Would produce a worksheet as follows: # # ----------------------------------------------------------- # | | A | B | C | D | E | ... # ----------------------------------------------------------- # | 1 | maggie | 13 | shell | ... | ... | ... # | 2 | milly | 14 | star | ... | ... | ... # | 3 | molly | 15 | crab | ... | ... | ... # | 4 | may | 16 | stone | ... | ... | ... # | 5 | ... | ... | ... | ... | ... | ... # | 6 | ... | ... | ... | ... | ... | ... # # To write the data in a row-column order refer to the write_col() method # below. # # Any +nil+ values 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. # # The write_row() method returns the first error encountered when # writing the elements of the data or zero if no errors were encountered. # See the return values described for the write() method above. # # See also the write_arrays.rb program in the examples directory of the # distro. # def write_row(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end # Catch non array refs passed by user. unless args[2].kind_of?(Array) raise "Not an array ref in call to write_row() #{$!}"; end row, col, tokens, options = args error = 0 unless tokens.nil? tokens.each do |token| # Check for nested arrays if token.kind_of?(Array) ret = write_col(row, col, token, options) else ret = write(row, col, token, options) end # Return only the first error encountered, if any. error ||= ret col += 1 end end return error end # # :call-seq: # write_column(row, col , array[, format]) # write_column(A1_notation, array[, format]) # # Write a column of data starting from (row, col). Call write_row() if any of # the elements of the array are in turn array. This allows the writing # of 1D or 2D arrays of data in one go. # # Returns: the first encountered error value or zero for no errors # # # The write_col() 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. The write() method is then called for each element of the # data. For example: # # array = ['awk', 'gawk', 'mawk'] # # worksheet.write_col(0, 0, array) # # # The above example is equivalent to: # worksheet.write(0, 0, array[0]) # worksheet.write(1, 0, array[1]) # worksheet.write(2, 0, array[2]) # # As with all of the write methods the format parameter is optional. If a # format is specified it is applied to all the elements of the data array. # # Array within the data will be treated as rows. This allows you to write # 2D arrays of data in one go. For example: # # eec = [ # ['maggie', 'milly', 'molly', 'may' ], # [13, 14, 15, 16 ], # ['shell', 'star', 'crab', 'stone'] # ] # # worksheet.write_col('A1', eec) # # Would produce a worksheet as follows: # # ----------------------------------------------------------- # | | A | B | C | D | E | ... # ----------------------------------------------------------- # | 1 | maggie | milly | molly | may | ... | ... # | 2 | 13 | 14 | 15 | 16 | ... | ... # | 3 | shell | star | crab | stone | ... | ... # | 4 | ... | ... | ... | ... | ... | ... # | 5 | ... | ... | ... | ... | ... | ... # | 6 | ... | ... | ... | ... | ... | ... # # To write the data in a column-row order refer to the write_row() method # above. # # Any +nil+ values 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 write_row() # and write_row() handles nested array as columns. Therefore, the following # two method calls are equivalent although the more explicit call to # write_col() would be preferable for maintainability: # # worksheet.write_col('A1', array) # Write a column of data # worksheet.write( 'A1', [ array ]) # Same thing # # The write_col() method returns the first error encountered when writing # the elements of the data or zero if no errors were encountered. See the # return values described for the write() method above. # # See also the write_arrays.pl program in the examples directory of the distro. # def write_col(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end # Catch non array refs passed by user. unless args[2].kind_of?(Array) raise "Not an array ref in call to write_row()"; end row, col, tokens, options = args error = 0 unless tokens.nil? tokens.each do |token| # write() will deal with any nested arrays ret = write(row, col, token, options) # Return only the first error encountered, if any. error ||= ret row += 1 end end return error end # # :call-seq: # write_comment(row, col, comment[, optionhash(es)]) -> Fixnum # write_comment(A1_notation, comment[, optionhash(es)]) -> Fixnum # # Write a comment to the specified row and column (zero indexed). # # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # # The write_comment() method is used to add a comment to a cell. A cell # comment is indicated in Excel by a small red triangle in the upper # right-hand corner of the cell. Moving the cursor over the red triangle # will reveal the comment. # # The following example shows how to add a comment to a cell: # # worksheet.write (2, 2, 'Hello') # worksheet.write_comment(2, 2, 'This is a comment.') # # As usual you can replace the row and column parameters with an A1 cell # reference. See the note about "Cell notation". # # worksheet.write ('C3', 'Hello') # worksheet.write_comment('C3', 'This is a comment.') # # On systems with perl 5.8 and later the write_comment() method will also # handle strings in UTF-8 format. # # worksheet.write_comment('C3', "\x{263a}") # Smiley # worksheet.write_comment('C4', 'Comment ca va?') # # In addition to the basic 3 argument form of write_comment() you can pass in # several optional key/value pairs to control the format of the comment. # For example: # # worksheet.write_comment('C3', 'Hello', visible => 1, author => 'Ruby') # # Most of these options are quite specific and in general the default comment # behaviour will be all that you need. However, should you need greater # control over the format of the cell comment the following options are # available: # # encoding # author # author_encoding # visible # x_scale # width # y_scale # height # color # start_cell # start_row # start_col # x_offset # y_offset # # Option: encoding # # This option is used to indicate that the comment string is encoded as # UTF-16BE. # # comment = [0x263a].pack('n') # UTF-16BE Smiley symbol # # worksheet.write_comment('C3', comment, :encoding => 1) # # Option: author # # This option is used to indicate who the author of the comment is. Excel # displays the author of the comment in the status bar at the bottom of # the worksheet. This is usually of interest in corporate environments # where several people might review and provide comments to a workbook. # # worksheet.write_comment('C3', 'Atonement', :author => 'Ian McEwan') # # Option: author_encoding # # This option is used to indicate that the author string is encoded as UTF-16BE. # # Option: visible # # This option is used to make a cell comment visible when the worksheet # is opened. The default behaviour in Excel is that comments are initially # hidden. However, it is also possible in Excel to make individual or all # comments visible. In WriteExcel individual comments can be made visible # as follows: # # worksheet.write_comment('C3', 'Hello', :visible => 1) # # It is possible to make all comments in a worksheet visible using the show # comments() worksheet method (see below). Alternatively, if all of the cell # comments have been made visible you can hide individual comments: # # worksheet.write_comment('C3', 'Hello', :visible => 0) # # Option: x_scale # # This option is used to set the width of the cell comment box as a factor # of the default width. # # worksheet.write_comment('C3', 'Hello', :x_scale => 2) # worksheet.write_comment('C4', 'Hello', :x_scale => 4.2) # # Option: width # # This option is used to set the width of the cell comment box # explicitly in pixels. # # worksheet.write_comment('C3', 'Hello', :width => 200) # # Option: y_scale # # This option is used to set the height of the cell comment box as a # factor of the default height. # # worksheet.write_comment('C3', 'Hello', :y_scale => 2) # worksheet.write_comment('C4', 'Hello', :y_scale => 4.2) # # Option: height # # This option is used to set the height of the cell comment box # explicitly in pixels. # # worksheet.write_comment('C3', 'Hello', :height => 200) # # Option: color # # This option is used to set the background colour of cell comment box. # You can use one of the named colours recognised by WriteExcel or a colour # index. See "COLOURS IN EXCEL". # # worksheet.write_comment('C3', 'Hello', :color => 'green') # worksheet.write_comment('C4', 'Hello', :color => 0x35) # Orange # # Option: start_cell # # This option is used to set the cell in which the comment will appear. # By default Excel displays comments one cell to the right and one cell # above the cell to which the comment relates. However, you can change # this behaviour if you wish. In the following example the comment which # would appear by default in cell D2 is moved to E2. # # worksheet.write_comment('C3', 'Hello', :start_cell => 'E2') # # Option: start_row # # This option is used to set the row in which the comment will appear. # See the start_cell option above. The row is zero indexed. # # worksheet.write_comment('C3', 'Hello', :start_row => 0) # # Option: start_col # # This option is used to set the column in which the comment will appear. # See the start_cell option above. The column is zero indexed. # # worksheet.write_comment('C3', 'Hello', :start_col => 4) # # Option: x_offset # # This option is used to change the x offset, in pixels, of a comment # within a cell: # # worksheet.write_comment('C3', comment, :x_offset => 30) # # Option: y_offset # # This option is used to change the y offset, in pixels, of a comment # within a cell: # # worksheet.write_comment('C3', comment, :x_offset => 30) # # You can apply as many of these options as you require. # # ==Note about row height and comments. # # If you specify the height of a row that contains a comment then WriteExcel # will adjust the height of the comment to maintain the default or user # specified dimensions. However, the height of a row can also be adjusted # automatically by Excel if the text wrap property is set or large fonts are # used in the cell. This means that the height of the row is unknown to # WriteExcel at run time and thus the comment box is stretched with the row. # Use the set_row() method to specify the row height explicitly and avoid # this problem. # def write_comment(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end return -1 if args.size < 3 # Check the number of args row = args[0] col = args[1] # Check for pairs of optional arguments, i.e. an odd number of args. raise "Uneven number of additional arguments" if args.size % 2 == 0 # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, col) != 0 # We have to avoid duplicate comments in cells or else Excel will complain. @comments[row] = { col => comment_params(*args) } end def active=(val) # :nodoc: @active = val end def encoding # :nodoc: @encoding end def index # :nodoc: @index end def index=(val) # :nodoc: @index = val end def sheet_type # :nodoc: @sheet_type end def images_array # :nodoc: @images_array end def date_1904=(val) # :nodoc: @date_1904 = val end def filter_area # :nodoc: @filter_area end def filter_count # :nodoc: @filter_count end def title_rowmin # :nodoc: @title_rowmin end def title_rowmax # :nodoc: @title_rowmax end def title_colmin # :nodoc: @title_colmin end def title_colmax # :nodoc: @title_colmax end def print_rowmin # :nodoc: @print_rowmin end def print_rowmax # :nodoc: @print_rowmax end def print_colmin # :nodoc: @print_colmin end def print_colmax # :nodoc: @print_colmax end def offset # :nodoc: @offset end def offset=(val) # :nodoc: @offset = val end def selected # :nodoc: @selected end def selected=(val) # :nodoc: @selected = val end def hidden # :nodoc: @hidden end def hidden=(val) # :nodoc: @hidden = val end def object_ids=(val) # :nodoc: @object_ids = val end def num_images # :nodoc: @num_images end def num_images=(val) # :nodoc: @num_images = val end def image_mso_size # :nodoc: @image_mso_size end def image_mso_size=(val) # :nodoc: @image_mso_size = val end ############################################################################### # # _xf_record_index() # # Returns an index to the XF record in the workbook. # # Note: this is a function, not a method. # def xf_record_index(row, col, xf=nil) #:nodoc: if xf.kind_of?(Format) return xf.xf_index elsif @row_formats.has_key?(row) return @row_formats[row].xf_index elsif @col_formats.has_key?(col) return @col_formats[col].xf_index else return 0x0F end end private :xf_record_index ############################################################################### ############################################################################### # # Internal methods # ############################################################################### # # _substitute_cellref() # # Substitute an Excel cell reference in A1 notation for zero based row and # column values in an argument list. # # Ex: ("A4", "Hello") is converted to (3, 0, "Hello"). # def substitute_cellref(cell, *args) #:nodoc: return [*args] if cell.kind_of?(Numeric) cell.upcase! # Convert a column range: 'A:A' or 'B:G'. # A range such as A:A is equivalent to A1:65536, so add rows as required if cell =~ /\$?([A-I]?[A-Z]):\$?([A-I]?[A-Z])/ row1, col1 = cell_to_rowcol($1 +'1') row2, col2 = cell_to_rowcol($2 +'65536') return [row1, col1, row2, col2, *args] end # Convert a cell range: 'A1:B7' if cell =~ /\$?([A-I]?[A-Z]\$?\d+):\$?([A-I]?[A-Z]\$?\d+)/ row1, col1 = cell_to_rowcol($1) row2, col2 = cell_to_rowcol($2) return [row1, col1, row2, col2, *args] end # Convert a cell reference: 'A1' or 'AD2000' if (cell =~ /\$?([A-I]?[A-Z]\$?\d+)/) row1, col1 = cell_to_rowcol($1) return [row1, col1, *args] end raise("Unknown cell reference #{cell}") end private :substitute_cellref ############################################################################### # # _cell_to_rowcol($cell_ref) # # Convert an Excel cell reference in A1 notation to a zero based row and column # reference; converts C1 to (0, 2). # # Returns: row, column # def cell_to_rowcol(cell) #:nodoc: cell =~ /\$?([A-I]?[A-Z])\$?(\d+)/ col = $1 row = $2.to_i # Convert base26 column string to number # All your Base are belong to us. chars = col.split(//) expn = 0 col = 0 while (!chars.empty?) char = chars.pop # LS char first col = col + (char[0] - "A"[0] +1) * (26**expn) expn = expn + 1 end # Convert 1-index to zero-index row -= 1 col -= 1 return [row, col] end private :cell_to_rowcol ############################################################################### # # _sort_pagebreaks() # # # This is an internal method that is used to filter elements of the array of # pagebreaks used in the _store_hbreak() and _store_vbreak() methods. It: # 1. Removes duplicate entries from the list. # 2. Sorts the list. # 3. Removes 0 from the list if present. # def sort_pagebreaks(breaks) #:nodoc: breaks.uniq.sort! breaks.shift if breaks[0] == 0 # 1000 vertical pagebreaks appears to be an internal Excel 5 limit. # It is slightly higher in Excel 97/200, approx. 1026 breaks.size > 1000 ? breaks[0..999] : breaks end private :sort_pagebreaks ############################################################################### # # _encode_password($password) # # Based on the algorithm provided by Daniel Rentz of OpenOffice. # # def encode_password(password) #:nodoc: i = 0 chars = password.split(//) count = chars.size chars.each do |char| i += 1 char = char[0] << i low_15 = char & 0x7fff high_15 = char & 0x7fff << 15 high_15 = high_15 >> 15 char = low_15 | high_15 end encoded_password = 0x0000 chars.each { |c| encoded_password ^= c } encoded_password ^= count encoded_password ^= 0xCE4B end private :encode_password ############################################################################### # # _encode_formula_result() # my $value = $_[0]; # Result to be encoded. # # Encode the user supplied result for a formula. # def encode_formula_result(value = nil) #:nodoc: is_string = 0; # Formula evaluates to str. # my $num; # Current value of formula. # my $grbit; # Option flags. unless value grbit = 0x03 num = [0].pack("d") else # The user specified the result of the formula. We turn off the recalc # flag and check the result type. grbit = 0x00 if value =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/ # Value is a number. num = [value].pack("d") else bools = { 'TRUE' => [1, 1], 'FALSE' => [1, 0], '#NULL!' => [2, 0], '#DIV/0!' => [2, 7], '#VALUE!' => [2, 15], '#REF!' => [2, 23], '#NAME?' => [2, 29], '#NUM!' => [2, 36], '#N/A' => [2, 42] } if bools[value] # Value is a boolean. num = [bools[value][0], bools[value][1], 0, 0xFFFF].pack("vvvv") else # Value is a string. num = [0, 0, 0, 0xFFFF].pack("vvvv") is_string = 1 end end end return [num, grbit, is_string] end private :encode_formula_result ############################################################################### # # _get_formula_string() # # Pack the string value when a formula evaluates to a string. The value cannot # be calculated by the module and thus must be supplied by the user. # def get_formula_string(string) #:nodoc: record = 0x0207 # Record identifier length = 0x00 # Bytes to follow # string # Formula string. strlen = string.length # Length of the formula string (chars). encoding = 0 # String encoding. # Handle utf8 strings in perl 5.8. if string =~ NonAscii string = NKF.nkf('-w16B0 -m0 -W', string) encoding = 1 end length = 0x03 + string.length # Length of the record data header = [record, length].pack("vv") data = [strlen, encoding].pack("vC") return header . data . string end private :get_formula_string # # :call-seq: # store_formula(formula) # formula : text string of formula # # Pre-parse a formula. This is used in conjunction with repeat_formula() # to repetitively rewrite a formula without re-parsing it. # # The store_formula() method is used in conjunction with repeat_formula() # to speed up the generation of repeated formulas. See # "Improving performance when working with formulas" in # "FORMULAS AND FUNCTIONS IN EXCEL". # # The store_formula() method pre-parses a textual representation of a # formula and stores it for use at a later stage by the repeat_formula() # method. # # store_formula() carries the same speed penalty as write_formula(). However, # in practice it will be used less frequently. # # The return value of this method is a scalar that can be thought of as a # reference to a formula. # # sin = worksheet.store_formula('=SIN(A1)') # cos = worksheet.store_formula('=COS(A1)') # # worksheet.repeat_formula('B1', sin, format, 'A1', 'A2') # worksheet.repeat_formula('C1', cos, format, 'A1', 'A2') # # Although store_formula() is a worksheet method the return value can be used # in any worksheet: # # now = worksheet.store_formula('=NOW()') # # worksheet1.repeat_formula('B1', now) # worksheet2.repeat_formula('B1', now) # worksheet3.repeat_formula('B1', now) # def store_formula(formula) #:nodoc: # Strip the = sign at the beginning of the formula string formula.sub!(/^=/, '') # In order to raise formula errors from the point of view of the calling # program we use an eval block and re-raise the error from here. # tokens = @parser.parse_formula(formula) # if ($@) { # $@ =~ s/\n$// # Strip the \n used in the Formula.pm die() # croak $@ # Re-raise the error # } # Return the parsed tokens in an anonymous array return [*tokens] end # # :call-seq: # repeat_formula(row, col, formula, format, ([pattern => replace, ...]) -> Fixnum # repeat_formula(A1_notation, formula, format, ([pattern => replace, ...]) -> Fixnum # # Write a formula to the specified row and column (zero indexed) by # substituting _pattern_ _replacement_ pairs in the formula created via # store_formula(). This allows the user to repetitively rewrite a formula # without the significant overhead of parsing. # # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # # The repeat_formula() method is used in conjunction with store_formula() to # speed up the generation of repeated formulas. See # "Improving performance when working with formulas" in # "FORMULAS AND FUNCTIONS IN EXCEL". # # In many respects repeat_formula() behaves like write_formula() except that # it is significantly faster. # # The repeat_formula() method creates a new formula based on the pre-parsed # tokens returned by store_formula(). The new formula is generated by # substituting _pattern_, _replace_ pairs in the stored formula: # # formula = worksheet.store_formula('=A1 * 3 + 50') # # (0...100).each do |row| # worksheet.repeat_formula(row, 1, formula, format, 'A1', 'A'.(row +1)) # end # # It should be noted that repeat_formula() doesn't modify the tokens. In the # above example the substitution is always made against the original token, # A1, which doesn't change. # # As usual, you can use undef if you don't wish to specify a format: # # worksheet.repeat_formula('B2', formula, format, 'A1', 'A2') # worksheet.repeat_formula('B3', formula, nil, 'A1', 'A3') # # The substitutions are made from left to right and you can use as many # pattern, replace pairs as you need. However, each substitution is made # only once: # # formula = worksheet.store_formula('=A1 + A1') # # # Gives '=B1 + A1' # worksheet.repeat_formula('B1', formula, undef, 'A1', 'B1') # # # Gives '=B1 + B1' # worksheet.repeat_formula('B2', formula, undef, ('A1', 'B1') x 2) # # Since the pattern is interpolated each time that it is used it is worth # using the qr operator to quote the pattern. The qr operator is explained # in the perlop man page. # # worksheet.repeat_formula('B1', formula, format, qr/A1/, 'A2') # # Care should be taken with the values that are substituted. The formula # returned by repeat_formula() contains several other tokens in addition to # those in the formula and these might also match the pattern that you are # trying to replace. In particular you should avoid substituting a single # 0, 1, 2 or 3. # # You should also be careful to avoid false matches. For example the following # snippet is meant to change the stored formula in steps # from =A1 + SIN(A1) to =A10 + SIN(A10). # # formula = worksheet.store_formula('=A1 + SIN(A1)') # # (1..10).each do |row| # worksheet.repeat_formula(row -1, 1, formula, nil, # qw/A1/, 'A' . row, #! Bad. # qw/A1/, 'A' . row #! Bad. # ) # end # # However it contains a bug. In the last iteration of the loop when row is # 10 the following substitutions will occur: # # s/A1/A10/; changes =A1 + SIN(A1) to =A10 + SIN(A1) # s/A1/A10/; changes =A10 + SIN(A1) to =A100 + SIN(A1) # !! # # The solution in this case is to use a more explicit match such as qw/^A1$/: # # worksheet.repeat_formula(row -1, 1, formula, nil, # qw/^A1$/, 'A' . row, # qw/^A1$/, 'A' . row # ) # # Another similar problem occurs due to the fact that substitutions are made # in order. For example the following snippet is meant to change the stored # formula from =A10 + A11 to =A11 + A12: # # formula = worksheet.store_formula('=A10 + A11') # # worksheet.repeat_formula('A1', formula, nil, # qw/A10/, 'A11', #! Bad. # qw/A11/, 'A12' #! Bad. # ) # # However, the actual substitution yields =A12 + A11: # # s/A10/A11/; changes =A10 + A11 to =A11 + A11 # s/A11/A12/; changes =A11 + A11 to =A12 + A11 # !! # # The solution here would be to reverse the order of the substitutions or to # start with a stored formula that won't yield a false match such as =X10 + Y11: # # formula = worksheet.store_formula('=X10 + Y11') # # worksheet.repeat_formula('A1', formula, nil, # qw/X10/, 'A11', # qw/Y11/, 'A12' # ) # # If you think that you have a problem related to a false match you can check # the tokens that you are substituting against as follows. # # formula = worksheet.store_formula('=A1*5+4') # print "#{formula}\n" # # See also the repeat.rb program in the examples directory of the distro. # def repeat_formula(*args) #:nodoc: # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(args) end return -1 if (args.size < 2) # Check the number of args record = 0x0006 # Record identifier # length # Bytes to follow row = args.shift # Zero indexed row col = args.shift # Zero indexed column formula_ref = args.shift # Array ref with formula tokens format = args.shift # XF format pairs = args # Pattern/replacement pairs # Enforce an even number of arguments in the pattern/replacement list raise "Odd number of elements in pattern/replacement list" if pairs.size % 2 != 0 # Check that formula is an array ref raise "Not a valid formula" unless formula_ref.kind_of?(Array) tokens = formula_ref.join("\t").split("\t") # Ensure that there are tokens to substitute raise "No tokens in formula" if tokens.empty? # As a temporary and undocumented measure we allow the user to specify the # result of the formula by appending a result => $value pair to the end # of the arguments. value = nil if pairs[-2] == 'result' value = pairs.pop pairs.pop end while (!pairs.empty?) pattern = pairs.shift replace = pairs.shift tokens.each do |token| break if token.sub!(pattern, replace) end end # Change the parameters in the formula cached by the Formula.pm object formula = @parser.parse_tokens(tokens) raise "Unrecognised token in formula" unless formula xf = xf_record_index(row, col, format) # The cell format chn = 0x0000 # Must be zero is_string = 0 # Formula evaluates to str # num # Current value of formula # grbit # Option flags # Excel normally stores the last calculated value of the formula in $num. # Clearly we are not in a position to calculate this "a priori". Instead # we set $num to zero and set the option flags in $grbit to ensure # automatic calculation of the formula when the file is opened. # As a workaround for some non-Excel apps we also allow the user to # specify the result of the formula. # num, grbit, is_string = encode_formula_result(value) # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, col) != 0 formlen = formula.length # Length of the binary string length = 0x16 + formlen # Length of the record data header = [record, length].pack("vv") data = [row, col, xf].pack("vvv") + num + [grbit, chn, formlen].pack('vVv') # The STRING record if the formula evaluates to a string. string = '' string = get_formula_string(value) if is_string != 0 # Store the data or write immediately depending on the compatibility mode. if @compatibility != 0 tmp = [] tmp[col] = header + data + formula + string @table[row] = tmp else print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data, formula, string) end return 0 end # # :call-seq: # write_url(row, col , url[, label, , format]) -> int # write_url(A1_notation, url[, label, , format]) -> int # # Write a hyperlink. This is comprised of two elements: the visible _label_ and # the invisible link. The visible _label_ is the same as the link unless an # alternative string is specified. # # The parameters _label_ and _format_ are optional. # # The _url_ can be to a http, ftp, mail, internal sheet, or external # directory url. # # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # -3 : long string truncated to 255 chars # # 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 parameters _label_ and the _format_ # are optional. # # The _label_ is written using the write() method. Therefore it is possible # to write strings, numbers or formulas as labels. # # There are four web style URI's supported: http://, https://, ftp:// and # mailto:: # # worksheet.write_url(0, 0, 'ftp://www.ruby.org/' ) # worksheet.write_url(1, 0, 'http://www.ruby.com/', 'Ruby home' ) # worksheet.write_url('A3', 'http://www.ruby.com/', format ) # worksheet.write_url('A4', 'http://www.ruby.com/', 'Perl', format) # worksheet.write_url('A5', 'mailto:bar@foo.com' ) # # There are two local URIs supported: internal: and external:. These are used # for hyperlinks to internal worksheet references or external workbook and # worksheet references: # # worksheet.write_url('A6', 'internal:Sheet2!A1' ) # worksheet.write_url('A7', 'internal:Sheet2!A1', format ) # worksheet.write_url('A8', 'internal:Sheet2!A1:B2' ) # worksheet.write_url('A9', q{internal:'Sales Data'!A1} ) # worksheet.write_url('A10', 'external:c:\temp\foo.xls' ) # worksheet.write_url('A11', 'external:c:\temp\foo.xls#Sheet2!A1' ) # worksheet.write_url('A12', 'external:..\..\..\foo.xls' ) # worksheet.write_url('A13', 'external:..\..\..\foo.xls#Sheet2!A1' ) # worksheet.write_url('A13', 'external:\\\\NETWORK\share\foo.xls' ) # # All of the these URI types are recognised by the 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. # # In external links the workbook and worksheet name must be separated by the # # character: external:Workbook.xls#Sheet1!A1'. # # You can also link to a named range in the target worksheet. For example say # you have a named range called my_name in the workbook c:\temp\foo.xls you # could link to it as follows: # # worksheet.write_url('A14', 'external:c:\temp\foo.xls#my_name') # # Note, you cannot currently create named ranges with WriteExcel. # # Links to network files are also supported. MS/Novell Network files normally # begin with two back slashes as follows \\NETWORK\etc. In order to generate # this in a single or double quoted string you will have to escape the # backslashes, '\\\\NETWORK\etc'. # # If you are using double quote strings then you should be careful to escape # anything that looks like a metacharacter. Why can't I use "C:\temp\foo" in # DOS paths?. # # Finally, you can avoid most of these quoting problems by using forward # slashes. These are translated internally to backslashes: # # worksheet.write_url('A14', "external:c:/temp/foo.xls" ) # worksheet.write_url('A15', 'external://NETWORK/share/foo.xls' ) # # See also, the note about "Cell notation". # def write_url(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end # Check the number of args return -1 if args.size < 3 # Add start row and col to arg list return write_url_range(args[0], args[1], *args) end # # :call-seq: # write_url_range(row1, col1, row2, col2, url[, string, , format]) -> Fixnum # write_url_range('A1:D2', url[, string, , format]) -> Fixnum # # This is the more general form of write_url(). It allows a hyperlink to be # written to a range of cells. This function also decides the type of hyperlink # to be written. These are either, Web (http, ftp, mailto), Internal # (Sheet1!A1) or external ('c:\temp\foo.xls#Sheet1!A1'). # # See also write_url() above for a general description and return values. # # This method is essentially the same as the write_url() method described # above. The main difference is that you can specify a link for a range of # cells: # # worksheet.write_url(0, 0, 0, 3, 'ftp://www.ruby.org/' ) # worksheet.write_url(1, 0, 0, 3, 'http://www.ruby.com/', 'Ruby home') # worksheet.write_url('A3:D3', 'internal:Sheet2!A1' ) # worksheet.write_url('A4:D4', 'external:c:\temp\foo.xls' ) # # This method is generally only required when used in conjunction with merged # cells. See the merge_range() method and the merge property of a Format # object, "CELL FORMATTING". # # There is no way to force this behaviour through the write() method. # # The parameters _string_ and the $format are optional and their position is # interchangeable. However, they are applied only to the first cell in the # range. # # See also, the note about "Cell notation". # def write_url_range(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end # Check the number of args return -1 if args.size < 5 # Reverse the order of _string_ and $format if necessary. We work on a copy # in order to protect the callers args. We don't use "local @_" in case of # perl50005 threads. # args[5], args[6] = [ args[6], args[5] ] if args[5].kind_of?(Format) url = args[4] # Check for internal/external sheet links or default to web link return write_url_internal(*args) if url =~ /^internal:/ return write_url_external(*args) if url =~ /^external:/ return write_url_web(*args) end ############################################################################### # # _write_url_web($row1, $col1, $row2, $col2, $url, $string, $format) # row1 = $_[0]; # Start row # col1 = $_[1]; # Start column # row2 = $_[2]; # End row # col2 = $_[3]; # End column # url = $_[4]; # URL string # str = $_[5]; # Alternative label # # Used to write http, ftp and mailto hyperlinks. # The link type ($options) is 0x03 is the same as absolute dir ref without # sheet. However it is differentiated by the $unknown2 data stream. # # See also write_url() above for a general description and return values. # def write_url_web(row1, col1, row2, col2, url, str = nil, format = nil) #:nodoc: record = 0x01B8 # Record identifier length = 0x00000 # Bytes to follow xf = format || @url_format # The cell format # Write the visible label but protect against url recursion in write(). str = url if str.nil? @writing_url = 1 error = write(row1, col1, str, xf) @writing_url = 0 return error if error == -2 # Pack the undocumented parts of the hyperlink stream unknown1 = ["D0C9EA79F9BACE118C8200AA004BA90B02000000"].pack("H*") unknown2 = ["E0C9EA79F9BACE118C8200AA004BA90B"].pack("H*") # Pack the option flags options = [0x03].pack("V") # URL encoding. encoding = 0 # Convert an Utf8 URL type and to a null terminated wchar string. if url =~ NonAscii url = NKF.nkf('-w16B0 -m0 -W', url) url += "\0\0" # URL is null terminated. encoding = 1 end # Convert an Ascii URL type and to a null terminated wchar string. if encoding == 0 url += "\0" url = url.unpack('c*').pack('v*') end # Pack the length of the URL url_len = [url.length].pack("V") # Calculate the data length length = 0x34 + url.length # Pack the header data header = [record, length].pack("vv") data = [row1, row2, col1, col2].pack("vvvv") # Write the packed data print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append( header, data,unknown1,options,unknown2,url_len,url) return error end private :write_url_web ############################################################################### # # _write_url_internal($row1, $col1, $row2, $col2, $url, $string, $format) # row1 = $_[0]; # Start row # col1 = $_[1]; # Start column # row2 = $_[2]; # End row # col2 = $_[3]; # End column # url = $_[4]; # URL string # str = $_[5]; # Alternative label # # Used to write internal reference hyperlinks such as "Sheet1!A1". # # See also write_url() above for a general description and return values. # def write_url_internal(row1, col1, row2, col2, url, str = nil, format = nil) #:nodoc: record = 0x01B8 # Record identifier length = 0x00000 # Bytes to follow xf = format || @url_format # The cell format # Strip URL type url.sub!(/^internal:/, '') # Write the visible label but protect against url recursion in write(). str = url if str.nil? @writing_url = 1 error = write(row1, col1, str, xf) @writing_url = 0 return error if error == -2 # Pack the undocumented parts of the hyperlink stream unknown1 = ["D0C9EA79F9BACE118C8200AA004BA90B02000000"].pack("H*") # Pack the option flags options = [0x08].pack("V") # URL encoding. encoding = 0 # Convert an Utf8 URL type and to a null terminated wchar string. if str =~ NonAscii # Quote sheet name if not already, i.e., Sheet!A1 to 'Sheet!A1'. url.sub!(/^(.+)!/, "'\1'!") if not url =~ /^'/; url = NKF.nkf('-w16L0 -m0 -W', url) + "\0\0" # URL is null terminated. encoding = 1 end # Convert an Ascii URL type and to a null terminated wchar string. if encoding == 0 url = url + "\0" url = url.unpack('c*').pack('v*') end # Pack the length of the URL as chars (not wchars) url_len = [(url.length/2).to_i].pack("V") # Calculate the data length length = 0x24 + url.length # Pack the header data header = [record, length].pack("vv") data = [row1, row2, col1, col2].pack("vvvv") # Write the packed data print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append( header, data, unknown1, options, url_len, url) return error end private :write_url_internal ############################################################################### # # _write_url_external($row1, $col1, $row2, $col2, $url, $string, $format) # # Write links to external directory names such as 'c:\foo.xls', # c:\foo.xls#Sheet1!A1', '../../foo.xls'. and '../../foo.xls#Sheet1!A1'. # # Note: Excel writes some relative links with the $dir_long string. We ignore # these cases for the sake of simpler code. # # See also write_url() above for a general description and return values. # def write_url_external(row1, col1, row2, col2, url, str = nil, format = nil) #:nodoc: # Network drives are different. We will handle them separately # MS/Novell network drives and shares start with \\ if url =~ /^external:\\\\/ return write_url_external_net(row1, col1, row2, col2, url, str, format) end record = 0x01B8 # Record identifier length = 0x00000 # Bytes to follow xf = format || @url_format # The cell format # Strip URL type and change Unix dir separator to Dos style (if needed) # url.sub!(/^external:/, '') url.gsub!(%r|/|, '\\') # Write the visible label but protect against url recursion in write(). str = url.sub!(/\#/, ' - ') if str.nil? @writing_url = 1 error = write(row1, col1, str, xf) @writing_url = 0 return error if error == -2 # Determine if the link is relative or absolute: # Absolute if link starts with DOS drive specifier like C: # Otherwise default to 0x00 for relative link. # absolute = 0x00 absolute = 0x02 if url =~ /^[A-Za-z]:/ # Determine if the link contains a sheet reference and change some of the # parameters accordingly. # Split the dir name and sheet name (if it exists) # dir_long , sheet = url.split(/\#/) link_type = 0x01 | absolute unless sheet.nil? link_type |= 0x08 sheet_len = [sheet.length + 0x01].pack("V") sheet = sheet.split('').join("\0") + "\0\0\0" else sheet_len = '' sheet = '' end # Pack the link type link_type = link_type.pack("V") # Calculate the up-level dir count e.g. (..\..\..\ == 3) up_count = 0 while dir_long.sub!(/^\.\.\\/, '') up_count = up_count + 1 end up_count = [up_count].pack("v") # Store the short dos dir name (null terminated) dir_short = dir_long + "\0" # Store the long dir name as a wchar string (non-null terminated) dir_long = dir_long.split('').join("\0") + "\0" # Pack the lengths of the dir strings dir_short_len = [dir_short.length].pack("V") dir_long_len = [dir_long.length].pack("V") stream_len = [dir_long.length + 0x06].pack("V") # Pack the undocumented parts of the hyperlink stream unknown1 = ['D0C9EA79F9BACE118C8200AA004BA90B02000000'].pack("H*") unknown2 = ['0303000000000000C000000000000046'].pack("H*") unknown3 = ['FFFFADDE000000000000000000000000000000000000000'].pack("H*") unknown4 = [0x03].pack("v") # Pack the main data stream data = [row1, row2, col1, col2].pack("vvvv") + unknown1 + link_type + unknown2 + up_count + dir_short_len+ dir_short + unknown3 + stream_len + dir_long_len + unknown4 + dir_long + sheet_len + sheet # Pack the header data length = data.length header = [record, length].pack("vv") # Write the packed data print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) return error end private :write_url_external ############################################################################### # # _write_url_external_net($row1, $col1, $row2, $col2, $url, $string, $format) # # Write links to external MS/Novell network drives and shares such as # '//NETWORK/share/foo.xls' and '//NETWORK/share/foo.xls#Sheet1!A1'. # # See also write_url() above for a general description and return values. # def write_url_external_net(row1, col1, row2, col2, url, str, format) #:nodoc: record = 0x01B8 # Record identifier length = 0x00000 # Bytes to follow xf = format || @url_format # The cell format # Strip URL type and change Unix dir separator to Dos style (if needed) # url.sub!(/^external:/, '') url.gsub!(%r|/|, '\\') # Write the visible label but protect against url recursion in write(). str = url.sub!(/\#/, ' - ') if str.nil? @writing_url = 1 error = write(row1, col1, str, xf) @writing_url = 0 return error if error == -2 # Determine if the link contains a sheet reference and change some of the # parameters accordingly. # Split the dir name and sheet name (if it exists) # dir_long , sheet = url.split(/\#/) link_type = 0x0103 # Always absolute unless sheet.nil? link_type |= 0x08 sheet_len = [sheet.length + 0x01].pack("V") sheet = sheet.split('').join("\0") + "\0\0\0" else sheet_len = '' sheet = '' end # Pack the link type link_type = [link_type].pack("V") # Make the string null terminated dir_long = dir_long + "\0" # Pack the lengths of the dir string dir_long_len = [dir_long.length].pack("V") # Store the long dir name as a wchar string (non-null terminated) dir_long = dir_long.split('').join("\0") + "\0" # Pack the undocumented part of the hyperlink stream unknown1 = ['D0C9EA79F9BACE118C8200AA004BA90B02000000'].pack("H*") # Pack the main data stream data = [row1, row2, col1, col2].pack("vvvv") + unknown1 + link_type + dir_long_len + dir_long + sheet_len + sheet # Pack the header data length = data.length header = [record, length].pack("vv") # Write the packed data print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) return error end private :write_url_external_net # # :call-seq: # write_date_time(row, col , date_string[, format]) # write_date_time(A1_notation, date_string[, format]) # # Write a datetime string in ISO8601 "yyyy-mm-ddThh:mm:ss.ss" format as a # number representing an Excel date. format is optional. # # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # -3 : Invalid date_time, written as string # # The write_date_time() method can be used to write a date or time to the # cell specified by row and column: # # worksheet.write_date_time('A1', '2004-05-13T23:20', date_format) # # The date_string should be in the following format: # # yyyy-mm-ddThh:mm:ss.sss # # This conforms to an ISO8601 date but it should be noted that the full # range of ISO8601 formats are not supported. # # The following variations on the date_string parameter are permitted: # # yyyy-mm-ddThh:mm:ss.sss # Standard format # yyyy-mm-ddT # No time # Thh:mm:ss.sss # No date # yyyy-mm-ddThh:mm:ss.sssZ # Additional Z (but not time zones) # yyyy-mm-ddThh:mm:ss # No fractional seconds # yyyy-mm-ddThh:mm # No seconds # # Note that the T is required in all cases. # # A date should always have a format, otherwise it will appear as a number, # see "DATES AND TIME IN EXCEL" and "CELL FORMATTING". Here is a typical # example: # # date_format = workbook.add_format(:num_format => 'mm/dd/yy') # worksheet.write_date_time('A1', '2004-05-13T23:20', date_format) # # Valid dates should be in the range 1900-01-01 to 9999-12-31, for the 1900 # epoch and 1904-01-01 to 9999-12-31, for the 1904 epoch. As with Excel, # dates outside these ranges will be written as a string. # # See also the date_time.rb program in the examples directory of the distro. # def write_date_time(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end return -1 if (args.size < 3) # Check the number of args row = args[0] # Zero indexed row col = args[1] # Zero indexed column str = args[2] # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, col) != 0 error = 0 date_time = convert_date_time(str) unless date_time.nil? error = write_number(row, col, date_time, args[3]) else # The date isn't valid so write it as a string. write_string(row, col, str, args[3]) error = -3 end return error end # # The function takes a date and time in ISO8601 "yyyy-mm-ddThh:mm:ss.ss" format # and converts it to a decimal number representing a valid Excel date. # # Dates and times in Excel are represented by real numbers. The integer part of # the number stores the number of days since the epoch and the fractional part # stores the percentage of the day in seconds. The epoch can be either 1900 or # 1904. # # Parameter: Date and time string in one of the following formats: # yyyy-mm-ddThh:mm:ss.ss # Standard # yyyy-mm-ddT # Date only # Thh:mm:ss.ss # Time only # # Returns: # A decimal number representing a valid Excel date, or # undef if the date is invalid. # def convert_date_time(date_time_string) #:nodoc: date_time = date_time_string days = 0 # Number of days since epoch seconds = 0 # Time expressed as fraction of 24h hours in seconds # Strip leading and trailing whitespace. date_time.sub!(/^\s+/, '') date_time.sub!(/\s+$/, '') # Check for invalid date char. return nil if date_time =~ /[^0-9T:\-\.Z]/ # Check for "T" after date or before time. return nil unless date_time =~ /\dT|T\d/ # Strip trailing Z in ISO8601 date. date_time.sub!(/Z$/, '') # Split into date and time. date, time = date_time.split(/T/) # We allow the time portion of the input DateTime to be optional. unless time.nil? # Match hh:mm:ss.sss+ where the seconds are optional if time =~ /^(\d\d):(\d\d)(:(\d\d(\.\d+)?))?/ hour = $1.to_i min = $2.to_i sec = $4.to_f || 0 else return nil # Not a valid time format. end # Some boundary checks return nil if hour >= 24 return nil if min >= 60 return nil if sec >= 60 # Excel expresses seconds as a fraction of the number in 24 hours. seconds = (hour * 60* 60 + min * 60 + sec) / (24.0 * 60 * 60) end # We allow the date portion of the input DateTime to be optional. return seconds if date == '' # Match date as yyyy-mm-dd. if date =~ /^(\d\d\d\d)-(\d\d)-(\d\d)$/ year = $1.to_i month = $2.to_i day = $3.to_i else return nil # Not a valid date format. end # Set the epoch as 1900 or 1904. Defaults to 1900. # Special cases for Excel. unless @date_1904 return seconds if date == '1899-12-31' # Excel 1900 epoch return seconds if date == '1900-01-00' # Excel 1900 epoch return 60 + seconds if date == '1900-02-29' # Excel false leapday end # We calculate the date by calculating the number of days since the epoch # and adjust for the number of leap days. We calculate the number of leap # days by normalising the year in relation to the epoch. Thus the year 2000 # becomes 100 for 4 and 100 year leapdays and 400 for 400 year leapdays. # epoch = @date_1904 ? 1904 : 1900 offset = @date_1904 ? 4 : 0 norm = 300 range = year -epoch # Set month days and check for leap year. mdays = [31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31] leap = 0 leap = 1 if year % 4 == 0 && year % 100 != 0 || year % 400 == 0 mdays[1] = 29 if leap != 0 # Some boundary checks return nil if year < epoch or year > 9999 return nil if month < 1 or month > 12 return nil if day < 1 or day > mdays[month -1] # Accumulate the number of days since the epoch. days = day # Add days for current month (0 .. month-2).each do |m| days += mdays[m] # Add days for past months end days += range *365 # Add days for past years days += ((range) / 4) # Add leapdays days -= ((range + offset) /100) # Subtract 100 year leapdays days += ((range + offset + norm)/400) # Add 400 year leapdays days -= leap # Already counted above # Adjust for Excel erroneously treating 1900 as a leap year. days = days + 1 if !@date_1904 and days > 59 return days + seconds end ############################################################################### # # _write_row_default() # row : Row Number # colMic : First defined column # colMac : Last defined column # # Write a default row record, in compatibility mode, for rows that don't have # user specified values.. # def write_row_default(row, colMic, colMac) #:nodoc: record = 0x0208 # Record identifier length = 0x0010 # Number of bytes to follow miyRw = 0xFF # Row height irwMac = 0x0000 # Used by Excel to optimise loading reserved = 0x0000 # Reserved grbit = 0x0100 # Option flags ixfe = 0x0F # XF index header = [record, length].pack("vv") data = [row, colMic, colMac, miyRw, irwMac, reserved, grbit, ixfe].pack("vvvvvvvv") print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end private :write_row_default ############################################################################### # # _check_dimensions($row, $col, $ignore_row, $ignore_col) # # Check that $row and $col are valid and store max and min values for use in # DIMENSIONS record. See, _store_dimensions(). # # The $ignore_row/$ignore_col flags is used to indicate that we wish to # perform the dimension check without storing the value. # # The ignore flags are use by set_row() and data_validate. # def check_dimensions(row, col, ignore_row = 0, ignore_col = 0) #:nodoc: return -2 if row.nil? return -2 if row >= @xls_rowmax return -2 if col.nil? return -2 if col >= @xls_colmax if ignore_row == 0 if @dim_rowmin.nil? or row < @dim_rowmin @dim_rowmin = row end if @dim_rowmax.nil? or row > @dim_rowmax @dim_rowmax = row end end if ignore_col == 0 if @dim_colmin.nil? or col < @dim_colmin @dim_colmin = col end if @dim_colmax.nil? or col > @dim_colmax @dim_colmax =col end end return 0 end private :check_dimensions ############################################################################### # # _store_dimensions() # # Writes Excel DIMENSIONS to define the area in which there is cell data. # # Notes: # Excel stores the max row/col as row/col +1. # Max and min values of 0 are used to indicate that no cell data. # We set the undef member data to 0 since it is used by _store_table(). # Inserting images or charts doesn't change the DIMENSION data. # def store_dimensions #:nodoc: record = 0x0200 # Record identifier length = 0x000E # Number of bytes to follow reserved = 0x0000 # Reserved by Excel row_min = @dim_rowmin.nil? ? 0 : @dim_rowmin row_max = @dim_rowmax.nil? ? 0 : @dim_rowmax + 1 col_min = @dim_colmin.nil? ? 0 : @dim_colmin col_max = @dim_colmax.nil? ? 0 : @dim_colmax + 1 # Set member data to the new max/min value for use by _store_table(). @dim_rowmin = row_min @dim_rowmax = row_max @dim_colmin = col_min @dim_colmax = col_max header = [record, length].pack("vv") fields = [row_min, row_max, col_min, col_max, reserved] data = fields.pack("VVvvv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end # private :store_dimensions ############################################################################### # # _store_window2() # # Write BIFF record Window2. # def store_window2 #:nodoc: record = 0x023E # Record identifier length = 0x0012 # Number of bytes to follow grbit = 0x00B6 # Option flags rwTop = @first_row # Top visible row colLeft = @first_col # Leftmost visible column rgbHdr = 0x00000040 # Row/col heading, grid color wScaleSLV = 0x0000 # Zoom in page break preview wScaleNormal = 0x0000 # Zoom in normal view reserved = 0x00000000 # The options flags that comprise $grbit fDspFmla = @display_formulas # 0 - bit fDspGrid = @screen_gridlines # 1 fDspRwCol = @display_headers # 2 fFrozen = @frozen # 3 fDspZeros = @display_zeros # 4 fDefaultHdr = 1 # 5 fArabic = @display_arabic # 6 fDspGuts = @outline_on # 7 fFrozenNoSplit = @frozen_no_split # 0 - bit fSelected = @selected # 1 fPaged = @active # 2 fBreakPreview = 0 # 3 grbit = fDspFmla grbit |= fDspGrid << 1 grbit |= fDspRwCol << 2 grbit |= fFrozen << 3 grbit |= fDspZeros << 4 grbit |= fDefaultHdr << 5 grbit |= fArabic << 6 grbit |= fDspGuts << 7 grbit |= fFrozenNoSplit << 8 grbit |= fSelected << 9 grbit |= fPaged << 10 grbit |= fBreakPreview << 11 header = [record, length].pack("vv") data =[grbit, rwTop, colLeft, rgbHdr, wScaleSLV, wScaleNormal, reserved].pack("vvvVvvV") print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end private :store_window2 ############################################################################### # # _store_page_view() # # Set page view mode. Only applicable to Mac Excel. # def store_page_view #:nodoc: return if @page_view == 0 data = ['C8081100C808000000000040000000000900000000'].pack("H*") print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(data) end private :store_page_view ############################################################################### # # _store_tab_color() # # Write the Tab Color BIFF record. # def store_tab_color #:nodoc: color = @tab_color return if color == 0 record = 0x0862 # Record identifier length = 0x0014 # Number of bytes to follow zero = 0x0000 unknown = 0x0014 header = [record, length].pack("vv") data = [record, zero, zero, zero, zero, zero, unknown, zero, color, zero].pack("vvvvvvvvvv") print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end private :store_tab_color ############################################################################### # # _store_defrow() # # Write BIFF record DEFROWHEIGHT. # def store_defrow #:nodoc: record = 0x0225 # Record identifier length = 0x0004 # Number of bytes to follow grbit = 0x0000 # Options. height = 0x00FF # Default row height header = [record, length].pack("vv") data = [grbit, height].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_defrow ############################################################################### # # _store_defcol() # # Write BIFF record DEFCOLWIDTH. # def store_defcol #:nodoc: record = 0x0055 # Record identifier length = 0x0002 # Number of bytes to follow colwidth = 0x0008 # Default column width header = [record, length].pack("vv") data = [colwidth].pack("v") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_defcol ############################################################################### # # _store_colinfo($firstcol, $lastcol, $width, $format, $hidden) # # firstcol : First formatted column # lastcol : Last formatted column # width : Col width in user units, 8.43 is default # format : format object # hidden : hidden flag # # Write BIFF record COLINFO to define column widths # # Note: The SDK says the record length is 0x0B but Excel writes a 0x0C # length record. # def store_colinfo(firstcol=0, lastcol=0, width=8.43, format=nil, hidden=0, level=0, collapsed=0) #:nodoc: record = 0x007D # Record identifier length = 0x000B # Number of bytes to follow # Excel rounds the column width to the nearest pixel. Therefore we first # convert to pixels and then to the internal units. The pixel to users-units # relationship is different for values less than 1. # width ||= 8.43 if width < 1 pixels = width *12 else pixels = width *7 +5 end pixels = pixels.to_i coldx = (pixels *256/7).to_i # Col width in internal units grbit = 0x0000 # Option flags reserved = 0x00 # Reserved # Check for a format object if !format.nil? && format.kind_of?(Format) ixfe = format.xf_index else ixfe = 0x0F end # Set the limits for the outline levels (0 <= x <= 7). level = 0 if level < 0 level = 7 if level > 7 # Set the options flags. (See set_row() for more details). grbit |= 0x0001 if hidden != 0 grbit |= level << 8 grbit |= 0x1000 if collapsed != 0 header = [record, length].pack("vv") data = [firstcol, lastcol, coldx, ixfe, grbit, reserved].pack("vvvvvC") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end # private :store_colinfo ############################################################################### # # _store_filtermode() # # Write BIFF record FILTERMODE to indicate that the worksheet contains # AUTOFILTER record, ie. autofilters with a filter set. # def store_filtermode #:nodoc: # Only write the record if the worksheet contains a filtered autofilter. return '' if @filter_on == 0 record = 0x009B # Record identifier length = 0x0000 # Number of bytes to follow header = [record, length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header) end # private :store_filtermode ############################################################################### # # _store_autofilterinfo() # # Write BIFF record AUTOFILTERINFO. # def store_autofilterinfo #:nodoc: # Only write the record if the worksheet contains an autofilter. return '' if @filter_count == 0 record = 0x009D # Record identifier length = 0x0002 # Number of bytes to follow num_filters = @filter_count header = [record, length].pack('vv') data = [num_filters].pack('v') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_autofilterinfo ############################################################################### # # _store_selection($first_row, $first_col, $last_row, $last_col) # # Write BIFF record SELECTION. # def store_selection(first_row=0, first_col=0, last_row = nil, last_col =nil) #:nodoc: record = 0x001D # Record identifier length = 0x000F # Number of bytes to follow pnn = @active_pane # Pane position rwAct = first_row # Active row colAct = first_col # Active column irefAct = 0 # Active cell ref cref = 1 # Number of refs rwFirst = first_row # First row in reference colFirst = first_col # First col in reference rwLast = last_row || rwFirst # Last row in reference colLast = last_col || colFirst # Last col in reference # Swap last row/col for first row/col as necessary if rwFirst > rwLast tmp = rwFirst rwFirst = rwLast rwLast = tmp end if colFirst > colLast tmp = colFirst colFirst = colLast colLast = tmp end header = [record, length].pack('vv') data = [pnn, rwAct, colAct, irefAct, cref, rwFirst, rwLast, colFirst, colLast].pack('CvvvvvvCC') print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end # private :store_selection ############################################################################### # # _store_externcount($count) # # Write BIFF record EXTERNCOUNT to indicate the number of external sheet # references in a worksheet. # # Excel only stores references to external sheets that are used in formulas. # For simplicity we store references to all the sheets in the workbook # regardless of whether they are used or not. This reduces the overall # complexity and eliminates the need for a two way dialogue between the formula # parser the worksheet objects. # def store_externcount(count) #:nodoc: record = 0x0016 # Record identifier length = 0x0002 # Number of bytes to follow cxals = count # Number of external references header = [record, length].pack('vv') data = [cxals].pack('v') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_externcount ############################################################################### # # _store_externsheet($sheetname) # sheetname : Worksheet name # # Writes the Excel BIFF EXTERNSHEET record. These references are used by # formulas. A formula references a sheet name via an index. Since we store a # reference to all of the external worksheets the EXTERNSHEET index is the same # as the worksheet index. # def store_externsheet(sheetname) #:nodoc: record = 0x0017 # Record identifier # length; # Number of bytes to follow # cch # Length of sheet name # rgch # Filename encoding # References to the current sheet are encoded differently to references to # external sheets. # if @name == sheetname sheetname = '' length = 0x02 # The following 2 bytes cch = 1 # The following byte rgch = 0x02 # Self reference else length = 0x02 + sheetname.length cch = sheetname.length rgch = 0x03 # Reference to a sheet in the current workbook end header = [record, length].pack('vv') data = [cch, rgch].pack('CC') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data, sheetname) end private :store_externsheet ############################################################################### # # _store_panes(y, x, colLeft, no_split, pnnAct) # y = args[0] || 0 # Vertical split position # x = $_[1] || 0; # Horizontal split position # rwTop = $_[2]; # Top row visible # my $colLeft = $_[3]; # Leftmost column visible # my $no_split = $_[4]; # No used here. # my $pnnAct = $_[5]; # Active pane # # # Writes the Excel BIFF PANE record. # The panes can either be frozen or thawed (unfrozen). # Frozen panes are specified in terms of a integer number of rows and columns. # Thawed panes are specified in terms of Excel's units for rows and columns. # def store_panes(y=0, x=0, rwtop=nil, colleft=nil, no_split=nil, pnnAct=nil) #:nodoc: record = 0x0041 # Record identifier length = 0x000A # Number of bytes to follow # Code specific to frozen or thawed panes. if @frozen != 0 # Set default values for $rwTop and $colLeft rwtop = y if rwtop.nil? colleft = x if colleft.nil? else # Set default values for $rwTop and $colLeft rwtop = 0 if rwtop.nil? colleft = 0 if colleft.nil? # Convert Excel's row and column units to the internal units. # The default row height is 12.75 # The default column width is 8.43 # The following slope and intersection values were interpolated. # y = 20*y + 255 x = 113.879*x + 390 end # Determine which pane should be active. There is also the undocumented # option to override this should it be necessary: may be removed later. # if pnnAct.nil? pnnAct = 0 if (x != 0 && y != 0) # Bottom right pnnAct = 1 if (x != 0 && y == 0) # Top right pnnAct = 2 if (x == 0 && y != 0) # Bottom left pnnAct = 3 if (x == 0 && y == 0) # Top left end @active_pane = pnnAct # Used in _store_selection header = [record, length].pack('vv') data = [x, y, rwtop, colleft, pnnAct].pack('vvvvv') print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end private :store_panes ############################################################################### # # _store_setup() # # Store the page setup SETUP BIFF record. # def store_setup #:nodoc: record = 0x00A1 # Record identifier length = 0x0022 # Number of bytes to follow iPaperSize = @paper_size # Paper size iScale = @print_scale # Print scaling factor iPageStart = @page_start # Starting page number iFitWidth = @fit_width # Fit to number of pages wide iFitHeight = @fit_height # Fit to number of pages high grbit = 0x00 # Option flags iRes = 0x0258 # Print resolution iVRes = 0x0258 # Vertical print resolution numHdr = @margin_header # Header Margin numFtr = @margin_footer # Footer Margin iCopies = 0x01 # Number of copies fLeftToRight = @page_order # Print over then down fLandscape = @orientation # Page orientation fNoPls = 0x0 # Setup not read from printer fNoColor = @black_white # Print black and white fDraft = @draft_quality # Print draft quality fNotes = @print_comments# Print notes fNoOrient = 0x0 # Orientation not set fUsePage = @custom_start # Use custom starting page grbit = fLeftToRight grbit |= fLandscape << 1 grbit |= fNoPls << 2 grbit |= fNoColor << 3 grbit |= fDraft << 4 grbit |= fNotes << 5 grbit |= fNoOrient << 6 grbit |= fUsePage << 7 numHdr = [numHdr].pack('d') numFtr = [numFtr].pack('d') if @byte_order != 0 && @byte_order != '' numHdr = numHdr.reverse numFtr = numFtr.reverse end header = [record, length].pack('vv') data1 = [iPaperSize, iScale, iPageStart, iFitWidth, iFitHeight, grbit, iRes, iVRes].pack("vvvvvvvv") data2 = numHdr + numFtr data3 = [iCopies].pack('v') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data1, data2, data3) end private :store_setup ############################################################################### # # _store_header() # # Store the header caption BIFF record. # def store_header #:nodoc: record = 0x0014 # Record identifier # length # Bytes to follow str = @header # header string cch = str.length # Length of header string encoding = @header_encoding # Character encoding # Character length is num of chars not num of bytes cch /= 2 if encoding != 0 # Change the UTF-16 name from BE to LE str = str.unpack('v*').pack('n*') if encoding != 0 length = 3 + str.length header = [record, length].pack('vv') data = [cch, encoding].pack('vC') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data, str) end private :store_header ############################################################################### # # _store_footer() # # Store the footer caption BIFF record. # def store_footer #:nodoc: record = 0x0015 # Record identifier # length; # Bytes to follow str = @footer # footer string cch = str.length # Length of ooter string encoding = @footer_encoding # Character encoding # Character length is num of chars not num of bytes cch /= 2 if encoding != 0 # Change the UTF-16 name from BE to LE str = str.unpack('v*').pack('n*') if encoding != 0 length = 3 + str.length header = [record, length].pack('vv') data = [cch, encoding].pack('vC') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data, str) end private :store_footer ############################################################################### # # _store_hcenter() # # Store the horizontal centering HCENTER BIFF record. # def store_hcenter #:nodoc: record = 0x0083 # Record identifier length = 0x0002 # Bytes to follow fHCenter = @hcenter # Horizontal centering header = [record, length].pack('vv') data = [fHCenter].pack('v') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_hcenter ############################################################################### # # _store_vcenter() # # Store the vertical centering VCENTER BIFF record. # def store_vcenter #:nodoc: record = 0x0084 # Record identifier length = 0x0002 # Bytes to follow mfVCenter = @vcenter # Horizontal centering header = [record, length].pack('vv') data = [mfVCenter].pack('v') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_vcenter ############################################################################### # # _store_margin_left() # # Store the LEFTMARGIN BIFF record. # def store_margin_left #:nodoc: record = 0x0026 # Record identifier length = 0x0008 # Bytes to follow margin = @margin_left # Margin in inches header = [record, length].pack('vv') data = [margin].pack('d') data = data.reverse if @byte_order != 0 && @byte_order != '' print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_margin_left ############################################################################### # # _store_margin_right() # # Store the RIGHTMARGIN BIFF record. # def store_margin_right #:nodoc: record = 0x0027 # Record identifier length = 0x0008 # Bytes to follow margin = @margin_right # Margin in inches header = [record, length].pack('vv') data = [margin].pack('d') data = data.reverse if @byte_order != 0 && @byte_order != '' print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_margin_right ############################################################################### # # _store_margin_top() # # Store the TOPMARGIN BIFF record. # def store_margin_top #:nodoc: record = 0x0028 # Record identifier length = 0x0008 # Bytes to follow margin = @margin_top # Margin in inches header = [record, length].pack('vv') data = [margin].pack('d') data = data.reverse if @byte_order != 0 && @byte_order != '' print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_margin_top ############################################################################### # # _store_margin_bottom() # # Store the BOTTOMMARGIN BIFF record. # def store_margin_bottom #:nodoc: record = 0x0029 # Record identifier length = 0x0008 # Bytes to follow margin = @margin_bottom # Margin in inches header = [record, length].pack('vv') data = [margin].pack('d') data = data.reverse if @byte_order != 0 && @byte_order != '' print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_margin_bottom ############################################################################### # # merge_cells($first_row, $first_col, $last_row, $last_col) # # This is an Excel97/2000 method. It is required to perform more complicated # merging than the normal align merge in Format.pm # def merge_cells(*args) #:nodoc: # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end record = 0x00E5 # Record identifier length = 0x000A # Bytes to follow cref = 1 # Number of refs rwFirst = args[0] # First row in reference colFirst = args[1] # First col in reference rwLast = args[2] || rwFirst # Last row in reference colLast = args[3] || colFirst # Last col in reference # Excel doesn't allow a single cell to be merged return if rwFirst == rwLast and colFirst == colLast # Swap last row/col with first row/col as necessary rwFirst, rwLast = rwLast, rwFirst if rwFirst > rwLast colFirst, colLast = colLast, colFirst if colFirst > colLast header = [record, length].pack("vv") data = [cref, rwFirst, rwLast, colFirst, colLast].pack("vvvvv") print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end ############################################################################### # # _store_print_headers() # # Write the PRINTHEADERS BIFF record. # def store_print_headers #:nodoc: record = 0x002a # Record identifier length = 0x0002 # Bytes to follow fPrintRwCol = @print_headers # Boolean flag header = [record, length].pack("vv") data = [fPrintRwCol].pack("v") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_print_headers ############################################################################### # # _store_print_gridlines() # # Write the PRINTGRIDLINES BIFF record. Must be used in conjunction with the # GRIDSET record. # def store_print_gridlines #:nodoc: record = 0x002b # Record identifier length = 0x0002 # Bytes to follow fPrintGrid = @print_gridlines # Boolean flag header = [record, length].pack("vv") data = [fPrintGrid].pack("v") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_print_gridlines ############################################################################### # # _store_gridset() # # Write the GRIDSET BIFF record. Must be used in conjunction with the # PRINTGRIDLINES record. # def store_gridset #:nodoc: record = 0x0082 # Record identifier length = 0x0002 # Bytes to follow fGridSet = @print_gridlines == 0 ? 1 : 0 # Boolean flag header = [record, length].pack("vv") data = [fGridSet].pack("v") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_gridset ############################################################################### # # _store_guts() # # Write the GUTS BIFF record. This is used to configure the gutter margins # where Excel outline symbols are displayed. The visibility of the gutters is # controlled by a flag in WSBOOL. See also _store_wsbool(). # # We are all in the gutter but some of us are looking at the stars. # def store_guts #:nodoc: record = 0x0080 # Record identifier length = 0x0008 # Bytes to follow dxRwGut = 0x0000 # Size of row gutter dxColGut = 0x0000 # Size of col gutter row_level = @outline_row_level col_level = 0 # Calculate the maximum column outline level. The equivalent calculation # for the row outline level is carried out in set_row(). # @colinfo.each do |colinfo| # Skip cols without outline level info. next if colinfo.size < 6 col_level = colinfo[5] if colinfo[5] > col_level end # Set the limits for the outline levels (0 <= x <= 7). col_level = 0 if col_level < 0 col_level = 7 if col_level > 7 # The displayed level is one greater than the max outline levels row_level = row_level + 1 if row_level > 0 col_level = col_level + 1 if col_level > 0 header = [record, length].pack("vv") data = [dxRwGut, dxColGut, row_level, col_level].pack("vvvv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_guts ############################################################################### # # _store_wsbool() # # Write the WSBOOL BIFF record, mainly for fit-to-page. Used in conjunction # with the SETUP record. # def store_wsbool #:nodoc: record = 0x0081 # Record identifier length = 0x0002 # Bytes to follow grbit = 0x0000 # Option flags # Set the option flags grbit |= 0x0001 # Auto page breaks visible grbit |= 0x0020 if @outline_style != 0 # Auto outline styles grbit |= 0x0040 if @outline_below != 0 # Outline summary below grbit |= 0x0080 if @outline_right != 0 # Outline summary right grbit |= 0x0100 if @fit_page != 0 # Page setup fit to page grbit |= 0x0400 if @outline_on != 0 # Outline symbols displayed header = [record, length].pack("vv") data = [grbit].pack('v') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_wsbool ############################################################################### # # _store_hbreak() # # Write the HORIZONTALPAGEBREAKS BIFF record. # def store_hbreak #:nodoc: # Return if the user hasn't specified pagebreaks return if @hbreaks.size == 0 # Sort and filter array of page breaks breaks = sort_pagebreaks(@hbreaks) record = 0x001b # Record identifier cbrk = breaks.size # Number of page breaks length = 2 + 6 * cbrk # Bytes to follow header = [record, length].pack("vv") data = [cbrk].pack("v") # Append each page break breaks.each do |brk| data = data + [brk, 0x0000, 0x00ff].pack("vvv") end print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_hbreak ############################################################################### # # _store_vbreak() # # Write the VERTICALPAGEBREAKS BIFF record. # def store_vbreak #:nodoc: # Return if the user hasn't specified pagebreaks return if @vbreaks.size == 0 # Sort and filter array of page breaks breaks = sort_pagebreaks(@vbreaks) record = 0x001a # Record identifier cbrk = breaks.size # Number of page breaks length = 2 + 6*cbrk # Bytes to follow header = [record, length].pack("vv") data = [cbrk].pack("v") # Append each page break breaks.each do |brk| data = data + [brk, 0x0000, 0x00ff].pack("vvv") end print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_vbreak ############################################################################### # # _store_protect() # # Set the Biff PROTECT record to indicate that the worksheet is protected. # def store_protect #:nodoc: # Exit unless sheet protection has been specified return if @protect == 0 record = 0x0012 # Record identifier length = 0x0002 # Bytes to follow fLock = @protect # Worksheet is protected header = [record, length].pack("vv") data = [fLock].pack("v") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_protect ############################################################################### # # _store_obj_protect() # # Set the Biff OBJPROTECT record to indicate that objects are protected. # def store_obj_protect #:nodoc: # Exit unless sheet protection has been specified return if @protect == 0 record = 0x0063 # Record identifier length = 0x0002 # Bytes to follow fLock = @protect # Worksheet is protected header = [record, length].pack("vv") data = [fLock].pack("v") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_obj_protect ############################################################################### # # _store_password() # # Write the worksheet PASSWORD record. # def store_password #:nodoc: # Exit unless sheet protection and password have been specified return if (@protect == 0 or @password.nil?) record = 0x0013 # Record identifier length = 0x0002 # Bytes to follow wPassword = @password # Encoded password header = [record, length].pack("vv") data = [wPassword].pack("v") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_password # # Note about compatibility mode. # # Excel doesn't require every possible Biff record to be present in a file. # In particular if the indexing records INDEX, ROW and DBCELL aren't present # it just ignores the fact and reads the cells anyway. This is also true of # the EXTSST record. Gnumeric and OOo also take this approach. This allows # WriteExcel to ignore these records in order to minimise the amount of data # stored in memory. However, other third party applications that read Excel # files often expect these records to be present. In "compatibility mode" # WriteExcel writes these records and tries to be as close to an Excel # generated file as possible. # # This requires additional data to be stored in memory until the file is # about to be written. This incurs a memory and speed penalty and may not be # suitable for very large files. # ############################################################################### # # _store_table() # # Write cell data stored in the worksheet row/col table. # # This is only used when compatibity_mode() is in operation. # # This method writes ROW data, then cell data (NUMBER, LABELSST, etc) and then # DBCELL records in blocks of 32 rows. This is explained in detail (for a # change) in the Excel SDK and in the OOo Excel file format doc. # def store_table #:nodoc: return if @compatibility == 0 # Offset from the DBCELL record back to the first ROW of the 32 row block. row_offset = 0 # Track rows that have cell data or modified by set_row(). written_rows = [] # Write the ROW records with updated max/min col fields. # (0 .. @dim_rowmax-1).each do |row| # Skip unless there is cell data in row or the row has been modified. next unless @table[row] or @row_data[row] # Store the rows with data. written_rows.push(row) # Increase the row offset by the length of a ROW record; row_offset += 20 # The max/min cols in the ROW records are the same as in DIMENSIONS. col_min = @dim_colmin col_max = @dim_colmax # Write a user specified ROW record (modified by set_row()). if @row_data[row] # Rewrite the min and max cols for user defined row record. packed_row = @row_data[row] packed_row[6..9] = [col_min, col_max].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__}) packed_row\n" if defined?($debug) append(packed_row) else # Write a default Row record if there isn't a user defined ROW. write_row_default(row, col_min, col_max) end # If 32 rows have been written or we are at the last row in the # worksheet then write the cell data and the DBCELL record. # if written_rows.size == 32 or row == @dim_rowmax -1 # Offsets to the first cell of each row. cell_offsets = [] cell_offsets.push(row_offset - 20) # Write the cell data in each row and sum their lengths for the # cell offsets. # written_rows.each do |row| cell_offset = 0 if @table[row] @table[row].each do |col| next unless col print "sheet #{@name} : #{__FILE__}(#{__LINE__}) cell_data\n" if defined?($debug) append(col) length = col.length row_offset += length cell_offset += length end end cell_offsets.push(cell_offset) end # The last offset isn't required. cell_offsets.pop # Stores the DBCELL offset for use in the INDEX record. @db_indices.push(@datasize) # Write the DBCELL record. store_dbcell(row_offset, cell_offsets) # Clear the variable for the next block of rows. written_rows = [] cell_offsets = [] row_offset = 0 end end end private :store_table ############################################################################### # # _store_dbcell() # # Store the DBCELL record using the offset calculated in _store_table(). # # This is only used when compatibity_mode() is in operation. # def store_dbcell(row_offset, cell_offsets) #:nodoc: record = 0x00D7 # Record identifier length = 4 + 2 * cell_offsets.size # Bytes to follow header = [record, length].pack('vv') data = [row_offset].pack('V') cell_offsets.each do |co| data = data + [co].pack('v') end print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) end private :store_dbcell ############################################################################### # # _store_index() # # Store the INDEX record using the DBCELL offsets calculated in _store_table(). # # This is only used when compatibity_mode() is in operation. # def store_index #:nodoc: return if @compatibility == 0 indices = @db_indices reserved = 0x00000000 row_min = @dim_rowmin row_max = @dim_rowmax record = 0x020B # Record identifier length = 16 + 4 * indices.size # Bytes to follow header = [record, length].pack('vv') data = [reserved, row_min, row_max, reserved].pack('VVVV') indices.each do |index| data = data + [index + @offset + 20 + length + 4].pack('V') end print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end private :store_index ############################################################################### # # insert_chart($row, $col, $chart, $x, $y, $scale_x, $scale_y) # insert_chart($A1_notation, $chart, $x, $y, $scale_x, $scale_y) # # Insert a chart into a worksheet. The $chart argument should be a Chart # object or else it is assumed to be a filename of an external binary file. # The latter is for backwards compatibility. # # This method can be used to insert a Chart object into a worksheet. # The Chart must be created by the add_chart() Workbook method and it must # have the embedded option set. # # chart = workbook.add_chart(:type => Chart::Line, :embedded => 1 ) # # # Configure the chart. # ... # # # Insert the chart into the a worksheet. # worksheet.insert_chart('E2', chart) # # See add_chart() for details on how to create the Chart object and # WriteExcel::Chart for details on how to configure it. See also the # chart_*.pl programs in the examples directory of the distro. # # The _x_, _y_, _scale_x_ and _scale_y_ parameters are optional. # # The parameters _x_ and _y_ can be used to specify an offset from the top # left hand corner of the cell specified by _row_ and _col_. The offset values # are in pixels. See the insert_image method above for more information on sizes. # # worksheet1.insert_chart('E2', chart, 3, 3) # # The parameters _scale_x_ and _scale_y_ can be used to scale the inserted # image horizontally and vertically: # # Scale the width by 120% and the height by 150% # worksheet.insert_chart('E2', chart, 0, 0, 1.2, 1.5) # # The easiest way to calculate the required scaling is to create a test # chart worksheet with WriteExcel. Then open the file, select the chart # and drag the corner to get the required size. While holding down the # mouse the scale of the resized chart is shown to the left of the formula # bar. # # Note: you must call set_row() or set_column() before insert_chart() # if you wish to change the default dimensions of any of the rows or columns # that the chart occupies. The height of a row can also change if you use # a font that is larger than the default. This in turn will affect the # scaling of your chart. To avoid this you should explicitly set the height # of the row using set_row() if it contains a font size that will change # the row height. # def insert_chart(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end row = args[0] col = args[1] chart = args[2] x_offset = args[3] || 0 y_offset = args[4] || 0 scale_x = args[5] || 1 scale_y = args[6] || 1 if chart.kind_of?(Chart) print "Not a embedded style Chart object in insert_chart()" unless chart.embedded else # Assume an external bin filename. print "Couldn't locate #{chart} in insert_chart()" unless FileTest.exist?(chart) end @charts[row] = { col => [row, col, chart, x_offset, y_offset, scale_x, scale_y] } end # # :call-seq: # insert_image(row, col, filename, x, y, scale_x, scale_y) # insert_image(A1_notation, filename, x, y, scale_x, scale_y) # # Insert an image into the worksheet. # # This method can be used to insert a image into a worksheet. The image can # be in PNG, JPEG or BMP format. The _x_, _y_, _scale_x_ and _scale_y_ # parameters are optional. # # worksheet1.insert_image('A1', 'ruby.bmp') # worksheet2.insert_image('A1', '../images/ruby.bmp') # worksheet3.insert_image('A1', '.c:\images\ruby.bmp') # # The parameters _x_ and _y_ can be used to specify an offset from the top # left hand corner of the cell specified by _row_ and _col_. The offset # values are in pixels. # # worksheet1.insert_image('A1', 'ruby.bmp', 32, 10) # # The default width of a cell is 63 pixels. The default height of a cell is # 17 pixels. The pixels offsets can be calculated using the following # relationships: # # Wp = int(12We) if We < 1 # Wp = int(7We +5) if We >= 1 # Hp = int(4/3He) # # where: # We is the cell width in Excels units # Wp is width in pixels # He is the cell height in Excels units # Hp is height in pixels # # The offsets can be greater than the width or height of the underlying cell. # This can be occasionally useful if you wish to align two or more images # relative to the same cell. # # The parameters _scale_x_ and _scale_y_ can be used to scale the inserted # image horizontally and vertically: # # # Scale the inserted image: width x 2.0, height x 0.8 # worksheet.insert_image('A1', 'ruby.bmp', 0, 0, 2, 0.8) # # See also the images.rb program in the examples directory of the distro. # # Note: # # you must call set_row() or set_column() before insert_image() if you wish # to change the default dimensions of any of the rows or columns that the # image occupies. The height of a row can also change if you use a font that # is larger than the default. This in turn will affect the scaling of your # image. To avoid this you should explicitly set the height of the row using # set_row() if it contains a font size that will change the row height. # # BMP images must be 24 bit, true colour, bitmaps. In general it is best to # avoid BMP images since they aren't compressed. # def insert_image(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end row = args[0] col = args[1] image = args[2] x_offset = args[3] || 0 y_offset = args[4] || 0 scale_x = args[5] || 1 scale_y = args[6] || 1 raise "Insufficient arguments in insert_image()" unless args.size >= 3 raise "Couldn't locate #{image}: $!" unless test(?e, image) @images[row] = { col => [ row, col, image, x_offset, y_offset, scale_x, scale_y] } end # Older method name for backwards compatibility. # *insert_bitmap = *insert_image; ############################################################################### # # _position_object() # # Calculate the vertices that define the position of a graphical object within # the worksheet. # # +------------+------------+ # | A | B | # +-----+------------+------------+ # | |(x1,y1) | | # | 1 |(A1)._______|______ | # | | | | | # | | | | | # +-----+----| BITMAP |-----+ # | | | | | # | 2 | |______________. | # | | | (B2)| # | | | (x2,y2)| # +---- +------------+------------+ # # Example of a bitmap that covers some of the area from cell A1 to cell B2. # # Based on the width and height of the bitmap we need to calculate 8 vars: # $col_start, $row_start, $col_end, $row_end, $x1, $y1, $x2, $y2. # The width and height of the cells are also variable and have to be taken into # account. # The values of $col_start and $row_start are passed in from the calling # function. The values of $col_end and $row_end are calculated by subtracting # the width and height of the bitmap from the width and height of the # underlying cells. # The vertices are expressed as a percentage of the underlying cell width as # follows (rhs values are in pixels): # # x1 = X / W *1024 # y1 = Y / H *256 # x2 = (X-1) / W *1024 # y2 = (Y-1) / H *256 # # Where: X is distance from the left side of the underlying cell # Y is distance from the top of the underlying cell # W is the width of the cell # H is the height of the cell # # Note: the SDK incorrectly states that the height should be expressed as a # percentage of 1024. # def position_object(col_start, row_start, x1, y1, width, height) #:nodoc: # col_start; # Col containing upper left corner of object # x1; # Distance to left side of object # row_start; # Row containing top left corner of object # y1; # Distance to top of object # col_end; # Col containing lower right corner of object # x2; # Distance to right side of object # row_end; # Row containing bottom right corner of object # y2; # Distance to bottom of object # width; # Width of image frame # height; # Height of image frame # Adjust start column for offsets that are greater than the col width while x1 >= size_col(col_start) x1 = x1 - size_col(col_start) col_start = col_start + 1 end # Adjust start row for offsets that are greater than the row height while y1 >= size_row(row_start) y1 = y1 - size_row(row_start) row_start = row_start + 1 end # Initialise end cell to the same as the start cell col_end = col_start row_end = row_start width = width + x1 height = height + y1 # Subtract the underlying cell widths to find the end cell of the image while width >= size_col(col_end) width -= size_col(col_end) col_end += 1 end # Subtract the underlying cell heights to find the end cell of the image while height >= size_row(row_end) height -= size_row(row_end) row_end += 1 end # Bitmap isn't allowed to start or finish in a hidden cell, i.e. a cell # with zero eight or width. # return if size_col(col_start) == 0 return if size_col(col_end) == 0 return if size_row(row_start) == 0 return if size_row(row_end) == 0 # Convert the pixel values to the percentage value expected by Excel x1 = 1024.0 * x1 / size_col(col_start) y1 = 256.0 * y1 / size_row(row_start) x2 = 1024.0 * width / size_col(col_end) y2 = 256.0 * height / size_row(row_end) # Simulate ceil() without calling POSIX::ceil(). x1 = (x1 +0.5).to_i y1 = (y1 +0.5).to_i x2 = (x2 +0.5).to_i y2 = (y2 +0.5).to_i return [ col_start, x1, row_start, y1, col_end, x2, row_end, y2 ] end ############################################################################### # # _size_col($col) # # 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 we use a value of zero. # def size_col(col) #:nodoc: # Look up the cell value to see if it has been changed unless @col_sizes[col].nil? width = @col_sizes[col] # The relationship is different for user units less than 1. if width < 1 return (width *12).to_i else return (width *7 +5 ).to_i end else return 64 end end private :size_col ############################################################################### # # _size_row($row) # # Convert the height of a cell from user's units to pixels. By interpolation # the relationship is: y = 4/3x. If the height hasn't been set by the user we # use the default value. If the row is hidden we use a value of zero. (Not # possible to hide row yet). # def size_row(row) #:nodoc: # Look up the cell value to see if it has been changed unless @row_sizes[row].nil? if @row_sizes[row] == 0 return 0 else return (4/3.0 * @row_sizes[row]).to_i end else return 17 end end private :size_row ############################################################################### # # _store_zoom($zoom) # # # Store the window zoom factor. This should be a reduced fraction but for # simplicity we will store all fractions with a numerator of 100. # def store_zoom #:nodoc: # If scale is 100 we don't need to write a record return if @zoom == 100 record = 0x00A0 # Record identifier length = 0x0004 # Bytes to follow header = [record, header].pack("vv") data = [@zoom, 100].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) end private :store_zoom ############################################################################### # # write_utf16be_string($row, $col, $string, $format) # # Write a Unicode string to the specified row and column (zero indexed). # $format is optional. # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # -3 : long string truncated to 255 chars # def write_utf16be_string(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end return -1 if (args.size < 3) # Check the number of args record = 0x00FD # Record identifier length = 0x000A # Bytes to follow row = args[0] # Zero indexed row col = args[1] # Zero indexed column strlen = args[2].length str = args[2] xf = xf_record_index(row, col, args[3]) # The cell format encoding = 0x1 str_error = 0 # Check that row and col are valid and store max and min values return -2 if check_dimensions(row, col) != 0 # Limit the utf16 string to the max number of chars (not bytes). if strlen > 32767* 2 str = str[0..32767*2] str_error = -3 end num_bytes = str.length num_chars = (num_bytes / 2).to_i # Check for a valid 2-byte char string. raise "Uneven number of bytes in Unicode string" if num_bytes % 2 != 0 # Change from UTF16 big-endian to little endian str = str.unpack('n*').pack('v*') # Add the encoding and length header to the string. str_header = [num_chars, encoding].pack("vC") str = str_header + str unless @sinfo[:str_table][str] @sinfo[:str_table][str] = @sinfo[:str_unique] @sinfo[:str_unique] += 1 end @sinfo[:str_total] += 1 header = [record, length].pack("vv") data = [row, col, xf, @sinfo[:str_table][str]].pack("vvvV") # Store the data or write immediately depending on the compatibility mode. if @compatibility != 0 tmp = [] tmp[col] = header + data @table[row] = tmp else print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) end return str_error end ############################################################################### # # write_utf16le_string($row, $col, $string, $format) # # Write a UTF-16LE string to the specified row and column (zero indexed). # $format is optional. # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # -3 : long string truncated to 255 chars # def write_utf16le_string(*args) # Check for a cell reference in A1 notation and substitute row and column if args[0] =~ /^\D/ args = substitute_cellref(*args) end return -1 if (args.size < 3) # Check the number of args record = 0x00FD # Record identifier length = 0x000A # Bytes to follow row = args[0] # Zero indexed row col = args[1] # Zero indexed column str = args[2] format = args[3] # The cell format # Change from UTF16 big-endian to little endian str = str.unpack('n*').pack("v*") return write_utf16be_string(row, col, str, format) end # Older method name for backwards compatibility. # *write_unicode = *write_utf16be_string; # *write_unicode_le = *write_utf16le_string; ############################################################################### # # _store_autofilters() # # Function to iterate through the columns that form part of an autofilter # range and write Biff AUTOFILTER records if a filter expression has been set. # def store_autofilters #:nodoc: # Skip all columns if no filter have been set. return '' if @filter_on == 0 col1 = @filter_area[2] col2 = @filter_area[3] col1.upto(col2) do |i| # Reverse order since records are being pre-pended. col = col2 -i # Skip if column doesn't have an active filter. next unless @filter_cols[col] # Retrieve the filter tokens and write the autofilter records. store_autofilter(col, *@filter_cols[col]) end end private :store_autofilters ############################################################################### # # _store_autofilter() # my $index = $_[0]; # my $operator_1 = $_[1]; # my $token_1 = $_[2]; # my $join = $_[3]; # And/Or # my $operator_2 = $_[4]; # my $token_2 = $_[5]; # # Function to write worksheet AUTOFILTER records. These contain 2 Biff Doper # structures to represent the 2 possible filter conditions. # def store_autofilter(index, operator_1, token_1, #:nodoc: join = nil, operator_2 = nil, token_2 = nil) record = 0x009E length = 0x0000 top10_active = 0 top10_direction = 0 top10_percent = 0 top10_value = 101 grbit = join || 0 optimised_1 = 0 optimised_2 = 0 doper_1 = '' doper_2 = '' string_1 = '' string_2 = '' # Excel used an optimisation in the case of a simple equality. optimised_1 = 1 if operator_1 == 2 optimised_2 = 1 if !operator_2.nil? and operator_2 == 2 # Convert non-simple equalities back to type 2. See _parse_filter_tokens(). operator_1 = 2 if operator_1 == 22 operator_2 = 2 if !operator_2.nil? and operator_2 == 22 # Handle a "Top" style expression. if operator_1 >= 30 # Remove the second expression if present. operator_2 = nil token_2 = nil # Set the active flag. top10_active = 1 if (operator_1 == 30 or operator_1 == 31) top10_direction = 1 end if (operator_1 == 31 or operator_1 == 33) top10_percent = 1 end if (top10_direction == 1) operator_1 = 6 else operator_1 = 3 end top10_value = token_1.to_i token_1 = 0 end grbit |= optimised_1 << 2 grbit |= optimised_2 << 3 grbit |= top10_active << 4 grbit |= top10_direction << 5 grbit |= top10_percent << 6 grbit |= top10_value << 7 doper_1, string_1 = pack_doper(operator_1, token_1) doper_2, string_2 = pack_doper(operator_2, token_2) doper_1 = '' if doper_1.nil? doper_2 = '' if doper_2.nil? string_1 = '' if string_1.nil? string_2 = '' if string_2.nil? data = [index].pack('v') data = data + [grbit].pack('v') data = data + doper_1 + doper_2 + string_1 + string_2 length = data.length header = [record, length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) prepend(header, data) end # private :store_autofilter ############################################################################### # # _pack_doper() # # Create a Biff Doper structure that represents a filter expression. Depending # on the type of the token we pack an Empty, String or Number doper. # def pack_doper(operator, token) #:nodoc: doper = '' string = '' # Return default doper for non-defined filters. if operator.nil? return pack_unused_doper, string end if token.to_s =~ /^blanks|nonblanks$/i doper = pack_blanks_doper(operator, token) elsif operator == 2 or !(token.to_s =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/) # Excel treats all tokens as strings if the operator is equality, =. string = token.to_s encoding = 0 length = string.length # Handle utf8 strings if string =~ NonAscii string = NKF.nkf('-w16B0 -m0 -W', string) encodign = 1 end string = [encoding].pack('C') + string doper = pack_string_doper(operator, length) else string = '' doper = pack_number_doper(operator, token) end return [doper, string] end private :pack_doper ############################################################################### # # _pack_unused_doper() # # Pack an empty Doper structure. # def pack_unused_doper #:nodoc: return [0x0,0x0,0x0,0x0,0x0,0x0,0x0,0x0,0x0,0x0].pack('C10') end private :pack_unused_doper ############################################################################### # # _pack_blanks_doper() # # Pack an Blanks/NonBlanks Doper structure. # def pack_blanks_doper(operator, token) #:nodoc: if token == 'blanks' type = 0x0C operator = 2 else type = 0x0E operator = 5 end doper = [type, # Data type operator, 0x0000, 0x0000 # Reserved ].pack('CCVV') return doper end private :pack_blanks_doper ############################################################################### # # _pack_string_doper() # # Pack an string Doper structure. # def pack_string_doper(operator, length) #:nodoc: doper = [0x06, # Data type operator, 0x0000, #Reserved length, # String char length 0x0, 0x0, 0x0 # Reserved ].pack('CCVCCCC') return doper end private :pack_string_doper ############################################################################### # # _pack_number_doper() # # Pack an IEEE double number Doper structure. # def pack_number_doper(operator, number) #:nodoc: number = [number].pack('d') number.reverse! if @byte_order != '' && @byte_order != 0 doper = [0x04, operator].pack('CC') + number return doper end private :pack_number_doper # # Methods related to comments and MSO objects. # ############################################################################### # # _prepare_images() # # Turn the HoH that stores the images into an array for easier handling. # def prepare_images #:nodoc: count = 0 images = [] # We sort the images by row and column but that isn't strictly required. # rows = @images.keys.sort rows.each do |row| cols = @images[row].keys.sort cols.each do |col| images.push(@images[row][col]) count += 1 end end @images = {} @images_array = images return count end # private :prepare_images ############################################################################### # # _prepare_comments() # # Turn the HoH that stores the comments into an array for easier handling. # def prepare_comments #:nodoc: count = 0 comments = [] # We sort the comments by row and column but that isn't strictly required. # rows = @comments.keys.sort rows.each do |row| cols = @comments[row].keys.sort cols.each do |col| comments.push(@comments[row][col]) count += 1 end end @comments = {} @comments_array = comments return count end # private :prepare_comments ############################################################################### # # _prepare_charts() # # Turn the HoH that stores the charts into an array for easier handling. # def prepare_charts #:nodoc: count = 0 charts = [] # We sort the charts by row and column but that isn't strictly required. # rows = @charts.keys.sort rows.each do |row| cols = @charts[row].keys.sort cols.each do |col| charts.push(@charts[row][col]) count += 1 end end @charts = {} @charts_array = charts count end # private :prepare_charts ############################################################################### # # _store_images() # # Store the collections of records that make up images. # def store_images #:nodoc: record = 0x00EC # Record identifier length = 0x0000 # Bytes to follow ids = @object_ids.dup spid = ids.shift images = @images_array num_images = images.size num_filters = @filter_count num_comments = @comments_array.size num_charts = @charts_array.size # Skip this if there aren't any images. return if num_images == 0 (0 .. num_images-1).each do |i| row = images[i][0] col = images[i][1] name = images[i][2] x_offset = images[i][3] y_offset = images[i][4] scale_x = images[i][5] scale_y = images[i][6] image_id = images[i][7] type = images[i][8] width = images[i][9] height = images[i][10] width = width * scale_x unless scale_x == 0 height = height * scale_y unless scale_y == 0 # Calculate the positions of image object. vertices = position_object(col,row,x_offset,y_offset,width,height) if (i == 0) # Write the parent MSODRAWIING record. dg_length = 156 + 84*(num_images -1) spgr_length = 132 + 84*(num_images -1) dg_length += 120 * num_charts spgr_length += 120 * num_charts dg_length += 96 * num_filters spgr_length += 96 * num_filters dg_length += 128 * num_comments spgr_length += 128 * num_comments data = store_mso_dg_container(dg_length) + store_mso_dg(*ids) + store_mso_spgr_container(spgr_length) + store_mso_sp_container(40) + store_mso_spgr() + store_mso_sp(0x0, spid, 0x0005) spid = spid + 1 data = data + store_mso_sp_container(76) + store_mso_sp(75, spid, 0x0A00) spid = spid + 1 data = data + store_mso_opt_image(image_id) + store_mso_client_anchor(2, *vertices) + store_mso_client_data() else # Write the child MSODRAWIING record. data = store_mso_sp_container(76) + store_mso_sp(75, spid, 0x0A00) spid = spid + 1 data = data + store_mso_opt_image(image_id) + store_mso_client_anchor(2, *vertices) + store_mso_client_data end length = data.length header = [record, length].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) store_obj_image(i+1) end @object_ids[0] = spid end private :store_images ############################################################################### # # _store_charts() # # Store the collections of records that make up charts. # def store_charts #:nodoc: record = 0x00EC # Record identifier length = 0x0000 # Bytes to follow ids = @object_ids.dup spid = ids.shift charts = @charts_array num_charts = charts.size num_filters = @filter_count num_comments = @comments_array.size # Number of objects written so far. num_objects = @images_array.size # Skip this if there aren't any charts. return if num_charts == 0 (0 .. num_charts-1 ).each do |i| row = charts[i][0] col = charts[i][1] chart = charts[i][2] x_offset = charts[i][3] y_offset = charts[i][4] scale_x = charts[i][5] scale_y = charts[i][6] width = 526 height = 319 width *= scale_x if scale_x.kind_of?(Numeric) && scale_x != 0 height *= scale_y if scale_y.kind_of?(Numeric) && scale_y != 0 # Calculate the positions of chart object. vertices = position_object( col, row, x_offset, y_offset, width, height ) if (i == 0 and num_objects == 0) # Write the parent MSODRAWIING record. dg_length = 192 + 120*(num_charts -1) spgr_length = 168 + 120*(num_charts -1) dg_length += 96 *num_filters spgr_length += 96 *num_filters dg_length += 128 *num_comments spgr_length += 128 *num_comments data = store_mso_dg_container(dg_length) + store_mso_dg(*ids) + store_mso_spgr_container(spgr_length) + store_mso_sp_container(40) + store_mso_spgr() + store_mso_sp(0x0, spid, 0x0005) spid += 1 data = data + store_mso_sp_container(112) + store_mso_sp(201, spid, 0x0A00) spid += 1 data = data + store_mso_opt_chart() + store_mso_client_anchor(0, *vertices) + store_mso_client_data() else # Write the child MSODRAWIING record. data = store_mso_sp_container(112) + store_mso_sp(201, spid, 0x0A00) spid += 1 data = data + store_mso_opt_chart() + store_mso_client_anchor(0, *vertices) + store_mso_client_data() end length = data.length header = [record, length].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) store_obj_chart(num_objects+i+1) store_chart_binary(chart) end # Simulate the EXTERNSHEET link between the chart and data using a formula # such as '=Sheet1!A1'. # TODO. Won't work for external data refs. Also should use a more direct # method. # formula = "='#{@name}'!A1" store_formula(formula) @object_ids[0] = spid end private :store_charts ############################################################################### # # _store_chart_binary # # Add the binary data for a chart. This could either be from a Chart object # or from an external binary file (for backwards compatibility). # def store_chart_binary(chart) #:nodoc: if chart.kind_of?(Chart) chart.close tmp = chart.get_data print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(tmp) else filehandle = File.open(chart, "rb") # die "Couldn't open $filename in add_chart_ext(): $!.\n"; while tmp = filehandle.read(4096) print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(tmp) end end end private :store_chart_binary ############################################################################### # # _store_filters() # # Store the collections of records that make up filters. # def store_filters #:nodoc: record = 0x00EC # Record identifier length = 0x0000 # Bytes to follow ids = @object_ids.dup spid = ids.shift filter_area = @filter_area num_filters = @filter_count num_comments = @comments_array.size # Number of objects written so far. num_objects = @images_array.size + @charts_array.size # Skip this if there aren't any filters. return if num_filters == 0 row1, row2, col1, col2 = @filter_area (0 .. num_filters-1).each do |i| vertices = [ col1 +i, 0, row1 , 0, col1 +i +1, 0, row1 +1, 0] if i == 0 and !num_objects.nil? # Write the parent MSODRAWIING record. dg_length = 168 + 96*(num_filters -1) spgr_length = 144 + 96*(num_filters -1) dg_length = dg_length + 128 *num_comments spgr_length = spgr_length + 128 *num_comments data = store_mso_dg_container(dg_length) + store_mso_dg(*ids) + store_mso_spgr_container(spgr_length) + store_mso_sp_container(40) + store_mso_spgr() + store_mso_sp(0x0, spid, 0x0005) spid = spid + 1 data = data + store_mso_sp_container(88) + store_mso_sp(201, spid, 0x0A00) + store_mso_opt_filter() + store_mso_client_anchor(1, *vertices) + store_mso_client_data() spid = spid + 1 else # Write the child MSODRAWIING record. data = store_mso_sp_container(88) + store_mso_sp(201, spid, 0x0A00) + store_mso_opt_filter() + store_mso_client_anchor(1, *vertices) + store_mso_client_data() spid = spid + 1 end length = data.length header = [record, length].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) store_obj_filter(num_objects+i+1, col1 +i) end # Simulate the EXTERNSHEET link between the filter and data using a formula # such as '=Sheet1!A1'. # TODO. Won't work for external data refs. Also should use a more direct # method. # formula = "=#{@name}!A1" store_formula(formula) @object_ids[0] = spid end private :store_filters ############################################################################### # # _store_comments() # # Store the collections of records that make up cell comments. # # NOTE: We write the comment objects last since that makes it a little easier # to write the NOTE records directly after the MSODRAWIING records. # def store_comments #:nodoc: record = 0x00EC # Record identifier length = 0x0000 # Bytes to follow ids = @object_ids.dup spid = ids.shift comments = @comments_array num_comments = @comments.size # Number of objects written so far. num_objects = @images_array.size + @filter_count + @charts_array.size # Skip this if there aren't any comments. return if num_comments == 0 (0 .. num_comments-1).each do |i| row = comments[i][0] col = comments[i][1] str = comments[i][2] encoding = comments[i][3] visible = comments[i][6] color = comments[i][7] vertices = comments[i][8] str_len = str.length str_len = str_len / 2 if encoding != 0 # Num of chars not bytes. formats = [[0, 9], [str_len, 0]] if i == 0 and num_objects != 0 # Write the parent MSODRAWIING record. dg_length = 200 + 128*(num_comments -1) spgr_length = 176 + 128*(num_comments -1) data = store_mso_dg_container(dg_length) + store_mso_dg(*ids) + store_mso_spgr_container(spgr_length) + store_mso_sp_container(40) + store_mso_spgr() + store_mso_sp(0x0, spid, 0x0005) spid = spid + 1 data = data + store_mso_sp_container(120) + store_mso_sp(202, spid, 0x0A00) + store_mso_opt_comment(0x80, visible, color) + store_mso_client_anchor(3, *vertices) + store_mso_client_data() spid = spid + 1 else # Write the child MSODRAWIING record. data = store_mso_sp_container(120) + store_mso_sp(202, spid, 0x0A00) + store_mso_opt_comment(0x80, visible, color) + store_mso_client_anchor(3, *vertices) + store_mso_client_data() spid = spid + 1 end length = data.length header = [record, length].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(eader, data) store_obj_comment(num_objects + i + 1) store_mso_drawing_text_box() store_txo(str_len) store_txo_continue_1(str, encoding) store_txo_continue_2(formats) end # Write the NOTE records after MSODRAWIING records. (0 .. num_comments-1).each do |i| row = comments[i][0] col = comments[i][1] author = comments[i][4] author_enc = comments[i][5] visible = comments[i][6] store_note(row, col, num_objects + i + 1, author, author_enc, visible) end end private :store_comments ############################################################################### # # _store_mso_dg_container() # # Write the Escher DgContainer record that is part of MSODRAWING. # def store_mso_dg_container(length) #:nodoc: type = 0xF002 version = 15 instance = 0 data = '' return add_mso_generic(type, version, instance, data, length) end # private :store_mso_dg_container ############################################################################### # # _store_mso_dg() # # Write the Escher Dg record that is part of MSODRAWING. # def store_mso_dg(instance, num_shapes, max_spid) #:nodoc: type = 0xF008 version = 0 data = '' length = 8 data = [num_shapes, max_spid].pack("VV") return add_mso_generic(type, version, instance, data, length) end # private :store_mso_dg ############################################################################### # # _store_mso_spgr_container() # # Write the Escher SpgrContainer record that is part of MSODRAWING. # def store_mso_spgr_container(length) #:nodoc: type = 0xF003 version = 15 instance = 0 data = '' return add_mso_generic(type, version, instance, data, length) end # private :store_mso_spgr_container ############################################################################### # # _store_mso_sp_container() # # Write the Escher SpContainer record that is part of MSODRAWING. # def store_mso_sp_container(length) #:nodoc: type = 0xF004 version = 15 instance = 0 data = '' return add_mso_generic(type, version, instance, data, length) end # private :store_mso_sp_container ############################################################################### # # _store_mso_spgr() # # Write the Escher Spgr record that is part of MSODRAWING. # def store_mso_spgr #:nodoc: type = 0xF009 version = 1 instance = 0 data = [0, 0, 0, 0].pack("VVVV") length = 16 return add_mso_generic(type, version, instance, data, length) end # private :store_mso_spgr ############################################################################### # # _store_mso_sp() # # Write the Escher Sp record that is part of MSODRAWING. # def store_mso_sp(instance, spid, options) #:nodoc: type = 0xF00A version = 2 data = '' length = 8 data = [spid, options].pack('VV') return add_mso_generic(type, version, instance, data, length) end # private :store_mso_sp ############################################################################### # # _store_mso_opt_comment() # # Write the Escher Opt record that is part of MSODRAWING. # def store_mso_opt_comment(spid, visible = nil, colour = 0x50) #:nodoc: type = 0xF00B version = 3 instance = 9 data = '' length = 54 # Use the visible flag if set by the user or else use the worksheet value. # Note that the value used is the opposite of _store_note(). # unless visible.nil? visible = visible ? 0x0000 : 0x0002 else visible = @comments_visible != 0 ? 0x0000 : 0x0002 end data = [spid].pack('V') + ['0000BF00080008005801000000008101'].pack("H*") + [colour].pack("C") + ['000008830150000008BF011000110001'+'02000000003F0203000300BF03'].pack("H*") + [visible].pack('v') + ['0A00'].pack('H*') return add_mso_generic(type, version, instance, data, length) end # private :store_mso_opt_comment ############################################################################### # # _store_mso_opt_image() # # Write the Escher Opt record that is part of MSODRAWING. # def store_mso_opt_image(spid) #:nodoc: type = 0xF00B version = 3 instance = 3 data = '' length = nil data = [0x4104].pack('v') + [spid].pack('V') + [0x01BF].pack('v') + [0x00010000].pack('V') + [0x03BF].pack( 'v') + [0x00080000].pack( 'V') return add_mso_generic(type, version, instance, data, length) end # private :store_mso_opt_image ############################################################################### # # _store_mso_opt_chart() # # Write the Escher Opt record that is part of MSODRAWING. # def store_mso_opt_chart #:nodoc: type = 0xF00B version = 3 instance = 9 data = '' length = nil data = [0x007F].pack('v') + # Protection -> fLockAgainstGrouping [0x01040104].pack('V') + [0x00BF].pack('v') + # Text -> fFitTextToShape [0x00080008].pack('V') + [0x0181].pack('v') + # Fill Style -> fillColor [0x0800004E].pack('V') + [0x0183].pack('v') + # Fill Style -> fillBackColor [0x0800004D].pack('V') + [0x01BF].pack('v') + # Fill Style -> fNoFillHitTest [0x00110010].pack('V') + [0x01C0].pack('v') + # Line Style -> lineColor [0x0800004D].pack('V') + [0x01FF].pack('v') + # Line Style -> fNoLineDrawDash [0x00080008].pack('V') + [0x023F].pack('v') + # Shadow Style -> fshadowObscured [0x00020000].pack('V') + [0x03BF].pack('v') + # Group Shape -> fPrint [0x00080000].pack('V') return add_mso_generic(type, version, instance, data, length) end # private :store_mso_opt_chart ############################################################################### # # _store_mso_opt_filter() # # Write the Escher Opt record that is part of MSODRAWING. # def store_mso_opt_filter #:nodoc: type = 0xF00B version = 3 instance = 5 data = '' length = nil data = [0x007F].pack('v') + # Protection -> fLockAgainstGrouping [0x01040104].pack('V') + [0x00BF].pack('v') + # Text -> fFitTextToShape [0x00080008].pack('V')+ [0x01BF].pack('v') + # Fill Style -> fNoFillHitTest [0x00010000].pack('V')+ [0x01FF].pack('v') + # Line Style -> fNoLineDrawDash [0x00080000].pack('V')+ [0x03BF].pack('v') + # Group Shape -> fPrint [0x000A0000].pack('V') return add_mso_generic(type, version, instance, data, length) end # private :store_mso_opt_filter ############################################################################### # # _store_mso_client_anchor() # my flag = shift; # my $col_start = $_[0]; # Col containing upper left corner of object # my $x1 = $_[1]; # Distance to left side of object # # my $row_start = $_[2]; # Row containing top left corner of object # my $y1 = $_[3]; # Distance to top of object # # my $col_end = $_[4]; # Col containing lower right corner of object # my $x2 = $_[5]; # Distance to right side of object # # my $row_end = $_[6]; # Row containing bottom right corner of object # my $y2 = $_[7]; # Distance to bottom of object # # Write the Escher ClientAnchor record that is part of MSODRAWING. # def store_mso_client_anchor(flag, col_start, x1, row_start, y1, col_end, x2, row_end, y2) #:nodoc: type = 0xF010 version = 0 instance = 0 data = '' length = 18 data = [flag, col_start, x1, row_start, y1, col_end, x2, row_end, y2].pack('v9') return add_mso_generic(type, version, instance, data, length) end # private :store_mso_client_anchor ############################################################################### # # _store_mso_client_data() # # Write the Escher ClientData record that is part of MSODRAWING. # def store_mso_client_data #:nodoc: type = 0xF011 version = 0 instance = 0 data = '' length = 0 return add_mso_generic(type, version, instance, data, length) end # private :store_mso_client_data ############################################################################### # # _store_obj_comment() # my $obj_id = $_[0]; # Object ID number. # # Write the OBJ record that is part of cell comments. # def store_obj_comment(obj_id) #:nodoc: record = 0x005D # Record identifier length = 0x0034 # Bytes to follow obj_type = 0x0019 # Object type (comment). data = '' # Record data. sub_record = 0x0000 # Sub-record identifier. sub_length = 0x0000 # Length of sub-record. sub_data = '' # Data of sub-record. options = 0x4011 reserved = 0x0000 # Add ftCmo (common object data) subobject sub_record = 0x0015 # ftCmo sub_length = 0x0012 sub_data = [obj_type, obj_id, options, reserved, reserved, reserved].pack( "vvvVVV") data = [sub_record, sub_length].pack("vv") + sub_data # Add ftNts (note structure) subobject sub_record = 0x000D # ftNts sub_length = 0x0016 sub_data = [reserved,reserved,reserved,reserved,reserved,reserved].pack( "VVVVVv") data = data + [sub_record, sub_length].pack("vv") + sub_data # Add ftEnd (end of object) subobject sub_record = 0x0000 # ftNts sub_length = 0x0000 data = data + [sub_record, sub_length].pack("vv") # Pack the record. header = [record, length].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) end # private :store_obj_comment ############################################################################### # # _store_obj_image() # my $obj_id = $_[0]; # Object ID number. # # Write the OBJ record that is part of image records. # def store_obj_image(obj_id) #:nodoc: record = 0x005D # Record identifier length = 0x0026 # Bytes to follow obj_type = 0x0008 # Object type (Picture). data = '' # Record data. sub_record = 0x0000 # Sub-record identifier. sub_length = 0x0000 # Length of sub-record. sub_data = '' # Data of sub-record. options = 0x6011 reserved = 0x0000 # Add ftCmo (common object data) subobject sub_record = 0x0015 # ftCmo sub_length = 0x0012 sub_data = [obj_type, obj_id, options, reserved, reserved, reserved].pack('vvvVVV') data = [sub_record, sub_length].pack('vv') + sub_data # Add ftCf (Clipboard format) subobject sub_record = 0x0007 # ftCf sub_length = 0x0002 sub_data = [0xFFFF].pack( 'v') data = data + [sub_record, sub_length].pack('vv') + sub_data # Add ftPioGrbit (Picture option flags) subobject sub_record = 0x0008 # ftPioGrbit sub_length = 0x0002 sub_data = [0x0001].pack('v') data = data + [sub_record, sub_length].pack('vv') + sub_data # Add ftEnd (end of object) subobject sub_record = 0x0000 # ftNts sub_length = 0x0000 data = data + [sub_record, sub_length].pack('vv') # Pack the record. header = [record, length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) end # private :store_obj_image ############################################################################### # # _store_obj_chart() # my $obj_id = $_[0]; # Object ID number. # # Write the OBJ record that is part of chart records. # def store_obj_chart(obj_id) #:nodoc: record = 0x005D # Record identifier length = 0x001A # Bytes to follow obj_type = 0x0005 # Object type (chart). data = '' # Record data. sub_record = 0x0000 # Sub-record identifier. sub_length = 0x0000 # Length of sub-record. sub_data = '' # Data of sub-record. options = 0x6011 reserved = 0x0000 # Add ftCmo (common object data) subobject sub_record = 0x0015 # ftCmo sub_length = 0x0012 sub_data = [obj_type, obj_id, options, reserved, reserved, reserved].pack('vvvVVV') data = [sub_record, sub_length].pack('vv') + sub_data # Add ftEnd (end of object) subobject sub_record = 0x0000 # ftNts sub_length = 0x0000 data = data + [sub_record, sub_length].pack('vv') # Pack the record. header = [record, length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) end # private :store_obj_chart ############################################################################### # # _store_obj_filter() # my $obj_id = $_[0]; # Object ID number. # my $col = $_[1]; # # Write the OBJ record that is part of filter records. # def store_obj_filter(obj_id, col) #:nodoc: record = 0x005D # Record identifier length = 0x0046 # Bytes to follow obj_type = 0x0014 # Object type (combo box). data = '' # Record data. sub_record = 0x0000 # Sub-record identifier. sub_length = 0x0000 # Length of sub-record. sub_data = '' # Data of sub-record. options = 0x2101 reserved = 0x0000 # Add ftCmo (common object data) subobject sub_record = 0x0015 # ftCmo sub_length = 0x0012 sub_data = [obj_type, obj_id, options, reserved, reserved, reserved].pack('vvvVVV') data = [sub_record, sub_length].pack('vv') + sub_data # Add ftSbs Scroll bar subobject sub_record = 0x000C # ftSbs sub_length = 0x0014 sub_data = ['0000000000000000640001000A00000010000100'].pack('H*') data = data + [sub_record, sub_length].pack('vv') + sub_data # Add ftLbsData (List box data) subobject sub_record = 0x0013 # ftLbsData sub_length = 0x1FEE # Special case (undocumented). # If the filter is active we set one of the undocumented flags. if @filter_cols[col] sub_data = ['000000000100010300000A0008005700'].pack('H*') else sub_data = ['00000000010001030000020008005700'].pack('H*') end data = data + [sub_record, sub_length].pack('vv') + sub_data # Add ftEnd (end of object) subobject sub_record = 0x0000 # ftNts sub_length = 0x0000 data = data + [sub_record, sub_length].pack('vv') # Pack the record. header = [record, length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) end # private :store_obj_filter ############################################################################### # # _store_mso_drawing_text_box() # # Write the MSODRAWING ClientTextbox record that is part of comments. # def store_mso_drawing_text_box #:nodoc: record = 0x00EC # Record identifier length = 0x0008 # Bytes to follow data = store_mso_client_text_box() header = [record, length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__})\n" if defined?($debug) append(header, data) end # private :store_mso_drawing_text_box ############################################################################### # # _store_mso_client_text_box() # # Write the Escher ClientTextbox record that is part of MSODRAWING. # def store_mso_client_text_box #:nodoc: type = 0xF00D version = 0 instance = 0 data = '' length = 0 return add_mso_generic(type, version, instance, data, length) end # private :store_mso_client_text_box ############################################################################### # # _store_txo() # my $string_len = $_[0]; # Length of the note text. # my $format_len = $_[1] || 16; # Length of the format runs. # my $rotation = $_[2] || 0; # Options # # Write the worksheet TXO record that is part of cell comments. # def store_txo(string_len, format_len = 16, rotation = 0) #:nodoc: record = 0x01B6 # Record identifier length = 0x0012 # Bytes to follow grbit = 0x0212 # Options reserved = 0x0000 # Options # Pack the record. header = [record, length].pack('vv') data = [grbit, rotation, reserved, reserved, string_len, format_len, reserved].pack("vvVvvvV") print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end # private :store_txo ############################################################################### # # _store_txo_continue_1() # my $string = $_[0]; # Comment string. # my $encoding = $_[1] || 0; # Encoding of the string. # # Write the first CONTINUE record to follow the TXO record. It contains the # text data. # def store_txo_continue_1(string, encoding = 0) #:nodoc: record = 0x003C # Record identifier # Split long comment strings into smaller continue blocks if necessary. # We can't let BIFFwriter::_add_continue() handled this since an extra # encoding byte has to be added similar to the SST block. # # We make the limit size smaller than the _add_continue() size and even # so that UTF16 chars occur in the same block. # limit = 8218 while string.length > limit string[0 .. limit] = "" tmp_str = string data = [encoding].pack("C") + tmp_str length = data.length header = [record, length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end # Pack the record. data = [encoding].pack("C") + string length = data.length header = [record, length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end # private :store_txo_continue_1 ############################################################################### # # _store_txo_continue_2() # my $formats = $_[0]; # Formatting information # # Write the second CONTINUE record to follow the TXO record. It contains the # formatting information for the string. # def store_txo_continue_2(formats) #:nodoc: record = 0x003C # Record identifier length = 0x0000 # Bytes to follow # Pack the record. data = '' formats.each do |a_ref| data = data + [a_ref[0], a_ref[1], 0x0].pack('vvV') end length = data.length header = [record, length].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end # private :store_txo_continue_2 ############################################################################### # # _store_note() # my $row = $_[0]; # my $col = $_[1]; # my $obj_id = $_[2]; # my $author = $_[3] || $self->{_comments_author}; # my $author_enc = $_[4] || $self->{_comments_author_enc}; # my $visible = $_[5]; # # Write the worksheet NOTE record that is part of cell comments. # def store_note(row, col, obj_id, author = nil, author_enc = nil, visible = nil) #:nodoc: record = 0x001C # Record identifier length = 0x000C # Bytes to follow author = @comments_author if author.nil? author_enc = @comments_author_enc if author_enc.nil? # Use the visible flag if set by the user or else use the worksheet value. # The flag is also set in _store_mso_opt_comment() but with the opposite # value. unless visible.nil? visible = visible != 0 ? 0x0002 : 0x0000 else visible = @comments_visible != 0 ? 0x0002 : 0x0000 end # Get the number of chars in the author string (not bytes). num_chars = author.length num_chars = num_chars / 2 if author_enc != 0 && !author_enc.nil? # Null terminate the author string. author = author + "\0" # Pack the record. data = [row, col, visible, obj_id, num_chars, author_enc].pack("vvvvvC") length = data.length + author.length header = [record, length].pack("vv") print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data, author) end # private :store_note ############################################################################### # # _comment_params() # # This method handles the additional optional parameters to write_comment() as # well as calculating the comment object position and vertices. # def comment_params(row, col, string, options = {}) #:nodoc: default_width = 128 default_height = 74 params = { :author => '', :author_encoding => 0, :encoding => 0, :color => nil, :start_cell => nil, :start_col => nil, :start_row => nil, :visible => nil, :width => default_width, :height => default_height, :x_offset => nil, :x_scale => 1, :y_offset => nil, :y_scale => 1 } # Overwrite the defaults with any user supplied values. Incorrect or # misspelled parameters are silently ignored. params.update(options) # Ensure that a width and height have been set. params[:width] = default_width if params[:width].nil? || params[:width] == 0 params[:height] = default_height if params[:height].nil? || params[:height] == 0 # Check that utf16 strings have an even number of bytes. if params[:encoding] != 0 raise "Uneven number of bytes in comment string" if string.length % 2 != 0 # Change from UTF-16BE to UTF-16LE string = string.unpack('n*').pack('v*') end if params[:author_encoding] != 0 raise "Uneven number of bytes in author string" if params[:author] % 2 != 0 # Change from UTF-16BE to UTF-16LE params[:author] = params[:author].unpack('n*').pack('v*') end # Handle utf8 strings if string =~ NonAscii string = NKF.nkf('-w16L0 -m0 -W', string) params[:encoding] = 1 end if params[:author] =~ NonAscii params[:author] = NKF.nkf('-w16L0 -m0 -W', params[:author]) params[:author_encoding] = 1 end # Limit the string to the max number of chars (not bytes). max_len = 32767 max_len = max_len * 2 if params[:encoding] != 0 if string.length > max_len string = string[0 .. max_len] end # Set the comment background colour. color = params[:color] color = Format._get_color(color) color = 0x50 if color == 0x7FFF # Default color. params[:color] = color # Convert a cell reference to a row and column. unless params[:start_cell].nil? row, col = substitute_cellref(params[:start_cell]) params[:start_row] = row params[:start_col] = col end # Set the default start cell and offsets for the comment. These are # generally fixed in relation to the parent cell. However there are # some edge cases for cells at the, er, edges. # if params[:start_row].nil? case row when 0 then params[:start_row] = 0 when 65533 then params[:start_row] = 65529 when 65534 then params[:start_row] = 65530 when 65535 then params[:start_row] = 65531 else params[:start_row] = row -1 end end if params[:y_offset].nil? case row when 0 then params[:y_offset] = 2 when 65533 then params[:y_offset] = 4 when 65534 then params[:y_offset] = 4 when 65535 then params[:y_offset] = 2 else params[:y_offset] = 7 end end if params[:start_col].nil? case col when 253 then params[:start_col] = 250 when 254 then params[:start_col] = 251 when 255 then params[:start_col] = 252 else params[:start_col] = col + 1 end end if params[:x_offset].nil? case col when 253 then params[:x_offset] = 49 when 254 then params[:x_offset] = 49 when 255 then params[:x_offset] = 49 else params[:x_offset] = 15 end end # Scale the size of the comment box if required. if params[:x_scale] != 0 params[:width] = params[:width] * params[:x_scale] end if params[:y_scale] != 0 params[:height] = params[:height] * params[:y_scale] end # Calculate the positions of comment object. vertices = position_object( params[:start_col], params[:start_row], params[:x_offset], params[:y_offset], params[:width], params[:height] ) return [row, col, string, params[:encoding], params[:author], params[:author_encoding], params[:visible], params[:color], vertices ] end # private :comment_params # # DATA VALIDATION # # # :call-seq: # data_validation(row, col, {...}) # data_validation(first_row, first_col, last_row, last_col, {...}) # # This method handles the interface to Excel data validation. # Somewhat ironically the this requires a lot of validation code since the # interface is flexible and covers a several types of data validation. # # We allow data validation to be called on one cell or a range of cells. The # hashref contains the validation parameters and must be the last param: # # Returns 0 : normal termination # -1 : insufficient number of arguments # -2 : row or column out of range # -3 : incorrect parameter. # # The data_validation() method is used to construct an Excel data validation # or to limit the user input to a dropdown list of values. # # worksheet.data_validation('B3', # { # :validate => 'integer', # :criteria => '>', # :value => 100, # }) # # worksheet.data_validation('B5:B9', # { # :validate => 'list', # :value => ['open', 'high', 'close'], # }) # # This method contains a lot of parameters and is described in detail in a # separate section "DATA VALIDATION IN EXCEL". # # See also the data_validate.rb program in the examples directory of the distro # # The data_validation() method is used to construct an Excel data validation. # # 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 also # use A1 style notation. For example: # # worksheet.data_validation(0, 0, {...}) # worksheet.data_validation(0, 0, 4, 1, {...}) # # # Which are the same as: # # $worksheet.data_validation('A1', {...}) # $worksheet.data_validation('A1:B5', {...}) # # See also the note about "Cell notation" for more information. # # The last parameter in data_validation() must be a hash ref containing the # parameters that describe the type and style of the data validation. The # allowable parameters are: # # validate # criteria # value | minimum | source # maximum # ignore_blank # dropdown # # input_title # input_message # show_input # # error_title # error_message # error_type # show_error # # These parameters are explained in the following sections. Most of the # parameters are optional, however, you will generally require the three main # options validate, criteria and value. # # worksheet.data_validation('B3', # { # :validate => 'integer', # :criteria => '>', # :value => 100 # }) # # The data_validation method returns: # # 0 for success. # -1 for insufficient number of arguments. # -2 for row or column out of bounds. # -3 for incorrect parameter or value. # # ===validate # # This parameter is passed in a hash ref to data_validation(). # # The validate parameter is used to set the type of data that you wish to # validate. It is always required and it has no default value. Allowable # values are: # # any # integer # decimal # list # date # time # length # custom # # * 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 WriteExcel. # # * 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. # # :validate => 'decimal', # :criteria => '>', # :value => 38.6, # # * 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 # 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. # # :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 # 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. # # :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. # Excel refers to this as 'Text length'. # # :validate => 'length', # :criteria => '>', # :value => 10, # # * 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 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' # 'not between' # 'equal to' | '==' | '=' # '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 operator alternatives. The following # are equivalent: # # :validate => 'integer', # :criteria => 'greater than', # :value => 100, # # :validate => 'integer', # :criteria => '>', # :value => 100, # # The list and custom validate options don't require a criteria. If you # specify one it will be ignored. # # :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(). # # 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: # # # Use 'value' # :validate => 'integer', # :criteria => '>', # :value => 100, # # # Use 'minimum' # :validate => 'integer', # :criteria => 'between', # :minimum => 1, # :maximum => 100, # # # Use 'source' # :validate => 'list', # :source => 'B1:B3', # # ====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 # criteria is either 'between' or 'not between': # # :validate => 'integer', # :criteria => 'between', # :minimum => 1, # :maximum => 100, # # ====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 # '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 # # This parameter is passed in a hash ref to data_validation(). # # 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 # # This parameter is passed in a hash ref to data_validation(). # # 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. UTF8 strings are handled # automatically. # # ====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 is # displayed when a cell is entered. It has no default value. # # :validate => 'integer', # :criteria => 'between', # :minimum => 1, # :maximum => 100, # :input_title => 'Enter the applied discount:', # :input_message => 'between 1 and 100', # # The message can be split over several lines using newlines, "\n" in double # quoted strings. # # :input_message => "This is\na test.", # # The maximum message length is 255 characters. UTF8 strings are handled # automatically. # # ====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 # 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 # # 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 # 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. UTF8 strings are handled # automatically. # # ====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 # 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', # :criteria => 'between', # :minimum => 1, # :maximum => 100, # :error_title => 'Input value is not valid', # :error_message => 'It should be an integer between 1 and 100', # # The message can be split over several lines using newlines, "\n" in double # quoted strings. # # :input_message => "This is\na test.", # # The maximum message length is 255 characters. UTF8 strings are handled # automatically. # # ====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: # # 'stop' # 'warning' # 'information' # # The default is 'stop'. # # ====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 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 # # Examples # # 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. # # worksheet.data_validation('A2', # { # :validate => 'integer', # :criteria => '>', # :value => '=E3', # }) # # 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. # # 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. # # worksheet.data_validation('A5', # { # :validate => 'list', # :source => '=E4:G4', # }) # # 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. # # worksheet.data_validation('A7', # { # :validate => 'integer', # :criteria => 'between', # :minimum => 1, # :maximum => 100, # :input_title => 'Enter an integer:', # :input_message => 'between 1 and 100', # }) # # 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 if args[0] =~ /^\D/ args = substitute_cellref(*args) end # Check for a valid number of args. return -1 if args.size != 5 && args.size != 3 # The final hashref contains the validation parameters. param = args.pop # Make the last row/col the same as the first if not defined. row1, col1, row2, col2 = args if row2.nil? row2 = row1 col2 = col1 end # Check that row and col are valid without storing the values. return -2 if check_dimensions(row1, col1, 1, 1) != 0 return -2 if check_dimensions(row2, col2, 1, 1) != 0 # Check that the last parameter is a hash list. unless param.kind_of?(Hash) # carp "Last parameter '$param' in data_validation() must be a hash ref"; return -3 end # List of valid input parameters. valid_parameter = { :validate => 1, :criteria => 1, :value => 1, :source => 1, :minimum => 1, :maximum => 1, :ignore_blank => 1, :dropdown => 1, :show_input => 1, :input_title => 1, :input_message => 1, :show_error => 1, :error_title => 1, :error_message => 1, :error_type => 1, :other_cells => 1 } # Check for valid input parameters. param.each_key do |param_key| unless valid_parameter.has_key?(param_key) # carp "Unknown parameter '$param_key' in data_validation()"; return -3 end end # Map alternative parameter names 'source' or 'minimum' to 'value'. param[:value] = param[:source] unless param[:source].nil? param[:value] = param[:minimum] unless param[:minimum].nil? # 'validate' is a required parameter. unless param.has_key?(:validate) # carp "Parameter 'validate' is required in data_validation()"; return -3 end # List of valid validation types. valid_type = { 'any' => 0, 'any value' => 0, 'whole number' => 1, 'whole' => 1, 'integer' => 1, 'decimal' => 2, 'list' => 3, 'date' => 4, 'time' => 5, 'text length' => 6, 'length' => 6, 'custom' => 7 } # Check for valid validation types. unless valid_type.has_key?(param[:validate].downcase) # carp "Unknown validation type '$param->{validate}' for parameter " . # "'validate' in data_validation()"; return -3 else param[:validate] = valid_type[param[:validate].downcase] end # No action is required for validation type 'any'. # TODO: we should perhaps store 'any' for message only validations. return 0 if param[:validate] == 0 # The list and custom validations don't have a criteria so we use a default # of 'between'. if param[:validate] == 3 || param[:validate] == 7 param[:criteria] = 'between' param[:maximum] = nil end # 'criteria' is a required parameter. unless param.has_key?(:criteria) # carp "Parameter 'criteria' is required in data_validation()"; return -3 end # List of valid criteria types. criteria_type = { 'between' => 0, 'not between' => 1, 'equal to' => 2, '=' => 2, '==' => 2, 'not equal to' => 3, '!=' => 3, '<>' => 3, 'greater than' => 4, '>' => 4, 'less than' => 5, '<' => 5, 'greater than or equal to' => 6, '>=' => 6, 'less than or equal to' => 7, '<=' => 7 } # Check for valid criteria types. unless criteria_type.has_key?(param[:criteria].downcase) # carp "Unknown criteria type '$param->{criteria}' for parameter " . # "'criteria' in data_validation()"; return -3 else param[:criteria] = criteria_type[param[:criteria].downcase] end # 'Between' and 'Not between' criteria require 2 values. if param[:criteria] == 0 || param[:criteria] == 1 unless param.has_key?(:maximum) # carp "Parameter 'maximum' is required in data_validation() " . # "when using 'between' or 'not between' criteria"; return -3 end else param[:maximum] = nil end # List of valid error dialog types. error_type = { 'stop' => 0, 'warning' => 1, 'information' => 2 } # Check for valid error dialog types. if not param.has_key?(:error_type) param[:error_type] = 0 elsif not error_type.has_key?(param[:error_type].downcase) # carp "Unknown criteria type '$param->{error_type}' for parameter " . # "'error_type' in data_validation()"; return -3 else param[:error_type] = error_type[param[:error_type].downcase] end # Convert date/times value if required. if param[:validate] == 4 || param[:validate] == 5 if param[:value] =~ /T/ date_time = convert_date_time(param[:value]) if date_time.nil? # carp "Invalid date/time value '$param->{value}' " . # "in data_validation()"; return -3 else param[:value] = date_time end end if !param[:maximum].nil? && param[:maximum] =~ /T/ date_time = convert_date_time(param[:maximum]) if date_time.nil? # carp "Invalid date/time value '$param->{maximum}' " . # "in data_validation()"; return -3 else param[:maximum] = date_time end end end # Set some defaults if they haven't been defined by the user. param[:ignore_blank] = 1 if param[:ignore_blank].nil? param[:dropdown] = 1 if param[:dropdown].nil? param[:show_input] = 1 if param[:show_input].nil? param[:show_error] = 1 if param[:show_error].nil? # These are the cells to which the validation is applied. param[:cells] = [[row1, col1, row2, col2]] # A (for now) undocumented parameter to pass additional cell ranges. if param.has_key?(:other_cells) param[:cells].push(param[:other_cells]) end # Store the validation information until we close the worksheet. @validations.push(param) end ############################################################################### # # _store_validation_count() # # Store the count of the DV records to follow. # # Note, this could be wrapped into _store_dv() but we may require separate # handling of the object id at a later stage. # def store_validation_count #:nodoc: dv_count = @validations.size obj_id = -1 return if dv_count == 0 store_dval(obj_id , dv_count) end private :store_validation_count ############################################################################### # # _store_validations() # # Store the data_validation records. # def store_validations #:nodoc: return if @validations.size == 0 @validations.each do |param| store_dv( param[:cells], param[:validate], param[:criteria], param[:value], param[:maximum], param[:input_title], param[:input_message], param[:error_title], param[:error_message], param[:error_type], param[:ignore_blank], param[:dropdown], param[:show_input], param[:show_error] ) end end private :store_validations ############################################################################### # # _store_dval() # my $obj_id = $_[0]; # Object ID number. # my $dv_count = $_[1]; # Count of DV structs to follow. # # Store the DV record which contains the number of and information common to # all DV structures. # def store_dval(obj_id, dv_count) #:nodoc: record = 0x01B2 # Record identifier length = 0x0012 # Bytes to follow flags = 0x0004 # Option flags. x_coord = 0x00000000 # X coord of input box. y_coord = 0x00000000 # Y coord of input box. # Pack the record. header = [record, length].pack('vv') data = [flags, x_coord, y_coord, obj_id, dv_count].pack('vVVVV') print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end # private :store_dval ############################################################################### # # _store_dv() # my $cells = $_[0]; # Aref of cells to which DV applies. # my $validation_type = $_[1]; # Type of data validation. # my $criteria_type = $_[2]; # Validation criteria. # my $formula_1 = $_[3]; # Value/Source/Minimum formula. # my $formula_2 = $_[4]; # Maximum formula. # my $input_title = $_[5]; # Title of input message. # my $input_message = $_[6]; # Text of input message. # my $error_title = $_[7]; # Title of error message. # my $error_message = $_[8]; # Text of input message. # my $error_type = $_[9]; # Error dialog type. # my $ignore_blank = $_[10]; # Ignore blank cells. # my $dropdown = $_[11]; # Display dropdown with list. # my $input_box = $_[12]; # Display input box. # my $error_box = $_[13]; # Display error box. # # Store the DV record that specifies the data validation criteria and options # for a range of cells.. # def store_dv(cells, validation_type, criteria_type, #:nodoc: formula_1, formula_2, input_title, input_message, error_title, error_message, error_type, ignore_blank, dropdown, input_box, error_box) record = 0x01BE # Record identifier length = 0x0000 # Bytes to follow flags = 0x00000000 # DV option flags. ime_mode = 0 # IME input mode for far east fonts. str_lookup = 0 # See below. # Set the string lookup flag for 'list' validations with a string array. if validation_type == 3 && formula_1.kind_of?(Array) str_lookup = 1 end # The dropdown flag is stored as a negated value. no_dropdown = dropdown ? 0 : 1 # Set the required flags. flags |= validation_type flags |= error_type << 4 flags |= str_lookup << 7 flags |= ignore_blank << 8 flags |= no_dropdown << 9 flags |= ime_mode << 10 flags |= input_box << 18 flags |= error_box << 19 flags |= criteria_type << 20 # Pack the validation formulas. formula_1 = pack_dv_formula(formula_1) formula_2 = pack_dv_formula(formula_2) # Pack the input and error dialog strings. input_title = pack_dv_string(input_title, 32 ) error_title = pack_dv_string(error_title, 32 ) input_message = pack_dv_string(input_message, 255) error_message = pack_dv_string(error_message, 255) # Pack the DV cell data. dv_count = cells.size dv_data = [dv_count].pack('v') cells.each do |range| dv_data = dv_data + [range[0], range[2], range[1], range[3]].pack('vvvv') end # Pack the record. data = [flags].pack('V') + input_title + error_title + input_message + error_message + formula_1 + formula_2 + dv_data header = [record, data.length].pack('vv') print "sheet #{@name} : #{__FILE__}(#{__LINE__}) \n" if defined?($debug) append(header, data) end private :store_dv ############################################################################### # # _pack_dv_string() # # Pack the strings used in the input and error dialog captions and messages. # Captions are limited to 32 characters. Messages are limited to 255 chars. # def pack_dv_string(string = nil, max_length = 0) #:nodoc: str_length = 0 encoding = 0 # The default empty string is "\0". if string.nil? || string == '' string = "\0" end # Excel limits DV captions to 32 chars and messages to 255. if string.length > max_length string = string[0 .. max_length-1] end str_length = string.length # Handle utf8 strings if string =~ NonAscii $KCODE = 'u' require 'jcode' str_length = string.jlength string = NKF.nkf('-w16L0 -m0 -W', string) encoding = 1 end return [str_length, encoding].pack('vC') + string end # private :pack_dv_string ############################################################################### # # _pack_dv_formula() # # Pack the formula used in the DV record. This is the same as an cell formula # with some additional header information. Note, DV formulas in Excel use # relative addressing (R1C1 and ptgXxxN) however we use the Formula.pm's # default absolute addressing (A1 and ptgXxx). # def pack_dv_formula(formula = nil) #:nodoc: encoding = 0 length = 0 unused = 0x0000 tokens = [] # Return a default structure for unused formulas. if formula.nil? || formula == '' return [0, unused].pack('vv') end # Pack a list array ref as a null separated string. if formula.kind_of?(Array) formula = formula.join("\0") formula = '"' + formula + '"' end # Strip the = sign at the beginning of the formula string formula = formula.to_s unless formula.kind_of?(String) formula.sub!(/^=/, '') # Parse the formula using the parser in Formula.pm parser = @parser # In order to raise formula errors from the point of view of the calling # program we use an eval block and re-raise the error from here. # tokens = parser.parse_formula(formula) # ???? # if ($@) { # $@ =~ s/\n$//; # Strip the \n used in the Formula.pm die() # croak $@; # Re-raise the error # } # else { # # TODO test for non valid ptgs such as Sheet2!A1 # } # Force 2d ranges to be a reference class. tokens.each do |t| t.sub!(/_range2d/, "_range2dR") t.sub!(/_name/, "_nameR") end # Parse the tokens into a formula string. formula = parser.parse_tokens(tokens) return [formula.length, unused].pack('vv') + formula end # private :pack_dv_formula end