# -*- coding: utf-8 -*-
# frozen_string_literal: true
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'
require 'date'
module Writexlsx
class Worksheet
include Writexlsx::Utility
MAX_DIGIT_WIDTH = 7 # For Calabri 11. # :nodoc:
PADDING = 5 # :nodoc:
COLINFO = Struct.new('ColInfo', :width, :format, :hidden, :level, :collapsed, :autofit)
attr_reader :index # :nodoc:
attr_reader :charts, :images, :tables, :shapes, :drawings # :nodoc:
attr_reader :header_images, :footer_images, :background_image # :nodoc:
attr_reader :vml_drawing_links # :nodoc:
attr_reader :vml_data_id # :nodoc:
attr_reader :vml_header_id # :nodoc:
attr_reader :autofilter_area # :nodoc:
attr_reader :writer, :set_rows, :col_info # :nodoc:
attr_reader :vml_shape_id # :nodoc:
attr_reader :comments, :comments_author # :nodoc:
attr_accessor :data_bars_2010, :dxf_priority # :nodoc:
attr_reader :vba_codename # :nodoc:
attr_writer :excel_version # :nodoc:
attr_reader :filter_cells # :nodoc:
def initialize(workbook, index, name) # :nodoc:
rowmax = 1_048_576
colmax = 16_384
strmax = 32_767
@writer = Package::XMLWriterSimple.new
@workbook = workbook
@index = index
@name = name
@col_info = {}
@cell_data_table = []
@excel_version = 2007
@palette = workbook.palette
@default_url_format = workbook.default_url_format
@max_url_length = workbook.max_url_length
@page_setup = PageSetup.new
@screen_gridlines = true
@show_zeros = true
@xls_rowmax = rowmax
@xls_colmax = colmax
@xls_strmax = strmax
@dim_rowmin = nil
@dim_rowmax = nil
@dim_colmin = nil
@dim_colmax = nil
@selections = []
@panes = []
@hide_row_col_headers = 0
@top_left_cell = ''
@tab_color = 0
@set_cols = {}
@set_rows = {}
@zoom = 100
@zoom_scale_normal = true
@right_to_left = false
@leading_zeros = false
@autofilter_area = nil
@filter_on = false
@filter_range = []
@filter_cols = {}
@filter_cells = {}
@filter_type = {}
@row_sizes = {}
@last_shape_id = 1
@rel_count = 0
@hlink_count = 0
@external_hyper_links = []
@external_drawing_links = []
@external_comment_links = []
@external_vml_links = []
@external_background_links = []
@external_table_links = []
@drawing_links = []
@vml_drawing_links = []
@charts = []
@images = []
@tables = []
@sparklines = []
@shapes = []
@shape_hash = {}
@drawing_rels = {}
@drawing_rels_id = 0
@vml_drawing_rels = {}
@vml_drawing_rels_id = 0
@has_dynamic_functions = false
@has_embedded_images = false
@use_future_functions = false
@header_images = []
@footer_images = []
@background_image = ''
@outline_row_level = 0
@outline_col_level = 0
@original_row_height = 15
@default_row_height = 15
@default_row_pixels = 20
@default_col_width = 8.43
@default_col_pixels = 64
@default_row_rezoed = 0
@default_date_pixels = 68
@merge = []
@has_vml = false
@has_header_vml = false
@comments = Package::Comments.new(self)
@buttons_array = []
@header_images_array = []
@ignore_errors = nil
@validations = []
@cond_formats = {}
@data_bars_2010 = []
@dxf_priority = 1
@protected_ranges = []
@num_protected_ranges = 0
if excel2003_style?
@original_row_height = 12.75
@default_row_height = 12.75
@default_row_pixels = 17
self.margins_left_right = 0.75
self.margins_top_bottom = 1
@page_setup.margin_header = 0.5
@page_setup.margin_footer = 0.5
@page_setup.header_footer_aligns = false
end
@embedded_image_indexes = @workbook.embedded_image_indexes
end
def set_xml_writer(filename) # :nodoc:
@writer.set_xml_writer(filename)
end
def assemble_xml_file # :nodoc:
write_xml_declaration do
@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_protected_ranges
# write_sheet_calc_pr
write_phonetic_pr if excel2003_style?
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_ignored_errors
write_drawings
write_legacy_drawing
write_legacy_drawing_hf
write_picture
write_table_parts
write_ext_list
end
end
end
#
# The name method is used to retrieve the name of a worksheet.
#
attr_reader :name
#
# Set this worksheet as a selected worksheet, i.e. the worksheet has its tab
# highlighted.
#
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.
#
def activate
@hidden = false
@selected = true
@workbook.activesheet = @index
end
#
# Hide this worksheet.
#
def hide(hidden = :hidden)
@hidden = hidden
@selected = false
@workbook.activesheet = 0 if @workbook.activesheet == @index
@workbook.firstsheet = 0 if @workbook.firstsheet == @index
end
#
# Hide this worksheet. This can only be unhidden from VBA.
#
def very_hidden
hide(:very_hidden)
end
def hidden? # :nodoc:
@hidden == :hidden
end
def very_hidden? # :nodoc:
@hidden == :very_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.
#
def set_first_sheet
@hidden = false
@workbook.firstsheet = @index
end
#
# Set the worksheet protection flags to prevent modification of worksheet
# objects.
#
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.
if password && password != ''
@protect[:password] =
encode_password(password)
end
end
#
# Unprotect ranges within a protected worksheet.
#
def unprotect_range(range, range_name = nil, password = nil)
if range.nil?
raise "The range must be defined in unprotect_range())\n"
else
range = range.gsub("$", "")
range = range.sub(/^=/, "")
@num_protected_ranges += 1
end
range_name ||= "Range#{@num_protected_ranges}"
password &&= encode_password(password)
@protected_ranges << [range, range_name, 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.
#
def set_column(*args)
# Check for a cell reference in A1 notation and substitute row and column
# ruby 3.2 no longer handles =~ for various types
if args[0].respond_to?(:=~) && args[0].to_s =~ /^\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
autofit = 0
# 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
# Excel has a maximum column width of 255 characters.
width = 255.0 if width && width > 255.0
@outline_col_level = level if level > @outline_col_level
# Store the column data based on the first column. Padded for sorting.
(firstcol..lastcol).each do |col|
@col_info[col] =
COLINFO.new(width, format, hidden, level, collapsed, autofit)
end
# Store the column change to allow optimisations.
@col_size_changed = 1
end
#
# Set the width (and properties) of a single column or a range of columns in
# pixels rather than character units.
#
def set_column_pixels(*data)
cell = data[0]
# Check for a cell reference in A1 notation and substitute row and column
if cell =~ /^\D/
data = substitute_cellref(*data)
# Returned values row1 and row2 aren't required here. Remove them.
data.shift # $row1
data.delete_at(1) # $row2
end
# Ensure at least $first_col, $last_col and $width
return if data.size < 3
first_col = data[0]
last_col = data[1]
pixels = data[2]
format = data[3]
hidden = data[4] || 0
level = data[5]
width = pixels_to_width(pixels) if ptrue?(pixels)
set_column(first_col, last_col, width, format, hidden, level)
end
#
# autofit()
#
# Simulate autofit based on the data, and datatypes in each column. We do this
# by estimating a pixel width for each cell data.
#
def autofit
col_width = {}
# Iterate through all the data in the worksheet.
(@dim_rowmin..@dim_rowmax).each do |row_num|
# Skip row if it doesn't contain cell data.
next unless @cell_data_table[row_num]
(@dim_colmin..@dim_colmax).each do |col_num|
length = 0
case (cell_data = @cell_data_table[row_num][col_num])
when StringCellData, RichStringCellData
# Handle strings and rich strings.
#
# For standard shared strings we do a reverse lookup
# from the shared string id to the actual string. For
# rich strings we use the unformatted string. We also
# split multiline strings and handle each part
# separately.
string = cell_data.raw_string
if string =~ /\n/
# Handle multiline strings.
length = max = string.split("\n").collect do |str|
xl_string_pixel_width(str)
end.max
else
length = xl_string_pixel_width(string)
end
when DateTimeCellData
# Handle dates.
#
# The following uses the default width for mm/dd/yyyy
# dates. It isn't feasible to parse the number format
# to get the actual string width for all format types.
length = @default_date_pixels
when NumberCellData
# Handle numbers.
#
# We use a workaround/optimization for numbers since
# digits all have a pixel width of 7. This gives a
# slightly greater width for the decimal place and
# minus sign but only by a few pixels and
# over-estimation is okay.
length = 7 * cell_data.token.to_s.length
when BooleanCellData
# Handle boolean values.
#
# Use the Excel standard widths for TRUE and FALSE.
if ptrue?(cell_data.token)
length = 31
else
length = 36
end
when FormulaCellData, FormulaArrayCellData, DynamicFormulaArrayCellData
# Handle formulas.
#
# We only try to autofit a formula if it has a
# non-zero value.
if ptrue?(cell_data.data)
length = xl_string_pixel_width(cell_data.data)
end
end
# If the cell is in an autofilter header we add an
# additional 16 pixels for the dropdown arrow.
if length > 0 &&
@filter_cells["#{row_num}:#{col_num}"]
length += 16
end
# Add the string lenght to the lookup hash.
max = col_width[col_num] || 0
col_width[col_num] = length if length > max
end
end
# Apply the width to the column.
col_width.each do |col_num, pixel_width|
# Convert the string pixel width to a character width using an
# additional padding of 7 pixels, like Excel.
width = pixels_to_width(pixel_width + 7)
# The max column character width in Excel is 255.
width = 255.0 if width > 255.0
# Add the width to an existing col info structure or add a new one.
if @col_info[col_num]
@col_info[col_num].width = width
@col_info[col_num].autofit = 1
else
@col_info[col_num] =
COLINFO.new(width, nil, 0, 0, 0, 1)
end
end
end
#
# :call-seq:
# set_selection(cell_or_cell_range)
#
# Set which cell or cells are selected in a worksheet.
#
def set_selection(*args)
return if args.empty?
if (row_col_array = row_col_notation(args.first))
row_first, col_first, row_last, col_last = row_col_array
else
row_first, col_first, row_last, col_last = args
end
active_cell = xl_rowcol_to_cell(row_first, col_first)
if row_last # 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
sqref = xl_range(row_first, row_last, col_first, col_last)
else # Single cell selection.
sqref = active_cell
end
# Selection isn't set for cell A1.
return if sqref == 'A1'
@selections = [[nil, active_cell, sqref]]
end
###############################################################################
#
# set_top_left_cell()
#
# Set the first visible cell at the top left of the worksheet.
#
def set_top_left_cell(row, col = nil)
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
else
_row = row
_col = col
end
@top_left_cell = xl_rowcol_to_cell(_row, _col)
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
#
def freeze_panes(*args)
return if args.empty?
# Check for a cell reference in A1 notation and substitute row and column.
if (row_col_array = row_col_notation(args.first))
row, col, top_row, left_col = row_col_array
type = args[1]
else
row, col, top_row, left_col, type = args
end
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.
#
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 = 1)
@page_view = flag
end
#
# set_pagebreak_view
#
# Set the page view mode.
#
def set_pagebreak_view
@page_view = 2
end
#
# Set the colour of the worksheet tab.
#
def tab_color=(color)
@tab_color = Colors.new.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
#
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.
#
def set_header(string = '', margin = 0.3, options = {})
raise 'Header string must be less than 255 characters' if string.length > 255
# Replace the Excel placeholder &[Picture] with the internal &G.
@page_setup.header = string.gsub("&[Picture]", '&G')
@page_setup.header_footer_aligns = options[:align_with_margins] if options[:align_with_margins]
@page_setup.header_footer_scales = options[:scale_with_doc] if options[:scale_with_doc]
# Reset the array in case the function is called more than once.
@header_images = []
[
[:image_left, 'LH'], [:image_center, 'CH'], [:image_right, 'RH']
].each do |p|
@header_images << [options[p.first], p.last] if options[p.first]
end
# placeholeder /&G/ の数
placeholder_count = @page_setup.header.scan("&G").count
image_count = @header_images.count
raise "Number of header image (#{image_count}) doesn't match placeholder count (#{placeholder_count}) in string: #{@page_setup.header}" if image_count != placeholder_count
@has_header_vml = true if image_count > 0
@page_setup.margin_header = margin || 0.3
@page_setup.header_footer_changed = true
end
#
# Set the page footer caption and optional margin.
#
def set_footer(string = '', margin = 0.3, options = {})
raise 'Footer string must be less than 255 characters' if string.length > 255
@page_setup.footer = string.dup
# Replace the Excel placeholder &[Picture] with the internal &G.
@page_setup.footer = string.gsub("&[Picture]", '&G')
@page_setup.header_footer_aligns = options[:align_with_margins] if options[:align_with_margins]
@page_setup.header_footer_scales = options[:scale_with_doc] if options[:scale_with_doc]
# Reset the array in case the function is called more than once.
@footer_images = []
[
[:image_left, 'LF'], [:image_center, 'CF'], [:image_right, 'RF']
].each do |p|
@footer_images << [options[p.first], p.last] if options[p.first]
end
# placeholeder /&G/ の数
placeholder_count = @page_setup.footer.scan("&G").count
image_count = @footer_images.count
raise "Number of footer image (#{image_count}) doesn't match placeholder count (#{placeholder_count}) in string: #{@page_setup.footer}" if image_count != placeholder_count
@has_header_vml = true if image_count > 0
@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.
#
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_*=().
#
def set_margins(margin)
put_deprecate_message("#{self}.set_margins")
self.margins = margin
end
#
# this method is deprecated. use margin_left_right=().
# Set the left and right margins to the same value in inches.
#
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.
#
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.
#
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.
#
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.
#
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.
#
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.
#
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.
#
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.
#
def print_area(*args)
return @page_setup.print_area.dup if args.empty?
if (row_col_array = row_col_notation(args.first))
row1, col1, row2, col2 = row_col_array
else
row1, col1, row2, col2 = args
end
return if [row1, col1, row2, col2].include?(nil)
# Ignore max print area since this is the same as no print area for Excel.
return if row1 == 0 && col1 == 0 && row2 == ROW_MAX - 1 && col2 == COL_MAX - 1
# 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:
#
def zoom=(scale)
# Confine the scale to Excel's range
@zoom = if scale < 10 or scale > 400
# carp "Zoom factor scale outside range: 10 <= zoom <= 400"
100
else
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:
#
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
#
# Set the option to print the worksheet in black and white.
#
def print_black_and_white
@page_setup.black_white = true
@page_setup.page_setup_changed = true
end
#
# Causes the write() method to treat integers with a leading zero as a string.
# This ensures that any leading zeros such, as in zip codes, are maintained.
#
def keep_leading_zeros(flag = true)
@leading_zeros = !!flag
end
#
# Display the worksheet right to left for some eastern versions of Excel.
#
def right_to_left(flag = true)
@right_to_left = !!flag
end
#
# Hide cell zero values.
#
def hide_zero(flag = true)
@show_zeros = !flag
end
#
# Set the order in which pages are printed.
#
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.
#
def start_page=(page_start)
@page_setup.page_start = page_start
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:
#
def write(row, col, token = nil, format = nil, value1 = nil, value2 = nil)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_token = col
_format = token
_value1 = format
_value2 = value1
else
_row = row
_col = col
_token = token
_format = format
_value1 = value1
_value2 = value2
end
_token ||= ''
_token = _token.to_s if token.instance_of?(Time) || token.instance_of?(Date)
if _format.respond_to?(:force_text_format?) && _format.force_text_format?
write_string(_row, _col, _token, _format) # Force text format
# Match an array ref.
elsif _token.respond_to?(:to_ary)
write_row(_row, _col, _token, _format, _value1, _value2)
elsif _token.respond_to?(:coerce) # Numeric
write_number(_row, _col, _token, _format)
elsif _token.respond_to?(:=~) # String
# Match integer with leading zero(s)
if @leading_zeros && _token =~ /^0\d*$/
write_string(_row, _col, _token, _format)
elsif _token =~ /\A([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?\Z/
write_number(_row, _col, _token, _format)
# Match formula
elsif _token =~ /^=/
write_formula(_row, _col, _token, _format, _value1)
# Match array formula
elsif _token =~ /^\{=.*\}$/
write_formula(_row, _col, _token, _format, _value1)
# Match blank
elsif _token == ''
# row_col_args.delete_at(2) # remove the empty string from the parameter list
write_blank(_row, _col, _format)
elsif @workbook.strings_to_urls
# Match http, https or ftp URL
if _token =~ %r{\A[fh]tt?ps?://}
write_url(_row, _col, _token, _format, _value1, _value2)
# Match mailto:
elsif _token =~ /\Amailto:/
write_url(_row, _col, _token, _format, _value1, _value2)
# Match internal or external sheet link
elsif _token =~ /\A(?:in|ex)ternal:/
write_url(_row, _col, _token, _format, _value1, _value2)
else
write_string(_row, _col, _token, _format)
end
else
write_string(_row, _col, _token, _format)
end
else
write_string(_row, _col, _token, _format)
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.
#
def write_row(row, col, tokens = nil, *options)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_tokens = col
_options = [tokens] + options
else
_row = row
_col = col
_tokens = tokens
_options = options
end
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.
#
def write_col(row, col, tokens = nil, *options)
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_tokens = col
_options = [tokens] + options if options
else
_row = row
_col = col
_tokens = tokens
_options = options
end
_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).
#
def write_comment(row, col, string = nil, options = nil)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_string = col
_options = string
else
_row = row
_col = col
_string = string
_options = options
end
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(@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:
#
def write_number(row, col, number, format = nil)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_number = col
_format = number
else
_row = row
_col = col
_number = number
_format = format
end
raise WriteXLSXInsufficientArgumentError if _row.nil? || _col.nil? || _number.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(_number, _format), _row, _col)
end
#
# :call-seq:
# write_string(row, column, string [, format ])
#
# Write a string to the specified row and column (zero indexed).
# +format+ is optional.
#
def write_string(row, col, string = nil, format = nil)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_string = col
_format = string
else
_row = row
_col = col
_string = string
_format = format
end
_string &&= _string.to_s
raise WriteXLSXInsufficientArgumentError if _row.nil? || _col.nil? || _string.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(_string.length > STR_MAX ? _string[0, STR_MAX] : _string)
store_data_to_table(StringCellData.new(index, _format, _string), _row, _col)
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.
#
def write_rich_string(row, col, *rich_strings)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_rich_strings = [col] + rich_strings
else
_row = row
_col = col
_rich_strings = rich_strings
end
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, _raw_string = rich_strings_fragments(_rich_strings)
# can't allow 2 formats in a row
return -4 unless _fragments
# Check that the string si < 32767 chars.
return 3 if _raw_string.size > @xls_strmax
index = shared_string_index(xml_str_of_rich_string(_fragments))
store_data_to_table(RichStringCellData.new(index, _xf, _raw_string), _row, _col)
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.
#
def write_blank(row, col, format = nil)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_format = col
else
_row = row
_col = col
_format = format
end
raise WriteXLSXInsufficientArgumentError if [_row, _col].include?(nil)
# Don't write a blank cell unless it has a format
return unless _format
# 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(_format), _row, _col)
end
def expand_formula(formula, function, addition = '')
if formula =~ /\b(#{function})/
formula.gsub(
::Regexp.last_match(1),
"_xlfn#{addition}.#{::Regexp.last_match(1)}"
)
else
formula
end
end
private :expand_formula
#
# Utility method to strip equal sign and array braces from a formula
# and also expand out future and dynamic array formulas.
#
def prepare_formula(given_formula, expand_future_functions = nil)
# Ignore empty/null formulas.
return given_formula unless ptrue?(given_formula)
# Remove array formula braces and the leading =.
formula = given_formula.sub(/^\{(.*)\}$/, '\1').sub(/^=/, '')
# # Don't expand formulas that the user has already expanded.
return formula if formula =~ /_xlfn\./
# Expand dynamic array formulas.
formula = expand_formula(formula, 'ANCHORARRAY\(')
formula = expand_formula(formula, 'BYCOL\(')
formula = expand_formula(formula, 'BYROW\(')
formula = expand_formula(formula, 'CHOOSECOLS\(')
formula = expand_formula(formula, 'CHOOSEROWS\(')
formula = expand_formula(formula, 'DROP\(')
formula = expand_formula(formula, 'EXPAND\(')
formula = expand_formula(formula, 'FILTER\(', '._xlws')
formula = expand_formula(formula, 'HSTACK\(')
formula = expand_formula(formula, 'LAMBDA\(')
formula = expand_formula(formula, 'MAKEARRAY\(')
formula = expand_formula(formula, 'MAP\(')
formula = expand_formula(formula, 'RANDARRAY\(')
formula = expand_formula(formula, 'REDUCE\(')
formula = expand_formula(formula, 'SCAN\(')
formula = expand_formula(formula, 'SEQUENCE\(')
formula = expand_formula(formula, 'SINGLE\(')
formula = expand_formula(formula, 'SORT\(', '._xlws')
formula = expand_formula(formula, 'SORTBY\(')
formula = expand_formula(formula, 'SWITCH\(')
formula = expand_formula(formula, 'TAKE\(')
formula = expand_formula(formula, 'TEXTSPLIT\(')
formula = expand_formula(formula, 'TOCOL\(')
formula = expand_formula(formula, 'TOROW\(')
formula = expand_formula(formula, 'UNIQUE\(')
formula = expand_formula(formula, 'VSTACK\(')
formula = expand_formula(formula, 'WRAPCOLS\(')
formula = expand_formula(formula, 'WRAPROWS\(')
formula = expand_formula(formula, 'XLOOKUP\(')
if !@use_future_functions && !ptrue?(expand_future_functions)
return formula
end
# Future functions.
formula = expand_formula(formula, 'ACOTH\(')
formula = expand_formula(formula, 'ACOT\(')
formula = expand_formula(formula, 'AGGREGATE\(')
formula = expand_formula(formula, 'ARABIC\(')
formula = expand_formula(formula, 'ARRAYTOTEXT\(')
formula = expand_formula(formula, 'BASE\(')
formula = expand_formula(formula, 'BETA.DIST\(')
formula = expand_formula(formula, 'BETA.INV\(')
formula = expand_formula(formula, 'BINOM.DIST.RANGE\(')
formula = expand_formula(formula, 'BINOM.DIST\(')
formula = expand_formula(formula, 'BINOM.INV\(')
formula = expand_formula(formula, 'BITAND\(')
formula = expand_formula(formula, 'BITLSHIFT\(')
formula = expand_formula(formula, 'BITOR\(')
formula = expand_formula(formula, 'BITRSHIFT\(')
formula = expand_formula(formula, 'BITXOR\(')
formula = expand_formula(formula, 'CEILING.MATH\(')
formula = expand_formula(formula, 'CEILING.PRECISE\(')
formula = expand_formula(formula, 'CHISQ.DIST.RT\(')
formula = expand_formula(formula, 'CHISQ.DIST\(')
formula = expand_formula(formula, 'CHISQ.INV.RT\(')
formula = expand_formula(formula, 'CHISQ.INV\(')
formula = expand_formula(formula, 'CHISQ.TEST\(')
formula = expand_formula(formula, 'COMBINA\(')
formula = expand_formula(formula, 'CONCAT\(')
formula = expand_formula(formula, 'CONFIDENCE.NORM\(')
formula = expand_formula(formula, 'CONFIDENCE.T\(')
formula = expand_formula(formula, 'COTH\(')
formula = expand_formula(formula, 'COT\(')
formula = expand_formula(formula, 'COVARIANCE.P\(')
formula = expand_formula(formula, 'COVARIANCE.S\(')
formula = expand_formula(formula, 'CSCH\(')
formula = expand_formula(formula, 'CSC\(')
formula = expand_formula(formula, 'DAYS\(')
formula = expand_formula(formula, 'DECIMAL\(')
formula = expand_formula(formula, 'ERF.PRECISE\(')
formula = expand_formula(formula, 'ERFC.PRECISE\(')
formula = expand_formula(formula, 'EXPON.DIST\(')
formula = expand_formula(formula, 'F.DIST.RT\(')
formula = expand_formula(formula, 'F.DIST\(')
formula = expand_formula(formula, 'F.INV.RT\(')
formula = expand_formula(formula, 'F.INV\(')
formula = expand_formula(formula, 'F.TEST\(')
formula = expand_formula(formula, 'FILTERXML\(')
formula = expand_formula(formula, 'FLOOR.MATH\(')
formula = expand_formula(formula, 'FLOOR.PRECISE\(')
formula = expand_formula(formula, 'FORECAST.ETS.CONFINT\(')
formula = expand_formula(formula, 'FORECAST.ETS.SEASONALITY\(')
formula = expand_formula(formula, 'FORECAST.ETS.STAT\(')
formula = expand_formula(formula, 'FORECAST.ETS\(')
formula = expand_formula(formula, 'FORECAST.LINEAR\(')
formula = expand_formula(formula, 'FORMULATEXT\(')
formula = expand_formula(formula, 'GAMMA.DIST\(')
formula = expand_formula(formula, 'GAMMA.INV\(')
formula = expand_formula(formula, 'GAMMALN.PRECISE\(')
formula = expand_formula(formula, 'GAMMA\(')
formula = expand_formula(formula, 'GAUSS\(')
formula = expand_formula(formula, 'HYPGEOM.DIST\(')
formula = expand_formula(formula, 'IFNA\(')
formula = expand_formula(formula, 'IFS\(')
formula = expand_formula(formula, 'IMAGE\(')
formula = expand_formula(formula, 'IMCOSH\(')
formula = expand_formula(formula, 'IMCOT\(')
formula = expand_formula(formula, 'IMCSCH\(')
formula = expand_formula(formula, 'IMCSC\(')
formula = expand_formula(formula, 'IMSECH\(')
formula = expand_formula(formula, 'IMSEC\(')
formula = expand_formula(formula, 'IMSINH\(')
formula = expand_formula(formula, 'IMTAN\(')
formula = expand_formula(formula, 'ISFORMULA\(')
formula = expand_formula(formula, 'ISOMITTED\(')
formula = expand_formula(formula, 'ISOWEEKNUM\(')
formula = expand_formula(formula, 'LET\(')
formula = expand_formula(formula, 'LOGNORM.DIST\(')
formula = expand_formula(formula, 'LOGNORM.INV\(')
formula = expand_formula(formula, 'MAXIFS\(')
formula = expand_formula(formula, 'MINIFS\(')
formula = expand_formula(formula, 'MODE.MULT\(')
formula = expand_formula(formula, 'MODE.SNGL\(')
formula = expand_formula(formula, 'MUNIT\(')
formula = expand_formula(formula, 'NEGBINOM.DIST\(')
formula = expand_formula(formula, 'NORM.DIST\(')
formula = expand_formula(formula, 'NORM.INV\(')
formula = expand_formula(formula, 'NORM.S.DIST\(')
formula = expand_formula(formula, 'NORM.S.INV\(')
formula = expand_formula(formula, 'NUMBERVALUE\(')
formula = expand_formula(formula, 'PDURATION\(')
formula = expand_formula(formula, 'PERCENTILE.EXC\(')
formula = expand_formula(formula, 'PERCENTILE.INC\(')
formula = expand_formula(formula, 'PERCENTRANK.EXC\(')
formula = expand_formula(formula, 'PERCENTRANK.INC\(')
formula = expand_formula(formula, 'PERMUTATIONA\(')
formula = expand_formula(formula, 'PHI\(')
formula = expand_formula(formula, 'POISSON.DIST\(')
formula = expand_formula(formula, 'QUARTILE.EXC\(')
formula = expand_formula(formula, 'QUARTILE.INC\(')
formula = expand_formula(formula, 'QUERYSTRING\(')
formula = expand_formula(formula, 'RANK.AVG\(')
formula = expand_formula(formula, 'RANK.EQ\(')
formula = expand_formula(formula, 'RRI\(')
formula = expand_formula(formula, 'SECH\(')
formula = expand_formula(formula, 'SEC\(')
formula = expand_formula(formula, 'SHEETS\(')
formula = expand_formula(formula, 'SHEET\(')
formula = expand_formula(formula, 'SKEW.P\(')
formula = expand_formula(formula, 'STDEV.P\(')
formula = expand_formula(formula, 'STDEV.S\(')
formula = expand_formula(formula, 'T.DIST.2T\(')
formula = expand_formula(formula, 'T.DIST.RT\(')
formula = expand_formula(formula, 'T.DIST\(')
formula = expand_formula(formula, 'T.INV.2T\(')
formula = expand_formula(formula, 'T.INV\(')
formula = expand_formula(formula, 'T.TEST\(')
formula = expand_formula(formula, 'TEXTAFTER\(')
formula = expand_formula(formula, 'TEXTBEFORE\(')
formula = expand_formula(formula, 'TEXTJOIN\(')
formula = expand_formula(formula, 'UNICHAR\(')
formula = expand_formula(formula, 'UNICODE\(')
formula = expand_formula(formula, 'VALUETOTEXT\(')
formula = expand_formula(formula, 'VAR.P\(')
formula = expand_formula(formula, 'VAR.S\(')
formula = expand_formula(formula, 'WEBSERVICE\(')
formula = expand_formula(formula, 'WEIBULL.DIST\(')
formula = expand_formula(formula, 'XMATCH\(')
formula = expand_formula(formula, 'XOR\(')
expand_formula(formula, 'Z.TEST\(')
end
#
# :call-seq:
# write_formula(row, column, formula [ , format [ , value ] ])
#
# Write a formula or function to the cell specified by +row+ and +column+:
#
def write_formula(row, col, formula = nil, format = nil, value = nil)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_formula = col
_format = formula
_value = format
else
_row = row
_col = col
_formula = formula
_format = format
_value = value
end
raise WriteXLSXInsufficientArgumentError if [_row, _col, _formula].include?(nil)
# Check for dynamic array functions.
regex = /\bANCHORARRAY\(|\bBYCOL\(|\bBYROW\(|\bCHOOSECOLS\(|\bCHOOSEROWS\(|\bDROP\(|\bEXPAND\(|\bFILTER\(|\bHSTACK\(|\bLAMBDA\(|\bMAKEARRAY\(|\bMAP\(|\bRANDARRAY\(|\bREDUCE\(|\bSCAN\(|\bSEQUENCE\(|\bSINGLE\(|\bSORT\(|\bSORTBY\(|\bSWITCH\(|\bTAKE\(|\bTEXTSPLIT\(|\bTOCOL\(|\bTOROW\(|\bUNIQUE\(|\bVSTACK\(|\bWRAPCOLS\(|\bWRAPROWS\(|\bXLOOKUP\(/
if _formula =~ regex
return write_dynamic_array_formula(
_row, _col, _row, _col, _formula, _format, _value
)
end
# Hand off array formulas.
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 = prepare_formula(_formula)
store_data_to_table(FormulaCellData.new(_formula, _format, _value), _row, _col)
end
end
#
# Internal method shared by the write_array_formula() and
# write_dynamic_array_formula() methods.
#
def write_array_formula_base(type, *args)
# Check for a cell reference in A1 notation and substitute row and column
# Convert single cell to range
if args.first.to_s =~ /^([A-Za-z]+[0-9]+)$/
range = "#{::Regexp.last_match(1)}:#{::Regexp.last_match(1)}"
params = [range] + args[1..-1]
else
params = args
end
if (row_col_array = row_col_notation(params.first))
row1, col1, row2, col2 = row_col_array
formula, xf, value = params[1..-1]
else
row1, col1, row2, col2, formula, xf, value = params
end
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(row1, col1)
check_dimensions(row2, col2)
store_row_col_max_min_values(row1, col1)
store_row_col_max_min_values(row2, col2)
# Define array range
range = if row1 == row2 && col1 == col2
xl_rowcol_to_cell(row1, col1)
else
"#{xl_rowcol_to_cell(row1, col1)}:#{xl_rowcol_to_cell(row2, col2)}"
end
# Modify the formula string, as needed.
formula = prepare_formula(formula, 1)
store_data_to_table(
if type == 'a'
FormulaArrayCellData.new(formula, xf, range, value)
elsif type == 'd'
DynamicFormulaArrayCellData.new(formula, xf, range, value)
else
raise "invalid type in write_array_formula_base()."
end,
row1, col1
)
# 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
#
# write_array_formula(row1, col1, row2, col2, formula, format)
#
# Write an array formula to the specified row and column (zero indexed).
#
def write_array_formula(row1, col1, row2 = nil, col2 = nil, formula = nil, format = nil, value = nil)
write_array_formula_base('a', row1, col1, row2, col2, formula, format, value)
end
#
# write_dynamic_array_formula(row1, col1, row2, col2, formula, format)
#
# Write a dynamic formula to the specified row and column (zero indexed).
#
def write_dynamic_array_formula(row1, col1, row2 = nil, col2 = nil, formula = nil, format = nil, value = nil)
write_array_formula_base('d', row1, col1, row2, col2, formula, format, value)
@has_dynamic_functions = true
end
#
# write_boolean(row, col, val, format)
#
# Write a boolean value to the specified row and column (zero indexed).
#
def write_boolean(row, col, val = nil, format = nil)
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_val = col
_format = val
else
_row = row
_col = col
_val = val
_format = format
end
raise WriteXLSXInsufficientArgumentError if _row.nil? || _col.nil?
_val = _val ? 1 : 0 # Boolean value.
# xf : cell format.
# 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(BooleanCellData.new(_val, _format), _row, _col)
end
#
# :call-seq:
# update_format_with_params(row, col, format_params)
#
# Update formatting of the cell to the specified row and column (zero indexed).
#
def update_format_with_params(row, col, params = nil)
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_params = args[1]
else
_row = row
_col = col
_params = params
end
raise WriteXLSXInsufficientArgumentError if _row.nil? || _col.nil? || _params.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)
format = nil
cell_data = nil
if @cell_data_table[_row].nil? || @cell_data_table[_row][_col].nil?
format = @workbook.add_format(_params)
write_blank(_row, _col, format)
else
if @cell_data_table[_row][_col].xf.nil?
format = @workbook.add_format(_params)
cell_data = @cell_data_table[_row][_col]
else
format = @workbook.add_format
cell_data = @cell_data_table[_row][_col]
format.copy(cell_data.xf)
format.set_format_properties(_params)
end
# keep original value of cell
value = if cell_data.is_a? FormulaCellData
"=#{cell_data.token}"
elsif cell_data.is_a? FormulaArrayCellData
"{=#{cell_data.token}}"
elsif cell_data.is_a? StringCellData
@workbook.shared_strings.string(cell_data.data[:sst_id])
else
cell_data.data
end
write(_row, _col, value, format)
end
end
#
# :call-seq:
# update_range_format_with_params(row_first, col_first, row_last, col_last, format_params)
#
# Update formatting of cells in range to the specified row and column (zero indexed).
#
def update_range_format_with_params(row_first, col_first, row_last = nil, col_last = nil, params = nil)
if (row_col_array = row_col_notation(row_first))
_row_first, _col_first, _row_last, _col_last = row_col_array
params = args[1..-1]
else
_row_first = row_first
_col_first = col_first
_row_last = row_last
_col_last = col_last
_params = params
end
raise WriteXLSXInsufficientArgumentError if [_row_first, _col_first, _row_last, _col_last, _params].include?(nil)
# 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)
(_row_first.._row_last).each do |row|
(_col_first.._col_last).each do |col|
update_format_with_params(row, col, _params)
end
end
end
#
# The outline_settings() method is used to control the appearance of
# outlines in Excel.
#
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, tip ])
#
# 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.
#
def write_url(row, col, url = nil, format = nil, str = nil, tip = nil, ignore_write_string = false)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_url = col
_format = url
_str = format
_tip = str
_ignore_write_string = tip
else
_row = row
_col = col
_url = url
_format = format
_str = str
_tip = tip
_ignore_write_string = ignore_write_string
end
_format, _str = _str, _format if _str.respond_to?(:xf_index) || !_format.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.factory(_url, _str, _tip)
store_hyperlink(_row, _col, hyperlink)
raise "URL '#{url}' added but URL exceeds Excel's limit of 65,530 URLs per worksheet." if hyperlinks_count > 65_530
# Add the default URL format.
_format ||= @default_url_format
# Write the hyperlink string.
write_string(_row, _col, hyperlink.str, _format) unless _ignore_write_string
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.
#
def write_date_time(row, col, str, format = nil)
# Check for a cell reference in A1 notation and substitute row and column
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_str = col
_format = str
else
_row = row
_col = col
_str = str
_format = format
end
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(DateTimeCellData.new(date_time, _format), _row, _col)
else
# If the date isn't valid then write it as a string.
write_string(_row, _col, _str, _format)
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.
#
def insert_chart(row, col, chart = nil, *options)
# Check for a cell reference in A1 notation and substitute row and column.
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_chart = col
_options = [chart] + options
else
_row = row
_col = col
_chart = chart
_options = options
end
raise WriteXLSXInsufficientArgumentError if [_row, _col, _chart].include?(nil)
if _options.first.instance_of?(Hash)
params = _options.first
x_offset = params[:x_offset]
y_offset = params[:y_offset]
x_scale = params[:x_scale]
y_scale = params[:y_scale]
anchor = params[:object_position]
description = params[:description]
decorative = params[:decorative]
else
x_offset, y_offset, x_scale, y_scale, anchor = _options
end
x_offset ||= 0
y_offset ||= 0
x_scale ||= 1
y_scale ||= 1
anchor ||= 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
if _chart.already_inserted? || (_chart.combined && _chart.combined.already_inserted?)
raise "Chart cannot be inserted in a worksheet more than once"
else
_chart.already_inserted = true
_chart.combined.already_inserted = true if _chart.combined
end
# 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, anchor, description, decorative
]
end
#
# :call-seq:
# insert_image(row, column, filename, options)
#
def insert_image(row, col, image = nil, *options)
# Check for a cell reference in A1 notation and substitute row and column.
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_image = col
_options = [image] + options
else
_row = row
_col = col
_image = image
_options = options
end
raise WriteXLSXInsufficientArgumentError if [_row, _col, _image].include?(nil)
if _options.first.instance_of?(Hash)
# Newer hash bashed options
params = _options.first
x_offset = params[:x_offset]
y_offset = params[:y_offset]
x_scale = params[:x_scale]
y_scale = params[:y_scale]
anchor = params[:object_position]
url = params[:url]
tip = params[:tip]
description = params[:description]
decorative = params[:decorative]
else
x_offset, y_offset, x_scale, y_scale, anchor = _options
end
x_offset ||= 0
y_offset ||= 0
x_scale ||= 1
y_scale ||= 1
anchor ||= 2
@images << [
_row, _col, _image, x_offset, y_offset,
x_scale, y_scale, url, tip, anchor, description, decorative
]
end
#
# Embed an image into the worksheet.
#
def embed_image(row, col, filename, options = nil)
# Check for a cell reference in A1 notation and substitute row and column.
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
image = col
_options = filename
else
_row = row
_col = col
image = filename
_options = options
end
xf, url, tip, description, decorative = []
raise WriteXLSXInsufficientArgumentError if [_row, _col, image].include?(nil)
raise "Couldn't locate #{image}" unless File.exist?(image)
# 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)
if options
xf = options[:cell_format]
url = options[:url]
tip = options[:tip]
description = options[:description]
decorative = options[:decorative]
end
# Write the url without writing a string.
if url
xf ||= @default_url_format
write_url(row, col, url, xf, nil, tip, true)
end
# Get the image properties, mainly for the type and checksum.
image_properties = get_image_properties(image)
type = image_properties[0]
md5 = image_properties[6]
# Check for duplicate images.
image_index = @embedded_image_indexes[md5]
unless ptrue?(image_index)
@workbook.embedded_images << [image, type, description, decorative]
image_index = @workbook.embedded_images.size
@embedded_image_indexes[md5] = image_index
end
# Write the cell placeholder.
store_data_to_table(EmbedImageCellData.new(image_index, xf), _row, _col)
@has_embedded_images = true
end
#
# :call-seq:
# repeat_formula(row, column, formula [ , format ])
#
# Deprecated. This is a writeexcel gem's method that is no longer
# required by WriteXLSX.
#
def repeat_formula(row, col, formula, format, *pairs)
# Check for a cell reference in A1 notation and substitute row and column.
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_formula = col
_format = formula
_pairs = [format] + pairs
else
_row = row
_col = col
_formula = formula
_format = format
_pairs = pairs
end
raise WriteXLSXInsufficientArgumentError if [_row, _col].include?(nil)
raise "Odd number of elements in pattern/replacement list" unless _pairs.size.even?
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
until _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.
#
def set_row(*args)
return unless args[0]
row = args[0]
height = args[1] || @default_height
xf = args[2]
hidden = args[3] || 0
level = args[4] || 0
collapsed = args[5] || 0
# 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, hidden]
end
#
# This method is used to set the height (in pixels) and the properties of the
# row.
#
def set_row_pixels(*data)
height = data[1]
data[1] = pixels_to_height(height) if ptrue?(height)
set_row(*data)
end
#
# Set the default row properties
#
def set_default_row(height = nil, zero_height = nil)
height ||= @original_row_height
zero_height ||= 0
if height != @original_row_height
@default_row_height = height
# Store the row change to allow optimisations.
@row_size_changed = 1
end
@default_row_zeroed = 1 if ptrue?(zero_height)
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.
#
def merge_range(*args)
if (row_col_array = row_col_notation(args.first))
row_first, col_first, row_last, col_last = row_col_array
string, format, *extra_args = args[1..-1]
else
row_first, col_first, row_last, col_last,
string, format, *extra_args = args
end
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 the data range is valid and store the max and min values.
check_dimensions(row_first, col_first)
check_dimensions(row_last, col_last)
store_row_col_max_min_values(row_first, col_first)
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.
#
def merge_range_type(type, *args)
case type
when 'array_formula', 'blank', 'rich_string'
if (row_col_array = row_col_notation(args.first))
row_first, col_first, row_last, col_last = row_col_array
*others = args[1..-1]
else
row_first, col_first, row_last, col_last, *others = args
end
format = others.pop
else
if (row_col_array = row_col_notation(args.first))
row_first, col_first, row_last, col_last = row_col_array
token, format, *others = args[1..-1]
else
row_first, col_first, row_last, col_last,
token, format, *others = args
end
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 the data range is valid and store the max and min values.
check_dimensions(row_first, col_first)
check_dimensions(row_last, col_last)
store_row_col_max_min_values(row_first, col_first)
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
case type
when 'string'
write_string(row_first, col_first, token, format, *others)
when 'number'
write_number(row_first, col_first, token, format, *others)
when 'blank'
write_blank(row_first, col_first, *others)
when 'date_time'
write_date_time(row_first, col_first, token, format, *others)
when 'rich_string'
write_rich_string(row_first, col_first, *others)
when 'url'
write_url(row_first, col_first, token, format, *others)
when 'formula'
write_formula(row_first, col_first, token, format, *others)
when '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.
#
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.
#
def add_table(*args)
# Table count is a member of Workbook, global to all Worksheet.
table = Package::Table.new(self, *args)
@tables << table
table
end
#
# :call-seq:
# add_sparkline(properties)
#
# Add sparklines to the worksheet.
#
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.
#
def insert_button(row, col, properties = nil)
if (row_col_array = row_col_notation(row))
_row, _col = row_col_array
_properties = col
else
_row = row
_col = col
_properties = properties
end
@buttons_array << button_params(_row, _col, _properties)
@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.
#
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.
#
def hide_gridlines(option = 1)
@screen_gridlines = (option != 2)
@page_setup.hide_gridlines(option)
end
# Set the option to print the row and column headers on the printed page.
#
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
#
# Set the option to hide the row and column headers in Excel.
#
def hide_row_col_headers
@hide_row_col_headers = 1
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.
#
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.
#
def autofilter(row1, col1 = nil, row2 = nil, col2 = nil)
if (row_col_array = row_col_notation(row1))
_row1, _col1, _row2, _col2 = row_col_array
else
_row1 = row1
_col1 = col1
_row2 = row2
_col2 = col2
end
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]
# Store the filter cell positions for use in the autofit calculation.
(_col1.._col2).each do |col|
@filter_cells["#{_row1}:#{col}"] = 1
end
end
#
# Set the column filter criteria.
#
# The filter_column method can be used to filter columns in a autofilter
# range based on simple conditions.
#
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)
raise "Incorrect number of tokens in expression '#{expression}'" unless tokens.size == 3 || tokens.size == 7
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.
#
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.
#
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.
#
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.
#
def show_comments(visible = true)
@comments_visible = visible
end
#
# This method is used to set the default author of all cell comments.
#
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_header_vml? # :nodoc:
@has_header_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, anchor, description, decorative = @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, anchor)
# 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.
drawing = Drawing.new(drawing_type, dimensions, 0, 0, nil, anchor, drawing_rel_index, 0, nil, name, description, decorative)
if drawings?
@drawings.add_drawing_object(drawing)
else
@drawings = Drawings.new
@drawings.add_drawing_object(drawing)
@drawings.embedded = 1
@external_drawing_links << ['/drawing', "../drawings/drawing#{drawing_id}.xml"]
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.
unless @cell_data_table[row_num]
data << nil
next
end
(col_start..col_end).each do |col_num|
cell = @cell_data_table[row_num][col_num]
if cell
data << cell.data
else
data << nil
end
end
end
data
end
#
# Calculate the vertices that define the position of a graphical object within
# the worksheet in pixels.
#
def position_object_pixels(col_start, row_start, x1, y1, width, height, anchor = nil) # :nodoc:
# Adjust start column for negative offsets.
while x1 < 0 && col_start > 0
x1 += size_col(col_start - 1)
col_start -= 1
end
# Adjust start row for negative offsets.
while y1 < 0 && row_start > 0
y1 += size_row(row_start - 1)
row_start -= 1
end
# Ensure that the image isn't shifted off the page at top left.
x1 = 0 if x1 < 0
y1 = 0 if y1 < 0
# Calculate the absolute x offset of the top-left vertex.
x_abs = if @col_size_changed
(0..col_start - 1).inject(0) { |sum, col| sum += size_col(col, anchor) }
else
# Optimisation for when the column widths haven't changed.
@default_col_pixels * col_start
end
x_abs += x1
# Calculate the absolute y offset of the top-left vertex.
# Store the column change to allow optimisations.
y_abs = if @row_size_changed
(0..row_start - 1).inject(0) { |sum, row| sum += size_row(row, anchor) }
else
# Optimisation for when the row heights haven't changed.
@default_row_pixels * row_start
end
y_abs += y1
# Adjust start column for offsets that are greater than the col width.
while x1 >= size_col(col_start, anchor)
x1 -= size_col(col_start)
col_start += 1
end
# Adjust start row for offsets that are greater than the row height.
while y1 >= size_row(row_start, anchor)
y1 -= size_row(row_start)
row_start += 1
end
# Initialise end cell to the same as the start cell.
col_end = col_start
row_end = row_start
# Only offset the image in the cell if the row/col isn't hidden.
width += x1 if size_col(col_start, anchor) > 0
height += y1 if size_row(row_start, anchor) > 0
# Subtract the underlying cell widths to find the end cell of the object.
while width >= size_col(col_end, anchor)
width -= size_col(col_end, anchor)
col_end += 1
end
# Subtract the underlying cell heights to find the end cell of the object.
while height >= size_row(row_end, anchor)
height -= size_row(row_end, anchor)
row_end += 1
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:
return write_cell_formula('=NA()') if value.is_a?(Float) && value.nan?
value ||= ''
int_value = value.to_i
value = int_value if value == int_value
@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,
[
%w[t array],
['ref', range]
]
)
end
def date_1904? # :nodoc:
@workbook.date_1904?
end
def excel2003_style? # :nodoc:
@workbook.excel2003_style
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 palette_color(index) # :nodoc:
if index.to_s =~ /^#([0-9A-F]{6})$/i
"FF#{::Regexp.last_match(1).upcase}"
else
"FF#{super(index)}"
end
end
def buttons_data # :nodoc:
@buttons_array
end
def header_images_data # :nodoc:
@header_images_array
end
def external_links
[
@external_hyper_links,
@external_drawing_links,
@external_vml_links,
@external_background_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".
data = "#{vml_data_id}"
(1..num_comments_block).each do |i|
data += ",#{vml_data_id + i}"
end
@vml_data_id = data
@vml_shape_id = vml_shape_id
end
#
# Setup external linkage for VML header/footer images.
#
def prepare_header_vml_objects(vml_header_id, vml_drawing_id)
@vml_header_id = vml_header_id
@external_vml_links << ['/vmlDrawing', "../drawings/vmlDrawing#{vml_drawing_id}.vml"]
end
#
# Set the table ids for the worksheet tables.
#
def prepare_tables(table_id, seen)
if tables_count > 0
id = table_id
tables.each do |table|
table.prepare(id)
if seen[table.name]
raise "error: invalid duplicate table name '#{table.name}' found."
else
seen[table.name] = 1
end
# Store the link used for the rels file.
@external_table_links << ['/table', "../tables/table#{id}.xml"]
id += 1
end
end
tables_count || 0
end
def num_comments_block
@comments.size / 1024
end
def tables_count
@tables.size
end
def horizontal_dpi=(val)
@page_setup.horizontal_dpi = val
end
def vertical_dpi=(val)
@page_setup.vertical_dpi = val
end
#
# set the vba name for the worksheet
#
def set_vba_name(vba_codename = nil)
@vba_codename = vba_codename || @name
end
#
# Ignore worksheet errors/warnings in user defined ranges.
#
def ignore_errors(ignores)
# List of valid input parameters.
valid_parameter_keys = %i[
number_stored_as_text
eval_error
formula_differs
formula_range
formula_unlocked
empty_cell_reference
list_data_validation
calculated_column
two_digit_text_year
]
raise "Unknown parameter '#{ignores.key - valid_parameter_keys}' in ignore_errors()." unless (ignores.keys - valid_parameter_keys).empty?
@ignore_errors = ignores
end
def write_ext(url, &block)
attributes = [
['xmlns:x14', "#{OFFICE_URL}spreadsheetml/2009/9/main"],
['uri', url]
]
@writer.tag_elements('ext', attributes, &block)
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 has_dynamic_functions?
@has_dynamic_functions
end
def has_embedded_images?
@has_embedded_images
end
private
#
# Compare adjacent column information structures.
#
def compare_col_info(col_options, previous_options)
if !col_options.width.nil? != !previous_options.width.nil?
return nil
end
if col_options.width && previous_options.width &&
col_options.width != previous_options.width
return nil
end
if !col_options.format.nil? != !previous_options.format.nil?
return nil
end
if col_options.format && previous_options.format &&
col_options.format != previous_options.format
return nil
end
return nil if col_options.hidden != previous_options.hidden
return nil if col_options.level != previous_options.level
return nil if col_options.collapsed != previous_options.collapsed
true
end
#
# Get the index used to address a drawing rel link.
#
def drawing_rel_index(target = nil)
if !target
# Undefined values for drawings like charts will always be unique.
@drawing_rels_id += 1
elsif ptrue?(@drawing_rels[target])
@drawing_rels[target]
else
@drawing_rels_id += 1
@drawing_rels[target] = @drawing_rels_id
end
end
#
# Get the index used to address a vml_drawing rel link.
#
def get_vml_drawing_rel_index(target)
if @vml_drawing_rels[target]
@vml_drawing_rels[target]
else
@vml_drawing_rels_id += 1
@vml_drawing_rels[target] = @vml_drawing_rels_id
end
end
def hyperlinks_count
@hyperlinks.keys.inject(0) { |s, n| s += @hyperlinks[n].keys.size }
end
def store_hyperlink(row, col, hyperlink)
@hyperlinks ||= {}
@hyperlinks[row] ||= {}
@hyperlinks[row][col] = hyperlink
end
def cell_format_of_rich_string(rich_strings)
# If the last arg is a format we use it as the cell format.
rich_strings.pop if rich_strings[-1].respond_to?(:xf_index)
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)
last = 'format'
pos = 0
raw_string = ''
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 was a format just add the string.
fragments << token
else
# If previous token wasn't a format add one before the string.
fragments << default << token
end
raw_string += token # Keep track of actual string length.
last = 'string'
end
pos += 1
end
[fragments, raw_string]
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') unless 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).
#
def extract_filter_tokens(expression = nil) # :nodoc:
return [] unless expression
tokens = []
str = expression
while str =~ /"(?:[^"]|"")*"|\S+/
tokens << ::Regexp.last_match(0)
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.
#
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.to_s =~ /\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
operator = if tokens[0] =~ /^top$/i
30
else
32
end
operator += 1 if tokens[2] == '%'
token = value
end
if !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.to_s =~ /^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'
token = ' ' if operator == 5
elsif operator == 5
operator = 2
token = 'blanks'
else
operator = 5
token = ' '
end
end
# if the string token contains an Excel match character then change the
# operator type to indicate a non "simple" equality.
operator = 22 if operator == 2 and token.to_s =~ /[*?]/
[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
#
# Calculate the vertices that define the position of a graphical object within
# the worksheet in EMUs.
#
def position_object_emus(col_start, row_start, x1, y1, width, height, anchor = nil) # :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, anchor)
# 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. A hidden column is treated as having a width of
# zero unless it has the special "object_position" of 4 (size with cells).
#
def size_col(col, anchor = 0) # :nodoc:
# Look up the cell value to see if it has been changed.
if @col_info[col]
width = @col_info[col].width || @default_col_width
hidden = @col_info[col].hidden
# Convert to pixels.
pixels = if hidden == 1 && anchor != 4
0
elsif width < 1
((width * (MAX_DIGIT_WIDTH + PADDING)) + 0.5).to_i
else
((width * MAX_DIGIT_WIDTH) + 0.5).to_i + PADDING
end
else
pixels = @default_col_pixels
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. A hidden row is
# treated as having a height of zero unless it has the special
# "object_position" of 4 (size with cells).
#
def size_row(row, anchor = 0) # :nodoc:
# Look up the cell value to see if it has been changed
if @row_sizes[row]
height, hidden = @row_sizes[row]
pixels = if hidden == 1 && anchor != 4
0
else
(4 / 3.0 * height).to_i
end
else
pixels = (4 / 3.0 * @default_row_height).to_i
end
pixels
end
#
# Convert the width of a cell from pixels to character units.
#
def pixels_to_width(pixels)
max_digit_width = 7.0
padding = 5.0
if pixels <= 12
pixels / (max_digit_width + padding)
else
(pixels - padding) / max_digit_width
end
end
#
# Convert the height of a cell from pixels to character units.
#
def pixels_to_height(pixels)
height = 0.75 * pixels
height = height.to_i if (height - height.to_i).abs < 0.1
height
end
#
# Set up image/drawings.
#
def prepare_image(index, image_id, drawing_id, width, height, name, image_type, x_dpi = 96, y_dpi = 96, md5 = nil) # :nodoc:
x_dpi ||= 96
y_dpi ||= 96
drawing_type = 2
row, col, _image, x_offset, y_offset,
x_scale, y_scale, url, tip, anchor, description, decorative = @images[index]
width *= x_scale
height *= y_scale
width *= 96.0 / x_dpi
height *= 96.0 / y_dpi
dimensions = position_object_emus(col, row, x_offset, y_offset, width, height, anchor)
# 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.
drawing = Drawing.new(drawing_type, dimensions, width, height, nil, anchor, 0, 0, tip, name, description, decorative)
if drawings?
drawings = @drawings
else
drawings = Drawings.new
drawings.embedded = 1
@drawings = drawings
@external_drawing_links << ['/drawing', "../drawings/drawing#{drawing_id}.xml"]
end
drawings.add_drawing_object(drawing)
drawing.description = name unless description
if url
rel_type = '/hyperlink'
target_mode = 'External'
target = escape_url(url) if url =~ %r{^[fh]tt?ps?://} || url =~ /^mailto:/
if url =~ /^external:/
target = escape_url(url.sub(/^external:/, ''))
# Additional escape not required in worksheet hyperlinks
target = target.gsub("#", '%23')
# Prefix absolute paths (not relative) with file:///
target = if target =~ /^\w:/ || target =~ /^\\\\/
"file:///#{target}"
else
target.gsub("\\", '/')
end
end
if url =~ /^internal:/
target = url.sub(/^internal:/, '#')
target_mode = nil
end
if target.length > 255
raise <<"EOS"
Ignoring URL #{target} where link or anchor > 255 characters since it exceeds Excel's limit for URLS. See LIMITATIONS section of the WriteXLSX documentation.
EOS
end
@drawing_links << [rel_type, target, target_mode] if target && !@drawing_rels[url]
drawing.url_rel_index = drawing_rel_index(url)
end
@drawing_links << ['/image', "../media/image#{image_id}.#{image_type}"] unless @drawing_rels[md5]
drawing.rel_index = drawing_rel_index(md5)
end
public :prepare_image
def prepare_header_image(image_id, width, height, name, image_type, position, x_dpi, y_dpi, md5)
# Strip the extension from the filename.
body = name.dup
body[/\.[^.]+$/, 0] = ''
@vml_drawing_links << ['/image', "../media/image#{image_id}.#{image_type}"] unless @vml_drawing_rels[md5]
ref_id = get_vml_drawing_rel_index(md5)
@header_images_array << [width, height, body, position, x_dpi, y_dpi, ref_id]
end
public :prepare_header_image
#
# Set the background image for the worksheet.
#
def set_background(image)
raise "Couldn't locate #{image}: $!" unless File.exist?(image)
@background_image = image
end
public :set_background
#
# Set up an image without a drawing object for the background image.
#
def prepare_background(image_id, image_type)
@external_background_links <<
['/image', "../media/image#{image_id}.#{image_type}"]
end
public :prepare_background
#
# :call-seq:
# insert_shape(row, col, shape [ , x, y, x_scale, y_scale ])
#
# Insert a shape into the worksheet.
#
def insert_shape(
row_start, column_start, shape = nil, x_offset = nil, y_offset = nil,
x_scale = nil, y_scale = nil, anchor = nil
)
# Check for a cell reference in A1 notation and substitute row and column.
if (row_col_array = row_col_notation(row_start))
_row_start, _column_start = row_col_array
_shape = column_start
_x_offset = shape
_y_offset = x_offset
_x_scale = y_offset
_y_scale = x_scale
_anchor = y_scale
else
_row_start = row_start
_column_start = column_start
_shape = shape
_x_offset = x_offset
_y_offset = y_offset
_x_scale = x_scale
_y_scale = y_scale
_anchor = anchor
end
raise "Insufficient arguments in insert_shape()" if [_row_start, _column_start, _shape].include?(nil)
_shape.set_position(
_row_start, _column_start, _x_offset, _y_offset,
_x_scale, _y_scale, _anchor
)
# 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
insert = 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.
_shape.dup
else
_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 drawings?
@drawings = Drawings.new
@drawings.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 = Drawing.new(
drawing_type, shape.dimensions, shape.width_emu, shape.height_emu,
shape, shape.anchor, drawing_rel_index, 0, shape.name, nil, 0
)
drawings.add_drawing_object(drawing)
end
public :prepare_shape
#
# This method handles the parameters passed to insert_button as well as
# calculating the button 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.
button.macro = if params[:macro]
"[0]!#{params[:macro]}"
else
"[0]!Button#{button_number}_Click"
end
# Set the alt text for the button.
button.description = params[:description]
# Ensure that a width and height have been set.
default_width = @default_col_pixels
default_height = @default_row_pixels
params[:width] = default_width unless params[:width]
params[:height] = default_height unless params[:height]
# Set the x/y offsets.
params[:x_offset] = 0 unless params[:x_offset]
params[:y_offset] = 0 unless params[:y_offset]
# Scale the size of the button box if required.
params[:width] = params[:width] * params[:x_scale] if params[:x_scale]
params[:height] = params[:height] * params[:y_scale] if params[:y_scale]
# 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 button 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
#
# Hash a worksheet password. Based on the algorithm in ECMA-376-4:2016,
# Office Open XML File Foemats -- Transitional Migration Features,
# Additional attributes for workbookProtection element (Part 1, §18.2.29). #
def encode_password(password) # :nodoc:
hash = 0
password.reverse.split("").each do |char|
hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff)
hash ^= char.ord
end
hash = ((hash >> 14) & 0x01) | ((hash << 1) & 0x7fff)
hash ^= password.length
hash ^= 0xCE4B
sprintf("%X", hash)
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 the row/column headers.
attributes << ['showRowColHeaders', 0] if ptrue?(@hide_row_col_headers)
# 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.
case @page_view
when 1
attributes << %w[view pageLayout]
when 2
attributes << %w[view pageBreakPreview]
end
# Set the first visible cell.
attributes << ['topLeftCell', @top_left_cell] if ptrue?(@top_left_cell)
# Set the zoom level.
if @zoom != 100
attributes << ['zoomScale', @zoom]
if @page_view == 1
attributes << ['zoomScalePageLayoutView', @zoom]
elsif @page_view == 2
attributes << ['zoomScaleSheetLayoutView', @zoom]
elsif ptrue?(@zoom_scale_normal)
attributes << ['zoomScaleNormal', @zoom]
end
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:
attributes = [
['defaultRowHeight', @default_row_height]
]
attributes << ['customHeight', 1] if @default_row_height != @original_row_height
attributes << ['zeroHeight', 1] if ptrue?(@default_row_zeroed)
attributes << ['outlineLevelRow', @outline_row_level] if @outline_row_level > 0
attributes << ['outlineLevelCol', @outline_col_level] if @outline_col_level > 0
attributes << ['x14ac:dyDescent', '0.25'] if @excel_version == 2010
@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 @col_info.empty?
@writer.tag_elements('cols') do
# Use the first element of the column informatin structure to set
# the initial/previous properties.
first_col = @col_info.keys.min
last_col = first_col
previous_options = @col_info[first_col]
deleted_col = first_col
deleted_col_options = previous_options
@col_info.delete(first_col)
@col_info.keys.sort.each do |col|
col_options = @col_info[col]
# Check if the column number is contiguous with the previous
# column and if the properties are the same.
if (col == last_col + 1) &&
compare_col_info(col_options, previous_options)
last_col = col
else
# If not contiguous/equal then we write out the current range
# of columns and start again.
write_col_info([first_col, last_col, previous_options])
first_col = col
last_col = first_col
previous_options = col_options
end
end
# We will exit the previous loop with one unhandled column range.
write_col_info([first_col, last_col, previous_options])
# Put back the deleted first column information structure:
@col_info[deleted_col] = deleted_col_options
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].width # Col width in user units.
format = args[2].format # Format index.
hidden = args[2].hidden || 0 # Hidden flag.
level = args[2].level || 0 # Outline level.
collapsed = args[2].collapsed || 0 # Outline Collapsed
autofit = args[2].autofit || 0 # Best fit for autofit numbers.
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
width ||= hidden == 0 ? @default_col_width : 0
# Convert column width from user units to character width.
width = if width && width < 1
(((width * (MAX_DIGIT_WIDTH + PADDING)) + 0.5).to_i / MAX_DIGIT_WIDTH.to_f * 256).to_i / 256.0
else
((((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 << ['bestFit', 1] if autofit != 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
@writer.tag_elements('sheetData') { write_rows }
else
# If the dimensions aren't defined then there is no data to write.
@writer.empty_tag('sheetData')
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
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:
row = @cell_data_table[row_num]
row_name = (row_num + 1).to_s
(@dim_colmin..@dim_colmax).each do |col_num|
if (cell = row[col_num])
cell.write_cell(self, row_num, row_name, col_num)
end
end
end
#
# Write the element.
#
def write_row_element(*args, &block) # :nodoc:
@writer.tag_elements('row', row_attributes(args), &block)
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 != @original_row_height
attributes << ['hidden', 1] if ptrue?(hidden)
attributes << ['customHeight', 1] if height != @original_row_height
attributes << ['outlineLevel', level] if ptrue?(level)
attributes << ['collapsed', 1] if ptrue?(collapsed)
attributes << ['x14ac:dyDescent', '0.25'] if @excel_version == 2010
attributes
end
#
# Write the frozen or split elements.
#
def write_panes # :nodoc:
return if @panes.empty?
if @panes[4] == 2
write_split_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.
state = if type == 0
'frozen'
elsif type == 1
'frozenSplit'
else
'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 # :nodoc:
row, col, top_row, left_col = @panes
has_selection = false
y_split = row
x_split = col
# Move user cell selection to the panes.
unless @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.
unless 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.
pixels = if width < 1
int((width * 12) + 0.5)
else
((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', 0],
%w[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, &block)
return if container.empty?
@writer.tag_elements(tag, [['count', container.size]], &block)
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, excel2003_style?)
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:
non_blanks = filters.reject { |filter| filter.to_s =~ /^blanks$/i }
attributes = []
attributes = [['blank', 1]] if filters != non_blanks
if filters.size == 1 && non_blanks.empty?
# Special case for blank cells only.
@writer.empty_tag('filters', attributes)
else
# General case.
@writer.tag_elements('filters', attributes) do
non_blanks.sort.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".
attributes = if tokens[2] == 0
[['and', 1]]
else
[['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
hlink_attributes = []
@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]) &&
@cell_data_table[row_num][col_num].display_url_string?
link.display_on
end
if link.respond_to?(:external_hyper_link)
# External link with rel file relationship.
@rel_count += 1
# Links for use by the packager.
@external_hyper_links << link.external_hyper_link
end
hlink_attributes << link.attributes(row_num, col_num, @rel_count)
end
end
return if hlink_attributes.empty?
# Write the hyperlink elements.
@writer.tag_elements('hyperlinks') do
hlink_attributes.each do |attributes|
@writer.empty_tag('hyperlink', attributes)
end
end
end
#
# Write the element.
#
def write_tab_color # :nodoc:
return unless tab_color?
@writer.empty_tag(
'tabColor',
[
['rgb', 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 element.
#
def write_protected_ranges
return if @num_protected_ranges == 0
@writer.tag_elements('protectedRanges') do
@protected_ranges.each do |protected_range|
write_protected_range(*protected_range)
end
end
end
#
# Write the element.
#
def write_protected_range(sqref, name, password)
attributes = []
attributes << ['password', password] if password
attributes << ['sqref', sqref]
attributes << ['name', name]
@writer.empty_tag('protectedRange', attributes)
end
#
# Write the elements.
#
def write_drawings # :nodoc:
increment_rel_id_and_write_r_id('drawing') if drawings?
end
#
# Write the element.
#
def write_legacy_drawing # :nodoc:
increment_rel_id_and_write_r_id('legacyDrawing') if has_vml?
end
#
# Write the element.
#
def write_legacy_drawing_hf # :nodoc:
return unless has_header_vml?
# Increment the relationship id for any drawings or comments.
@rel_count += 1
attributes = [['r:id', "rId#{@rel_count}"]]
@writer.empty_tag('legacyDrawingHF', attributes)
end
#
# Write the element.
#
def write_picture
return unless ptrue?(@background_image)
# Increment the relationship id.
@rel_count += 1
id = @rel_count
attributes = [['r:id', "rId#{id}"]]
@writer.empty_tag('picture', attributes)
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 for data bars and sparklines.
#
def write_ext_list # :nodoc:
return if @data_bars_2010.empty? && @sparklines.empty?
@writer.tag_elements('extLst') do
write_ext_list_data_bars if @data_bars_2010.size > 0
write_ext_list_sparklines if @sparklines.size > 0
end
end
#
# Write the Excel 2010 data_bar subelements.
#
def write_ext_list_data_bars
# Write the ext element.
write_ext('{78C0D931-6437-407d-A8EE-F0AAD7539E65}') do
@writer.tag_elements('x14:conditionalFormattings') do
# Write each of the Excel 2010 conditional formatting data bar elements.
@data_bars_2010.each do |data_bar|
# Write the x14:conditionalFormatting element.
write_conditional_formatting_2010(data_bar)
end
end
end
end
#
# Write the element.
#
def write_conditional_formatting_2010(data_bar)
xmlns_xm = 'http://schemas.microsoft.com/office/excel/2006/main'
attributes = [['xmlns:xm', xmlns_xm]]
@writer.tag_elements('x14:conditionalFormatting', attributes) do
# Write the ' element.
#
def write_x14_cfvo(type, value)
attributes = [['type', type]]
if %w[min max autoMin autoMax].include?(type)
@writer.empty_tag('x14:cfvo', attributes)
else
@writer.tag_elements('x14:cfvo', attributes) do
@writer.data_element('xm:f', value)
end
end
end
#
# Write the <' element.
#
def write_x14_cf_rule(data_bar)
type = 'dataBar'
id = data_bar[:guid]
attributes = [
['type', type],
['id', id]
]
@writer.start_tag('x14:cfRule', attributes)
end
#
# Write the element.
#
def write_x14_data_bar(data_bar)
min_length = 0
max_length = 100
attributes = [
['minLength', min_length],
['maxLength', max_length]
]
attributes << ['border', 1] unless ptrue?(data_bar[:bar_no_border])
attributes << ['gradient', 0] if ptrue?(data_bar[:bar_solid])
attributes << %w[direction leftToRight] if data_bar[:bar_direction] == 'left'
attributes << %w[direction rightToLeft] if data_bar[:bar_direction] == 'right'
attributes << ['negativeBarColorSameAsPositive', 1] if ptrue?(data_bar[:bar_negative_color_same])
if !ptrue?(data_bar[:bar_no_border]) &&
!ptrue?(data_bar[:bar_negative_border_color_same])
attributes << ['negativeBarBorderColorSameAsPositive', 0]
end
attributes << %w[axisPosition middle] if data_bar[:bar_axis_position] == 'middle'
attributes << %w[axisPosition none] if data_bar[:bar_axis_position] == 'none'
@writer.start_tag('x14:dataBar', attributes)
end
#
# Write the element.
#
def write_x14_border_color(rgb)
attributes = [['rgb', rgb]]
@writer.empty_tag('x14:borderColor', attributes)
end
#
# Write the element.
#
def write_x14_negative_fill_color(rgb)
attributes = [['rgb', rgb]]
@writer.empty_tag('x14:negativeFillColor', attributes)
end
#
# Write the element.
#
def write_x14_negative_border_color(rgb)
attributes = [['rgb', rgb]]
@writer.empty_tag('x14:negativeBorderColor', attributes)
end
#
# Write the element.
#
def write_x14_axis_color(rgb)
attributes = [['rgb', rgb]]
@writer.empty_tag('x14:axisColor', attributes)
end
#
# Write the sparkline subelements.
#
def write_ext_list_sparklines
# Write the ext element.
write_ext('{05C60535-1F16-4fd2-B633-F4F36F0B64E0}') do
# Write the x14:sparklineGroups element.
write_sparkline_groups
end
end
#
# Write the element and subelements.
#
def write_sparklines(sparkline)
# Write the sparkline elements.
@writer.tag_elements('x14:sparklines') do
(0..sparkline[:count] - 1).each do |i|
range = sparkline[:ranges][i]
location = sparkline[:locations][i]
@writer.tag_elements('x14:sparkline') do
@writer.data_element('xm:f', range)
@writer.data_element('xm:sqref', location)
end
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, row, col) # :nodoc:
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.
#
def calculate_spans # :nodoc:
span_min = nil
span_max = 0
spans = []
(@dim_rowmin..@dim_rowmax).each do |row_num|
span_min, span_max = calc_spans(@cell_data_table, row_num, span_min, span_max) if @cell_data_table[row_num]
# Calculate spans for comments.
span_min, span_max = calc_spans(@comments, row_num, span_min, span_max) if @comments[row_num]
next unless ((row_num + 1) % 16 == 0) || (row_num == @dim_rowmax)
span_index = row_num / 16
next unless span_min
span_min += 1
span_max += 1
spans[span_index] = "#{span_min}:#{span_max}"
span_min = nil
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 if col_num < span_min
span_max = col_num if col_num > span_max
else
span_min = col_num
span_max = col_num
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) # :nodoc:
@workbook.shared_string_index(str)
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).
area = if range1 == range2 && !row_col_only
range1
else
"#{range1}:#{range2}"
end
# Build up the print area range "Sheet1!$A$1:$C$13".
"#{quote_sheetname(@name)}!#{area}"
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 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 drawings? # :nodoc:
!!@drawings
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.to_s =~ /^\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.
raise "Column '#{col}' outside autofilter column range (#{col_first} .. #{col_last})" if col < col_first or col > col_last
col
end
#
# Write the element.
#
def write_ignored_errors
return unless @ignore_errors
ignore = @ignore_errors
@writer.tag_elements('ignoredErrors') do
{
number_stored_as_text: 'numberStoredAsText',
eval_error: 'evalError',
formula_differs: 'formula',
formula_range: 'formulaRange',
formula_unlocked: 'unlockedFormula',
empty_cell_reference: 'emptyCellReference',
list_data_validation: 'listDataValidation',
calculated_column: 'calculatedColumn',
two_digit_text_year: 'twoDigitTextYear'
}.each do |key, value|
write_ignored_error(value, ignore[key]) if ignore[key]
end
end
end
#
# Write the element.
#
def write_ignored_error(type, sqref)
attributes = [
['sqref', sqref],
[type, 1]
]
@writer.empty_tag('ignoredError', attributes)
end
end
end