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.
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
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)
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.