#!/usr/bin/perl -w

######################################################################
+#########
#
# Uses functions from the Spreadsheet::WriteExcel::Utility #module.
######################################################################
+#########

use strict;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;

# Create a new workbook and add a worksheet
my $workbook        = Spreadsheet::WriteExcel->new("Audit.xls");
my $worksheet       = $workbook->add_worksheet('AP');
my $worksheet2       = $workbook->add_worksheet('NAP');


# Set up some formats
my %heading         =   (
                            bold        => 1,
                            pattern     => 1,
                            fg_color    => 15,
                            border      => 1,
                            align       => 'center',
                        );

my $heading         = $workbook->add_format(%heading);


# Write the main headings for the worksheet AP
$worksheet->freeze_panes(1); # Freeze the first row
$worksheet->write('A1', 'Login',     $heading);
$worksheet->write('B1', 'UserName', $heading);
$worksheet->write('C1', 'Group',    $heading);
$worksheet->write('D1', 'Phone',    $heading);
$worksheet->write('E1', 'City',     $heading);
$worksheet->write('F1', 'State',     $heading);

#Write the server headings for the worksheet AP
$worksheet->write('G1', 'LDAP-Status',     $heading);
$worksheet->write('H1', 'Dopey',     $heading);
$worksheet->write('I1', 'Sleepy',     $heading);
$worksheet->write('J1', 'Sneezy',     $heading);
$worksheet->write('K1', 'Grumpy',     $heading);
$worksheet->write('L1', 'Doc',     $heading);
$worksheet->write('M1', 'Lazy',     $heading);
$worksheet->write('N1', 'Smelly',     $heading);
$worksheet->write('O1', 'Stinky',     $heading);
$worksheet->write('P1', 'Sweaty',     $heading);


# Write the main headings for the worksheet NAP
$worksheet2->freeze_panes(1); # Freeze the first row
$worksheet2->write('A1', 'Login',     $heading);
$worksheet2->write('B1', 'UserName', $heading);
$worksheet2->write('C1', 'LDAP-Status',    $heading);
#$worksheet->write('D1', 'Phone',    $heading);
#$worksheet->write('E1', 'City',     $heading);
#$worksheet->write('F1', 'State',     $heading);

#Write the server headings for the worksheet NAP
$worksheet2->write('D1', 'Dopey',     $heading);
$worksheet2->write('E1', 'Sleepy',     $heading);
$worksheet2->write('F1', 'Sneezy',     $heading);
$worksheet2->write('G1', 'Grumpy',     $heading);
$worksheet2->write('H1', 'Doc',     $heading);
$worksheet2->write('I1', 'Lazy',     $heading);
$worksheet2->write('J1', 'Smelly',     $heading);
$worksheet2->write('K1', 'Stinky',     $heading);
$worksheet2->write('L1', 'Sweaty',     $heading);




# Set the column widths for AP
$worksheet->set_column('A:A', 8);
$worksheet->set_column('B:B', 21);
$worksheet->set_column('C:C', 33);
$worksheet->set_column('D:D', 12);
$worksheet->set_column('E:E', 16);
$worksheet->set_column('F:F', 5);
$worksheet->set_column('G:G', 6);
$worksheet->set_column('H:P', 11);

# Set the column widths for NAP
$worksheet2->set_column('A:A', 8);
$worksheet2->set_column('B:B', 39);
$worksheet2->set_column('C:C', 12);
$worksheet2->set_column('D:L', 11);
#$worksheet->set_column('E:E', 16);
#$worksheet->set_column('F:F', 5);
#$worksheet->set_column('G:O', 15);

my @data;
my $data="outfile";
open (DATA, "$data");
foreach my $line (<DATA>) {
    chomp $line;
    next if $line eq '';
    # Simple-minded processing of CSV data. Refer to the #Text::CSV_XS
+ and Text::xSV modules for a more complete CVS #handling.
    my @items = split /:/, $line;
    #my @server = split /;/,$line;
    push @data, \@items;
    #push @data, \@server;
   #print AP @data, \@sitems;
}

my @sata;
my $sata="sysend";
open (SATA, "$sata");
foreach my $sine (<SATA>) {
    chomp $sine;
    next if $sine eq '';
    # Simple-minded processing of CSV data. Refer to the Text::CSV_XS
    # and Text::xSV modules for a more complete CVS handling.
    my @sitems = split /:/, $sine;
    #my @server = split /;/,$line;
    push @sata, \@sitems;
    #push @data, \@server;
   #print NAP @data, \@items;
}



# Write out the items from each row
my $row = 1;
foreach my $data (@data) {

    

    $worksheet->write($row, 0, @$data[0]);
    $worksheet->write($row, 1, @$data[1]);
    $worksheet->write($row, 2, @$data[2]);
    $worksheet->write($row, 3, @$data[3]);
    $worksheet->write($row, 4, @$data[4]);
    $worksheet->write($row, 5, @$data[5]);
    $worksheet->write($row, 6, @$data[6]);
    
    
    
   $worksheet->write($row, 7, @$data[7]);
    $worksheet->write($row, 8, @$data[8]);
    $worksheet->write($row, 9, @$data[9]);
    $worksheet->write($row, 10, @$data[10]);
    $worksheet->write($row, 11, @$data[11]);
    $worksheet->write($row, 12, @$data[12]);
    $worksheet->write($row, 13, @$data[13]);
    $worksheet->write($row, 14, @$data[14]);
    $worksheet->write($row, 15, @$data[15]);
    $worksheet->write($row, 16, @$data[16]);
    

#increment rows
   $row++;
}

# Write out the items from each row
my $sow = 1;
foreach my $sata (@sata) {

    

    $worksheet2->write($sow, 0, @$sata[0]);
    $worksheet2->write($sow, 1, @$sata[1]);
    $worksheet2->write($sow, 2, @$sata[2]);
    $worksheet2->write($sow, 3, @$sata[3]);
    $worksheet2->write($sow, 4, @$sata[4]);
    $worksheet2->write($sow, 5, @$sata[5]);
    $worksheet2->write($sow, 6, @$sata[6]);
    
    
    
    $worksheet2->write($sow, 7, @$sata[7]);
    $worksheet2->write($sow, 8, @$sata[8]);
    $worksheet2->write($sow, 9, @$sata[9]);
    $worksheet2->write($sow, 10, @$sata[10]);
    $worksheet2->write($sow, 11, @$sata[11]);
    $worksheet2->write($sow, 12, @$sata[12]);
    #$worksheet2->write($row, 13, @$sata[13]);
    #$worksheet2->write($row, 14, @$sata[14]);
    #$worksheet2->write($row, 15, @$sata[15]);
    
   #increment rows
   $sow++;
}
[download]