Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Read dates from excel file

Posted on 2004-09-06
4
Medium Priority
?
181 Views
Last Modified: 2010-04-06
Hi experts,

I have an excel file of the form:
              ColA                ColB         ColC       ........       ColK          Col L      .........................
Row4      Name            Position       Location             Start Date     Appraisal date
Row5    Person1        Manager        Location1            01/Aug/04      01/Oct/04
Row6    Person2        Nurse            Location2             08/Aug/04      08/Oct/04

I'm writing a simple html page where the locations will be in a drop down menu, and another menu will contain the 12 months of the year.
What i need is for the user to pick the location and/or month, and what is returned (in any way) is the details of people with the appraisals due in that period.

Thanks,
Glen
0
Comment
Question by:Glenhomes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Expert Comment

by:sgalzin
ID: 11989261
hi,

at first sight, i'd suggest you enter all the information in a javascript array. the way you'd go about that is :

*** begin file test.html ***
<html><head><script language="JavaScript"><!--

var data = new Array ();

data [ 0 ] = new Array ( 'Person1', 'Manager', 'Location1', /* etc. */ '01/Aug/04', '01/Oct/04' /* etc. */ );
data [ 1 ] = new Array ( 'Person2', 'Nurse', 'Location2', /* etc. */ '08/Aug/04', '08/Oct/04' /* etc. */ );
// and continue with data [ 2 ], data [ 3 ], etc.

//--></script></head><body>
<!-- the rest of your html code -->
</body></html>
*** end file test.html ***

then, please show us your html code so that we can link it with the javascript in order for it to do what you want.

please note : if you have too much of this information to enter by hand, we could probably write a small excel formula to help you type it, such as this one you could put in cell M5 :

="data [ " & ( ROW($A5) - 5 ) & " ] = new Array ( '" & A5 & "', '" & B5 & "', '" & C5 & "', '" & D5 & "', '" & E5 & "', '" & F5 & "', '" & G5 & "', '" & H5 & "', '" & I5 & "', '" & J5 & "', '" & K5 & "', '" & L5 & "' );"

then all you'd have to do is copy and paste the result in notepad.

cheers,

stephane.
0
 
LVL 4

Accepted Solution

by:
sgalzin earned 2000 total points
ID: 11990182
hi,

here's a new file that integrates a little of the html necessary. this is a working script (at least on my computer ;-). i hope this helps,

stephane.

*** begin file test.html ***
<html><head><script language="JavaScript"><!--

function getMonthNumber ( name )
{
 alert ( name );
 return '08';
}

function goSearch ()
{
 var s = '<table border="1"><tr><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td><td>G</td><td>H</td><td>I</td><td>J</td><td>K</td><td>L</td>';
 for ( var i = 0 ; i < data.length; i ++ )
 {
  var min = ( new Date ( Date.parse ( data [ i ] [ 10 ] .replace ( /(\d\d)\/([A-Z][a-z][a-z])\/(\d\d)/gi, '$2 $1, 20$3' ) ) ) ).getMonth () +1;
  var max = ( new Date ( Date.parse ( data [ i ] [ 11 ] .replace ( /(\d\d)\/([A-Z][a-z][a-z])\/(\d\d)/gi, '$2 $1, 20$3' ) ) ) ).getMonth () +1;
  if ( ( ( document.getElementById ( 'location' ).value == 0 ) || ( document.getElementById ( 'location' ).value == data [ i ] [ 2 ] ) ) && ( ( document.getElementById ( 'month' ).value == 0 ) || ( ( document.getElementById ( 'month' ).value >= min ) && ( document.getElementById ( 'month' ).value <= max ) ) ) )
  {
   s += '<tr>';
   for ( var j = 0; j < data [i ].length; j ++ )
    s += '<td>' + ( data [ i ] [ j ] == '' ? '&nbsp;' : data [ i ] [ j ] ) + '</td>';
   s += '</tr>';
  }
 }
 searchResults.innerHTML = s + '</table>';
}

var data = new Array ();

data [ 0 ] = new Array ( 'Person1', 'Manager', 'Location1', '', '', '', '', '', '', '', '01/Aug/04', '01/Oct/04' );
data [ 1 ] = new Array ( 'Person2', 'Nurse', 'Location2', '', '', '', '', '', '', '', '08/Aug/04', '08/Oct/04' );
// and continue with data [ 2 ], data [ 3 ], etc.

//--></script></head><body>

<select id="location">
<option value="0" selected>Select a location
<option value="Location1">Location1
<option value="Location2">Location2
<option value="Location3">Location3
</select>
&nbsp;&nbsp;
<select id="month">
<option value="0" selected>Select a month
<option value="1">January
<option value="2">February
<option value="3">March
<option value="4">April
<option value="5">May
<option value="6">June
<option value="7">July
<option value="8">August
<option value="9">September
<option value="10">October
<option value="11">November
<option value="12">December
</select>
&nbsp;&nbsp;<input type="button" onclick="goSearch()" value="Go Search !">
<div id="searchResults"></div>
</body></html>
*** end file test.html ***
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preface This article introduces an authentication and authorization system for a website.  It is understood by the author and the project contributors that there is no such thing as a "one size fits all" system.  That being said, there is a certa…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question