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";
+++ /dev/null
-#!/usr/bin/perl -w
-
-use strict;
-use Spreadsheet::ParseExcel;
-
-my $xls_file = shift @ARGV || die "usage: $0 file.xls\n";
-
-my $oBook = Spreadsheet::ParseExcel::Workbook->Parse($xls_file);
-
-# BackupPC hosts file
-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][ $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][ $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";
- next;
- }
-
- print "$ip hostname[$hostname] os[$os] path[$path] share[$share]\n";
-
- my $drive = lc(substr($path,0,1));
-
- my $rsync_share;
- if ($drive eq 'c' || $drive eq 'd') {
- $rsync_share = $drive . 'Drive';
- } else {
- die "unknown drive '$drive' from path '$path'";
- }
-
- $path =~ s#^[cd]:\\#/#gi;
- $path =~ s#\\#/#g;
-
- open(O, "> conf/${conf_name}.pl") || die "can't open conf/${conf_name}.pl: $!";
-
- if ($os =~ m#(98|95|9x)#i) {
-
- print "$ip\tsmb://${share}\n";
-
- push @{$smb_share_name{$conf_name}}, $share;
-
-print O qq`
-# $conf_name on $hostname $ip, $os
-\$Conf{XferMethod} = 'smb';
-\$Conf{SmbHostName} = '$hostname';
-\$Conf{SmbShareName} = [ '`,join("','", @{$smb_share_name{$conf_name}}),qq`' ];
-`;
-
- if ($hosts{$ip}) {
- $hosts{$ip} .= "\t$hostname" unless ($hosts{$ip} =~ m/$hostname/i);
- } else {
- $hosts{$ip} = "$ip\t$hostname";
- }
- } else {
-
- sub case_insesitive {
- my $t = shift || return;
- return '[' . lc($t) . uc($t) . ']';
- }
-
- $path =~ s/([a-zA-z])/case_insesitive($1)/ge;
-
- print "$ip\trsync://${share}::${path}\n";
-
- $path =~ s/([ '])/\\$1/g;
- push @{$rsync_share_name->{$conf_name}->{$rsync_share}}, $path;
-
- my @shares = sort keys %{$rsync_share_name->{$conf_name}};
- my $rsync_share_names = "'" . join("','", @shares) . "'";
- my @backup_files_only;
- foreach my $s (@shares) {
- foreach my $p (@{$rsync_share_name->{$conf_name}->{$s}}) {
- push @backup_files_only, "'$s' => '$p'";
- }
- }
-print O qq`
-# $conf_name on $hostname $ip, $os
-\$Conf{XferMethod} = 'rsyncd';
-\$Conf{RsyncShareName} = [ $rsync_share_names ];
-\$Conf{BackupFilesOnly} = [ `, join(",", @backup_files_only), qq` ];
-`;
-
- }
-
- close(O);
-
- $bpc_hosts{$conf_name} = "$conf_name\t0\tasa";
- if ($hosts{$ip}) {
- $hosts{$ip} .= "\t$conf_name" unless ($hosts{$ip} =~ m/$conf_name/i);
- $hosts{$ip} .= "\t$hostname" unless ($hosts{$ip} =~ m/$hostname/i);
- } else {
- $hosts{$ip} = "$ip\t$conf_name\t$hostname";
- }
- }
-
-}
-
-open(H, "> conf/hosts.backuppc") || die "hosts.backuppc $!";
-foreach my $k (sort keys %bpc_hosts) {
- print H $bpc_hosts{$k},"\n";
-}
-close(H);
-
-open(H, "> conf/hosts.add") || die "hosts.add $!";
-foreach my $k (sort keys %hosts) {
- print H $hosts{$k},"\n";
-}
-close(H);