As it was asked how we handle reading content from Excel, this is a very quick blog post that goes over what we do for that. First you will need the following added to your Gemfile and then bundle install:
gem 'roo', :git => 'https://github.com/roo-rb/roo'
gem 'roo-xls', :git => 'https://github.com/roo-rb/roo-xls.git'
Now that we have the library that will allow us to read the spreadsheet, we can go ahead and setup a variable to hold the content of the spreadsheet. This assumes you have a “sheet_location” variable set that indicates where the file you are trying to read lives (be it uploaded or not) and assigns the content to @worksheet:
if sheet_location =~ /\b.xlsx$\b/
@worksheet = Roo::Excelx.new(sheet_location)
elsif sheet_location =~ /\b.xls$\b/
@worksheet = Roo::Excel.new(sheet_location)
elsif sheet_location =~ /\b.csv\b/
@worksheet = Roo::CSV.new(sheet_location)
elsif sheet_location =~ /\b.ods\b/
@worksheet = Roo::OpenOffice.new(sheet_location)
end
@worksheet.default_sheet = @worksheet.sheets.first #Sets to the first sheet in the workbook
The next thing we want to do is grab the header row that has our column headers. At the BPL, this is the third row in the spreadsheet (previous rows are for notes). As such, with the library using “1” as its first row, we would get the header row via the following:
header_row_index = 3
@header_row = @worksheet.row(header_row_index)
From here, I loop through each data row in my spreadsheet (which starts at index 5 for us) and pass that row value along with the header row to a method to process that row. It looks something like:
data_start_row = 5
data_start_row_index.upto @worksheet.last_row do |index| row = @worksheet.row(index)
if row.present? && @header_row.present?
begin
process_a_row(@header_row, row)
rescue Exception => e
#Exception handling for when encounter bad data...
end
end
end
end
Now we have each row in our spreadsheet being processed! But… how do we access each individual cell? In our case, our spreadsheet template has over 150 possible headers and having a spreadsheet with every header becomes unwieldy. As such, each one has some combination of potential headers and the order of those headers in the spreadsheet is not guaranteed. So we end up with something like the following to get the value of “title” out of our spreadsheet:
def process_a_row(header_row, row_value)
# ...
title = find_in_row(header_row, row_value, 'title_primary')
# ...
end
Essentially this is calling a method called “find_in_row” from within the “process_a_row” block and adds a third argument of the row header identifier we are using to find that data element. The “find_in_row” method then looks like:
def find_in_row(header_row, row_value, column_identifier)
0.upto header_row.length do |row_pos|
case header_row[row_pos]
when column_identifier
return strip_value(row_value[row_pos])
end
end
return nil
end
This has another new method: strip_value. The plan is to move this function into “Bpl_Enrich” in the near future but essentially this is to return our data elements as UTF-8 strings. The code for this looks like:
def strip_value(value)
if(value == nil)
return nil
else
if value.class == Float
value = value.to_f.to_s
value = value.gsub(/.0$/, '') #FIXME: Temporary. Bugged as see: https://github.com/roo-rb/roo/issues/86 , https://github.com/roo-rb/roo/issues/133 , https://github.com/zdavatz/spreadsheet/issues/41
elsif value.class == Fixnum
value = value.to_i.to_s #FIXME: to_i as otherwise non-existant values cause problems
end
# Make sure it is all UTF-8 and not character encodings or HTML tags and remove any cariage returns
return utf8Encode(value)
end
end
def utf8Encode(value)
return HTMLEntities.new.decode(ActionView::Base.full_sanitizer.sanitize(value.to_s.gsub(/\r?\n?\t/, ' ').gsub(/\r?\n/, ' '))).strip
end
We can now repeat the calls to “find_in”row” within the “process_a_row” method for each of our data elements and insert them into our system as needed. But what do we do for multi-valued fields? We use a deliminator of a double pipe (“||”) to delineate values in those cases. For example, if our title was allowed to be multi-valued, we could have the above return “title1||title2″. There is then another helper function to convert that into an array as the following:
def split_with_nils(value)
if(value == nil)
return ""
else
split_value = value.split("||")
0.upto split_value.length-1 do |pos|
split_value[pos] = strip_value(split_value[pos])
end
return split_value
end
end
Why do I return “” on the nil case? To make processing easier for related pairs of multivalued column fields when doing indexing without an extra logic check in the inserts of “process_a_row”. As a full example, assume we have titles of “title1||title2||title3″ and title types of “primary||||alternative” (ie. the second title lacks a type in this made up example of bad MODS data). You would do something like:
title = find_in_row(header_row, row_value, 'title')
if title.present?
title_list = split_with_nils(title)
title_type_list = split_with_nils(find_in_row(header_row, row_value, 'title_type'))
0.upto title_list.length-1 do |pos|
@digital_object.descMetadata.insert_title(title_list[pos],title_type_list[pos])
end
end
Of course, in the insert title method, you would need to check for blank values to not insert the empty title type value for the second title in our list. If there was no “title_type” specified at all as that was omitted as an optional field, our function would still work as indexing an empty string (the returned “” from split_with_nils) would just give us all blank values for the title_type as we index through it.
I hope this was somewhat useful, not a completely crazy approach, and made some sense. Take care!
