Ruby spreadsheet example

ruby_spreadsheet_example.rb

1 require 'spreadsheet'

# Open workbooks
current_workbook = Spreadsheet.open('jan.xls')
total_workbook   = Spreadsheet.open('output.xls')
 
# Process xls
# Iterate through workbook spreadsheets
current_workbook.worksheets.each_with_index do |current_spreadsheet, spreadsheet_index|
  
  # Open current sheet on total xls 
  total_sheet = total_workbook.worksheet(spreadsheet_index)
  
  row_blacklist = [0, 1, 7]
  col_blacklist = [0, 3]
  
  current_spreadsheet.each_with_index do |row, row_idx|
    row.each_with_index do |value, col_idx|
      if row_blacklist.include?(row_idx)
        next
      end
      if col_blacklist.include?(col_idx)
        next
      end
  
      if value.class.to_s != 'Spreadsheet::Formula'
        total_sheet.row(row_idx)[col_idx] = total_sheet.row(row_idx)[col_idx].to_i + value.to_i
      end
    end
  end
  
  total_workbook.write('output2.xls')
end