- new version of xls2conf has been put as the official version

[BackupPC.git] / xls2conf / xls2conf.pl

diff --git a/xls2conf/xls2conf.pl b/xls2conf/xls2conf.pl
index ff12e93..c717024 100755 (executable)
--- a/xls2conf/xls2conf.pl
+++ b/xls2conf/xls2conf.pl
@@ -12,25 +12,63 @@ my %bpc_hosts;
 my %hosts;
 my %smb_share_name;
 my $rsync_share_name;
-
+my $column_data = {
+                    "ID" => {"name" => "InstrumentID_part2", "c_index" => -1},
+                    "name" => {"name" => "InstrumentID_part1", "c_index" => -1},
+                    "IP" => {"name" => "PC_IP", "c_index" => -1},
+                    "pc_name" => {"name" => "PC_Name", "c_index" => -1},
+                    "OS" => {"name" => "PC_OS", "c_index" => -1},
+                    "path" => {"name" => "Local export path", "c_index" => -1},
+                    "share" => {"name" => "Shared folder", "c_index" => -1}
+           };
+                  
+outer_loop:
 foreach my $sheet (@{$oBook->{Worksheet}}) {
        if ($sheet->{Name} !~ m/(Instruments|sheet1)/i) {
                print "# skipped sheet ",$sheet->{Name},"\n";
                next;
        }
+       
+       # reset all column data
+       foreach my $key(keys(%{$column_data})) {
+               $column_data->{$key}->{c_index} = -1;
+       }
+       
+       # retrieve all column data
+       for (my $column = $sheet->{MinCol}; defined($sheet->{MaxCol}) && $column <= $sheet->{MaxRow}; $column++) {
+               next if (!defined($sheet->{Cells}[0][$column]));
+               my $column_name = $sheet->{Cells}[0][$column]->Value;
+               
+               foreach my $key(keys(%{$column_data})) {
+                       my $column_match = $column_data->{$key}->{name};
+                       next if !defined($column_match);
+                       if ($column_name =~ m/$column_match/i) {
+                               $column_data->{$key}->{c_index} = $column;
+                       }
+               }
+       }
+       
+       # if some columns are not defined, skip this sheet
+       foreach my $key(keys(%{$column_data})) {
+               if ($column_data->{$key}->{c_index} == -1) {
+                       print "# skipped sheet ",$sheet->{Name}," because not all columns are properly defined.\n";
+                       next outer_loop;
+               }
+       }
+       
        for(my $i = $sheet->{MinRow} ; defined $sheet->{MaxRow} && $i <= $sheet->{MaxRow} ; $i++) {
                # IP
-               my $id = $sheet->{Cells}[$i][3]->{Val} || next;
-               my $ime = $sheet->{Cells}[$i][4]->{Val} || next;
+               my $id = $sheet->{Cells}[$i][ $column_data->{ID}->{c_index} ]->{Val} || next;
+               my $ime = $sheet->{Cells}[$i][ $column_data->{name}->{c_index} ]->{Val} || next;
 
                my $conf_name = lc($ime . '_' . $id);
                $conf_name =~ s/\s+/_/g;
 
-               my $ip = $sheet->{Cells}[$i][16]->{Val} || next;
-               my $hostname = $sheet->{Cells}[$i][17]->Value || next;
-               my $os = $sheet->{Cells}[$i][19]->Value || next;
-               my $path = $sheet->{Cells}[$i][23]->Value || next;
-               my $share = $sheet->{Cells}[$i][24]->Value || next;
+               my $ip = $sheet->{Cells}[$i][ $column_data->{IP}->{c_index} ]->{Val} || next;
+               my $hostname = $sheet->{Cells}[$i][ $column_data->{pc_name}->{c_index} ]->Value || next;
+               my $os = $sheet->{Cells}[$i][ $column_data->{OS}->{c_index} ]->Value || next;
+               my $path = $sheet->{Cells}[$i][ $column_data->{path}->{c_index} ]->Value || next;
+               my $share = $sheet->{Cells}[$i][ $column_data->{share}->{c_index} ]->Value || next;
 
                if ($ip !~ /\d+\.\d+\.\d+\.\d+/) {
                        print "# skipped $ip, $hostname, $os, $path, $share\n";