I have been trying to convert a Dashing widget called spreadsheetDemo to use the Roo GEM to simplify it and allow for it to cater for local and Google Doc Excel files.(Simply the same just with Roo) I began by looking at other widgets and sources (similar to this just to a table not graph) that are already using Roo and I have tried to reuse their code for the file source in place of the original, resulting in my code below. I am trying to use Roo to access the local spreadsheet, take the headings and data for the 6 columns and rows and then push them to a table. I believe my problem may lie with accessing the speadsheet correctly. Everything is on the first workbook and starts in cell A1.
Sources I have used: https:// github.com/Shopify/dashing/issues/78#issuecomment-14940695
http:// stackoverflow.com/questions/29400811/use-with-excel-data-to-display-on-dashing-dashboard/29421179?newreg=5c8c7db27d104f5ab3b855c8ea729bc8
require 'roo'
#Tried using roo-xls as it started throwing errors saying I needed too.
#require 'roo-xls'
EM.kqueue = EM.kqueue?
file_path = "#{Dir.pwd}/spreadsheet.xls"
def fetch_spreadsheet_data(path)
s = Roo::Excel.new(path)
ws = workbook.sheets[0]
# create an array to hold the lines
rows = Array.new
header = Array.new
# other variables
up = ws.updated.localtime
up = up.strftime "%d.%m.%Y %H:%M"
#workbook.default_sheet = workbook.sheets[0]
# fill lines
for row in 1..ws.num_rows
if row == 1
header.push({ :c1 => ws[row, 1], :c2 => ws[row, 2], :c3 => ws[row, 3], :c4 => ws[row, 4], :c5 => ws[row, 5], :c6 => ws[row, 6]})
else
rows.push({ :c1 => ws[row, 1], :c2 => ws[row, 2], :c3 => ws[row, 3], :c4 => ws[row, 4], :c5 => ws[row, 5], :c6 => ws[row, 6]})
end
end
send_event('spread', :modified => up, :header => header, :rows => rows)
rescue => err
#puts "Exception: #{err}"
end
module Handler
def file_modified
fetch_spreadsheet_data(path)
end
end
fetch_spreadsheet_data(file_path)
end
