CODEWITHSUNDEEP
rubyformatspreadsheetnull
heinztomato
heinztomato

Reputation: 835

read excel file, work around or with nil? values, edit/format individual column

I have a spreadsheet with 146 columns and 530 rows. I want to iterate through certain columns, find nil values and change the value based on the class type. I cant figure out how to do this, right now I have this going for me:

require 'spreadsheet'


Spreadsheet.client_encoding = 'UTF-8'
raw_data = Spreadsheet.open '../October 2014.xls'
raw_data_sheet = raw_data.worksheet 0

designated_rows = raw_data_sheet.each {|row| [row[6], row[7], row[15], row[14], row[16], row[17], row[18], row[139], row[3], row[19]]}

I have tried iterations like if row[3].nil? ? row[3] = "blank" : row[3].strip!; end but I can only get these to run, not actually change the value of column 3 in this file. I would preferably like to find the nil values in these columns and change them to a designated value base on their class, the classes look as follows:

[String, String, Float, Float, Float, Float, Float, Date, String, Float] with cell reference its ["row[6]: String", "row[7]: String", "row[15]: Float", "row[14]: Float", "row[16]: Float", "row[17]: Float", "row[18]: Float", "row[139]: Date", "row[3]: String", "row[19]: Float"] if thats important.

can anyone assist with this one? would you need more information? thank you for your responses.

Upvotes: 0

Views: 462

Answers (1)

Anthony
Anthony

Reputation: 15967

designated_rows is holding the entire spreadsheet in memory, now that you have that variable you need to save it down as a new spreadsheet file, or you can do this after you've modified everything:

raw_data = Spreadsheet.open '../October 2014.xls'
raw_data_sheet = raw_data.worksheet 0
keys = ["N/A", "N/A", 0.0, 0.0, 0.0, 0.0, 0.0, "N/A", "N/A", 0.0]
raw_data_sheet.each do |row|
  example_row.each_with_index { |val, i| example_row[i] = keys[i] if val.nil? }
end
raw_data.write '../October 2014.xls'

example for the nil logic:

[7] pry(main)> example_row = ["hi", nil, 9.9, 8.8, nil, 5.5, 1.2, nil, "hello", 1.1]
=> ["hi", nil, 9.9, 8.8, nil, 5.5, 1.2, nil, "hello", 1.1]
[8] pry(main)> example_row.each_with_index { |val, i| example_row[i] = keys[i] if val.nil? }
=> ["hi", "N/A", 9.9, 8.8, 0.0, 5.5, 1.2, "N/A", "hello", 1.1]

Upvotes: 1

Related Questions