Dashboard Excel integration

[thefixxxer75]

Hi all, I've tried to integrate some xls data with dashboard, using some old post and modifying "sample.rb". When I start "smashing start", I get the following error

bundler: failed to load command: thin (/usr/local/bin/thin) LoadError: cannot load such file -- roo /var/lib/gems/2.3.0/gems/backports-3.6.8/lib/backports/std_lib.rb:9:in require' /var/lib/gems/2.3.0/gems/backports-3.6.8/lib/backports/std_lib.rb:9:inrequire_with_backports' /home/kino/nagioszurich/jobs/sample.rb:1:in <top (required)>' /var/lib/gems/2.3.0/gems/backports-3.6.8/lib/backports/std_lib.rb:9:inrequire' /var/lib/gems/2.3.0/gems/backports-3.6.8/lib/backports/std_lib.rb:9:in require_with_backports' /var/lib/gems/2.3.0/gems/smashing-1.0.0/lib/dashing/app.rb:171:inblock in require_glob' /var/lib/gems/2.3.0/gems/smashing-1.0.0/lib/dashing/app.rb:170:in each' /var/lib/gems/2.3.0/gems/smashing-1.0.0/lib/dashing/app.rb:170:inrequire_glob' /var/lib/gems/2.3.0/gems/smashing-1.0.0/lib/dashing/app.rb:181:in <top (required)>' /var/lib/gems/2.3.0/gems/smashing-1.0.0/lib/dashing.rb:3:inrequire' /var/lib/gems/2.3.0/gems/smashing-1.0.0/lib/dashing.rb:3:in <top (required)>' config.ru:1:inrequire' config.ru:1:in block in <main>' /var/lib/gems/2.3.0/gems/rack-1.5.5/lib/rack/builder.rb:55:ininstance_eval' /var/lib/gems/2.3.0/gems/rack-1.5.5/lib/rack/builder.rb:55:in initialize' config.ru:1:innew' config.ru:1:in <main>' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/rack/adapter/loader.rb:33:ineval' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/rack/adapter/loader.rb:33:in load' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/thin/controllers/controller.rb:182:inload_rackup_config' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/thin/controllers/controller.rb:72:in start' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/thin/runner.rb:200:inrun_command' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/thin/runner.rb:156:in run!' /var/lib/gems/2.3.0/gems/thin-1.6.4/bin/thin:6:in<top (required)>' /usr/local/bin/thin:23:in load' /usr/local/bin/thin:23:in<top (required)>'

I've the spreadsheet.xls in the root dir and all the permissions are fine. I've installed roo and roo-xls via gem...but seems not working yet. Please, someone has already passed through that? Thanks in advance for your support

[ghost]

I have this working...can you post your sample.rb & Gemfile please.

What version of excel is the file created in & what is its file extension xls,xlsx, xlsm etc?

[thefixxxer75]

sample.rb

!/usr/bin/env ruby

require 'roo'

SCHEDULER.every '2s' do file_path = "/path/to/spreadsheet.xls"

def fetch_spreadsheet_data(path) s = Roo::Excel.new(path) send_event('valuation', { current: s.cell('B',49) })

end

module Handler def file_modified fetch_spreadsheet_data(path) end end

Gemfile

source 'https://rubygems.org'

gem 'smashing' gem 'nagiosharder', :git => 'https://github.com/railsmachine/nagiosharder.git'

Remove this if you don't need a twitter widget.

gem 'twitter', '>= 5.9.0'

[thefixxxer75]

extension is xls, done with LibreOffice Calc

[ghost]

I'll assume that an xls file created in LibreOffice is 100% compatible with this solution, I use this method with Excel created files.

Additionally I'm assuming that you have read/write permissions to the location of your xls file.

Make a backup of your smashing folder.

Now replace the contents of your Sample.rb with the following:

Copy from below this line

require 'roo' require 'roo-xls'

SCHEDULER.every '2s', :first_in => 0 do |job| file_path = "#{Dir.pwd}/THIS_IS_THE_PATH_LOCATION_TO_THE_XLS_FILE/YOUR_FILENAME.xls"

s = Roo::Excel.new(path) send_event('valuation', { current: s.cell ('B',49) })

end

module Handler def file_modified fetch_spreadsheet_data(path) end end

to above this line

Add the next 2 lines to your Gem file

gem 'roo', '~> 2.1.0' gem 'roo-xls'

At the command line run (without the quotes) "bundle"

Check for errors...if you have any post them back to this thread. Otherwise, start your dashboard.....fingers crossed.

[thefixxxer75]

thanks for your reply!! I've tried but here's results

SyntaxError: /home/xxxxxx/xxxxxxxxx/jobs/sample.rb:8: syntax error, unexpected ( arg, expecting '}' send_event('valuation', { current: s.cell ('B',49) }) ^ /home/xxxxxx/xxxxxxxx/jobs/sample.rb:8: syntax error, unexpected ',', expecting ')' send_event('valuation', { current: s.cell ('B',49) }) ^ /home/xxxxxx/xxxxxxxx/jobs/sample.rb:16: syntax error, unexpected end-of-input, expecting keyword_end

/var/lib/gems/2.3.0/gems/thin-1.6.4/lib/rack/adapter/loader.rb:33:in eval' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/rack/adapter/loader.rb:33:inload' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/thin/controllers/controller.rb:182:in load_rackup_config' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/thin/controllers/controller.rb:72:instart' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/thin/runner.rb:200:in run_command' /var/lib/gems/2.3.0/gems/thin-1.6.4/lib/thin/runner.rb:156:inrun!' /var/lib/gems/2.3.0/gems/thin-1.6.4/bin/thin:6:in <top (required)>' /usr/local/bin/thin:23:inload' /usr/local/bin/thin:23:in `<top (required)>'

any idea about it? Syntax seems fine or am I wrong? thx in advance

[ghost]

you're getting close...

I'm not on a system I can test this but I think there is a formatting problem, aka something got muddled with the copy and paste. Replace send_event('valuation', { current: s.cell ('B',49) })

with send_event('valuation', { current: s.cell ('B',49) })

Yeah I know it looks exactly the same but humor me....

[thefixxxer75]

same error here...I don't know where the problem is. I'm pointing to the right path of the xls file but seems that this sample.rb has something wrong. It's quite weird...

[ghost]

I'll take a closer look tomorrow and post back...

[ghost]

sorry for the delay. Try this line in your .rb file instead of the line that is there. I had eyeballed my own job file with multiple cells in one 'send_event', needed to tidy that up.

send_event('valuation', {current: s.cell('B',49)})

[thefixxxer75]

thanks sooooooooooooo much for your time. Now error is changed Smashing start but this log

scheduler caught exception:

undefined local variable or method path' for main:Object Did you mean? patch /home/xxxxx/xxxxxxxxx/jobs/sample.rb:7:inblock in <top (required)>' /var/lib/gems/2.3.0/gems/rufus-scheduler-2.0.24/lib/rufus/sc/jobs.rb:230:in trigger_block' /var/lib/gems/2.3.0/gems/rufus-scheduler-2.0.24/lib/rufus/sc/jobs.rb:204:inblock in trigger' /var/lib/gems/2.3.0/gems/rufus-scheduler-2.0.24/lib/rufus/sc/scheduler.rb:430:in `block in trigger_job'

Maybe refer to this one?

file_path = "#{Dir.pwd}/THIS_IS_THE_PATH_LOCATION_TO_THE_XLS_FILE/YOUR_FILENAME.xls"

[ghost]

yes exactly.

in what directory is your excel file? Wherever that is you need to replace what I have written in CAPITAL letters with it.

e.g. /home/howardsternisbatman/myfile.xls

[thefixxxer75]

my excel file is in /home/username/dashing/myfile.xls. Is it correct?

[ghost]

if you are asking me is that path as you have written it is correct for you, the answer is no. I'm sensing you are not familiar with Linux's file/folder structure. You need to read up on it since where you are stuck now has nothing to do with Smashing.

This might help http://www.howtogeek.com/117435/htg-explains-the-linux-directory-structure-explained/

You're close, solve the file path and you should be all good.

[thefixxxer75]

Thanks mate! I'm quite confident with folder structure. My only "concern" is that I've understand - reading a post - the the spreadsheet should be present in the dashboard/smashin "root" dir (so /home/xxxxx/smashing/). My fault :) I'll try and send u a feedback. Thanks so much

[thefixxxer75]

error persist. File moved as suggested.

undefined local variable or method path' for main:Object Did you mean? patch /home/xxxxx/xxxxxxxxx/jobs/sample.rb:7:inblock in <top (required)>'

[ghost]

I have my xls located in

/home/howardsternisbatman/mydashboard/xls/myfile.xls

As long as the file is somewhere within the dashboard file structure I don't see why this wouldn't work.