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 |
Happy Coding! 🙂
