# -*- coding: utf-8 -*-
require 'write_xlsx/package/xml_writer_simple'
require 'write_xlsx/package/button'
require 'write_xlsx/colors'
require 'write_xlsx/format'
require 'write_xlsx/drawing'
require 'write_xlsx/sparkline'
require 'write_xlsx/compatibility'
require 'write_xlsx/utility'
require 'write_xlsx/package/conditional_format'
require 'write_xlsx/worksheet/cell_data'
require 'write_xlsx/worksheet/data_validation'
require 'write_xlsx/worksheet/hyperlink'
require 'write_xlsx/worksheet/page_setup'
require 'tempfile'
module Writexlsx
#
# A new worksheet is created by calling the add_worksheet() method from a
# workbook object:
#
# worksheet1 = workbook.add_worksheet
# worksheet2 = workbook.add_worksheet
#
# The following methods are available through a new worksheet:
#
# * {#write}[#method-i-write]
# * write_number
# * write_string
# * write_rich_string
# * write_blank
# * write_row
# * write_col
# * write_date_time
# * write_url
# * write_formula
# * write_comment
# * show_comments
# * {#comments_author=}[#method-i-comments_author-3D]
# * insert_image
# * insert_chart
# * insert_shape
# * insert_button
# * data_validation
# * conditional_formatting
# * add_sparkline
# * add_table
# * {#name}[#method-i-name]
# * {#activate}[#method-i-activate]
# * {#select}[#method-i-select]
# * {#hide}[#method-i-hide]
# * set_first_sheet
# * {#protect}[#method-i-protect]
# * set_selection
# * set_row
# * set_column
# * outline_settings
# * freeze_panes
# * split_panes
# * merge_range
# * merge_range_type
# * {#zoom=}[#method-i-zoom-3D]
# * right_to_left
# * hide_zero
# * {#tab_color=}[#method-i-tab_color-3D]
# * {#autofilter}[#method-i-autofilter]
# * filter_column
# * filter_column_list
#
# == PAGE SET-UP METHODS
#
# Page set-up methods affect the way that a worksheet looks
# when it is printed. They control features such as page headers and footers
# and margins. These methods are really just standard worksheet methods.
#
# The following methods are available for page set-up:
#
# * set_landscape
# * set_portrait
# * set_page_view
# * {paper=}[#method-i-paper-3D]
# * center_horizontally
# * center_vertically
# * {margins=}[#method-i-margin-3D]
# * set_header
# * set_footer
# * repeat_rows
# * repeat_columns
# * hide_gridlines
# * print_row_col_headers
# * print_area
# * print_across
# * fit_to_pages
# * {start_page=}[#method-i-start_page-3D]
# * {print_scale=}[#method-i-print_scale-3D]
# * set_h_pagebreaks
# * set_v_pagebreaks
#
# A common requirement when working with WriteXLSX is to apply the same
# page set-up features to all of the worksheets in a workbook. To do this
# you can use the sheets() method of the workbook class to access the array
# of worksheets in a workbook:
#
# workbook.sheets.each do |worksheet|
# worksheet.set_landscape
# end
#
# ==Cell notation
#
# WriteXLSX supports two forms of notation to designate the position of cells:
# Row-column notation and A1 notation.
#
# Row-column notation uses a zero based index for both row and column
# while A1 notation uses the standard Excel alphanumeric sequence of column
# letter and 1-based row. For example:
#
# (0, 0) # The top left cell in row-column notation.
# ('A1') # The top left cell in A1 notation.
#
# (1999, 29) # Row-column notation.
# ('AD2000') # The same cell in A1 notation.
#
# Row-column notation is useful if you are referring to cells
# programmatically:
#
# (0..9).each do |i|
# worksheet.write(i, 0, 'Hello') # Cells A1 to A10
# end
#
# A1 notation is useful for setting up a worksheet manually and
# for working with formulas:
#
# worksheet.write('H1', 200)
# worksheet.write('H2', '=H1+1')
#
# In formulas and applicable methods you can also use the A:A column notation:
#
# worksheet.write('A1', '=SUM(B:B)')
#
# The Writexlsx::Utility module that is included in the distro contains
# helper functions for dealing with A1 notation, for example:
#
# include Writexlsx::Utility
#
# row, col = xl_cell_to_rowcol('C2') # (1, 2)
# str = xl_rowcol_to_cell(1, 2) # C2
#
# For simplicity, the parameter lists for the worksheet method calls in the
# following sections are given in terms of row-column notation. In all cases
# it is also possible to use A1 notation.
#
# Note: in Excel it is also possible to use a R1C1 notation. This is not
# supported by WriteXLSX.
#
# == FORMULAS AND FUNCTIONS IN EXCEL
#
# === Introduction
#
# The following is a brief introduction to formulas and functions in Excel
# and WriteXLSX.
#
# 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 +XFD+ i.e. 0 to 16384, rows range from 1 to 1048576.
# The Writexlsx::Utility module that is included in the distro contains
# helper functions for dealing with A1 notation, for example:
#
# require 'write_xlsx'
#
# include Writexlsx::Utility
#
# row, col = xl_cell_to_rowcol('C2') # (1, 2)
# str = xl_rowcol_to_cell(1, 2) # C2
#
# 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 or
# 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. 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 Ruby'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)
# % Percent (Not modulus) 13%
#
#
# 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!" # [1]
#
#
# Reference operators:
# ====================
# Operator Meaning Example
# : Range operator A1:A4 # [2]
# , Union operator SUM(1, 2+2, B3) # [3]
#
#
# Notes:
# [1]: Equivalent to "Hello " + "World!" in Ruby.
# [2]: This range is equivalent to cells A1, A2, A3 and A4.
# [3]: 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 may be supported in a later version of WriteXLSX.
# In the meantime European users of Excel take note:
#
# worksheet.write('A1', '=SUM(1; 2; 3)') # Wrong!!
# worksheet.write('A1', '=SUM(1, 2, 3)') # Okay
#
# 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 Excel::Writer::XLSX try the following:
#
# 1. Verify that the formula works in Excel.
# 2. Ensure that cell references and formula names are in uppercase.
# 3. Ensure that you are using ':' as the range operator, A1:A4.
# 4. Ensure that you are using ',' as the union operator, SUM(1,2,3).
# 5. If you verify that the formula works in Gnumeric, OpenOffice.org
# or LibreOffice, make sure to note items 2-4 above, since these
# applications are more flexible than Excel with formula syntax.
#
class Worksheet
include Writexlsx::Utility
MAX_DIGIT_WIDTH = 7 # For Calabri 11. # :nodoc:
PADDING = 5 # :nodoc:
attr_reader :index # :nodoc:
attr_reader :charts, :images, :tables, :shapes, :drawing # :nodoc:
attr_reader :vml_data_id # :nodoc:
attr_reader :autofilter_area # :nodoc:
attr_reader :writer, :set_rows, :col_formats # :nodoc:
attr_reader :vml_shape_id # :nodoc:
attr_reader :comments, :comments_author # :nodoc:
attr_accessor :dxf_priority # :nodoc:
attr_reader :vba_codename # :nodoc:
def initialize(workbook, index, name) #:nodoc:
@writer = Package::XMLWriterSimple.new
@workbook = workbook
@index = index
@name = name
@colinfo = []
@cell_data_table = {}
@excel_version = 2007
@page_setup = PageSetup.new
@screen_gridlines = true
@show_zeros = true
@dim_rowmin = nil
@dim_rowmax = nil
@dim_colmin = nil
@dim_colmax = nil
@selections = []
@panes = []
@tab_color = 0
@set_cols = {}
@set_rows = {}
@zoom = 100
@zoom_scale_normal = true
@right_to_left = false
@autofilter_area = nil
@filter_on = false
@filter_range = []
@filter_cols = {}
@filter_type = {}
@col_sizes = {}
@row_sizes = {}
@col_formats = {}
@last_shape_id = 1
@rel_count = 0
@hlink_count = 0
@hlink_refs = []
@external_hyper_links = []
@external_drawing_links = []
@external_comment_links = []
@external_vml_links = []
@external_table_links = []
@drawing_links = []
@charts = []
@images = []
@tables = []
@sparklines = []
@shapes = []
@shape_hash = {}
@outline_row_level = 0
@outline_col_level = 0
@default_row_height = 15
@default_row_rezoed = 0
@merge = []
@has_vml = false
@comments = Package::Comments.new(self)
@buttons_array = []
@validations = []
@cond_formats = {}
@dxf_priority = 1
end
def set_xml_writer(filename) #:nodoc:
@writer.set_xml_writer(filename)
end
def assemble_xml_file #:nodoc:
@writer.xml_decl
@writer.tag_elements('worksheet', write_worksheet_attributes) do
write_sheet_pr
write_dimension
write_sheet_views
write_sheet_format_pr
write_cols
write_sheet_data
write_sheet_protection
write_auto_filter
write_merge_cells
write_conditional_formats
write_data_validations
write_hyperlinks
write_print_options
write_page_margins
write_page_setup
write_header_footer
write_row_breaks
write_col_breaks
write_drawings
write_legacy_drawing
write_table_parts
write_ext_sparklines
end
@writer.crlf
@writer.close
end
#
# The name method is used to retrieve the name of a worksheet.
# For example:
#
# workbook.sheets.each do |sheet|
# print sheet.name
# end
#
# For reasons related to the design of WriteXLSX and to the internals
# of Excel there is no set_name() method. The only way to set the
# worksheet name is via the Workbook#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 = false # Selected worksheet can't be hidden.
@selected = true
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, however only one
# worksheet can be active.
#
# The default active worksheet is the first worksheet.
#
def activate
@hidden = false
@selected = true
@workbook.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 = true
@selected = false
@workbook.activesheet = 0
@workbook.firstsheet = 0
end
def hidden? # :nodoc:
@hidden
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 = false
@workbook.firstsheet = @index
end
#
# Set the worksheet protection flags to prevent modification of worksheet
# objects.
#
# The protect() method is used to protect a worksheet from modification:
#
# worksheet.protect
#
# 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 and this property is on by default for all cells. A hidden
# cell will display the results of a formula but not the formula itself.
#
# See the protection.rb program in the examples directory of the distro
# for an illustrative example and the +set_locked+ and +set_hidden+ format
# methods in "CELL FORMATTING", see Format.
#
# You can optionally add a password to the worksheet protection:
#
# worksheet.protect('drowssap')
#
# Passing the empty string '' is the same as turning on protection
# without a password.
#
# Note, the worksheet level password in Excel provides very weak
# protection. It does not encrypt your data and is very easy to
# deactivate. Full workbook encryption is not supported by WriteXLSX
# since it requires a completely different file format and would take
# several man months to implement.
#
# You can specify which worksheet elements that you which to protect
# by passing a hash with any or all of the following keys:
#
# # Default shown.
# options = {
# :objects => false,
# :scenarios => false,
# :format_cells => false,
# :format_columns => false,
# :format_rows => false,
# :insert_columns => false,
# :insert_rows => false,
# :insert_hyperlinks => false,
# :delete_columns => false,
# :delete_rows => false,
# :select_locked_cells => true,
# :sort => false,
# :autofilter => false,
# :pivot_tables => false,
# :select_unlocked_cells => true
# }
#
# The default boolean values are shown above. Individual elements
# can be protected as follows:
#
# worksheet.protect('drowssap', { :insert_rows => true } )
#
def protect(password = nil, options = {})
check_parameter(options, protect_default_settings.keys, 'protect')
@protect = protect_default_settings.merge(options)
# Set the password after the user defined values.
@protect[:password] =
sprintf("%X", encode_password(password)) if password && password != ''
end
def protect_default_settings # :nodoc:
{
:sheet => true,
:content => false,
:objects => false,
:scenarios => false,
:format_cells => false,
:format_columns => false,
:format_rows => false,
:insert_columns => false,
:insert_rows => false,
:insert_hyperlinks => false,
:delete_columns => false,
:delete_rows => false,
:select_locked_cells => true,
:sort => false,
:autofilter => false,
:pivot_tables => false,
:select_unlocked_cells => true
}
end
private :protect_default_settings
#
# :call-seq:
# set_column(firstcol, lastcol, width, format, hidden, level, collapsed)
#
# This method can be used to change the default properties of a single
# column or a range of columns. All parameters apart from +first_col+
# and +last_col+ are optional.
#
# If set_column() is applied to a single column the value of +first_col+
# and +last_col+ should be the same. In the case where +last_col+ is zero
# it is set to the same value as +first_col+.
#
# It is also possible, and generally clearer, to specify a column range
# using the form of A1 notation used for columns. See the note about
# {"Cell notation"}[#label-Cell+notation].
#
# 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"}[Format.html#label-CELL+FORMATTING].
# If you wish to set the format without changing the width you can pass
# nil as the width parameter:
#
# worksheet.set_column(0, 0, nil, format)
#
# The format parameter will be applied to any cells in the column that
# don't have a format. For example
#
# worksheet.set_column('A:A', nil, format1) # Set format for col 1
# worksheet.write('A1', 'Hello') # Defaults to format1
# worksheet.write('A2', 'Hello', format2) # Keeps format2
#
# If you wish to define a column format in this way you should call the
# method before any calls to {#write()}[#method-i-write].
# If you call it afterwards it won't have any effect.
#
# A default row format takes precedence over a default column format
#
# worksheet.set_row( 0, nil, format1 ) # Set format for row 1
# worksheet.set_column( 'A:A', nil, format2 ) # Set format for col 1
# worksheet.write( 'A1', 'Hello' ) # Defaults to format1
# worksheet.write( 'A2', 'Hello' ) # Defaults to format2
#
# The +hidden+ parameter should be set to 1 if you wish to hide a column.
# 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"}["method-i-set_row-label-OUTLINES+AND+GROUPING+IN+EXCEL"].
# Adjacent columns with the same outline level are grouped together into
# a single outline.
#
# The following example sets an outline level of 1 for columns B to G:
#
# worksheet.set_column( 'B:G', nil, nil, 0, 1 )
#
# The +hidden+ parameter can also be used to hide collapsed outlined
# columns when used in conjunction with the +level+ parameter.
#
# 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.rb and outline_collapsed.rb
# programs in the examples directory of the distro.
#
# Excel allows up to 7 outline levels. Therefore the level parameter
# should be in the range 0 <= level <= 7.
#
def set_column(*args)
# Check for a cell reference in A1 notation and substitute row and column
if args[0] =~ /^\D/
row1, firstcol, row2, lastcol, *data = substitute_cellref(*args)
else
firstcol, lastcol, *data = args
end
# Ensure at least firstcol, lastcol and width
return unless firstcol && lastcol && !data.empty?
# Assume second column is the same as first if 0. Avoids KB918419 bug.
lastcol = firstcol unless ptrue?(lastcol)
# Ensure 2nd col is larger than first. Also for KB918419 bug.
firstcol, lastcol = lastcol, firstcol if firstcol > lastcol
width, format, hidden, level, collapsed = data
# Check that cols are valid and store max and min values with default row.
# NOTE: The check shouldn't modify the row dimensions and should only modify
# the column dimensions in certain cases.
ignore_row = 1
ignore_col = 1
ignore_col = 0 if format.respond_to?(:xf_index) # Column has a format.
ignore_col = 0 if width && ptrue?(hidden) # Column has a width but is hidden
check_dimensions_and_update_max_min_values(0, firstcol, ignore_row, ignore_col)
check_dimensions_and_update_max_min_values(0, lastcol, ignore_row, ignore_col)
# Set the limits for the outline levels (0 <= x <= 7).
level ||= 0
level = 0 if level < 0
level = 7 if level > 7
@outline_col_level = level if level > @outline_col_level
# Store the column data.
@colinfo << [firstcol, lastcol, width, format, hidden, level, collapsed]
# Store the column change to allow optimisations.
@col_size_changed = 1
# Store the col sizes for use when calculating image vertices taking
# hidden columns into account. Also store the column formats.
width ||= 0 # Ensure width isn't nil.
width = 0 if ptrue?(hidden) # Set width to zero if col is hidden
(firstcol .. lastcol).each do |col|
@col_sizes[col] = width
@col_formats[col] = format if format
end
end
#
# :call-seq:
# set_selection(cell_or_cell_range)
#
# Set which cell or cells are selected in a worksheet.
#
# This method can be used to specify which cell or cells are selected
# in a worksheet. The most common requirement is to select a single cell,
# in which case +last_row+ and +last_col+ can be omitted. The active cell
# within a selected range is determined by the order in which +first+ and
# +last+ are specified. It is also possible to specify a cell or a range
# using A1 notation. See the note about
# {"Cell notation"}[#label-Cell+notation].
#
# 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)
return if args.empty?
row_first, col_first, row_last, col_last = row_col_notation(args)
active_cell = xl_rowcol_to_cell(row_first, col_first)
if row_last.nil? # Single cell selection.
sqref = active_cell
else # Range selection.
# Swap last row/col for first row/col as necessary
row_first, row_last = row_last, row_first if row_first > row_last
col_first, col_last = col_last, col_first if col_first > col_last
# If the first and last cell are the same write a single cell.
if row_first == row_last && col_first == col_last
sqref = active_cell
else
sqref = xl_range(row_first, row_last, col_first, col_last)
end
end
# Selection isn't set for cell A1.
return if sqref == 'A1'
@selections = [ [ nil, active_cell, sqref ] ]
end
#
# :call-seq:
# freeze_panes(row, col [ , top_row, left_col ] )
#
# This method can be used to divide a worksheet into horizontal or
# vertical regions known as panes and to also "freeze" these panes so
# that the splitter bars are not visible. This is the same as the
# Window->Freeze Panes menu command in Excel
#
# 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)
return if args.empty?
# Check for a cell reference in A1 notation and substitute row and column.
row, col, top_row, left_col, type = row_col_notation(args)
col ||= 0
top_row ||= row
left_col ||= col
type ||= 0
@panes = [row, col, top_row, left_col, type ]
end
#
# :call-seq:
# split_panes(y, x, top_row, left_col)
#
# Set panes and mark them as split.
#--
# Implementers note. The API for this method doesn't map well from the XLS
# file format and isn't sufficient to describe all cases of split panes.
# It should probably be something like:
#
# split_panes(y, x, top_row, left_col, offset_row, offset_col)
#
# I'll look at changing this if it becomes an issue.
#++
# 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 15
# for a row and 8.43 for a column.
#
# You can set one of the +y+ and +x+ parameters as zero if you do not want
# either a vertical or horizontal split. The parameters +top_row+ and
# +left_col+ are optional. They are used to specify the top-most or
# left-most visible row or column in the bottom-right pane.
#
# Example:
#
# worksheet.split_panes(15, 0 ) # First row
# worksheet.split_panes( 0, 8.43) # First column
# worksheet.split_panes(15, 8.43) # First row and column
#
# You cannot use A1 notation with this method.
#
# See also the freeze_panes() method and the panes.rb program in the
# examples directory of the distribution.
#
def split_panes(*args)
# Call freeze panes but add the type flag for split panes.
freeze_panes(args[0], args[1], args[2], args[3], 2)
end
#
# Set the page orientation as portrait.
# The default worksheet orientation is portrait, so you won't generally
# need to call this method.
#
def set_portrait
@page_setup.orientation = true
@page_setup.page_setup_changed = true
end
#
# Set the page orientation as landscape.
#
def set_landscape
@page_setup.orientation = false
@page_setup.page_setup_changed = true
end
#
# This method is used to display the worksheet in "Page View/Layout" mode.
#
def set_page_view(flag = true)
@page_view = !!flag
end
#
# Set the colour of the worksheet tab.
#
# The 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.tab_color = 'red'
# worksheet2.tab_color = 0x0C
#
# See the tab_colors.rb program in the examples directory of the distro.
#
def tab_color=(color)
@tab_color = Colors.new.get_color(color)
end
# This method is deprecated. use tab_color=().
def set_tab_color(color)
put_deprecate_message("#{self}.set_tab_color")
self.tab_color = color
end
#
# 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 paper=(paper_size)
@page_setup.paper = paper_size
end
def set_paper(paper_size)
put_deprecate_message("#{self}.set_paper")
self::paper = paper_size
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.3 inch. Note, the default margin is different from the
# default used in the binary file format by Spreadsheet::WriteExcel.
# 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.
#
# See, also the headers.rb program in the examples directory of the
# distribution.
#
def set_header(string = '', margin = 0.3)
raise 'Header string must be less than 255 characters' if string.length >= 255
@page_setup.header = string
@page_setup.margin_header = margin
@page_setup.header_footer_changed = true
end
#
# Set the page footer caption and optional margin.
#
# The syntax of the set_footer() method is the same as set_header()
#
def set_footer(string = '', margin = 0.3)
raise 'Footer string must be less than 255 characters' if string.length >= 255
@page_setup.footer = string
@page_setup.margin_footer = margin
@page_setup.header_footer_changed = true
end
#
# Center the worksheet data horizontally between the margins on the printed page:
#
def center_horizontally
@page_setup.center_horizontally
end
#
# Center the worksheet data vertically between the margins on the printed page:
#
def center_vertically
@page_setup.center_vertically
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:
#
# margins=() # Set all margins to the same value
# margins_left_right=() # Set left and right margins to the same value
# margins_top_bottom=() # Set top and bottom margins to the same value
# margin_left=() # Set left margin
# margin_right=() # Set right margin
# margin_top=() # Set top margin
# 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.7 inch.
# The default top and bottom margin is 0.75 inch. Note, these defaults
# are different from the defaults used in the binary file format
# by writeexcel gem.
#
def margins=(margin)
self::margin_left = margin
self::margin_right = margin
self::margin_top = margin
self::margin_bottom = margin
end
#
# Set the left and right margins to the same value in inches.
# See set_margins
#
def margins_left_right=(margin)
self::margin_left = margin
self::margin_right = margin
end
#
# Set the top and bottom margins to the same value in inches.
# See set_margins
#
def margins_top_bottom=(margin)
self::margin_top = margin
self::margin_bottom = margin
end
#
# Set the left margin in inches.
# See margins=()
#
def margin_left=(margin)
@page_setup.margin_left = remove_white_space(margin)
end
#
# Set the right margin in inches.
# See margins=()
#
def margin_right=(margin)
@page_setup.margin_right = remove_white_space(margin)
end
#
# Set the top margin in inches.
# See margins=()
#
def margin_top=(margin)
@page_setup.margin_top = remove_white_space(margin)
end
#
# Set the bottom margin in inches.
# See margins=()
#
def margin_bottom=(margin)
@page_setup.margin_bottom = remove_white_space(margin)
end
#
# set_margin_* methods are deprecated. use margin_*=().
#
# 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.7 inch.
# The default top and bottom margin is 0.75 inch. Note, these defaults
# are different from the defaults used in the binary file format
# by writeexcel gem.
#
def set_margins(margin)
put_deprecate_message("#{self}.set_margins")
self::margin = margin
end
#
# this method is deprecated. use margin_left_right=().
# Set the left and right margins to the same value in inches.
# See set_margins
#
def set_margins_LR(margin)
put_deprecate_message("#{self}.set_margins_LR")
self::margins_left_right = margin
end
#
# this method is deprecated. use margin_top_bottom=().
# Set the top and bottom margins to the same value in inches.
# See set_margins
#
def set_margins_TB(margin)
put_deprecate_message("#{self}.set_margins_TB")
self::margins_top_bottom = margin
end
#
# this method is deprecated. use margin_left=()
# Set the left margin in inches.
# See set_margins
#
def set_margin_left(margin = 0.7)
put_deprecate_message("#{self}.set_margin_left")
self::margin_left = margin
end
#
# this method is deprecated. use margin_right=()
# Set the right margin in inches.
# See set_margins
#
def set_margin_right(margin = 0.7)
put_deprecate_message("#{self}.set_margin_right")
self::margin_right = margin
end
#
# this method is deprecated. use margin_top=()
# Set the top margin in inches.
# See set_margins
#
def set_margin_top(margin = 0.75)
put_deprecate_message("#{self}.set_margin_top")
self::margin_top = margin
end
#
# this method is deprecated. use margin_bottom=()
# Set the bottom margin in inches.
# See set_margins
#
def set_margin_bottom(margin = 0.75)
put_deprecate_message("#{self}.set_margin_bottom")
self::margin_bottom = margin
end
#
# 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(row_min, row_max = nil)
row_max ||= row_min
# Convert to 1 based.
row_min += 1
row_max += 1
area = "$#{row_min}:$#{row_max}"
# Build up the print titles "Sheet1!$1:$2"
sheetname = quote_sheetname(name)
@page_setup.repeat_rows = "#{sheetname}!#{area}"
end
def print_repeat_rows # :nodoc:
@page_setup.repeat_rows
end
#
# :call-seq:
# repeat_columns(first_col, last_col = nil)
#
# Set the columns to repeat at the left hand side of each printed page.
#
# 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"}[#label-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)
if args[0] =~ /^\D/
dummy, first_col, dummy, last_col = substitute_cellref(*args)
else
first_col, last_col = args
end
last_col ||= first_col
area = "#{xl_col_to_name(first_col, 1)}:#{xl_col_to_name(last_col, 1)}"
@page_setup.repeat_cols = "#{quote_sheetname(@name)}!#{area}"
end
def print_repeat_cols # :nodoc:
@page_setup.repeat_cols
end
#
# :call-seq:
# print_area(first_row, first_col, last_row, last_col)
#
# 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"}[#label-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)
return @page_setup.print_area.dup if args.empty?
row1, col1, row2, col2 = row_col_notation(args)
return if [row1, col1, row2, col2].include?(nil)
# Ignore max print area since this is the same as no print area for Excel.
if row1 == 0 && col1 == 0 && row2 == ROW_MAX - 1 && col2 == COL_MAX - 1
return
end
# Build up the print area range "=Sheet2!R1C1:R2C1"
@page_setup.print_area = convert_name_area(row1, col1, row2, col2)
end
#
# Set the worksheet zoom factor in the range 10 <= scale <= 400:
#
# worksheet1.zoom = 50
# worksheet2.zoom = 75
# worksheet3.zoom = 300
# worksheet4.zoom = 400
#
# The default zoom factor is 100. You cannot zoom to "Selection" because
# it is calculated by Excel at run-time.
#
# Note, zoom=() does not affect the scale of the printed page.
# For that you should use print_scale=().
#
def zoom=(scale)
# Confine the scale to Excel's range
if scale < 10 or scale > 400
# carp "Zoom factor scale outside range: 10 <= zoom <= 400"
@zoom = 100
else
@zoom = scale.to_i
end
end
# This method is deprecated. use zoom=().
def set_zoom(scale)
put_deprecate_message("#{self}.set_zoom")
self.zoom = scale
end
#
# Set the scale factor of the printed page.
# Scale factors in the range 10 <= scale <= 400 are valid:
#
# worksheet1.print_scale = 50
# worksheet2.print_scale = 75
# worksheet3.print_scale = 300
# worksheet4.print_scale = 400
#
# The default scale factor is 100. Note, print_scale=() does not
# affect the scale of the visible page in Excel. For that you should
# use zoom=().
#
# Note also that although it is valid to use both fit_to_pages() and
# 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 print_scale=(scale = 100)
scale_val = scale.to_i
# Confine the scale to Excel's range
scale_val = 100 if scale_val < 10 || scale_val > 400
# Turn off "fit to page" option.
@page_setup.fit_page = false
@page_setup.scale = scale_val
@page_setup.page_setup_changed = true
end
#
# This method is deprecated. use print_scale=().
#
def set_print_scale(scale = 100)
put_deprecate_message("#{self}.set_print_scale")
self::print_scale = (scale)
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(flag = true)
@right_to_left = !!flag
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(flag = true)
@show_zeros = !flag
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(across = true)
if across
@page_setup.across = true
@page_setup.page_setup_changed = true
else
@page_setup.across = false
end
end
#
# The 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 start_page=(page_start)
@page_setup.page_start = page_start
@custom_start = 1
end
def set_start_page(page_start)
put_deprecate_message("#{self}.set_start_page")
self::start_page = page_start
end
#
# :call-seq:
# write(row, column [ , token [ , format ] ])
#
# Excel makes a distinction between data types such as strings, numbers,
# blanks, formulas and hyperlinks. To simplify the process of writing
# data the {#write()}[#method-i-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.com/') # write_url()
# worksheet.write('A9', 'ftp://ftp.ruby.org/' ) # write_url()
# worksheet.write('A10', 'internal:Sheet1!A1' ) # write_url()
# worksheet.write('A11', 'external:c:\foo.xlsx') # write_url()
# worksheet.write('A12', '=A3 + 3*A4' ) # write_formula()
# worksheet.write('A13', '=SIN(PI()/4)' ) # write_formula()
# worksheet.write('A14', [1, 2] ) # write_row()
# worksheet.write('A15', [ [1, 2] ] ) # write_col()
#
# # Write an array formula. Not available in writeexcel gem.
# worksheet.write('A16', '{=SUM(A1:B1*A2:B2)}' ) # write_formula()
#
# The +format+ parameter is optional. It should be a valid Format object,
# See {"CELL FORMATTING"}[Format.html#label-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-i-write] method will ignore empty strings or +nil+ tokens unless a
# format is also supplied. As such you needn't worry about special handling
# for empty or nil in your data. See also the write_blank() method.
#
# One problem with the {#write()}[#method-i-write] method is that occasionally data looks like
# a number but you don't want it treated as a number. For example, zip
# codes or ID numbers often start with a leading zero.
# If you want to write this data with leading zero(s), use write_string.
#
# The write methods return:
# 0 for success.
#
def write(*args)
# Check for a cell reference in A1 notation and substitute row and column
token = row_col_notation(args)[2] || ''
# Match an array ref.
if token.respond_to?(:to_ary)
write_row(*args)
elsif token.respond_to?(:coerce) # Numeric
write_number(*args)
elsif token =~ /^\d+$/
write_number(*args)
# Match http, https or ftp URL
elsif token =~ %r|^[fh]tt?ps?://|
write_url(*args)
# Match mailto:
elsif token =~ %r|^mailto:|
write_url(*args)
# Match internal or external sheet link
elsif token =~ %r!^(?:in|ex)ternal:!
write_url(*args)
# Match formula
elsif token =~ /^=/
write_formula(*args)
# Match array formula
elsif token =~ /^\{=.*\}$/
write_formula(*args)
# Match blank
elsif token == ''
args.delete_at(2) # remove the empty string from the parameter list
write_blank(*args)
else
write_string(*args)
end
end
#
# :call-seq:
# write_row(row, col, 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.
#
# 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-i-write] method is then
# called for each element of the data. For example:
#
# array = ['awk', 'gawk', 'mawk']
#
# worksheet.write_row(0, 0, array)
#
# # 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-i-write] method behaves in the same way as
# write_row() if it is passed an array.
# Therefore the following two method calls are equivalent:
#
# worksheet.write_row('A1', array) # Write a row of data
# worksheet.write( 'A1', array) # Same thing
#
# 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+ 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.
#
# 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
row, col, tokens, *options = row_col_notation(args)
raise "Not an array ref in call to write_row()$!" unless tokens.respond_to?(:to_ary)
tokens.each do |token|
# Check for nested arrays
if token.respond_to?(:to_ary)
write_col(row, col, token, *options)
else
write(row, col, token, *options)
end
col += 1
end
end
#
# :call-seq:
# write_col(row, col, 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.
#
# 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. 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_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 references 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+ 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-i-write] method can be used as a synonym for
# write_row() and write_row() handles nested array refs as columns.
# Therefore, the following two method calls are equivalent although
# the more explicit call to write_col() would be preferable for
# maintainability:
#
# worksheet.write_col('A1', array ) # Write a column of data
# worksheet.write( 'A1', [ array ] ) # Same thing
#
# See also the write_arrays.rb program in the examples directory of
# the distro.
#
def write_col(*args)
row, col, tokens, *options = row_col_notation(args)
tokens.each do |token|
# write() will deal with any nested arrays
write(row, col, token, *options)
row += 1
end
end
#
# :call-seq:
# write_comment(row, column, string, options = {})
#
# Write a comment to the specified row and column (zero indexed).
#
# 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"}[#label-Cell+notation].
#
# worksheet.write( 'C3', 'Hello')
# worksheet.write_comment('C3', 'This is a comment.')
#
# The write_comment() method will also handle strings in UTF-8 format.
#
# worksheet.write_comment('C3', "日本")
# worksheet.write_comment('C4', 'Comment ça 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 => 'Perl')
#
# 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:
#
# :author
# :visible
# :x_scale
# :width
# :y_scale
# :height
# :color
# :start_cell
# :start_row
# :start_col
# :x_offset
# :y_offset
#
# ===Option: author
#
# This option is used to indicate who is the author of the cell
# comment. 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')
#
# The default author for all cell comments can be set using the
# comments_author=() method.
#
# worksheet.comments_author = 'Ruby'
#
# ===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 WriteXLSX 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. 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 WriteXLSX
# 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 using options that adjust the position of the cell comment
# such as start_cell, start_row, start_col, x_offset and y_offset:
# Excel only displays offset cell comments when they are displayed as
# "visible". Excel does not display hidden cells as moved when you
# mouse over them.
#
# Note about row height and comments. If you specify the height of a
# row that contains a comment then WriteXLSX 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 the module 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
row, col, string, options = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col, string].include?(nil)
# Check that row and col are valid and store max and min values
check_dimensions(row, col)
store_row_col_max_min_values(row, col)
@has_vml = true
# Process the properties of the cell comment.
@comments.add(Package::Comment.new(@workbook, self, row, col, string, options))
end
#
# :call-seq:
# write_number(row, column, number [ , format ] )
#
# 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"}[#label-Cell+notation].
# The +format+ parameter is optional.
#
# In general it is sufficient to use the {#write()}[#method-i-write] method.
#
# Note: some versions of Excel 2007 do not display the calculated values
# of formulas written by WriteXLSX. Applying all available Service Packs
# to Excel should fix this.
#
def write_number(*args)
# Check for a cell reference in A1 notation and substitute row and column
row, col, num, xf = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col, num].include?(nil)
# Check that row and col are valid and store max and min values
check_dimensions(row, col)
store_row_col_max_min_values(row, col)
store_data_to_table(NumberCellData.new(self, row, col, num, xf))
end
#
# :call-seq:
# write_string(row, column, string [, format ] )
#
# Write a string to the specified row and column (zero indexed).
# +format+ is optional.
#
# 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.
#
# In general it is sufficient to use the {#write()}[#method-i-write] method.
# However, you may sometimes wish to use the write_string() method
# to write data that looks like a number but that you don't want
# treated as a number. For example, zip codes or phone numbers:
#
# # Write as a plain string
# 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
row, col, str, xf = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col, str].include?(nil)
# Check that row and col are valid and store max and min values
check_dimensions(row, col)
store_row_col_max_min_values(row, col)
index = shared_string_index(str[0, STR_MAX])
store_data_to_table(StringCellData.new(self, row, col, index, xf))
end
#
# :call-seq:
# write_rich_string(row, column, (string | format, string)+, [,cell_format] )
#
# The write_rich_string() method is used to write strings with multiple formats.
# The method receives string fragments prefixed by format objects. The final
# format object is used as the cell format.
#
# For example to write the string "This is *bold* and this is _italic_"
# you would use the following:
#
# bold = workbook.add_format(:bold => 1)
# italic = workbook.add_format(:italic => 1)
#
# worksheet.write_rich_string('A1',
# 'This is ', bold, 'bold', ' and this is ', italic, 'italic')
#
# The basic rule is to break the string into fragments and put a +format+
# object before the fragment that you want to format. For example:
#
# # Unformatted string.
# 'This is an example string'
#
# # Break it into fragments.
# 'This is an ', 'example', ' string'
#
# # Add formatting before the fragments you want formatted.
# 'This is an ', format, 'example', ' string'
#
# # In WriteXLSX.
# worksheet.write_rich_string('A1',
# 'This is an ', format, 'example', ' string')
#
# String fragments that don't have a format are given a default format.
# So for example when writing the string "Some *bold* text"
# you would use the first example below but it would be equivalent
# to the second:
#
# # With default formatting:
# bold = workbook.add_format(:bold => 1)
#
# worksheet.write_rich_string('A1',
# 'Some ', bold, 'bold', ' text')
#
# # Or more explicitly:
# bold = workbook.add_format(:bold => 1)
# default = workbook.add_format
#
# worksheet.write_rich_string('A1',
# default, 'Some ', bold, 'bold', default, ' text')
#
# As with Excel, only the font properties of the format such as font
# name, style, size, underline, color and effects are applied to the
# string fragments. Other features such as border, background and
# alignment must be applied to the cell.
#
# The write_rich_string() method allows you to do this by using the
# last argument as a cell format (if it is a format object).
# The following example centers a rich string in the cell:
#
# bold = workbook.add_format(:bold => 1)
# center = workbook.add_format(:align => 'center')
#
# worksheet.write_rich_string('A5',
# 'Some ', bold, 'bold text', ' centered', center)
#
# See the rich_strings.rb example in the distro for more examples.
#
# bold = workbook.add_format(:bold => 1)
# italic = workbook.add_format(:italic => 1)
# red = workbook.add_format(:color => 'red')
# blue = workbook.add_format(:color => 'blue')
# center = workbook.add_format(:align => 'center')
# super = workbook.add_format(:font_script => 1)
#
# # Write some strings with multiple formats.
# worksheet.write_rich_string('A1',
# 'This is ', bold, 'bold', ' and this is ', italic, 'italic')
#
# worksheet.write_rich_string('A3',
# 'This is ', red, 'red', ' and this is ', blue, 'blue')
#
# worksheet.write_rich_string('A5',
# 'Some ', bold, 'bold text', ' centered', center)
#
# worksheet.write_rich_string('A7',
# italic, 'j = k', super, '(n-1)', center)
#
# "http://jmcnamara.github.com/excel-writer-xlsx/images/examples/rich_strings.jpg"
#
# As with write_sting() the maximum string size is 32767 characters.
# See also the note about {"Cell notation"}[#label-Cell+notation].
#
def write_rich_string(*args)
# Check for a cell reference in A1 notation and substitute row and column
row, col, *rich_strings = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col, rich_strings[0]].include?(nil)
xf = cell_format_of_rich_string(rich_strings)
# Check that row and col are valid and store max and min values
check_dimensions(row, col)
store_row_col_max_min_values(row, col)
fragments, length = rich_strings_fragments(rich_strings)
# can't allow 2 formats in a row
return -4 unless fragments
index = shared_string_index(xml_str_of_rich_string(fragments))
store_data_to_table(StringCellData.new(self, row, col, index, xf))
end
#
# :call-seq:
# write_blank(row, col, format)
#
# 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.
#
# worksheet.write_blank(0, 0, format)
#
# This method is used to add formatting to cell which doesn't contain a
# string or number value.
#
# 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.
#
# 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 +nil+ or empty string values.
#
# See the note about {"Cell notation"}[#label-Cell+notation].
#
def write_blank(*args)
# Check for a cell reference in A1 notation and substitute row and column
row, col, xf = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col].include?(nil)
# Don't write a blank cell unless it has a format
return unless xf
# Check that row and col are valid and store max and min values
check_dimensions(row, col)
store_row_col_max_min_values(row, col)
store_data_to_table(BlankCellData.new(self, row, col, nil, xf))
end
#
# :call-seq:
# write_formula(row, column, formula [ , format [ , value ] ] )
#
# 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")')
#
# Array formulas are also supported:
#
# worksheet.write_formula('A7', '{=SUM(A1:B1*A2:B2)}')
#
# See also the write_array_formula() method.
#
# See the note about {"Cell notation"}[#label-Cell+notation].
# For more information about writing Excel formulas see
# {"FORMULAS AND FUNCTIONS IN EXCEL"}[#label-FORMULAS+AND+FUNCTIONS+IN+EXCEL]
#
# 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 calculate 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.
#
def write_formula(*args)
# Check for a cell reference in A1 notation and substitute row and column
row, col, formula, format, value = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col, formula].include?(nil)
if formula =~ /^\{=.*\}$/
write_array_formula(row, col, row, col, formula, format, value)
else
check_dimensions(row, col)
store_row_col_max_min_values(row, col)
formula = formula.sub(/^=/, '')
store_data_to_table(FormulaCellData.new(self, row, col, formula, format, value))
end
end
#
# :call-seq:
# write_array_formula(row1, col1, row2, col2, formula [ , format [ , value ] ] )
#
# Write an array formula to a cell range. In Excel an array formula is a
# formula that performs a calculation on a set of values. It can return
# a single value or a range of values.
#
# An array formula is indicated by a pair of braces around the
# formula: +{=SUM(A1:B1*A2:B2)}+. If the array formula returns a single
# value then the +first_+ and +last_+ parameters should be the same:
#
# worksheet.write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}')
#
# It this case however it is easier to just use the write_formula()
# or {#write()}[#method-i-write] methods:
#
# # Same as above but more concise.
# worksheet.write('A1', '{=SUM(B1:C1*B2:C2)}')
# worksheet.write_formula('A1', '{=SUM(B1:C1*B2:C2)}')
#
# For array formulas that return a range of values you must specify
# the range that the return values will be written to:
#
# worksheet.write_array_formula('A1:A3', '{=TREND(C1:C3,B1:B3)}')
# worksheet.write_array_formula(0, 0, 2, 0, '{=TREND(C1:C3,B1:B3)}')
#
# 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 calculate the value of the formula.
# The calculated value is added at the end of the argument list:
#
# worksheet.write_array_formula('A1:A3', '{=TREND(C1:C3,B1:B3)}', format, 105)
#
# In addition, some early versions of Excel 2007 don't calculate the
# values of array formulas when they aren't supplied. Installing the
# latest Office Service Pack should fix this issue.
#
# See also the array_formula.rb program in the examples directory of
# the distro.
#
# Note: Array formulas are not supported by writeexcel gem.
#
def write_array_formula(*args)
# Check for a cell reference in A1 notation and substitute row and column
row1, col1, row2, col2, formula, xf, value = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row1, col1, row2, col2, formula].include?(nil)
# Swap last row/col with first row/col as necessary
row1, row2 = row2, row1 if row1 > row2
col1, col2 = col2, col1 if col1 > col2
# Check that row and col are valid and store max and min values
check_dimensions(row2, col2)
store_row_col_max_min_values(row2, col2)
# Define array range
if row1 == row2 && col1 == col2
range = xl_rowcol_to_cell(row1, col1)
else
range ="#{xl_rowcol_to_cell(row1, col1)}:#{xl_rowcol_to_cell(row2, col2)}"
end
# Remove array formula braces and the leading =.
formula = formula.sub(/^\{(.*)\}$/, '\1').sub(/^=/, '')
store_data_to_table(FormulaArrayCellData.new(self, row1, col1, formula, xf, range, value))
# Pad out the rest of the area with formatted zeroes.
(row1..row2).each do |row|
(col1..col2).each do |col|
next if row == row1 && col == col1
write_number(row, col, 0, xf)
end
end
end
#
# The outline_settings() method is used to control the appearance of
# outlines in Excel. Outlines are described in
# {"OUTLINES AND GROUPING IN EXCEL"}["method-i-set_row-label-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 WriteXLSX but it
# does have an effect on how the worksheet behaves after it is created.
# The default setting is 0 for "Automatic Styles" to be turned off.
#
# 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(visible = 1, symbols_below = 1, symbols_right = 1, auto_style = 0)
@outline_on = visible
@outline_below = symbols_below
@outline_right = symbols_right
@outline_style = auto_style
@outline_changed = 1
end
#
# Deprecated. This is a writeexcel method that is no longer required
# by WriteXLSX. See below.
#
def store_formula(string)
string.split(/(\$?[A-I]?[A-Z]\$?\d+)/)
end
#
# :call-seq:
# write_url(row, column, url [ , format, label ] )
#
# Write a hyperlink to a URL in the cell specified by +row+ and +column+.
# The hyperlink is comprised of two elements: the visible label and
# the invisible link. The visible label is the same as the link unless
# an alternative label is specified. The label parameter is optional.
# The label is written using the {#write()}[#method-i-write] method. Therefore it is
# possible to write strings, numbers or formulas as labels.
#
# The +format+ parameter is also optional, however, without a format
# the link won't look like a format.
#
# The suggested format is:
#
# format = workbook.add_format(:color => 'blue', :underline => 1)
#
# Note, this behaviour is different from writeexcel gem which
# provides a default hyperlink format if one isn't specified
# by the user.
#
# There are four web style URI's supported:
# http://, https://, ftp:// and mailto::
#
# worksheet.write_url(0, 0, 'ftp://www.ruby-lang.org/', format)
# worksheet.write_url('A3', 'http://www.ruby-lang.org/', format)
# worksheet.write_url('A4', 'mailto:foo@bar.com', format)
#
# You can display an alternative string using the +label+ parameter:
#
# worksheet.write_url(1, 0, 'http://www.ruby-lang.org/', format, 'Ruby')
#
# If you wish to have some other cell data such as a number or a formula
# you can overwrite the cell using another call to write_*():
#
# worksheet.write_url('A1', 'http://www.ruby-lang.org/')
#
# # Overwrite the URL string with a formula. The cell is still a link.
# worksheet.write_formula('A1', '=1+1', format)
#
# 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', format)
# worksheet.write_url('A7', 'internal:Sheet2!A1', format)
# worksheet.write_url('A8', 'internal:Sheet2!A1:B2', format)
# worksheet.write_url('A9', %q{internal:'Sales Data'!A1}, format)
# worksheet.write_url('A10', 'external:c:\temp\foo.xlsx', format)
# worksheet.write_url('A11', 'external:c:\foo.xlsx#Sheet2!A1', format)
# worksheet.write_url('A12', 'external:..\foo.xlsx', format)
# worksheet.write_url('A13', 'external:..\foo.xlsx#Sheet2!A1', format)
# worksheet.write_url('A13', 'external:\\\\NET\share\foo.xlsx', format)
#
# All of the these URI types are recognised by the {#write()}[#method-i-write] method, see above.
#
# Worksheet references are typically of the form Sheet1!A1. You can
# also refer to a worksheet range using the standard Excel notation:
# +Sheet1!A1:B2+.
#
# In external links the workbook and worksheet name must be separated
# by the # character: +external:Workbook.xlsx#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.xlsx+ you could link to it as follows:
#
# worksheet.write_url('A14', 'external:c:\temp\foo.xlsx#my_name')
#
# Excel requires that worksheet names containing spaces or non
# alphanumeric characters are single quoted as follows +'Sales Data'!A1+.
#
# Note: WriteXLSX will escape the following characters in URLs as required
# by Excel: \s " < > \ [ ] ` ^ { } unless the URL already contains +%xx+
# style escapes. In which case it is assumed that the URL was escaped
# correctly by the user and will by passed directly to Excel.
#
# See also, the note about {"Cell notation"}[#label-Cell+notation].
#
def write_url(*args)
# Check for a cell reference in A1 notation and substitute row and column
row, col, url, xf, str, tip = row_col_notation(args)
xf, str = str, xf if str.respond_to?(:xf_index) || !xf.respond_to?(:xf_index)
raise WriteXLSXInsufficientArgumentError if [row, col, url].include?(nil)
# Check that row and col are valid and store max and min values
check_dimensions(row, col)
store_row_col_max_min_values(row, col)
hyperlink = Hyperlink.new(url, str)
hyperlink.tip = tip
@hlink_count += 1
if @hlink_count > 65_530
raise "URL '#{hyperlink.url}' added but number of URLS is over Excel's limit of 65,530 URLS per worksheet."
end
# Write the hyperlink string.
write_string(row, col, hyperlink.str, xf)
# Store the hyperlink data in a separate structure.
@hyperlinks ||= {}
@hyperlinks[row] ||= {}
@hyperlinks[row][col] = hyperlink
end
#
# :call-seq:
# write_date_time (row, col, 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.
#
# 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"}[#method-i-write_date_time-label-DATES+AND+TIME+IN+EXCEL]
# and {"CELL FORMATTING"}[#label-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.
#
#
# == DATES AND TIME IN EXCEL
#
# There are two important things to understand about dates and times in Excel:
#
# 1 A date/time in Excel is a real number plus an Excel number format.
# 2 WriteXLSX doesn't automatically convert date/time strings in {#write()}[#method-i-write] to an Excel date/time.
#
# These two points are explained in more detail below along with some
# suggestions on how to convert times and dates to the required format.
#
# === An Excel date/time is a number plus a format
#
# If you write a date string with {#write()}[#method-i-write] then all you will get is a string:
#
# worksheet.write('A1', '02/03/04') # !! Writes a string not a date. !!
#
# Dates and times in Excel are represented by real numbers, for example
# "Jan 1 2001 12:30 AM" is represented by the number 36892.521.
#
# The integer part of the number stores the number of days since the epoch
# and the fractional part stores the percentage of the day.
#
# A date or time in Excel is just like any other number. To have the number
# display as a date you must apply an Excel number format to it.
# Here are some examples.
#
# #!/usr/bin/ruby -w
#
# require 'write_xlsx'
#
# workbook = WriteXLSX.new('date_examples.xlsx')
# worksheet = workbook>add_worksheet
#
# worksheet.set_column('A:A', 30) # For extra visibility.
#
# number = 39506.5
#
# worksheet.write('A1', number) # 39506.5
#
# format2 = workbook.add_format(:num_format => 'dd/mm/yy')
# worksheet.write('A2', number, format2) # 28/02/08
#
# format3 = workbook.add_format(:num_format => 'mm/dd/yy')
# worksheet.write('A3', number, format3) # 02/28/08
#
# format4 = workbook.add_format(:num_format => 'd-m-yyyy')
# worksheet.write('A4', number, format4) # 28-2-2008
#
# format5 = workbook.add_format(:num_format => 'dd/mm/yy hh:mm')
# worksheet.write('A5', number, format5) # 28/02/08 12:00
#
# format6 = workbook.add_format(:num_format => 'd mmm yyyy')
# worksheet.write('A6', number, format6) # 28 Feb 2008
#
# format7 = workbook.add_format(:num_format => 'mmm d yyyy hh:mm AM/PM')
# worksheet.write('A7', number , format7) # Feb 28 2008 12:00 PM
#
# WriteXLSX doesn't automatically convert date/time strings
#
# WriteXLSX doesn't automatically convert input date strings into Excel's
# formatted date numbers due to the large number of possible date formats
# and also due to the possibility of misinterpretation.
#
# For example, does 02/03/04 mean March 2 2004, February 3 2004 or
# even March 4 2002.
#
# Therefore, in order to handle dates you will have to convert them to
# numbers and apply an Excel format. Some methods for converting dates are
# listed in the next section.
#
# The most direct way is to convert your dates to the ISO8601
# yyyy-mm-ddThh:mm:ss.sss date format and use the write_date_time()
# worksheet method:
#
# worksheet.write_date_time('A2', '2001-01-01T12:20', format)
#
# See the write_date_time() section of the documentation for more details.
#
# A general methodology for handling date strings with write_date_time() is:
#
# 1. Identify incoming date/time strings with a regex.
# 2. Extract the component parts of the date/time using the same regex.
# 3. Convert the date/time to the ISO8601 format.
# 4. Write the date/time using write_date_time() and a number format.
# For a slightly more advanced solution you can modify the {#write()}[#method-i-write] method
# to handle date formats of your choice via the add_write_handler() method.
# See the add_write_handler() section of the docs and the
# write_handler3.rb and write_handler4.rb programs in the examples
# directory of the distro.
#
# Converting dates and times to an Excel date or time
#
# The write_date_time() method above is just one way of handling dates and
# times.
#
# You can also use the convert_date_time() worksheet method to convert
# from an ISO8601 style date string to an Excel date and time number.
#
def write_date_time(*args)
# Check for a cell reference in A1 notation and substitute row and column
row, col, str, xf = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col, str].include?(nil)
# Check that row and col are valid and store max and min values
check_dimensions(row, col)
store_row_col_max_min_values(row, col)
date_time = convert_date_time(str)
if date_time
store_data_to_table(NumberCellData.new(self, row, col, date_time, xf))
else
# If the date isn't valid then write it as a string.
write_string(*args)
end
end
#
# :call-seq:
# insert_chart(row, column, chart [ , x, y, x_scale, y_scale ] )
#
# 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 => '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
# Writexlsx::Chart for details on how to configure it. See also the
# chart_*.rb programs in the examples directory of the distro.
#
# The +x+, +y+, +x_scale+ and +y_scale+ 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 +column+. The offset
# values are in pixels.
#
# worksheet1.insert_chart('E2', chart, 3, 3)
#
# The parameters x_scale and y_scale 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)
#
def insert_chart(*args)
# Check for a cell reference in A1 notation and substitute row and column.
row, col, chart, x_offset, y_offset, x_scale, y_scale = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col, chart].include?(nil)
x_offset ||= 0
y_offset ||= 0
x_scale ||= 1
y_scale ||= 1
raise "Not a Chart object in insert_chart()" unless chart.is_a?(Chart) || chart.is_a?(Chartsheet)
raise "Not a embedded style Chart object in insert_chart()" if chart.respond_to?(:embedded) && chart.embedded == 0
# Use the values set with chart.set_size, if any.
x_scale = chart.x_scale if chart.x_scale != 1
y_scale = chart.y_scale if chart.y_scale != 1
x_offset = chart.x_offset if ptrue?(chart.x_offset)
y_offset = chart.y_offset if ptrue?(chart.y_offset)
@charts << [row, col, chart, x_offset, y_offset, x_scale, y_scale]
end
#
# :call-seq:
# insert_image(row, column, filename, x=0, y=0, x_scale=1, y_scale=1)
#
# Partially supported. Currently only works for 96 dpi images.
#
# 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, x_scale and y_scale
# 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 +column+. The offset
# values are in pixels.
#
# worksheet1.insert_image('A1', 'ruby.bmp', 32, 10)
#
# 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 +x_scale+ and +y_scale+ 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', 'perl.bmp', 0, 0, 2, 0.8)
#
# 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.
row, col, image, x_offset, y_offset, x_scale, y_scale = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col, image].include?(nil)
x_offset ||= 0
y_offset ||= 0
x_scale ||= 1
y_scale ||= 1
@images << [row, col, image, x_offset, y_offset, x_scale, y_scale]
end
#
# :call-seq:
# repeat_formula(row, column, formula [ , format ] )
#
# Deprecated. This is a writeexcel gem's method that is no longer
# required by WriteXLSX.
#
# In writeexcel it was computationally expensive to write formulas
# since they were parsed by a recursive descent parser. The store_formula()
# and repeat_formula() methods were used as a way of avoiding the overhead
# of repeated formulas by reusing a pre-parsed formula.
#
# In WriteXLSX this is no longer necessary since it is just as quick
# to write a formula as it is to write a string or a number.
#
# The methods remain for backward compatibility but new WriteXLSX
# programs shouldn't use them.
#
def repeat_formula(*args)
# Check for a cell reference in A1 notation and substitute row and column.
row, col, formula, format, *pairs = row_col_notation(args)
raise WriteXLSXInsufficientArgumentError if [row, col].include?(nil)
raise "Odd number of elements in pattern/replacement list" unless pairs.size % 2 == 0
raise "Not a valid formula" unless formula.respond_to?(:to_ary)
tokens = formula.join("\t").split("\t")
raise "No tokens in formula" if tokens.empty?
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
formula = tokens.join('')
write_formula(row, col, formula, format, value)
end
#
# :call-seq:
# set_row(row [ , height, format, hidden, level, collapsed ] )
#
# This method can be used to change the default properties of a row.
# All parameters apart from +row+ are optional.
#
# 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()}[#method-i-write]. Calling it afterwards will overwrite
# any format that was previously specified.
#
# The +hidden+ parameter should be set to 1 if you wish to hide a row.
# This can be used, for example, to hide intermediary steps in a
# complicated calculation:
#
# worksheet.set_row(0, 20, format, 1)
# worksheet.set_row(1, nil, nil, 1)
#
# The +level+ parameter is used to set the outline level of the row.
# 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.rb and outline_collapsed.rb
# programs in the examples directory of the distro.
#
# Excel allows up to 7 outline levels. Therefore the +level+ parameter
# should be in the range 0 <= level <= 7.
#
# == OUTLINES AND GROUPING IN EXCEL
#
# Excel allows you to group rows or columns so that they can be hidden or
# displayed with a single mouse click. This feature is referred to as
# outlines.
#
# Outlines can reduce complex data down to a few salient sub-totals or
# summaries.
#
# This feature is best viewed in Excel but the following is an ASCII
# representation of what a worksheet with three outlines might look like.
# Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at
# level 1. The lines at the left hand side are called outline level bars.
#
# ------------------------------------------
# 1 2 3 | | A | B | C | D | ...
# ------------------------------------------
# _ | 1 | A | | | | ...
# | _ | 2 | B | | | | ...
# | | | 3 | (C) | | | | ...
# | | | 4 | (D) | | | | ...
# | - | 5 | E | | | | ...
# | _ | 6 | F | | | | ...
# | | | 7 | (G) | | | | ...
# | | | 8 | (H) | | | | ...
# | - | 9 | I | | | | ...
# - | . | ... | ... | ... | ... | ...
#
# Clicking the minus sign on each of the level 2 outlines will collapse
# and hide the data as shown in the next figure. The minus sign changes
# to a plus sign to indicate that the data in the outline is hidden.
#
# ------------------------------------------
# 1 2 3 | | A | B | C | D | ...
# ------------------------------------------
# _ | 1 | A | | | | ...
# | | 2 | B | | | | ...
# | + | 5 | E | | | | ...
# | | 6 | F | | | | ...
# | + | 9 | I | | | | ...
# - | . | ... | ... | ... | ... | ...
#
# Clicking on the minus sign on the level 1 outline will collapse the
# remaining rows as follows:
#
# ------------------------------------------
# 1 2 3 | | A | B | C | D | ...
# ------------------------------------------
# | 1 | A | | | | ...
# + | . | ... | ... | ... | ... | ...
#
# Grouping in WritXLSX is achieved by setting the outline level via the
# set_row() and set_column() worksheet methods:
#
# set_row(row, height, format, hidden, level, collapsed)
# set_column(first_col, last_col, width, format, hidden, level, collapsed)
#
# The following example sets an outline level of 1 for rows 1 and 2
# (zero-indexed) and columns B to G. The parameters $height and $XF are
# assigned default values since they are undefined:
#
# worksheet.set_row(1, nil, nil, 0, 1)
# worksheet.set_row(2, nil, nil, 0, 1)
# worksheet.set_column('B:G', nil, nil, 0, 1)
#
# Excel allows up to 7 outline levels. Therefore the +level+ parameter
# should be in the range 0 <= $level <= 7.
#
# Rows and columns can be collapsed by setting the +hidden+ flag for the
# hidden rows/columns and setting the +collapsed+ flag for the row/column
# that has the collapsed + symbol:
#
# worksheet.set_row(1, nil, nil, 1, 1)
# worksheet.set_row(2, nil, nil, 1, 1)
# worksheet.set_row(3, nil, nil, 0, 0, 1) # Collapsed flag.
#
# worksheet.set_column('B:G', nil, nil, 1, 1)
# worksheet.set_column('H:H', nil, nil, 0, 0, 1) # Collapsed flag.
#
# Note: Setting the $collapsed flag is particularly important for
# compatibility with OpenOffice.org and Gnumeric.
#
# For a more complete example see the outline.rb and outline_collapsed.rb
# programs in the examples directory of the distro.
#
# Some additional outline properties can be set via the outline_settings()
# worksheet method, see above.
#
def set_row(*args)
row = args[0]
height = args[1] || @default_height
xf = args[2]
hidden = args[3] || 0
level = args[4] || 0
collapsed = args[5] || 0
return if row.nil?
# Get the default row height.
default_height = @default_row_height
# Use min col in check_dimensions. Default to 0 if undefined.
min_col = @dim_colmin || 0
# Check that row and col are valid and store max and min values.
check_dimensions(row, min_col)
store_row_col_max_min_values(row, min_col)
height ||= default_height
# If the height is 0 the row is hidden and the height is the default.
if height == 0
hidden = 1
height = default_height
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
# Store the row properties.
@set_rows[row] = [height, xf, hidden, level, collapsed]
# Store the row change to allow optimisations.
@row_size_changed = true
# Store the row sizes for use when calculating image vertices.
@row_sizes[row] = height
end
#
# Set the default row properties
#
def set_default_row(height = nil, zero_height = nil)
height ||= 15
zero_height ||= 0
if height != 15
@default_row_height = height
# Store the row change to allow optimisations.
@row_size_changed = 1
end
if ptrue?(zero_height)
@default_row_zeroed = 1
end
end
#
# merge_range(first_row, first_col, last_row, last_col, string, format)
#
# Merge a range of cells. The first cell should contain the data and the
# others should be blank. All cells should contain the same format.
#
# The merge_range() method allows you to merge cells that contain other
# types of alignment in addition to the merging:
#
# format = workbook.add_format(
# :border => 6,
# :valign => 'vcenter',
# :align => 'center'
# )
#
# worksheet.merge_range('B3:D4', 'Vertical and horizontal', format)
#
# merge_range() writes its +token+ argument using the worksheet
# {#write()}[#method-i-write] method. Therefore it will handle numbers,
# strings, formulas or urls as required. If you need to specify the
# required write_*() method use the merge_range_type() method, see below.
#
# The full possibilities of this method are shown in the merge3.rb to
# merge6.rb programs in the examples directory of the distribution.
#
def merge_range(*args)
row_first, col_first, row_last, col_last, string, format, *extra_args = row_col_notation(args)
raise "Incorrect number of arguments" if [row_first, col_first, row_last, col_last, format].include?(nil)
raise "Fifth parameter must be a format object" unless format.respond_to?(:xf_index)
raise "Can't merge single cell" if row_first == row_last && col_first == col_last
# Swap last row/col with first row/col as necessary
row_first, row_last = row_last, row_first if row_first > row_last
col_first, col_last = col_last, col_first if col_first > col_last
# Check that column number is valid and store the max value
check_dimensions(row_last, col_last)
store_row_col_max_min_values(row_last, col_last)
# Store the merge range.
@merge << [row_first, col_first, row_last, col_last]
# Write the first cell
write(row_first, col_first, string, format, *extra_args)
# Pad out the rest of the area with formatted blank cells.
write_formatted_blank_to_area(row_first, row_last, col_first, col_last, format)
end
#
# Same as merge_range() above except the type of
# {#write()}[#method-i-write] is specified.
#
# The merge_range() method, see above, uses write() to insert the required
# data into to a merged range. However, there may be times where this
# isn't what you require so as an alternative the merge_range_type ()
# method allows you to specify the type of data you wish to write.
# For example:
#
# worksheet.merge_range_type('number', 'B2:C2', 123, format1)
# worksheet.merge_range_type('string', 'B4:C4', 'foo', format2)
# worksheet.merge_range_type('formula', 'B6:C6', '=1+2', format3)
#
# The +type+ must be one of the following, which corresponds to a write_*()
# method:
#
# 'number'
# 'string'
# 'formula'
# 'array_formula'
# 'blank'
# 'rich_string'
# 'date_time'
# 'url'
#
# Any arguments after the range should be whatever the appropriate method
# accepts:
#
# worksheet.merge_range_type('rich_string', 'B8:C8',
# 'This is ', bold, 'bold', format4)
#
# Note, you must always pass a format object as an argument, even if it is
# a default format.
#
def merge_range_type(type, *args)
case type
when 'array_formula', 'blank', 'rich_string'
row_first, col_first, row_last, col_last, *others = row_col_notation(args)
format = others.pop
else
row_first, col_first, row_last, col_last, token, format, *others = row_col_notation(args)
end
raise "Format object missing or in an incorrect position" unless format.respond_to?(:xf_index)
raise "Can't merge single cell" if row_first == row_last && col_first == col_last
# Swap last row/col with first row/col as necessary
row_first, row_last = row_last, row_first if row_first > row_last
col_first, col_last = col_last, col_first if col_first > col_last
# Check that column number is valid and store the max value
check_dimensions(row_last, col_last)
store_row_col_max_min_values(row_last, col_last)
# Store the merge range.
@merge << [row_first, col_first, row_last, col_last]
# Write the first cell
case type
when 'blank', 'rich_string', 'array_formula'
others << format
end
if type == 'string'
write_string(row_first, col_first, token, format, *others)
elsif type == 'number'
write_number(row_first, col_first, token, format, *others)
elsif type == 'blank'
write_blank(row_first, col_first, *others)
elsif type == 'date_time'
write_date_time(row_first, col_first, token, format, *others)
elsif type == 'rich_string'
write_rich_string(row_first, col_first, *others)
elsif type == 'url'
write_url(row_first, col_first, token, format, *others)
elsif type == 'formula'
write_formula(row_first, col_first, token, format, *others)
elsif type == 'array_formula'
write_formula_array(row_first, col_first, *others)
else
raise "Unknown type '#{type}'"
end
# Pad out the rest of the area with formatted blank cells.
write_formatted_blank_to_area(row_first, row_last, col_first, col_last, format)
end
#
# :call-seq:
# conditional_formatting(cell_or_cell_range, options)
#
# Conditional formatting is a feature of Excel which allows you to apply a
# format to a cell or a range of cells based on a certain criteria.
#
# For example the following criteria is used to highlight cells >= 50 in
# red in the conditional_format.rb example from the distro.
#
# worksheet.conditional_formatting('A1:J10',
# {
# :type => 'cell',
# :criteria => '>=',
# :value => 50,
# :format => format1
# }
# )
#
# http://jmcnamara.github.com/excel-writer-xlsx/images/examples/conditional_example.jpg
#
# The conditional_formatting method is used to apply formatting based
# on user defined criteria to an write_xlsx file.
#
# It can be applied to a single cell or a range of cells.
# You can pass 3 parameters such as (+row+, +col+, {...})
# or 5 parameters such as (+first_row+, +first_col+, +last_row+, +last_col+, {...}).
# You can also use A1 style notation. For example:
#
# worksheet.conditional_formatting( 0, 0, {...} )
# worksheet.conditional_formatting( 0, 0, 4, 1, {...} )
#
# # Which are the same as:
#
# worksheet.conditional_formatting( 'A1', {...} )
# worksheet.conditional_formatting( 'A1:B5', {...} )
#
#
# Using A1 style notation is is also possible to specify
# non-contiguous ranges, separated by a comma. For example:
#
# worksheet.conditional_formatting( 'A1:D5,A8:D12', {...} )
# The last parameter in conditional_formatting must be a hash containing
# the parameters that describe the type and style of the data validation.
#
# The main parameters are:
#
# :type
# :format
# :criteria
# :value
# :minimum
# :maximum
#
# Other, less commonly used parameters are:
#
# :min_type
# :mid_type
# :max_type
# :min_value
# :mid_value
# :max_value
# :min_color
# :mid_color
# :max_color
# :bar_color
#
# Additional parameters which are used for specific conditional format types
# are shown in the relevant sections below.
#
# === :type
#
# This parameter is passed in a hash to conditional_formatting.
#
# The +:type+ parameter is used to set the type of conditional formatting
# that you wish to apply. It is always required and it has no default value.
# Allowable type values and their associated parameters are:
#
# Type Parameters
# ====== ==========
# 'cell' :criteria
# :value
# :minimum
# :maximum
#
# 'date' :criteria
# :value
# :minimum
# :maximum
#
# 'time_period' :criteria
#
# 'text' :criteria
# :value
#
# 'average' :criteria
#
# 'duplicate' (none)
#
# 'unique' (none)
#
# 'top' :criteria
# :value
#
# 'bottom' :criteria
# :value
#
# 'blanks' (none)
#
# 'no_blanks' (none)
#
# 'errors' (none)
#
# 'no_errors' (none)
#
# '2_color_scale' (none)
#
# '3_color_scale' (none)
#
# 'data_bar' (none)
#
# 'formula' :criteria
#
# All conditional formatting types have a format parameter, see below.
# Other types and parameters such as icon sets will be added in time.
#
# === :type => 'cell'
#
# This is the most common conditional formatting type. It is used when
# a format is applied to a cell based on a simple criterion. For example:
#
# worksheet.conditional_formatting( 'A1',
# {
# :type => 'cell',
# :criteria => 'greater than',
# :value => 5,
# :format => red_format
# }
# )
# Or, using the between criteria:
#
# worksheet.conditional_formatting( 'C1:C4',
# {
# :type => 'cell',
# :criteria => 'between',
# :minimum => 20,
# :maximum => 30,
# :format => green_format
# }
# )
# === :criteria
#
# The +:criteria+ parameter is used to set the criteria by which the cell data
# will be evaluated. It has no default value. The most common criteria
# as applied to { type => 'cell' } are:
#
# 'between'
# 'not between'
# '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 symbolic alternatives.
#
# Additional criteria which are specific to other conditional format types
# are shown in the relevant sections below.
#
# === :value
#
# The +:value+ is generally used along with the criteria parameter to set the
# rule by which the cell data will be evaluated.
#
# :type => 'cell',
# :criteria => '>',
# :value => 5
# :format => format
#
# The +:value+ property can also be an cell reference.
#
# :type => 'cell',
# :criteria => '>',
# :value => '$C$1',
# :format => format
#
# === :format
#
# The +:format+ parameter is used to specify the format that will be applied
# to the cell when the conditional formatting criterion is met.
# The format is created using the add_format method in the same way as cell
# formats:
#
# format = workbook.add_format( :bold => 1, :italic => 1 )
#
# worksheet.conditional_formatting( 'A1',
# {
# :type => 'cell',
# :criteria => '>',
# :value => 5
# :format => format
# }
# )
#
# The conditional format follows the same rules as in Excel:
# it is superimposed over the existing cell format and not all font and
# border properties can be modified. Font properties that can't be modified
# are font name, font size, superscript and subscript.
# The border property that cannot be modified is diagonal borders.
#
# Excel specifies some default formats to be used with conditional
# formatting. You can replicate them using the following write_xlsx formats:
#
# # Light red fill with dark red text.
#
# format1 = workbook.add_format(
# :bg_color => '#FFC7CE',
# :color => '#9C0006'
# )
#
# # Light yellow fill with dark yellow text.
#
# format2 = workbook.add_format(
# :bg_color => '#FFEB9C',
# :color => '#9C6500'
# )
#
# # Green fill with dark green text.
#
# format3 = workbook.add_format(
# :bg_color => '#C6EFCE',
# :color => '#006100'
# )
#
# === :minimum
#
# The +:minimum+ parameter is used to set the lower limiting value when the
# +:criteria+ is either 'between' or 'not between':
#
# :validate => 'integer',
# :criteria => 'between',
# :minimum => 1,
# :maximum => 100
#
# === :maximum
#
# The +:maximum+ parameter is used to set the upper limiting value when the
# +:criteria+ is either 'between' or 'not between'. See the previous example.
#
# === :type => 'date'
#
# The date type is the same as the cell type and uses the same criteria
# and values. However it allows the value, minimum and maximum properties
# to be specified in the ISO8601 yyyy-mm-ddThh:mm:ss.sss date format which
# is detailed in the write_date_time() method.
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'date',
# :criteria => 'greater than',
# :value => '2011-01-01T',
# :format => format
# }
# )
#
# === :type => 'time_period'
#
# The time_period type is used to specify Excel's "Dates Occurring" style
# conditional format.
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'time_period',
# :criteria => 'yesterday',
# :format => format
# }
# )
#
# The period is set in the criteria and can have one of the following
# values:
#
# :criteria => 'yesterday',
# :criteria => 'today',
# :criteria => 'last 7 days',
# :criteria => 'last week',
# :criteria => 'this week',
# :criteria => 'next week',
# :criteria => 'last month',
# :criteria => 'this month',
# :criteria => 'next month'
#
# === :type => 'text'
#
# The text type is used to specify Excel's "Specific Text" style conditional
# format. It is used to do simple string matching using the criteria and
# value parameters:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'text',
# :criteria => 'containing',
# :value => 'foo',
# :format => format
# }
# )
#
# The criteria can have one of the following values:
#
# :criteria => 'containing',
# :criteria => 'not containing',
# :criteria => 'begins with',
# :criteria => 'ends with'
#
# The value parameter should be a string or single character.
#
# === :type => 'average'
#
# The average type is used to specify Excel's "Average" style conditional
# format.
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'average',
# :criteria => 'above',
# :format => format
# }
# )
#
# The type of average for the conditional format range is specified by the
# criteria:
#
# :criteria => 'above',
# :criteria => 'below',
# :criteria => 'equal or above',
# :criteria => 'equal or below',
# :criteria => '1 std dev above',
# :criteria => '1 std dev below',
# :criteria => '2 std dev above',
# :criteria => '2 std dev below',
# :criteria => '3 std dev above',
# :criteria => '3 std dev below'
#
# === :type => 'duplicate'
#
# The duplicate type is used to highlight duplicate cells in a range:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'duplicate',
# :format => format
# }
# )
#
# === :type => 'unique'
#
# The unique type is used to highlight unique cells in a range:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'unique',
# :format => format
# }
# )
#
# === :type => 'top'
#
# The top type is used to specify the top n values by number or percentage
# in a range:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'top',
# :value => 10,
# :format => format
# }
# )
#
# The criteria can be used to indicate that a percentage condition is
# required:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'top',
# :value => 10,
# :criteria => '%',
# :format => format
# }
# )
#
# === :type => 'bottom'
#
# The bottom type is used to specify the bottom n values by number or
# percentage in a range.
#
# It takes the same parameters as top, see above.
#
# === :type => 'blanks'
#
# The blanks type is used to highlight blank cells in a range:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'blanks',
# :format => format
# }
# )
#
# === :type => 'no_blanks'
#
# The no_blanks type is used to highlight non blank cells in a range:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'no_blanks',
# :format => format
# }
# )
#
# === :type => 'errors'
#
# The errors type is used to highlight error cells in a range:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'errors',
# :format => format
# }
# )
#
# === :type => 'no_errors'
#
# The no_errors type is used to highlight non error cells in a range:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'no_errors',
# :format => format
# }
# )
#
# === :type => '2_color_scale'
#
# The 2_color_scale type is used to specify Excel's "2 Color Scale" style
# conditional format.
#
# worksheet.conditional_formatting( 'A1:A12',
# {
# :type => '2_color_scale'
# }
# )
#
# At the moment only the default colors and properties can be used. These
# will be extended in time.
#
# === :type => '3_color_scale'
#
# The 3_color_scale type is used to specify Excel's "3 Color Scale" style
# conditional format.
#
# worksheet.conditional_formatting( 'A1:A12',
# {
# :type => '3_color_scale'
# }
# )
#
# At the moment only the default colors and properties can be used.
# These will be extended in time.
#
# === :type => 'data_bar'
#
# The data_bar type is used to specify Excel's "Data Bar" style conditional
# format.
#
# worksheet.conditional_formatting( 'A1:A12',
# {
# :type => 'data_bar',
# }
# )
#
# At the moment only the default colors and properties can be used. These
# will be extended in time.
#
# === :type => 'formula'
#
# The formula type is used to specify a conditional format based on
# a user defined formula:
#
# worksheet.conditional_formatting( 'A1:A4',
# {
# :type => 'formula',
# :criteria => '=$A$1 > 5',
# :format => format
# }
# )
#
# The formula is specified in the criteria.
#
# === :min_type, :mid_type, :max_type
#
# The min_type and max_type properties are available when the conditional
# formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_type
# is available for 3_color_scale. The properties are used as follows:
#
# worksheet.conditional_formatting( 'A1:A12',
# {
# :type => '2_color_scale',
# :min_type => 'percent',
# :max_type => 'percent'
# }
# )
#
# The available min/mid/max types are:
#
# 'num'
# 'percent'
# 'percentile'
# 'formula'
#
# === :min_value, :mid_value, :max_value
#
# The +:min_value+ and +:max_value+ properties are available when the
# conditional formatting type is 2_color_scale, 3_color_scale or
# data_bar. The +:mid_value+ is available for 3_color_scale. The properties
# are used as follows:
#
# worksheet.conditional_formatting( 'A1:A12',
# {
# :type => '2_color_scale',
# :min_value => 10,
# :max_value => 90
# }
# )
#
# === :min_color, :mid_color, :max_color, :bar_color
#
# The min_color and max_color properties are available when the conditional
# formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_color
# is available for 3_color_scale. The properties are used as follows:
#
# worksheet.conditional_formatting( 'A1:A12',
# {
# ;type => '2_color_scale',
# :min_color => "#C5D9F1",
# :max_color => "#538ED5"
# }
# )
#
# The color can be specifies as an Excel::Writer::XLSX color index or,
# more usefully, as a HTML style RGB hex number, as shown above.
#
# === Conditional Formatting Examples
#
# === Example 1. Highlight cells greater than an integer value.
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'cell',
# :criteria => 'greater than',
# :value => 5,
# :format => format
# }
# )
# === Example 2. Highlight cells greater than a value in a reference cell.
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'cell',
# :criteria => 'greater than',
# :value => '$H$1',
# :format => format
# }
# )
# === Example 3. Highlight cells greater than a certain date:
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'date',
# :criteria => 'greater than',
# :value => '2011-01-01T',
# :format => format
# }
# )
# === Example 4. Highlight cells with a date in the last seven days:
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'time_period',
# :criteria => 'last 7 days',
# :format => format
# }
# )
# === Example 5. Highlight cells with strings starting with the letter b:
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'text',
# :criteria => 'begins with',
# :value => 'b',
# :format => format
# }
# )
# === Example 6. Highlight cells that are 1 std deviation above the average for the range:
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'average',
# :format => format
# }
# )
# === Example 7. Highlight duplicate cells in a range:
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'duplicate',
# :format => format
# }
# )
# === Example 8. Highlight unique cells in a range.
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'unique',
# :format => format
# }
# )
# === Example 9. Highlight the top 10 cells.
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'top',
# :value => 10,
# :format => format
# }
# )
# === Example 10. Highlight blank cells.
#
# worksheet.conditional_formatting( 'A1:F10',
# {
# :type => 'blanks',
# :format => format
# }
# )
# See also the conditional_format.rb example program in EXAMPLES.
#
def conditional_formatting(*args)
cond_format = Package::ConditionalFormat.factory(self, *args)
@cond_formats[cond_format.range] ||= []
@cond_formats[cond_format.range] << cond_format
end
#
# :call-seq:
# add_table(row1, col1, row2, col2, properties)
#
# Add an Excel table to a worksheet.
#
# The add_table() method is used to group a range of cells into
# an Excel Table.
#
# worksheet.add_table('B3:F7', { ... } )
#
# This method contains a lot of parameters and is described
# in detail in a section
# {"TABLES IN EXCEL"}[#method-i-add_table-label-TABLES+IN+EXCEL].
#
# See also the tables.rb program in the examples directory of the distro
#
# ==TABLES IN EXCEL
#
# Tables in Excel are a way of grouping a range of cells into a single
# entity that has common formatting or that can be referenced from
# formulas. Tables can have column headers, autofilters, total rows,
# column formulas and default formatting.
#
# http://jmcnamara.github.com/excel-writer-xlsx/images/examples/tables.jpg
#
# For more information see "An Overview of Excel Tables"
# http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx.
#
# Tables are added to a worksheet using the add_table() method:
#
# worksheet.add_table('B3:F7', parameters)
#
# The data range can be specified in 'A1' or 'row/col' notation (see also
# the note about
# {"Cell notation"}[#label-Cell+notation] for more information.
#
# worksheet.add_table('B3:F7')
#
# # Same as:
# worksheet.add_table(2, 1, 6, 5)
#
# The last parameter in add_table() should be a hash ref containing the
# parameters that describe the table options and data. The available
# parameters are:
#
# :data
# :autofilter
# :header_row
# :banded_columns
# :banded_rows
# :first_column
# :last_column
# :style
# :total_row
# :columns
# :name
#
# The table parameters are detailed below. There are no required parameters
# and the hash ref isn't required if no options are specified.
#
# ===:data
#
# The +:data+ parameter can be used to specify the data in the cells of the
# table.
#
# data = [
# [ 'Apples', 10000, 5000, 8000, 6000 ],
# [ 'Pears', 2000, 3000, 4000, 5000 ],
# [ 'Bananas', 6000, 6000, 6500, 6000 ],
# [ 'Oranges', 500, 300, 200, 700 ]
# ]
#
# worksheet.add_table('B3:F7', :data => data)
#
# Table data can also be written separately, as an array or individual
# cells.
#
# # These two statements are the same as the single statement above.
# worksheet.add_table('B3:F7')
# worksheet.write_col('B4', data)
#
# Writing the cell data separately is occasionally required when you need
# to control the write_*() method used to populate the cells or if you
# wish to tweak the cell formatting.
#
# The data structure should be an array ref of array refs holding row data
# as shown above.
#
# ===:header_row
#
# The +:header_row+ parameter can be used to turn on or off the header row
# in the table. It is on by default.
#
# worksheet.add_table('B4:F7', :header_row => 0) # Turn header off.
#
# The header row will contain default captions such as Column 1, Column 2,
# etc. These captions can be overridden using the +:columns+ parameter
# below.
#
# ===:autofilter
#
# The +:autofilter+ parameter can be used to turn on or off the autofilter
# in the header row. It is on by default.
#
# worksheet.add_table('B3:F7', :autofilter => 0) # Turn autofilter off.
#
# The +:autofilter+ is only shown if the +:header_row+ is on. Filters
# within the table are not supported.
#
# ===:banded_rows
#
# The +:banded_rows+ parameter can be used to used to create rows of
# alternating colour in the table. It is on by default.
#
# worksheet.add_table('B3:F7', :banded_rows => 0)
#
# ===:banded_columns
#
# The +:banded_columns+ parameter can be used to used to create columns
# of alternating colour in the table. It is off by default.
#
# worksheet.add_table('B3:F7', :banded_columns => 1)
#
# ===:first_column
#
# The +:first_column+ parameter can be used to highlight the first column
# of the table. The type of highlighting will depend on the style of the
# table. It may be bold text or a different colour. It is off by default.
#
# worksheet.add_table('B3:F7', :first_column => 1)
#
# ===:last_column
#
# The +:last_column+ parameter can be used to highlight the last column
# of the table. The type of highlighting will depend on the style of the
# table. It may be bold text or a different colour. It is off by default.
#
# worksheet.add_table('B3:F7', :last_column => 1)
#
# ===:style
#
# The +:style+ parameter can be used to set the style of the table.
# Standard Excel table format names should be used (with matching
# capitalisation):
#
# worksheet11.add_table(
# 'B3:F7',
# {
# :data => data,
# :style => 'Table Style Light 11'
# }
# )
#
# The default table style is 'Table Style Medium 9'.
#
# ===:name
#
# The +:name+ parameter can be used to set the name of the table.
#
# By default tables are named Table1, Table2, etc. If you override the
# table name you must ensure that it doesn't clash with an existing table
# name and that it follows Excel's requirements for table names.
#
# worksheet.add_table('B3:F7', :name => 'SalesData')
#
# If you need to know the name of the table, for example to use it in a
# formula, you can get it as follows:
#
# table = worksheet2.add_table('B3:F7')
# table_name = table.name
#
# ===:total_row
#
# The +:total_row+ parameter can be used to turn on the total row in the
# last row of a table. It is distinguished from the other rows by a
# different formatting and also with dropdown SUBTOTAL functions.
#
# worksheet.add_table('B3:F7', :total_row => 1)
#
# The default total row doesn't have any captions or functions. These must
# by specified via the +:columns+ parameter below.
#
# ===:columns
#
# The +:columns+ parameter can be used to set properties for columns
# within the table.
#
# The sub-properties that can be set are:
#
# :header
# :formula
# :total_string
# :total_function
# :format
#
# The column data must be specified as an array of hash. For example to
# override the default 'Column n' style table headers:
#
# worksheet.add_table(
# 'B3:F7',
# {
# :data => data,
# :columns => [
# { :header => 'Product' },
# { :header => 'Quarter 1' },
# { :header => 'Quarter 2' },
# { :header => 'Quarter 3' },
# { :header => 'Quarter 4' }
# ]
# }
# )
#
# If you don't wish to specify properties for a specific column you pass
# an empty hash and the defaults will be applied:
#
# ...
# :columns => [
# { :header => 'Product' },
# { :header => 'Quarter 1' },
# { }, # Defaults to 'Column 3'.
# { :header => 'Quarter 3' },
# { :header => 'Quarter 4' }
# ]
# ...
#
# Column formulas can by applied using the formula column property:
#
# worksheet8.add_table(
# 'B3:G7',
# {
# :data => data,
# :columns => [
# { :header => 'Product' },
# { :header => 'Quarter 1' },
# { :header => 'Quarter 2' },
# { :header => 'Quarter 3' },
# { :header => 'Quarter 4' },
# {
# :header => 'Year',
# :formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
# }
# ]
# }
# )
#
# The Excel 2007 [#This Row] and Excel 2010 @ structural references are
# supported within the formula.
#
# As stated above the total_row table parameter turns on the "Total" row
# in the table but it doesn't populate it with any defaults. Total
# captions and functions must be specified via the columns property and
# the total_string and total_function sub properties:
#
# worksheet10.add_table(
# 'B3:F8',
# {
# :data => data,
# :total_row => 1,
# :columns => [
# { :header => 'Product', total_string => 'Totals' },
# { :header => 'Quarter 1', total_function => 'sum' },
# { :header => 'Quarter 2', total_function => 'sum' },
# { :header => 'Quarter 3', total_function => 'sum' },
# { :header => 'Quarter 4', total_function => 'sum' }
# ]
# }
# )
#
# The supported totals row SUBTOTAL functions are:
#
# average
# count_nums
# count
# max
# min
# std_dev
# sum
# var
#
# User defined functions or formulas aren't supported.
#
# Format can also be applied to columns:
#
# currency_format = workbook.add_format(:num_format => '$#,##0')
#
# worksheet.add_table(
# 'B3:D8',
# {
# :data => data,
# :total_row => 1,
# :columns => [
# { :header => 'Product', :total_string => 'Totals' },
# {
# :header => 'Quarter 1',
# :total_function => 'sum',
# :format => $currency_format
# },
# {
# :header => 'Quarter 2',
# :total_function => 'sum',
# :format => $currency_format
# }
# ]
# }
# )
#
# Standard WriteXLSX format objects can be used. However, they should be
# limited to numerical formats. Overriding other table formatting may
# produce inconsistent results.
#
def add_table(*args)
# Table count is a member of Workbook, global to all Worksheet.
@workbook.table_count += 1
id = @workbook.table_count
table = Package::Table.new(self, id, *args)
@external_table_links << ['/table', "../tables/table#{id}.xml"]
@tables << table
table
end
#
# :call-seq:
# add_sparkline(properties)
#
# Add sparklines to the worksheet.
#
# Sparklines are a feature of Excel 2010+ which allows you to add small
# charts to worksheet cells. These are useful for showing visual trends
# in data in a compact format.
#
# In WriteXLSX Sparklines can be added to cells using the add_sparkline()
# worksheet method:
#
# worksheet.add_sparkline(
# {
# :location => 'F2',
# :range => 'Sheet1!A2:E2',
# :type => 'column',
# :style => 12
# }
# )
#
# http://jmcnamara.github.com/excel-writer-xlsx/images/examples/sparklines1.jpg
#
# Note: Sparklines are a feature of Excel 2010+ only. You can write them
# to an XLSX file that can be read by Excel 2007 but they won't be
# displayed.
#
# The add_sparkline() worksheet method is used to add sparklines to a
# cell or a range of cells.
#
# The parameters to add_sparkline() must be passed in a hash.
# The main sparkline parameters are:
#
# :location (required)
# :range (required)
# :type
# :style
#
# :markers
# :negative_points
# :axis
# :reverse
# Other, less commonly used parameters are:
#
# :high_point
# :low_point
# :first_point
# :last_point
# :max
# :min
# :empty_cells
# :show_hidden
# :date_axis
# :weight
#
# :series_color
# :negative_color
# :markers_color
# :first_color
# :last_color
# :high_color
# :low_color
#
# These parameters are explained in the sections below:
#
# ===:location
#
# This is the cell where the sparkline will be displayed:
#
# :location => 'F1'
#
# The location should be a single cell. (For multiple cells see
# {"Grouped Sparklines"}[#method-i-add_sparkline-label-Grouped+Sparklines]
# below).
#
# To specify the location in row-column notation use the
# xl_rowcol_to_cell() function from the Writexlsx::Utility module.
#
# include Writexlsx::Utility
# ...
# location => xl_rowcol_to_cell( 0, 5 ), # F1
#
# ===:range
#
# This specifies the cell data range that the sparkline will plot:
#
# worksheet.add_sparkline(
# {
# :location => 'F1',
# :range => 'A1:E1'
# }
# )
#
# The range should be a 2D array. (For 3D arrays of cells see
# {"Grouped Sparklines"}[#method-i-add_sparkline-label-Grouped+Sparklines]
# below).
#
# If range is not on the same worksheet you can specify its location using
# the usual Excel notation:
#
# Lrange => 'Sheet1!A1:E1'
#
# If the worksheet contains spaces or special characters you should quote
# the worksheet name in the same way that Excel does:
#
# :range => q('Monthly Data'!A1:E1)
#
# To specify the location in row-column notation use the xl_range() or
# xl_range_formula() functions from the Writexlsx::Utility module.
#
# include Writexlsx::Utility
# ...
# range => xl_range( 1, 1, 0, 4 ), # 'A1:E1'
# range => xl_range_formula( 'Sheet1', 0, 0, 0, 4 ), # 'Sheet1!A2:E2'
#
# ===:type
#
# Specifies the type of sparkline. There are 3 available sparkline types:
#
# :line (default)
# :column
# :win_loss
#
# For example:
#
# {
# :location => 'F1',
# :range => 'A1:E1',
# :type => 'column'
# }
#
# ===:style
#
# Excel provides 36 built-in Sparkline styles in 6 groups of 6. The style
# parameter can be used to replicate these and should be a corresponding
# number from 1 .. 36.
#
# {
# :location => 'A14',
# :range => 'Sheet2!A2:J2',
# :style => 3
# }
#
# The style number starts in the top left of the style grid and runs left
# to right. The default style is 1. It is possible to override colour
# elements of the sparklines using the *_color parameters below.
#
# ===:markers
#
# Turn on the markers for line style sparklines.
#
# {
# :location => 'A6',
# :range => 'Sheet2!A1:J1',
# :markers => 1
# }
#
# Markers aren't shown in Excel for column and win_loss sparklines.
#
# ===:negative_points
#
# Highlight negative values in a sparkline range. This is usually required
# with win_loss sparklines.
#
# {
# :location => 'A21',
# :range => 'Sheet2!A3:J3',
# :type => 'win_loss',
# :negative_points => 1
# }
#
# ===:axis
#
# Display a horizontal axis in the sparkline:
#
# {
# :location => 'A10',
# :range => 'Sheet2!A1:J1',
# :axis => 1
# }
#
# ===:reverse
#
# Plot the data from right-to-left instead of the default left-to-right:
#
# {
# :location => 'A24',
# :range => 'Sheet2!A4:J4',
# :type => 'column',
# :reverse => 1
# }
#
# ===:weight
#
# Adjust the default line weight (thickness) for line style sparklines.
#
# :weight => 0.25
#
# The weight value should be one of the following values allowed by Excel:
#
# 0.25 0.5 0.75
# 1 1.25
# 2.25
# 3
# 4.25
# 6
#
# ===:high_point, low_point, first_point, last_point
#
# Highlight points in a sparkline range.
#
# :high_point => 1,
# :low_point => 1,
# :first_point => 1,
# :last_point => 1
#
# ===:max, min
#
# Specify the maximum and minimum vertical axis values:
#
# :max => 0.5,
# :min => -0.5
#
# As a special case you can set the maximum and minimum to be for a group
# of sparklines rather than one:
#
# max => 'group'
# See
# {"Grouped Sparklines"}[#method-i-add_sparkline-label-Grouped+Sparklines]
# below.
#
# ===:empty_cells
#
# Define how empty cells are handled in a sparkline.
#
# :empty_cells => 'zero',
#
# The available options are:
#
# gaps : show empty cells as gaps (the default).
# zero : plot empty cells as 0.
# connect: Connect points with a line ("line" type sparklines only).
#
# ===:show_hidden
#
# Plot data in hidden rows and columns:
#
# :show_hidden => 1
#
# Note, this option is off by default.
#
# ===:date_axis
#
# Specify an alternative date axis for the sparkline. This is useful if
# the data being plotted isn't at fixed width intervals:
#
# {
# :location => 'F3',
# :range => 'A3:E3',
# :date_axis => 'A4:E4'
# }
#
# The number of cells in the date range should correspond to the number
# of cells in the data range.
#
# ===:series_color
#
# It is possible to override the colour of a sparkline style using the
# following parameters:
#
# :series_color
# :negative_color
# :markers_color
# :first_color
# :last_color
# :high_color
# :low_color
#
# The color should be specified as a HTML style #rrggbb hex value:
#
# {
# :location => 'A18',
# :range => 'Sheet2!A2:J2',
# :type => 'column',
# :series_color => '#E965E0'
# }
#
# ==Grouped Sparklines
#
# The add_sparkline() worksheet method can be used multiple times to write
# as many sparklines as are required in a worksheet.
#
# However, it is sometimes necessary to group contiguous sparklines so that
# changes that are applied to one are applied to all. In Excel this is
# achieved by selecting a 3D range of cells for the data range and a
# 2D range of cells for the location.
#
# In WriteXLSX, you can simulate this by passing an array of values to
# location and range:
#
# {
# :location => [ 'A27', 'A28', 'A29' ],
# :range => [ 'Sheet2!A5:J5', 'Sheet2!A6:J6', 'Sheet2!A7:J7' ],
# :markers => 1
# }
#
# ===Sparkline examples
#
# See the sparklines1.rb and sparklines2.rb example programs in the
# examples directory of the distro.
#
def add_sparkline(param)
@sparklines << Sparkline.new(self, param, quote_sheetname(@name))
end
#
# :call-seq:
# insert_button(row, col, properties)
#
# The insert_button() method can be used to insert an Excel form button
# into a worksheet.
#
# This method is generally only useful when used in conjunction with
# the Workbook add_vba_project() method to tie the button to a macro
# from an embedded VBA project:
#
# workbook = WriteXLSX.new('file.xlsm')
# ...
# workbook.add_vba_project('./vbaProject.bin')
#
# worksheet.insert_button('C2', { :macro => 'my_macro' } )
#
# The properties of the button that can be set are:
#
# :macro
# :caption
# :width
# :height
# :x_scale
# :y_scale
# :x_offset
# :y_offset
#
# === Option: macro
# This option is used to set the macro that the button will invoke when
# the user clicks on it. The macro should be included using the
# Workbook#add_vba_project() method shown above.
#
# worksheet.insert_button('C2', { :macro => 'my_macro' } )
#
# The default macro is +ButtonX_Click+ where X is the button number.
#
# ===Option: caption
# This option is used to set the caption on the button. The default is
# Button X where X is the button number.
#
# worksheet.insert_button('C2', { :macro => 'my_macro', :caption => 'Hello' })
#
# ===Option: width
# This option is used to set the width of the button in pixels.
#
# worksheet.insert_button('C2', { :macro => 'my_macro', :width => 128 })
#
# The default button width is 64 pixels which is the width of a default cell.
#
# ===Option: height
# This option is used to set the height of the button in pixels.
#
# worksheet.insert_button('C2', { :macro => 'my_macro', :height => 40 })
#
# The default button height is 20 pixels which is the height of a default cell.
#
# ===Option: x_scale
# This option is used to set the width of the button as a factor of the
# default width.
#
# worksheet.insert_button('C2', { :macro => 'my_macro', :x_scale => 2.0 })
#
# ===Option: y_scale
# This option is used to set the height of the button as a factor of the
# default height.
#
# worksheet.insert_button('C2', { :macro => 'my_macro', y_:scale => 2.0 } )
#
# ===Option: x_offset
# This option is used to change the x offset, in pixels, of a button
# within a cell:
#
# worksheet.insert_button('C2', { :macro => 'my_macro', :x_offset => 2 })
#
# ===Option: y_offset
# This option is used to change the y offset, in pixels, of a comment
# within a cell.
#
# Note: Button is the only Excel form element that is available in
# WriteXLSX. Form elements represent a lot of work to implement and the
# underlying VML syntax isn't very much fun.
#
def insert_button(*args)
@buttons_array << button_params(*(row_col_notation(args)))
@has_vml = 1
end
#
# :call-seq:
# data_validation(cell_or_cell_range, options)
#
# Data validation is a feature of Excel which allows you to restrict
# the data that a users enters in a cell and to display help and
# warning messages. It also allows you to restrict input to values
# in a drop down list.
#
# A typical use case might be to restrict data in a cell to integer
# values in a certain range, to provide a help message to indicate
# the required value and to issue a warning if the input data doesn't
# meet the stated criteria. In WriteXLSX we could do that as follows:
#
# worksheet.data_validation('B3',
# {
# :validate => 'integer',
# :criteria => 'between',
# :minimum => 1,
# :maximum => 100,
# :input_title => 'Input an integer:',
# :input_message => 'Between 1 and 100',
# :error_message => 'Sorry, try again.'
# })
#
# For more information on data validation see the following Microsoft
# support article "Description and examples of data validation in Excel":
# http://support.microsoft.com/kb/211485.
#
# The following sections describe how to use the data_validation()
# method and its various options.
#
# 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"}[#label-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
# })
#
# ===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 WriteXLSX.
#
# +: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"}[#method-i-write_date_time-label-DATES+AND+TIME+IN+EXCEL]
# for more information about working with Excel's dates.
#
# :validate => 'date',
# :criteria => '>',
# :value => 39653, # 24 July 2008
# # Or like this:
# :value => '2008-07-24T',
#
# +:time+ restricts the cell to time values. Times in Excel are expressed
# 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"}[#method-i-write_date_time-label-DATES+AND+TIME+IN+EXCEL]
# for more information about working with Excel's times.
#
# :validate => 'time',
# :criteria => '>',
# :value => 0.5, # Noon
# # Or like this:
# :value => 'T12:00:00',
#
# +:length+ restricts the cell data based on an integer string length.
# 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 symbolic 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 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 => '$B$1:$B$3',
#
# ===: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.
#
# ===: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.
#
# ===: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.
#
# ===: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.
#
# ===: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
#
# ===Data Validation 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 => '=$E$4:$G$4',
# });
# ===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)
validation = DataValidation.new(*args)
@validations << validation unless validation.validate_none?
end
#
# Set the option to hide gridlines on the screen and the printed page.
#
# This was mainly useful for Excel 5 where printed gridlines were on by
# default.
#
# 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 or use nil the default option
# is true, i.e. only the printed gridlines are hidden.
#
def hide_gridlines(option = 1)
if option == 2
@screen_gridlines = false
else
@screen_gridlines = true
end
@page_setup.hide_gridlines(option)
end
# Set the option to print the row and column headers on the printed page.
#
# An Excel worksheet looks something like the following;
#
# ------------------------------------------
# | | 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 above.
#
def print_row_col_headers(headers = true)
@page_setup.print_row_col_headers(headers)
# if headers
# @print_headers = 1
# @page_setup.print_options_changed = 1
# else
# @print_headers = 0
# end
end
#
# The fit_to_pages() method is used to fit the printed area to a specific
# number of pages both vertically and horizontally. If the printed area
# 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
# as described above.
#
# 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:
#
# worksheet1.fit_to_pages(1, 0) # 1 page wide and as long as necessary
#
# 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 = 1, height = 1)
@page_setup.fit_page = true
@page_setup.fit_width = width
@page_setup.fit_height = height
@page_setup.page_setup_changed = true
end
#
# :call-seq:
# autofilter(first_row, first_col, last_row, last_col)
#
# 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 shown 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() or
# filter_column_list() method.
#
# See the autofilter.rb program in the examples directory of the distro
# for a more detailed example.
#
def autofilter(*args)
row1, col1, row2, col2 = row_col_notation(args)
return if [row1, col1, row2, col2].include?(nil)
# Reverse max and min values if necessary.
row1, row2 = row2, row1 if row2 < row1
col1, col2 = col2, col1 if col2 < col1
@autofilter_area = convert_name_area(row1, col1, row2, col2)
@autofilter_ref = xl_range(row1, row2, col1, col2)
@filter_range = [col1, col2]
end
#
# 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. WriteXLSX cannot
# do this automatically since it isn't part of the file format.
# See the autofilter.rb program in the examples directory of the distro
# for an example.
#
# The conditions for the filter are specified using simple expressions:
#
# worksheet.filter_column('A', 'x > 2000')
# worksheet.filter_column('B', 'x > 2000 and x < 5000')
#
# The +column+ parameter can either be a zero indexed column number or
# 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'
#
# 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.
#
# Note writeExcel gem supports Top 10 style filters. These aren't
# currently supported by WriteXLSX but may be added later.
#
def filter_column(col, expression)
raise "Must call autofilter before filter_column" unless @autofilter_area
col = prepare_filter_column(col)
tokens = extract_filter_tokens(expression)
unless tokens.size == 3 || tokens.size == 7
raise "Incorrect number of tokens in expression '#{expression}'"
end
tokens = parse_filter_expression(expression, tokens)
# Excel handles single or double custom filters as default filters. We need
# to check for them and handle them accordingly.
if tokens.size == 2 && tokens[0] == 2
# Single equality.
filter_column_list(col, tokens[1])
elsif tokens.size == 5 && tokens[0] == 2 && tokens[2] == 1 && tokens[3] == 2
# Double equality with "or" operator.
filter_column_list(col, tokens[1], tokens[4])
else
# Non default custom filter.
@filter_cols[col] = Array.new(tokens)
@filter_type[col] = 0
end
@filter_on = 1
end
#
# Set the column filter criteria in Excel 2007 list style.
#
# Prior to Excel 2007 it was only possible to have either 1 or 2 filter
# conditions such as the ones shown above in the filter_column method.
#
# Excel 2007 introduced a new list style filter where it is possible
# to specify 1 or more 'or' style criteria. For example if your column
# contained data for the first six months the initial data would be
# displayed as all selected as shown on the left. Then if you selected
# 'March', 'April' and 'May' they would be displayed as shown on the right.
#
# No criteria selected Some criteria selected.
#
# [/] (Select all) [X] (Select all)
# [/] January [ ] January
# [/] February [ ] February
# [/] March [/] March
# [/] April [/] April
# [/] May [/] May
# [/] June [ ] June
#
# The filter_column_list() method can be used to represent these types of
# filters:
#
# worksheet.filter_column_list('A', 'March', 'April', 'May')
#
# The column parameter can either be a zero indexed column number or
# a string column name.
#
# One or more criteria can be selected:
#
# worksheet.filter_column_list(0, 'March')
# worksheet.filter_column_list(1, 100, 110, 120, 130)
#
# NOTE: It isn't sufficient to just specify the filter condition. You must
# also hide any rows that don't match the filter condition. Rows are hidden
# using the set_row() visible parameter. WriteXLSX cannot 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. e conditions for the filter are specified
# using simple expressions:
#
def filter_column_list(col, *tokens)
tokens.flatten!
raise "Incorrect number of arguments to filter_column_list" if tokens.empty?
raise "Must call autofilter before filter_column_list" unless @autofilter_area
col = prepare_filter_column(col)
@filter_cols[col] = tokens
@filter_type[col] = 1 # Default style.
@filter_on = 1
end
#
# Store the horizontal page breaks on a worksheet.
#
# 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 list 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(*args)
breaks = args.collect do |brk|
Array(brk)
end.flatten
@page_setup.hbreaks += breaks
end
#
# Store the vertical page breaks on a worksheet.
#
# 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(*args)
@page_setup.vbreaks += args
end
#
# This method is used to make all cell comments visible when a worksheet
# is opened.
#
# worksheet.show_comments
#
# Individual comments can be made visible using the visible parameter of
# the write_comment method:
#
# 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.show_comments
# worksheet.write_comment('C3', 'Hello', :visible => 0)
#
def show_comments(visible = true)
@comments_visible = visible
end
#
# This method is used to set the default author of all cell comments.
#
# worksheet.comments_author = 'Ruby'
#
# Individual comment authors can be set using the author parameter
# of the write_comment method.
#
# The default comment author is an empty string, '',
# if no author is specified.
#
def comments_author=(author)
@comments_author = author || ''
end
# This method is deprecated. use comments_author=().
def set_comments_author(author)
put_deprecate_message("#{self}.set_comments_author")
self.comments_author = author
end
def has_vml? # :nodoc:
@has_vml
end
def has_comments? # :nodoc:
!@comments.empty?
end
def has_shapes?
@has_shapes
end
def is_chartsheet? # :nodoc:
!!@is_chartsheet
end
def set_external_vml_links(vml_drawing_id) # :nodoc:
@external_vml_links <<
['/vmlDrawing', "../drawings/vmlDrawing#{vml_drawing_id}.vml"]
end
def set_external_comment_links(comment_id) # :nodoc:
@external_comment_links <<
['/comments', "../comments#{comment_id}.xml"]
end
#
# Set up chart/drawings.
#
def prepare_chart(index, chart_id, drawing_id) # :nodoc:
drawing_type = 1
row, col, chart, x_offset, y_offset, x_scale, y_scale = @charts[index]
chart.id = chart_id - 1
x_scale ||= 0
y_scale ||= 0
# Use user specified dimensions, if any.
width = chart.width if ptrue?(chart.width)
height = chart.height if ptrue?(chart.height)
width = (0.5 + (width * x_scale)).to_i
height = (0.5 + (height * y_scale)).to_i
dimensions = position_object_emus(col, row, x_offset, y_offset, width, height)
# Set the chart name for the embedded object if it has been specified.
name = chart.name
# Create a Drawing object to use with worksheet unless one already exists.
if !drawing?
drawing = Drawing.new
drawing.add_drawing_object(drawing_type, dimensions, 0, 0, name)
drawing.embedded = 1
@drawing = drawing
@external_drawing_links << ['/drawing', "../drawings/drawing#{drawing_id}.xml" ]
else
@drawing.add_drawing_object(drawing_type, dimensions, 0, 0, name)
end
@drawing_links << ['/chart', "../charts/chart#{chart_id}.xml"]
end
#
# Returns a range of data from the worksheet _table to be used in chart
# cached data. Strings are returned as SST ids and decoded in the workbook.
# Return nils for data that doesn't exist since Excel can chart series
# with data missing.
#
def get_range_data(row_start, col_start, row_end, col_end) # :nodoc:
# TODO. Check for worksheet limits.
# Iterate through the table data.
data = []
(row_start .. row_end).each do |row_num|
# Store nil if row doesn't exist.
if !@cell_data_table[row_num]
data << nil
next
end
(col_start .. col_end).each do |col_num|
if cell = @cell_data_table[row_num][col_num]
data << cell.data
else
# Store nil if col doesn't exist.
data << nil
end
end
end
return data
end
#
# Calculate the vertices that define the position of a graphical object within
# the worksheet in pixels.
#
# +------------+------------+
# | A | B |
# +-----+------------+------------+
# | |(x1,y1) | |
# | 1 |(A1)._______|______ |
# | | | | |
# | | | | |
# +-----+----| BITMAP |-----+
# | | | | |
# | 2 | |______________. |
# | | | (B2)|
# | | | (x2,y2)|
# +---- +------------+------------+
#
# Example of an object that covers some of the area from cell A1 to cell B2.
#
# Based on the width and height of the object we need to calculate 8 vars:
#
# col_start, row_start, col_end, row_end, x1, y1, x2, y2.
#
# We also calculate the absolute x and y position of the top left vertex of
# the object. This is required for images.
#
# x_abs, y_abs
#
# The width and height of the cells that the object occupies can be 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 object from the width and height of the
# underlying cells.
#
# 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 object frame.
# height # Height of object frame.
def position_object_pixels(col_start, row_start, x1, y1, width, height) #:nodoc:
# Calculate the absolute x offset of the top-left vertex.
if @col_size_changed
x_abs = (0 .. col_start-1).inject(0) {|sum, col| sum += size_col(col)}
else
# Optimisation for when the column widths haven't changed.
x_abs = 64 * col_start
end
x_abs += x1
# Calculate the absolute y offset of the top-left vertex.
# Store the column change to allow optimisations.
if @row_size_changed
y_abs = (0 .. row_start-1).inject(0) {|sum, row| sum += size_row(row)}
else
# Optimisation for when the row heights haven't changed.
y_abs = 20 * row_start
end
y_abs += y1
# Adjust start column for offsets that are greater than the col width.
x1, col_start = adjust_column_offset(x1, col_start)
# Adjust start row for offsets that are greater than the row height.
y1, row_start = adjust_row_offset(y1, row_start)
# Initialise end cell to the same as the start cell.
col_end = col_start
row_end = row_start
width += x1
height += y1
# Subtract the underlying cell widths to find the end cell of the object.
width, col_end = adjust_column_offset(width, col_end)
# Subtract the underlying cell heights to find the end cell of the object.
height, row_end = adjust_row_offset(height, row_end)
# The end vertices are whatever is left from the width and height.
x2 = width
y2 = height
[col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
end
def comments_visible? # :nodoc:
!!@comments_visible
end
def sorted_comments # :nodoc:
@comments.sorted_comments
end
#
# Write the cell value element.
#
def write_cell_value(value = '') #:nodoc:
value ||= ''
value = value.to_i if value == value.to_i
@writer.data_element('v', value)
end
#
# Write the cell formula element.
#
def write_cell_formula(formula = '') #:nodoc:
@writer.data_element('f', formula)
end
#
# Write the cell array formula element.
#
def write_cell_array_formula(formula, range) #:nodoc:
@writer.data_element('f', formula, ['t', 'array', 'ref', range])
end
def date_1904? #:nodoc:
@workbook.date_1904?
end
#
# Convert from an Excel internal colour index to a XML style #RRGGBB index
# based on the default or user defined values in the Workbook palette.
#
def get_palette_color(index) #:nodoc:
if index =~ /^#([0-9A-F]{6})$/i
return "FF#{$~[1]}"
end
# Adjust the colour index.
index -= 8
# Palette is passed in from the Workbook class.
rgb = @workbook.palette[index]
# TODO Add the alpha part to the RGB.
sprintf("FF%02X%02X%02X", *rgb[0, 3])
end
def buttons_data # :nodoc:
@buttons_array
end
def external_links
[
@external_hyper_links,
@external_drawing_links,
@external_vml_links,
@external_table_links,
@external_comment_links
].reject { |a| a.empty? }
end
def drawing_links
[@drawing_links]
end
#
# Turn the HoH that stores the comments into an array for easier handling
# and set the external links for comments and buttons.
#
def prepare_vml_objects(vml_data_id, vml_shape_id, vml_drawing_id, comment_id)
set_external_vml_links(vml_drawing_id)
set_external_comment_links(comment_id) if has_comments?
# The VML o:idmap data id contains a comma separated range when there is
# more than one 1024 block of comments, like this: data="1,2".
(1 .. num_comments_block).each do |i|
vml_data_id = "#{vml_data_id},#{vml_data_id + i}"
end
@vml_data_id = vml_data_id
@vml_shape_id = vml_shape_id
end
def num_comments_block
@comments.size / 1024
end
def tables_count
@tables.size
end
private
def cell_format_of_rich_string(rich_strings)
# If the last arg is a format we use it as the cell format.
if rich_strings[-1].respond_to?(:xf_index)
rich_strings.pop
else
nil
end
end
# Convert the list of format, string tokens to pairs of (format, string)
# except for the first string fragment which doesn't require a default
# formatting run. Use the default for strings without a leading format.
def rich_strings_fragments(rich_strings) # :nodoc:
# Create a temp format with the default font for unformatted fragments.
default = Format.new(0)
length = 0 # String length.
last = 'format'
pos = 0
fragments = []
rich_strings.each do |token|
if token.respond_to?(:xf_index)
# Can't allow 2 formats in a row
return nil if last == 'format' && pos > 0
# Token is a format object. Add it to the fragment list.
fragments << token
last = 'format'
else
# Token is a string.
if last != 'format'
# If previous token wasn't a format add one before the string.
fragments << default << token
else
# If previous token was a format just add the string.
fragments << token
end
length += token.size # Keep track of actual string length.
last = 'string'
end
pos += 1
end
[fragments, length]
end
def xml_str_of_rich_string(fragments)
# Create a temp XML::Writer object and use it to write the rich string
# XML to a string.
writer = Package::XMLWriterSimple.new
# If the first token is a string start the element.
writer.start_tag('r') if !fragments[0].respond_to?(:xf_index)
# Write the XML elements for the format string fragments.
fragments.each do |token|
if token.respond_to?(:xf_index)
# Write the font run.
writer.start_tag('r')
token.write_font_rpr(writer, self)
else
# Write the string fragment part, with whitespace handling.
attributes = []
attributes << 'xml:space' << 'preserve' if token =~ /^\s/ || token =~ /\s$/
writer.data_element('t', token, attributes)
writer.end_tag('r')
end
end
writer.string
end
# Pad out the rest of the area with formatted blank cells.
def write_formatted_blank_to_area(row_first, row_last, col_first, col_last, format)
(row_first .. row_last).each do |row|
(col_first .. col_last).each do |col|
next if row == row_first && col == col_first
write_blank(row, col, format)
end
end
end
#
# 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 = []
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
tokens
end
#
# 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])
[expression_1, conditional, expression_2].flatten
else
parse_filter_tokens(expression, tokens)
end
end
#
# 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 += 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
token = ' '
end
else
if operator == 5
operator = 2
token = 'blanks'
else
operator = 5
token = ' '
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
[operator, token]
end
#
# 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(*args) #:nodoc:
return [] if args.empty?
breaks = args.uniq.sort
breaks.delete(0)
# The Excel 2007 specification says that the maximum number of page breaks
# is 1026. However, in practice it is actually 1023.
max_num_breaks = 1023
if breaks.size > max_num_breaks
breaks[0, max_num_breaks]
else
breaks
end
end
def adjust_column_offset(x, column)
while x >= size_col(column)
x -= size_col(column)
column += 1
end
[x, column]
end
def adjust_row_offset(y, row)
while y >= size_row(row)
y -= size_row(row)
row += 1
end
[y, row]
end
#
# Calculate the vertices that define the position of a graphical object within
# the worksheet in EMUs.
#
# The vertices are expressed as English Metric Units (EMUs). There are 12,700
# EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per pixel.
#
def position_object_emus(col_start, row_start, x1, y1, width, height) #:nodoc:
col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs =
position_object_pixels(col_start, row_start, x1, y1, width, height)
# Convert the pixel values to EMUs. See above.
x1 = (0.5 + 9_525 * x1).to_i
y1 = (0.5 + 9_525 * y1).to_i
x2 = (0.5 + 9_525 * x2).to_i
y2 = (0.5 + 9_525 * y2).to_i
x_abs = (0.5 + 9_525 * x_abs).to_i
y_abs = (0.5 + 9_525 * y_abs).to_i
[col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
end
#
# Convert the width of a cell from user's units to pixels. Excel rounds the
# column width to the nearest pixel. If the width hasn't been set by the user
# we use the default value. If the column is hidden it has a value of zero.
#
def size_col(col) #:nodoc:
# Look up the cell value to see if it has been changed.
if @col_sizes[col]
width = @col_sizes[col]
# Convert to pixels.
if width == 0
pixels = 0
elsif width < 1
pixels = (width * (MAX_DIGIT_WIDTH + PADDING) + 0.5).to_i
else
pixels = (width * MAX_DIGIT_WIDTH + 0.5).to_i + PADDING
end
else
pixels = 64
end
pixels
end
#
# Convert the height of a cell from user's units to pixels. If the height
# hasn't been set by the user we use the default value. If the row is hidden
# it has a value of zero.
#
def size_row(row) #:nodoc:
# Look up the cell value to see if it has been changed
if @row_sizes[row]
height = @row_sizes[row]
if height == 0
pixels = 0
else
pixels = (4 / 3.0 * height).to_i
end
else
pixels = (4 / 3.0 * @default_row_height).to_i
end
pixels
end
#
# Set up image/drawings.
#
def prepare_image(index, image_id, drawing_id, width, height, name, image_type) #:nodoc:
drawing_type = 2
drawing
row, col, image, x_offset, y_offset, x_scale, y_scale = @images[index]
width *= x_scale
height *= y_scale
dimensions = position_object_emus(col, row, x_offset, y_offset, width, height)
# Convert from pixels to emus.
width = (0.5 + (width * 9_525)).to_i
height = (0.5 + (height * 9_525)).to_i
# Create a Drawing object to use with worksheet unless one already exists.
if !drawing?
drawing = Drawing.new
drawing.embedded = 1
@drawing = drawing
@external_drawing_links << ['/drawing', "../drawings/drawing#{drawing_id}.xml"]
else
drawing = @drawing
end
drawing.add_drawing_object(drawing_type, dimensions, width, height, name)
@drawing_links << ['/image', "../media/image#{image_id}.#{image_type}"]
end
public :prepare_image
#
# :call-seq:
# insert_shape(row, col, shape [ , x, y, x_scale, y_scale ] )
#
# Insert a shape into the worksheet.
#
# This method can be used to insert a Shape object into a worksheet.
# The Shape must be created by the add_shape() Workbook method.
#
# shape = workbook.add_shape(:name => 'My Shape', :type => 'plus')
#
# # Configure the shape.
# shape.set_text('foo')
# ...
#
# # Insert the shape into the a worksheet.
# worksheet.insert_shape('E2', shape)
#
# See add_shape() for details on how to create the Shape object
# and Writexlsx::Shape for details on how to configure it.
#
# The +x+, +y+, +x_scale+ and +y_scale+ 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.
#
# worksheet1.insert_shape('E2', chart, 3, 3)
#
# The parameters +x_scale+ and +y_scale+ can be used to scale the
# inserted shape horizontally and vertically:
#
# # Scale the width by 120% and the height by 150%
# worksheet.insert_shape('E2', shape, 0, 0, 1.2, 1.5)
#
# See also the shape*.rb programs in the examples directory of the distro.
#
def insert_shape(*args)
# Check for a cell reference in A1 notation and substitute row and column.
row_start, column_start, shape, x_offset, y_offset, x_scale, y_scale =
row_col_notation(args)
if [row_start, column_start, shape].include?(nil)
raise "Insufficient arguments in insert_shape()"
end
shape.set_position(
row_start, column_start, x_offset, y_offset,
x_scale, y_scale
)
# Assign a shape ID.
while true
id = shape.id || 0
used = @shape_hash[id]
# Test if shape ID is already used. Otherwise assign a new one.
if !used && id != 0
break
else
@last_shape_id += 1
shape.id = @last_shape_id
end
end
# Allow lookup of entry into shape array by shape ID.
@shape_hash[shape.id] = shape.element = @shapes.size
if ptrue?(shape.stencil)
# Insert a copy of the shape, not a reference so that the shape is
# used as a stencil. Previously stamped copies don't get modified
# if the stencil is modified.
insert = shape.dup
else
insert = shape
end
# For connectors change x/y coords based on location of connected shapes.
insert.auto_locate_connectors(@shapes, @shape_hash)
# Insert a link to the shape on the list of shapes. Connection to
# the parent shape is maintained.
@shapes << insert
insert
end
public :insert_shape
#
# Set up drawing shapes
#
def prepare_shape(index, drawing_id)
shape = @shapes[index]
# Create a Drawing object to use with worksheet unless one already exists.
unless drawing?
@drawing = Drawing.new
@drawing.embedded = 1
@external_drawing_links << ['/drawing', "../drawings/drawing#{drawing_id}.xml"]
@has_shapes = true
end
# Validate the he shape against various rules.
shape.validate(index)
shape.calc_position_emus(self)
drawing_type = 3
drawing.add_drawing_object(drawing_type, shape.dimensions, shape.name, shape)
end
public :prepare_shape
#
# This method handles the parameters passed to insert_button as well as
# calculating the comment object position and vertices.
#
def button_params(row, col, params)
button = Writexlsx::Package::Button.new
button_number = 1 + @buttons_array.size
# Set the button caption.
caption = params[:caption] || "Button #{button_number}"
button.font = { :_caption => caption }
# Set the macro name.
if params[:macro]
button.macro = "[0]!#{params[:macro]}"
else
button.macro = "[0]!Button#{button_number}_Click"
end
# Ensure that a width and height have been set.
default_width = 64
default_height = 20
params[:width] = default_width if !params[:width]
params[:height] = default_height if !params[:height]
# Set the x/y offsets.
params[:x_offset] = 0 if !params[:x_offset]
params[:y_offset] = 0 if !params[:y_offset]
# Scale the size of the comment box if required.
if params[:x_scale]
params[:width] = params[:width] * params[:x_scale]
end
if params[:y_scale]
params[:height] = params[:height] * params[:y_scale]
end
# Round the dimensions to the nearest pixel.
params[:width] = (0.5 + params[:width]).to_i
params[:height] = (0.5 + params[:height]).to_i
params[:start_row] = row
params[:start_col] = col
# Calculate the positions of comment object.
vertices = position_object_pixels(
params[:start_col],
params[:start_row],
params[:x_offset],
params[:y_offset],
params[:width],
params[:height]
)
# Add the width and height for VML.
vertices << [params[:width], params[:height]]
button.vertices = vertices
button
end
#
# Based on the algorithm provided by Daniel Rentz of OpenOffice.
#
def encode_password(password) #:nodoc:
i = 0
chars = password.split(//)
count = chars.size
chars.collect! do |char|
i += 1
char = char.ord << 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
#
# Write the element. This is the root element of Worksheet.
#
def write_worksheet_attributes #:nodoc:
schema = 'http://schemas.openxmlformats.org/'
attributes = [
'xmlns', "#{schema}spreadsheetml/2006/main",
'xmlns:r', "#{schema}officeDocument/2006/relationships"
]
if @excel_version == 2010
attributes << 'xmlns:mc' << "#{schema}markup-compatibility/2006"
attributes << 'xmlns:x14ac' << "#{OFFICE_URL}spreadsheetml/2009/9/ac"
attributes << 'mc:Ignorable' << 'x14ac'
end
attributes
end
#
# Write the element for Sheet level properties.
#
def write_sheet_pr #:nodoc:
return unless tab_outline_fit? || vba_codename? || filter_on?
attributes = []
attributes << 'codeName' << @vba_codename if vba_codename?
attributes << 'filterMode' << 1 if filter_on?
if tab_outline_fit?
@writer.tag_elements('sheetPr', attributes) do
write_tab_color
write_outline_pr
write_page_set_up_pr
end
else
@writer.empty_tag('sheetPr', attributes)
end
end
def tab_outline_fit?
tab_color? || outline_changed? || fit_page?
end
#
# Write the element.
#
def write_page_set_up_pr #:nodoc:
@writer.empty_tag('pageSetUpPr', ['fitToPage', 1]) if fit_page?
end
# Write the element. This specifies the range of cells in the
# worksheet. As a special case, empty spreadsheets use 'A1' as a range.
#
def write_dimension #:nodoc:
if !@dim_rowmin && !@dim_colmin
# If the min dims are undefined then no dimensions have been set
# and we use the default 'A1'.
ref = 'A1'
elsif !@dim_rowmin && @dim_colmin
# If the row dims aren't set but the column dims are then they
# have been changed via set_column().
if @dim_colmin == @dim_colmax
# The dimensions are a single cell and not a range.
ref = xl_rowcol_to_cell(0, @dim_colmin)
else
# The dimensions are a cell range.
cell_1 = xl_rowcol_to_cell(0, @dim_colmin)
cell_2 = xl_rowcol_to_cell(0, @dim_colmax)
ref = cell_1 + ':' + cell_2
end
elsif @dim_rowmin == @dim_rowmax && @dim_colmin == @dim_colmax
# The dimensions are a single cell and not a range.
ref = xl_rowcol_to_cell(@dim_rowmin, @dim_colmin)
else
# The dimensions are a cell range.
cell_1 = xl_rowcol_to_cell(@dim_rowmin, @dim_colmin)
cell_2 = xl_rowcol_to_cell(@dim_rowmax, @dim_colmax)
ref = cell_1 + ':' + cell_2
end
@writer.empty_tag('dimension', ['ref', ref])
end
#
# Write the element.
#
def write_sheet_views #:nodoc:
@writer.tag_elements('sheetViews', []) { write_sheet_view }
end
def write_sheet_view #:nodoc:
attributes = []
# Hide screen gridlines if required
attributes << 'showGridLines' << 0 unless @screen_gridlines
# Hide zeroes in cells.
attributes << 'showZeros' << 0 unless show_zeros?
# Display worksheet right to left for Hebrew, Arabic and others.
attributes << 'rightToLeft' << 1 if @right_to_left
# Show that the sheet tab is selected.
attributes << 'tabSelected' << 1 if @selected
# Turn outlines off. Also required in the outlinePr element.
attributes << "showOutlineSymbols" << 0 if @outline_on
# Set the page view/layout mode if required.
# TODO. Add pageBreakPreview mode when requested.
(attributes << 'view' << 'pageLayout') if page_view?
# Set the zoom level.
if @zoom != 100
(attributes << 'zoomScale' << @zoom) unless page_view?
(attributes << 'zoomScaleNormal' << @zoom) if zoom_scale_normal?
end
attributes << 'workbookViewId' << 0
if @panes.empty? && @selections.empty?
@writer.empty_tag('sheetView', attributes)
else
@writer.tag_elements('sheetView', attributes) do
write_panes
write_selections
end
end
end
#
# Write the elements.
#
def write_selections #:nodoc:
@selections.each { |selection| write_selection(*selection) }
end
#
# Write the element.
#
def write_selection(pane, active_cell, sqref) #:nodoc:
attributes = []
(attributes << 'pane' << pane) if pane
(attributes << 'activeCell' << active_cell) if active_cell
(attributes << 'sqref' << sqref) if sqref
@writer.empty_tag('selection', attributes)
end
#
# Write the element.
#
def write_sheet_format_pr #:nodoc:
base_col_width = 10
attributes = ['defaultRowHeight', @default_row_height]
if @default_row_height != 15
attributes << 'customHeight' << 1
end
if ptrue?(@default_row_zeroed)
attributes << 'zeroHeight' << 1
end
attributes << 'outlineLevelRow' << @outline_row_level if @outline_row_level > 0
attributes << 'outlineLevelCol' << @outline_col_level if @outline_col_level > 0
if @excel_version == 2010
attributes << 'x14ac:dyDescent' << '0.25'
end
@writer.empty_tag('sheetFormatPr', attributes)
end
#
# Write the element and sub elements.
#
def write_cols #:nodoc:
# Exit unless some column have been formatted.
return if @colinfo.empty?
@writer.tag_elements('cols') do
@colinfo.each {|col_info| write_col_info(col_info) }
end
end
#
# Write the element.
#
def write_col_info(args) #:nodoc:
@writer.empty_tag('col', col_info_attributes(args))
end
def col_info_attributes(args)
min = args[0] || 0 # First formatted column.
max = args[1] || 0 # Last formatted column.
width = args[2] # Col width in user units.
format = args[3] # Format index.
hidden = args[4] || 0 # Hidden flag.
level = args[5] || 0 # Outline level.
collapsed = args[6] || 0 # Outline level.
xf_index = format ? format.get_xf_index : 0
custom_width = true
custom_width = false if width.nil? && hidden == 0
custom_width = false if width == 8.43
if width.nil?
width = hidden == 0 ? 8.43 : 0
end
# Convert column width from user units to character width.
if width && width < 1
width =
((width * (MAX_DIGIT_WIDTH + PADDING) + 0.5).to_i / MAX_DIGIT_WIDTH.to_f * 256).to_i / 256.0
else
width =
(((width * MAX_DIGIT_WIDTH + 0.5).to_i + PADDING).to_i/ MAX_DIGIT_WIDTH.to_f * 256).to_i / 256.0
end
width = width.to_i if width - width.to_i == 0
attributes = [
'min', min + 1,
'max', max + 1,
'width', width
]
attributes << 'style' << xf_index if xf_index != 0
attributes << 'hidden' << 1 if hidden != 0
attributes << 'customWidth' << 1 if custom_width
attributes << 'outlineLevel' << level if level != 0
attributes << 'collapsed' << 1 if collapsed != 0
attributes
end
#
# Write the element.
#
def write_sheet_data #:nodoc:
if !@dim_rowmin
# If the dimensions aren't defined then there is no data to write.
@writer.empty_tag('sheetData')
else
@writer.tag_elements('sheetData') { write_rows }
end
end
#
# Write out the worksheet data as a series of rows and cells.
#
def write_rows #:nodoc:
calculate_spans
(@dim_rowmin .. @dim_rowmax).each do |row_num|
# Skip row if it doesn't contain row formatting or cell data.
next if not_contain_formatting_or_data?(row_num)
span_index = row_num / 16
span = @row_spans[span_index]
# Write the cells if the row contains data.
if @cell_data_table[row_num]
args = @set_rows[row_num] || []
write_row_element(row_num, span, *args) do
write_cell_column_dimension(row_num)
end
elsif @comments[row_num]
write_empty_row(row_num, span, *(@set_rows[row_num]))
else
# Row attributes only.
write_empty_row(row_num, span, *(@set_rows[row_num]))
end
end
end
def not_contain_formatting_or_data?(row_num) # :nodoc:
!@set_rows[row_num] && !@cell_data_table[row_num] && !@comments.has_comment_in_row?(row_num)
end
def write_cell_column_dimension(row_num) # :nodoc:
(@dim_colmin .. @dim_colmax).each do |col_num|
@cell_data_table[row_num][col_num].write_cell if @cell_data_table[row_num][col_num]
end
end
#
# Write the element.
#
def write_row_element(*args) # :nodoc:
@writer.tag_elements('row', row_attributes(args)) do
yield
end
end
#
# Write and empty element, i.e., attributes only, no cell data.
#
def write_empty_row(*args) #:nodoc:
@writer.empty_tag('row', row_attributes(args))
end
def row_attributes(args)
r, spans, height, format, hidden, level, collapsed, empty_row = args
height ||= @default_row_height
hidden ||= 0
level ||= 0
xf_index = format ? format.get_xf_index : 0
attributes = ['r', r + 1]
(attributes << 'spans' << spans) if spans
(attributes << 's' << xf_index) if ptrue?(xf_index)
(attributes << 'customFormat' << 1 ) if ptrue?(format)
(attributes << 'ht' << height) if height != 15
(attributes << 'hidden' << 1 ) if ptrue?(hidden)
(attributes << 'customHeight' << 1 ) if height != 15
(attributes << 'outlineLevel' << level) if ptrue?(level)
(attributes << 'collapsed' << 1 ) if ptrue?(collapsed)
if @excel_version == 2010
attributes << 'x14ac:dyDescent' << '0.25'
end
attributes
end
#
# Write the frozen or split elements.
#
def write_panes #:nodoc:
return if @panes.empty?
if @panes[4] == 2
write_split_panes(*(@panes))
else
write_freeze_panes(*(@panes))
end
end
#
# Write the element for freeze panes.
#
def write_freeze_panes(row, col, top_row, left_col, type) #:nodoc:
y_split = row
x_split = col
top_left_cell = xl_rowcol_to_cell(top_row, left_col)
# Move user cell selection to the panes.
unless @selections.empty?
dummy, active_cell, sqref = @selections[0]
@selections = []
end
active_cell ||= nil
sqref ||= nil
active_pane = set_active_pane_and_cell_selections(row, col, row, col, active_cell, sqref)
# Set the pane type.
if type == 0
state = 'frozen'
elsif type == 1
state = 'frozenSplit'
else
state = 'split'
end
attributes = []
(attributes << 'xSplit' << x_split) if x_split > 0
(attributes << 'ySplit' << y_split) if y_split > 0
attributes << 'topLeftCell' << top_left_cell
attributes << 'activePane' << active_pane
attributes << 'state' << state
@writer.empty_tag('pane', attributes)
end
#
# Write the element for split panes.
#
# See also, implementers note for split_panes().
#
def write_split_panes(row, col, top_row, left_col, type) #:nodoc:
has_selection = false
y_split = row
x_split = col
# Move user cell selection to the panes.
if !@selections.empty?
dummy, active_cell, sqref = @selections[0]
@selections = []
has_selection = true
end
# Convert the row and col to 1/20 twip units with padding.
y_split = (20 * y_split + 300).to_i if y_split > 0
x_split = calculate_x_split_width(x_split) if x_split > 0
# For non-explicit topLeft definitions, estimate the cell offset based
# on the pixels dimensions. This is only a workaround and doesn't take
# adjusted cell dimensions into account.
if top_row == row && left_col == col
top_row = (0.5 + (y_split - 300) / 20 / 15).to_i
left_col = (0.5 + (x_split - 390) / 20 / 3 * 4 / 64).to_i
end
top_left_cell = xl_rowcol_to_cell(top_row, left_col)
# If there is no selection set the active cell to the top left cell.
if !has_selection
active_cell = top_left_cell
sqref = top_left_cell
end
active_pane = set_active_pane_and_cell_selections(row, col, top_row, left_col, active_cell, sqref)
attributes = []
(attributes << 'xSplit' << x_split) if x_split > 0
(attributes << 'ySplit' << y_split) if y_split > 0
attributes << 'topLeftCell' << top_left_cell
(attributes << 'activePane' << active_pane) if has_selection
@writer.empty_tag('pane', attributes)
end
#
# Convert column width from user units to pane split width.
#
def calculate_x_split_width(width) #:nodoc:
# Convert to pixels.
if width < 1
pixels = int(width * 12 + 0.5)
else
pixels = (width * MAX_DIGIT_WIDTH + 0.5).to_i + PADDING
end
# Convert to points.
points = pixels * 3 / 4
# Convert to twips (twentieths of a point).
twips = points * 20
# Add offset/padding.
twips + 390
end
#
# Write the element for the worksheet calculation properties.
#
def write_sheet_calc_pr #:nodoc:
@writer.empty_tag('sheetCalcPr', ['fullCalcOnLoad', 1])
end
#
# Write the element.
#
def write_phonetic_pr #:nodoc:
attributes = [
'fontId', 1,
'type', 'noConversion'
]
@writer.empty_tag('phoneticPr', attributes)
end
#
# Write the element.
#
def write_page_margins #:nodoc:
@page_setup.write_page_margins(@writer)
end
#
# Write the element.
#
def write_page_setup #:nodoc:
@page_setup.write_page_setup(@writer)
end
#
# Write the element.
#
def write_merge_cells #:nodoc:
write_some_elements('mergeCells', @merge) do
@merge.each { |merged_range| write_merge_cell(merged_range) }
end
end
def write_some_elements(tag, container)
return if container.empty?
@writer.tag_elements(tag, ['count', container.size]) do
yield
end
end
#
# Write the element.
#
def write_merge_cell(merged_range) #:nodoc:
row_min, col_min, row_max, col_max = merged_range
# Convert the merge dimensions to a cell range.
cell_1 = xl_rowcol_to_cell(row_min, col_min)
cell_2 = xl_rowcol_to_cell(row_max, col_max)
@writer.empty_tag('mergeCell', ['ref', "#{cell_1}:#{cell_2}"])
end
#
# Write the element.
#
def write_print_options #:nodoc:
@page_setup.write_print_options(@writer)
end
#
# Write the element.
#
def write_header_footer #:nodoc:
@page_setup.write_header_footer(@writer)
end
#
# Write the element.
#
def write_row_breaks #:nodoc:
write_breaks('rowBreaks')
end
#
# Write the element.
#
def write_col_breaks #:nodoc:
write_breaks('colBreaks')
end
def write_breaks(tag) # :nodoc:
case tag
when 'rowBreaks'
page_breaks = sort_pagebreaks(*(@page_setup.hbreaks))
max = 16383
when 'colBreaks'
page_breaks = sort_pagebreaks(*(@page_setup.vbreaks))
max = 1048575
else
raise "Invalid parameter '#{tag}' in write_breaks."
end
count = page_breaks.size
return if page_breaks.empty?
attributes = ['count', count, 'manualBreakCount', count]
@writer.tag_elements(tag, attributes) do
page_breaks.each { |num| write_brk(num, max) }
end
end
#
# Write the element.
#
def write_brk(id, max) #:nodoc:
attributes = [
'id', id,
'max', max,
'man', 1
]
@writer.empty_tag('brk', attributes)
end
#
# Write the element.
#
def write_auto_filter #:nodoc:
return unless autofilter_ref?
attributes = ['ref', @autofilter_ref]
if filter_on?
# Autofilter defined active filters.
@writer.tag_elements('autoFilter', attributes) do
write_autofilters
end
else
# Autofilter defined without active filters.
@writer.empty_tag('autoFilter', attributes)
end
end
#
# Function to iterate through the columns that form part of an autofilter
# range and write the appropriate filters.
#
def write_autofilters #:nodoc:
col1, col2 = @filter_range
(col1 .. col2).each do |col|
# Skip if column doesn't have an active filter.
next unless @filter_cols[col]
# Retrieve the filter tokens and write the autofilter records.
tokens = @filter_cols[col]
type = @filter_type[col]
# Filters are relative to first column in the autofilter.
write_filter_column(col - col1, type, *tokens)
end
end
#
# Write the element.
#
def write_filter_column(col_id, type, *filters) #:nodoc:
@writer.tag_elements('filterColumn', ['colId', col_id]) do
if type == 1
# Type == 1 is the new XLSX style filter.
write_filters(*filters)
else
# Type == 0 is the classic "custom" filter.
write_custom_filters(*filters)
end
end
end
#
# Write the element.
#
def write_filters(*filters) #:nodoc:
if filters.size == 1 && filters[0] == 'blanks'
# Special case for blank cells only.
@writer.empty_tag('filters', ['blank', 1])
else
# General case.
@writer.tag_elements('filters') do
filters.each { |filter| write_filter(filter) }
end
end
end
#
# Write the element.
#
def write_filter(val) #:nodoc:
@writer.empty_tag('filter', ['val', val])
end
#
# Write the element.
#
def write_custom_filters(*tokens) #:nodoc:
if tokens.size == 2
# One filter expression only.
@writer.tag_elements('customFilters') { write_custom_filter(*tokens) }
else
# Two filter expressions.
# Check if the "join" operand is "and" or "or".
if tokens[2] == 0
attributes = ['and', 1]
else
attributes = ['and', 0]
end
# Write the two custom filters.
@writer.tag_elements('customFilters', attributes) do
write_custom_filter(tokens[0], tokens[1])
write_custom_filter(tokens[3], tokens[4])
end
end
end
#
# Write the element.
#
def write_custom_filter(operator, val) #:nodoc:
operators = {
1 => 'lessThan',
2 => 'equal',
3 => 'lessThanOrEqual',
4 => 'greaterThan',
5 => 'notEqual',
6 => 'greaterThanOrEqual',
22 => 'equal'
}
# Convert the operator from a number to a descriptive string.
if operators[operator]
operator = operators[operator]
else
raise "Unknown operator = #{operator}\n"
end
# The 'equal' operator is the default attribute and isn't stored.
attributes = []
attributes << 'operator' << operator unless operator == 'equal'
attributes << 'val' << val
@writer.empty_tag('customFilter', attributes)
end
#
# Process any sored hyperlinks in row/col order and write the
# element. The attributes are different for internal and external links.
#
def write_hyperlinks #:nodoc:
return unless @hyperlinks
@hyperlinks.keys.sort.each do |row_num|
# Sort the hyperlinks into column order.
col_nums = @hyperlinks[row_num].keys.sort
# Iterate over the columns.
col_nums.each do |col_num|
# Get the link data for this cell.
link = @hyperlinks[row_num][col_num]
# If the cell isn't a string then we have to add the url as
# the string to display
if ptrue?(@cell_data_table) &&
ptrue?(@cell_data_table[row_num]) &&
ptrue?(@cell_data_table[row_num][col_num])
if @cell_data_table[row_num][col_num].display_url_string?
link.display = link.url_str
end
end
if link.link_type == 1
# External link with rel file relationship.
@rel_count += 1
@hlink_refs << [link, row_num, col_num, @rel_count]
# Links for use by the packager.
@external_hyper_links << ['/hyperlink', link.url, 'External']
else
# Internal link with rel file relationship.
@hlink_refs << [link, row_num, col_num]
end
end
end
return if @hlink_refs.empty?
# Write the hyperlink elements.
@writer.tag_elements('hyperlinks') do
@hlink_refs.each do |aref|
case aref[0].link_type
when 1
write_hyperlink_external(*aref)
when 2
write_hyperlink_internal(*aref)
end
end
end
end
#
# Write the element for external links.
#
def write_hyperlink_external(link, row, col, id) # :nodoc:
@writer.empty_tag('hyperlink', link.write_external_attributes(row, col, id))
end
#
# Write the element for internal links.
#
def write_hyperlink_internal(link, row, col) #:nodoc:
@writer.empty_tag('hyperlink', link.write_internal_attributes(row, col))
end
#
# Write the element.
#
def write_tab_color #:nodoc:
return unless tab_color?
@writer.empty_tag('tabColor', ['rgb', get_palette_color(@tab_color)])
end
#
# Write the element.
#
def write_outline_pr
return unless outline_changed?
attributes = []
attributes << "applyStyles" << 1 if @outline_style != 0
attributes << "summaryBelow" << 0 if @outline_below == 0
attributes << "summaryRight" << 0 if @outline_right == 0
attributes << "showOutlineSymbols" << 0 if @outline_on == 0
@writer.empty_tag('outlinePr', attributes)
end
#
# Write the element.
#
def write_sheet_protection #:nodoc:
return unless protect?
attributes = []
attributes << "password" << @protect[:password] if ptrue?(@protect[:password])
attributes << "sheet" << 1 if ptrue?(@protect[:sheet])
attributes << "content" << 1 if ptrue?(@protect[:content])
attributes << "objects" << 1 unless ptrue?(@protect[:objects])
attributes << "scenarios" << 1 unless ptrue?(@protect[:scenarios])
attributes << "formatCells" << 0 if ptrue?(@protect[:format_cells])
attributes << "formatColumns" << 0 if ptrue?(@protect[:format_columns])
attributes << "formatRows" << 0 if ptrue?(@protect[:format_rows])
attributes << "insertColumns" << 0 if ptrue?(@protect[:insert_columns])
attributes << "insertRows" << 0 if ptrue?(@protect[:insert_rows])
attributes << "insertHyperlinks" << 0 if ptrue?(@protect[:insert_hyperlinks])
attributes << "deleteColumns" << 0 if ptrue?(@protect[:delete_columns])
attributes << "deleteRows" << 0 if ptrue?(@protect[:delete_rows])
attributes << "selectLockedCells" << 1 unless ptrue?(@protect[:select_locked_cells])
attributes << "sort" << 0 if ptrue?(@protect[:sort])
attributes << "autoFilter" << 0 if ptrue?(@protect[:autofilter])
attributes << "pivotTables" << 0 if ptrue?(@protect[:pivot_tables])
attributes << "selectUnlockedCells" << 1 unless ptrue?(@protect[:select_unlocked_cells])
@writer.empty_tag('sheetProtection', attributes)
end
#
# Write the elements.
#
def write_drawings #:nodoc:
increment_rel_id_and_write_r_id('drawing') if drawing?
end
#
# Write the element.
#
def write_legacy_drawing #:nodoc:
increment_rel_id_and_write_r_id('legacyDrawing') if has_vml?
end
#
# Write the underline font element.
#
def write_underline(writer, underline) #:nodoc:
writer.empty_tag('u', underline_attributes(underline))
end
#
# Write the element.
#
def write_table_parts
return if @tables.empty?
@writer.tag_elements('tableParts', ['count', tables_count]) do
tables_count.times { increment_rel_id_and_write_r_id('tablePart') }
end
end
#
# Write the element.
#
def write_table_part(id)
@writer.empty_tag('tablePart', r_id_attributes(id))
end
def increment_rel_id_and_write_r_id(tag)
@rel_count += 1
write_r_id(tag, @rel_count)
end
def write_r_id(tag, id)
@writer.empty_tag(tag, r_id_attributes(id))
end
#
# Write the element and sparkline subelements.
#
def write_ext_sparklines # :nodoc:
@writer.tag_elements('extLst') { write_ext } unless @sparklines.empty?
end
def write_ext
@writer.tag_elements('ext', write_ext_attributes) do
write_sparkline_groups
end
end
def write_ext_attributes
[
'xmlns:x14', "#{OFFICE_URL}spreadsheetml/2009/9/main",
'uri', '{05C60535-1F16-4fd2-B633-F4F36F0B64E0}'
]
end
def write_sparkline_groups
# Write the x14:sparklineGroups element.
@writer.tag_elements('x14:sparklineGroups', sparkline_groups_attributes) do
# Write the sparkline elements.
@sparklines.reverse.each do |sparkline|
sparkline.write_sparkline_group(@writer)
end
end
end
def sparkline_groups_attributes # :nodoc:
['xmlns:xm', "#{OFFICE_URL}excel/2006/main"]
end
#
# Write the element.
#
def write_data_validations #:nodoc:
write_some_elements('dataValidations', @validations) do
@validations.each { |validation| validation.write_data_validation(@writer) }
end
end
#
# Write the Worksheet conditional formats.
#
def write_conditional_formats #:nodoc:
@cond_formats.keys.sort.each do |range|
write_conditional_formatting(range, @cond_formats[range])
end
end
#
# Write the element.
#
def write_conditional_formatting(range, cond_formats) #:nodoc:
@writer.tag_elements('conditionalFormatting', ['sqref', range]) do
cond_formats.each { |cond_format| cond_format.write_cf_rule }
end
end
def store_data_to_table(cell_data) #:nodoc:
row, col = cell_data.row, cell_data.col
if @cell_data_table[row]
@cell_data_table[row][col] = cell_data
else
@cell_data_table[row] = {}
@cell_data_table[row][col] = cell_data
end
end
def store_row_col_max_min_values(row, col)
store_row_max_min_values(row)
store_col_max_min_values(col)
end
#
# Calculate the "spans" attribute of the tag. This is an XLSX
# optimisation and isn't strictly required. However, it makes comparing
# files easier.
#
# The span is the same for each block of 16 rows.
#
def calculate_spans #:nodoc:
span_min = nil
span_max = 0
spans = []
(@dim_rowmin .. @dim_rowmax).each do |row_num|
if @cell_data_table[row_num]
span_min, span_max = calc_spans(@cell_data_table, row_num, span_min, span_max)
end
# Calculate spans for comments.
if @comments[row_num]
span_min, span_max = calc_spans(@comments, row_num, span_min, span_max)
end
if ((row_num + 1) % 16 == 0) || (row_num == @dim_rowmax)
span_index = row_num / 16
if span_min
span_min += 1
span_max += 1
spans[span_index] = "#{span_min}:#{span_max}"
span_min = nil
end
end
end
@row_spans = spans
end
def calc_spans(data, row_num, span_min, span_max)
(@dim_colmin .. @dim_colmax).each do |col_num|
if data[row_num][col_num]
if !span_min
span_min = col_num
span_max = col_num
else
span_min = col_num if col_num < span_min
span_max = col_num if col_num > span_max
end
end
end
[span_min, span_max]
end
#
# Add a string to the shared string table, if it isn't already there, and
# return the string index.
#
def shared_string_index(str, params = {}) #:nodoc:
@workbook.shared_string_index(str, params)
end
#
# convert_name_area(first_row, first_col, last_row, last_col)
#
# Convert zero indexed rows and columns to the format required by worksheet
# named ranges, eg, "Sheet1!$A$1:$C$13".
#
def convert_name_area(row_num_1, col_num_1, row_num_2, col_num_2) #:nodoc:
range1 = ''
range2 = ''
row_col_only = false
# Convert to A1 notation.
col_char_1 = xl_col_to_name(col_num_1, 1)
col_char_2 = xl_col_to_name(col_num_2, 1)
row_char_1 = "$#{row_num_1 + 1}"
row_char_2 = "$#{row_num_2 + 1}"
# We need to handle some special cases that refer to rows or columns only.
if row_num_1 == 0 and row_num_2 == ROW_MAX - 1
range1 = col_char_1
range2 = col_char_2
row_col_only = true
elsif col_num_1 == 0 and col_num_2 == COL_MAX - 1
range1 = row_char_1
range2 = row_char_2
row_col_only = true
else
range1 = col_char_1 + row_char_1
range2 = col_char_2 + row_char_2
end
# A repeated range is only written once (if it isn't a special case).
if range1 == range2 && !row_col_only
area = range1
else
area = "#{range1}:#{range2}"
end
# Build up the print area range "Sheet1!$A$1:$C$13".
"#{quote_sheetname(name)}!#{area}"
end
#
# Sheetnames used in references should be quoted if they contain any spaces,
# special characters or if the look like something that isn't a sheet name.
# TODO. We need to handle more special cases.
#
def quote_sheetname(sheetname) #:nodoc:
return sheetname if sheetname =~ /^Sheet\d+$/
return "'#{sheetname}'"
end
def fit_page? #:nodoc:
@page_setup.fit_page
end
def filter_on? #:nodoc:
ptrue?(@filter_on)
end
def tab_color? #:nodoc:
ptrue?(@tab_color)
end
def outline_changed?
ptrue?(@outline_changed)
end
def vba_codename?
ptrue?(@vba_codename)
end
def zoom_scale_normal? #:nodoc:
ptrue?(@zoom_scale_normal)
end
def page_view? #:nodoc:
!!@page_view
end
def right_to_left? #:nodoc:
!!@right_to_left
end
def show_zeros? #:nodoc:
!!@show_zeros
end
def protect? #:nodoc:
!!@protect
end
def autofilter_ref? #:nodoc:
!!@autofilter_ref
end
def drawing? #:nodoc:
!!@drawing
end
def remove_white_space(margin) #:nodoc:
if margin.respond_to?(:gsub)
margin.gsub(/[^\d\.]/, '')
else
margin
end
end
def set_active_pane_and_cell_selections(row, col, top_row, left_col, active_cell, sqref) # :nodoc:
if row > 0 && col > 0
active_pane = 'bottomRight'
row_cell = xl_rowcol_to_cell(top_row, 0)
col_cell = xl_rowcol_to_cell(0, left_col)
@selections <<
[ 'topRight', col_cell, col_cell ] <<
[ 'bottomLeft', row_cell, row_cell ] <<
[ 'bottomRight', active_cell, sqref ]
elsif col > 0
active_pane = 'topRight'
@selections << [ 'topRight', active_cell, sqref ]
else
active_pane = 'bottomLeft'
@selections << [ 'bottomLeft', active_cell, sqref ]
end
active_pane
end
def prepare_filter_column(col) # :nodoc:
# Check for a column reference in A1 notation and substitute.
if col =~ /^\D/
col_letter = col
# Convert col ref to a cell ref and then to a col number.
dummy, col = substitute_cellref("#{col}1")
raise "Invalid column '#{col_letter}'" if col >= COL_MAX
end
col_first, col_last = @filter_range
# Reject column if it is outside filter range.
if col < col_first or col > col_last
raise "Column '#{col}' outside autofilter column range (#{col_first} .. #{col_last})"
end
col
end
end
end