in reply to Re: EXCEL TO HTML Conversion w/Perl
in thread EXCEL TO HTML Conversion w/Perl

Hi David,

This is how my excel work books look like:

Milestone.xls

MilestoneID Task IDs
Milestone1 Task1a, Task1b, Task2a, Task2c
Milestone2 Task1c, Task2a, Task1d, Task2b

Where the Tasks come from TaskList1.xls and Tasklist2.xls. These files look like this (as an example snippet from TaskList1.xls):

TaskID Task Description Start Date End Date
Task1a Task1a Description 06/10/04 06/12/04
Task1b Task1b Description 06/13/04 06/15/04

So on and so forth...

What I need to do is, open and read the Milestone.xls to get the Milestone and the task IDs. Then query these task IDs in the TaskList1.xls and Tasklist2.xls and then generate a "schedule" table for each milestone by getting the start and end dates for each task.

I am looking for either a code snippet or an "efficient" way to do this.

One suggestion I got was to read the Tasklist1 and Tasklist2 excel files into memory (hash table??) and then query since the Task IDs are unique.

Any help would be greatly appreciated.

Thank you very much.

Wisdom Seeker, Andy

Replies are listed 'Best First'.
Re^3: EXCEL TO HTML Conversion w/Perl
by davidj (Priest) on Jun 08, 2004 at 01:10 UTC
    This isn't very elegant (and maybe not too efficient), but it will store and extract the data in a way that is usable. I don't do html work, so you are on your own for figuring that part out :)
    Note: it assumes that in milestones.xls, all the taskID's are stored in a single cell.

    use Spreadsheet::ParseExcel::Simple;

my (%milestones, %taskID);
my $xls = Spreadsheet::ParseExcel::Simple->read('milestones.xls');
foreach my $sheet ($xls->sheets) {
    while ($sheet->has_data) {  
        my @data = $sheet->next_row;
        next if $data[0] =~ m/ID/;
        next if $data[0] eq '';
        my @tasks = split(",", $data[1]);
        foreach my $task (@tasks) {
             $milestones{$data[0]} = [ @tasks ];
        }
    }
}

my $xls = Spreadsheet::ParseExcel::Simple->read('TaskLists.xls');
foreach my $sheet ($xls->sheets) {
    while ($sheet->has_data) {  
        my @data = $sheet->next_row;
        next if $data[0] =~ m/ID/;
        next if $data[0] eq '';
        my @tasks = @data[2..3];
        $taskID{$data[0]} = [ @tasks ];
    }
}

foreach my $milestone (keys %milestones) {
    my @tasks = @{ $milestones{$milestone} };
    foreach my $task (@tasks) {
        print "milestone = $milestone, id = $task, start = @{ $taskID{
+$task} }[0], stop = @{ $taskID{$task} }[1]\n";  
    }
}

exit;

    [download]
[reply]
[d/l]

      Hi David,

      Thank you very much for the code. I will try this out. Your assumption is right. All the TaskID's are in the same single cell comma separated. The HTML part is easy since it is a bunch or print statements. I was stuck with the perl code for extracting the information from the excel work books.

      Thanks a bunch,

      --Wisdom Seeker, Andy

[reply]

In Section Seekers of Perl Wisdom