module RubyXL::WorksheetConvenienceMethods

# File lib/rubyXL/convenience_methods.rb, line 557
def change_column_alignment(column_index, &block)
  validate_workbook
  ensure_cell_exists(0, column_index)

  cols.get_range(column_index).style_index = @workbook.modify_alignment(get_col_style(column_index), &block)
  # Excel gets confused if width is not explicitly set for a column that had alignment changes
  change_column_width(column_index) if get_column_width_raw(column_index).nil?

  sheet_data.rows.each { |row|
    c = row[column_index]
    next if c.nil?
    c.style_index = @workbook.modify_alignment(c.style_index, &block)
  }
end

# File lib/rubyXL/convenience_methods.rb, line 503
def change_column_bold(column_index, bolded = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_bold(bolded)
  change_column_font(column_index, Worksheet::BOLD, bolded, font, xf)
end

# File lib/rubyXL/convenience_methods.rb, line 532
def change_column_border(column_index, direction, weight)
  validate_workbook
  ensure_cell_exists(0, column_index)

  cols.get_range(column_index).style_index = @workbook.modify_border(get_col_style(column_index), direction, weight)

  sheet_data.rows.each { |row|
    c = row.cells[column_index]
    c.change_border(direction, weight) unless c.nil?
  }
end

# File lib/rubyXL/convenience_methods.rb, line 487
def change_column_font_color(column_index, font_color='000000')
  Color.validate_color(font_color)

  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_rgb_color(font_color)
  change_column_font(column_index, Worksheet::COLOR, font_color, font, xf)
end

# File lib/rubyXL/convenience_methods.rb, line 473
def change_column_font_name(column_index = 0, font_name = 'Verdana')
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_name(font_name)
  change_column_font(column_index, Worksheet::NAME, font_name, font, xf)
end

# File lib/rubyXL/convenience_methods.rb, line 480
def change_column_font_size(column_index, font_size=10)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_size(font_size)
  change_column_font(column_index, Worksheet::SIZE, font_size, font, xf)
end

# File lib/rubyXL/convenience_methods.rb, line 524
def change_column_horizontal_alignment(column_index, alignment = 'center')
  change_column_alignment(column_index) { |a| a.horizontal = alignment }
end

# File lib/rubyXL/convenience_methods.rb, line 496
def change_column_italics(column_index, italicized = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_italic(italicized)
  change_column_font(column_index, Worksheet::ITALICS, italicized, font, xf)
end

# File lib/rubyXL/convenience_methods.rb, line 517
def change_column_strikethrough(column_index, struckthrough=false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_strikethrough(struckthrough)
  change_column_font(column_index, Worksheet::STRIKETHROUGH, struckthrough, font, xf)
end

# File lib/rubyXL/convenience_methods.rb, line 510
def change_column_underline(column_index, underlined = false)
  xf = get_col_xf(column_index)
  font = @workbook.fonts[xf.font_id].dup
  font.set_underline(underlined)
  change_column_font(column_index, Worksheet::UNDERLINE, underlined, font, xf)
end

# File lib/rubyXL/convenience_methods.rb, line 528
def change_column_vertical_alignment(column_index, alignment = 'center')
  change_column_alignment(column_index) { |a| a.vertical = alignment }
end

# File lib/rubyXL/convenience_methods.rb, line 544
def change_row_alignment(row, &block)
  validate_workbook
  validate_nonnegative(row)
  ensure_cell_exists(row)

  sheet_data.rows[row].style_index = @workbook.modify_alignment(get_row_style(row), &block)

  sheet_data[row].cells.each { |c|
    next if c.nil?
    c.style_index = @workbook.modify_alignment(c.style_index, &block)
  }
end

# File lib/rubyXL/convenience_methods.rb, line 443
def change_row_bold(row = 0, bolded = false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_bold(bolded)
  change_row_font(row, Worksheet::BOLD, bolded, font)
end

# File lib/rubyXL/convenience_methods.rb, line 394
def change_row_border(row, direction, weight)
  validate_workbook
  ensure_cell_exists(row)

  sheet_data.rows[row].style_index = @workbook.modify_border(get_row_style(row), direction, weight)

  sheet_data[row].cells.each { |c|
    c.change_border(direction, weight) unless c.nil?
  }
end

# File lib/rubyXL/convenience_methods.rb, line 405
def change_row_fill(row_index = 0, rgb = 'ffffff')
  validate_workbook
  ensure_cell_exists(row_index)
  Color.validate_color(rgb)

  sheet_data.rows[row_index].style_index = @workbook.modify_fill(get_row_style(row_index), rgb)
  sheet_data[row_index].cells.each { |c| c.change_fill(rgb) unless c.nil? }
end

# File lib/rubyXL/convenience_methods.rb, line 428
def change_row_font_color(row = 0, font_color = '000000')
  ensure_cell_exists(row)
  Color.validate_color(font_color)
  font = row_font(row).dup
  font.set_rgb_color(font_color)
  change_row_font(row, Worksheet::COLOR, font_color, font)
end

# File lib/rubyXL/convenience_methods.rb, line 414
def change_row_font_name(row = 0, font_name = 'Verdana')
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_name(font_name)
  change_row_font(row, Worksheet::NAME, font_name, font)
end

# File lib/rubyXL/convenience_methods.rb, line 421
def change_row_font_size(row = 0, font_size=10)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_size(font_size)
  change_row_font(row, Worksheet::SIZE, font_size, font)
end

# File lib/rubyXL/convenience_methods.rb, line 464
def change_row_height(row = 0, height = 10)
  validate_workbook
  ensure_cell_exists(row)

  c = sheet_data.rows[row]
  c.ht = height
  c.custom_height = true
end

# File lib/rubyXL/convenience_methods.rb, line 382
def change_row_horizontal_alignment(row = 0, alignment = 'center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row) { |a| a.horizontal = alignment }
end

# File lib/rubyXL/convenience_methods.rb, line 436
def change_row_italics(row = 0, italicized = false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_italic(italicized)
  change_row_font(row, Worksheet::ITALICS, italicized, font)
end

# File lib/rubyXL/convenience_methods.rb, line 457
def change_row_strikethrough(row = 0, struckthrough=false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_strikethrough(struckthrough)
  change_row_font(row, Worksheet::STRIKETHROUGH, struckthrough, font)
end

# File lib/rubyXL/convenience_methods.rb, line 450
def change_row_underline(row = 0, underlined=false)
  ensure_cell_exists(row)
  font = row_font(row).dup
  font.set_underline(underlined)
  change_row_font(row, Worksheet::UNDERLINE, underlined, font)
end

# File lib/rubyXL/convenience_methods.rb, line 388
def change_row_vertical_alignment(row = 0, alignment = 'center')
  validate_workbook
  validate_nonnegative(row)
  change_row_alignment(row) { |a| a.vertical = alignment }
end

# File lib/rubyXL/convenience_methods.rb, line 210
def delete_cell(row_index = 0, column_index=0, shift=nil)
  validate_workbook
  validate_nonnegative(row_index)
  validate_nonnegative(column_index)

  row = sheet_data[row_index]
  old_cell = row && row[column_index]

  case shift
  when nil then
    row.cells[column_index] = nil if row
  when :left then
    row.delete_cell_shift_left(column_index) if row
  when :up then
    (row_index...(sheet_data.size - 1)).each { |index|
      c = sheet_data.rows[index].cells[column_index] = sheet_data.rows[index + 1].cells[column_index]
      c.row -= 1 if c.is_a?(Cell)
    }
  else
    raise 'invalid shift option'
  end

  return old_cell
end

# File lib/rubyXL/convenience_methods.rb, line 573
def extract_data(args = {})
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please access data directly by iterating through .sheet_data.rows"
  sheet_data.rows.map { |row|
    row.cells.map { |c| c && c.value(args) } unless row.nil?
  }
end

# File lib/rubyXL/convenience_methods.rb, line 374
def get_column_alignment(col, type)
  validate_workbook
  validate_nonnegative(col)

  xf = @workbook.cell_xfs[get_cols_style_index(col)]
  xf.alignment && xf.alignment.send(type)
end

# File lib/rubyXL/convenience_methods.rb, line 365
def get_column_border(col, border_direction)
  validate_workbook
  validate_nonnegative(col)

  xf = @workbook.cell_xfs[get_cols_style_index(col)]
  border = @workbook.borders[xf.border_id]
  border && border.get_edge_style(border_direction)
end

# File lib/rubyXL/convenience_methods.rb, line 358
def get_column_fill(col=0)
  validate_workbook
  validate_nonnegative(col)

  @workbook.get_fill_color(get_col_xf(col))
end

# File lib/rubyXL/convenience_methods.rb, line 316
def get_column_font_color(col = 0)
  font = column_font(col)
  font && (font.get_rgb_color || '000000')
end

# File lib/rubyXL/convenience_methods.rb, line 306
def get_column_font_name(col = 0)
  font = column_font(col)
  font && font.get_name
end

# File lib/rubyXL/convenience_methods.rb, line 311
def get_column_font_size(col = 0)
  font = column_font(col)
  font && font.get_size
end

# File lib/rubyXL/convenience_methods.rb, line 352
def get_column_width(column_index = 0)
  width = get_column_width_raw(column_index)
  return RubyXL::ColumnRange::DEFAULT_WIDTH if width.nil?
  (width - (5.0 / RubyXL::Font::MAX_DIGIT_WIDTH)).round
end

# File lib/rubyXL/convenience_methods.rb, line 342
def get_column_width_raw(column_index = 0)
  validate_workbook
  validate_nonnegative(column_index)

  range = cols.locate_range(column_index)
  range && range.width
end

# File lib/rubyXL/convenience_methods.rb, line 284
def get_row_alignment(row, is_horizontal)
  validate_workbook
  validate_nonnegative(row)

  xf_obj = get_row_xf(row)
  return nil if xf_obj.alignment.nil?

  if is_horizontal then return xf_obj.alignment.horizontal
  else                  return xf_obj.alignment.vertical
  end
end

# File lib/rubyXL/convenience_methods.rb, line 276
def get_row_border(row, border_direction)
  validate_workbook
  validate_nonnegative(row)

  border = @workbook.borders[get_row_xf(row).border_id]
  border && border.get_edge_style(border_direction)
end

# File lib/rubyXL/convenience_methods.rb, line 235
def get_row_fill(row = 0)
  (row = sheet_data.rows[row]) && row.get_fill_color
end

# File lib/rubyXL/convenience_methods.rb, line 247
def get_row_font_color(row = 0)
  font = row_font(row)
  color = font && font.color
  color && (color.rgb || '000000')
end

# File lib/rubyXL/convenience_methods.rb, line 239
def get_row_font_name(row = 0)
  (font = row_font(row)) && font.get_name
end

# File lib/rubyXL/convenience_methods.rb, line 243
def get_row_font_size(row = 0)
  (font = row_font(row)) && font.get_size
end

# File lib/rubyXL/convenience_methods.rb, line 269
def get_row_height(row = 0)
  validate_workbook
  validate_nonnegative(row)
  row = sheet_data.rows[row]
  row && row.ht || 13
end

# File lib/rubyXL/convenience_methods.rb, line 296
def get_row_horizontal_alignment(row = 0)
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `get_row_alignment` instead."
  return get_row_alignment(row, true)
end

# File lib/rubyXL/convenience_methods.rb, line 301
def get_row_vertical_alignment(row = 0)
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please use `get_row_alignment` instead."
  return get_row_alignment(row, false)
end

# File lib/rubyXL/convenience_methods.rb, line 580
def get_table(headers = [], opts = {})
  warn "[DEPRECATION] `#{__method__}` is deprecated.  Please access data directly by iterating through .sheet_data.rows"
  validate_workbook

  headers = [headers] unless headers.is_a?(Array)
  row_num = find_first_row_with_content(headers)
  return nil if row_num.nil?

  table_hash = {}
  table_hash[:table] = []

  header_row = sheet_data[row_num]
  header_row.cells.each_with_index { |header_cell, index|
    break if index>0 && !opts[:last_header].nil? && !header_row[index-1].nil? && !header_row[index-1].value.nil? && header_row[index-1].value.to_s==opts[:last_header]
    next if header_cell.nil? || header_cell.value.nil?
    header = header_cell.value.to_s
    table_hash[:sorted_headers]||=[]
    table_hash[:sorted_headers] << header
    table_hash[header] = []

    original_row = row_num + 1
    current_row = original_row

    row = sheet_data.rows[current_row]
    cell = row && row.cells[index]

    # makes array of hashes in table_hash[:table]
    # as well as hash of arrays in table_hash[header]
    table_index = current_row - original_row
    cell_test = (!cell.nil? && !cell.value.nil?)

    while cell_test || (table_hash[:table][table_index] && !table_hash[:table][table_index].empty?)
      table_hash[header] << cell.value if cell_test
      table_index = current_row - original_row

      if cell_test then
        table_hash[:table][table_index] ||= {}
        table_hash[:table][table_index][header] = cell.value
      end

      current_row += 1
      if sheet_data.rows[current_row].nil? then
        cell = nil
      else
        cell = sheet_data.rows[current_row].cells[index]
      end
      cell_test = (!cell.nil? && !cell.value.nil?)
    end
  }

  return table_hash
end

# File lib/rubyXL/convenience_methods.rb, line 187
def insert_cell(row = 0, col = 0, data = nil, formula = nil, shift = nil)
  validate_workbook
  ensure_cell_exists(row, col)

  case shift
  when nil then # No shifting at all
  when :right then
    sheet_data.rows[row].insert_cell_shift_right(nil, col)
  when :down then
    add_row(sheet_data.size, :cells => Array.new(sheet_data.rows[row].size))
    (sheet_data.size - 1).downto(row+1) { |index|
      sheet_data.rows[index].cells[col] = sheet_data.rows[index-1].cells[col]
    }
  else
    raise 'invalid shift option'
  end

  return add_cell(row,col,data,formula)
end

# File lib/rubyXL/convenience_methods.rb, line 326
def is_column_bolded(col = 0)
  font = column_font(col)
  font && font.is_bold
end

# File lib/rubyXL/convenience_methods.rb, line 321
def is_column_italicized(col = 0)
  font = column_font(col)
  font && font.is_italic
end

# File lib/rubyXL/convenience_methods.rb, line 336
def is_column_struckthrough(col = 0)
  font = column_font(col)
  font && font.is_strikethrough
end

# File lib/rubyXL/convenience_methods.rb, line 331
def is_column_underlined(col = 0)
  font = column_font(col)
  font && font.is_underlined
end

# File lib/rubyXL/convenience_methods.rb, line 257
def is_row_bolded(row = 0)
  (font = row_font(row)) && font.is_bold
end

# File lib/rubyXL/convenience_methods.rb, line 253
def is_row_italicized(row = 0)
  (font = row_font(row)) && font.is_italic
end

# File lib/rubyXL/convenience_methods.rb, line 265
def is_row_struckthrough(row = 0)
  (font = row_font(row)) && font.is_strikethrough
end

# File lib/rubyXL/convenience_methods.rb, line 261
def is_row_underlined(row = 0)
  (font = row_font(row)) && font.is_underlined
end