Facility Control

 View Only

  • 1.  How to link Excel to DashBoard?

    Posted 20 days ago

    Hi everyone,

    I am looking for a way to connect an Excel spreadsheet with Ross DashBoard, but I'm not sure how to go about it.

    My goal is simple : when I type a number or text into cell A1 in Excel, I want that specific value to be displayed inside my DashBoard panel in real-time (or near real-time).

    What is the best way to achieve this? Should I use ogScript to read a CSV/XML file, or is there a better workflow for Excel integration?

    Any advice, examples, or sample scripts would be greatly appreciated.

    Thank you!



    ------------------------------
    DK Han
    Employee
    Youngdo B&C
    [Seoul] [Republic of Korea]
    ------------------------------


  • 2.  RE: How to link Excel to DashBoard?

    Posted 19 days ago

    Polling a CSV is going to be your fastest path to success.  DashBoard does not have any native decoder for Excel's binary document types so reading the CSV text and using one of the existing examples (in this forum) will be the simplest mechanism to access the data from your sheet.



    ------------------------------
    James Peltzer
    Ross Video
    ------------------------------



  • 3.  RE: How to link Excel to DashBoard?

    Posted 14 days ago

    Hi James,

    I have confirmed that I need to save it as a CSV file and import it from DashBoard.

    It helped me a lot. Thank you so much!



    ------------------------------
    DK Han
    Employee
    Youngdo B&C
    [Seoul] [Republic of Korea]
    ------------------------------

    Original Message


  • 4.  RE: How to link Excel to DashBoard?

    Posted 19 days ago

    Hi,

    The simplest and most reliable approach is usually not to read the Excel workbook directly from DashBoard. Instead, have Excel save or export the value you need to a small CSV or JSON file, then let DashBoard poll that file every second or two with ogScript.

    For your example, if you only need the value from cell A1, a very straightforward workflow is:

    1. Excel writes the current value of A1 to a file such as value.csv

    2. A DashBoard panel uses a timer or poller in ogScript

    3. The panel checks whether the file changed

    4. If it changed, DashBoard reloads the file and updates the panel

    That gives you near real-time updates and is easy to understand and support.

    A few practical notes:

    • CSV is usually the best option if you only need one or a few values

    • XML or JSON can be better if you want named fields or more structured data

    • DashBoard does not generally treat a live .xlsx file as a real-time data source, so using an exported file is the cleaner workflow

    • If you want true live integration without manual saving, then Excel would need a macro, script, or external process to automatically write out the latest value whenever the sheet changes

    So in short: for a simple and customer-friendly solution, I would recommend Excel -> CSV file -> DashBoard ogScript poller.

    If helpful, here is a small DashBoard ogScript example that watches a CSV file and updates a panel automatically when the file is saved.

    <abs contexttype="opengear" gridsize="20" id="_top" keepalive="false">
   <meta>
      <params>
         <param access="1" maxlength="0" name="File Path" oid="file.path" type="STRING" value="avangers.csv" widget="file-picker"/>
         <param access="1" maxlength="0" name="Poll Status" oid="poll.status" type="STRING" value="CSV loaded successfully." widget="label"/>
         <param access="1" maxlength="0" name="Last Update" oid="last.update" type="STRING" value="Loaded avangers.csv at 09:30:07" widget="label"/>
         <param access="1" maxlength="0" name="Role" oid="label.1" type="STRING" value="Iron Man" widget="label"/>
         <param access="1" maxlength="0" name="First Name" oid="label.2" type="STRING" value="Tony" widget="label"/>
         <param access="1" maxlength="0" name="Last Name" oid="label.3" type="STRING" value="Stark" widget="label"/>
      </params>
   </meta>
   <meta>
      <color id="color.1" value="#000B58"/>
      <color id="color.2" value="#003161"/>
      <color id="color.3" value="#006A67"/>
      <color id="color.4" value="#FFF4B7"/>
      <style id="panel.outer" value="bg#color.1;bdr:round;bdr#color.1;"/>
      <style id="panel.inner" value="bg#color.2;bdr:round;bdr#color.2;"/>
      <style id="panel.card" value="bg#color.3;bdr:round;bdr#color.3;"/>
      <style id="text.value" value="bdr:round;bdr#color.3;fg#color.4;size:Big;"/>
      <style id="text.copy" value="fg#color.4;"/>
   </meta>
   <meta>
      <api>var POLL_TIMER_NAME = 'csvPollTimer';
var POLL_INTERVAL_MS = 2000;

function trimValue(value) {
   if (value == null) {
      return '';
   }
   return String(value).replace(/^\s+|\s+$/g, '');
}

function setStatus(message) {
   params.setValue('poll.status', 0, message);
}

function setLastUpdate(message) {
   params.setValue('last.update', 0, message);
}

function clearLabels() {
   params.setValue('label.1', 0, '');
   params.setValue('label.2', 0, '');
   params.setValue('label.3', 0, '');
}

function getTimeStamp() {
   var formatter = new Packages.java.text.SimpleDateFormat('HH:mm:ss');
   return formatter.format(new Packages.java.util.Date());
}

function splitCsvLine(line) {
   var rawColumns = String(line).split(',');
   var cleanColumns = [];
   var i;

   for (i = 0; i &lt; rawColumns.length; i++) {
      cleanColumns.push(trimValue(rawColumns[i]));
   }

   return cleanColumns;
}

function getFilePath() {
   return trimValue(params.getValue('file.path', 0));
}

function getFileChangeToken(filePath) {
   var file = ogscript.getFile(filePath);
   if (file == null || !file.exists() || !file.isFile()) {
      return null;
   }

   return String(file.lastModified()) + ':' + String(ogscript.getFileSize(filePath));
}

function updatePanelFromCsv(fileContent, filePath, changeReason) {
   var rows;
   var i;
   var dataRow = null;
   var columns;

   if (fileContent == null) {
      clearLabels();
      setStatus('Could not read the CSV file.');
      setLastUpdate('Read failed at ' + getTimeStamp());
      return;
   }

   rows = String(fileContent).replace(/\r/g, '').split('\n');

   for (i = 1; i &lt; rows.length; i++) {
      if (trimValue(rows[i]) != '') {
         dataRow = rows[i];
         break;
      }
   }

   if (dataRow == null) {
      clearLabels();
      setStatus('CSV loaded, but there are no data rows yet.');
      setLastUpdate('Checked ' + filePath + ' at ' + getTimeStamp());
      return;
   }

   columns = splitCsvLine(dataRow);

   params.setValue('label.1', 0, columns.length &gt; 0 ? columns[0] : '');
   params.setValue('label.2', 0, columns.length &gt; 1 ? columns[1] : '');
   params.setValue('label.3', 0, columns.length &gt; 2 ? columns[2] : '');

   if (changeReason == 'changed') {
      setStatus('Change detected. Panel refreshed automatically.');
   } else {
      setStatus('CSV loaded successfully.');
   }

   setLastUpdate('Loaded ' + filePath + ' at ' + getTimeStamp());
}

function loadSelectedCsv(changeReason) {
   var filePath = getFilePath();

   if (filePath == '') {
      clearLabels();
      setStatus('Choose a CSV file first.');
      setLastUpdate('Waiting for CSV...');
      return;
   }

   ogscript.asyncPost(filePath, null, function(fileContent) {
      updatePanelFromCsv(fileContent, filePath, changeReason);

      if (fileContent != null) {
         ogscript.putString('csv.poll.token', getFileChangeToken(filePath));
      }
   });
}

function pollCsvFile() {
   var filePath = getFilePath();
   var currentToken;
   var previousToken;

   if (filePath == '') {
      clearLabels();
      setStatus('Choose a CSV file to start polling.');
      setLastUpdate('Waiting for CSV...');
      return;
   }

   currentToken = getFileChangeToken(filePath);
   if (currentToken == null) {
      clearLabels();
      setStatus('CSV file not found. Check the selected path.');
      setLastUpdate('Waiting for a valid file...');
      ogscript.putString('csv.poll.token', '');
      return;
   }

   previousToken = ogscript.getString('csv.poll.token');
   if (previousToken == null || previousToken == '') {
      loadSelectedCsv('initial');
      return;
   }

   if (previousToken != currentToken) {
      loadSelectedCsv('changed');
      return;
   }

   setStatus('Polling every 2 seconds. No file change detected.');
}

function stopPoller() {
   try {
      ogscript.cancelTimer(POLL_TIMER_NAME);
   } catch (error) {
   }

   setStatus('Poller stopped. Press Start Poller to resume.');
}

function startPoller() {
   stopPoller();
   ogscript.installTimer(POLL_TIMER_NAME, true, POLL_INTERVAL_MS, POLL_INTERVAL_MS, pollCsvFile);
   setStatus('Polling every 2 seconds. Waiting for file changes.');
}

function restartPoller() {
   ogscript.putString('csv.poll.token', '');
   startPoller();
   pollCsvFile();
}

function useSampleFile(fileName) {
   params.setValue('file.path', 0, fileName);
   restartPoller();
}

function initialisePoller() {
   clearLabels();
   setLastUpdate('Waiting for CSV...');
   restartPoller();
}</api>
      <ogscript handles="onload">initialisePoller();</ogscript>
   </meta>
   <abs height="560" left="20" style="style:panel.outer;" top="20" width="780">
      <abs height="520" left="20" style="style:panel.inner;" top="20" width="740">
         <label height="34" left="20" name="CSV Polling Demo" style="style:text.copy;size:Bigger;font:bold;" top="20" width="260"/>
         <label height="20" left="20" name="1. Pick a CSV file. 2. Edit the file and save it. 3. The panel reloads the first data row automatically." style="style:text.copy;" top="58" width="680"/>
         <label height="20" left="20" name="This example uses ogscript.getFile(), lastModified(), getFileSize(), and asyncPost() for a simple file poller." style="style:text.copy;" top="82" width="690"/>
         <param expand="true" height="56" left="20" oid="file.path" showlabel="false" style="style:text.value;" top="114" width="700">
            <config key="w.remove">false</config>
            <config key="w.save">false</config>
            <config key="w.filetypes">csv,.csv</config>
            <config key="w.absolute">false</config>
            <task tasktype="ogscript">restartPoller();</task>
         </param>
         <button buttontype="push" height="42" left="20" name="Load Now" style="bg#color.3;bdr:round;size:Big;font:bold;fg#color.4;" top="184" width="160">
            <task tasktype="ogscript">loadSelectedCsv('manual');</task>
         </button>
         <button buttontype="push" height="42" left="194" name="Start Poller" style="bg#color.3;bdr:round;size:Big;font:bold;fg#color.4;" top="184" width="160">
            <task tasktype="ogscript">startPoller();</task>
         </button>
         <button buttontype="push" height="42" left="368" name="Stop Poller" style="bg#color.3;bdr:round;size:Big;font:bold;fg#color.4;" top="184" width="160">
            <task tasktype="ogscript">stopPoller();</task>
         </button>
         <button buttontype="push" height="42" left="542" name="Use avangers.csv" style="bg#color.3;bdr:round;size:Big;font:bold;fg#color.4;" top="184" width="178">
            <task tasktype="ogscript">useSampleFile('avangers.csv');</task>
         </button>
         <button buttontype="push" height="42" left="542" name="Use starwars.csv" style="bg#color.3;bdr:round;size:Big;font:bold;fg#color.4;" top="234" width="178">
            <task tasktype="ogscript">useSampleFile('starwars.csv');</task>
         </button>
         <param expand="true" height="42" left="20" oid="poll.status" showlabel="false" style="style:text.value;size:Small;" top="234" width="508"/>
         <param expand="true" height="42" left="20" oid="last.update" showlabel="false" style="style:text.value;size:Small;" top="284" width="700"/>
         <abs height="170" left="20" style="style:panel.card;" top="340" width="700">
            <simplegrid cols="2" height="130" hspace="8" left="16" rows="3" top="18" vspace="8" width="668">
               <label name="Role" style="style:text.value;txt-align:center;"/>
               <param expand="true" oid="label.1" showlabel="false" style="style:text.value;"/>
               <label name="First Name" style="style:text.value;txt-align:center;"/>
               <param expand="true" oid="label.2" showlabel="false" style="style:text.value;"/>
               <label name="Last Name" style="style:text.value;txt-align:center;"/>
               <param expand="true" oid="label.3" showlabel="false" style="style:text.value;"/>
            </simplegrid>
         </abs>
      </abs>
   </abs>
</abs>

    CSV Files:

    Function,Firstname,Lastname
Iron Man,Tony,Stark
Captain America,Steve,Rogers
God of Thunder,Thor,(Son of Odin)
Spider-Man,Peter,Parker
Hulk,Dr Bruce,Banner


    Function,Firstname,Lastname,
Jedi,Obi-Wan ,Kenobi,
Sith Lord,Darth ,Vader,
Republic Politician,Leia ,Organa,
Aviator/Smuggler,Han ,Solo,
Tatooine Farmhand,Luke  ,Skywalker



    ------------------------------
    Richard Crutwell
    Ross Video UK
    ------------------------------



  • 5.  RE: How to link Excel to DashBoard?

    Posted 14 days ago

    Hi Richard,

    Thank you so much for sharing the script.

    Thanks to your help, I now see the possibility of importing a CSV file and viewing the cell text directly on the dashboard.

    If you don't mind, I would like to ask a few follow-up

    Questions a few things :

    1. Is it only possible to view the data by explicitly importing the CSV file from the dashboard every time?I would like to know if there is a way to automate this or if manual importing is the only method.

    2. Could you please check the attached image?As shown in the image, my goal is to make the text from the Excel/CSV file automatically appear in the dashboard's Text input field.

    image
    I reviewed the script you sent entirely, but since I am not from a coding background, I am having trouble identifying the specific part of the code that pulls the Excel data into the dashboard.
    3. If it is not too much trouble, could you please share a simple script that extracts data from a CSV file and displays it on the dashboard?Any simplified example or guidance on where to inject the data values would be greatly appreciated.
    Thank you so much for your time and support!



    ------------------------------
    DK Han
    Employee
    Youngdo B&C
    [Seoul] [Republic of Korea]
    ------------------------------

    Original Message


  • 6.  RE: How to link Excel to DashBoard?

    Posted 2 days ago

    Yes, the file loading can be fully automated - the dashboard can simply load a predefined file on startup. Based on Richard's great example, I put together a more compact version that always reads the contents of a file named data.csv and displays the fields shown in the screenshot.

    To keep the example as clear as possible, I stripped out several sections from the original version. In this simplified demo the three fields are just named A, B, and C in the code.

    <?xml version="1.0" encoding="UTF-8"?><abs contexttype="opengear" gridsize="20" id="_top" keepalive="false">
   <meta>
      <params>
         <param access="1" maxlength="0" name="File Path" oid="file.path" type="STRING" value="data.csv" widget="label"/>
         <param access="1" maxlength="0" name="A" oid="A" type="STRING" value="TEST1" widget="label"/>
         <param access="1" maxlength="0" name="B" oid="B" type="STRING" value="TEST2" widget="label"/>
         <param access="1" maxlength="0" name="C" oid="C" type="STRING" value="TEST3" widget="label"/>
      </params>
      <api>var POLL_TIMER_NAME = 'csvPollTimer';
var POLL_INTERVAL_MS = 2000;

function trimValue(value) {
   if (value == null) {
      return '';
   }
   return String(value).replace(/^\s+|\s+$/g, '');
}

function clearLabels() {
   params.setValue('A', 0, '');
   params.setValue('B', 0, '');
   params.setValue('C', 0, '');
}

function splitCsvLine(line) {
   var rawColumns = String(line).split(',');
   var cleanColumns = [];
   var i;
   for (i = 0; i &lt; rawColumns.length; i++) {
      cleanColumns.push(trimValue(rawColumns[i]));
   }
   return cleanColumns;
}

function getFileChangeToken(filePath) {
   var file = ogscript.getFile(filePath);
   if (file == null || !file.exists() || !file.isFile()) {
      return null;
   }
   return String(file.lastModified()) + ':' + String(ogscript.getFileSize(filePath));
}

function updatePanelFromCsv(fileContent, filePath) {
   var rows;
   var i;
   var dataRow = null;
   var columns;
   if (fileContent == null) {
      clearLabels();
      ogscript.debug("Could not read the CSV file.");
      return;
   }
   rows = String(fileContent).replace(/\r/g, '').split('\n');
   for (i = 1; i &lt; rows.length; i++) {
      if (trimValue(rows[i]) != '') {
         dataRow = rows[i];
         break;
      }
   }
   if (dataRow == null) {
      clearLabels();
      ogscript.debug('CSV loaded, but there are no data rows yet.');
      return;
   }
   columns = splitCsvLine(dataRow);
   params.setValue('A', 0, columns.length &gt; 0 ? columns[0] : '');
   params.setValue('B', 0, columns.length &gt; 1 ? columns[1] : '');
   params.setValue('C', 0, columns.length &gt; 2 ? columns[2] : '');
   // ogscript.debug('CSV loaded successfully.');
}

function loadSelectedCsv() {
   var filePath = params.getValue('file.path', 0);
   if (filePath == '') {
      clearLabels();
      ogscript.debug('File name is missing...');
      return;
   }
   ogscript.asyncPost(filePath, null, function(fileContent) {
      updatePanelFromCsv(fileContent, filePath);
      if (fileContent != null) {
         ogscript.putString('csv.poll.token', getFileChangeToken(filePath));
      }
   });
}

function pollCsvFile() {
   var filePath = params.getValue('file.path', 0);
   var currentToken;
   var previousToken;
   if (filePath == '') {
      clearLabels();
      ogscript.debug('CSV filepath is missing...');
      return;
   }
   currentToken = getFileChangeToken(filePath);
   if (currentToken == null) {
      clearLabels();
      ogscript.debug('CSV file not found.');
      ogscript.putString('csv.poll.token', '');
      return;
   }
   previousToken = ogscript.getString('csv.poll.token');
   if (previousToken != currentToken || previousToken == null || previousToken == '') {
      loadSelectedCsv();
      return;
   }
}

function initialisePoller() {
   clearLabels();
   ogscript.putString('csv.poll.token', '');
   try {
      ogscript.cancelTimer(POLL_TIMER_NAME);
   } catch (error) {
   }
   ogscript.installTimer(POLL_TIMER_NAME, true, POLL_INTERVAL_MS, POLL_INTERVAL_MS, pollCsvFile);
   ogscript.debug('Starting poller. Waiting for file changes.');
   pollCsvFile();
}</api>
      <ogscript handles="onload">initialisePoller();</ogscript>
   </meta>
   <abs height="500" left="20" top="20" width="600">
      <label height="34" left="20" name="CSV Polling" style="size:Bigger;font:bold;" top="20" width="200"/>
      <abs height="200" left="20" top="100" width="500">
         <simplegrid cols="3" height="100" hspace="8" left="20" rows="1" top="18" vspace="8" width="400">
            <param expand="true" oid="A" showlabel="false" style="style:text.value;"/>
            <param expand="true" oid="B" showlabel="false" style="style:text.value;"/>
            <param expand="true" oid="C" showlabel="false" style="style:text.value;"/>
         </simplegrid>
      </abs>
   </abs>
</abs>


    ------------------------------
    Juha Koivisto
    Tampere
    Finland
    ------------------------------

    Original Message