• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 727
  • Last Modified:

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
0
damoncf1234
Asked:
damoncf1234
  • 10
  • 9
2 Solutions
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
 
damoncf1234Author Commented:
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
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
>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/
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
damoncf1234Author Commented:
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
0
 
damoncf1234Author Commented:
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
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
 
damoncf1234Author Commented:
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
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
 
damoncf1234Author Commented:
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
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
This is a lot more complex than it appears.  We are supposed to be helping you with your own coding errors or leading you in the right direction.  With that, I am going do a little more than lead you but I think you should take this in small steps and start a new question for each step.

I have simplified a bit by not using a 3rd party plug in.  You are going to have to QC the data and make sure it is good as there is no error checking of your data built in.

The first step is to create your csv file either by hand typing or in excel and save as csv.  When you do, make sure you save as windows csv if you are on a mac or there may be some issues.  Save the csv file in the same folder or one folder below the rest of your project.  You can name it whatever you want and can even change the extension to something else like txt or js as long as the computer/server will recognize it.

My example is using a csv file http://jsbin.com/guvudaqogo/1.js

http://jsbin.com/jepunazove/1
<!DOCTYPE html>
<html>
<head>
<script src="//code.jquery.com/jquery-1.11.1.min.js"></script>
  <script>
  $(function(){ // Call when html is done loading
$.ajax({ //Your url will be filename.csv or folder/filename.csv
  method: "get",
  url: "http://jsbin.com/guvudaqogo/1.js"

})
  .done(function( data ) {
    var lines = data.split("\n"); // split on line break
    $.each(lines,function(i){     // loop through each line
       var rowdata = lines[i];    // assign the row a variable
                                 // data looks like rowdata = "1,2,3"
       $("table#schedule tbody").append("<tr><td>"+rowdata.replace(/,/g,"</td><td>")+"</td></tr>"); 
      // replace all commas with ending and starting td tags then append row to table
    });
  });
  
});  
  </script>
  <meta charset="utf-8">
  <title>Scott Fell</title>
</head>
<body>
  <table id="schedule">
    <thead><tr><th>Col1</th><th>Col2</th><th>Col3</th></tr></thead>
  <tbody></tbody>
  </table>
  
</body>
</html>

Open in new window


Your next steps
1) Simply make this work on your own as is.  

2) Play with saving your excel file as a csv and pasting that to the data file.

3) Style the table with css

4) Add more jquery/js to show 10 rows at a time and auto scroll or show the next 10.

Work each of these steps individually and as you have issues with each step, post a new question.  For step 4, I would probably add some logic that adds a numbered class to each row. As example
<tr class="1"><td></td></tr>
<tr class="1"><td></td></tr>
<tr class="2"><td></td></tr>
<tr class="2"><td></td></tr>
<tr class="3"><td></td></tr>
<tr class="3"><td></td></tr>

Open in new window

On page load, hide all the rows and show only tr.1.  Wait x seconds, then hide tr.1 and show tr.2
Some hints
<style>
table#schedule tbody tr{display:none}
</style>
http://api.jquery.com/hide/
http://api.jquery.com/show/
http://www.tizag.com/javascriptT/javascriptfor.php
http://www.codecademy.com/courses/a-simple-counter/0/1
0
 
damoncf1234Author Commented:
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
0
 
damoncf1234Author Commented:
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
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Url not urlacher. .. auto correct
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I had to get off my phone to look at this.    Line 9 in my example
  url: "http://jsbin.com/guvudaqogo/1.js"

Open in new window


That needs to be a file on your local folder and should be in the same folder as the html file.  If you name the file schedule.csv then line 9 is going to be
  url: "schedule.csv"

Open in new window

Make sure you copy the current file over at http://jsbin.com/guvudaqogo/1.js by A) create a new file with note pad (not wordpad) and save it to where the html file is and name it schedule.csv.  Then copy the data from the jsbin file and past to your note pad then save.  

This is the reason I suggest to do in steps.   Getting this to work should be very basic if you know what you are doing and probably confusing if you don't.  If you went right to the end, there could be a number of things going on.

The other examples for papa parse are complete and again very basic if you know what you are doing.
0
 
damoncf1234Author Commented:
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
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
>,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.
0
 
damoncf1234Author Commented:
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
0
 
damoncf1234Author Commented:
Scott was very responsive.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now