Use with excel data

[thedwm]

Hi,

Would it be posible to use a excel spreadsheet data for the dashboard data.

So the dashboard uses excel data and is updated each time the file is modified and saved.

Thanks.

Any idea would be appreciated.

[triangletodd]

This might help: http://roo.rubyforge.org/

[thedwm]

Hi,

Thanks will have a look and let you know what i come up with.

[pushmatrix]

Something like http://www.youtube.com/watch?v=cJMRKB3RU_s ? Hehe

So your first step is to figure out how to monitor the updates to the file:

---

Assuming you have a setup where the spreadsheet you are working on is on a different machine than the server Dashing is running on, you could use write a small ruby program that uses something like rb-kqueue to monitor when the file changes-- assuming you're on OS X. Whenever your file changes, you just use the roo gem that @toddedw mentioned, and then push the data to your dashboard using the Dashing api. Done.

---

Assuming you have a setup where the spreadsheet you are working on is on the same pc as the Dashing server, you could do something prettttty cool. Since the Thin webserver uses EventMachine, it has access to something called FileWatch. If you're on OS X, EM will use kqueue to monitor file changes. If you're on linux, it uses inotify. If you're on Windows...I have no idea. Anyway!

In your jobs directory, replace the sample.rb with:

sample.rb

require 'roo'

EM.kqueue = EM.kqueue?
file_path = "#{Dir.pwd}/spreadsheet.xls"

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

module Handler
  def file_modified
    fetch_spreadsheet_data(path)
  end
end

fetch_spreadsheet_data(file_path)

EM.next_tick do
  EM.watch_file(file_path, Handler)
end

For this to work, make sure that:

• You include roo in your Gemfile.
• You have a spreadsheet in the root directory called spreadsheet.xls. The data it is fetching is in cell (1, 2)
• You have a Number widget called 'valuation'. The Sample dashboard has it.

Let me know how that goes for you. Hope this helps!

As a side note, I guess you could do something fancier with ODBC straight to the excel file, but I'll leave that as an exercise to the reader :P

[pushmatrix]

And roo also supports Google spreadsheets, which is pretty awesome.

[thedwm]

Awesome stuff.
Thanks alot will give it go and let you know.

[ggusten]

This seems like a realy usefull thing, i tried to set it up but i am no geinius in linux and that is the platform i use for dashing.

I tried your sample.rb and i have two problems

1. kqueue is not supported on linux and you mention to use inotify, and i tried the logical and changed kqueue to inotify in the sample.rb sp i says
   EM.inotify = EM.inotify?
   insteed, but it dont work and is probebly all wrong.

it says

undefined method inotify? for EventMachine:Module

1. The other thing that i can not get to work is roo, its installing as it should and is on the GEM list, but it says
   cannot load such file -- roo

Thankful for any help you can provide

//Martin

[tschew72]

I follow the example to the dot but I realise that whenever I modify the value in the spreadsheet and save it, the watch_file detects it as "file_deleted" instead of "file_modified".

Can someone help me?

[michaelgra]

Could anyone help me with displaying a spreadsheet as text instead of pushing the value to a number?

[Painkillerlee]

@pushmatrix Is it possible to get a sample that works on linux? I have spent two days trying to reverse engineer the provided above and trying to find something similar with inotify and have not found anything that makes sense to me. I have never coded and trying to use your dashboard as a tool to learn and have something that I can use as a result. Thanks

[Painkillerlee]

I was able to get this to work in Linux/Ubuntu. See details here: http://stackoverflow.com/questions/29400811/use-with-excel-data-to-display-on-dashing-dashboard/29421179#29421179

[pushmatrix]

@rickleigh Good to hear you figured it out. I don't have a linux box handy, so couldn't test.

[JRibeiro13]

Hi, i am getting the following error, can you help me please?

scheduler caught exception:
Excel support has been extracted to roo-xls due to its dependency on the GPL'd spreadsheet gem. Install roo-xls to use Roo::Excel.
/var/lib/gems/1.9.1/gems/roo-2.0.0/lib/roo.rb:19:in const_missing' /home/pi/dashboard_project/jobs/sample.rb:9:infetch_spreadsheet_data'
/home/pi/dashboard_project/jobs/sample.rb:20:in block in <top (required)>' /var/lib/gems/1.9.1/gems/rufus-scheduler-2.0.24/lib/rufus/sc/jobs.rb:230:incall'
/var/lib/gems/1.9.1/gems/rufus-scheduler-2.0.24/lib/rufus/sc/jobs.rb:230:in trigger_block' /var/lib/gems/1.9.1/gems/rufus-scheduler-2.0.24/lib/rufus/sc/jobs.rb:204:inblock in trigger'
/var/lib/gems/1.9.1/gems/rufus-scheduler-2.0.24/lib/rufus/sc/scheduler.rb:430:in call' /var/lib/gems/1.9.1/gems/rufus-scheduler-2.0.24/lib/rufus/sc/scheduler.rb:430:inblock in trigger_job'

Thanks in Advance! :)

[Noble7ven]

Anyone have any luck with making this work on Windows? Ruby noob here

[terraboops]

Please direct support requests to Stack Overflow: https://stackoverflow.com/questions/tagged/dashing

[KonamiKode]

@JRibeiro13 I'm having the same problem as you! Any luck in finding a solution?