asked on

Importing data from Excel into HTML table for meeting room display

Hello,

We're trying to come up with an HTML page that will be displayed outside of several conference rooms. Currently, we have the meeting name, start, and end times exported to an Excel spreasheet that we use to manually display the meetings in each room (and the file is updated each morning) on a monitor attached to a thin client. I'm thinking the best option would be to create an HTML page that contains a table that pulls it's contents from the existing Excel file that we currently use (which gets updated by an existing script each morning).

In addition to the table displaying the meeting name, start, and end times, there are a few other elements that we'd like to include in the HTML page (from top to bottom):

- Current date and time
- Two lines of static text (location and room name)
- Space for one JPEG photo (one photo would be displayed for about 5 seconds, then scroll to the next in a folder of photos)
- A table, consisting of 3 columns - Meeting Name, Start, End times (table data pulled from Excel file)
- If there are more than 10 meetings, the first 10 entries would be displayed for 10 seconds, then the next 10 meeting entries would be displayed for 10 seconds (and so on; then start back with the first 10 meeting entries, continuing in a loop)

Thank you for any assistance.

Chris

Scott Fell

If you don't have to do the animation, just create what you want visually in excel and save as html.

Otherwise, your choices are to connect to excel as you would a database, http://php.net/manual/en/function.odbc-connect.php or keep the excel data in nice neat columns and save as a csv http://php.net/manual/en/function.fgetcsv.php or just do it all client side in js http://papaparse.com/.

Are you able to start this on your own? Would it be easier to just use digital sign software? http://www.digitalsignage.com/

If you go the code your own route, I would start with one of the bootstrap examples and modify to your needs http://jsbin.com/kejeledovu/1/edit

<!DOCTYPE html>
<html>
<head>
  <link href="http://getbootstrap.com/examples/carousel/carousel.css" rel="stylesheet">
<script src="//code.jquery.com/jquery.min.js"></script>
<link href="//maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css" rel="stylesheet" type="text/css" />
<script src="//maxcdn.bootstrapcdn.com/bootstrap/3.3.2/js/bootstrap.min.js"></script>
  <meta charset="utf-8">
  <title>JS Bin</title>
</head>
<body>



    <!-- Carousel
    ================================================== -->
    <div id="myCarousel" class="carousel slide" data-ride="carousel">
      <!-- Indicators -->
      <ol class="carousel-indicators">
        <li data-target="#myCarousel" data-slide-to="0" class="active"></li>
        <li data-target="#myCarousel" data-slide-to="1"></li>
        <li data-target="#myCarousel" data-slide-to="2"></li>
      </ol>
      <div class="carousel-inner" role="listbox">
        <div class="item active">
          <img class="first-slide" src="data:image/gif;base64,R0lGODlhAQABAIAAAHd3dwAAACH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" alt="First slide">
          <div class="container">
            <div class="carousel-caption">
              <h1>Example headline.</h1>
              <p>Note: If you're viewing this page via a <code>file://</code> URL, the "next" and "previous" Glyphicon buttons on the left and right might not load/display properly due to web browser security rules.</p>
              <p><a class="btn btn-lg btn-primary" href="#" role="button">Sign up today</a></p>
            </div>
          </div>
        </div>
        <div class="item">
          <img class="second-slide" src="data:image/gif;base64,R0lGODlhAQABAIAAAHd3dwAAACH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" alt="Second slide">
          <div class="container">
            <div class="carousel-caption">
              <h1>Another example headline.</h1>
              <p>Cras justo odio, dapibus ac facilisis in, egestas eget quam. Donec id elit non mi porta gravida at eget metus. Nullam id dolor id nibh ultricies vehicula ut id elit.</p>
              <p><a class="btn btn-lg btn-primary" href="#" role="button">Learn more</a></p>
            </div>
          </div>
        </div>
        <div class="item">
          <img class="third-slide" src="data:image/gif;base64,R0lGODlhAQABAIAAAHd3dwAAACH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" alt="Third slide">
          <div class="container">
            <div class="carousel-caption">
              <h1>One more for good measure.</h1>
              <p>Cras justo odio, dapibus ac facilisis in, egestas eget quam. Donec id elit non mi porta gravida at eget metus. Nullam id dolor id nibh ultricies vehicula ut id elit.</p>
              <p><a class="btn btn-lg btn-primary" href="#" role="button">Browse gallery</a></p>
            </div>
          </div>
        </div>
      </div>
      <a class="left carousel-control" href="#myCarousel" role="button" data-slide="prev">
        <span class="glyphicon glyphicon-chevron-left" aria-hidden="true"></span>
        <span class="sr-only">Previous</span>
      </a>
      <a class="right carousel-control" href="#myCarousel" role="button" data-slide="next">
        <span class="glyphicon glyphicon-chevron-right" aria-hidden="true"></span>
        <span class="sr-only">Next</span>
      </a>
    </div><!-- /.carousel -->


    <!-- Marketing messaging and featurettes
    ================================================== -->
    <!-- Wrap the rest of the page in another container to center all the content. -->

    <div class="container marketing">

      <!-- Three columns of text below the carousel -->
      <div class="row">
        <div class="col-lg-4">
          <img class="img-circle" src="data:image/gif;base64,R0lGODlhAQABAIAAAHd3dwAAACH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" alt="Generic placeholder image" width="140" height="140">
          <h2>Heading</h2>
          <p>Donec sed odio dui. Etiam porta sem malesuada magna mollis euismod. Nullam id dolor id nibh ultricies vehicula ut id elit. Morbi leo risus, porta ac consectetur ac, vestibulum at eros. Praesent commodo cursus magna.</p>
          <p><a class="btn btn-default" href="#" role="button">View details &raquo;</a></p>
        </div><!-- /.col-lg-4 -->
        <div class="col-lg-4">
          <img class="img-circle" src="data:image/gif;base64,R0lGODlhAQABAIAAAHd3dwAAACH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" alt="Generic placeholder image" width="140" height="140">
          <h2>Heading</h2>
          <p>Duis mollis, est non commodo luctus, nisi erat porttitor ligula, eget lacinia odio sem nec elit. Cras mattis consectetur purus sit amet fermentum. Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh.</p>
          <p><a class="btn btn-default" href="#" role="button">View details &raquo;</a></p>
        </div><!-- /.col-lg-4 -->
        <div class="col-lg-4">
          <img class="img-circle" src="data:image/gif;base64,R0lGODlhAQABAIAAAHd3dwAAACH5BAAAAAAALAAAAAABAAEAAAICRAEAOw==" alt="Generic placeholder image" width="140" height="140">
          <h2>Heading</h2>
          <p>Donec sed odio dui. Cras justo odio, dapibus ac facilisis in, egestas eget quam. Vestibulum id ligula porta felis euismod semper. Fusce dapibus, tellus ac cursus commodo, tortor mauris condimentum nibh, ut fermentum massa justo sit amet risus.</p>
          <p><a class="btn btn-default" href="#" role="button">View details &raquo;</a></p>
        </div><!-- /.col-lg-4 -->
      </div><!-- /.row -->


      <!-- START THE FEATURETTES -->

      <hr class="featurette-divider">

      <div class="row featurette">
        <div class="col-md-7">
          <h2 class="featurette-heading">First featurette heading. <span class="text-muted">It'll blow your mind.</span></h2>
          <p class="lead">Donec ullamcorper nulla non metus auctor fringilla. Vestibulum id ligula porta felis euismod semper. Praesent commodo cursus magna, vel scelerisque nisl consectetur. Fusce dapibus, tellus ac cursus commodo.</p>
        </div>
        <div class="col-md-5">
          <img class="featurette-image img-responsive center-block" data-src="holder.js/500x500/auto" alt="Generic placeholder image">
        </div>
      </div>

      <hr class="featurette-divider">

      <div class="row featurette">
        <div class="col-md-7 col-md-push-5">
          <h2 class="featurette-heading">Oh yeah, it's that good. <span class="text-muted">See for yourself.</span></h2>
          <p class="lead">Donec ullamcorper nulla non metus auctor fringilla. Vestibulum id ligula porta felis euismod semper. Praesent commodo cursus magna, vel scelerisque nisl consectetur. Fusce dapibus, tellus ac cursus commodo.</p>
        </div>
        <div class="col-md-5 col-md-pull-7">
          <img class="featurette-image img-responsive center-block" data-src="holder.js/500x500/auto" alt="Generic placeholder image">
        </div>
      </div>

      <hr class="featurette-divider">

      <div class="row featurette">
        <div class="col-md-7">
          <h2 class="featurette-heading">And lastly, this one. <span class="text-muted">Checkmate.</span></h2>
          <p class="lead">Donec ullamcorper nulla non metus auctor fringilla. Vestibulum id ligula porta felis euismod semper. Praesent commodo cursus magna, vel scelerisque nisl consectetur. Fusce dapibus, tellus ac cursus commodo.</p>
        </div>
        <div class="col-md-5">
          <img class="featurette-image img-responsive center-block" data-src="holder.js/500x500/auto" alt="Generic placeholder image">
        </div>
      </div>

      <hr class="featurette-divider">

      <!-- /END THE FEATURETTES -->


      <!-- FOOTER -->
      <footer>
        <p class="pull-right"><a href="#">Back to top</a></p>
        <p>&copy; 2014 Company, Inc. &middot; <a href="#">Privacy</a> &middot; <a href="#">Terms</a></p>
      </footer>

    </div><!-- /.container -->
</body>
</html>

Open in new window

damoncf1234

ASKER

Scott,
Hello - thank you for the response. I forgot to mention, these boards will be running on a network that does not have internet access. We can't use commercial software. So I'm looking down the 'code your own' route as you suggested. Not really able to start this on my own - any further assistance would be appreciated. Thank you.

The room display would be 'just the monitor' - non-touchscreen - so if there's more than say 10 meeting entries, we'd need the table to automatically go to the next 10 entries, pause for about 10 seconds, then go back to the first 10 entries.

Thank you again.
-Chris

Scott Fell

>does not have internet access
My answer does not need internet access. Though you would need to run php probably via WAMP/MAMP to do something serverside.

It would probably be easier to just use javascript/jquery though.

> we'd need the table to automatically go to the next 10 entries,

Just use javascript/jquery to cycle through. There are a ton of jquery slide show thingys http://wowslider.com/rq/jquery-slideshow/

damoncf1234

ASKER

Scott,

Thanks for the additional response. Do you have an 'example' that could get me started (a basic HTML page that includes the scrolling picture, and the text pulled from the CSV file?) I haven't done anything with HTML since ~1995...

I just looked at the jquery slideshow, and it looks like a stand-alone application (vs a script). (We can't use 3rd-party applications on this specific network.)

Thank you again.

Chris

damoncf1234

ASKER

Scott,

Hi again. I don't want to seem too helpless -- I took your advice and looked for java-based items... I found a java-based realtime clock:

http://www.webestools.com/scripts_tutorials-code-source-7-display-date-and-time-in-javascript-real-time-clock-javascript-date-time.html

and java-based picture slideshow:

http://www.javascriptkit.com/script/script2/jsslide.shtml

So now I'm just looking for help with displaying the CSV spreadsheet data as a table on the HTML page...
(With the ability to automatically 'scroll through two or three 'pages' if there are more than, say 10 meetings/entries in the table, then scroll back to page 1 in a loop.)

Thank you again,
Chris

Scott Fell

For the clock http://flipclockjs.com/ http://hilios.github.io/jQuery.countdown/examples.html

For the csv file, you can't easily directly pull it from the local computer automatically. You can do manual updates and this is a good start http://papaparse.com/#remote-files If you look at that example, they are using a remote address. What you could do is place the csv in localhost and end up with a url like http://localhost/mycsv.csv

You have php in your question tag, but the only way to use php like this would be to either get iis running with php installed on your windows computer or start apache in mac. If you can install MAMP it makes it easier https://www.mamp.info/en/ it it is not your computer than chances are none of these choices will be good and you will have to use javascript.

damoncf1234

ASKER

Scott,

Thanks for the additional suggestions. I think we're good with the real time date/clock, and the rotating photo slideshow.

So that leaves the csv to HTML table. In one of your earlier responses, you mention query and JavaScript for the table/auto-rotating between pages if the table contains more than 10 rows. Can you provide an example of how this would be done?

I found these two items - would either of these work?

http://code.google.com/p/jquerycsvtotable/

stackoverflow.com/questions/14167677/how-to-format-csv-to-html-table-rows-and-columns-using-javascript

Thank you again for your help.

Chris

Scott Fell

What is wrong with the PapaParse project I pointed you to?

The trick is being able to update your data. With the remote sample, you just need to save your file in the folder where localhost is. The point your remote url to localhost/yourifle.csv https://support.microsoft.com/en-us/kb/972034

What javascript will not be able to do is use c:\somefolder\yourfile.csv as that would be a security risk.

damoncf1234

ASKER

Scott,

Hello. Nothing's wrong with it - I'm just trying to figure out 'how' to configure/use it.

Other examples I've found include examples -- "copy this part into the header, and this part into the body where you want something to appear." Is it possible to provide a basic example? There's also the 'page two' part -- if there are more than 10 rows, pause 10 seconds, then automatically scroll to the 2nd page, pause for 10 seconds, then scroll back to the 1st page (back and forth)...

Thank you,
Chris

SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

damoncf1234

ASKER

Scott,

Sounds good - I'll work on this today. I forgot one other element - roughly every hour, our current solution 'removes' meetings that have already happened (it's 11am; anything ending before 11am is 'dropped' off the display).

Thank you,
Chris

damoncf1234

ASKER

Scott,
Hello again - I've been working on this for most of the day -- for some reason, part one of your solution doesn't work. If I go to the JSBin.com link you provided, it works fine in there. But if I copy and save the code into notepad, save as xxx.html, it only displays 'col1, col2, col3' -- with a name of Scott.
It seems to take a while to load, then displays the column headers - col1, col2, col3 -- I've left the window open for several hours, but none of the fields/data appear. This is without changing anything/leaving everything the way it appears/works in JSBin.com - which makes no sense... :)

I've scoured around the internet, looking for a 'start to finish' example of using PapaParse -- what should be in the header, what should be in the body where you want it to display a table, what should be saved separately as a .js file, etc. There are a billion snippets of code describing PapaParse - but none are 'complete.'

One helpful site that has 'the full examples' I like is javascriptkit.com -- it shows exactly what to put into the header, what to put into the body, and what to save as a .js file. The last time I've done any type of HTML/web stuff is probably back in 1995. I'm working on this current project because my work needs a replacement for an existing system that is not supported in Win7 or 10...

Anyway, the rest of the stand-alone HTML page is functioning well - real-time clock, scrolling photos, etc. The last thing we need to get working is this CSV to HTML table (w/ 10-second page 'scroll', update).

I've tried running the code you pasted above, and on JSBin.com -- on Firefox and Chrome -- with the same results - the header (col1, col2, col3) appear, but none of the test csv text. I'll open just the CSV to HTML table issue in a new question if you'd like.

Thank you again for your help.

Chris

Scott Fell

If you copied exactly it will not work. Copy the cover file to the same folder as the home file. Make sure to adjust the Ajax Urlacher to just the file name.

Scott Fell

Url not urlacher. .. auto correct

ASKER CERTIFIED SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

damoncf1234

ASKER

Scott,

Excellent, thank you for the update. I followed your instructions and changed line 9 to "schedule.csv"

It still didn't show the data from schedule.csv (just the headers) -- so I went into "developer tools" in Chrome, and found "failed to load resource:net::ERR_FILE_NOT_FOUND file://code.jquery.com/jquery-1.11.1.min.js" -- after looking at that for a minute, I saved the jquery-1.11.1.min.js file from http://code.jquery.com to the same folder on my local machine, then changed line 4 to <script src="file://g:/test/IDS/jquery-1.11.1.min.js"></script>

Now, it works in IE -- shows the headers, plus the data in schedule.csv.
Doesn't work in Chrome (got some Cross Origin Requests only support http......" message in the developer tool window. But if it works in IE, that should be good. I found this https://code.google.com/p/locallinks/ that might fix that issue in Chrome, but haven't tried it yet. Just glad it's working in IE.

I'm working on the 'look' of the table now, so it matches our current setup...

Also looking at the "10 rows at a time/scroll" issue... How would I add the same numbered class to the first 10 rows, then increase the class by 1, and continue numbering the rows? I found something that increments by 1 until 6, then restarts at 1 -- but I need to mark the first 10 items as numbered class 1, then start over at 2, for the next 10 items.
https://css-tricks.com/forums/topic/addclass-1-to-6-then-repeat-after-six-list-items-jquery/

http://jsfiddle.net/Mottie/Y65hS/

Thank you for your help. I can ask this (item #4) in a new question if you'd prefer.

-Chris

Scott Fell

>,then changed line 4 to <script src="file://g:/test/IDS/jquery-1.11.1.min.js"></script>

You didn't follow my instructions. Please read again.

<script src="myfile.js"></scipt> where myfile.js is in the same folder as the rest of the files.

Any other errors you have, post that to a new question after you close this out. Get the basic page working first.

damoncf1234

ASKER

Scott,

Ok, thank you for your help. The page is functioning in IE, and the CSV file is appearing as an HTML table. I'm trying to apply a style to the entire table using STYLE-TYPE in the head then TABLE CLASS in the body, which isn't working yet... I'll post another question for styling the table if I can't figure it out, along with another question for adding the first 10 rows to the same numbered class as you suggested.

Thanks again.
Chris

damoncf1234

ASKER

Scott was very responsive. Thank you.