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
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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! 🙂
PUNEET PANDEY's WEBLOG 