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

Read dates from excel file

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
Glenhomes
Asked:
Glenhomes
  • 2
1 Solution
 
sgalzinCommented:
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
 
sgalzinCommented:
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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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