Method: Writexlsx::Worksheet#write_array_formula

Defined in:
lib/write_xlsx/worksheet.rb

#write_array_formula(*args) ⇒ Object

:call-seq:

write_array_formula(row1, col1, row2, col2, formula [ , format [ , value ] ] )

Write an array formula to the specified row and column (zero indexed).

format is optional.

In Excel an array formula is a formula that performs a calculation on a set of values. It can return a single value or a range of values.

An array formula is indicated by a pair of braces around the formula: =SUM(A1:B1*A2:B2). If the array formula returns a single value then the first and last parameters should be the same:

worksheet.write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}')

It this case however it is easier to just use the write_formula() or write() methods:

# Same as above but more concise.
worksheet.write('A1', '{=SUM(B1:C1*B2:C2)}')
worksheet.write_formula('A1', '{=SUM(B1:C1*B2:C2)}')

For array formulas that return a range of values you must specify the range that the return values will be written to:

worksheet.write_array_formula('A1:A3',    '{=TREND(C1:C3,B1:B3)}')
worksheet.write_array_formula(0, 0, 2, 0, '{=TREND(C1:C3,B1:B3)}')

If required, it is also possible to specify the calculated value of the formula. This is occasionally necessary when working with non-Excel applications that don’t calculate the value of the formula. The calculated value is added at the end of the argument list:

worksheet.write_array_formula('A1:A3', '{=TREND(C1:C3,B1:B3)}', format, 105)

In addition, some early versions of Excel 2007 don’t calculate the values of array formulas when they aren’t supplied. Installing the latest Office Service Pack should fix this issue.

See also the array_formula.rb program in the examples directory of the distro.

Note: Array formulas are not supported by writeexcel gem.

Raises:



2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
 
# File 'lib/write_xlsx/worksheet.rb', line 2399

def write_array_formula(*args)
  # Check for a cell reference in A1 notation and substitute row and column
  row1, col1, row2, col2, formula, xf, value = row_col_notation(args)
  raise WriteXLSXInsufficientArgumentError if [row1, col1, row2, col2, formula].include?(nil)

  # Swap last row/col with first row/col as necessary
  row1, row2 = row2, row1 if row1 > row2
  col1, col2 = col2, col1 if col1 > col2

  # Check that row and col are valid and store max and min values
  check_dimensions(row2, col2)
  store_row_col_max_min_values(row2, col2)

  # Define array range
  if row1 == row2 && col1 == col2
    range = xl_rowcol_to_cell(row1, col1)
  else
    range ="#{xl_rowcol_to_cell(row1, col1)}:#{xl_rowcol_to_cell(row2, col2)}"
  end

  # Remove array formula braces and the leading =.
  formula.sub!(/^\{(.*)\}$/, '\1')
  formula.sub!(/^=/, '')

  store_data_to_table(FormulaArrayCellData.new(self, row1, col1, formula, xf, range, value))
end