From ca51c12c372eed6c4265b6a16a09ac4a221d5fd3 Mon Sep 17 00:00:00 2001
From: dpavlin <dpavlin@8392b6e1-25fa-0310-8288-cc32f8e212ea>
Date: Wed, 31 Aug 2005 21:22:48 +0000
Subject: [PATCH] document BackupPC_xls_report, merge all hosts into single
 sheet so you can use autofilter to select just some hosts, call test to
 install new version in xls target

git-svn-id: svn+ssh://llin/home/dpavlin/private/svn/BackupPC/trunk@108 8392b6e1-25fa-0310-8288-cc32f8e212ea
---
 Makefile                |  2 +-
 bin/BackupPC_xls_report | 36 ++++++++++++++++++++----------------
 doc/Search.pm           | 20 +++++++++++++++++++-
 3 files changed, 40 insertions(+), 18 deletions(-)

diff --git a/Makefile b/Makefile
index f02eb6a..111ff51 100644
--- a/Makefile
+++ b/Makefile
@@ -33,7 +33,7 @@ index: test
 	sudo rm -Rf /data/backuppc/data/casket || true
 	sudo -u backuppc /data/backuppc/bin/BackupPC_updatedb -i
 
-xls:
+xls: test
 	sudo -u backuppc /data/backuppc/bin/BackupPC_xls_report && cp -vf /tmp/report.xls ~/public_html/
 
 asa:
diff --git a/bin/BackupPC_xls_report b/bin/BackupPC_xls_report
index 717f54d..823df50 100755
--- a/bin/BackupPC_xls_report
+++ b/bin/BackupPC_xls_report
@@ -1,4 +1,4 @@
-#!/usr/local/bin/perl
+#!/usr/bin/perl
 
 use strict;
 use lib "__INSTALLDIR__/lib";
@@ -13,11 +13,12 @@ my $xls_file = '/tmp/report.xls';
 my $workbook  = Spreadsheet::WriteExcel->new($xls_file) || die "can't write to $xls_file: $!";
 
 my @cols = qw(
+hostname
 type
 num
 startTime
 endTime
-=D##-C##,duration
+=E##-D##,duration
 level
 noFill
 fillFromNum
@@ -39,9 +40,9 @@ sizeExist
 
 compress
 sizeNewComp
-=V##/S##,sizeNewRatio
+=W##/T##,sizeNewRatio
 sizeExistComp
-=X##/T##,sizeExistRatio
+=Y##/U##,sizeExistRatio
 );
 
 my $date_format = $workbook->add_format(num_format => 'yyyy-mm-dd hh:mm:ss');
@@ -67,29 +68,32 @@ my $TopDir = $bpc->TopDir();
 
 my $hosts = $bpc->HostInfoRead();
 
+my $worksheet = $workbook->add_worksheet('BackupPC');
+my $row = 0;
+
+my $i = 0;
+# insert sheet header (with formatting)
+foreach my $c (@cols) {
+	if ($c =~ m/^=.*,(.+)/) {
+		$worksheet->write($row, $i++, $1, $header_format);
+	} else {
+		$worksheet->write($row, $i++, $c, $header_format);
+	}
+}
+$row++;
+
 foreach my $host_key (sort keys %{$hosts}) {
 
 	my $hostname = $hosts->{$host_key}->{'host'} || die "can't find host for $host_key";
 
 	print $hostname, " ";
 
-	my $worksheet = $workbook->add_worksheet($hostname);
-
 	# get backups for a host
 	my @backups = $bpc->BackupInfoRead($hostname);
 	print scalar @backups, " increments\n";
 
-	my $row = 0;
 	my $i = 0;
-
-	foreach my $c (@cols) {
-		if ($c =~ m/^=.*,(.+)/) {
-			$worksheet->write($row, $i++, $1, $header_format);
-		} else {
-			$worksheet->write($row, $i++, $c, $header_format);
-		}
-	}
-	$row++;
+	$worksheet->write($row, $i++, $hostname);
 
 	foreach my $backup (@backups) {
 		for (my $i = 0; $i <= $#cols; $i++) {
diff --git a/doc/Search.pm b/doc/Search.pm
index 697c7d5..3bc3921 100644
--- a/doc/Search.pm
+++ b/doc/Search.pm
@@ -109,11 +109,16 @@ You will also need a few additional cpan modules
 
 =item File::Pid
 
+=item Spreadsheet::WriteExcel
+
 =back
 
+Last module, C<Spreadsheet::WriteExcel> is needed only if you want to use
+C<BackupPC_xls_report> to generate Excel reports from your backup data.
+
 Easiest way to install them is using C<cpan> shell.
 
-  $ sudo cpan File::Pid
+  $ sudo cpan File::Pid Spreadsheet::WriteExcel
 
 =head1 Creation of initial database
 
@@ -150,6 +155,19 @@ B<Documentation is still incomplete>.
 Now that you finished installation, you can select new options from
 menu on the left and example search and archival.
 
+=head1 Reporting in Excel
+
+Often, it is useful to be able to present report about your BackupPC hosts, number
+of snapshots, total size and other useful info. While all those information can
+be accessed using web browser, for analysis it's useful to have them in spreadsheet
+form. With this data, you can monitor changes on your backup cycle, effects of changes
+on your server or network to your BackupPC installation and so on.
+
+You can create Excel spreadsheet (which works perfectly with Gnumeric also) using
+following command:
+
+  $ sudo -u backuppc /data/backuppc/bin/BackupPC_xls_report
+
 =head1 Related projects
 
 BackupPC allready has archival host which might suit your needs better (and
-- 
2.20.1