trunk/xls2conf/xls2conf.pl

#!/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);
1	dpavlin	22	#!/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	dpavlin	302	my $rsync_share_name;
15	iklaric	369	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	dpavlin	22	foreach my $sheet (@{$oBook->{Worksheet}}) {
27	dpavlin	133	if ($sheet->{Name} !~ m/(Instruments\|sheet1)/i) {
28			print "# skipped sheet ",$sheet->{Name},"\n";
29	dpavlin	22	next;
30			}
31	iklaric	369
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	dpavlin	22	for(my $i = $sheet->{MinRow} ; defined $sheet->{MaxRow} && $i <= $sheet->{MaxRow} ; $i++) {
60			# IP
61	iklaric	369	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	dpavlin	22
64			my $conf_name = lc($ime . '_' . $id);
65			$conf_name =~ s/\s+/_/g;
66
67	iklaric	369	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	dpavlin	22
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	dpavlin	302	if ($drive eq 'c' \|\| $drive eq 'd') {
84			$rsync_share = $drive . 'Drive';
85	dpavlin	22	} else {
86			die "unknown drive '$drive' from path '$path'";
87			}
88
89			$path =~ s#^[cd]:\\#/#gi;
90			$path =~ s#\\#/#g;
91
92	dpavlin	132	open(O, "> conf/${conf_name}.pl") \|\| die "can't open conf/${conf_name}.pl: $!";
93	dpavlin	22
94	dpavlin	133	if ($os =~ m#(98\|95\|9x)#i) {
95	dpavlin	22
96			print "$ip\tsmb://${share}\n";
97
98	dpavlin	185	push @{$smb_share_name{$conf_name}}, $share;
99	dpavlin	22
100			print O qq`
101	dpavlin	185	# $conf_name on $hostname $ip, $os
102	dpavlin	22	\$Conf{XferMethod} = 'smb';
103			\$Conf{SmbHostName} = '$hostname';
104	dpavlin	185	\$Conf{SmbShareName} = [ '`,join("','", @{$smb_share_name{$conf_name}}),qq`' ];
105	dpavlin	22	`;
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	dpavlin	132	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	dpavlin	22	print "$ip\trsync://${share}::${path}\n";
122
123			$path =~ s/([ '])/\\$1/g;
124	dpavlin	302	push @{$rsync_share_name->{$conf_name}->{$rsync_share}}, $path;
125	dpavlin	22
126	dpavlin	302	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	dpavlin	22	print O qq`
135	dpavlin	185	# $conf_name on $hostname $ip, $os
136	dpavlin	22	\$Conf{XferMethod} = 'rsyncd';
137	dpavlin	302	\$Conf{RsyncShareName} = [ $rsync_share_names ];
138			\$Conf{BackupFilesOnly} = [ `, join(",", @backup_files_only), qq` ];
139	dpavlin	22	`;
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);