Playing with Excel Files in Ruby Using Roo and Write_XLSX

Because of a small tool pitch I gave to our technical writer, I had a chance to play with Excel files through code for several hours last week. I have not done that before, but I felt that it was a pretty easy enough challenge as I have worked with CSV files in the past and thought that the experience would be something similar. I knew I just have to find out what existing libraries I could take advantage in order to build what I thought the program should be capable of doing, which is basically reading two Excel files, comparing them, and creating a new file with updated data based on both files.

Some googling brought me to the ruby gems Roo and Write_XLSX.

Roo helped with reading Excel files, and we can do this by:

xlsx = Roo::Spreadsheet.open(FILE_PATH)
xlsx = Roo::Excelx.new(xlsx)
spreadsheet = xlsx.sheet(SHEET_NUMBER)

Once we have access to a desired Excel file, we can get whatever data we need from it:

spreadsheet.each(column_data_1: COLUMN_NAME_1) do |info|
    // do something with information
end

After that, it’s just a matter of what information do we want to retrieve and manipulate from desired files. I mostly like to use arrays and hashes from here on.

As for creating new Excel files, we can use write_xlsx:

workbook = WriteXLSX.new(FILE_PATH)
worksheet = workbook.add_worksheet
worksheet.write(ROW_NUMBER, COLUMN_NUMBER, INFORMATION)
workbook.close

These two libraries can actually do more. Roo has an interesting read/write access with Google spreadsheets using roo-google. Write_XLSX on the other hand have formatting features we can leverage for better looking Excel outputs. I didn’t have to use those functionalies for the current test project though so I’ll leave those for another time.

Advertisements

Leave a Reply

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

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 )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s