Add duplicate rows in Excel using Ruby

Puneet Pandey

Happy New Year Folks,

Few days back a friend of mine came to me and said, “I am a RUBY Programmer and I am stuck in a requirement, wherein I have to read an excel-sheet (that contains multiple records and each record can have multiple entries in it.)”

Think of an example like given in the link: timesheet. It says we have 2 columns: EmployeeID and WorkingHours. Each EmployeeID can have multiple working hours.

Now, I would like to write a small Ruby program that iterate over each record in the excel, combine EmployeeID’s that has multiple entries and add working hours.

Let’s code it then!

Requirements:

  • Ruby 1.9.2 or higher
  • Spreadsheet gem: to read Excel
  • Download timesheet excel from the link above


require 'spreadsheet'
Spreadsheet.client_encoding = 'UTF-8'
class TimeSheet
def calculate_total_working_hours
employeeHash = {}
book = Spreadsheet.open 'timesheet.xls'
default_sheet = book.worksheet 0 # If there are multiple worksheet it will take the 1rst.
default_sheet.each 1 do |row| # This will start from the 1st record.
employeeHash[row[0]] = employeeHash.has_key?(row[0]) ? employeeHash[row[0]] + row[1] : row[1]
end
puts "Timesheet entry***** #{employeeHash.inspect}"
end
end
time_sheet = TimeSheet.new
time_sheet.calculate_total_working_hours

view raw

time_sheet.rb

hosted with ❤ by GitHub

Happy Coding! 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s