xls2conf/xls2conf_new.pl

   1 #!/usr/bin/perl -w
   2
   3 use strict;
   4 use Spreadsheet::ParseExcel;
   5
   6 my $xls_file = shift @ARGV || die "usage: $0 file.xls\n";
   7
   8 my $oBook = Spreadsheet::ParseExcel::Workbook->Parse($xls_file);
   9
  10 # BackupPC hosts file
  11 my %bpc_hosts;
  12 my %hosts;
  13 my %smb_share_name;
  14 my $rsync_share_name;
  15 my $column_data = {
  16                     "ID" => {"name" => "InstrumentID_part2", "c_index" => -1},
  17                     "name" => {"name" => "InstrumentID_part1", "c_index" => -1},
  18                     "IP" => {"name" => "PC_IP", "c_index" => -1},
  19                     "pc_name" => {"name" => "PC_Name", "c_index" => -1},
  20                     "OS" => {"name" => "PC_OS", "c_index" => -1},
  21                     "path" => {"name" => "Local export path", "c_index" => -1},
  22                     "share" => {"name" => "Shared folder", "c_index" => -1}
  23             };
  24
  25 outer_loop:
  26 foreach my $sheet (@{$oBook->{Worksheet}}) {
  27         if ($sheet->{Name} !~ m/(Instruments|sheet1)/i) {
  28                 print "# skipped sheet ",$sheet->{Name},"\n";
  29                 next;
  30         }
  31
  32         # reset all column data
  33         foreach my $key(keys(%{$column_data})) {
  34                 $column_data->{$key}->{c_index} = -1;
  35         }
  36
  37         # retrieve all column data
  38         for (my $column = $sheet->{MinCol}; defined($sheet->{MaxCol}) && $column <= $sheet->{MaxRow}; $column++) {
  39                 next if (!defined($sheet->{Cells}[0][$column]));
  40                 my $column_name = $sheet->{Cells}[0][$column]->Value;
  41
  42                 foreach my $key(keys(%{$column_data})) {
  43                         my $column_match = $column_data->{$key}->{name};
  44                         next if !defined($column_match);
  45                         if ($column_name =~ m/$column_match/i) {
  46                                 $column_data->{$key}->{c_index} = $column;
  47                         }
  48                 }
  49         }
  50
  51         # if some columns are not defined, skip this sheet
  52         foreach my $key(keys(%{$column_data})) {
  53                 if ($column_data->{$key}->{c_index} == -1) {
  54                         print "# skipped sheet ",$sheet->{Name}," because not all columns are properly defined.\n";
  55                         next outer_loop;
  56                 }
  57         }
  58
  59         for(my $i = $sheet->{MinRow} ; defined $sheet->{MaxRow} && $i <= $sheet->{MaxRow} ; $i++) {
  60                 # IP
  61                 my $id = $sheet->{Cells}[$i][ $column_data->{ID}->{c_index} ]->{Val} || next;
  62                 my $ime = $sheet->{Cells}[$i][ $column_data->{name}->{c_index} ]->{Val} || next;
  63
  64                 my $conf_name = lc($ime . '_' . $id);
  65                 $conf_name =~ s/\s+/_/g;
  66
  67                 my $ip = $sheet->{Cells}[$i][ $column_data->{IP}->{c_index} ]->{Val} || next;
  68                 my $hostname = $sheet->{Cells}[$i][ $column_data->{pc_name}->{c_index} ]->Value || next;
  69                 my $os = $sheet->{Cells}[$i][ $column_data->{OS}->{c_index} ]->Value || next;
  70                 my $path = $sheet->{Cells}[$i][ $column_data->{path}->{c_index} ]->Value || next;
  71                 my $share = $sheet->{Cells}[$i][ $column_data->{share}->{c_index} ]->Value || next;
  72
  73                 if ($ip !~ /\d+\.\d+\.\d+\.\d+/) {
  74                         print "# skipped $ip, $hostname, $os, $path, $share\n";
  75                         next;
  76                 }
  77
  78                 print "$ip hostname[$hostname] os[$os] path[$path] share[$share]\n";
  79
  80                 my $drive = lc(substr($path,0,1));
  81
  82                 my $rsync_share;
  83                 if ($drive eq 'c' || $drive eq 'd') {
  84                         $rsync_share = $drive . 'Drive';
  85                 } else {
  86                         die "unknown drive '$drive' from path '$path'";
  87                 }
  88
  89                 $path =~ s#^[cd]:\\#/#gi;
  90                 $path =~ s#\\#/#g;
  91
  92                 open(O, "> conf/${conf_name}.pl") || die "can't open conf/${conf_name}.pl: $!";
  93
  94                 if ($os =~ m#(98|95|9x)#i) {
  95
  96                         print "$ip\tsmb://${share}\n";
  97
  98                         push @{$smb_share_name{$conf_name}}, $share;
  99
 100 print O qq`
 101 # $conf_name on $hostname $ip, $os
 102 \$Conf{XferMethod} = 'smb';
 103 \$Conf{SmbHostName} = '$hostname';
 104 \$Conf{SmbShareName} = [ '`,join("','", @{$smb_share_name{$conf_name}}),qq`' ];
 105 `;
 106
 107                         if ($hosts{$ip}) {
 108                                 $hosts{$ip} .= "\t$hostname" unless ($hosts{$ip} =~ m/$hostname/i);
 109                         } else {
 110                                 $hosts{$ip} = "$ip\t$hostname";
 111                         }
 112                 } else {
 113
 114                         sub case_insesitive {
 115                                 my $t = shift || return;
 116                                 return '[' . lc($t) . uc($t) . ']';
 117                         }
 118
 119                         $path =~ s/([a-zA-z])/case_insesitive($1)/ge;
 120
 121                         print "$ip\trsync://${share}::${path}\n";
 122
 123                         $path =~ s/([ '])/\\$1/g;
 124                         push @{$rsync_share_name->{$conf_name}->{$rsync_share}}, $path;
 125
 126                         my @shares = sort keys %{$rsync_share_name->{$conf_name}};
 127                         my $rsync_share_names = "'" . join("','", @shares) . "'";
 128                         my @backup_files_only;
 129                         foreach my $s (@shares) {
 130                                 foreach my $p (@{$rsync_share_name->{$conf_name}->{$s}}) {
 131                                         push @backup_files_only, "'$s' => '$p'";
 132                                 }
 133                         }
 134 print O qq`
 135 # $conf_name on $hostname $ip, $os
 136 \$Conf{XferMethod} = 'rsyncd';
 137 \$Conf{RsyncShareName} = [ $rsync_share_names ];
 138 \$Conf{BackupFilesOnly} = [ `, join(",", @backup_files_only), qq` ];
 139 `;
 140
 141                 }
 142
 143                 close(O);
 144
 145                 $bpc_hosts{$conf_name} = "$conf_name\t0\tasa";
 146                 if ($hosts{$ip}) {
 147                         $hosts{$ip} .= "\t$conf_name" unless ($hosts{$ip} =~ m/$conf_name/i);
 148                         $hosts{$ip} .= "\t$hostname" unless ($hosts{$ip} =~ m/$hostname/i);
 149                 } else {
 150                         $hosts{$ip} = "$ip\t$conf_name\t$hostname";
 151                 }
 152         }
 153
 154 }
 155
 156 open(H, "> conf/hosts.backuppc") || die "hosts.backuppc $!";
 157 foreach my $k (sort keys %bpc_hosts) {
 158         print H $bpc_hosts{$k},"\n";
 159 }
 160 close(H);
 161
 162 open(H, "> conf/hosts.add") || die "hosts.add $!";
 163 foreach my $k (sort keys %hosts) {
 164         print H $hosts{$k},"\n";
 165 }
 166 close(H);