lib/write_xlsx/worksheet.rb in write_xlsx-0.0.4 vs lib/write_xlsx/worksheet.rb in write_xlsx-0.51.0
- old
+ new
@@ -30,13 +30,15 @@
# write_comment
# show_comments
# set_comments_author
# insert_image
# insert_chart
+ # insert_shape
# data_validation
- # conditional_format
- # get_name
+ # conditional_formatting
+ # add_table
+ # name
# activate
# select
# hide
# set_first_sheet
# protect
@@ -60,20 +62,21 @@
#
# 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:
+ # 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:
+ # 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
#
@@ -97,10 +100,48 @@
#
# 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.
#
+ # == 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.
+ # They are documented here in a separate section for the sake of clarity.
+ #
+ # The following methods are available for page set-up:
+ #
+ # set_landscape()
+ # set_portrait()
+ # set_page_view()
+ # set_paper()
+ # center_horizontally()
+ # center_vertically()
+ # set_margins()
+ # set_header()
+ # set_footer()
+ # repeat_rows()
+ # repeat_columns()
+ # hide_gridlines()
+ # print_row_col_headers()
+ # print_area()
+ # print_across()
+ # fit_to_pages()
+ # set_start_page()
+ # set_print_scale()
+ # set_h_pagebreaks()
+ # set_v_pagebreaks()
+ # A common requirement when working with WriteXLSX is to apply the same
+ # page set-up features to all of the worksheets in a workbook. To do this
+ # you can use the sheets() method of the workbook class to access the array
+ # of worksheets in a workbook:
+ #
+ # workbook.sheets.each do |worksheet|
+ # worksheet.set_landscape
+ # end
+ #
class Worksheet
include Writexlsx::Utility
class CellData # :nodoc:
include Writexlsx::Utility
@@ -175,11 +216,15 @@
def data
@result || 0
end
def write_cell
- @worksheet.writer.tag_elements('c', cell_attributes) do
+ attributes = cell_attributes
+ if @result && !(@result.to_s =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/)
+ attributes << 't' << 'str'
+ end
+ @worksheet.writer.tag_elements('c', attributes) do
@worksheet.write_cell_formula(token)
@worksheet.write_cell_value(result || 0)
end
end
end
@@ -220,15 +265,15 @@
@worksheet.write_cell_value(token)
end
if link_type == 1
# External link with rel file relationship.
- @worksheet.hlink_count += 1
+ @worksheet.rel_count += 1
@worksheet.hlink_refs <<
[
link_type, row, col,
- @worksheet.hlink_count, @str, @tip
+ @worksheet.rel_count, @str, @tip
]
@worksheet.external_hyper_links << [ '/hyperlink', @url, 'External' ]
elsif link_type
# External link with rel file relationship.
@@ -257,11 +302,11 @@
attr_accessor :margin_header, :margin_footer # :nodoc:
attr_accessor :repeat_rows, :repeat_cols, :print_area # :nodoc:
attr_accessor :hbreaks, :vbreaks, :scale # :nodoc:
attr_accessor :fit_page, :fit_width, :fit_height, :page_setup_changed # :nodoc:
attr_accessor :across # :nodoc:
- attr_writer :orientation
+ attr_accessor :orientation # :nodoc:
def initialize # :nodoc:
@margin_left = 0.7
@margin_right = 0.7
@margin_top = 0.75
@@ -297,27 +342,28 @@
!!@orientation
end
end
attr_reader :index # :nodoc:
- attr_reader :charts, :images, :drawing # :nodoc:
+ attr_reader :charts, :images, :tables, :shapes, :drawing # :nodoc:
attr_reader :external_hyper_links, :external_drawing_links # :nodoc:
+ attr_reader :external_vml_links, :external_table_links # :nodoc:
attr_reader :external_comment_links, :drawing_links # :nodoc:
attr_reader :vml_data_id # :nodoc:
attr_reader :autofilter_area # :nodoc:
attr_reader :writer, :set_rows, :col_formats # :nodoc:
- attr_accessor :vml_shape_id, :hlink_count, :hlink_refs # :nodoc:
+ attr_accessor :vml_shape_id, :rel_count, :hlink_refs # :nodoc:
+ attr_reader :comments_author # :nodoc:
def initialize(workbook, index, name) #:nodoc:
@writer = Package::XMLWriterSimple.new
@workbook = workbook
@index = index
@name = name
@colinfo = []
@cell_data_table = {}
- @filter_on = false
@print_style = PrintStyle.new
@print_area = ''
@@ -346,18 +392,25 @@
@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 = []
+ @shapes = []
+ @shape_hash = {}
@zoom = 100
@outline_row_level = 0
@outline_col_level = 0
@@ -396,10 +449,11 @@
write_header_footer
write_row_breaks
write_col_breaks
write_drawings
write_legacy_drawing
+ write_table_parts
# write_ext_lst
@writer.end_tag('worksheet')
@writer.crlf
@writer.close
end
@@ -576,12 +630,20 @@
# can be protected as follows:
#
# worksheet.protect('drowssap', { :insert_rows => true } )
#
def protect(password = nil, options = {})
- # Default values for objects that can be protected.
- defaults = {
+ 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,
@@ -594,28 +656,14 @@
:delete_rows => false,
:select_locked_cells => true,
:sort => false,
:autofilter => false,
:pivot_tables => false,
- :select_unlocked_cells => true,
+ :select_unlocked_cells => true
}
-
- # Overwrite the defaults with user specified values.
- options.each do |k, v|
- if defaults.has_key?(k)
- defaults[k] = options[k]
- else
- raise "Unknown protection object: #{k}\n"
- end
- end
-
- # Set the password after the user defined values.
- defaults[:password] =
- sprintf("%X", encode_password(password)) if password && password != ''
-
- @protect = defaults
end
+ private :protect_default_settings
#
# :call-seq:
# set_column(firstcol, lastcol, width, format, hidden, level)
#
@@ -710,11 +758,11 @@
# 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 if lastcol == 0
+ lastcol = firstcol unless ptrue?(lastcol)
# Ensure 2nd col is larger than first. Also for KB918419 bug.
firstcol, lastcol = lastcol, firstcol if firstcol > lastcol
width, format, hidden, level = data
@@ -722,12 +770,12 @@
# 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 && hidden && hidden != 0 # Column has a width but is hidden
+ 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).
@@ -744,11 +792,11 @@
@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 hidden && hidden != 0 # Set width to zero if col is hidden
+ 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
@@ -1010,17 +1058,22 @@
# worksheet.set_paper(9) # A4
#
# If you do not specify a paper type the worksheet will print using
# the printer's default paper.
#
- def set_paper(paper_size)
+ def paper=(paper_size)
if paper_size
@paper_size = paper_size
@print_style.page_setup_changed = true
end
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.
@@ -1417,18 +1470,18 @@
# 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)
+ 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)}"
- @print_style.repeat_rows = "#{quote_sheetname(@name)}!#{area}"
+ @print_style.repeat_cols = "#{quote_sheetname(@name)}!#{area}"
end
def print_repeat_cols # :nodoc:
@print_style.repeat_cols
end
@@ -1455,11 +1508,10 @@
return
end
# Build up the print area range "=Sheet2!R1C1:R2C1"
@print_area = convert_name_area(row1, col1, row2, col2)
- @print_area.dup
end
#
# Set the worksheet zoom factor.
#
@@ -2233,10 +2285,12 @@
# Create a temp XML::Writer object and use it to write the rich string
# XML to a string.
writer = Package::XMLWriterSimple.new
fragments, length = rich_strings_fragments(rich_strings)
+ # can't allow 2 formats in a row
+ return -4 unless fragments
# If the first token is a string start the <r> element.
writer.start_tag('r') if !fragments[0].respond_to?(:xf_index)
# Write the XML elements for the format string fragments.
@@ -2419,10 +2473,18 @@
# Remove array formula braces and the leading =.
formula.sub!(/^\{(.*)\}$/, '\1')
formula.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".
#
@@ -2537,11 +2599,11 @@
# alphanumeric characters are single quoted as follows 'Sales Data'!A1.
#
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, str = str, xf if str.respond_to?(:xf_index) || !xf.respond_to?(:xf_index)
raise WriteXLSXInsufficientArgumentError if [row, col, url].include?(nil)
link_type = 1
# Remove the URI scheme from internal links.
@@ -2553,11 +2615,11 @@
url.sub!(/^external:/, '')
link_type = 3
end
# The displayed string defaults to the url string.
- str ||= url
+ str ||= url.dup
# For external links change the directory separator from Unix to Dos.
if link_type == 3
url.gsub!(%r|/|, '\\')
str.gsub!(%r|/|, '\\')
@@ -2574,27 +2636,47 @@
index = shared_string_index(str[0, STR_MAX])
# External links to URLs and to other Excel workbooks have slightly
# different characteristics that we have to account for.
if link_type == 1
+ # Substiture white space in url.
+ url = url.sub(/[\s\x00]/, '%20')
+
# Ordinary URL style external links don't have a "location" string.
str = nil
elsif link_type == 3
# External Workbook links need to be modified into the right format.
# The URL will look something like 'c:\temp\file.xlsx#Sheet!A1'.
# We need the part to the left of the # as the URL and the part to
- # the right as the "location" string (if it exists)
+ # the right as the "location" string (if it exists).
url, str = url.split(/#/)
# Add the file:/// URI to the url if non-local.
-# url = "file:///#{url}" if url =~ m{[\\/]} && url !~ m{^\.\.}
+ if url =~ %r![:]! || # Windows style "C:/" link.
+ url =~ %r!^\\\\! # Network share.
+ url = "file:///#{url}"
+ end
+ # Convert a ./dir/file.xlsx link to dir/file.xlsx.
+ url = url.sub(%r!^.\\!, '')
# Treat as a default external link now that the data has been modified.
link_type = 1
end
+ # Excel limits escaped URL to 255 characters.
+ if url.bytesize > 255
+ raise "URL '#{url}' > 255 characters, it exceeds Excel's limit for URLS."
+ end
+
+ # Check the limit of URLS per worksheet.
+ @hlink_count += 1
+
+ if @hlink_count > 65_530
+ raise "URL '#{url}' added but number of URLS is over Excel's limit of 65,530 URLS per worksheet."
+ end
+
store_data_to_table(HyperlinkCellData.new(self, row, col, index, xf, link_type, url, str, tip))
end
#
# :call-seq:
@@ -2652,11 +2734,11 @@
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) unless date_time
+ write_string(*args)
end
end
#
# :call-seq:
@@ -2704,17 +2786,28 @@
x_offset ||= 0
y_offset ||= 0
scale_x ||= 1
scale_y ||= 1
- raise "Not a Chart object in insert_chart()" unless chart.is_a?(Chart)
- raise "Not a embedded style Chart object in insert_chart()" if chart.embedded == 0
+ 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
@charts << [row, col, chart, x_offset, y_offset, scale_x, scale_y]
end
#
+ # Sort the worksheet charts into the order that they were created in rather
+ # than the insertion order. This is ensure that the chart and drawing objects
+ # written in the same order. The chart id is used to sort back into creation
+ # order.
+ #
+ def sort_charts
+ return if @charts.size < 2
+ @charts = @charts.sort {|a, b| a[2].id <=> b[2].id}
+ end
+
+ #
# :call-seq:
# insert_image(row, column, filename [ , x, y, scale_x, scale_y ] )
#
# Partially supported. Currently only works for 96 dpi images. This
# will be fixed in an upcoming release.
@@ -2758,11 +2851,11 @@
#++
#
def insert_image(*args)
# Check for a cell reference in A1 notation and substitute row and column.
row, col, image, x_offset, y_offset, scale_x, scale_y = row_col_notation(args)
- raise WriteXLSXInsufficientArgumentError if [row, col, chart].include?(nil)
+ raise WriteXLSXInsufficientArgumentError if [row, col, image].include?(nil)
x_offset ||= 0
y_offset ||= 0
scale_x ||= 1
scale_y ||= 1
@@ -2932,11 +3025,17 @@
days -= leap # Already counted above
# Adjust for Excel erroneously treating 1900 as a leap year.
days += 1 if !date_1904? and days > 59
- days + seconds
+ date_time = sprintf("%0.10f", days + seconds)
+ date_time = date_time.sub(/\.?0+$/, '') if date_time =~ /\./
+ if date_time =~ /\./
+ date_time.to_f
+ else
+ date_time.to_i
+ end
end
#
# :call-seq:
# set_row(row [ , height, format, hidden, level, collapsed ] )
@@ -3007,13 +3106,16 @@
level = args[4] || 0
collapsed = args[5] || 0
return if row.nil?
+ # 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, 0)
- store_row_col_max_min_values(row, 0)
+ check_dimensions(row, min_col)
+ store_row_col_max_min_values(row, min_col)
# If the height is 0 the row is hidden and the height is the default.
if height == 0
hidden = 1
height = 15
@@ -3151,10 +3253,14 @@
#
# See also the conditional_format.rb program in the examples directory of the distro
#
def conditional_formatting(*args)
# Check for a cell reference in A1 notation and substitute row and column
+ if args[0] =~ /^\D/
+ # Check for a user defined multiple range like B3:K6,B8:K11.
+ user_range = args[0].gsub(/\s*,\s*/, ' ').gsub(/\$/, '') if args[0] =~ /,/
+ end
row1, col1, row2, col2, param = row_col_notation(args)
if row2.respond_to?(:keys)
param = row2
row2, col2 = row1, col1
end
@@ -3163,31 +3269,374 @@
# Check that row and col are valid without storing the values.
check_dimensions(row1, col1)
check_dimensions(row2, col2)
check_conditional_formatting_parameters(param)
- param[:format] = param[:format].get_dxf_index if param[:format]
- param[:priority] = @dxf_priority
- @dxf_priority += 1
-
# Swap last row/col for first row/col as necessary
row1, row2 = row2, row1 if row1 > row2
col1, col2 = col2, col1 if col1 > col2
# If the first and last cell are the same write a single cell.
if row1 == row2 && col1 == col2
range = xl_rowcol_to_cell(row1, col1)
+ start_cell = range
else
range = xl_range(row1, row2, col1, col2)
+ start_cell = xl_rowcol_to_cell(row1, col1)
end
+ # Override with user defined multiple range if provided.
+ range = user_range if user_range
+
+ param[:format] = param[:format].get_dxf_index if param[:format]
+ param[:priority] = @dxf_priority
+ @dxf_priority += 1
+
+ # Special handling of text criteria.
+ if param[:type] == 'text'
+ case param[:criteria]
+ when 'containsText'
+ param[:type] = 'containsText';
+ param[:formula] = %Q!NOT(ISERROR(SEARCH("#{param[:value]}",#{start_cell})))!
+ when 'notContains'
+ param[:type] = 'notContainsText';
+ param[:formula] = %Q!ISERROR(SEARCH("#{param[:value]}",#{start_cell}))!
+ when 'beginsWith'
+ param[:type] = 'beginsWith'
+ param[:formula] = %Q!LEFT(#{start_cell},1)="#{param[:value]}"!
+ when 'endsWith'
+ param[:type] = 'endsWith'
+ param[:formula] = %Q!RIGHT(#{start_cell},1)="#{param[:value]}"!
+ else
+ raise "Invalid text criteria '#{param[:criteria]} in conditional_formatting()"
+ end
+ end
+
+ # Special handling of time time_period criteria.
+ if param[:type] == 'timePeriod'
+ case param[:criteria]
+ when 'yesterday'
+ param[:formula] = "FLOOR(#{start_cell},1)=TODAY()-1"
+ when 'today'
+ param[:formula] = "FLOOR(#{start_cell},1)=TODAY()"
+ when 'tomorrow'
+ param[:formula] = "FLOOR(#{start_cell},1)=TODAY()+1"
+ when 'last7Days'
+ param[:formula] =
+ "AND(TODAY()-FLOOR(#{start_cell},1)<=6,FLOOR(#{start_cell},1)<=TODAY())"
+ when 'lastWeek'
+ param[:formula] =
+ "AND(TODAY()-ROUNDDOWN(#{start_cell},0)>=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN(#{start_cell},0)<(WEEKDAY(TODAY())+7))"
+ when 'thisWeek'
+ param[:formula] =
+ "AND(TODAY()-ROUNDDOWN(#{start_cell},0)<=WEEKDAY(TODAY())-1,ROUNDDOWN(#{start_cell},0)-TODAY()<=7-WEEKDAY(TODAY()))"
+ when 'nextWeek'
+ param[:formula] =
+ "AND(ROUNDDOWN(#{start_cell},0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN(#{start_cell},0)-TODAY()<(15-WEEKDAY(TODAY())))"
+ when 'lastMonth'
+ param[:formula] =
+ "AND(MONTH(#{start_cell})=MONTH(TODAY())-1,OR(YEAR(#{start_cell})=YEAR(TODAY()),AND(MONTH(#{start_cell})=1,YEAR(A1)=YEAR(TODAY())-1)))"
+ when 'thisMonth'
+ param[:formula] =
+ "AND(MONTH(#{start_cell})=MONTH(TODAY()),YEAR(#{start_cell})=YEAR(TODAY()))"
+ when 'nextMonth'
+ param[:formula] =
+ "AND(MONTH(#{start_cell})=MONTH(TODAY())+1,OR(YEAR(#{start_cell})=YEAR(TODAY()),AND(MONTH(#{start_cell})=12,YEAR(#{start_cell})=YEAR(TODAY())+1)))"
+ else
+ raise "Invalid time_period criteria '#{param[:criteria]}' in conditional_formatting()"
+ end
+ end
+
+ # Special handling of blanks/error types.
+ case param[:type]
+ when 'containsBlanks'
+ param[:formula] = "LEN(TRIM(#{start_cell}))=0"
+ when 'notContainsBlanks'
+ param[:formula] = "LEN(TRIM(#{start_cell}))>0"
+ when 'containsErrors'
+ param[:formula] = "ISERROR(#{start_cell})"
+ when 'notContainsErrors'
+ param[:formula] = "NOT(ISERROR(#{start_cell}))"
+ when '2_color_scale'
+ param[:type] = 'colorScale'
+
+ # Color scales don't use any additional formatting.
+ param[:format] = nil
+
+ # Turn off 3 color parameters.
+ param[:mid_type] = nil
+ param[:mid_color] = nil
+
+ param[:min_type] ||= 'min'
+ param[:max_type] ||= 'max'
+ param[:min_value] ||= 0
+ param[:max_value] ||= 0
+ param[:min_color] ||= '#FF7128'
+ param[:max_color] ||= '#FFEF9C'
+
+ param[:max_color] = get_palette_color( param[:max_color] )
+ param[:min_color] = get_palette_color( param[:min_color] )
+ when '3_color_scale'
+ param[:type] = 'colorScale'
+
+ # Color scales don't use any additional formatting.
+ param[:format] = nil
+
+ param[:min_type] ||= 'min'
+ param[:mid_type] ||= 'percentile'
+ param[:max_type] ||= 'max'
+ param[:min_value] ||= 0
+ param[:mid_value] ||= 50
+ param[:max_value] ||= 0
+ param[:min_color] ||= '#F8696B'
+ param[:mid_color] ||= '#FFEB84'
+ param[:max_color] ||= '#63BE7B'
+
+ param[:max_color] = get_palette_color(param[:max_color])
+ param[:mid_color] = get_palette_color(param[:mid_color])
+ param[:min_color] = get_palette_color(param[:min_color])
+ when 'dataBar'
+ # Color scales don't use any additional formatting.
+ param[:format] = nil
+
+ param[:min_type] ||= 'min'
+ param[:max_type] ||= 'max'
+ param[:min_value] ||= 0
+ param[:max_value] ||= 0
+ param[:bar_color] ||= '#638EC6'
+
+ param[:bar_color] = get_palette_color(param[:bar_color])
+ end
+
# Store the validation information until we close the worksheet.
@cond_formats[range] ||= []
@cond_formats[range] << param
end
#
+ # Add an Excel table to a worksheet.
+ #
+ # The add_table() method is used to group a range of cells into
+ # an Excel Table.
+ #
+ # worksheet.add_table('B3:F7', { ... } )
+ #
+ # This method contains a lot of parameters and is described
+ # in detail in a separate section "TABLES IN EXCEL".
+ #
+ # See also the tables.rb program in the examples directory of the distro
+ #
+ def add_table(*args)
+ col_formats = []
+=begin
+ # We would need to order the write statements very carefully within this
+ # function to support optimisation mode. Disable add_table() when it is
+ # on for now.
+ if @optimization
+ carp "add_table() isn't supported when set_optimization() is on"
+ return -1
+ end
+=end
+ # Check for a cell reference in A1 notation and substitute row and column
+ row1, col1, row2, col2, param = row_col_notation(args)
+
+ # Check for a valid number of args.
+ raise "Not enough parameters to add_table()" if [row1, col1, row2, col2].include?(nil)
+
+ # Check that row and col are valid without storing the values.
+ check_dimensions_and_update_max_min_values(row1, col1, 1, 1)
+ check_dimensions_and_update_max_min_values(row2, col2, 1, 1)
+
+ # The final hashref contains the validation parameters.
+ param ||= {}
+
+ check_parameter(param, valid_table_parameter, 'add_table')
+
+ # Table count is a member of Workbook, global to all Worksheet.
+ @workbook.table_count += 1
+ table = {}
+ table[:_columns] = []
+ table[:id] = @workbook.table_count
+
+ # Turn on Excel's defaults.
+ param[:banded_rows] ||= 1
+ param[:header_row] ||= 1
+ param[:autofilter] ||= 1
+
+ # Set the table options.
+ table[:_show_first_col] = ptrue?(param[:first_column]) ? 1 : 0
+ table[:_show_last_col] = ptrue?(param[:last_column]) ? 1 : 0
+ table[:_show_row_stripes] = ptrue?(param[:banded_rows]) ? 1 : 0
+ table[:_show_col_stripes] = ptrue?(param[:banded_columns]) ? 1 : 0
+ table[:_header_row_count] = ptrue?(param[:header_row]) ? 1 : 0
+ table[:_totals_row_shown] = ptrue?(param[:total_row]) ? 1 : 0
+
+ # Set the table name.
+ if param[:name]
+ table[:_name] = param[:name]
+ else
+ # Set a default name.
+ table[:_name] = "Table#{table[:id]}"
+ end
+
+ # Set the table style.
+ if param[:style]
+ table[:_style] = param[:style]
+ # Remove whitespace from style name.
+ table[:_style].gsub!(/\s/, '')
+ else
+ table[:_style] = "TableStyleMedium9"
+ end
+
+ # Swap last row/col for first row/col as necessary.
+ row1, row2 = row2, row1 if row1 > row2
+ col1, col2 = col2, col1 if col1 > col2
+
+ # Set the data range rows (without the header and footer).
+ first_data_row = row1
+ last_data_row = row2
+ first_data_row += 1 if param[:header_row] != 0
+ last_data_row -= 1 if param[:total_row]
+
+ # Set the table and autofilter ranges.
+ table[:_range] = xl_range(row1, row2, col1, col2)
+ table[:_a_range] = xl_range(row1, last_data_row, col1, col2)
+
+ # If the header row if off the default is to turn autofilter off.
+ param[:autofilter] = 0 if param[:header_row] == 0
+
+ # Set the autofilter range.
+ if param[:autofilter] && param[:autofilter] != 0
+ table[:_autofilter] = table[:_a_range]
+ end
+
+ # Add the table columns.
+ col_id = 1
+ (col1..col2).each do |col_num|
+ # Set up the default column data.
+ col_data = {
+ :_id => col_id,
+ :_name => "Column#{col_id}",
+ :_total_string => '',
+ :_total_function => '',
+ :_formula => '',
+ :_format => nil
+ }
+
+ # Overwrite the defaults with any use defined values.
+ if param[:columns]
+ # Check if there are user defined values for this column.
+ if user_data = param[:columns][col_id - 1]
+ # Map user defined values to internal values.
+ if user_data[:header] && !user_data[:header].empty?
+ col_data[:_name] = user_data[:header]
+ end
+ # Handle the column formula.
+ if user_data[:formula]
+ formula = user_data[:formula]
+ # Remove the leading = from formula.
+ formula.sub!(/^=/, '')
+ # Covert Excel 2010 "@" ref to 2007 "#This Row".
+ formula.gsub!(/@/,'[#This Row],')
+
+ col_data[:_formula] = formula
+
+ (first_data_row..last_data_row).each do |row|
+ write_formula(row, col_num, formula, user_data[:format])
+ end
+ end
+
+ # Handle the function for the total row.
+ if user_data[:total_function]
+ function = user_data[:total_function]
+
+ # Massage the function name.
+ function = function.downcase
+ function.gsub!(/_/, '')
+ function.gsub!(/\s/,'')
+
+ function = 'countNums' if function == 'countnums'
+ function = 'stdDev' if function == 'stddev'
+
+ col_data[:_total_function] = function
+
+ formula = table_function_to_formula(function, col_data[:_name])
+ write_formula(row2, col_num, formula, user_data[:format])
+ elsif user_data[:total_string]
+ # Total label only (not a function).
+ total_string = user_data[:total_string]
+ col_data[:_total_string] = total_string
+
+ write_string(row2, col_num, total_string, user_data[:format])
+ end
+
+ # Get the dxf format index.
+ if user_data[:format]
+ col_data[:_format] = user_data[:format].get_dxf_index
+ end
+
+ # Store the column format for writing the cell data.
+ # It doesn't matter if it is undefined.
+ col_formats[col_id - 1] = user_data[:format]
+ end
+ end
+
+ # Store the column data.
+ table[:_columns] << col_data
+
+ # Write the column headers to the worksheet.
+ if param[:header_row] != 0
+ write_string(row1, col_num, col_data[:_name])
+ end
+
+ col_id += 1
+ end # Table columns.
+
+ # Write the cell data if supplied.
+ if data = param[:data]
+
+ i = 0 # For indexing the row data.
+ (first_data_row..last_data_row).each do |row|
+ next unless data[i]
+
+ j = 0 # For indexing the col data.
+ (col1..col2).each do |col|
+ token = data[i][j]
+ write(row, col, token, col_formats[j]) if token
+ j += 1
+ end
+ i += 1
+ end
+ end
+
+ # Store the table data.
+ @tables << table
+
+ # Store the link used for the rels file.
+ @external_table_links << ['/table', "../tables/table#{table[:id]}.xml"]
+
+ return table
+ end
+
+ # List of valid input parameters.
+ def valid_table_parameter
+ [
+ :autofilter,
+ :banded_columns,
+ :banded_rows,
+ :columns,
+ :data,
+ :first_column,
+ :header_row,
+ :last_column,
+ :name,
+ :style,
+ :total_row
+ ]
+ 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
@@ -3659,18 +4108,18 @@
# 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 = true)
- if option == true
- @print_gridlines = false
- @screen_gridlines = true
- elsif !option
+ def hide_gridlines(option = 1)
+ if option == 0 || !option
@print_gridlines = true # 1 = display, 0 = hide
@screen_gridlines = true
@print_options_changed = true
+ elsif option == 1
+ @print_gridlines = false
+ @screen_gridlines = true
else
@print_gridlines = false
@screen_gridlines = false
end
end
@@ -3969,11 +4418,14 @@
#
# 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)
- @print_style.hbreaks += args
+ breaks = args.collect do |brk|
+ brk.respond_to?(:to_a) ? brk.to_a : brk
+ end.flatten
+ @print_style.hbreaks += breaks
end
#
# Store the vertical page breaks on a worksheet.
#
@@ -4067,13 +4519,17 @@
@vml_data_id = vml_data_id
count
end
+ def set_external_vml_links(comment_id) # :nodoc:
+ @external_vml_links <<
+ ['/vmlDrawing', "../drawings/vmlDrawing#{comment_id}.vml"]
+ end
+
def set_external_comment_links(comment_id) # :nodoc:
@external_comment_links <<
- ['/vmlDrawing', "../drawings/vmlDrawing#{comment_id}.vml"] <<
['/comments', "../comments#{comment_id}.xml"]
end
#
# Set up chart/drawings.
@@ -4088,21 +4544,24 @@
width = (0.5 + (480 * scale_x)).to_i
height = (0.5 + (288 * scale_y)).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)
+ 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)
+ @drawing.add_drawing_object(drawing_type, dimensions, 0, 0, name)
end
@drawing_links << ['/chart', "../charts/chart#{chart_id}.xml"]
end
#
@@ -4222,11 +4681,11 @@
# Subtract the underlying cell heights to find the end cell of the object.
height, row_end = adjust_row_offset(height, row_end)
# The following is only required for positioning drawing/chart objects
# and not comments. It is probably the result of a bug.
- if is_drawing
+ if ptrue?(is_drawing)
col_end -= 1 if width == 0
row_end -= 1 if height == 0
end
# The end vertices are whatever is left from the width and height.
@@ -4277,16 +4736,34 @@
@writer.data_element('f', formula, attributes)
end
private
- def check_for_valid_input_params(param)
- param.each_key do |param_key|
- unless valid_validation_parameter.include?(param_key)
- raise WriteXLSXOptionParameterError, "Unknown parameter '#{param_key}' in data_validation()"
- end
+ #
+ # Convert a table total function to a worksheet formula.
+ #
+ def table_function_to_formula(function, col_name)
+ subtotals = {
+ :average => 101,
+ :countNums => 102,
+ :count => 103,
+ :max => 104,
+ :min => 105,
+ :stdDev => 107,
+ :sum => 109,
+ :var => 110
+ }
+
+ unless func_num = subtotals[function.to_sym]
+ raise "Unsupported function '#{function}' in add_table()"
end
+ "SUBTOTAL(#{func_num},[#{col_name}])"
+ end
+
+ def check_for_valid_input_params(param)
+ check_parameter(param, valid_validation_parameter, 'data_validation')
+
unless param.has_key?(:validate)
raise WriteXLSXOptionParameterError, "Parameter :validate is required in data_validation()"
end
unless valid_validation_type.has_key?(param[:validate].downcase)
raise WriteXLSXOptionParameterError,
@@ -4392,11 +4869,12 @@
pos = 0
fragments = []
rich_strings.each do |token|
if token.respond_to?(:xf_index)
- raise AugumentError, "Can't allow 2 formats in a row" if last == 'format' && pos > 0
+ # 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
@@ -4416,29 +4894,38 @@
end
[fragments, length]
end
def check_conditional_formatting_parameters(param) # :nodoc:
- # List of valid validation types.
- valid_type = { 'cell' => 'cellIs' }
-
# Check for valid input parameters.
- unless (param.keys.uniq - [:type, :format, :criteria, :value, :minimum, :maximum]).empty? ||
- param.has_key?(:type) ||
- param.has_key?(:criteria) ||
- valid_type.has_key?(param[:type].downcase) ||
- valid_criteria_type.has_key?(param[:criteria].downcase)
- raise WriteXLSXOptionParameterError
+ unless (param.keys.uniq - valid_parameter_for_conditional_formatting).empty? &&
+ param.has_key?(:type) &&
+ valid_type_for_conditional_formatting.has_key?(param[:type].downcase)
+ raise WriteXLSXOptionParameterError, "Invalid type : #{param[:type]}"
end
- param[:type] = valid_type[param[:type].downcase]
- param[:criteria] = valid_criteria_type[param[:criteria].downcase]
+ param[:direction] = 'bottom' if param[:type] == 'bottom'
+ param[:type] = valid_type_for_conditional_formatting[param[:type].downcase]
+ # Check for valid criteria types.
+ if param.has_key?(:criteria) && valid_criteria_type_for_conditional_formatting.has_key?(param[:criteria].downcase)
+ param[:criteria] = valid_criteria_type_for_conditional_formatting[param[:criteria].downcase]
+ end
+
+ # Convert date/times value if required.
+ if %w[date time cellIs].include?(param[:type])
+ param[:type] = 'cellIs'
+
+ param[:value] = convert_date_time_if_required(param[:value])
+ param[:minimum] = convert_date_time_if_required(param[:minimum])
+ param[:maximum] = convert_date_time_if_required(param[:maximum])
+ end
+
# 'Between' and 'Not between' criteria require 2 values.
if param[:criteria] == 'between' || param[:criteria] == 'notBetween'
unless param.has_key?(:minimum) || param.has_key?(:maximum)
- raise WriteXLSXOptionParameterError
+ raise WriteXLSXOptionParameterError, "Invalid criteria : #{param[:criteria]}"
end
else
param[:minimum] = nil
param[:maximum] = nil
end
@@ -4449,10 +4936,100 @@
raise WriteXLSXOptionParameterError
end
end
end
+ def convert_date_time_if_required(val)
+ if val =~ /T/
+ date_time = convert_date_time(val)
+ raise "Invalid date/time value '#{val}' in conditional_formatting()" unless date_time
+ date_time
+ else
+ val
+ end
+ end
+
+ # List of valid input parameters for conditional_formatting.
+ def valid_parameter_for_conditional_formatting
+ [
+ :type,
+ :format,
+ :criteria,
+ :value,
+ :minimum,
+ :maximum,
+ :min_type,
+ :mid_type,
+ :max_type,
+ :min_value,
+ :mid_value,
+ :max_value,
+ :min_color,
+ :mid_color,
+ :max_color,
+ :bar_color
+ ]
+ end
+
+ # List of valid validation types for conditional_formatting.
+ def valid_type_for_conditional_formatting
+ {
+ 'cell' => 'cellIs',
+ 'date' => 'date',
+ 'time' => 'time',
+ 'average' => 'aboveAverage',
+ 'duplicate' => 'duplicateValues',
+ 'unique' => 'uniqueValues',
+ 'top' => 'top10',
+ 'bottom' => 'top10',
+ 'text' => 'text',
+ 'time_period' => 'timePeriod',
+ 'blanks' => 'containsBlanks',
+ 'no_blanks' => 'notContainsBlanks',
+ 'errors' => 'containsErrors',
+ 'no_errors' => 'notContainsErrors',
+ '2_color_scale' => '2_color_scale',
+ '3_color_scale' => '3_color_scale',
+ 'data_bar' => 'dataBar',
+ 'formula' => 'expression'
+ }
+ end
+
+ # List of valid criteria types for conditional_formatting.
+ def valid_criteria_type_for_conditional_formatting
+ {
+ 'between' => 'between',
+ 'not between' => 'notBetween',
+ 'equal to' => 'equal',
+ '=' => 'equal',
+ '==' => 'equal',
+ 'not equal to' => 'notEqual',
+ '!=' => 'notEqual',
+ '<>' => 'notEqual',
+ 'greater than' => 'greaterThan',
+ '>' => 'greaterThan',
+ 'less than' => 'lessThan',
+ '<' => 'lessThan',
+ 'greater than or equal to' => 'greaterThanOrEqual',
+ '>=' => 'greaterThanOrEqual',
+ 'less than or equal to' => 'lessThanOrEqual',
+ '<=' => 'lessThanOrEqual',
+ 'containing' => 'containsText',
+ 'not containing' => 'notContains',
+ 'begins with' => 'beginsWith',
+ 'ends with' => 'endsWith',
+ 'yesterday' => 'yesterday',
+ 'today' => 'today',
+ 'last 7 days' => 'last7Days',
+ 'last week' => 'lastWeek',
+ 'this week' => 'thisWeek',
+ 'next week' => 'nextWeek',
+ 'last month' => 'lastMonth',
+ 'this month' => 'thisMonth',
+ 'next month' => 'nextMonth'
+ }
+ 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
@@ -4623,10 +5200,14 @@
#
# 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]
@@ -4696,10 +5277,48 @@
[col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs]
end
#
+ # Calculate the vertices that define the position of a shape object within
+ # the worksheet in EMUs. Save the vertices with the object.
+ #
+ # 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_shape_emus(shape)
+ col_start, row_start, x1, y1, col_end, row_end, x2, y2, x_abs, y_abs =
+ position_object_pixels(
+ shape[:column_start],
+ shape[:row_start],
+ shape[:x_offset],
+ shape[:y_offset],
+ shape[:width] * shape[:scale_x],
+ shape[:height] * shape[:scale_y],
+ shape[:drawing]
+ )
+
+ # Now that x2/y2 have been calculated with a potentially negative
+ # width/height we use the absolute value and convert to EMUs.
+ shape[:width_emu] = (shape[:width] * 9_525).abs.to_i
+ shape[:height_emu] = (shape[:height] * 9_525).abs.to_i
+
+ shape[:column_start] = col_start.to_i
+ shape[:row_start] = row_start.to_i
+ shape[:column_end] = col_end.to_i
+ shape[:row_end] = row_end.to_i
+
+ # Convert the pixel values to EMUs. See above.
+ shape[:x1] = (x1 * 9_525).to_i
+ shape[:y1] = (y1 * 9_525).to_i
+ shape[:x2] = (x2 * 9_525).to_i
+ shape[:y2] = (y2 * 9_525).to_i
+ shape[:x_abs] = (x_abs * 9_525).to_i
+ shape[:y_abs] = (y_abs * 9_525).to_i
+ 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:
@@ -4758,12 +5377,12 @@
height *= scale_y
dimensions = position_object_emus(col, row, x_offset, y_offset, width, height)
# Convert from pixels to emus.
- width = int(0.5 + (width * 9_525))
- height = int(0.5 + (height * 9_525))
+ 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
@@ -4777,12 +5396,240 @@
drawing.add_drawing_object(drawing_type, dimensions, width, height, name)
@drawing_links << ['/image', "../media/image#{image_id}.#{image_type}"]
end
+ public :prepare_image
#
+ # 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 Excel::Writer::XLSX::Shape for details on how to configure it.
+ #
+ # The x, y, scale_x and scale_y parameters are optional.
+ #
+ # The parameters x and y can be used to specify an offset
+ # from the top left hand corner of the cell specified by row and col.
+ # The offset values are in pixels.
+ #
+ # worksheet1.insert_shape('E2', chart, 3, 3)
+ #
+ # The parameters scale_x and scale_y 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*.pl 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, scale_x, scale_y =
+ row_col_notation(args)
+ if [row_start, column_start, shape].include?(nil)
+ raise "Insufficient arguments in insert_shape()"
+ end
+
+ # Set the shape properties
+ shape[:row_start] = row_start
+ shape[:column_start] = column_start
+ shape[:x_offset] = x_offset || 0
+ shape[:y_offset] = y_offset || 0
+
+ # Override shape scale if supplied as an argument. Otherwise, use the
+ # existing shape scale factors.
+ shape[:scale_x] = scale_x if scale_x
+ shape[:scale_y] = scale_y if scale_y
+
+ # 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
+
+ shape[:element] = @shapes.size
+
+ # Allow lookup of entry into shape array by shape ID.
+ @shape_hash[shape[:id]] = shape[:element]
+
+ # Create link to Worksheet color palette.
+ shape[:palette] = @workbook.palette
+
+ 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
+
+ # For connectors change x/y coords based on location of connected shapes.
+ auto_locate_connectors(insert)
+
+ @shapes << insert
+ insert
+ else
+ # For connectors change x/y coords based on location of connected shapes.
+ auto_locate_connectors(shape)
+
+ # Insert a link to the shape on the list of shapes. Connection to
+ # the parent shape is maintained.
+ @shapes << shape
+ return shape
+ end
+ end
+ public :insert_shape
+
+ #
+ # Set up drawing shapes
+ #
+ def prepare_shape(index, drawing_id)
+ shape = @shapes[index]
+ drawing_type = 3
+
+ # 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"]
+ end
+
+ # Validate the he shape against various rules.
+ validate_shape(shape, index)
+ position_shape_emus(shape)
+
+ dimensions = [
+ shape[:column_start], shape[:row_start],
+ shape[:x1], shape[:y1],
+ shape[:column_end], shape[:row_end],
+ shape[:x2], shape[:y2],
+ shape[:x_abs], shape[:y_abs],
+ shape[:width_emu], shape[:height_emu]
+ ]
+
+ drawing.add_drawing_object(drawing_type, dimensions, shape[:name], shape)
+ end
+ public :prepare_shape
+
+ #
+ # Re-size connector shapes if they are connected to other shapes.
+ #
+ def auto_locate_connectors(shape)
+ # Valid connector shapes.
+ connector_shapes = {
+ :straightConnector => 1,
+ :Connector => 1,
+ :bentConnector => 1,
+ :curvedConnector => 1,
+ :line => 1
+ }
+
+ shape_base = shape[:type].chop.to_sym # Remove the number of segments from end of type.
+ shape[:connect] = connector_shapes[shape_base] ? 1 : 0
+ return if shape[:connect] == 0
+
+ # Both ends have to be connected to size it.
+ return if shape[:start] == 0 && shape[:end] == 0
+
+ # Both ends need to provide info about where to connect.
+ return if shape[:start_side] == 0 && shape[:end_side] == 0
+
+ sid = shape[:start]
+ eid = shape[:end]
+
+ slink_id = @shape_hash[sid] || 0
+ sls = @shapes.fetch(slink_id, Hash.new(0))
+ elink_id = @shape_hash[eid] || 0
+ els = @shapes.fetch(elink_id, Hash.new(0))
+
+ # Assume shape connections are to the middle of an object, and
+ # not a corner (for now).
+ connect_type = shape[:start_side] + shape[:end_side]
+ smidx = sls[:x_offset] + sls[:width] / 2
+ emidx = els[:x_offset] + els[:width] / 2
+ smidy = sls[:y_offset] + sls[:height] / 2
+ emidy = els[:y_offset] + els[:height] / 2
+ netx = (smidx - emidx).abs
+ nety = (smidy - emidy).abs
+
+ if connect_type == 'bt'
+ sy = sls[:y_offset] + sls[:height]
+ ey = els[:y_offset]
+
+ shape[:width] = (emidx - smidx).to_i.abs
+ shape[:x_offset] = [smidx, emidx].min.to_i
+ shape[:height] =
+ (els[:y_offset] - (sls[:y_offset] + sls[:height])).to_i.abs
+ shape[:y_offset] =
+ [sls[:y_offset] + sls[:height], els[:y_offset]].min.to_i
+ shape[:flip_h] = smidx < emidx ? 1 : 0
+ shape[:rotation] = 90
+
+ if sy > ey
+ shape[:flip_v] = 1
+
+ # Create 3 adjustments for an end shape vertically above a
+ # start shape. Adjustments count from the upper left object.
+ if shape[:adjustments].empty?
+ shape[:adjustments] = [-10, 50, 110]
+ end
+ shape[:type] = 'bentConnector5'
+ end
+ elsif connect_type == 'rl'
+ shape[:width] =
+ (els[:x_offset] - (sls[:x_offset] + sls[:width])).to_i.abs
+ shape[:height] = (emidy - smidy).to_i.abs
+ shape[:x_offset] =
+ [sls[:x_offset] + sls[:width], els[:x_offset]].min
+ shape[:y_offset] = [smidy, emidy].min
+
+ shape[:flip_h] = 1 if smidx < emidx && smidy > emidy
+ shape[:flip_h] = 1 if smidx > emidx && smidy < emidy
+
+ if smidx > emidx
+ # Create 3 adjustments for an end shape to the left of a
+ # start shape.
+ if shape[:adjustments].empty?
+ shape[:adjustments] = [-10, 50, 110]
+ end
+ shape[:type] = 'bentConnector5'
+ end
+ end
+ end
+
+ #
+ # Check shape attributes to ensure they are valid.
+ #
+ def validate_shape(shape, index)
+ unless %w[l ctr r just].include?(shape[:align])
+ raise "Shape #{index} (#{shape[:type]}) alignment (#{shape[:align]}) not in ['l', 'ctr', 'r', 'just']\n"
+ end
+
+ unless %w[t ctr b].include?(shape[:valign])
+ raise "Shape #{index} (#{shape[:type]}) vertical alignment (#{shape[:valign]}) not in ['t', 'ctr', 'v']\n"
+ end
+ end
+
+ #
# Based on the algorithm provided by Daniel Rentz of OpenOffice.
#
def encode_password(password) #:nodoc:
i = 0
chars = password.split(//)
@@ -4817,17 +5664,18 @@
#
# Write the <sheetPr> element for Sheet level properties.
#
def write_sheet_pr #:nodoc:
- return if !fit_page? && !filter_on? && !tab_color?
+ return if !fit_page? && !filter_on? && !tab_color? && !outline_changed?
attributes = []
(attributes << 'filterMode' << 1) if filter_on?
- if fit_page? || tab_color?
+ if fit_page? || tab_color? || outline_changed?
@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
@@ -4894,10 +5742,13 @@
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.
@@ -4986,10 +5837,11 @@
# Convert column width from user units to character width.
max_digit_width = 7.0 # For Calabri 11.
padding = 5.0
if width && width > 0
width = ((width * max_digit_width + padding) / max_digit_width * 256).to_i/256.0
+ width = width.to_i if width.to_s =~ /\.0+$/
end
attributes = [
'min', min + 1,
'max', max + 1,
'width', width
@@ -5037,10 +5889,12 @@
write_row_element(row_num, span, *(@set_rows[row_num]))
end
write_cell_column_dimension(row_num)
@writer.end_tag('row')
+ elsif @comments[row_num]
+ write_empty_row(row_num, span, *(@set_rows[row_num]))
else
# Row attributes only.
write_empty_row(row_num, nil, *(@set_rows[row_num]))
end
end
@@ -5105,16 +5959,16 @@
(attributes << 'spans' << spans) if spans
(attributes << 's' << xf_index) if xf_index != 0
(attributes << 'customFormat' << 1 ) if format
(attributes << 'ht' << height) if height != 15
- (attributes << 'hidden' << 1 ) if !!hidden && hidden != 0
+ (attributes << 'hidden' << 1 ) if ptrue?(hidden)
(attributes << 'customHeight' << 1 ) if height != 15
- (attributes << 'outlineLevel' << level) if !!level && level != 0
- (attributes << 'collapsed' << 1 ) if !!collapsed && collapsed != 0
+ (attributes << 'outlineLevel' << level) if ptrue?(level)
+ (attributes << 'collapsed' << 1 ) if ptrue?(collapsed)
- if empty_row && empty_row != 0
+ if ptrue?(empty_row)
@writer.empty_tag('row', attributes)
else
@writer.start_tag('row', attributes)
end
end
@@ -5371,21 +6225,25 @@
#
# Write the <mergeCells> element.
#
def write_merge_cells #:nodoc:
- return if @merge.empty?
-
- attributes = ['count', @merge.size]
-
- @writer.tag_elements('mergeCells', attributes) do
- # Write the mergeCell element.
+ 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?
+ attributes = ['count', container.size]
+
+ @writer.tag_elements(tag, attributes) do
+ yield
+ end
+ end
+
#
# Write the <mergeCell> element.
#
def write_merge_cell(merged_range) #:nodoc:
row_min, col_min, row_max, col_max = merged_range
@@ -5529,11 +6387,12 @@
# Retrieve the filter tokens and write the autofilter records.
tokens = @filter_cols[col]
type = @filter_type[col]
- write_filter_column(col, type, *tokens)
+ # Filters are relative to first column in the autofilter.
+ write_filter_column(col - col1, type, *tokens)
end
end
#
# Write the <filterColumn> element.
@@ -5688,46 +6547,64 @@
attributes = ['rgb', get_palette_color(@tab_color)]
@writer.empty_tag('tabColor', attributes)
end
#
+ # Write the <outlinePr> element.
+ #
+ def write_outline_pr
+ attributes = []
+
+ return unless outline_changed?
+
+ 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 <sheetProtection> element.
#
def write_sheet_protection #:nodoc:
return unless protect?
attributes = []
- attributes << "password" << @protect[:password] if @protect[:password]
- attributes << "sheet" << 1 if @protect[:sheet]
- attributes << "content" << 1 if @protect[:content]
- attributes << "objects" << 1 if !@protect[:objects]
- attributes << "scenarios" << 1 if !@protect[:scenarios]
- attributes << "formatCells" << 0 if @protect[:format_cells]
- attributes << "formatColumns" << 0 if @protect[:format_columns]
- attributes << "formatRows" << 0 if @protect[:format_rows]
- attributes << "insertColumns" << 0 if @protect[:insert_columns]
- attributes << "insertRows" << 0 if @protect[:insert_rows]
- attributes << "insertHyperlinks" << 0 if @protect[:insert_hyperlinks]
- attributes << "deleteColumns" << 0 if @protect[:delete_columns]
- attributes << "deleteRows" << 0 if @protect[:delete_rows]
+ 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 if !@protect[:select_locked_cells]
+ attributes << "selectLockedCells" << 1 unless ptrue?(@protect[:select_locked_cells])
- attributes << "sort" << 0 if @protect[:sort]
- attributes << "autoFilter" << 0 if @protect[:autofilter]
- attributes << "pivotTables" << 0 if @protect[:pivot_tables]
+ 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 if !@protect[:select_unlocked_cells]
+ attributes << "selectUnlockedCells" << 1 unless ptrue?(@protect[:select_unlocked_cells])
@writer.empty_tag('sheetProtection', attributes)
end
#
# Write the <drawing> elements.
#
def write_drawings #:nodoc:
- write_drawing(@hlink_count + 1) if drawing?
+ return unless drawing?
+ @rel_count += 1
+ write_drawing(@rel_count)
end
#
# Write the <drawing> element.
#
@@ -5744,12 +6621,12 @@
#
def write_legacy_drawing #:nodoc:
return unless has_comments?
# Increment the relationship id for any drawings or comments.
- id = @hlink_count + 1
- id += 1 if @drawing
+ @rel_count += 1
+ id = @rel_count
attributes = ['r:id', "rId#{id}"]
@writer.empty_tag('legacyDrawing', attributes)
end
@@ -5773,13 +6650,13 @@
writer.empty_tag('sz', ['val', format.size])
theme = format.theme
color = format.color
- if !theme.nil? && theme != 0
+ if ptrue?(theme)
write_color(writer, 'theme', theme)
- elsif !color.nil? && color != 0
+ elsif ptrue?(color)
color = get_palette_color(color)
write_color(writer, 'rgb', color)
else
write_color(writer, 'theme', 1)
end
@@ -5818,18 +6695,44 @@
writer.empty_tag('color', attributes)
end
#
- # Write the <dataValidations> element.
+ # Write the <tableParts> element.
#
- def write_data_validations #:nodoc:
- return if @validations.empty?
+ def write_table_parts
+ # Return if worksheet doesn't contain any tables.
+ return if @tables.empty?
- attributes = ['count', @validations.size]
+ attributes = ['count', @tables.size]
- @writer.tag_elements('dataValidations', attributes) do
+ @writer.tag_elements('tableParts', attributes) do
+
+ @tables.each do |table|
+ # Write the tablePart element.
+ @rel_count += 1
+ write_table_part(@rel_count)
+ end
+ end
+ end
+
+ #
+ # Write the <tablePart> element.
+ #
+ def write_table_part(id)
+ r_id = "rId#{id}"
+
+ attributes = ['r:id', r_id]
+
+ @writer.empty_tag('tablePart', attributes)
+ end
+
+ #
+ # Write the <dataValidations> element.
+ #
+ def write_data_validations #:nodoc:
+ write_some_elements('dataValidations', @validations) do
@validations.each { |validation| write_data_validation(validation) }
end
end
#
@@ -5910,14 +6813,53 @@
end
# in Perl module : _write_formula()
#
def write_formula_tag(data) #:nodoc:
+ data = data.sub(/^=/, '') if data.respond_to?(:sub)
@writer.data_element('formula', data)
end
#
+ # Write the <colorScale> element.
+ #
+ def write_color_scale(param)
+ @writer.tag_elements('colorScale') do
+ write_cfvo(param[:min_type], param[:min_value])
+ write_cfvo(param[:mid_type], param[:mid_value]) if param[:mid_type]
+ write_cfvo(param[:max_type], param[:max_value])
+ write_color(@writer, 'rgb', param[:min_color])
+ write_color(@writer, 'rgb', param[:mid_color]) if param[:mid_color]
+ write_color(@writer, 'rgb', param[:max_color])
+ end
+ end
+
+ #
+ # Write the <dataBar> element.
+ #
+ def write_data_bar(param)
+ @writer.tag_elements('dataBar') do
+ write_cfvo(param[:min_type], param[:min_value])
+ write_cfvo(param[:max_type], param[:max_value])
+
+ write_color(@writer, 'rgb', param[:bar_color])
+ end
+ end
+
+ #
+ # Write the <cfvo> element.
+ #
+ def write_cfvo(type, val)
+ attributes = [
+ 'type', type,
+ 'val', val
+ ]
+
+ @writer.empty_tag('cfvo', attributes)
+ end
+
+ #
# Write the Worksheet conditional formats.
#
def write_conditional_formats #:nodoc:
ranges = @cond_formats.keys.sort
return if ranges.empty?
@@ -5926,10 +6868,27 @@
end
#
# Write the <conditionalFormatting> element.
#
+ # The conditional_formatting() method is used to add formatting
+ # to a cell or range of cells based on user defined criteria.
+ #
+ # worksheet.conditional_formatting('A1:J10',
+ # {
+ # :type => 'cell',
+ # :criteria => '>=',
+ # :value => 50,
+ # :format => format1
+ # }
+ # )
+ # This method contains a lot of parameters and is described
+ # in detail in a separate section "CONDITIONAL FORMATTING IN EXCEL".
+ #
+ # See also the conditional_format.rb program in the examples directory
+ # of the distro
+ #
def write_conditional_formatting(range, params) #:nodoc:
attributes = ['sqref', range]
@writer.tag_elements('conditionalFormatting', attributes) do
params.each { |param| write_cf_rule(param) }
@@ -5944,21 +6903,64 @@
if param[:format]
attributes << 'dxfId' << param[:format]
end
attributes << 'priority' << param[:priority]
- attributes << 'operator' << param[:criteria]
- @writer.tag_elements('cfRule', attributes) do
- if param[:type] == 'cellIs'
+ case param[:type]
+ when 'cellIs'
+ attributes << 'operator' << param[:criteria]
+ @writer.tag_elements('cfRule', attributes) do
if param[:minimum] && param[:maximum]
write_formula_tag(param[:minimum])
write_formula_tag(param[:maximum])
else
write_formula_tag(param[:value])
end
end
+ when 'aboveAverage'
+ attributes << 'aboveAverage' << 0 if param[:criteria] =~ /below/
+ attributes << 'equalAverage' << 1 if param[:criteria] =~ /equal/
+ if param[:criteria] =~ /([123]) std dev/
+ attributes << 'stdDev' << $~[1]
+ end
+ @writer.empty_tag('cfRule', attributes)
+ when 'top10'
+ attributes << 'percent' << 1 if param[:criteria] == '%'
+ attributes << 'bottom' << 1 if param[:direction]
+ rank = param[:value] || 10
+ attributes << 'rank' << rank
+ @writer.empty_tag('cfRule', attributes)
+ when 'duplicateValues', 'uniqueValues'
+ @writer.empty_tag('cfRule', attributes)
+ when 'containsText', 'notContainsText', 'beginsWith', 'endsWith'
+ attributes << 'operator' << param[:criteria]
+ attributes << 'text' << param[:value]
+ @writer.tag_elements('cfRule', attributes) do
+ write_formula_tag(param[:formula])
+ end
+ when 'timePeriod'
+ attributes << 'timePeriod' << param[:criteria]
+ @writer.tag_elements('cfRule', attributes) do
+ write_formula_tag(param[:formula])
+ end
+ when 'containsBlanks', 'notContainsBlanks', 'containsErrors', 'notContainsErrors'
+ @writer.tag_elements('cfRule', attributes) do
+ write_formula_tag(param[:formula])
+ end
+ when 'colorScale'
+ @writer.tag_elements('cfRule', attributes) do
+ write_color_scale(param)
+ end
+ when 'dataBar'
+ @writer.tag_elements('cfRule', attributes) do
+ write_data_bar(param)
+ end
+ when 'expression'
+ @writer.tag_elements('cfRule', attributes) do
+ write_formula_tag(param[:criteria])
+ end
end
end
def store_data_to_table(cell_data) #:nodoc:
row, col = cell_data.row, cell_data.col
@@ -5979,11 +6981,11 @@
end
end
#
# Check that row and col are valid and store max and min values for use in
- # DIMENSIONS record. See, store_dimensions().
+ # other methods/elements.
#
# The ignore_row/ignore_col flags is used to indicate that we wish to
# perform the dimension check without storing the value.
#
# The ignore flags are use by set_row() and data_validate.
@@ -6027,25 +7029,21 @@
#
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]
- (@dim_colmin .. @dim_colmax).each do |col_num|
- if @cell_data_table[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 = 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
@@ -6056,10 +7054,25 @@
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
+
def xf(format) #:nodoc:
if format.kind_of?(Format)
format.xf_index
else
0
@@ -6129,26 +7142,22 @@
def fit_page? #:nodoc:
@print_style.fit_page
end
def filter_on? #:nodoc:
- if @filter_on
- @filter_on != 0
- else
- false
- end
+ ptrue?(@filter_on)
end
def tab_color? #:nodoc:
- if @tab_color
- @tab_color != 0
- else
- false
- end
+ ptrue?(@tab_color)
end
+ def outline_changed?
+ ptrue?(@outline_changed)
+ end
+
def zoom_scale_normal? #:nodoc:
- !!@zoom_scale_normal
+ ptrue?(@zoom_scale_normal)
end
def page_view? #:nodoc:
!!@page_view
end