Displaying Metrics from an Excel Sheet

[bminer1]

Hello,

My father in law was impressed with my magic mirror and asked me the below:
“Can someone use the concept to report metrics in an operation? Are you limited as to where you can pull information from? For example, can we pull information from an Excel spreadsheet?”

Could there be an existing module that could reference cells or a group of cells information and display that information on the screen?

[rummyr]

I guess the bottom line is the question “can nodejs access the data?”

A quick search turned up more than one npm module for reading xls files.

Not sure pulling from an Excel sheet is particularly sensible, probably more sensible to push from Excel on change to a more accessible source.

[robiv8]

@rummyr said in Displaying Metrics from an Excel Sheet:

I guess the bottom line is the question “can nodejs access the data?”

A quick search turned up more than one npm module for reading xls files.

Not sure pulling from an Excel sheet is particularly sensible, probably more sensible to push from Excel on change to a more accessible source.

My English is not very good!
Can you explain that further? That would interest me too.

[shbatm]

You can use a Node.js module like this one: https://www.npmjs.com/package/excel-as-json to save the data in a convenient format (JSON) and then use a MM module like https://github.com/timdows/MMM-JsonTable to display it.

[bhepler]

@robiv8 - Data in an excel spreadsheet is useful for calculating values and displaying them. The difficulty in using a Magic Mirror to display such values is that you now have to transfer the excel file to the Magic Mirror so it can be read by the module. (You can get around this by making the file available over the network and telling the mirror to look at a specific network path).

You also have to write some sort of interpretation to pull the data out of Excel and display it on the screen. If you just wish to display a spreadsheet, this isn’t too difficult. Things get complicated when you want to extract only certain parts of that spreadsheet (such as computed totals). Excel doesn’t store total values in the cells, it stores formulas. Just displaying the contents on the mirror results in values that look like =$AE+SUM(B3..B14)

The preferred way of doing these things is to make the computed data available via a web API and then use the Magic Mirror to query the API and display the values. That module has already been written.

[bhepler]

@bminer1 - I’m glad your FiL likes your mirror! To answer his question, the answer is generally “Any location with a web API is easy. Most other data sources are possible *.”

I’ve been developing systems for more years than I care to admit. I would guess that he has an employee/coworker who maintains a spreadsheet with metrics for his various processes. Spreadsheets are simple and easy to use, so they end up doing a lot of lifting in any business. The spreadsheet probably performs both data storage and calculation roles and they’ve probably been using it for years.

I wonder… there’s got to be a simple tool out there that will use a spreadsheet as a data source and expose an API to query the spreadsheet. You could then use the existing modules (MMM-Rest, for example) to query that API.

*Sometimes I am too much of a literalist. There are certain sources of data that are too large for the Pi to handle with its limited memory and processor. You will not be able to display a ray tracing rendering process, for example, as there is too much data for the Pi to keep up.